Ứng dụng Excel trong công việc kế toán lương – Bài 1 (tiếp theo)

Phần 1: Excel với bảng chấm công

Bài 1: Lập bảng chấm công – cho bộ phận hành chính

3. Yêu cầu 2: Chấm công theo giờ bắt đầu và giờ kết thúc

Thông thường việc chấm công theo giờ bắt đầu và giờ kết thúc được thực hiện thông qua máy chấm công.

Tôi sẽ ví dụ với các bạn kết quả thu được từ một máy chấm công như sau:

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)

Đây là bước rất quan trọng, Việc phân tích bảng dữ liệu nguồn sẽ giúp chúng ta có phương án xử lý phù hợp và chính xác.

Để thực hiện công việc này, chúng ta cần nắm được các 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 có thể thấy, để tính toán được, cầ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

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 ý các cách làm để các bạ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 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.

Bước 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 xử lý. Sử dụng chức nă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 xử lý

Bước 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 làm 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: Quên chấm công. Việc này thường xảy ra