Cách sử dụng Excel để xử lý dữ liệu cho máy chấm công

Máy chấm công là công cụ đã quá quen thuộc tại mỗi cơ quan công ty. Nó giúp người quản lý nhân sự có thể kiểm soát được hoạt động làm việc của các công nhân viên trong công ty nhằm tính lương, thưởng hoặc phạt. Mang lại tiện ích cho doanh nghiệp, thế nhưng việc xử lý máy chấm công lại là một công việc tương đối phiền toái bởi có hàng tá các hoạt động gây ra sai số như nhân viên quên chấm công khiến cho việc đưa sai kết quả khi sàng lọc từ máy chấm công sang quá trình tính lương,...

Trong bài viết này, Gitiho.com sẽ giúp các bạn cách thức lọc thông tin từ máy chấm công qua bảng tính Excel để dễ dàng tính lương, thưởng.

Những công cụ cần thiết

Trước khi đi tới công đoạn sàng lọc, ta cần chuẩn bị trước một file Excel xuất dự liệu từ máy chấm công và một file Excel trống có đuôi .xlsx hoặc xls hoặc cả đuôi .xlsm cho những người đã thành thạo với VBA.

Thực hiện quá trình sàng lọc

Bước 1: Đánh giá sơ bộ và phân tích dữ liệu cho máy chấm công

Đây là một bước cực kỳ quan trọng khi ta kết xuất dữ liệu từ máy chấm công bởi nếu làm cẩn thận ngay từ bước này thì khi gặp sai sót ta vẫn có thể đưa ra những xử lý chính xác cũng như phù hợp để tránh việc làm lại từ đầu.

Một số nguyên tắc khi phân tích dữ liệu từ máy chấm công:

- Mục đích của công việc (Tính công làm việc theo thời gian)

- Đối tượng để tính toán công: Giờ vào làm, giờ ra khỏi cơ quan, mã nhân viên, ngày trong tháng (những đối tượng này sẽ cho ra kết quả đạt được để cho ra tiêu chí)

+ Giờ vào, ra: Khi tính công làm sẽ dựa vào đánh giá của giờ làm, giờ ra để tính ra các loại công.

+ Mã nhân viên: Liên tục theo dòng, không để dòng trống

+ Ngày trong tháng: Cần xác định rõ ngày trong tháng, ngày trong tuần. Bên cạnh đó là phân biệt những ngày nghỉ phép, nghỉ lễ,...

Với kiểu dữ liệu nhiều kiểu thế này, chúng ta nên sử dụng những hàm phục vụ tính toán nhiều điều kiện như IF, SUMIFS, COUNTIF

Bước 2: Cấu trúc hoá bảng dữ liệu

Ta sẽ sử dụng bảng dữ liệu dưới đây để làm ví dụ minh hoạ

- Phần thời gian Tháng, Năm ở dòng 2, 3  sẽ được đặt phía ngoài để tiện viêc thay đổi

- Ngày trong tháng tại dòng 4 ta sẽ dùng hàm DATE để tính từ ngày 26 trước đó (Bởi ngày 26 là ngày bảng nguyên liệu kết xuất. Vậy nên ta sẽ tính công từ ngày 26 tháng này đến 25 tháng sau). Trong trường hợp như tháng 2 chỉ có 28 hoặc 29 ngày thì 2 ngày cuối tháng có thể dịch được sang tháng tiếp theo nên có thể bỏ trống sang tháng tiếp theo. Hay dễ hiểu hơn thì ngày 27 của tháng này trở đi sẽ kết thúc để bước sang tháng tiếp theo của bảng chấm công.

- Mã nhân viên (cột A, B, C): Một mã tương ứng với một dòng giúp tập hợp các mã dễ dàng hơn

- Thứ trong tuần (dòng 5):  Để xác định phần này, ta kết hợp hàm CHOOSE với hàm WEEKDAY để xác định thứ trong tuần và sử dụng Conditional Formatting để bôi đỏ những ngày Chủ Nhật.

- Giờ vào, giờ ra (dòng 6): Ta có thể quy ước V (vào), R (ra) hoặc 1 là giờ vào, 2 là giờ ra

Chú ý: 1  ngày có 2 giờ, suy ra 2 cột tính cho 1 ngày. Vậy nên khi xử lý dòng 4 ta cần chú ý điều này.

Xem thêm: Cách chấm công ngày lễ trong bảng chấm công Excel

Bước 3: Sàng lọc và lấy dữ liệu

Đầu tiên ta dùng Data/Filter để lọc dòng trống trong cột Mã nhân viên rồi copy qua bảng xử lý. Tiếp theo ta sử dụng Paste Special/Value để dán dữ liệu bỏ qua dòng bị ẩn.

Kết quả khi sử dụng Data/Filter trên bảng kết xuất

Kết quả khi sử dụng Copy/Paste Special > Value để sang bảng xử lý

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

Tới bước này ta cần tạo thêm một vùng để tính giờ công làm việc trong một ngày, trong đó mỗi ngày tương ứng với 1 cột. 

Ta sẽ có công thức sau: Giờ làm việc trong ngày = Giờ làm việc - Giờ ra.

Giờ ra = (Tổng số phút tính từ khi chấm công = Số giờ x 60 + Số phút)/60

Giờ vào = (Tổng số phút từ khi chấm công = Số giờ x 60  + Số phút)/60

Suy ra, ta có công thức BN7

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)

Khi đã có công thức BN7, ta có thể filldown công thức này cho các ô khác.

Lưu ý: Nếu như kết quả cho ra số âm thì có nghĩa là người lao động quên chấm công bởi chỉ có giờ ra mà không có giờ vào.

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

Chẳng hạn ta có kết quả chấm công như sau
 

- Với ngày công đầy đủ 8 tiếng  tính dựa trên vùng đã xác định đủ giờ công với số lớn hơn hoặc bằng 8 thì ta sẽ có công thức sau.

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

- Công thức tính khi quên chấm công dựa trên vùng xác định số công với số giờ <0

CT7 = COUNTIFS(BN7:CR7,”<“&0)
- Tổng số ngày công = Quên chấm công + Đủ 8h làm việc.

- Nếu như quên chấm công mà quy ước thành nửa ngày công thì sẽ là: Tổng ngày công = Đủ 8h làm việc  + (Quên chấm công/2)

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

- Cách tính ngày nghỉ: (Tổng số ngày có công = 0) -(Số ngày nghỉ tuần = Chủ nhật) 

- Nếu nghỉ cả Thứ 7 nữa thì thêm số ngày xuất hiện của Thứ 7 trong tháng

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

- Cách tính công đi muộn: Dựa vào giờ vào làm và quy ước giờ làm của từng đơn vị (như ví dụ này là làm việc 8 tiếng/ngày)

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

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

Muộn trên 15′ => CY7 = COUNTIFS($D7:$BM7,”>”&”8:15″,$D$6:$BM$6,1)

Có công thức này bạn có thể filldown công thức cho các cột tiếp theo.

Xem thêm: 6 tiêu chí lựa chọn phần mềm chấm công cho doanh nghiệp

Tối ưu hoá bảng xử lý dữ liệu

Với việc tối ưu bảng xử lý này thì bạn phải biết một chút về VBA, nhưng nếu không biết thì vẫn có thể dùng được nhưng sẽ mất thời gian hơn.

- Sử dung VBA để sao lưu lại những thông tin cần được xử lý và kết quả sang hẳn một sheet riêng biệt nhằm mục đích lưu lại kết quả. Với cách xử lý này thì ta có thể tiếp tục bảng chấm công từ tháng sau mà không gây ảnh hưởng cho tháng trước.

- Ẩn/hiện dữ liệu bằng VBA: Với chức năng ẩn/hiện Dữ liệu chấm công giờ vào-ra, Dữ liệu giờ làm việc sẽ giúp chúng ta làm gọn bảng xử lý, tránh rối mắt

Vậy nên việc biết thêm VBA cũng vô cùng quan trọng khi ta cần làm những thao tác phức tạp về sau này.

Ngoài ra bạn đọc cũng có thể tìm hiểu qua những nội dung về Excel trên trang Gitiho.com