Kỹ thuật xử lý dữ liệu máy chấm công bằng excel – Học Excel Online

• Việc người lao động “quên” không chấm, khiến cho máy đưa ra kết quả sai
• Việc sử dụng kết quả chấm công từ máy vào việc tính lương lại không thể làm được ngay, mà cần phải sàng lọc lại.
• Mỗi phần mềm máy chấm công lại một kiểu, gây khó cho người sử dụng

Bài viết này Học Excel Online sẽ hướng dẫn các bạn kỹ năng để có thể xây dựng 1 bảng excel với chức năng sàng lọc thông tin từ máy chấm công để cho ra kết quả như ý muốn.

1. Chuẩn bị

– File excel kết xuất dữ liệu từ máy chấm công

– File excel mới (.xlsx hoặc .xls là được. Ngoài ra nếu muốn nâng cao thêm VBA có thể dùng file .xlsm)

Ví dụ với bảng dữ liệu kết xuất từ máy chấm công ra như trên

2. Thực hiện

2.1. Bước 1: Khảo sát, đánh giá và phân tích dữ liệu nguồn (bảng dữ liệu kết xuất từ máy chấm công ra excel)

Dù đã có ứng dụng, nhưng kỹ năng và kiến thức Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa ? Hãy để tôi giúp bạn, ĐK khóa học Excel :

Đây là bước rất quan trọng, Việc nghiên cứu và phân tích bảng dữ liệu nguồn sẽ giúp tất cả chúng ta có giải pháp xử lý tương thích và đúng chuẩn .

Xem thêm: Hướng dẫn cách tạo bảng chấm công bằng Excel cực kỳ chi tiết

Để thực thi việc làm này, tất cả chúng ta cần nắm được những nguyên tắc sau :

• Mục đích 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 mục đích, kết quả 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 hoàn toàn có thể thấy, để giám sát được, cần phải kiểm soát và điều chỉnh lại cấu trúc bảng dữ liệu sao cho đúng những tiêu chuẩn .
Việc đo lường và thống kê trên nhiều điều kiện kèm theo, nên hàm sẽ sử dụng để giám sát 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 thao tác, mà sẽ đưa hình mẫu và gợi ý những cách làm để những bạn hoàn toàn có thể tự làm được :

• Phần Tháng và Năm (dòng 2, 3): Đặt bên ngoài để tiện việc thay đổi
• 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 tiếp theo (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 cụ thể 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 cụ thể .

2.3. Lọc và lấy dữ liệu 

Tại bảng kết xuất, dùng tính năng Data / Filter để lọc bỏ dòng trống trong cột Mã NV, sau đó copy dữ liệu sang bảng xử lý. Sử dụng tính năng Paste Special / Value để dán dữ liệu bỏ lỡ dòng bị ẩn .

Bảng kết xuất sử dụng công dụ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 xử lý

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 xử lý ngày, thứ giống với phần trước.

=> Mục đích : Tính số giờ công thao tác theo từng ngày
Giờ công thao tác trong ngày = Giờ ra – Giờ vào
Giờ ra = ( Tổng số phút quy ước tại thời gian 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 gian chấm công vào = Số giờ * 60 + Số phút ) / 60

BN7 = ROUND ( ( ( HOUR ( SUMIFS ( $ D7 : $ BM7, USD D USD 4 : USD BM $ 4, BN $ 4, USD D USD 6 : USD BM $ 6,2 ) ) * 60 + MINUTE ( SUMIFS ( $ D7 : $ BM7, USD D USD 4 : USD BM $ 4, BN $ 4, USD D USD 6 : USD BM $ 6,2 ) ) ) – ( HOUR ( SUMIFS ( $ D7 : $ BM7, USD D USD 4 : USD BM $ 4, BN $ 4, USD D USD 6 : USD BM $ 6,1 ) ) * 60 + MINUTE ( SUMIFS ( $ D7 : $ BM7, USD D USD 4 : USD BM $ 4, BN $ 4, USD D USD 6 : USD BM $ 6,1 ) ) ) ) / 60,2 )

Bạn hoàn toàn có thể filldown, fillright công thức từ BN7 cho những ô 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 : Quên chấm công. Việc này thường xảy ra

Xem thêm : lớp học VBA ở Thành Phố Hà Nội

2.5. Xác định kết quả chấm công

Ví dụ tất cả chúng ta cần 1 số tác dụng chấm công như sau :

Ta hoàn toàn có thể thấy :

• Ngày công đủ 8h tính dựa trên vùng đã xác định cụ thể số giờ công, với số giờ làm >=8 (mục 2.4)

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

• Quên chấm công tính dựa trên vùng đã xác định số giờ công, với số giờ <0 (mục 2.4)

CT7 = COUNTIFS ( BN7 : CR7, ” < “ và 0 )

• Tổng số ngày công = Đủ 8h + Quên chấm công. Vì quên 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 USD 5 : CR USD 5, ” CN ” ) – COUNTIF ( BN $ 4 : CR USD 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, ” > ” và ” 8 : 05 ″, $ D7 : $ BM7, ” < “ và ” 8 : 10 ”, USD D USD 6 : USD BM $ 6,1 ) Muộn 10 ′ => CX7 = COUNTIFS ( $ D7 : $ BM7, ” > ” và ” 8 : 10 ″, $ D7 : $ BM7, ” < “ và ” 8 : 15 ”, USD D USD 6 : USD BM $ 6,1 ) Muộn trên 15 ′ => CY7 = COUNTIFS ( $ D7 : $ BM7, ” > ” và ” 8 : 15 ″, USD D USD 6 : USD BM $ 6,1 )

=> Bạn hoàn toàn có thể filldown công thức xuống cho những mã NV tiếp theo

Kết quả 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 bảng xử lý

Mình sẽ gợi ý một vài kỹ thuật giúp tối ưu hóa bảng xử lý : ( Ưu tiên cho những bạn biết sử dụng VBA, nếu không biết VBA thì làm thủ công bằng tay )

• Sử dụng VBA để lưu thông tin xử lý và kết quả tính được sang 1 Sheet riêng để lưu trữ kết quả. Bảng xử lý có thể làm tiếp cho các tháng sau mà không ảnh hưởng tới kết quả tháng trước.
• Sử dụng VBA ẩn/hiện các vùng: Dữ liệu chấm công theo giờ vào-ra (cột D đến cột BM), Dữ liệu xử lý tính giờ làm việc (Cột BN đến cột CR) để làm gọn bảng xử lý. Khi cần làm việc với bảng nào thì ta mở ẩn vùng bảng đó ra. Như vậy sẽ gọn mắt hơn. Vì mục tiêu cuối cùng là kết quả tính công mà thôi.

VBA giúp ích rất nhiều trong việc làm : giúp bạn tăng cường trong quy trình xử lý trên Excel, giúp lập những báo cáo giải trình tự động hóa, tinh chỉnh và điều khiển những chương trình khác như Word, Power Point, Outlook … giúp biến file Excel của bạn trở thành 1 ứng dụng chuyên nghiệp …
Để hoàn toàn có thể học VBA một cách không thiếu, có mạng lưới hệ thống, bạn hãy tham gia khóa học VBA101 – VBA cơ bản dành cho người mới khởi đầu của hệ thống Học Excel Online. Hiện nay mạng lưới hệ thống đang có nhiều khuyến mại khi bạn ĐK khóa học này. Chi tiết xem tại :

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