Nhiều công ty, đơn vị hiện nay sử dụng máy chấm công để quản lý thời gian làm việc của nhân viên. Tuy nhiên, dữ liệu từ máy chấm công không phải lúc nào cũng đáp ứng ngay nhu cầu theo dõi. Bài viết này hướng dẫn cách theo dõi đi muộn từ dữ liệu máy chấm công bằng Excel, giúp bộ phận hành chính nhân sự dễ dàng quản lý và thống kê.
Dữ liệu chấm công được trích xuất từ máy chấm công và lưu vào sheet “Data” trong Excel, bao gồm thông tin về mã nhân viên (từ NV01 đến NV10), ngày giờ chấm công. Dữ liệu này sẽ được sử dụng để theo dõi số lần đi muộn của từng nhân viên.
Dữ liệu máy chấm công
Mục tiêu là tạo ra bảng theo dõi đi muộn (sheet “BCC”) như hình dưới đây:
Mẫu bảng theo dõi đi muộn
Các Bước Thực Hiện
Bước 1: Đặt Tên Vùng Dữ Liệu
Đầu tiên, đặt tên cho các cột dữ liệu trong sheet “Data” để thuận tiện cho việc viết công thức. Sử dụng Name Box hoặc Define Name trong thẻ Formulas để đặt tên như sau:
- A2:A213 là
cot_ID - B2:B213 là
cot_Ngay - C2:C213 là
cot_GioVao
Bước 2: Thiết Lập Bảng Chấm Công Đi Muộn
Tại sheet “BCC”, thiết lập bảng chấm công đi muộn, bao gồm Tháng (B2), Năm (B3) và Mốc thời gian tính đi muộn (B4).
Dòng thể hiện các ngày trong tháng được tạo bằng cách sử dụng hàm DATE.
- Ngày đầu tháng (B6):
=DATE(B3,B2,1) - Những ngày tiếp theo: Cộng thêm 1 vào ngày trước đó.
- Ngày cuối tháng: Xử lý trường hợp chuyển sang tháng sau.
Bước 3: Xây Dựng Công Thức Tính Đi Muộn
Sử dụng hàm COUNTIFS để đếm số lần đi muộn của từng nhân viên trong mỗi ngày. Công thức tại ô B7 (nhân viên NV01, ngày 1) như sau:
B7=COUNTIFS(cot_ID,$A7,cot_Ngay,B$6,cot_GioVao,”>”&$B$4)
Công thức này đếm số lần mã nhân viên trong cot_ID trùng với mã nhân viên ở cột A7, ngày trong cot_Ngay trùng với ngày ở hàng 6, và giờ vào trong cot_GioVao lớn hơn mốc thời gian đi muộn ở ô B4.
Áp dụng công thức này cho các ô còn lại bằng cách kéo Fill Right và Fill Down.
Kết quả bảng theo dõi đi muộn ban đầu
Bước 4: Kiểm Tra Và Sửa Lỗi Dữ Liệu
Kiểm tra định dạng dữ liệu của cột Ngày và Giờ Vào trong sheet “Data” bằng hàm ISNUMBER. Nếu kết quả là FALSE, dữ liệu không phải dạng số và cần được xử lý.
Kiểm tra dữ liệu chấm công
Sử dụng hàm VALUE kết hợp với TEXT để chuyển đổi dữ liệu sang dạng số:
- Dữ liệu ngày (cột B):
=VALUE(TEXT(B2,"dd/mm/yyyy")) - Dữ liệu giờ (cột C):
=VALUE(TEXT(C2,"hh:mm:ss"))
Chuyển dữ liệu về dạng thời gian
Sao chép kết quả và dán giá trị (Paste Value) vào cột B và C. Định dạng lại dữ liệu cột B là “dd/mm/yyyy” và cột C là “hh:mm:ss”.
Copy paste kết quả xử lý thời gian
Bước 5: Kiểm Tra Kết Quả
Quay lại sheet “BCC”, kết quả bảng theo dõi đi muộn sẽ tự động cập nhật.
Discussion about this post