Hướng dẫn làm bảng chấm công Excel & giải pháp tự động giúp CEO tối ưu quỹ lương

Bảng chấm công Excel vẫn là công cụ được nhiều doanh nghiệp sử dụng để theo dõi ngày công, giờ làm và làm căn cứ tính lương. Tuy nhiên, khi quy mô nhân sự tăng lên, việc quản lý chấm công bằng Excel không chỉ tốn thời gian mà còn làm phát sinh nhiều rủi ro như sai lệch dữ liệu, khó kiểm soát tăng ca, thiếu minh bạch giữa các bộ phận và chậm ra quyết định quản trị.

Trong bài viết này, MISA AMIS sẽ hướng dẫn cách làm bảng chấm công trên Excel chi tiết kèm mẫu tải miễn phí. Đồng thời, giúp doanh nghiệp nhìn rõ khi nào nên chuyển từ công cụ thủ công sang hệ thống chấm công tự động để tối ưu hiệu suất vận hành.

1. Tìm hiểu về bảng chấm công Excel và tầm quan trọng trong vận hành doanh nghiệp

Bảng chấm công Excel là biểu mẫu dùng để theo dõi ngày công, giờ làm việc, ca làm, nghỉ phép, làm thêm giờ của nhân viên trong doanh nghiệp. Đây là căn cứ quan trọng để tính lương, thưởng và đảm bảo chế độ phúc lợi minh bạch.

Tuy nhiên dưới góc độ quản trị, bảng chấm công không chỉ là tài liệu nghiệp vụ của HR hay kế toán. Đây còn là dữ liệu đầu vào ảnh hưởng trực tiếp đến quỹ lương, năng suất lao động, chi phí tăng ca và khả năng kiểm soát vận hành của doanh nghiệp.

Khi doanh nghiệp càng lớn, việc quản lý bảng chấm công bằng Excel càng dễ trở thành điểm nghẽn nếu dữ liệu không được đồng bộ và cập nhật kịp thời. Vì vậy, nhiều doanh nghiệp đang dần chuyển sang các phần mềm chấm công tự động để tối ưu quy trình quản lý nhân sự.

2. Xác định mô hình bảng chấm công trên Excel

Để làm bảng chấm công trên excel chuẩn, đúng, đầu tiên người lập bảng xác định cụ thể bảng chấm công gồm:

• Hạng mục gì quan trong, bắt buộc phải có?
• Có bao nhiêu sheet?
• Nội dung gì cần làm rõ, cần bao nhiêu dòng và bao nhiêu cột?

Khi đã trả lời được các câu hỏi vừa nêu trên, người lập sẽ hiểu rõ hơn cách làm bảng chấm công trên Excel. Từ đó sẽ lập được file như ý muốn mà không phải chỉnh sửa, thêm, xóa, bớt nhiều lần.

Dưới đây MISA AMIS gợi ý mẫu mô hình của bảng chấm công mà bạn có thể hình dung dễ dàng:

• Một bảng chấm công có tổng cộng 13 sheet, trong đó 1 sheet dùng liệt kê danh sách toàn bộ nhân viên trong công ty, 12 sheet còn lại tương ứng với 12 tháng trong năm. 
• Số lượng nhân viên trong mỗi sheet sẽ gồm khoảng 20 nhân viên hoặc nhiều hơn, phụ thuộc vào tổng số nhân viên ở các phòng ban.
• Mỗi người có mã nhân viên riêng, mã này được sử dụng xuyên suốt quá trình chấm công và trong công tác quản trị nhân sự.
• Bảng chấm công phải có đầy đủ các cột tháng, ngày, thứ. Thứ 7 và Chủ nhật thì người lập bảng nên đổi màu để phân biệt với những ngày thường.
• Công của nhân viên sẽ được chấm theo ngày vào cột tương ứng và tổng kết vào ngày cuối cùng của mỗi tháng.
• Tên nhân viên trong các sheet chấm công sẽ được lấy từ sheet danh sách nhân viên, dữ liệu hai bên trùng khớp nhau.
• Có thể thêm liên kết giữa các tháng với nhau để dễ thao tác hơn.

Trước khi bắt đầu tạo file, doanh nghiệp cần xác định rõ mô hình chấm công phù hợp với quy mô nhân sự, số lượng chi nhánh, hình thức làm việc và yêu cầu kiểm soát dữ liệu.

• Với doanh nghiệp nhỏ, bảng Excel có thể đáp ứng nhu cầu cơ bản.
• Với doanh nghiệp nhiều phòng ban, nhiều ca làm hoặc nhiều điểm làm việc, việc thiết kế bảng chấm công ngay từ đầu cần tính đến khả năng mở rộng để tránh phát sinh sai sót khi vận hành.

Doanh nghiệp đang quản lý chấm công bằng Excel? Xem cách chuẩn hóa dữ liệu công, giảm thời gian tổng hợp cuối tháng và kiểm soát chi phí nhân sự hiệu quả: 

3. Hướng dẫn cách làm bảng chấm công trên Excel

Khi đã xây dựng được bố cục tổng thể của file bảng chấm công, bạn có thể tiến hành tạo bảng chấm công excel tự động. Dưới đây là 6 bước tiêu chuẩn hướng dẫn cách làm bảng chấm công excel tự động đẹp mắt:

Bước 1: Tạo bố cục các sheet

Trong tổng số 13 sheet nêu trên, bạn chỉ cần thiết lập 2 sheet như sau:

• 1 Sheet là Danh sách nhân viên (DSNV)
• 1 Sheet là Tháng 1. Lưu ý, Sheet tháng 1 cần làm chuẩn, các tháng tiếp theo chỉ cần sao chép (Ctrl+C) và Ctrl+V và đổi tên tháng. Cách này sẽ tiết kiệm thời gian điều chỉnh.

Bước 2: Tạo sheet Danh sách nhân viên

Bước tiếp theo trong quy trình tạo bảng chấm công trên excel đó là tạo sheet Danh sách nhân viên. Sheet danh sách nhân viên được nhập bằng tay. Sheet này gồm hai mục “Tên” và “Mã nhân viên”. Bạn cần phải tạo mã nhân viên để phân biệt các nhân viên trùng tên. Ngoài ra, trong bảng cần có những cột khác như số thứ tự, ngày sinh, quê quán, số CCCD/CMND, ngày bắt đầu làm việc …

• Lưu ý nên nhập nội dung từ cột B để dự phòng nếu cần bổ sung thêm thông tin và dòng thứ 4 để tiện cho việc liên kết với các sheet khác
• Cột ngày/tháng/năm sinh nên để đồng bộ cùng 1 định dạng bằng cách bôi đen cả cột chứa ngày tháng năm sinh đó, chuột phải chọn Format cell > Custom > Định dang dd-mm-yyyy

Thực hiện tương tự với các cột khác như cột ngày bắt đầu làm việc, ngày ký hợp đồng nếu có.

Bước 3: Tạo sheet Tháng 1

Trong sheet Tháng 1, bạn cần tạo khung cho bảng chấm công, sau đó thiết lập nội dung chi tiết khác cho bảng chấm công:

• Tiêu đề – bảng chấm công (B3-E3)
• Tháng năm chấm công (B4-E4)
• Bộ phận chấm công ô B5
• Định mức ngày công trong tháng (B6-E6)

Các cột sẽ bao gồm: 

• Số thứ tự
• Mã nhân viên
• Họ và tên nhân viên
• Ngày trong tháng (31 cột để tương ứng với 31 ngày, lấy số ngày lớn nhất của 1 tháng)
• 4-5 cột để tính quy ra công
• Cột ghi chú – Cuối cùng

Tiếp theo, bạn sẽ phải căn chỉnh độ rộng của từng cột cho khoa học và dễ nhìn. Trong đó, các cột ngày trong tháng co nhỏ vừa đủ cho việc chấm công. Cột quy ra công cần vừa đủ. Cột tên và mã nhân viên cần điều chỉnh kích thước để hiển thị đầy đủ thông tin.

Cách để co độ rộng: Độ rộng của cột ngày tháng (cột E) nên để rộng khoảng 30 pixels. Bôi đen tất cả cột ngày trong tháng, quy ra công (trong ví dụ trên là bôi đen từ cột E cho đến cột AM). Sau đó bạn co độ rộng ở cột E khoảng 30 pixels (hoặc có thể bôi đen từ cột E đến AM, sau đó bạn bấm chọn nút Command Column Width và chọn 3,13, tương ứng 30 pixels).

Bước 4: Tạo ngày tháng trong bảng chấm công

Bước tạo ngày tháng trong bảng chấm công excel là bước quan trọng và phức tạp nhất, bước này đòi hỏi sự cẩn thận, chi tiết và tỉ mỉ.

Đầu tiên, bạn phải xác định năm chấm công. Tại ô D1, bạn hãy nhập giá trị của năm. Ví dụ năm 2014.

Tại ô B4 (ô xác định tháng chấm công), bạn hãy nhập hàm xác định ngày, gồm: =date($D$1;1;1).

Bạn cần lưu ý rằng dấu ngăn cách các thành phần có trong hàm có thể là dấu chấm phẩy “;” hoặc dấu phẩy “,” tùy vào thiết lập của máy tính. Ở đây MISA sử dụng dấu chấm phẩy “;”.

Nội dung của hàm date: Xác định giá trị ngày tháng theo như giá trị có trong hàm. Giá trị theo thứ tự lần lượt là Năm, tháng, ngày. Ở hàm trên ta có: Năm = giá trị ô D1, Tháng = 1, Ngày = 1.

Sau khi đã nhập hàm xong, ngay tại ô B4, bạn hãy chọn format cell/ custom/ nhập giá trị [“tháng “mm” năm “yyyy] vào ngay ô Type bên phải, sau đó ấn Ok (cụ thể: dấu nháy kép => tháng => dấu cách => dấu nháy kép => mm => dấu nháy kép => năm => dấu cách => dấu nháy kép => yyyy).

Các ô B4 đến D4 nên merge cell để nội dung hiển thị dễ nhìn hơn.

Tại ô ngày 1 (tức ô E9), bạn hãy nhập =b4 để xác định ngày đầu tiên của tháng.

Tại ô F9, bạn hãy nhập =e9+1 (xác định ngày tiếp theo trong tháng).

Copy công thức ở ô F9 sang những ô bên cạnh, cho đến ô ngày thứ 31 (tức ô AI9) bằng một trong hai cách sau:

• Cách 1: Bôi đen từ ô F9 đến ô AI9 và sau đó bấm Ctrl + R.
• Cách 2: Bấm chuột vào ngay ô F9, giữ chuột tại dấu chấm đen trong ô để cho con trỏ chuột biến thành dấu +, kéo chuột đến ô AI9 rồi thả ra.

Bôi đen từ ô E9 tới ô AI9 -> chọn Format cells/ custom/ mục Type. Sau đó, bạn hãy gõ chữ dd rồi bấm OK (chỉ hiện thị số ngày).
Nội dung từ ô E9 đến ô AI9 sẽ hiển thị ra ngày giống như trong bảng trên.

Tại ô E10 nhập chính xác hàm =CHOOSE(WEEKDAY(E9);”Chủ nhật”;”T. hai”;”T. ba”;”T. tư”;”T. năm”;”T. sáu”;”T. bảy”).

• Weekday (E9): là hàm lấy giá trị ngày trong tuần của ô E9. Nếu hàm Weekday không có quy định về thứ tự ngày trong tuần, nó sẽ tự động trả về một giá trị từ 1 đến 8, thể hiện cho các ngày trong tuần theo thứ tự từ Chủ Nhật đến Thứ Bảy.
• Choose: là hàm chọn giá trị trả về. Nếu giá trị đầu tiên trong hàm được chọn là căn cứ xác định, thì các giá trị tiếp theo sẽ là nội dung được trả về tương ứng với giá trị đầu tiên.
• Để biểu thị ngày trong tuần bằng tiếng Việt, bạn cần kết hợp hàm weekday với hàm choose để chuyển đổi giá trị này thành “Chủ Nhật”, “Thứ Hai”, “Thứ Ba”, “Thứ Tư”, “Thứ Năm”, “Thứ Sáu”, hoặc “Thứ Bảy”.

Copy công thức của ô E10 sang những ô kế tiếp bên phải, cho đến ô AI10 (ngày thứ 31).

Trong năm có các tháng 28, 29, 30 ngày, không phải đều đặn 31 ngày, để tránh việc hiển thị ngày của tháng khác vào tháng này, bạn thao tác như sau:

• Tại ô AG9 (ngày thứ 29), điều chỉnh hàm thành: =IF(DAY(AF9+1)=DAY(E9);””;AF9+1).

Ý nghĩa của hàm: Nếu giá trị ngày trong ô AF9 + 1 bằng giá trị ngày trong ô E9, thì kết quả sẽ là một giá trị rỗng. Nếu giá trị ngày trong ô AF9 + 1 không bằng giá trị ngày trong ô E9, thì kết quả sẽ là giá trị của ô AF9 + 1.

• Tại ô AH9 dùng hàm =IF(AG9=””;””;if(DAY(AG9+1)=DAY(E9);””;AG9+1))

Ý nghĩa của hàm: IF thứ 1: Nếu ô AG9 có giá trị rỗng, thì ô AH9 cũng sẽ có giá trị rỗng. Trong trường hợp tháng 2 có 28 ngày, ngày thứ 30 sẽ bị bỏ qua). IF thứ 2: Tương tự với hàm IF tại ô AG9, nếu tháng 2 có 29 ngày, thì ngày thứ 30 cũng sẽ không hiển thị.

• Tại ô AI9, dùng hàm =IF(AH9=””;””;if(DAY(AH9+1)=DAY(E9);””;AH9+1))

Ý nghĩa của hàm: IF thứ 1: Nếu ô AH9 có giá trị rỗng, thì ô AI9 cũng sẽ có giá trị rỗng. Trong trường hợp không có ngày thứ 30, thì cũng không hiển thị ngày thứ 31. IF thứ 2: Trường hợp tháng chỉ có 30 ngày thì ngày thứ 31 sẽ không hiển thị.

• Nếu AG9, AH9, AI9 rỗng thì hàm Choose ở AG10, AH10, AI10 sẽ hiển thị lỗi. Tuy nhiên các phần này không ảnh hưởng đến bảng tính nên bạn không cần điều chỉnh thêm.

Để dễ phân biệt các thứ trong tuần, bạn thực hiện đổi màu ngày thứ 7, chủ nhật như sau:

• Bôi đen tất cả ngày tháng trong bảng, từ ngày 1 đến ngày 31. Tiếp theo bạn chọn Conditional Formatting để định dạng theo điều kiện, chọn tiếp Conditional Formatting >> New Rule.

• Tại bảng New Formatting Rule, bạn chọn Use a formula to determine which cells to format
• Trong phần “Format values where this formula is true” (Định dạng giá trị khi công thức này đúng), bạn nhập hàm sau: =IF(WEEKDAY(E$9) = 1, TRUE, FALSE). Nếu ngày trong tuần tại ô E9 là Chủ Nhật (ngày thứ 1), thì điều kiện sẽ đúng và Conditional Formatting sẽ được áp dụng. Nếu không, điều kiện sẽ sai và không áp dụng định dạng.
• Sau khi đặt hàm, bạn có thể thiết lập định dạng cho Conditional Formatting. Trong bảng định dạng, bạn có thể chọn font chữ, màu chữ tại mục Font, màu nền tại mục Fill, kẻ đường viền ô tại mục Border, và thực hiện các thiết lập định dạng khác giống như bạn làm với Format Cells thông thường. Khi bạn đã hoàn thành cài đặt định dạng, hãy bấm OK để áp dụng.
• Thực hiện tương tự với cột ngày thứ 7 nếu cần thiết.

Để tự động bôi xám các ngày trống trong tháng, ví dụ như ngày 29-30-31 tháng 2 trên thực tế không có, bạn thực hiện như sau:

• Giả sử chúng ta đang chỉnh thông tin cho sheet tháng 2
• Bôi đen vùng cần đánh dấu là ngày trống, chọn chức năng Conditional Formatting >> New Rule >> Use A Formula. Bạn nhập hàm Trong công thức =IF(E$9=””;TRUE;FALSE), chọn định dạng màu xám. Kết quả sẽ như hình dưới đây:

Như vậy các bước trên đã giúp bạn thiết lập về công thức và cách hiển thị một số hàng cột đặc biệt trong bảng chấm công. Tiếp theo chúng ta chuyển sang bước đặt ký hiệu chấm công.

Xem thêm bài viết liên quan:

• Top 15 phần mềm chấm công Online được sử dụng nhiều nhất
• Mẫu bảng chấm công theo giờ, theo ngày tự động trong Excel

Bước 5: Đặt ký hiệu chấm công tương ứng 

Tùy vào mỗi doanh nghiệp, và cách người chấm công quy định và ký hiệu chấm công sẽ có sự khác nhau. Thông thường một số dữ liệu chấm công nên đặt ký hiệu bao gồm:

• Ký hiệu X: Ngày công thực tế, đi làm đầy đủ và đúng giờ
• Ký hiệu V: Nửa ngày công, làm 1 buổi và nghỉ 1 buổi: 
• Ký hiệu P: Ngày nghỉ hưởng nguyên lương
• Ký hiệu K: Nghỉ không lương
• Ký hiệu O: Nghỉ ốm đau, thai sản, chế độ

Mỗi hạng mục trên nên tương ứng với 1 cột để tiện cho việc tính tổng số công cuối tháng.

Bước 6: Đặt hàm, công thức để tạo bảng chấm công 

Bước đặt hàm và công thức tính công là bước cuối cùng trong bài hướng dẫn tạo bảng chấm công excel tự động.

Tại cột ngày công thực tế (tức cột AJ), tại ô AJ11, bạn hãy đặt hàm sau: =COUNTIF($E11:$AI11;$G$34).

Ý nghĩa của hàm này là đếm số lần mà giá trị tại ô G34 (đại diện cho ký hiệu chấm công của ngày công đủ) xuất hiện trong vùng từ cột E đến AI tại hàng 11. Đặc biệt, cố định cột E và AI để đảm bảo rằng khi bạn sao chép công thức, nó không thay đổi vùng cố định của dữ liệu chấm công.

Cũng tương tự với các cột khác, bạn hãy đặt công thức:

• Ở ô AK11 (Nửa công) =countif($E11:$AI11;$G$35).
• Ở ô AL11 (Nghỉ hưởng lương) =countif($E11:$AI11;$G$36).
• Ở ô AM11 (Nghỉ không lương) =countif($E11:$AI11;$G$37).
• Ở ô AN11 (Nghỉ ốm đau, thai sản) =countif($E11:$AI11;$G$38).

Sau khi bạn đặt công thức xong thì chỉ cần copy công thức hoặc kéo chuột xuống vào ô của các nhân viên khác. Hoàn thành công thức bảng sẽ hiển thị dưới đây. Hãy nhập thử một số giá trị để kiểm tra bảng đã chuẩn số liệu chưa.

❗Lưu ý cho doanh nghiệp đang tăng trưởng: Excel có thể phù hợp khi quy mô nhỏ. Nhưng nếu phòng nhân sự phải duy trì quá nhiều công thức, xử lý chấm công cho nhiều chi nhánh hoặc thường xuyên chỉnh sửa dữ liệu, chi phí thời gian và rủi ro sai sót sẽ tăng nhanh theo quy mô.

Vấn đề không nằm ở việc file Excel có đủ cột hay đủ công thức mà nằm ở chỗ doanh nghiệp không còn kiểm soát được dữ liệu công theo thời gian thực. Lúc này, bảng chấm công không chỉ là công cụ hành chính mà đã trở thành bài toán về năng suất vận hành, độ chính xác quỹ lương và khả năng ra quyết định của lãnh đạo.

Nếu doanh nghiệp của bạn có nhiều phòng ban, nhiều ca làm hoặc nhiều chi nhánh, hãy trải nghiệm hệ thống chấm công tự động để giảm tải cho HR và tăng khả năng kiểm soát cho quản lý.

Dùng thử & nhận tư vấn

4. Hướng dẫn cách làm bảng chấm công với phần mềm MISA AMIS

Khi doanh nghiệp phát triển, bài toán chấm công không còn dừng ở việc ghi nhận ngày làm việc của nhân viên mà làm sao để lãnh đạo nắm được dữ liệu công chính xác, đồng bộ để kiểm soát quỹ lương và hiệu suất vận hành. Đó là lý do nhiều doanh nghiệp chuyển từ bảng chấm công Excel sang MISA AMIS Chấm Công nhằm tự động hóa toàn bộ quy trình ghi nhận, tổng hợp và theo dõi dữ liệu công.

• Chuẩn hóa dữ liệu công trên toàn doanh nghiệp, kể cả khi có nhiều chi nhánh hoặc nhiều hình thức làm việc.
• Rút ngắn thời gian tổng hợp công cuối tháng, giảm áp lực cho HR và kế toán.
• Tăng độ chính xác khi tính lương, hạn chế tranh cãi về tăng ca, nghỉ phép, đi muộn.
• Giúp quản lý và lãnh đạo theo dõi tình hình chấm công theo thời gian thực.
• Tạo nền tảng để kết nối chấm công với lương, nghỉ phép và báo cáo nhân sự. 

Để có cái nhìn tổng quan và nhanh chóng nhất về sự khác biệt giữa quản lý thủ công qua Excel và quản lý bằng hệ thống tự động MISA AMIS Chấm Công, bạn có thể tham khảo bảng so sánh tóm tắt dưới đây:

Đừng để bảng chấm công thủ công làm chậm tốc độ vận hành của doanh nghiệp. Chuyển từ Excel sang hệ thống chấm công tự động để kiểm soát dữ liệu nhân sự chính xác, minh bạch và kịp thời hơn.

Dùng thử miễn phí

Đã có hơn 17.000 Doanh nghiệp tin tưởng và sử dụng AMIS Chấm Công và đã giúp doanh nghiệp quản lý việc chấm công hiệu quả. Trong đó, có thể kể đến các thương hiệu lớn như Trống Đồng Palace, IVY moda, Hệ thống Giáo dục Hoa Sen,…

5. Những lưu ý trong làm bảng chấm công Excel

Để có được bảng chấm công bằng Excel chính xác, đảm bảo quyền lợi của nhân sự, khi lập bảng chấm công bạn cần lưu ý những yếu tố sau: 

• Các sheet cần thống nhất và đồng bộ về cách đặt ký hiệu, hàm, công thức, cách hoạt động. Cần đồng bộ quy định và hình thức bảng chấm công (xác định rõ ràng thời gian theo ngày, nghỉ, làm bù,…). 
• Định hình rõ các tiêu chí chính trong việc phân hàng, phân cột để tạo ra bảng lớn. Trong mỗi bảng sẽ có những mục bảng nhỏ và chi tiết. Tiếp tục cho đến khi hết các nội dung cần điền vào bảng nhằm tránh sai sót, chỉnh sửa nhiều
• Khi nhập dữ liệu xong hoặc hoàn thành xong bảng chấm công cần lưu lại ngày/giờ thời gian hoàn thành tiện cho việc kiểm tra, khôi phục tài liệu và tránh nhầm lẫn.

Kết luận 

Việc xây dựng một bảng chấm công Excel chuẩn chỉnh không chỉ đòi hỏi sự thành thạo các công thức mà còn cần sự tỉ mỉ để đảm bảo tính chính xác tuyệt đối. Hy vọng với hướng dẫn trên, bạn đã có thể tự tay thiết lập một công cụ quản lý thời gian hiệu quả cho doanh nghiệp của mình.

Tuy nhiên, hãy lưu ý khi quy mô nhân sự tăng trưởng, việc quản lý thủ công trên Excel thường dẫn đến những rủi ro về sai sót dữ liệu, thất thoát chi phí và khó khăn trong việc truy xuất báo cáo thời gian thực.

Để giải quyết triệt để bài toán này, các doanh nghiệp hiện đại đang dần chuyển dịch sang hệ thống chấm công tự động. Đây không chỉ là cách để tiết kiệm thời gian cho bộ phận HR mà còn là giải pháp giúp Ban lãnh đạo kiểm soát quỹ lương và năng suất nhân sự một cách minh bạch và tối ưu nhất.