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 các 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á các 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 tác dụng 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 các 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ụ thiết yếu

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 .

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.

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

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 các ô khác .* Lưu Ý : Nếu như tác dụng 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 các cột tiếp theo .

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à hiệu quả 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 tác dụng. 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 tính năng ẩn / hiện Dữ liệu chấm công giờ vào-ra, Dữ liệu giờ thao tác sẽ giúp tất cả chúng ta làm gọn bảng xử lý, tránh rối mắtVậ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 hoàn toàn có thể tìm hiểu và khám phá qua những nội dung về Excel trên trang Gitiho. com

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong ExcelHƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN : AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ

Source: https://vh2.com.vn
Category : Tin Học