Mẫu bảng chấm công Excel: cách tự làm tự động theo tháng, kèm công thức
Hướng dẫn tự làm mẫu bảng chấm công Excel tự động theo tháng: tiêu đề ngày và thứ tự nhảy, ký hiệu chấm công, công thức COUNTIF tổng hợp công, tô màu ngày nghỉ, chấm công làm thêm giờ và nối sang bảng lương.
Trả lời nhanh: một mẫu bảng chấm công Excel dùng được cho mọi tháng gồm ba phần: thông tin nhân viên (mã, họ tên, bộ phận), lưới ngày trong tháng với ngày và thứ tự động thay đổi khi bạn đổi tháng, và cột tổng hợp đếm số công bằng hàm COUNTIF theo ký hiệu (X, X/2, P, L, KL…). Chỉ cần nhập tháng, năm và đánh ký hiệu cho từng người mỗi ngày; số ngày công, ngày phép và ngày nghỉ không lương được tính sẵn để chuyển sang bảng lương.
Bài viết hướng dẫn tự dựng bảng chấm công từ đầu, giải thích từng công thức, cách tô màu ngày nghỉ tự động, cách xử lý dữ liệu từ máy chấm công và cách tính lương theo ngày công. Các con số trong bài là ví dụ minh họa.
Bố cục bảng chấm công
| Vùng | Vị trí gợi ý | Nội dung |
|---|---|---|
| Tiêu đề | Dòng 1–3 | Tên công ty, “BẢNG CHẤM CÔNG”, ô nhập tháng (C2) và năm (C3) |
| Đầu cột | Dòng 5–6 | Ngày (dòng 5) và thứ (dòng 6) cho 31 cột, từ F đến AJ |
| Nhân viên | Từ dòng 7, cột A–E | STT, mã NV, họ tên, bộ phận, chức vụ |
| Lưới chấm công | F7:AJ… | Ký hiệu chấm công mỗi ngày |
| Tổng hợp | AK trở đi | Công thực tế, phép, lễ, không lương, công hưởng lương |
Lưu ý dấu phân cách: công thức trong bài dùng dấu phẩy. Nếu Excel trên máy bạn dùng định dạng số kiểu Việt Nam, hãy thay bằng dấu chấm phẩy, ví dụ =DATE(C3;C2;1).
Bước 1: Ngày và thứ tự động theo tháng
Nhập tháng vào C2 (ví dụ 12) và năm vào C3 (2026). Sau đó:
- Ngày đầu tháng (F5):
=DATE($C$3,$C$2,1), định dạng ô kiểuddđể chỉ hiện số ngày. - Các ngày tiếp theo (G5, kéo sang đến AJ5):
=IF(F5="","",IF(MONTH(F5+1)=$C$2,F5+1,"")). Tháng có 28, 29 hoặc 30 ngày sẽ tự để trống các cột thừa. - Thứ (F6, kéo sang):
=IF(F5="","",CHOOSE(WEEKDAY(F5,2),"T2","T3","T4","T5","T6","T7","CN"))
Từ nay, chỉ cần đổi số tháng ở C2 là toàn bộ đầu cột cập nhật, không phải làm lại bảng mỗi tháng.
Bước 2: Thống nhất ký hiệu chấm công
Mỗi công ty có thể dùng ký hiệu riêng; điều quan trọng là thống nhất và ghi rõ trong bảng. Một bộ ký hiệu phổ biến:
| Ký hiệu | Ý nghĩa | Tính công |
|---|---|---|
| X | Đi làm đủ ngày | 1 |
| X/2 | Đi làm nửa ngày | 0,5 |
| CT | Đi công tác | 1 |
| P | Nghỉ phép năm có lương | 1 (hưởng lương) |
| L | Nghỉ lễ, Tết | 1 (hưởng lương) |
| O | Nghỉ ốm | theo chế độ |
| TS | Nghỉ thai sản | theo chế độ |
| KL | Nghỉ không lương | 0 |
Để tránh gõ sai, chọn vùng F7:AJ200, vào Data → Data Validation → List và nhập danh sách X,X/2,CT,P,L,O,TS,KL. Người chấm công chỉ cần chọn từ danh sách thả xuống.
Bước 3: Công thức tổng hợp công
Với dòng nhân viên đầu tiên (dòng 7), đặt các cột tổng hợp:
- Công thực tế (AK7):
=COUNTIF(F7:AJ7,"X")+COUNTIF(F7:AJ7,"CT")+0.5*COUNTIF(F7:AJ7,"X/2") - Nghỉ phép (AL7):
=COUNTIF(F7:AJ7,"P") - Nghỉ lễ, Tết (AM7):
=COUNTIF(F7:AJ7,"L") - Nghỉ ốm, thai sản (AN7):
=COUNTIF(F7:AJ7,"O")+COUNTIF(F7:AJ7,"TS") - Không lương (AO7):
=COUNTIF(F7:AJ7,"KL") - Công hưởng lương (AP7):
=AK7+AL7+AM7
Trên máy dùng dấu phẩy thập phân, số 0.5 viết là 0,5. Kéo các công thức xuống cho mọi nhân viên. Ngày nghỉ ốm và thai sản thường do cơ quan bảo hiểm xã hội chi trả theo chế độ, nên được tách riêng thay vì cộng vào công hưởng lương.
Bước 4: Tô màu ngày nghỉ tự động
Chọn vùng F5:AJ200, vào Home → Conditional Formatting → New Rule → Use a formula:
- Tô Chủ nhật:
=AND(F$5<>"",WEEKDAY(F$5,2)=7) - Nếu công ty nghỉ cả thứ Bảy:
=AND(F$5<>"",WEEKDAY(F$5,2)>=6) - Tô ngày lễ: tạo danh sách ngày lễ trong năm ở một sheet riêng (đặt tên vùng là NgayLe) và dùng
=COUNTIF(NgayLe,F$5)>0
Thêm một quy tắc tô đỏ ô có ký hiệu KL để quản lý dễ nhận ra ngày nghỉ không lương.
Bước 5: Lấy dữ liệu từ máy chấm công
Hầu hết máy chấm công vân tay hoặc khuôn mặt xuất được file Excel/CSV có mã nhân viên, ngày, giờ vào và giờ ra. Dán file đó vào một sheet “DuLieuMay”, rồi thêm cột quy đổi sang ký hiệu, ví dụ với giờ vào ở C2 và giờ ra ở D2:
=IF(AND(C2<=TIME(8,0,0),D2>=TIME(17,0,0)),"X",IF(D2-C2>=TIME(4,0,0),"X/2",""))
Điều chỉnh giờ chuẩn theo nội quy của bạn. Những dòng trống (quên chấm, đi công tác, nghỉ phép) cần người phụ trách nhân sự bổ sung ký hiệu bằng tay và lưu lại căn cứ (đơn xin nghỉ, quyết định cử đi công tác).
Bước 6: Nối sang bảng lương
Với cách trả lương theo ngày công thực tế, công thức phổ biến là:
Lương theo công = Lương tháng ÷ Ngày công chuẩn × Công hưởng lương
Ngày công chuẩn là số ngày làm việc trong tháng theo quy chế lương của công ty. Có công ty cố định một con số (ví dụ 26 ngày), có công ty tính theo lịch thực tế: nếu chỉ nghỉ Chủ nhật, =NETWORKDAYS.INTL(F5,EOMONTH(F5,0),11,NgayLe) cho số ngày làm việc trong tháng.
Ví dụ minh họa: lương tháng 9.000.000 đ, ngày công chuẩn 26, nhân viên có 24 công hưởng lương. Lương theo công = 9.000.000 ÷ 26 × 24 ≈ 8.307.692 đ. Hãy làm tròn theo quy chế của công ty và ghi rõ cách tính trong hợp đồng hoặc quy chế lương để tránh tranh chấp.
Chấm công làm thêm giờ
Làm thêm giờ nên theo dõi ở một bảng riêng, đếm theo giờ và tách theo loại ngày, vì mức trả khác nhau. Theo Bộ luật Lao động 2019, tiền lương làm thêm giờ ít nhất bằng 150% vào ngày thường, 200% vào ngày nghỉ hằng tuần và 300% vào ngày lễ, Tết (chưa kể tiền lương của ngày lễ đối với người hưởng lương ngày). Một bảng làm thêm giờ đơn giản có các cột: ngày, mã NV, số giờ, loại ngày (thường / nghỉ tuần / lễ), người duyệt. Tổng giờ theo từng loại tính bằng SUMIFS, rồi nhân với đơn giá giờ và hệ số tương ứng trong bảng lương.
Giới hạn số giờ làm thêm và các trường hợp đặc biệt cần đối chiếu với quy định hiện hành; bài viết chỉ hướng dẫn cách tổ chức số liệu, không thay thế tư vấn pháp lý hay kế toán.
Những lỗi thường gặp
- Gõ ký hiệu tự do: “x”, “X “, “1” lẫn lộn khiến COUNTIF đếm sai. Luôn dùng danh sách thả xuống.
- Tạo bảng mới bằng tay mỗi tháng: dễ sai thứ trong tuần. Hãy dùng ngày tự động như Bước 1 và lưu mỗi tháng thành một sheet.
- Không lưu căn cứ cho ngày phép, công tác: khi có thắc mắc về lương sẽ không đối chiếu được.
- Trộn giờ làm thêm vào bảng công ngày: khó tính đúng hệ số. Tách bảng riêng.
- Để mọi người sửa công thức: khóa vùng tổng hợp bằng Review → Protect Sheet, chỉ mở vùng nhập ký hiệu.
Câu hỏi thường gặp
Có dùng bảng chấm công này trên Google Sheets được không?
Được. Các hàm DATE, WEEKDAY, CHOOSE, COUNTIF và định dạng có điều kiện đều có trên Google Sheets. Google Sheets tiện khi quản lý các bộ phận cùng chấm công trên một file.
Ngày công chuẩn là 26 hay 24 ngày?
Không có con số chung cho mọi doanh nghiệp. Công ty làm việc cả thứ Bảy thường có khoảng 26–27 ngày làm việc mỗi tháng, công ty làm 5 ngày một tuần khoảng 21–23 ngày. Con số áp dụng cần ghi trong quy chế lương hoặc hợp đồng lao động.
Làm sao chấm công đi muộn, về sớm?
Thêm cột số phút đi muộn ở sheet dữ liệu máy chấm công: =MAX(0,C2-TIME(8,0,0))*1440 cho ra số phút trễ. Tổng số phút mỗi tháng tính bằng SUMIFS theo mã nhân viên. Cách xử lý (nhắc nhở, trừ lương…) phải theo nội quy lao động đã ban hành.
Nhân viên bán thời gian tính công thế nào?
Với người làm theo giờ, hãy chấm số giờ thay vì ký hiệu ngày và tính lương bằng tổng giờ nhân đơn giá giờ.
Có nên lưu bảng chấm công lâu dài?
Có. Bảng chấm công là căn cứ trả lương, đóng bảo hiểm và giải quyết thắc mắc của người lao động. Lưu mỗi tháng một sheet hoặc một file, kèm chữ ký xác nhận nếu công ty yêu cầu.
Xem thêm các hướng dẫn trong nhóm Vận hành & nhân sự, ví dụ mẫu biên bản họp cho các buổi họp nội bộ.