Cách tạo bảng chấm công tự động bằng Excel (kèm công thức tính công)

Bảng chấm công tự động trong Excel cần những phần nào?

Với văn phòng hoặc cửa hàng nhỏ chưa dùng máy chấm công vân tay, một bảng chấm công bằng Excel vẫn đủ chính xác nếu có đúng 3 phần:

• Khung ngày trong tháng: các cột từ ngày 1 đến ngày cuối tháng, tự động hiện thứ để biết ngày nào là Chủ nhật.
• Vùng nhập ký hiệu chấm công: mỗi ngày một ô, nhập ký hiệu quy ước sẵn (ví dụ X = đi làm, P = nghỉ phép, O = nghỉ không phép).
• Cột tổng hợp cuối bảng: dùng công thức đếm tự động tổng ngày công, tổng ngày nghỉ phép, tổng ngày nghỉ không phép của từng người, không cần đếm tay.

Bài này dựng đúng 3 phần trên bằng công thức có sẵn của Excel, không cần VBA hay add-in.

Bước 1: Dựng khung ngày tự động cho cả tháng

Thay vì gõ tay từng ngày 1, 2, 3... rồi tự tính lệch khi đổi tháng, dùng công thức để Excel tự sinh ngày.

Tại ô đầu tiên của dòng ngày (giả sử ô C1), nhập ngày đầu tháng cần chấm công, ví dụ:

=DATE(2026,9,1)

Tại ô kế tiếp (D1), nhập công thức cộng thêm 1 ngày so với ô trước:

=C1+1

Sau đó kéo công thức này sang phải đến hết số ngày của tháng (28-31 ô tùy tháng). Định dạng dòng này chỉ hiện số ngày bằng cách bôi đen cả dòng, bấm Ctrl + 1 → tab Number → chọn Custom → nhập định dạng d.

Thêm một dòng ngay bên dưới để hiện thứ trong tuần, tại ô C2 nhập:

=TEXT(C1,"ddd")

Kéo công thức này sang phải tương ứng với dòng ngày phía trên. Excel sẽ tự hiện "T2, T3... CN" theo đúng ngày, giúp nhìn nhanh ngày nào là cuối tuần mà không cần tra lịch.

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

Để không phải tự dò từng cột xem ngày nào là Chủ nhật, dùng Conditional Formatting dựa trên hàm WEEKDAY:

1. Bôi đen toàn bộ vùng ngày và vùng chấm công (ví dụ C1:AG20).
2. Vào Home → Conditional Formatting → New Rule.
3. Chọn Use a formula to determine which cells to format, nhập:

=WEEKDAY(C$1,2)=7

4. Bấm Format, chọn màu nền (ví dụ xám nhạt), bấm OK hai lần.

WEEKDAY(C$1,2) trả về số thứ tự ngày trong tuần theo kiểu Thứ 2 = 1 ... Chủ nhật = 7 (tham số 2 quy định cách đánh số này). Dấu $ trước số dòng (C$1) giữ cố định dòng chứa ngày khi Conditional Formatting áp cho nhiều dòng nhân viên phía dưới, còn cột vẫn tự trượt theo từng cột khi so sánh.

Bước 3: Nhập ký hiệu chấm công theo ngày

Ở các dòng bên dưới (mỗi dòng một nhân viên), nhập ký hiệu quy ước cho từng ngày. Quy ước phổ biến:

Ký hiệu	Ý nghĩa
X	Đi làm đủ công
P	Nghỉ phép (có lương)
O	Nghỉ không phép
CN	Nghỉ Chủ nhật (không tính công, không trừ phép)

Ví dụ một dòng chấm công của nhân viên trong 5 ngày đầu tháng:

Họ và tên	1	2	3	4	5
Nguyễn Văn A	X	X	P	X	O

Ghi rõ quy ước ký hiệu này ở một ô chú thích cố định trong file (ví dụ góc trên bên trái) để người chấm công không nhầm lẫn, đặc biệt khi có nhiều người cùng nhập liệu.

Bước 4: Dùng COUNTIF tự động tính tổng công cuối bảng

Thêm 3 cột ở cuối bảng: Tổng công, Tổng phép, Tổng nghỉ không phép. Giả sử vùng chấm công của một nhân viên nằm ở C5:AG5, công thức tương ứng:

Tổng ngày công (đi làm đủ):

=COUNTIF(C5:AG5,"X")

Tổng ngày nghỉ phép:

=COUNTIF(C5:AG5,"P")

Tổng ngày nghỉ không phép:

=COUNTIF(C5:AG5,"O")

COUNTIF đếm số ô trong vùng C5:AG5 khớp đúng với ký hiệu chỉ định, không cần dò tay từng ô. Kéo cả 3 công thức xuống các dòng nhân viên còn lại là xong — mỗi người tự có kết quả tổng hợp riêng.

Muốn kiểm tra nhanh tổng số ngày đã chấm có khớp với số ngày trong tháng không (tránh bỏ sót ô trống), thêm một cột kiểm tra:

=COUNTA(C5:AG5)

COUNTA đếm tất cả ô có dữ liệu (bất kể ký hiệu gì). Nếu kết quả nhỏ hơn số ngày trong tháng, nghĩa là còn ô chưa chấm công, cần rà lại trước khi tính lương.

Bước 5: Liên kết tổng công sang bảng tính lương

Nếu đã có bảng tính lương riêng (theo mẫu bảng lương cơ bản), có thể kéo cột "Tổng công" sang thẳng bảng lương bằng cách tham chiếu trực tiếp thay vì gõ tay lại số liệu:

=BangChamCong!J5

Trong đó BangChamCong là tên sheet chứa bảng chấm công, J5 là ô chứa tổng công của nhân viên tương ứng. Cách này giúp khi chấm công thay đổi (sửa lại ngày nghỉ chẳng hạn), số liệu lương cập nhật theo ngay mà không phải nhập lại thủ công.

Lỗi thường gặp

COUNTIF trả về 0 dù rõ ràng có ký hiệu khớp Thường do ký hiệu nhập vào có khoảng trắng thừa (gõ "X " thay vì "X") hoặc nhập bằng phông chữ/kiểu gõ khác khiến ký tự trông giống nhưng không khớp. Xóa và gõ lại trực tiếp trong ô, tránh copy dán từ nguồn khác.

Ngày tự sinh bị nhảy sai khi kéo công thức Do công thức =C1+1 bị kéo nhầm hướng hoặc bị chèn thêm cột ở giữa làm lệch tham chiếu. Kiểm tra lại từng ô đầu dòng ngày, đảm bảo mỗi ô chỉ cộng thêm đúng 1 ngày so với ô liền trước.

Tổng công + Tổng phép + Tổng nghỉ không phép không bằng số ngày trong tháng Nghĩa là có ô bị bỏ trống hoặc nhập ký hiệu ngoài quy ước (gõ nhầm x thường thay vì kiểm tra được vì COUNTIF không phân biệt hoa/thường, nhưng gõ nhầm ký tự khác như x. thì có). Dùng cột COUNTA ở Bước 4 để rà soát trước khi chốt bảng lương.

Conditional Formatting tô sai cột sau khi thêm/xóa cột ngày Do vùng áp dụng của rule (Applies to) chưa được mở rộng hoặc thu hẹp lại theo đúng vùng ngày mới. Vào Home → Conditional Formatting → Manage Rules để chỉnh lại vùng áp dụng mỗi khi thay đổi cấu trúc bảng.

Khi nào nên nâng cấp bảng chấm công?

Cấu trúc trên phù hợp với đội ngũ vài chục người chấm công thủ công. Khi quy mô lớn hơn, có thể cân nhắc:

• Dùng IF lồng để tự động phân loại "đi trễ"/"về sớm" nếu có thêm cột giờ vào - giờ ra.
• Dùng SUMPRODUCT khi cần tính công theo trọng số khác nhau (ví dụ ngày lễ tính gấp đôi).
• Kết nối với máy chấm công vân tay/khuôn mặt xuất file Excel, rồi dùng Power Query để tự động gộp dữ liệu thay vì nhập tay.

Những phần này thuộc nhóm công thức và công cụ nâng cao hơn, nên nắm chắc COUNTIF, COUNTA và tham chiếu giữa các sheet như trong bài trước khi học tiếp.

Học Excel thực chiến tại TP Vinh

Chấm công và tính lương là một trong những công việc lặp lại hàng tháng mà Excel giải quyết rất tốt nếu nắm đúng công thức. Nếu bạn muốn học Excel bài bản từ công thức cơ bản đến xử lý dữ liệu, làm báo cáo tự động, tham khảo khóa học Excel tại Điền Nguyễn AZ.

📞 Hotline / Zalo: 0362 465 361

👉 Xem chi tiết: Khóa học Excel thực tế tại TP Vinh