Cách Làm Bảng Chấm Công Trên Máy Tính: Hướng dẫn toàn diện cho Excel và Word, đủ mọi loại theo giờ, theo ca, theo ngày

Bảng chấm công chuẩn giúp doanh nghiệp theo dõi thời gian làm việc, nghỉ phép, làm thêm giờ, ca kíp và tự động hóa tính lương. Trên máy tính, bạn có thể thiết kế bảng chấm công bằng Excel để tính toán tự động hoặc dùng Word để in ấn và lưu hồ sơ dễ dàng. Bài viết này cung cấp quy trình chi tiết, công thức mẫu, mẹo xử lý dữ liệu từ máy chấm công và những lỗi thường gặp để bạn vận hành hệ thống chấm công hiệu quả.

Mẫu bảng chấm công tự động trong Excel với công thức và định dạng trực quan

Để tối ưu vận hành, hãy chuẩn hóa cấu trúc dữ liệu, mã nhân viên, quy tắc chấm công (ngày thường, cuối tuần, ngày lễ), ca làm việc (sáng, chiều, đêm) và chính sách tăng ca. Sau đó, liên kết bảng chấm công với bảng lương để tổng hợp công cuối tháng và tính thu nhập, phụ cấp, khấu trừ một cách nhất quán.

Chuẩn bị dữ liệu và thiết kế bảng chấm công theo tháng

Bạn nên tách 3 phần chính trong file Excel:

• Sheet Cấu_hình: lịch tháng, danh mục ngày nghỉ lễ, hệ số lương OT, danh sách ca kíp.
• Sheet Danh_sach_NV: mã nhân viên, họ tên, phòng ban, chức danh, hệ số công.
• Sheet Bang_cong: khung chấm công theo tháng (cột nhân viên, cột ngày 1…31, hàng tổng giờ, tổng ngày công, tăng ca).

Khung chấm công tối thiểu cần có: cột mã NV, họ tên, 31 cột ngày, dòng tổng hợp (tổng giờ, tổng công, tổng OT), cột ghi chú. Với doanh nghiệp có ca đêm, thêm cột “Qua đêm” và dùng công thức xử lý giờ qua ngày.

Bảng chấm công theo tháng chuẩn, cột ngày và thứ được sắp xếp rõ ràng

Khi thiết kế, hãy cố định hàng tiêu đề, dùng Data Validation cho mã chấm công (VD: X – đi làm, P – phép, CT – công tác, OT – tăng ca, KL – không lương), và tạo Conditional Formatting để tô màu cuối tuần, ngày lễ hoặc cảnh báo thiếu công.

Các bước tạo bảng chấm công trong Excel (tự động, linh hoạt)

• Tạo vùng chọn tháng/năm: nhập Tháng, Năm tại ô cấu hình; dùng tên vùng (Named Range) để tham chiếu.
• Sinh dãy ngày theo tháng: ở hàng ngày, dùng công thức động:
  • =SEQUENCE(DAY(EOMONTH(DATE($Năm;$Tháng;1);0));1;DATE($Năm;$Tháng;1);1)
• Hiển thị thứ trong tuần:
  • Ô dưới ngày: =TEXT([ô ngày];”ddd”) hoặc CHOOSE(WEEKDAY([ô ngày];2);”T2″;”T3″;”T4″;”T5″;”T6″;”T7″;”CN”)
• Tô màu cuối tuần:
  • Công thức CF: =WEEKDAY([ô ngày];2)>=6
• Tạo danh sách mã chấm công (X, P, CT, OT, KL…):
  • Data Validation > List > tham chiếu vùng mã.
• Khóa công thức và bảo vệ sheet, cho phép nhập ô mã chấm công.

Sau khi hoàn thiện khung, nhân bản mẫu cho từng phòng ban hoặc lọc theo mã NV. Nên thêm khu vực tổng hợp: Tổng ngày công chuẩn, Tổng giờ làm, Tổng giờ OT, Số ngày nghỉ phép còn lại.

Bảng công Excel gồm nhiều sheet: danh sách nhân viên, cấu hình và chấm công

Khuyến nghị dùng Table (Ctrl+T) cho vùng dữ liệu để công thức và định dạng tự mở rộng khi thêm nhân viên mới. Sử dụng Power Query/PivotTable để tổng hợp theo phòng ban, dự án.

Tự động tính giờ làm việc, đi trễ/về sớm, tăng ca

Nếu có giờ vào/ra, lưu lần quẹt đầu tiên là “Vào” và lần quẹt cuối cùng trong ngày là “Ra”. Tổng giờ làm:

• Tổng giờ = (Ra – Vào) × 24
• Qua đêm (vào hôm trước, ra hôm sau): Tổng giờ = MOD(Ra – Vào;1) × 24
• Đi trễ: MAX(0; Vào – Giờ_bắt_đầu) × 24
• Về sớm: MAX(0; Giờ_kết_thúc – Ra) × 24
• OT ngày thường: MAX(0; Tổng giờ – 8)
• OT cuối tuần/ngày lễ: toàn bộ giờ làm × Hệ số (VD: Chủ nhật 200%; Lễ 300%)

Cách tính số giờ làm việc từ dữ liệu máy chấm công trong bảng Excel

Để phân loại ngày thường/cuối tuần/ngày lễ, tạo cột Kiểu ngày:

• Ngày lễ: MATCH([Ngày]; Danh_sach_le;0) > 0
• Cuối tuần: WEEKDAY([Ngày];2)>=6
• Ngày thường: còn lại
  Sau đó, áp dụng hệ số lương cho từng kiểu ngày trong tính OT.

Kết nối dữ liệu từ máy chấm công vào Excel bằng Power Query

• Lấy file xuất máy chấm công (CSV/XLSX).
• Power Query: Data > Get Data > From Text/CSV.
• Chuẩn hóa cột: Mã NV, Họ tên, Ngày, Giờ quẹt, Loại (In/Out).
• Nhóm theo Mã NV + Ngày, tạo cột Vào = Min(Giờ), Ra = Max(Giờ).
• Tính Tổng giờ = Duration.Hours(Ra – Vào) hoặc chuyển sang số giờ × 24.
• Load về sheet Bang_du_lieu; dùng SUMIFS/VLOOKUP/XLOOKUP để đẩy giờ sang Bang_cong.

Cấu trúc bảng chấm công và dữ liệu đầu vào từ máy chấm công

Nếu cần chi tiết theo phiên quẹt (nhiều lần trong ngày), dùng Group By nâng cao và quy tắc loại bỏ quẹt dư (chỉ lấy lần đầu/cuối hoặc ghép cặp theo thứ tự In–Out).

Ký hiệu chấm công và Data Validation chuẩn HR

Quy ước phổ biến:

• X: Đi làm đủ
• P: Nghỉ phép hưởng lương
• KL: Nghỉ không lương
• CT: Công tác
• WFH: Làm việc từ xa
• OT: Tăng ca
• B: Nghỉ bệnh; TS: Thai sản; Ro: Nghỉ có lý do
  Thiết lập Data Validation dạng List cho các mã, kèm mô tả bên cạnh. Tạo Conditional Formatting để đổi màu theo mã, giúp kiểm soát nhanh tình trạng công.

Bộ ký hiệu chấm công: đi làm, nghỉ phép, tăng ca, công tác

Nên có cột “Giải thích” cho các mã đặc thù (VD: OT: 1.5x ngày thường, 2.0x Chủ nhật, 3.0x ngày lễ) và liên kết tới sheet Cấu_hình.

Mẫu bảng chấm công theo giờ, theo ca, theo ngày

• Theo giờ: nhập giờ Vào/Ra, tổng giờ tự tính; phù hợp part-time, thời vụ.
• Theo ca: đăng ký ca (Sáng/Chiều/Đêm), quy tắc OT theo ca; xử lý ca qua đêm bằng MOD.
• Theo ngày: nhập mã X/P/CT…, tổng ngày công dựa trên quy ước: X=1, P=1 (hoặc 0.5), KL=0.

Mẫu bảng chấm công theo ca trong Excel có phân ca sáng, chiều, đêm

Với ca đêm (22:00–06:00), công thức tổng giờ:

• =MOD(Ra – Vào;1) × 24
  Kết hợp quy tắc cộng thêm phụ cấp ca đêm theo chính sách doanh nghiệp.

Tạo bảng chấm công trong Word để in ấn nhanh

• Insert > Table theo số cột/hàng cần thiết (Mã NV, Họ tên, 1…31, Tổng).
• Layout > AutoFit > Fixed Column Width để cột đều nhau.
• Table Properties > Row > Repeat as header row để lặp tiêu đề mỗi trang.
• Design > Borders để kẻ ô rõ ràng; dùng Styles cho tiêu đề, chữ ký.

Tạo bảng chấm công trong Word với bảng biểu và tiêu đề cố định

Word phù hợp cho biểu mẫu nộp phòng nhân sự, ký duyệt; Excel vẫn là nơi tính toán tự động và tổng hợp dữ liệu.

Tổng hợp công cuối tháng và liên kết bảng lương

• Số ngày công: SUMPRODUCT chuyển mã sang hệ số công (VD: X=1; P=1; 0.5P=0.5).
• Tổng giờ: SUMIFS theo Mã NV và khoảng ngày.
• Tổng OT: tách theo loại ngày (thường/tuần/lễ) để áp dụng hệ số.
• Tính lương: Lương cơ bản + Phụ cấp + Lương theo công/giờ + OT × hệ số – Khấu trừ.
  Ví dụ:
• Ngày công = SUMPRODUCT(–(Mã=”X”);1) + SUMPRODUCT(–(Mã=”0.5P”);0.5) …
• Giờ OT CN = SUMIFS(Giờ;KiểuNgày;”CN”;MãNV;$A2)
• Lương OT CN = Giờ OT CN × Đơn giá giờ × 2.0

Bảng lương nhân viên liên kết dữ liệu từ bảng chấm công Excel

Dùng PivotTable để tổng hợp theo phòng ban/dự án, và Power Query để hợp nhất dữ liệu nhiều chi nhánh.

Thiết kế và tối ưu file bảng chấm công

• Freeze Panes: cố định tiêu đề/ngày.
• Protect Sheet: khóa công thức, chỉ cho nhập mã công.
• Named Range và Table: công thức tự mở rộng.
• Conditional Formatting: tô màu cảnh báo thiếu công, vắng mặt, OT vượt ngưỡng.
• Data Validation + Input Message: hướng dẫn nhập liệu ngay tại ô.
• Audit: dùng Trace Dependents/Precedents để kiểm tra chuỗi công thức.

Kẻ ô, tạo bảng và định dạng trong Excel để thiết kế bảng chấm công đẹp

Nên lưu phiên bản theo tháng/năm và thiết lập lịch sử thay đổi (Change Log) khi điều chỉnh dữ liệu chấm công.

Tải và sử dụng mẫu sẵn có

Bạn có thể tham khảo các mẫu bảng chấm công theo giờ, theo ngày, theo ca; mẫu tự động cập nhật ngày/thứ; mẫu chia phòng ban; mẫu liên kết bảng lương. Tùy nhu cầu, chọn cấu trúc phù hợp và tinh chỉnh mã chấm công, hệ số, ca kíp theo chính sách nội bộ.

Tổng hợp file mẫu bảng chấm công Excel theo giờ, ngày, ca

Sau khi tải mẫu, hãy kiểm tra vùng dữ liệu, tên vùng, công thức tổng hợp, danh mục mã công và bảo vệ sheet trước khi triển khai chính thức.

Lỗi thường gặp và cách khắc phục

• Lỗi giờ qua đêm âm: dùng MOD(Ra – Vào;1) × 24 để xử lý.
• Quên cập nhật ngày lễ: thêm danh sách ngày lễ vào sheet Cấu_hình, dùng MATCH để nhận diện.
• Sai hệ số OT: tách OT ngày thường/cuối tuần/lễ, áp hệ số riêng biệt.
• Trùng mã nhân viên: chuẩn hóa mã NV duy nhất, tránh trùng lặp.
• Công thức không tự mở rộng: chuyển vùng dữ liệu thành Table, dùng tên cột.
• Dữ liệu máy chấm công lẫn In/Out: làm sạch với Power Query, quy tắc lấy lần quẹt đầu/ cuối.
• Conditional Formatting quá nhiều: gom quy tắc, áp dụng theo vùng cụ thể để tránh nặng file.

Bằng cách chuẩn hóa cấu trúc, tự động hóa bằng công thức và Power Query, kiểm soát mã chấm công và bảo vệ dữ liệu, bạn sẽ xây dựng một hệ thống bảng chấm công trên máy tính vừa đẹp, dễ dùng, vừa chính xác cho mọi loại hình làm việc.