Cách tạo bảng chấm công trong Excel cực chuẩn, tiết kiệm thời gian

Bạn đang loay hoay mỗi tháng mất hàng giờ đồng hồ để làm bảng chấm công thủ công? Hãy để Excel làm thay bạn! Trong bài viết này, chuyên gia AntBook về Excel sẽ bật mí cách tạo bảng chấm công tự động giúp tiết kiệm đến 90% thời gian, đồng thời tránh sai sót khi tính công, quản lý nhân viên hiệu quả. Khám phá ngay cùng chúng tôi nhé!

1. Hướng dẫn cách tạo bảng chấm công trong Excel cực chuẩn xác

1.1 Tạo bố cục các Sheet trong bảng chấm công Excel

Ở giai đoạn đầu trong cách tạo bảng chấm công trong Excel, bạn chỉ cần tập trung tạo 2 sheet chính:

• Sheet “Danh sách nhân viên”: Nơi lưu trữ thông tin cơ bản của toàn bộ nhân viên.
• Sheet “Tháng 1”: Là mẫu chuẩn để copy cho các tháng còn lại.

Thay vì mất công tạo từng sheet từ tháng 2 đến tháng 12, bạn chỉ cần thiết kế hoàn chỉnh sheet Tháng 1 một cách chuyên nghiệp, đầy đủ công thức, định dạng, màu sắc…. Sau đó, chỉ cần sao chép (Ctrl + C) và dán (Ctrl + V) để nhân bản sang các tháng tiếp theo.

Dùng tổ hợp phím tắt máy tính Ctrl + C và Ctrl + V để copy y nguyên nội dung trên sheet tháng 1 sang

1.2 Tạo Sheet danh sách nhân viên để làm bảng chấm công Excel

Sau khi đã thiết lập bố cục các sheet, bước tiếp theo là tạo Sheet “Danh sách nhân viên”, chứa toàn bộ thông tin cơ bản về nhân sự, đóng vai trò trung tâm để liên kết với các bảng chấm công hàng tháng.

Cấu trúc bảng nên có:

STT

Mã NV

Họ và tên

Ngày sinh

Quê quán

Số CCCD

Ngày vào làm

 

Tùy vào nhu cầu của doanh nghiệp, bạn có thể bổ sung thêm các cột như giới tính, bộ phận, chức vụ, số điện thoại, email, v.v.

Những lưu ý cần nắm khi tạo Sheet “Danh sách nhân viên”:

+ Nên bắt đầu nhập dữ liệu từ cột B và dòng số 4. Điều này giúp thuận tiện hơn khi sử dụng các công thức liên kết như VLOOKUP, INDEX hay VLOOKUP trong các sheet khác.

+ Để bảng tính trông chuyên nghiệp và tránh lỗi khi xử lý dữ liệu, bạn nên đồng nhất định dạng ngày theo kiểu dd-mm-yyyy. Cách thực hiện như sau:
Chọn toàn bộ cột ngày sinh → Click chuột phải → Chọn Format Cells → Tab Custom → Gõ định dạng dd-mm-yyyy → Nhấn OK.

Gợi ý thêm để quản lý bảng chấm công Excel hiệu quả:

• Mã nhân viên nên được quy ước rõ ràng và không trùng lặp, ví dụ: NV001, NV002… để làm khóa liên kết dữ liệu.
• Có thể sử dụng công cụ lọc (Filter) để dễ dàng tìm kiếm nhân viên theo tên, mã hoặc ngày vào làm.
• Nên dùng tính năng Data Validation để tạo danh sách chọn phòng ban hoặc vị trí làm việc, tránh nhập sai dữ liệu.

Lập các cột như số thứ tự, mã nhân viên, họ và tên, ngày tháng năm sinh, quê quán, số CCCD, ngày vào làm,…

1.3 Cách làm bảng chấm công trên Excel cho tháng 1

Đây là sheet mẫu về bảng chấm công chuẩn trong Excel, được dùng để nhân bản cho các tháng còn lại nên cần được thiết kế cẩn thận và đầy đủ ngay từ đầu.

Các nội dung cần có trong Sheet Tháng 1:

+ Tiêu đề bảng chấm công: Đặt ở vị trí B3 đến E3 với dòng chữ “BẢNG CHẤM CÔNG” – viết in hoa và căn giữa để tạo điểm nhấn.

+ Thông tin tháng, năm chấm công: Ghi rõ tháng và năm tại vùng B4 đến E4, ví dụ: “Tháng 1 năm 2025”.

+ Tên bộ phận: Ghi tại ô B5, ví dụ: “Phòng Hành chính – Nhân sự”.

+ Định mức ngày công trong tháng:  Nhập tại vùng B6 đến E6, tùy theo số ngày làm việc trong tháng thực tế (có thể là 22, 26, 28…).

+ Cấu trúc bảng chấm công chính:

Các cột bắt buộc gồm:

• STT
• Mã nhân viên
• Họ và tên
• Ngày trong tháng: từ ngày 1 đến ngày 31 (tương ứng 31 cột)
• Tổng hợp công: gồm các cột quy đổi như công thực tế, phép, nghỉ lễ, tăng ca…
• Cột ghi chú

Sau khi tạo đầy đủ các cột trên, hãy điều chỉnh độ rộng của từng cột sao cho nội dung hiển thị rõ ràng, không bị che khuất.
Cách thực hiện: Di chuyển chuột tới đường viền giữa hai cột trên thanh tiêu đề, khi biểu tượng con trỏ chuyển thành mũi tên hai chiều, click đúp chuột để Excel tự động căn chỉnh theo nội dung dài nhất trong cột.

Tạo các cột như trên và tiến hành tùy chỉnh độ rộng của cột sao cho phù hợp các chữ trong mỗi cột đều hiện hết trong ô

1.4 Tạo ngày tháng năm trong bảng chấm công trong Excel

Được mệnh danh là bước quan trọng và có phần phức tạp nhất khi thiết lập bảng chấm công Excel tự động, vì nó liên quan đến công thức ngày tháng và định dạng ngày trong tuần. Tuy nhiên, chỉ cần bạn làm theo đúng hướng dẫn từng bước dưới đây, bảng chấm công sẽ tự cập nhật chính xác số ngày, thứ trong tuần và cả những ngày Chủ Nhật.

Các bước thiết lập ngày tháng trong Excel:

Bước 1: Nhập tháng và năm

• Tại ô B4, gõ chữ Tháng
• Tại ô D4, nhập số tháng (ví dụ: 1 nếu là tháng 1)
• Tại ô E4, gõ chữ Năm
• Tại ô F4, nhập năm cần tạo bảng chấm công (ví dụ: 2025)

Tại ô B4 nhập chữ tháng, ô D4 nhập 1, E4 nhập chữ Năm, F4 nhập năm

Bước 2: Tạo ngày đầu tiên của tháng

Tại ô E9, nhập công thức:

=DATE(F4,D4,1)

Sau đó click chuột phải vào ô E9 → Chọn Format Cells → Tab Custom → Gõ dd vào mục Type → Nhấn OK.
→ Kết quả là số ngày đầu tiên trong tháng (vd: 01).

Tại ô E9 nhập hàm =date(F4,D4,1), bấm chuột phải vào ô đó chọn Format Cell, chọn Custom rồi nhập dd vào ô Type và click Ok

Bước 3: Tạo các ngày tiếp theo trong tháng

Tại ô F9, nhập công thức:

=E9+1

Sau đó, bôi đen từ ô F9 đến AI9 (tương đương 31 cột cho 31 ngày), nhấn tổ hợp phím Ctrl + R để sao chép công thức sang các ô còn lại.
→ Dãy ngày từ 1 đến 31 sẽ được tạo tự động.

Tại ô F9 gõ hàm =E9+1 rồi nhấn Enter, sau đó bôi đen từ ô F9 đến AI9 gõ Ctrl + R

Bước 4: Tạo thứ trong tuần tương ứng

Tại ô E10, nhập công thức:

=IF(WEEKDAY(E9)=1,”CN”,WEEKDAY(E9))

Bôi đen từ E10 đến AI10 → Click chuột phải → Chọn Format Cells → Tab Custom → Gõ TGeneral vào mục Type → Nhấn OK.
→ Excel sẽ hiển thị thứ trong tuần (ví dụ: 2 là thứ Hai, 7 là thứ Bảy, CN là Chủ Nhật).

Tại ô E10 nhập hàm =IF(WEEKDAY(E9)=1,’CN’,WEEKDAY(E9)) rồi nhấn Enter

Bước 5: Tô màu tự động cho ngày Chủ Nhật

• Bôi đen toàn bộ vùng E10:AI30 (bao gồm dòng thứ và các ô nhập công)
• Trên thanh công cụ, chọn Conditional Formatting → Chọn New Rule
• Chọn mục Use a formula to determine which cells to format
• Nhập công thức:

=IF(E$10=”CN”,TRUE,FALSE)

• Nhấn Format → Chọn tab Fill → Chọn màu nền (ví dụ: màu đỏ nhạt hoặc xám) → Nhấn OK

→ Kết quả: Tất cả các cột tương ứng với Chủ Nhật sẽ được tô màu nổi bật, giúp người quản lý dễ nhận biết ngày nghỉ cuối tuần.

Bôi đen toàn bộ từ E10:E30 đến AI10:AI30, chọn Conditional Formatting để tô màu có điều kiện trong Excel rồi sau đó bạn click vào mục New rule

1.5 Chỉnh tháng không đủ 31 ngày khi tạo bảng chấm công trên Excel

Không phải tháng nào trong năm cũng có đủ 31 ngày, và nếu bạn không xử lý đúng, các ngày như 30 hoặc 31 sẽ vô tình hiển thị sang tháng mới, gây sai sót trong việc chấm công. Để bảng chấm công chuyên nghiệp và chính xác hơn, bạn cần thiết lập các công thức loại bỏ tự động những ngày không tồn tại trong từng tháng.

Bước 1: Thiết lập công thức cho ngày 29 tại ô AF9

Nhập công thức:

=IF(DAY(AF9+1)=DAY(E9), “”, AF9+1)

Nếu ngày kế tiếp của ô AF9 trùng với ngày đầu tháng đã nhập tại ô E9, nghĩa là đã chuyển sang tháng sau, kết quả sẽ để trống. Ngược lại, hiển thị ngày tiếp theo. Cách này giúp tự động ẩn ngày 29 nếu tháng chỉ có 28 ngày (ví dụ tháng 2 không nhuận).

Bắt đầu từ ngày 29 chúng ta sẽ điều chỉnh về hàm như mẫu sau: =IF(DAY(AF9+1=DAY(E9);””;AF9+1)

Bước 2: Thiết lập công thức cho ngày 30 tại ô AG9

Nhập công thức:

=IF(AF9=””, “”, IF(DAY(AF9+1)=DAY(E9), “”, AF9+1))

AF9 không có giá trị (tức không có ngày 29), ô AG9 sẽ để trống. Nếu có ngày 29, công thức tiếp tục kiểm tra xem ngày tiếp theo có trùng ngày đầu tháng không. Nếu có, ô AG9 cũng để trống. Nếu không, hiển thị ngày 30.

Tại ô AH9 (ngày 30), ta sẽ dùng hàm sau: =IF(AG9=””;””;IF(DAY(AG9+1=DAY(E9);””;AG9+1))

Bước 3: Thiết lập công thức cho ngày 31 tại ô AH9

Nhập công thức:

=IF(AG9=””, “”, IF(DAY(AG9+1)=DAY(E9), “”, AG9+1))

Nếu ô AG9 không có giá trị, tức không có ngày 30, ô AH9 sẽ không hiển thị gì. Nếu có, công thức kiểm tra tiếp để loại trừ ngày 31 nếu không tồn tại trong tháng.

Tại ô AI9 (ngày 31), ta dùng hàm sau: =IF(AH9=””;””;IF(DAY(AH9+1=DAY(E9);””;AH9+1))

Lưu ý: Trong trường hợp các ô AF9, AG9 hoặc AH9 để trống, những ô liên quan phía dưới (hàng thứ 10 – hiển thị thứ trong tuần) có thể phát sinh lỗi công thức. Tuy nhiên, lỗi này không ảnh hưởng đến quá trình chấm công tổng thể và có thể bỏ qua.

1.6 Đổi màu tự động ngày chủ nhật trong bảng chấm công Excel

Bước 1: Bôi đen toàn bộ bảng, bao gồm các ngày trong tháng và phần chấm công của nhân viên.

Bôi đen nội dung trong bảng với tất cả nội dung về ngày trong tháng phần chấm công của các nhân viên

Bước 2: Chọn Conditional Formatting > New Rule > Chọn Use a formula to determine which cells to format.

Trong mục Conditional Formatting ta sẽ chọn New Rule, chọn Use a fomula to detemine which cells to format

Bước 3: Nhập công thức:

=IF(E$10=”Chủ nhật”, 1, 0)

→ Công thức này giúp xác định ngày Chủ nhật (ở ô E10) và áp dụng định dạng.

Nhấp vào mục Format values where this formula is true, ta sẽ nhập hàm: =IF(E$10=”Chủ nhật”;1;0)

Bước 4: Chọn Format để tùy chỉnh: màu chữ, màu nền hoặc đường viền cho ngày Chủ nhật, sau đó nhấn OK.

Chọn ô Format, chọn font chữ, màu chữ tại tag Font , màu nền tại tag Fill, kẻ đường viền ô tại tag Border, nhấn OK

Bước 5: Nhấn OK để áp dụng, ngày Chủ nhật trong bảng sẽ được tô màu tự động.

Sau khi định dạng xong ta nhấn OK

1.7 Đặt ký hiệu chấm công trong bảng chấm công bằng Excel

Ở bước này, việc thiết lập ký hiệu chấm công chuẩn xác là điều bắt buộc nếu bạn muốn bảng chấm công vừa khoa học, vừa dễ đối chiếu dữ liệu giữa các tháng. Việc sử dụng ký hiệu không chỉ giúp tiết kiệm thời gian nhập liệu mà còn đảm bảo tính nhất quán và rõ ràng trong quá trình quản lý nhân sự.

Các bước thực hiện:

Bước 1: Tạo mục chú giải ký hiệu

• Ở phía dưới bảng chấm công hoặc một khu vực riêng biệt trên Sheet, bạn nên tạo mục nhỏ mang tên “Ký hiệu chấm công”.
• Tại đây, bạn liệt kê tất cả các trường hợp chấm công phổ biến kèm ký hiệu tương ứng để người nhập liệu và người kiểm tra dễ dàng theo dõi.

Bước 2: Nhập ký hiệu vào cột “Quy ra công”

• Trong bảng chấm công chính, phần “Quy ra công” sẽ là nơi bạn sử dụng các ký hiệu đã đặt.
• Khi điền dữ liệu, bạn chỉ cần nhập ký hiệu tương ứng vào ô tương ứng với từng ngày trong tháng của từng nhân viên.

Một số ký hiệu chấm công phổ biến:

Trường hợp	Ký hiệu
Đi làm đầy đủ, đúng giờ	X
Làm nửa ngày (sáng hoặc chiều)	V
Nghỉ có lý do, vẫn hưởng lương	P
Nghỉ không lương	K
Nghỉ thai sản, ốm đau dài hạn	O

 

Lập một mục nhỏ dưới bảng tính rồi viết các trường hợp và ký hiệu của chúng ra

1.8 Đặt hàm công thức tính công cho nhân viên

Sau khi đã thiết lập bảng chấm công, bước tiếp theo là nhập hàm công thức để tính số buổi công cho mỗi nhân viên. Bạn sẽ tính công dựa trên các ký hiệu đã quy định như đi làm đầy đủ, làm nửa ngày, nghỉ ốm, v.v.

Các bước thực hiện:

Bước 1: Nhập hàm công thức vào các cột tính công:

• Tại cột AI11: =COUNTIF(E11:AI11, G34)
• Tại cột AK11: =COUNTIF(E11:AI11, G35)
• Tại cột AL11: =COUNTIF(E11:AI11, G36)
• Tại cột AM11: =COUNTIF(E11:AI11, G37)
• Tại cột AN11: =COUNTIF(E11:AI11, G38)

Bước 2: Các công thức trên sẽ đếm số lần ký hiệu tương ứng (ví dụ: đi làm đủ ngày, nghỉ, làm nửa ngày) xuất hiện trong phạm vi ô từ E11 đến AI11.

Bước 3: Kéo công thức xuống: Sau khi đã nhập công thức cho dòng 11, bạn kéo chuột xuống để sao chép công thức cho các dòng nhân viên khác.

Bước 4: Sao chép sheet: Sau khi hoàn tất các tính toán trong tháng 1, sao chép sheet chấm công này sang các trang tính tháng 2 đến tháng 12 để áp dụng công thức tương tự cho các tháng còn lại.

Cần nhập hàm vào từng loại công để tính số buổi mà nhân viên đã đi làm đầy đủ, làm nửa ngày, nghỉ ốm đau,…

2. Mẫu mô hình bảng chấm công trong Excel phổ biến

Trước khi bắt tay vào xây dựng bảng chấm công trên Excel, việc xác định mô hình cấu trúc của bảng là rất quan trọng. Bạn cần làm rõ các yếu tố như nội dung cần có trong bảng chấm công, số lượng sheet trong file, và các cột, dòng cần thiết. Câu trả lời cho các câu hỏi này sẽ giúp bạn tạo bảng chấm công nhanh chóng và khoa học hơn. Dưới đây là gợi ý mô hình bảng chấm công mà bạn có thể tham khảo:

+ Số lượng sheet: File chấm công bao gồm 13 sheet. Một sheet dùng để liệt kê danh sách nhân viên, và 12 sheet còn lại đại diện cho 12 tháng trong năm.

+ Số lượng nhân viên: Mỗi sheet chấm công thường có khoảng 20 nhân viên (hoặc nhiều hơn, tùy vào quy mô của phòng ban).

+ Ký hiệu chấm công: Mỗi nhân viên sẽ có một ký hiệu chấm công riêng biệt, giúp phân biệt các loại công (ví dụ: đi làm đầy đủ, nghỉ phép, ốm…). Các ký hiệu này sẽ được sử dụng xuyên suốt trong cả 12 tháng.
+ Cấu trúc cột và ngày: Mỗi bảng chấm công sẽ bao gồm các cột như số thứ tự, tên nhân viên, các ngày trong tháng, và cuối cùng là tổng số công. Lưu ý, thứ bảy và chủ nhật sẽ được tô màu nổi bật để dễ dàng nhận diện.

+ Tính toán công: Công của mỗi nhân viên được tính theo ngày và tổng kết vào cuối tháng. Các cột này sẽ tính toán tự động dựa trên các ký hiệu đã nhập.

+ Tính nhất quán: Tên nhân viên trong các sheet chấm công của từng tháng sẽ khớp với danh sách nhân viên trong sheet liệt kê danh sách.

Một bảng chấm công có tổng cộng 13 sheet

3. Lưu ý trong cách tạo bảng chấm công trong Excel

Khi làm bảng chấm công trong Excel, bạn sẽ gặp một số thách thức nhỏ trong quá trình thiết kế và nhập liệu. Để đảm bảo quá trình tạo bảng diễn ra thuận lợi và chính xác, có một số lưu ý quan trọng mà bạn cần nhớ. 

Các sheet chấm công trong Excel của mỗi tháng cần được đồng bộ với nhau, đặc biệt là về ký hiệu chấm công và công thức tính công, nhằm đảm bảo sự nhất quán trong suốt năm. Trước khi phân chia các hàng và cột, hãy xác định rõ các tiêu chí và cấu trúc bảng lớn. Sau đó, chia nhỏ bảng thành các phần nhỏ hơn để dễ dàng điền thông tin mà không bị sót mục nào. 

Quan trọng nhất, khi đã hoàn tất việc nhập dữ liệu và tính công, đừng quên ghi rõ ngày, giờ hoàn thành để thuận tiện trong việc kiểm tra sau này, tránh nhầm lẫn và đảm bảo tính chính xác cho bảng chấm công. Những bước chuẩn bị và chú ý này sẽ giúp bạn tạo ra bảng chấm công hiệu quả, khoa học và dễ sử dụng.

Một số vấn đề phát sinh khi tạo hàng, cột hay nhập hàm cần phải lưu ý về ký hiệu, hàm công thức,…

Tạo bảng chấm công trong Excel không chỉ giúp bạn quản lý công việc một cách hiệu quả mà còn tiết kiệm thời gian và công sức trong việc theo dõi và tính toán số ngày làm việc của nhân viên.

Với những hướng dẫn chi tiết và những lưu ý quan trọng đã được chia sẻ, bạn hoàn toàn có thể tự tạo cho mình một bảng chấm công chuyên nghiệp và dễ dàng sử dụng. Tuy nhiên, để nâng cao kỹ năng và khám phá thêm nhiều mẹo hữu ích trong Excel, đừng quên tham khảo sách Bí quyết làm chủ Excel của AntBook. Cuốn sách này sẽ cung cấp cho bạn những kiến thức sâu và bí quyết để tối ưu hóa công việc với Excel, từ đó giúp bạn trở thành một bậc thầy trong việc xử lý dữ liệu và tạo bảng tính hiệu quả. Mua ngay tại Antbook.vn nhé!