Chào bạn đọc, chắc hẳn xử lý dữ liệu từ máy chấm công đang là chủ đề được bạn quan tâm. Biết được vấn đề đó nên hôm nay chúng mình sẽ đưa đến các bạn bài viết xử lý dữ liệu từ máy chấm công mong có thể xử lý được vấn đề của bạn
1. Công cụ xử lý dữ liệu từ máy chấm công
Trước khi đi tới công việc chuẩn bị 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.
2. Xử lý dữ liệu từ máy chấm công
2.1. Bước 1: Khảo sát, đánh giá và đo đạt dữ liệu nguồn (bảng dữ liệu kết xuất từ máy chấm công ra excel)
Đây chính là bước rất quan trọng, Việc đo đạt bảng dữ liệu nguồn sẽ giúp con người có phương án giải quyết phù hợp và chính xác.
Để làm việc này, con người cần nắm được các nguyên tắc sau:
- Kết quả công việc: Tính công làm việc theo thời gian
- Đối tượng liên quan: Mã nhân viên, Ngày trong tháng, Giờ vào, Giờ ra
- Tiêu chí đánh giá (dựa trên kết quả, mục đích cần đạt được để đặt ra tiêu chí cho các đối tượng liên quan)
- Mã Nhân viên: Liên tục theo dòng, không được để dòng trống
- Ngày trong tháng: Bao gồm xác định ngày trong tháng, thứ trong tuần, phân biệt ngày nghỉ lễ, nghỉ bù
- Giờ vào, giờ ra: Các ngày làm việc đều phải có giờ vào, giờ ra. Khi tính công sẽ dựa trên việc đánh giá giờ vào, giờ ra để tính ra các loại công.
Ta có thể thấy, để tính toán được, luôn phải điều chỉnh lại cấu trúc bảng dữ liệu sao cho đúng các tiêu chí.
Việc tính toán trên nhiều điều kiện, nên hàm sẽ sử dụng để tính toán là SUMIFS, COUNTIFS và hàm IF
2.2. Bước 2: Cấu trúc lại bảng dữ liệu
Để rút gọn bài viết, mình sẽ không nêu từng thực hàng các bước, mà sẽ đưa hình mẫu và gợi ý các cách làm để các bạn sẽ tự làm được:
- Phần Tháng và Năm (dòng 2, 3): Đặt bên ngoài để tiện việc chỉnh sửa
- Ngày trong tháng (dòng 4): Dùng hàm Date, bắt đầu với ngày 26 tháng trước đó (vì bảng nguyên liệu kết xuất tính từ ngày 26 nên ta sẽ giữ nguyên yêu cầu này. Tính công từ ngày 26 tháng trước đến ngày 25 tháng sau). Riêng 2 ngày cuối có thể sang tháng sau đây (trường hợp tháng có 28-29 ngày) nên có thể xử lý để bỏ trống nếu sang tháng sau, tức là ngày 27 tháng này trở đi là đã bước sang 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, để tập hợp theo mã NV chính xác hơn.
- Thứ trong tuần (dòng 5): Dùng hàm Choose kết hợp với hàm Weekday để xác định. Kết hợp tính năng Conditional Formatting để tô màu phân biệt ngày CN với ngày thường.
- Giờ vào, giờ ra (dòng 6): có thể quy ước số 1 là giờ vào, số 2 là giờ ra.
- Lưu ý: 1 ngày có 2 giờ, nên 2 cột sẽ tính cho 1 ngày. Vì vậy khi xử lý ngày ở dòng 4 cần chú ý nội dung này.
Tất cả những nội dung trên, mình đã hướng dẫn chi tiết trong bài: Tạo bảng chấm công trên excel. Vui lòng xem lại bài này để được hướng dẫn chi tiết.
2.3. Lọc và lấy dữ liệu
Tại bảng kết xuất, dùng chức năng Data/Filter để lọc bỏ dòng trống trong cột Mã NV, sau đó copy dữ liệu sang bảng giải quyết. Sử dụng công dụng Paste Special/Value để dán dữ liệu bỏ qua dòng bị ẩn.
Bảng kết xuất sử dụng chức năng lọc (Data Filter)
Dữ liệu chấm công ở bảng kết xuất => Copy / Paste Special -> Value => Sang bảng giải quyết
2.4. Tính giờ công trong ngày
Tạo thêm 1 vùng, trong đó mỗi ngày chỉ tương ứng 1 cột. Cách giải quyết ngày, thứ giống với phần trước.
=> Mục đích: Tính số giờ công thực hiện công 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ố giờ * 60 + Số phút)/60
Giờ vào = (Tổng số phú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)
Bạn có thể filldown, fillright công thức từ BN7 cho các ô khác.
* Lưu ý:
Có thể có số âm. Số âm là trường hợp Chỉ có giờ vào mà không có giờ ra => Lý do: bỏ xót chấm công. Việc này thường xuất hiện
2.5. Đề ra mục đích chấm công
Ví dụ chúng ta cần 1 số mục đích chấm công như sau:
Ta có thể thấy:
- Ngày công đủ 8h tính dựa trên vùng đã xác định rõ ràng số giờ công, với số giờ làm >=8 (mục 2.4)
CS7 = COUNTIF(BN7:CR7,”>=”&8)
- Quên chấm công tính dựa trên vùng đã đề ra số giờ công, với số giờ <0 (mục 2.4)
CT7 = COUNTIFS(BN7:CR7,”<“&0)
- Tổng số ngày công = Đủ 8h + bỏ xót chấm công. Vì bỏ xót chấm công có thể vẫn được tính là 1 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 chấm công sẽ coi là nửa ngày công.
CU7 = CS7+CT7 hoặc CU7 = CS7+(CT7/2)
- Ngày nghỉ = (Tổng số ngày có công = 0) – (Số ngày nghỉ tuần = chủ nhật) Trường hợp ngày nghỉ tuần là chủ nhật. Nếu nghỉ cả T7, CN thì sẽ trừ thêm số ngày thứ 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 cứ vào giờ vào làm, so sánh với chỉ tiêu 8h (tuỳ quy ước giờ vào làm của đơn vị)
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)
=> Bạn có thể filldown công thức xuống cho các mã NV tiếp theo
Mục đích của mục 2.5 là kết quả cuối cùng, có thể sử dụng để tính lương cho NV
3. Tối ưu xử lý dữ liệu từ máy chấm công
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 liên quan cho tháng trước.
– Ẩn/hiện dữ liệu bằng VBA: Với công dụ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 giải quyết, tránh rối mắt
Vậy nên việc biết thêm VBA cũng vô cùng trọng yếu khi ta cần làm những thao tác phức tạp về sau này.
Tổng kết
Trên đây là cách xử lý dữ liệu từ máy chấm công. Hi vọng bài viết này sẽ giúp ích được bạn. Cảm ơn các bạn đã theo dõi!
Nguồn: Tổng hợp