Cách Làm Bảng Chấm Công Trên Excel Theo Tháng: Công Thức Đếm Công, Phép, Tăng Ca (2026)

Trả lời nhanh: Bảng chấm công trên Excel gồm danh sách nhân viên, lưới 31 cột ngày và các cột tổng hợp. Mỗi ô ngày chỉ nhập một ký hiệu (x đi làm, N nửa ngày, P phép, L lễ, KP nghỉ không phép), rồi dùng COUNTIF để đếm, ví dụ =COUNTIF(D7:AH7,"x")+COUNTIF(D7:AH7,"N")*0.5. Thêm danh sách chọn bằng Data Validation và tô màu thứ Bảy, Chủ nhật bằng Conditional Formatting là bảng đã dùng được cho văn phòng dưới 20 người.

Bài này dựng từng bước một bảng chấm công theo tháng, có ô tháng tự đổi ngày, công thức đếm công và cách nối sang tính lương theo công. Mọi ô và công thức bên dưới dùng đúng địa chỉ cụ thể để bạn gõ lại được ngay, không cần tải file.

Bảng chấm công trên Excel cần những cột nào?

Một bảng chấm công theo tháng thường có ba khối: thông tin nhân viên ở bên trái, lưới ngày ở giữa và các cột tổng hợp ở bên phải. Quy ước vị trí dưới đây sẽ dùng xuyên suốt bài, nên nếu bạn bố trí khác thì chỉ cần đổi địa chỉ ô tương ứng trong công thức.

Vị trí	Nội dung
A1	Tiêu đề, ví dụ "BẢNG CHẤM CÔNG"
A2 và B2	Chữ "Tháng" và ngày đầu của tháng, ví dụ =DATE(2026,10,1)
A3 và B3	Chữ "Công chuẩn" và số ngày công chuẩn của tháng, ví dụ 26
Hàng 5, cột D đến AH	31 ô ngày (từ ngày 1 đến ngày 31)
Hàng 6	Thứ trong tuần dưới mỗi ngày, và tiêu đề các cột A, B, C và AI trở đi
Cột A, B, C	STT, Mã nhân viên, Họ tên (từ hàng 7)
Hàng 7 đến 26	Mỗi nhân viên một hàng, tối đa 20 người
Cột AI đến AO	Công, Phép, Lễ, Nghỉ KP, Công tính lương, Lương cơ bản, Lương theo công

Nên để mỗi tháng một sheet riêng (Tháng 10, Tháng 11…) thay vì dồn cả năm vào một sheet. Sheet nhẹ hơn, dễ lọc và tháng cũ không bị công thức mới làm đổi số.

Dựng hàng ngày và hàng thứ tự đổi theo tháng như thế nào?

Thay vì gõ tay 1, 2, 3… rồi sửa lại mỗi tháng, hãy để các ngày tính từ ô B2. Gõ vào các ô sau rồi kéo sang phải đến cột AH:

D5:  =$B$2
E5:  =IF(D5="","",IF(MONTH(D5+1)=MONTH($B$2),D5+1,""))   (kéo từ E5 đến AH5)
D6:  =IF(D5="","",CHOOSE(WEEKDAY(D5,2),"T2","T3","T4","T5","T6","T7","CN"))   (kéo từ D6 đến AH6)

Công thức ở E5 cộng thêm một ngày nếu ngày đó vẫn thuộc cùng tháng với B2, nếu không thì để trống, và ô trống kéo theo các ô bên phải cũng trống. Nhờ vậy tháng 30 ngày hay tháng 28 ngày thì các cột thừa tự rỗng. Công thức ở hàng 6 dùng WEEKDAY(D5,2) (thứ Hai là 1, Chủ nhật là 7) kết hợp CHOOSE nên cho kết quả T2 đến CN giống nhau trên mọi máy, không phụ thuộc ngôn ngữ cài đặt của Excel như hàm TEXT.

Định dạng D5:AH5 thành kiểu hiển thị chỉ số ngày: chọn vùng, nhấn Ctrl+1, mục Custom, gõ d. Sang tháng mới, chỉ cần sửa B2 (ví dụ =DATE(2026,11,1)), cả hai hàng ngày và thứ đổi theo.

Nên dùng những ký hiệu chấm công nào?

Ký hiệu càng ít và càng ngắn thì càng ít người nhập sai. Một bộ năm ký hiệu đủ cho phần lớn văn phòng nhỏ:

Ký hiệu	Ý nghĩa	Cách tính trong bảng
x	Đi làm đủ ngày	Tính 1 công
N	Làm nửa ngày	Tính 0,5 công
P	Nghỉ phép	Đếm vào cột Phép
L	Nghỉ lễ	Đếm vào cột Lễ
KP	Nghỉ không phép	Đếm vào cột Nghỉ KP, không tính công

Hãy ghi bảng ký hiệu này ngay ở đầu sheet để ai nhập cũng nhìn thấy. Việc nghỉ phép hay nghỉ lễ có được tính lương hay không do quy chế công ty và hợp đồng lao động quyết định, bài này chỉ hướng dẫn cách đếm và cộng trong Excel.

Công thức tổng hợp công, phép, lễ và nghỉ không phép là gì?

Với nhân viên đầu tiên ở hàng 7, các ô tổng hợp ở bên phải lưới ngày dùng COUNTIF để đếm số ô chứa đúng một ký hiệu:

AI7:  =COUNTIF(D7:AH7,"x")+COUNTIF(D7:AH7,"N")*0.5      (Công)
AJ7:  =COUNTIF(D7:AH7,"P")                              (Phép)
AK7:  =COUNTIF(D7:AH7,"L")                              (Lễ)
AL7:  =COUNTIF(D7:AH7,"KP")                             (Nghỉ không phép)
AM7:  =AI7+AJ7+AK7                                      (Công tính lương)

Chọn AI7:AM7 rồi kéo xuống hàng 26 để áp dụng cho cả 20 nhân viên. Công thức ở AM7 giả định phép và lễ vẫn được tính lương, nếu công ty bạn quy định khác thì sửa lại phép cộng này cho đúng quy chế.

Muốn biết mỗi ngày có bao nhiêu người đi làm, thêm một hàng tổng ở hàng 27 rồi gõ vào D27 và kéo sang phải:

D27:  =COUNTIF(D7:D26,"x")+COUNTIF(D7:D26,"N")*0.5

COUNTIF không phân biệt chữ hoa và chữ thường, nên "X" và "x" đều được đếm. Điều nó không bỏ qua là dấu cách: ô chứa "x " (có khoảng trắng phía sau) sẽ không khớp, đó là lý do nên dùng danh sách chọn ở mục tiếp theo.

Tạo danh sách chọn ký hiệu và tô màu cuối tuần ra sao?

Danh sách chọn giúp mỗi ô chỉ nhận đúng năm ký hiệu. Chọn vùng D7:AH26, vào tab Data, chọn Data Validation, ở mục Allow chọn List, rồi gõ vào ô Source: x,N,P,L,KP. Nếu Excel báo lỗi vì cài đặt khu vực, hãy dùng dấu chấm phẩy thay cho dấu phẩy: x;N;P;L;KP.

Để tô màu thứ Bảy và Chủ nhật, chọn vùng D5:AH26, vào Home, chọn Conditional Formatting, New Rule, Use a formula to determine which cells to format, rồi nhập:

=OR(D$6="T7",D$6="CN")

Chọn một màu nền nhạt trong Format rồi bấm OK. Dấu $ đứng trước số 6 giữ nguyên hàng thứ, còn cột D thay đổi theo từng ô nên mỗi cột lấy đúng thứ của chính nó. Có thể thêm một quy tắc nữa cho cột Nghỉ KP (AL7:AL26) để tô đỏ khi số ngày vượt ngưỡng do công ty đặt.

Tính lương theo công và tiền tăng ca từ bảng chấm công thế nào?

Khi đã có cột Công tính lương, bước nối sang lương chỉ là một phép nhân. Thêm các cột sau vào bên phải:

AN7:  Lương cơ bản (nhập tay)
AO7:  =ROUND(AN7/$B$3*AM7,0)                 (Lương theo công)
AP7:  Giờ tăng ca (nhập tay)
AQ7:  Hệ số tăng ca (nhập tay, theo quy chế công ty)
AR7:  =ROUND(AN7/$B$3/8*AP7*AQ7,0)           (Tiền tăng ca)
AS7:  =AO7+AR7                               (Tổng trước khấu trừ)

Ví dụ minh họa với số liệu giả định: lương cơ bản 8.000.000đ, công chuẩn 26 ngày. Trong tháng nhân viên A có 22 ngày "x", 2 ngày "N", 1 ngày "P", 6 giờ tăng ca với hệ số giả định 1,5.

Ô	Cách ra số	Kết quả
AI7 (Công)	22 + 2 × 0,5	23
AM7 (Công tính lương)	23 + 1 phép + 0 lễ	24
AO7 (Lương theo công)	8.000.000 ÷ 26 × 24	7.384.615đ
AR7 (Tiền tăng ca)	8.000.000 ÷ 26 ÷ 8 × 6 × 1,5	346.154đ
AS7 (Tổng)	7.384.615 + 346.154	7.730.769đ

Hàm ROUND làm tròn về đồng để tránh các số lẻ thập phân khi cộng nhiều nhân viên. Con số này mới là lương trước các khoản khấu trừ; hệ số tăng ca, công chuẩn và phần khấu trừ phải theo quy chế công ty và quy định hiện hành, bài này không bàn thuế hay bảo hiểm.

Những lỗi hay gặp khi làm bảng chấm công trên Excel là gì?

• Ký hiệu nhập không thống nhất. Người gõ "x ", người gõ "v", người gõ dấu tích khiến COUNTIF bỏ sót. Dùng danh sách chọn ở mục 5 là cách sửa tận gốc.
• Gộp ô (Merge) trong vùng dữ liệu. Ô gộp làm lệch phạm vi công thức và gây lỗi khi lọc hoặc sắp xếp. Chỉ nên căn giữa tiêu đề, không gộp vùng nhập.
• Gõ đè lên cột tổng. Ai đó nhập tay số công vào AI7 thì công thức mất. Hãy mở khóa vùng D7:AH26 (Format Cells, tab Protection, bỏ chọn Locked) rồi bật Review, Protect Sheet để chỉ vùng nhập ký hiệu sửa được.
• Quên cập nhật công chuẩn. Mỗi tháng số ngày công chuẩn khác nhau; nếu B3 giữ nguyên thì lương theo công lệch. Tạo thói quen sửa B2 và B3 cùng lúc khi sang tháng.
• Dồn cả năm vào một sheet. File chậm, khó đối chiếu và tháng cũ dễ bị sửa nhầm. Sao chép sheet tháng cũ thành sheet mới, sửa B2, B3 và xóa các ký hiệu cũ.
• Công thức báo lỗi vì dấu phân cách. Một số máy cài khu vực dùng dấu chấm phẩy giữa các đối số. Nếu =COUNTIF(D7:AH7,"x") báo lỗi, thử =COUNTIF(D7:AH7;"x").

Khi nào nên dùng file chấm công làm sẵn?

Với dưới 20 nhân viên, bảng tự dựng ở trên là đủ và bạn nắm hoàn toàn công thức. Việc dựng sẽ tốn thời gian nhất ở phần phiếu lương, báo cáo theo tháng và theo dõi tạm ứng, nên nếu không muốn dựng thì có ba hướng làm sẵn:

• Template chấm công nhân viên hằng tháng là mẫu Google Sheets miễn phí có chấm công hằng ngày, giờ làm thêm, nghỉ phép, đi trễ về sớm và tổng hợp công tháng. Phù hợp để xem cách người khác bố trí rồi chỉnh theo ý mình.
• File Excel làm sẵn Chấm công và quản lý lương bằng Excel dành cho văn phòng từ 1 đến 20 nhân viên, mở lên dùng ngay, không cần cài đặt hay Internet.
• Nếu cần nhiều người dùng và phân quyền, phần mềm quản lý nhân sự và tính lương trên Google Sheets có chấm công check-in, check-out theo giờ thực, phân quyền Admin, HR, nhân viên và gửi phiếu lương qua email.

Phần mềm nhân sự trên Google Sheets không có bản dùng thử, vì vậy nên đọc kỹ trang sản phẩm trước khi mua. So sánh giữa chấm công trên Google Sheets và chấm công giấy có ở bài Google Sheets hay bảng chấm công giấy.

Câu hỏi thường gặp

Bảng chấm công Excel có tính được tăng ca không?

Có. Thêm hai cột nhập tay là giờ tăng ca và hệ số tăng ca, rồi tính tiền bằng công thức =ROUND(Lương cơ bản / Công chuẩn / 8 * Giờ tăng ca * Hệ số, 0). Hệ số phải lấy theo quy chế công ty, bài này chỉ minh họa cách tính trong Excel.

Vì sao COUNTIF không đếm hết ký hiệu trong bảng chấm công?

Thường do ô chứa thêm dấu cách (ví dụ "x ") hoặc nhân viên gõ ký hiệu khác như v, √, X có dấu cách. COUNTIF chỉ đếm ô khớp hoàn toàn với ký hiệu, không phân biệt chữ hoa chữ thường. Cách sửa gọn nhất là dùng danh sách chọn bằng Data Validation để mọi ô chỉ nhận đúng ký hiệu đã quy định.

Làm sao để bảng chấm công tự đổi ngày khi sang tháng mới?

Chỉ cần sửa ô ngày đầu tháng (B2, ví dụ =DATE(2026,11,1)). Hàng ngày và hàng thứ đều tính từ ô này nên tự đổi theo, kể cả tháng 28, 29 hay 30 ngày vì các cột ngày thừa sẽ để trống. Nên sao chép sheet tháng cũ thành sheet mới rồi sửa B2 và xóa ký hiệu cũ, để vẫn giữ lại lịch sử từng tháng.

Có dùng bảng chấm công này trên Google Sheets được không?

Được. COUNTIF, CHOOSE, WEEKDAY, DATE và ROUND hoạt động giống hệt trong Google Sheets. Chỗ khác duy nhất là danh sách chọn nằm ở menu Dữ liệu > Xác thực dữ liệu, và định dạng có điều kiện nằm ở Định dạng > Định dạng có điều kiện. Nếu công thức báo lỗi, hãy thử đổi dấu phẩy giữa các đối số thành dấu chấm phẩy.