Không buộc phải thời điểm nào Việc chấm công bên trên trang bị chnóng công cũng mang về kết quả tức thì. Trong một vài trường phù hợp chúng ta phải gửi dữ liệu từ bỏ máy chnóng công sang Excel thì mới có thể tính được số giờ công thao tác của nhân viên. Trong bài học kinh nghiệm này Học Excel Online đã lý giải các bạn phương pháp tính thời gian làm việc ở bảng chnóng công Excel với dữ liệu được trích xuất từ máy chấm công. Nội dung ví dụ như sau:

Chúng ta bao gồm bảng dữ liệu trích xuất tự đồ vật chnóng công mon 07 năm 2017 gồm:

*

Yêu cầu: Dựa vào khoảng thời gian vào, giờ ra theo từng giờ của từng nhân viên, hãy tính thời gian làm việc, số lần đi muộn của những nhân viên.

Bạn đang xem: Bảng chấm công theo giờ


Xem nhanh


Cách làm

Cách 1: Khảo gần cạnh, Đánh Giá và so với dữ liệu mối cung cấp (bảng tài liệu kết xuất tự đồ vật chấm công ra excel)

Đây là bước cực kỳ đặc trưng, Việc so sánh bảng tài liệu mối cung cấp sẽ giúp chúng ta gồm phương án xử lý tương xứng cùng đúng đắn.

Để tiến hành các bước này, họ bắt buộc nắm được những bề ngoài sau:


Dù đang bao gồm phần mềm, nhưng lại tài năng Excel vẫn rất là đặc trưng với kế toán thù, bạn đang vững vàng Excel chưa? Hãy nhằm tôi khiến cho bạn, đăng ký khoá học tập Excel:



Mục đích công việc: Tính công thao tác làm việc theo thời gianĐối tượng liên quan: Mã nhân viên, Ngày vào thời điểm tháng, Giờ vào, Giờ raTiêu chí review (dựa trên mục đích, tác dụng cần đạt được để đưa ra tiêu chuẩn mang lại các đối tượng người dùng liên quan) Mã Nhân viên: Liên tục theo dòng, không được để loại trống Ngày vào tháng: Bao tất cả xác định ngày vào tháng, vật dụng vào tuần, sáng tỏ ngày nghỉ lễ, nghỉ ngơi bù Giờ vào, tiếng ra: Các ngày có tác dụng việc đều sở hữu giờ đồng hồ vào, giờ đồng hồ ra. lúc tính công sẽ dựa vào việc Reviews tiếng vào, giờ đồng hồ ra để tính ra những nhiều loại công.

Ta có thể thấy, để tính toán được, đề nghị phải điều chỉnh lại kết cấu bảng dữ liệu sao cho đúng những tiêu chí.

Việc tính toán bên trên nhiều điều kiện, cần hàm sẽ sử dụng để tính toán thù là SUMIFS, COUNTIFS cùng hàm IF

Cách 2: Cấu trúc lại bảng dữ liệu

Để rút gọn gàng bài viết, mình sẽ không nêu từng thao tác, nhưng sẽ đưa biểu trưng cùng gợi ý các biện pháp làm để những bạn có thể tự làm được:

(Bảng chnóng công này còn có cấu tạo hàng ngày gồm 2 cột, khác bài trước là mỗi ngày 1 cột)

*
*

Phần Tháng và Năm (dòng 2, 3): Đặt bên ngoài để luôn thể Việc thay đổiNgày vào tháng (cái 4): Dùng hàm Date, bắt đầu với ngày 26 mon trước đó (vày bảng nguyên liệu kết xuất tính từ ngày 26 yêu cầu ta vẫn giữ nguyên đòi hỏi này. Tính công từ ngày 26 tháng trước cho ngày 25 tháng sau). Riêng 2 ngày cuối có thể thanh lịch mon tiếp theo (trường phù hợp mon có 28-29 ngày) buộc phải có thể xử lý nhằm quăng quật trống nếu như thanh lịch tháng sau, tức là ngày 27 mon này trở đi là sẽ bước lịch sự tháng sau của bảng tính công.Phần Mã NV (cột A, B, C): Mỗi mã 1 dòng, nhằm tập thích hợp theo mã NV đúng đắn hơn.Thđọng trong tuần (cái 5): Dùng hàm Choose kết hợp với hàm Weekday để xác định. Kết hòa hợp tuấn kiệt Conditional Formatting để tô color riêng biệt ngày CN với ngày thường xuyên.Giờ vào, giờ đồng hồ ra (dòng 6): hoàn toàn có thể quy ước số 1 là giờ vào, số 2 là giờ ra.Lưu ý: một ngày bao gồm 2 giờ, bắt buộc 2 cột sẽ tính cho 1 ngày. Vì vậy khi cách xử lý ngày sinh hoạt chiếc 4 đề nghị chú ý ngôn từ này.

Tất cả hồ hết câu chữ trên, mình sẽ giải đáp chi tiết trong bài: Tạo bảng chnóng công trên excel. Vui lòng xem lại bài bác này để được gợi ý chi tiết.

Cách 3: Lọc cùng mang dữ liệu 

Tại bảng kết xuất, cần sử dụng công dụng Data/Filter nhằm thanh lọc vứt mẫu trống vào cột Mã NV, sau đó copy tài liệu lịch sự bảng xử trí. Sử dụng chức năng Paste Special/Value cần nên dán tài liệu làm lơ dòng bị ẩn.

*

Bảng kết xuất thực hiện tác dụng lọc (Data Filter)

*

Dữ liệu chấm công nghỉ ngơi bảng kết xuất => Copy / Paste Special -> Value => Sang bảng xử lý

Bước 4: Tính giờ công trong ngày

*

Tạo thêm 1 vùng, trong đó hàng ngày chỉ tương ứng 1 cột. Cách giải pháp xử lý ngày, thứ giống như với phần trước.

=> Mục đích: Tính số giờ công làm việc theo từng ngày

Giờ công làm việc trong ngày = Giờ ra – Giờ vào

Giờ ra = (Tổng số phút quy ước tại thời điểm chấm công ra = Số tiếng * 60 + Số phút)/60

Giờ vào = (Tổng số phút ít quy ước tại thời điểm chấm công vào = Số giờ * 60 + Số phút)/60

BN7 =ROUND(((HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2)))-(HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))))/60,2)

quý khách có thể filldown, fillright cách làm từ BN7 mang đến các ô khác.

* Lưu ý:

Có thể có số âm. Số âm là trường hợp Chỉ gồm giờ đồng hồ vào nhưng mà không tồn tại giờ đồng hồ ra => Lý do: Quên chấm công. Việc này thường xuyên xảy ra

Bước 5: Xác định kết quả chấm công

lấy ví dụ như họ đề xuất 1 số ít hiệu quả chấm công như sau:

*

Ta có thể thấy:

Ngày công đủ 8h tính dựa vào vùng vẫn xác định ví dụ thời gian công, với khoảng thời gian làm >=8 (mục 2.4)

CS7 = COUNTIF(BN7:CR7,”>=”&8)

Quên chnóng công tính dựa trên vùng đã xác định số giờ công, cùng với khoảng thời gian

CT7 = COUNTIFS(BN7:CR7,”Tổng số ngày công = Đủ 8h + Quên chnóng công. Vì quên chnóng công rất có thể vẫn được tính là 1 trong ngày công,hoặc Tổng số ngày công = Đủ 8h + (Quên chấm công /2) nếu quy ước quên chnóng công sẽ coi là nửa ngày công.

Xem thêm: Kiểm Tra Các Thiết Bị Đang Kết Nối Đến Router, Mách Bạn Cách Kiểm Tra Ai Đang Dùng Wifi Nhà Bạn

CU7 = CS7+CT7 hoặc CU7 = CS7+(CT7/2)

Ngày ngủ = (Tổng số ngày tất cả công = 0) – (Số ngày ngủ tuần = chủ nhật) Trường thích hợp ngày nghỉ tuần là chủ nhật. Nếu nghỉ ngơi cả T7, công nhân thì đã trừ thêm số ngày vật dụng 7 trong tháng

CV7 = IF(B7=””,0,COUNTIF(BN7:CR7,0)-COUNTIF(BN$5:CR$5,”CN”)-COUNTIF(BN$4:CR$4,””))

Đi muộn: Căn uống cứ vào khung giờ vào làm cho, đối chiếu cùng với chỉ tiêu 8h (tuỳ quy ước giờ đồng hồ vào làm cho của đơn vị)

Muộn 5′ => CW7 = COUNTIFS($D7:$BM7,”>”&”8:05″,$D7:$BM7,” CX7 = COUNTIFS($D7:$BM7,”>”&”8:10″,$D7:$BM7,” CY7 = COUNTIFS($D7:$BM7,”>”&”8:15″,$D$6:$BM$6,1)

=> Bạn rất có thể filldown phương pháp xuống cho các mã NV tiếp theo

Kết quả của mục 2.5 là kết quả sau cùng, hoàn toàn có thể sử dụng nhằm tính lương cho NV

Cách tối ưu

Mình đang gợi ý một vài ba nghệ thuật giúp tối ưu hoá bảng xử lý: (Ưu tiên mang lại hầu hết các bạn biết sử dụng VBA, nếu không biết VBA thì làm bằng tay bằng tay)

Sử dụng VBA nhằm lưu giữ báo cáo xử lý cùng kết quả tính được qua một Sheet riêng nhằm tàng trữ hiệu quả. Bảng cách xử trí rất có thể cầm lại cho những tháng sau nhưng không ảnh hưởng cho tới công dụng mon trước.Sử dụng VBA ẩn/hiện nay những vùng: Dữ liệu chnóng công theo giờ đồng hồ vào-ra (cột D mang lại cột BM), Dữ liệu giải pháp xử lý tính giờ làm việc (Cột BN cho cột CR) để triển khai gọn gàng bảng giải pháp xử lý. khi bắt buộc thao tác làm việc cùng với bảng nào thì ta mở ẩn vùng bảng kia ra. bởi vậy vẫn gọn đôi mắt rộng. Vì mục tiêu sau cùng là hiệu quả tính công mà lại thôi.

Công vấn đề chnóng công, tính lương tương đối tinh vi và đa dạng mẫu mã. Để xem thêm những kiến thức và kỹ năng liên quan tới các bước này, Học Excel Online xin reviews cùng với các bạn khóa huấn luyện và đào tạo “Ứng dụng Excel VBA vào công việc Tiền lương – nhân sự”. Học xong xuôi khóa đào tạo này chúng ta cũng có thể thay vững được quy trình chnóng công, tính lương trong doanh nghiệp, từ bỏ tay tạo ra 1 phần mềm quản lý lao đụng, chnóng công tính lương trên Excel. Chi tiết xem tại: