Chia sẻ bảng chấm công 2023 trên Excel dành cho kế toán tiền lương

Để lập một bảng chấm công và tính lương hoàn chỉnh trên Excel, kế toán tiền lương cần thực hiện những thao tác nào? Hôm nay, Gitiho sẽ hướng dẫn các bạn lập bảng chấm công 2023 từ A đến Z. Ngoài ra, chúng mình cũng sẽ chia sẻ đến các bạn file bảng chấm công bảng lương Excel mẫu ở cuối bài viết. Hãy tải file Excel mẫu của chúng mình để cùng thực hành nhé.

Cách xác định thời gian trong bảng chấm công 2023

Cách xác định năm nhuận trong bảng chấm công 2023

Vì năm nhuận có nhiều hơn 1 ngày so với các năm khác nên trong kế toán tiền lương, việc xác định đúng năm nhuận là rất cần thiết để tính chính xác ngày lương trên bảng chấm công bảng lương Excel.

Lưu ý rằng năm nhuận là năm chia hết cho 400 hoặc năm không chia hết cho 100 nhưng chia hết cho 4. Như vậy, để xác định một năm có phải năm nhuận hay không, chúng ta sử dụng công thức hàm IF trong Excel như sau:

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,100)<>0,MOD(A1,4)=0)),"Năm nhuận","")

Trong đó:

Hàm MOD được dùng để xác định phép chia năm cho 400 không dư hoặc kết hợp với hàm AND để xác định phép chia năm cho 100 có dư và phép chia cho 4 không dư. Đặt công thức hàm MOD vào trong hàm OR, ta nhập xong điều kiện của để xác định năm nhuận. Như vậy, hàm IF trong Excel sẽ hiểu nếu giá trị trong ô thỏa mãn 1 trong 2 đối số của hàm OR, trả về kết quả "Năm nhuận". Nếu giá trị trong ô không thỏa mãn bất kì điều kiện nào, kết quả là rỗng.

Ta có hình ảnh ví dụ cho công thức hàm như sau:

Cách xác định số ngày của tháng 2 trong bảng chấm công 2023

Sau khi chúng ta đã hoàn thành xác định năm nhuận trong bảng chấm công 2023, chúng ta cần xử lí số ngày tháng 2 trong bảng chấm công bảng lương Excel. Lí do là vì số ngày của tháng 2 nhiều hơn vào năm nhuận so với các năm khác.

Chúng ta sẽ thực hiện xác định tháng 2 có phải tháng của năm nhuận không bằng công thức sau:

=IF(OR(MOD(nam,400)=0,AND(MOD(nam,100<>0,MOD(nam,4)=0)),TRUE,FALSE)

Trong đó: 

"nam" là tên chúng ta đặt tên cho ô C3 - ô chứa giá trị năm.
 

Tương tự như ở phần trên, chúng ta xác định năm có chia hết cho 400 hoặc đồng thời chia hết cho 4 và không chia hết cho 100. Nếu năm đáp ứng 1 trong 2 điều kiện, hàm IF trong Excel trả về kết quả TRUE. Ngược lại, chúng ta nhận được FALSE nếu năm không đáp ứng bất kì điều kiện nào.

Dưới đây là một ví dụ về bảng chấm công 2023 để các bạn thấy rõ hơn cách sử dụng hàm IF trong Excel trong trường hợp này:

Xem thêm: Hướ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ế

Cách thiết lập bảng chấm công 2023 hoàn chỉnh

Hãy cùng tìm hiểu cách lập một bảng chấm công 2023 từ A đến Z. Việc đầu tiên khi mở Excel là tạo 12 sheet tương ứng với 12 tháng trong năm. Ngoài ra, các bạn hãy tạo thêm 1 sheet mới để thực hiện các thao tác cài đặt cho file nhé. Trong phần này, mình cũng sẽ hướng dẫn làm bảng lương trên Excel.

Cách sử dụng các hàm thời gian trong bảng chấm công 2023

Cách xác định ngày trong tháng bằng hàm DATE

Chúng ta sẽ thao tác lập bảng trên sheet đầu tiên tương ứng với tháng 1. Hãy nhập các thông tin sau vào trang tính Excel nhé.

Sau đó, chúng ta sẽ điền các ngày trong tháng vào bảng chấm công 2023 trên Excel. Đương nhiên chúng ta không cần nhập liệu thủ công từ số 1 đến 31. Thay vào đó, chúng ta sử dụng công thức hàm DATE trong ô D7 để xác định ngày như sau:

=DATE(nam,C3,1)

Trong đó:

• "nam" là giá trị năm trong ô C4 (đã đặt tên).
• C3 là giá trị tháng.
• 1 là giá trị ngày trong tháng.

Chúng ta chỉ dùng công thức trên cho ô D7 để hiển thị ngày 01/01/2021. Với ô E7 ngày 02/01/2021, chúng ta điền công thức:

=D7+1

Công thức này giúp Excel tự động hiểu rằng giá trị của ô là ngày tiếp theo của giá trị trong ô D7. Sau đó, chúng ta nhấn vào ô E7 và sử dụng tính năng FillDown cho các hàng bên phải để bảng hiển thị đầy đủ 31 ngày trong tháng kế toán tiền lương.

Để rút gọn định dạng ngày thay vì ngày/tháng/năm, chúng ta mở hộp thoại Format Cells > Custom > điền "dd". 

 

Như vậy chúng ta đã có bảng như sau:

Cách xác định thứ của ngày trong tháng bằng hàm WEEKDAY

Vậy là bảng chấm công 2023 trong kế toán tiền lương đã có bảng biểu diễn số liệu các ngày trong tháng rồi. Bây giờ, chúng ta sẽ xác định thứ trong tuần của các ngày này. Đầu tiên chúng ta thử công thức hàm WEEKDAY để xác định thứ của ngày 01/01/2021 ở một ô ngoài bảng:

=WEEKDAY(D7)

 

Kết quả Excel trả về là 6, nghĩa là ngày 01/01/2021 là ngày thứ 6. Điều này cho ta biết ngày 02/01/2021 sẽ rơi vào thứ 7, và tương tự với các ngày sau đó. Để không mất công nhập dữ liệu thủ công, ta sử dụng hàm CHOOSE với công thức như sau để tự động điền kết quả thứ theo định dạng "Thứ n":

=CHOOSE(WEEKDAY(D7),"Chủ Nhật","Thứ 2","Thứ 3","Thứ 4","Thứ 5","Thứ 6","Thứ 7")

Sau đó, ta sử dụng tính năng FillDown để tự động áp dụng công thức cho các ngày còn lại trong tháng. Kết quả thu được như sau:

Như các bạn thấy trong bảng chấm công 2023 trên, Excel đã trả về kết quả thứ của 31 ngày trong tháng. Tuy nhiên, chúng ta không thể nhìn thấy rõ giá trị. Do đó, hãy vào Format Cells > Alignment > Orientation > nhập 90. Như vậy, giá trị trong ô sẽ được hiển thị dọc chiều dài của ô. Chúng ta chỉ cần kéo dài hàng số 8 ra thay vì kéo rộng các cột D-AH.

 

Sau khi nhấn OK, ta sẽ thấy các giá trị thứ trong tuần được xoay dọc theo chiều dài của ô. Và chúng ta có bảng chấm công 2023 như trong hình dưới đây.

Chúng ta đã có đầy đủ thông tin về thứ, ngày, tháng rồi. Điều cần làm tiếp theo là xác định các ngày chủ nhật và hiển thị chúng trong bảng lương của các nhân viên. Vì chủ nhật là ngày nghỉ nên kế toán tiền lương cần đánh dấu nổi bật trong bảng.

Ở bước này, chúng ta sẽ nhập mã và tên các nhân viên vào bảng chấm công bảng lương trên Excel. Trong ví dụ dưới đây, chúng mình điền tên 5 nhân viên. Sau đó, hãy chọn tất cả các ô hàng nhân viên tương ứng với 31 cột ngày trong tháng, tức các ô trong phạm vi D9:AH13. Chúng ta sẽ áp dụng định dạng theo điều kiện để đánh dấu các ô là Chủ nhật bằng các bước sau đây:

Mở hộp thoại Conditional Formating > Use a formula to determine which cells to format > nhập =WEEKDAY(D$7)=1. 

Mở hộp thoại Format... > Fill > Chọn màu xanh.

Vậy là chúng ta đã đánh dấu được các ngày Chủ nhật trong bảng chấm công 2023.
 

Chúng ta điền thêm các cột xác định Đủ công, Nửa công, Phép, Thai sản, Ốm và các cột tương tự làm cơ sở chấm công và tính lương.

Sau khi đã có đầy đủ các đề mục thông tin trong bảng, chúng ta định dạng bảng bằng các công cụ tô màu và kẻ viền bảng. Chúng mình có bảng chấm công 2023 cũng như bảng lương trên Excel cuối cùng như sau:

 

Cách chấm công trong bảng chấm công 2023 trên Excel

Chúng ta bắt đầu với việc tạo lập một bảng phụ ghi chú kí hiệu đánh dấu các điều kiện chấm công tương ứng với các điều kiện ta đã thêm ở bảng chấm công. Ví dụ như sau:

Một cách khác là bạn có thể điền kí tự ngay trên các cột màu vàng trong bảng để tiện theo dõi. Bằng cách này, sau khi chấm công đầy đủ từ ngày 01 đến ngày 31/01/2021, kế toán tiền lương có thể tổng hợp số ngày đủ công, nửa công, phép,... của từng nhân viên bằng công thức hàm COUNTIF. Với các ngày đủ công của nhân viên Nguyễn Ngọc Anh, chúng ta điền vào ô AI9:

=COUNTIF($D$9:$AH$9,AI7)

Trong đó:

• $D$9:$AH$9 là vùng phạm vi chạy hàm COUNTIF đặt trong dấu tham chiếu tuyệt đối.
• A17 là ô chứa kí hiệu điền vào trong bảng chấm công.

Chúng ta áp dụng công thức tương tự cho các tiêu chí chấm công còn lại và việc chấm công các nhân viên khác để hoàn thành bảng tháng 1 trong bảng chấm công bảng lương Excel.

Đối với các tháng tiếp theo, bạn chỉ cần thực hiện các bước tương tự hoặc copy paste và định dạng bảng để nhanh chóng thiết lập được bảng chấm công. Nếu bạn không muốn phải thiết lập một file Excel từ đầu thì hãy tham khảo file mẫu của chúng mình phía trên nhé.

Tổng kết

Vậy là chúng ta đã tìm hiểu quá trình thiết lập bảng chấm công 2023 cho kế toán tiền lương từ A đến Z rồi. Các bạn hãy tải file mẫu đính kèm bài viết để thực hành với chúng mình nhé. Đừng quên đọc thêm các bài viết hữu ích khác về chủ đề Excel và tham gia khóa học Tuyệt đỉnh Excel để nâng cao kĩ năng tin học văn phòng nhé.

Gitiho chúc các bạn thành công!