Cách làm bảng chấm công trên Excel tự đổi theo tháng
Mục lục (12 phần)
Mỗi đầu tháng, nhiều phòng hành chính lại ngồi tạo bảng chấm công: gõ ngày, gõ thứ, tô cuối tuần, chép công thức. Làm một lần cho đúng thì những tháng sau chỉ cần đổi một ô. Bài này hướng dẫn cách làm bảng chấm công trên Excel tự đổi theo tháng, tự đếm công, tự tô cuối tuần.
Bảng chấm công trên Excel cần những gì?
| Phần | Vị trí trong bài | Tác dụng |
|---|---|---|
| Ô năm, ô tháng | B3, B4 | Chỉ đổi hai ô này khi sang tháng mới |
| Công chuẩn | B5 | Tự tính từ tháng |
| Hàng ngày, hàng thứ | Dòng 6, 7 | Tự sinh |
| Vùng chấm công | E8 trở đi | Chọn ký hiệu từng ngày |
| Cột tổng | Cuối mỗi dòng | Tự đếm theo ký hiệu |
Trong bài dùng bốn ký hiệu: X là đi làm đủ ngày, N là nửa ngày, P là nghỉ phép có lương, K là nghỉ không lương. Công ty bạn có ký hiệu khác thì đổi theo. Cách tính công và lương nên theo đúng quy chế của công ty.
Bước 1: tạo lịch tự đổi theo tháng
Ô E6 là ngày đầu tháng:
=DATE($B$3,$B$4,1)
Định dạng ô bằng mã dd để chỉ hiện ngày. Ô F6 lấy ô trước cộng 1, sang tháng khác thì để trống:
=IF(E6="","",IF(MONTH(E6+1)=$B$4,E6+1,""))
Kéo F6 sang phải đến cột AI, tức đủ 31 ô. Tháng 2 chỉ có 28 hoặc 29 ngày, các ô thừa tự để trống.
Bước 2: hiện thứ bằng tiếng Việt
=IF(E6="","",CHOOSE(WEEKDAY(E6,2),"T2","T3","T4","T5","T6","T7","CN"))
WEEKDAY với kiểu 2 cho Thứ Hai là 1. CHOOSE đổi số thành chữ. Cách này cho kết quả giống nhau ở mọi máy, không phụ thuộc ngôn ngữ của Windows.
Bước 3: danh sách thả xuống để nhập không sai
Chọn vùng chấm công, vào Data, Data Validation, chọn List, ô Source gõ X,N,P,K. Từ giờ ô chỉ nhận đúng bốn ký hiệu, không còn những lỗi như gõ chữ X có dấu cách ở sau khiến công thức đếm thiếu.
Bước 4: tô màu cuối tuần tự động
Chọn vùng từ E6 đến hết bảng, vào Conditional Formatting, New Rule, Use a formula, nhập:
=AND(ISNUMBER(E$6),WEEKDAY(E$6,2)>=6)
Dấu $ trước số 6 giữ mọi dòng cùng nhìn lên hàng ngày. Chọn nền xám. Đổi sang tháng khác, màu tự chạy đúng.
Bước 5: đếm công và tính lương theo công
Giả sử cột AJ đếm X, AK đếm N, AL đếm P, AM đếm K, AN là tổng công:
=COUNTIF($E8:$AI8,"X")
=AJ8+0.5*AK8+AL8
Khoá cột E đến AI để khi kéo ngang sang cột đếm khác, vùng không bị trượt; kéo xuống thì số dòng tự đổi. Công chuẩn ở B5 tính bằng NETWORKDAYS:
=NETWORKDAYS(DATE(B3,B4,1),EOMONTH(DATE(B3,B4,1),0))
Công ty làm cả Thứ Bảy thì dùng NETWORKDAYS.INTL với mã 11 để chỉ nghỉ Chủ nhật. Lương theo công ở ví dụ của bài:
=ROUND($D8/$B$5*AN8,-3)
Ví dụ giả định: nhân viên lương 11.500.000 đồng, tháng có 22 công chuẩn, đi 20,5 công thì lương theo công là 10.716.000 đồng, làm tròn đến nghìn. Đây chỉ là ví dụ về cách viết công thức. Cách tính lương thực tế theo quy chế của công ty và quy định pháp luật hiện hành.
Những lỗi hay gặp
- Tháng 2 vẫn hiện ngày 30, 31thiếu điều kiện MONTH ở ô F6.
- Kéo ngang công thức đếm bị lệchchưa khoá cột bằng dấu $.
- Chỉ dòng ngày được tô, các dòng dưới không tôthiếu dấu $ trước số dòng 6 trong quy tắc định dạng.
- Có người vô tình xoá công thứcmở khoá vùng chấm công, bật Protect Sheet.
Làm mẫu một dòng để kiểm công thức
Trước khi kéo công thức cho cả bảng, hãy đếm tay một dòng để chắc công thức đúng. Ví dụ giả định một nhân viên trong tháng có 22 ngày làm việc như sau:
| Ký hiệu | Số ngày | Được tính |
|---|---|---|
| X (đủ ngày) | 19 | 19 |
| N (nửa ngày) | 2 | 1 |
| P (phép có lương) | 1 | 1 |
| K (không lương) | 0 | 0 |
| Tổng công | 22 ngày có mặt trong bảng | 21 |
Nếu cột tổng công của dòng đó ra đúng 21 thì mới kéo xuống. Thói quen này mất chưa đến một phút nhưng phát hiện được hầu hết lỗi viết công thức.
Sang tháng mới làm thế nào cho nhanh?
- Bấm chuột phải vào thẻ trang tháng cũ, chọn Move or Copy, đánh dấu Create a copy.
- Đổi tên trang mới theo tháng, ví dụ 2026-11.
- Đổi ô tháng ở B4 từ 10 thành 11. Ngày, thứ, công chuẩn, màu cuối tuần tự cập nhật.
- Chọn vùng chấm công, bấm Delete để xoá ký hiệu cũ. Chỉ xoá vùng ký hiệu, đừng xoá cột tổng.
Thêm dòng kiểm tra cho cả bảng
Ngoài cột tổng cho từng người, nên có một dòng tổng ở dưới cùng để đếm số người có mặt theo từng ngày. Ô dưới cột ngày 1 viết =COUNTIF(E8:E19,"X")+COUNTIF(E8:E19,"N"), rồi kéo sang phải. Ngày nào số người có mặt thấp bất thường thì đó thường là ngày chấm thiếu, cần hỏi lại trước khi chốt công.
Một phép kiểm khác là đếm ô trống trong những ngày làm việc của mỗi dòng. Nếu số ô có ký hiệu của một người ít hơn công chuẩn thì có thể bạn đã bỏ sót ngày:
=COUNTA($E8:$AI8)-$B$5
Kết quả âm nghĩa là còn ngày chưa chấm. Với người đi làm cả ngày nghỉ, kết quả có thể dương. Đây chỉ là tín hiệu để xem lại, không phải kết luận.
Chống sửa nhầm và giữ dữ liệu riêng tư
Bảng chấm công thường có cột lương, nên cần cẩn thận khi chia sẻ. Trước khi gửi cho các trưởng phòng chấm công, nên làm ba việc:
- Chỉ mở khoá vùng ký hiệu, rồi bật Protect Sheet để không ai gõ đè lên công thức.
- Tách cột lương ra một tệp riêng cho phòng nhân sự, kế toán. Ẩn cột không phải là cách an toàn, vì ai cũng có thể hiện lại.
- Mỗi phòng chỉ nhận danh sách của phòng mình.
Khi cần gửi tệp có lương qua thư điện tử, hãy mã hoá bằng File, Info, Protect Workbook, Encrypt with Password và gửi mật khẩu qua một kênh khác. Protect Sheet chỉ chống sửa nhầm, không có tác dụng bảo mật.
Cuối cùng, hãy giữ lại một bản đã chốt của mỗi tháng và không sửa vào bản đó nữa. Khi có người thắc mắc về công của tháng trước, bạn mở đúng bản đã chốt để đối chiếu, thay vì phải đoán xem ai đã sửa gì và sửa vào lúc nào.
Câu hỏi thường gặp
Bảng chấm công có dùng được cho nhiều tháng không?
Có. Mỗi tháng sao chép trang tính, đổi ô năm và tháng, xoá ký hiệu cũ. Đặt tên trang theo tháng, ví dụ 2026-10.
Làm sao tính ngày nghỉ lễ?
Thêm một ký hiệu riêng, ví dụ L, cộng vào công thức tổng công, và đưa danh sách ngày lễ vào đối số thứ ba của NETWORKDAYS nếu quy chế công ty trừ chúng khỏi công chuẩn.
Có mẫu sẵn để dùng không?
Khoá Excel văn phòng từ cơ bản đến thành thạo của Vietdemy có bộ mẫu chấm công, theo dõi đơn hàng và báo cáo tuần, do Vietdemy biên soạn, đã gắn sẵn công thức.
Công ty và số liệu trong bài là giả định để minh hoạ.












