Hướng dẫn cách theo dõi đi làm muộn từ dữ liệu máy chấm công
Hiện nay nhiều công ty, đơn vị thường sử dụng máy chấm công để theo dõi thời gian đi làm, thời gian làm việc của nhân viên. Tuy nhiên không phải dữ liệu từ máy chấm công cũng đáp ứng được ngay nhu cầu theo dõi. Trong bài viết này chúng ta sẽ cùng Học Excel Online tìm hiểu cách theo dõi đi làm muộn từ dữ liệu máy chấm công. Cụ thể như sau:
Xem nhanh
Bài tập theo dõi đi muộn từ dữ liệu máy chấm công
Tại 1 đơn vị có dữ liệu trích xuất từ bảng chấm công (sheet Data):
Dữ liệu này tính cho 10 nhân viên có mã nhân viên từ NV01 đến NV10, gồm 212 dòng dữ liệu và 1 dòng tiêu đề.
Yêu cầu của người chấm công là theo dõi số lần đi làm muộn của từng người và điền vào bảng dưới đây (Sheet BCC):
Cách làm
Bước 1: Đặt tên các cột trong bảng dữ liệu chấm công
Để cho tiện theo dõi và việc viết công thức được dễ hơn, đầu tiên chúng ta sẽ tiến hành đặt tên cho các cột dữ liệu trong bảng dữ liệu chấm công.
Sử dụng Name Box hoặc công cụ Define Name trong thẻ Formulas để đặt tên cho các vùng dữ liệu tại sheet Data 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
Khi thiết lập bảng này, cần lưu ý các vị trí: Tháng (B2), Năm (B3), Mốc thời gian tính đi muộn (B4).
Dòng thể hiện các ngày trong bảng chấm công thực hiện như sau:
- Ngày đầu tháng: Dùng hàm Date lấy theo năm tại ô B3, tháng tại B2, ngày là ngày 01 => B6=Date(B3,B2,1)
- Những ngày tiếp theo: bằng ngày trước đó cộng thêm 1
- Ngày cuối tháng: biện luận trường hợp bắt đầu sang tháng sau. Nội dung này các bạn có thể xem thêm tại bài viết: Hướng dẫn cách tạo bảng chấm công trên Excel chi tiết nhất
Bước 4: Xây dựng công thức tính đi muộn
Việc theo dõi số lần đi muộn thực tế là đếm xem tại ngày nào thì nhân viên đó có giờ vào muộn hơn so với giờ quy định.
Như vậy chúng ta sử dụng hàm COUNTIFS để đếm theo nhiều điều kiện, trong đó:
- cot_ID tìm theo mã nhân viên tương ứng của BCC
- cot_Ngay tìm theo ngày tương ứng với dòng 6 trong BCC
- cot_GioVao tìm theo giá trị lớn hơn mốc thời gian
Tại ô B7 cho nhân viên 01, ngày 1 ta có công thức như sau:
B7=COUNTIFS(cot_ID,$A7,cot_Ngay,B$6,cot_GioVao,”>”&$B$4)
Để tìm hiểu cách cố định tọa độ trong các điều kiện của công thức trên, các bạn có thể tham khảo bài viết:
Hướng dẫn cách viết điều kiện hàm SUMIFS để đạt hiệu quả tốt nhất
Áp dụng công thức tại B7 cho các ngày còn lại (fillright) và các nhân viên còn lại (filldown), chúng ta có kết quả như sau:
Toàn bộ đều bằng 0. Như vậy chắc chắn là không đúng rồi. Ở đây có 1 lỗi mà đang ra chúng ta phải làm ngay từ đầu, đó là kiểm tra tính chính xác của dữ liệu trước khi tính toán, báo cáo. Cụ thể ở đây là kiểm tra dữ liệu về thời gian Ngày, giờ đã chính xác chưa, đã đúng loại dữ liệu thời gian chưa.
Bước 5: Kiểm tra và sửa lỗi dữ liệu
Quay trở lại bảng dữ liệu, kiểm tra 2 cột Date và InTime bằng hàm IsNumber xem dữ liệu tại đây có phải là dữ liệu dạng Số hay không
Kết quả chúng ta thấy đều là FALSE, tức là không phải dữ liệu dạng số. Chính vì vậy mà hàm COUNTIFS đã không ra kết quả đúng.
Để sửa lỗi dữ liệu này chúng ta làm như sau:
Sử dụng hàm Value kết hợp hàm Text để sửa lỗi dữ liệu cho từng cột
- Với dữ liệu dạng ngày (cột B, từ B2): =VALUE(TEXT(B2,”dd/mm/yyyy”))
- Với dữ liệu dạng giờ (cột C, từ C2): =VALUE(TEXT(C2,”hh:mm:ss”))
Tham khảo bài viết: Cách sửa định dạng thời gian bị lỗi về dạng chuẩn cả giờ phút giây
Sau khi có được kết quả, chúng ta sẽ tiến hành định dạng dữ liệu về chuẩn dạng thời gian:
Cuối cùng, để trả lại kết quả đúng vào các cột Date, InTime, chúng ta sẽ copy dữ liệu từ cột E, F; Sau đó Paste Value vào cột B, C
Khi paste value thì chỉ đảm bảo dữ liệu về đúng loại dữ liệu và giá trị, còn định dạng sẽ ở dạng mặc định sẵn trong ô.
- Định dạng lại dữ liệu tại cột B về dạng dd/mm/yyyy
- Định dạng lại dữ liệu tại cột C về dạng hh:mm:ss
Bây giờ quay trở lại bảng chấm công đi muộn và xem kết quả của công thức COUNTIFS tự động thay đổi nào:
Đây chính là kết quả mong muốn của chúng ta rồi. Các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2n71uKI
Xem thêm:
Hướng dẫn cách theo dõi số ngày phép còn lại trong năm trên Excel
Hướng dẫn cách quản lý công văn, hợp đồng trên Excel
Ngoài việc theo dõi đi muộn, công việc của hành chính-nhân sự còn phải theo dõi nhiều nội dung khác như: Công văn giấy tờ, hợp đồng lao động, quản lý cấp phát đồ dùng văn phòng phẩm, chấm công tính lương… Để nắm được những kiến thức và cách ứng dụng Excel trong công việc hành chính-nhân sự, mời bạn tham gia khóa học Hành chính nhân sự từ A-Z của Học Excel Online. Bật mí là bạn còn được tặng kèm phần mềm quản lý nhân sự khi tham gia khóa học này nhé. Chi tiết xem tại:
