Tri ân 20/11: tặng thầy cô khoá học, giảm 10%Mã TRIAN10Đến hết 22/11/2026Xem ưu đãi
Kiểm tra trình độGói thành viên

Cách làm bảng chấm công trên Excel tự đổi theo tháng

Vietdemy6 phút đọc

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ì?

Bảng chấm công mẫu với hàng ngày, hàng thứ và cột tổng công
PhầnVị trí trong bàiTác dụng
Ô năm, ô thángB3, B4Chỉ đổi hai ô này khi sang tháng mới
Công chuẩnB5Tự tính từ tháng
Hàng ngày, hàng thứDòng 6, 7Tự sinh
Vùng chấm côngE8 trở điChọn ký hiệu từng ngày
Cột tổngCuối mỗi dòngTự đế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, 31
    thiếu điều kiện MONTH ở ô F6.
  • Kéo ngang công thức đếm bị lệch
    chư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ức
    mở 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ệuSố ngàyĐược tính
X (đủ ngày)1919
N (nửa ngày)21
P (phép có lương)11
K (không lương)00
Tổng công22 ngày có mặt trong bảng21

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?

  1. Bấm chuột phải vào thẻ trang tháng cũ, chọn Move or Copy, đánh dấu Create a copy.
  2. Đổi tên trang mới theo tháng, ví dụ 2026-11.
  3. Đổ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.
  4. 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ạ.

Bản tin Vietdemy

Nhận bài viết mới theo chủ đề bạn chọn

Chọn chủ đề bạn quan tâm - Vietdemy gửi bài viết mới, kinh nghiệm học và ưu đãi khoá học đúng chủ đề đó.

  • Bài viết chọn lọc về GMAT, GRE, TOEFL, TOEIC, Marketing và du học
  • Biết sớm mã ưu đãi và khoá học miễn phí mới
  • Xác nhận qua email, huỷ nhận chỉ với một lần bấm

Chủ đề quan tâm (chọn một hoặc nhiều)