Có thể bạn quan tâm: Hàm Tính Tuổi Trong Excel: Cách Sử Dụng, Ví Dụ Thực Tế Và Các Mẹo Nâng Cao
1. Giới thiệu chung về bảng chấm công trong môi trường doanh nghiệp
Trong bất kỳ doanh nghiệp nào, việc quản lý thời gian làm việc của nhân viên là một trong những yếu tố then chốt để duy trì hiệu suất và kiểm soát chi phí. Bảng chấm công không chỉ giúp doanh nghiệp biết được số giờ làm việc thực tế của mỗi người mà còn hỗ trợ trong việc tính lương, đánh giá hiệu suất, và tuân thủ các quy định pháp luật về thời gian làm việc.
Trong thời đại số hoá, Excel đã trở thành công cụ phổ biến nhất để thiết kế và vận hành các mẫu bảng chấm công vì:
- Dễ dàng tùy chỉnh: Người dùng có thể tạo các cột, công thức, định dạng tùy ý.
- Tính linh hoạt: Dễ dàng mở rộng, thêm chức năng mới (ví dụ: tính overtime, nghỉ phép).
- Khả năng tích hợp: Dễ dàng xuất báo cáo, nhập dữ liệu từ các hệ thống khác.
- Chi phí thấp: Không cần đầu tư phần mềm chuyên dụng đắt tiền.
Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết về cách tạo, sử dụng và tối ưu hoá mẫu bảng chấm công Excel, từ những nguyên tắc cơ bản đến các tính năng nâng cao như tự động tính lương, báo cáo tổng hợp, và bảo mật dữ liệu.
Có thể bạn quan tâm: Hàm Nối Chuỗi Trong Excel: Từ Cơ Bản Đến Nâng Cao
2. Các thành phần cơ bản của một mẫu bảng chấm công Excel
Trước khi bắt tay vào thiết kế, chúng ta cần nắm rõ các thành phần thường xuất hiện trong một bảng chấm công chuẩn:
| Thành phần | Mô tả | Ghi chú |
|---|---|---|
| Tên nhân viên | Họ và tên hoặc mã số nhân viên | Dùng để liên kết với dữ liệu nhân sự |
| Mã phòng/ban | Thông tin về phòng ban, bộ phận | Hỗ trợ phân loại, báo cáo theo đơn vị |
| Ngày | Các ngày trong tháng (hoặc tuần) | Thường đặt theo dạng cột |
| Giờ vào | Thời gian bắt đầu làm việc mỗi ngày | Định dạng thời gian (hh:mm) |
| Giờ ra | Thời gian kết thúc làm việc mỗi ngày | Định dạng thời gian |
| Giờ làm việc thực tế | Tổng số giờ làm việc trong ngày | Tự động tính bằng công thức |
| Nghỉ lễ/Ngày nghỉ | Đánh dấu ngày nghỉ, nghỉ lễ, nghỉ bệnh | Dùng màu hoặc ký hiệu đặc biệt |
| Overtime (tăng ca) | Số giờ làm thêm so với chuẩn | Thường tính khi >8h/ngày |
| Ghi chú | Nhận xét, lý do vắng mặt, … | Tự do nhập |
| Tổng giờ trong tháng | Tổng hợp giờ làm việc, nghỉ, tăng ca | Kết quả cuối tháng |
Các thành phần này có thể mở rộng tùy theo nhu cầu thực tế (ví dụ: chấm công theo ca, chấm công dựa trên dự án, v.v.). Tuy nhiên, việc giữ cấu trúc rõ ràng, thống nhất sẽ giúp việc xử lý dữ liệu nhanh chóng và giảm thiểu lỗi.
Có thể bạn quan tâm: Cách Lọc Dữ Liệu Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
3. Bước đầu: Tạo mẫu bảng chấm công cơ bản
3.1. Chuẩn bị môi trường làm việc
- Mở Microsoft Excel (phiên bản 2026 trở lên để tận dụng các tính năng mới như
XLOOKUP). - Tạo một workbook mới và đặt tên file:
BangChamCong_ThangMM_YYYY.xlsx. - Lưu file vào thư mục chung của bộ phận nhân sự hoặc hệ thống lưu trữ đám mây (OneDrive, Google Drive) để mọi người có thể truy cập và cập nhật.
3.2. Xây dựng tiêu đề và định dạng chung
| Ô | Nội dung | Định dạng đề xuất |
|---|---|---|
| A1 | BẢNG CHẤM CÔNG THÁNG | Font: Arial, 16pt, in đậm, căn giữa, màu xanh đậm |
| A2 | Tháng: MM/YYYY (điền tháng hiện tại) | Font: Arial, 12pt, in đậm |
| A3 | Đơn vị: Tên công ty / Bộ phận | Font: Arial, 12pt |
| A4 | Ngày tạo: =TEXT(NOW(),"dd/mm/yyyy") | Font: Arial, 10pt, màu xám |
- Merge cells A1:G1 để tiêu đề chiếm toàn bộ cột.
- Đặt màu nền nhẹ (ví dụ: xanh nhạt) cho hàng tiêu đề để phân biệt.
3.3. Thiết lập cột dữ liệu
Giả sử chúng ta muốn chấm công cho 31 ngày trong một tháng, mỗi ngày sẽ có 2 cột: Giờ vào và Giờ ra. Cấu trúc cột như sau (bắt đầu từ cột A):
| Cột | Nội dung | Ghi chú |
|---|---|---|
| A | STT | Số thứ tự, tự động tăng |
| B | Mã NV | Mã số nhân viên |
| C | Họ và Tên | Tên đầy đủ |
| D | Phòng/Ban | Đơn vị làm việc |
| E | Ngày 1 (Giờ vào) | Định dạng thời gian |
| F | Ngày 1 (Giờ ra) | Định dạng thời gian |
| G | Ngày 1 (Số giờ) | Công thức tính |
| H | Ngày 2 (Giờ vào) | … |
| I | Ngày 2 (Giờ ra) | … |
| J | Ngày 2 (Số giờ) | … |
| … | … | … |
| … | … | … |
| … | … | … |
| … | Tổng giờ | Tổng cộng cuối cột |
Lưu ý: Để giảm độ rộng bảng, bạn có thể gộp 3 cột cho mỗi ngày (Giờ vào, Giờ ra, Số giờ) thành một nhóm và dùng grouping trong Excel (Data → Group) để người dùng có thể thu gọn/ mở rộng.
3.4. Áp dụng công thức tính số giờ
Giả sử Giờ vào ở cột E2, Giờ ra ở cột F2. Đặt công thức tính giờ làm việc trong cột G2:
=IF(OR(E2="",F2=""),"",ROUND((F2-E2)24,2))
Giải thích:
F2-E2trả về giá trị thời gian (đơn vị ngày). Nhân 24 để chuyển sang giờ.ROUND(...,2)làm tròn đến 2 chữ số thập phân.IF(OR(E2="",F2=""),"",...)nếu chưa nhập giờ vào/ra thì để trống.
Sao chép công thức này cho toàn bộ cột Số giờ của các ngày.
3.5. Tính tổng giờ trong tháng
Ở dòng cuối cùng (giả sử là dòng 200), tạo công thức tổng:
=SUM(G2:G199) // Tổng ngày 1
Lặp lại cho các cột Số giờ của các ngày khác, sau đó cộng lại:
=SUM(G2:G199, J2:J199, M2:M199, …) // Hoặc dùng SUMPRODUCT
Để đơn giản, bạn có thể tạo một cột tổng (ví dụ: cột Z) và dùng công thức:
=SUM(G2:Z2) // Giả sử G đến Y là các cột số giờ
Kéo công thức xuống cho toàn bộ nhân viên.
4. Tùy chỉnh nâng cao: Tự động tính overtime và nghỉ lễ
4.1. Định nghĩa chuẩn giờ làm việc
- Giờ chuẩn: 8 giờ/ngày.
- Overtime: Giờ làm việc > 8h.
- Nghỉ lễ: Đánh dấu bằng màu vàng hoặc ký hiệu
L.
4.2. Thêm cột Overtime
Giả sử cột AA là Overtime cho ngày 1:
=MAX(0, G2-8)
MAX(0, ...)để tránh giá trị âm nếu làm ít hơn 8h.- Kéo công thức sang các ngày tương ứng.
4.3. Đánh dấu ngày nghỉ lễ
- Tạo danh sách ngày lễ trong một sheet riêng (
Sheet2), cột A:Ngày lễ(định dạng ngày), cột B:Tên lễ. - Sử dụng Conditional Formatting để tô màu các ô ngày tương ứng:
- Chọn toàn bộ vùng Giờ vào và Giờ ra (ví dụ:
E2:W199). - Vào Home → Conditional Formatting → New Rule → Use a formula.
- Nhập công thức:
excel =COUNTIF(Sheet2!$A:$A, E$1) // E$1 chứa ngày thực tế (ví dụ: 01/05/2026)
- Đặt định dạng màu vàng và ký hiệu “L”.
4.4. Tự động bỏ qua ngày lễ trong tính tổng giờ
Khi tính tổng giờ, chúng ta muốn không tính ngày lễ. Sử dụng công thức mảng (Excel 365) hoặc SUMIFS:
=SUMIFS(G2:G199, $E$1:$E$31, "<>"&"L") // Giả sử cột E1:E31 là tiêu đề ngày
Hoặc nếu dùng cột đánh dấu (cột AB là “Ngày làm”/“Nghỉ”):
=SUMIFS(G2:G199, AB2:AB199, "Làm")
5. Tích hợp dữ liệu nhân sự và tính lương tự động
Có thể bạn quan tâm: Hàm Xếp Hạng Trong Excel: Cách Sử Dụng, Ứng Dụng Thực Tiễn Và Mẹo Tối Ưu
5.1. Tạo bảng dữ liệu nhân sự (Sheet “NhanSu”)
| Mã NV | Họ và Tên | Phòng/Ban | Lương cơ bản (VNĐ) | Hệ số OT |
|---|---|---|---|---|
| NV001 | Nguyễn Văn A | Kế toán | 7,000,000 | 1.5 |
| NV002 | Trần Thị B | Kinh doanh | 9,000,000 | 2.0 |
| … | … | … | … | … |
5.2. Sử dụng XLOOKUP để lấy lương cơ bản
Trong sheet chấm công, cột AC là Lương cơ bản:
=XLOOKUP(B2, NhanSu!$A:$A, NhanSu!$D:$D, 0)
B2là Mã NV.- Nếu không tìm thấy, trả về 0 (có thể dùng
IFERROR).
5.3. Tính tiền overtime
Giả sử cột AD là Tiền OT:
=SUM(AA2:...) (XLOOKUP(B2, NhanSu!$A:$A, NhanSu!$E:$E, 0)) (AC2/ (822)) // 22 ngày làm việc trung bình
Giải thích:
SUM(AA2:…)là tổng giờ OT trong tháng.- Hệ số OT lấy từ bảng nhân sự.
AC2/ (822)tính mức lương giờ (lương cơ bản chia cho số giờ chuẩn trong tháng).
5.4. Tổng lương tháng
Cột AE (Tổng Lương):
=AC2 + AD2 // Lương cơ bản + tiền OT
Bạn có thể mở rộng để tính phụ cấp ăn trưa, bảo hiểm, khấu trừ bằng cách thêm các cột và công thức tương tự.
6. Báo cáo tổng hợp và phân tích dữ liệu
6.1. PivotTable (Bảng tổng hợp)
- Chọn toàn bộ dữ liệu (cột A đến AE).
- Insert → PivotTable, đặt vào sheet mới (
Report). - Fields:
- Rows:
Phòng/Ban - Columns:
Tháng(nếu có nhiều tháng) - Values:
Tổng giờ,Tổng OT,Tổng Lương - Filter:
Ngày nghỉ,Nghỉ bệnhđể phân tích tỉ lệ vắng mặt.
PivotTable cho phép điều chỉnh nhanh, lấy insight như phòng nào có overtime cao, mức lương trung bình, …
6.2. Đồ thị trực quan
- Column Chart cho tổng giờ làm việc theo phòng.
- Line Chart cho xu hướng overtime tháng.
- Pie Chart cho tỉ lệ ngày nghỉ lễ vs ngày làm.
Sử dụng Insert → Chart, chọn dữ liệu từ PivotTable để giữ cho biểu đồ luôn cập nhật khi dữ liệu thay đổi.
7. Bảo mật và chia sẻ bảng chấm công
7.1. Mật khẩu bảo vệ sheet
- Review → Protect Sheet: Đặt mật khẩu để ngăn người dùng chỉnh sửa công thức.
- Chỉ cho phép Edit Cells ở cột Giờ vào/ra và Ghi chú.
7.2. Quyền truy cập qua OneDrive/SharePoint
- Upload file lên OneDrive hoặc SharePoint.
- Set permissions:
- View only cho quản lý cấp cao.
- Edit cho phòng nhân sự.
- Comment cho trưởng bộ phận khi cần duyệt.
7.3. Lưu phiên bản (Version History)
Kích hoạt Version History để có thể quay lại phiên bản trước nếu có lỗi nhập liệu.
8. Các lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #VALUE! trong cột số giờ | Định dạng thời gian không đúng (ví dụ: nhập “8h” thay vì “08:00”) | Định dạng ô thành Time và nhập theo hh:mm. |
| Giờ OT tính âm | Nhân viên làm ít hơn 8h, công thức không dùng MAX(0,…) | Sửa công thức thành =MAX(0, G2-8) |
| Tổng lương sai | Công thức lấy lương cơ bản sai (mã NV trùng hoặc không tồn tại) | Kiểm tra bảng “NhanSu”, dùng IFERROR để bắt lỗi. |
| Conditional Formatting không áp dụng | Tham chiếu ngày không khớp (định dạng ngày khác) | Đảm bảo ngày trong tiêu đề và danh sách lễ cùng định dạng dd/mm/yyyy. |
| PivotTable không cập nhật | Dữ liệu gốc thay đổi nhưng không refresh | Click chuột phải vào PivotTable → Refresh. |
9. Mẫu bảng chấm công mẫu (đính kèm)
Dưới đây là định dạng mẫu mà bạn có thể sao chép ngay vào Excel:
A1: BẢNG CHẤM CÔNG THÁNG
A2: Tháng: 05/2026
A3: Đơn vị: Công ty ABC
A4: Ngày tạo: 21/06/2026 Cột:
A: STT
B: Mã NV
C: Họ và Tên
D: Phòng/Ban
E: 01 (Giờ vào)
F: 01 (Giờ ra)
G: 01 (Số giờ)
H: 01 (OT)
I: 02 (Giờ vào)
J: 02 (Giờ ra)
K: 02 (Số giờ)
L: 02 (OT)
...
Z: Tổng giờ
AA: Tổng OT
AB: Lương cơ bản
AC: Tiền OT
AD: Tổng Lương
Sau khi nhập công thức (theo phần 3.4 và 4.2), bạn chỉ cần sao chép xuống cho toàn bộ nhân viên.
10. Kết luận
Mẫu bảng chấm công Excel không chỉ là công cụ ghi nhận thời gian làm việc mà còn là nền tảng cho quản lý nhân sự, tính lương, và phân tích hiệu suất. Bằng cách:
- Xây dựng cấu trúc dữ liệu rõ ràng.
- Áp dụng công thức tự động tính giờ, overtime, và lương.
- Sử dụng PivotTable & Chart để báo cáo nhanh.
- Bảo mật và chia sẻ đúng cách.
Bạn sẽ có một hệ thống chấm công đáng tin cậy, tiết kiệm thời gian và giảm thiểu sai sót. Hãy tùy chỉnh mẫu này sao cho phù hợp với quy trình và quy định của doanh nghiệp mình, và đừng quên cập nhật các quy định pháp luật về thời gian làm việc để luôn duy trì tính hợp pháp.
“Quản lý thời gian là quản lý hiệu suất, và một bảng chấm công chuẩn sẽ là chìa khóa mở ra cánh cửa thành công cho doanh nghiệp.”
Chúc bạn thành công trong việc triển khai và vận hành mẫu bảng chấm công Excel!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.