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

Nhã Linh
Nhã Linh
Apr 19 2021

Để 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 2022 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é.

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

VỪA BẬN VỪA LƯỜI VẪN GIỎI EXCEL NHỜ KHÓA HỌC NÀY

EXG01: Tuyệt đỉnh Excel - Trở thành bậc thầy Excel

G-LEARNING

32426 học viên

4.8 (110 đánh giá)

499,000đ

799,000đ

Nhập môn Excel cùng Gitiho

G-LEARNING

10745 học viên

4.63 (19 đánh giá)

Miễn phí

499,000đ

EXG05 - Kỹ năng sử dụng công thức và hàm A-Z trong Excel

G-LEARNING

678 học viên

4.8 (5 đánh giá)

499,000đ

799,000đ

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

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

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:

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

Xem thêm: Hướng dẫn chi tiết cách sử dụng các hàm And, hàm Or, hàm XOR và hàm Not trong Excel

 

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

Sau khi chúng ta đã hoàn thành xác định năm nhuận trong bảng chấm công 2022, 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 2022 để 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:

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

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 2022 hoàn chỉnh

Hãy cùng tìm hiểu cách lập một bảng chấm công 2022 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 2022

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é.

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

Sau đó, chúng ta sẽ điền các ngày trong tháng vào bảng chấm công 2022 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". 

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

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

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

Xem thêm: Ứng dụng hàm DATE sử dụng trong kế toán tiền lương, nhân sự

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

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

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:

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

Như các bạn thấy trong bảng chấm công 2022 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.

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

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 2022 như trong hình dưới đây.

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

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.

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

Xem thêm: Hướng dẫn phân loại dữ liệu bằng màu, mô phỏng chức năng của conditional formatting

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

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

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 2022 cũng như bảng lương trên Excel cuối cùng như sau:

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

Cách chấm công trong bảng chấm công 2022 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:

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

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.

Chia sẻ bảng chấm công 2022 trên Excel dành cho kế toán tiền lươ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.

Xem thêm: Hướng dẫn cách dùng hàm COUNTIF trong công việc kế toán trên 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 2022 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!

 

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội