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 trị nhân sự hoàn toàn có thể trấn áp được hoạt động giải trí thao tác của những công nhân viên trong công ty nhằm mục đích 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 việc làm tương đối phiền phức bởi có hàng tá những hoạt động giải trí gây ra sai số như nhân viên cấp dưới quên chấm công khiến cho việc đưa sai hiệu quả khi sàng lọc từ máy chấm công sang quy trình tính lương, …Trong bài viết này, Gitiho. com sẽ giúp những bạn phương pháp lọc thông tin từ máy chấm công qua bảng tính Excel để thuận tiện tính lương, thưởng .

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

Trước khi đi tới quy trình sàng lọc, ta cần chuẩn bị sẵn sàng 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 .

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

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 trọng ngay từ bước này thì khi gặp sai sót ta vẫn hoàn toàn có thể đưa ra những xử lý đúng mực cũng như tương thích để tránh việc làm lại từ đầu .Một số nguyên tắc khi nghiên cứu và 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, tất cả chúng ta nên sử dụng những hàm ship hàng giám sát nhiều điều kiện kèm theo 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 họa

– 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 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 cấp dưới rồi copy qua bảng xử lý. Tiếp theo ta sử dụng Paste Special / Value để dán dữ liệu bỏ lỡ 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 thao tá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

Xem thêm: Tìm việc Làm Giám đốc Đầu tư và Phát triển Dự án Tuyển Dụng 19/04/2023 | https://thomaygiat.com

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 hoàn toàn có thể filldown công thức này cho những ô 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 lập 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 = Đủ 8 h thao tá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 Open 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ị chức năng ( như ví dụ này là thao tá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 hoàn toàn có thể filldown công thức cho những 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 ít về VBA, nhưng nếu không biết thì vẫn hoàn toàn có thể dùng được nhưng sẽ mất thời hạn hơn .- Sử dung VBA để sao lưu lại những thông tin cần được xử lý và tác dụng sang hẳn một sheet riêng không liên quan gì đến nhau nhằm mục đích mục tiêu lưu lại hiệu quả. Với cách xử lý này thì ta hoàn toàn có thể liên tục bảng chấm công từ tháng sau mà không gây ảnh hưởng tác độ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

Xem thêm: Tìm việc Làm Giám đốc Đầu tư và Phát triển Dự án Tuyển Dụng 19/04/2023 | https://thomaygiat.com

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