Nội Dung Bài Viết

Máy chấm công, tưởng chừng là trợ thủ đắc lực cho bộ phận nhân sự, nhưng thực tế lại gây ra không ít khó khăn và phiền toái:

Nhân viên “quên” chấm công dẫn đến dữ liệu sai lệch.
Kết quả chấm công từ máy cần phải sàng lọc trước khi tính lương.
Mỗi phần mềm máy chấm công có định dạng khác nhau, gây khó khăn cho người sử dụng.

Bài viết này sẽ hướng dẫn bạn cách xây dựng bảng Excel để sàng lọc thông tin từ máy chấm công, cho ra kết quả chính xác và dễ dàng tính lương.

Xây Dựng Bảng Chấm Công Trong Excel

Chuẩn Bị

File Excel chứa dữ liệu kết xuất từ máy chấm công.
Một file Excel mới (.xlsx, .xls hoặc .xlsm nếu muốn sử dụng VBA).

Dữ liệu chấm công kết xuất từ máy chấm công

Các Bước Thực Hiện

Bước 1: Phân Tích Dữ Liệu Nguồn

Kỹ năng Excel vẫn rất quan trọng đối với kế toán, ngay cả khi đã có phần mềm hỗ trợ. Việc phân tích dữ liệu nguồn giúp chúng ta có phương án xử lý phù hợp.

Xem thêm: Xóa Định Dạng Có Điều Kiện (Conditional Formatting) trong Excel

Khóa học Excel cơ bản

Khóa học VBA cơ bản

Khóa học Excel cơ bản

Để phân tích dữ liệu, cần xác định:

Mục đích: Tính công làm việc theo thời gian.
Đối tượng: Mã nhân viên, ngày trong tháng, giờ vào, giờ ra.
Tiêu chí:
- Mã nhân viên: Liên tục, không dòng trống.
- Ngày trong tháng: Xác định ngày, thứ, ngày nghỉ lễ, nghỉ bù.
- Giờ vào, giờ ra: Đầy đủ cho mỗi ngày làm việc.

Chúng ta sẽ điều chỉnh cấu trúc bảng dữ liệu theo các tiêu chí này. Hàm SUMIFS, COUNTIFS và IF sẽ được sử dụng để tính toán.

Bước 2: Cấu Trúc Lại Bảng Dữ Liệu

Cấu trúc bảng dữ liệu 1Cấu trúc bảng dữ liệu 2

Hướng dẫn cấu trúc:

Tháng, Năm (dòng 2, 3): Đặt bên ngoài để dễ thay đổi.
Ngày trong tháng (dòng 4): Dùng hàm DATE, bắt đầu từ ngày 26 tháng trước. Xử lý để bỏ trống nếu sang tháng sau.
Mã NV (cột A, B, C): Mỗi mã một dòng.
Thứ trong tuần (dòng 5): Dùng hàm CHOOSE và WEEKDAY. Sử dụng Conditional Formatting để phân biệt Chủ Nhật.
Giờ vào, giờ ra (dòng 6): Quy ước số 1 là giờ vào, số 2 là giờ ra. Lưu ý: 2 cột cho 1 ngày.

Bước 3: Lọc Và Lấy Dữ Liệu

Tại bảng kết xuất, dùng Data/Filter để lọc bỏ dòng trống trong cột Mã NV. Copy dữ liệu sang bảng xử lý bằng Paste Special/Value.

Lọc dữ liệu 1

Lọc dữ liệu 2

Bước 4: Tính Giờ Công Trong Ngày

Tính giờ công

Tạo vùng mới, mỗi ngày một cột. Tính số giờ công làm việc theo từng ngày:

Giờ công = Giờ ra – Giờ vào
Giờ ra = (Số giờ * 60 + Số phút) / 60
Giờ vào = (Số giờ * 60 + Số phút) / 60

Xem thêm: Hàm VLOOKUP trong Excel: Hướng dẫn chi tiết từ cơ bản đến nâng cao

Sử dụng công thức SUMIFS kết hợp HOUR và MINUTE để tính toán. Ví dụ:

=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)

Lưu ý: Có thể xuất hiện số âm nếu quên chấm công ra.

Bước 5: Xác Định Kết Quả Chấm Công

Kết quả chấm công

Một số kết quả chấm công cần xác định:

Ngày công đủ 8h: Sử dụng COUNTIF với điều kiện >= 8.
Quên chấm công: Sử dụng COUNTIFS với điều kiện < 0.
Tổng số ngày công: Đủ 8h + Quên chấm công (hoặc Đủ 8h + (Quên chấm công / 2) nếu tính nửa ngày).
Ngày nghỉ: Tổng số ngày có công = 0 – Số ngày Chủ Nhật (và Thứ Bảy nếu nghỉ cả hai ngày). Sử dụng COUNTIF và IF.
Đi muộn: So sánh giờ vào với giờ quy định (ví dụ 8h). Sử dụng COUNTIFS.

Tối Ưu Bảng Xử Lý (Với VBA)

Sử dụng VBA để lưu kết quả sang sheet riêng.
Sử dụng VBA để ẩn/hiện các vùng dữ liệu, giúp bảng xử lý gọn gàng hơn.

Kết quả từ bước 5 là cơ sở để tính lương cho nhân viên. Việc sử dụng Excel kết hợp với VBA giúp tự động hóa và tối ưu quy trình xử lý dữ liệu chấm công.

Đánh Giá Bài Viết