Để biết được cách tạo bảng chấm công trong excel, bạn cần phải hiểu rõ bố cục của nó cũng như cách sử dụng một số hàm cơ bản. Đây là việc mà các bộ phận quản lý nhân sự sẽ phải thực hiện trong một công ty.

Bảng chấm công là thành phần quan trọng và không thể thiếu để quản lý thời gian làm việc của các nhân viên, từ đó tính được số tiền lương chính xác nhất. Để biết cách thực hiện, mời các bạn cùng tìm hiểu qua bài viết dưới đây của jacquart-lowe.com.

Cấu trúc của bảng chấm công trong Excel

Đây là bước đầu tiên và cũng rất quan trọng để tạo nên một bảng chấm công hoàn chỉnh. Những yếu tố quan trọng của bảng sẽ bao gồm số lượng Sheet cần thiết, danh sách nhân viên, cách hoạt động của bảng chấm công,…

Thông thường, một bảng chấm công sẽ đảm bảo có đầy đủ những yếu tố sau:

• Có tất cả là 13 Sheet, mỗi Sheet đại diện cho 12 tháng và Sheet còn lại dùng để thống kê nhân sự
• Bảng Thống kê nhân sự sẽ gồm tên, ngày sinh, thông tin liên lạc, ngày gia nhập doanh nghiệp
• Mỗi nhân viên cần phải có một mã ký hiệu khác nhau, thường sẽ được đặt theo phong ban, tên viết tắt hoặc số thứ tự (VD: KD01, KDLTV). Lưu ý, mã ký hiệu này không được thay đổi trong 12 tháng.
• Ngày, tháng là yếu tố bắt buộc phải có trong bảng chấm công. Ngoài ra bạn cũng nên đánh dấu màu khác biệt cho những ngày lễ hoặc ngày cuối tuần không đi làm.
• Cần xây dựng công thức tính tổng số công để tổng hợp ngày công nhân viên.
• Việc liên kết giữa các bảng công tháng là điều nên làm để thuận tiện thao tác và giảm tình trạng sai sót.

Ngoài cách tạo bảng chấm công trên Excel, mời bạn tham khảo những bài viết liên quan: Cách di chuyển thứ tự trang trong Word, Cách chỉnh định dạng số trong Excel, Phím tắt di chuyển Sheet trong Excel.

Cách tạo bảng chấm công trên Excel chi tiết

Tạo bố cục cho các Sheet

Sheet danh sách nhân viên

Sheet này dùng để thống kê số lượng nhân viên đang làm việc trong công ty. Thành phần quan trọng nhất của nó là mã nhân viên, cấu tạo của nó nên được đồng nhất như Phòng ban – Số thứ tự hoặc Phòng ban – Tên viết tắt – Số thứ tự nếu có người trùng tên,…

Những cột thông tin cần có trong Sheet này là 

• STT
• Mã nhân viên
• Họ và tên
• Ngày sinh
• Ngày vào làm việc
• Số điện thoại liên lạc
• Ghi chú

Riêng cột ngày sinh và ngày bắt đầu làm việc, bạn sẽ cần chỉnh định dạng hiển thị của nó như sau: Bôi đen hai cột → chọn Format Cell → Number → Custom → dd/mm/yyyy (ngày/tháng/năm). 

Lưu ý: Nên chừa 2 – 3 hàng trống ở đầu bảng để liên kết đến Sheet chấm công theo tháng. Cách tạo bảng chấm công trên Excel này sẽ giúp thao tác thuận tiện hơn

Sheet chấm công theo tháng

Trước tiên bạn cần tạo các cột có đủ những thông tin sau đây 

• STT
• Mã nhân viên
• Họ và tên
• Ngày trong tháng
• Tổng hợp số ngày công
• Tổng hợp số ngày nghỉ
• Ghi chú

Để bảng trở nên gọn gàng hơn, bạn có thể bôi đen 30 hoặc 31 ngày → chọn “Command Column Width” →  nhập độ rộng nhỏ hơn.

Sau đó, bạn cần thực hiện theo các bước dưới đây để gán giá trị ngày cho các ô và định dạng hiển thị cho chúng:

Bước 1: Nhập giá trị Năm

• Xác định năm cho bảng chấm công tại ô D1 bằng hàm =date($D$1;1;1)
• Sử dụng hàm để tăng tính chính xác và tránh hiện trạng trùng ngày

Bước 2: Nhập giá trị Tháng 

Chọn ô B4 → Format Cell → Number → Custom →  nhập giá trị [“tháng “mm” năm “yyyy] vào ô Type → OK

Bước 3: Nhập giá trị Ngày 

• Tại ô thể hiện ngày đầu tiên của tháng (E9) nhập =B4
• Tại ô F9, nhập E9+1 để xác định ngày thứ 2 trong tháng
• Sau đó copy công thức từ ô F9 sang các ô bên cạnh cho tới ô thể hiện ngày cuối cùng của tháng

Bước 4: Định dạng Ngày

• Bôi đen các ô từ ngày 1 đến ngày cuối cùng của tháng
• Chọn Format Cell →  Custom → nhập giá trị “dd” → Nhấn OK

Tính ngày công của nhân viên trong tháng

Mỗi ngày đi làm của nhân viên sẽ được ghi nhận lại trong bảng chấm công, tuy nhiên chúng sẽ không đồng đều vì có ngày nghỉ, ngày làm nửa công,… Vì thế mỗi loại ngày công sẽ đi kèm với một ký hiệu riêng, thông thường là:

• Ngày công thực tế: X
• Ngày nửa công: V
• Nghỉ phép: P
• Nghỉ không lương: K
• Nghỉ ốm, thai sản, tai nạn: O

Sau đó dùng hàm COUNTIFS để đếm số lần xuất hiện của các ký hiệu trong tổng số ngày đi làm của nhân viên. Cuối cùng cộng tất cả kết quả cho từng ký hiệu lại sẽ có được tổng ngày công, cách làm cụ thể như sau

Bước 1: Tính ngày công thực tế

Tại AJ11, nhập hàm: =COUNTIF($E11:$AI11;$G$34)

• Giải thích: G34 chứa ký hiệu X, là đại diện cho số ngày công thực tế
• Ý nghĩa: Đếm số lần giá trị ký hiệu công thực tế xuất hiện từ ô E11 đến AI11 (số ngày công của nhân viên thứ nhất)

Bước 2: Tính ngày nghỉ phép, nghỉ không lương, nghỉ ốm,…

Bạn thực hiện tương tự công thức trên để tính các ngày công khác của nhân viên thứ nhất:

• Ô AK11 (Nửa ngày công): Nhập công thức =countif($E11:$AI11;$G$35)
• Ô AL11 (Nghỉ phép có lương): Nhập công thức =countif($E11:$AI11;$G$36)
• Ô AM11 (Nghỉ không lương): Nhập công thức =countif($E11:$AI11;$G$37)
• Ô AN11 (Ốm, thai sản, tai nạn): Nhập công thức =countif($E11:$AI11;$G$38)

Bước 3: Tính tổng sổ công

Tổng số công được tính dựa theo công thức của mỗi doanh nghiệp. Thông thường nó sẽ có dạng:

Tổng ngày công = Ngày công thực tế + Nửa ngày công x 0,5 + Nghỉ phép có lương

Cách thực hiện: Tại ô AO11, nhập = AJ11+AK11*0,5+AL11+AN11

Hoàn thiện bảng chấm công excel

Bước 1: Sao chép công thức cho các nhân viên khác

Sau khi hoàn thành cách tạo bảng chấm công trên Excel cho một nhân viên, bạn tiến hành sao chép công thức cho tất cả nhân viên còn lại theo hai cách sau:

• Cách 1: Bôi đen các dòng bên dưới đến khi hết danh sách nhân sự, bấm tổ hợp phím Ctrl + R
• Cách 2: Click chuột vào ô AO11, giữ chuột tại vị trí dấu chấm đen trong ô. Lúc này, trỏ chuột trở thành dấu +. Bạn chỉ cần kéo chuột tới khi hết danh sách nhân sự, rồi thả ra

Bước 2: Sao chép công thức cho các Sheet của những tháng còn lại

Khi đã hoàn thiện bảng chấm công cho nhân viên trong tháng 1, bạn hãy sao chép và dán chúng sang các tháng tiếp theo ở các Sheet khác. Khi đó, dữ liệu và mọi công thức sẽ được đồng bộ và không cần nhập liệu nhiều lần.

Qua bài viết, chuyên mục kiến thức – jacquart-lowe.com đã hướng dẫn cách tạo bảng chấm công trên Excel chi tiết. Hy vọng các bạn có thể vừa theo dõi vừa tự thực hành để các thao tác trở nên nhuần nhuyễn hơn.