Cách làm bảng chấm công trên Excel dễ dàng và chi tiết

Bảng chấm công Excel là công cụ không thể thiếu của mỗi doanh nghiệp, giúp chuyên nghiệp hóa công tác quản lý thời gian làm việc. Tuy nhiên với nhiều người việc tạo file Excel để chấm công khá phức tạp. Sau đây, MISA AMIS HRM sẽ hướng dẫn cách tạo bảng chấm công trên Excel dễ dàng và chi tiết. Hãy cùng tìm hiểu ngay.

Tải miễn phí – Mẫu bảng chấm công trên Excel

1. 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 đúng cách, đầu tiên người lập bảng cần hình dung cụ thể trong bảng chấm công gồm những hạng mục gì, có bao nhiêu sheet, cần nội dung gì, 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 nhiều lần.

Nếu bạn chưa phác họa được mô hình bảng chấm công trên Excel, hãy tham khảo những dữ liệu mà chúng tôi gợi ý dưới đây: 

• Một bảng chấm công có tổng cộng 13 sheet: 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.

2. 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ạn hãy tiếp tục thực hiện các bước sau đây để có được một bảng chấm công tự động, đẹp mắt:

Bước 1: 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 đặt tên là là Danh sách nhân viên (DSNV)
• 1 Sheet mẫu đặt tên là Tháng 1. Cần làm tháng đầu tiên thật chỉn chu, chính xác. Các tháng sau có thể copy sheet này và đổi tên theo tháng tương ứng, như vậy sẽ tiết kiệm thời gian và công sức hơn. 

>>> Xem thêm: 16 Mẫu bảng chấm công excel mới nhất hiện nay

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

Nội dung chủ yếu trong 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 gồm những nội dung khác liên quan tới nhân viên như quê quán, ngày sinh, số CCCD/CMND, ngày bắt đầu làm việc…

Để các cột liên quan đến ngày tháng hiển thị đồng bộ, bạn định dạng như sau: Bôi đen chọn cả cột, ví dụ như cột ngày sinh. Vào Format Cell >> Number >> Custom >> chọn dd-mm-yyy. Khi bạn nhập dữ liệu ngày tháng vào cột này, dữ liệu tự động được đưa về dạng ngày-tháng-năm (ví dụ 15-09-1998). 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ó.

Sheet danh sách nhân viên hầu hết được nhập bằng tay. Lưu ý, bạn phải để ra khoảng 2-3 dòng trên cùng để có thể tạo được liên kết tới các sheet khác. Bên trái nên cách ra 1 cột để dự phòng nếu như cần bổ sung thông tin.

>>> Xem thêm: Top app chấm công trên điện thoại miễn phí tốt nhất

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

Trong sheet Tháng 1, bạn hãy tạo khung cho bảng chấm công, bao gồm các nội dung chi tiết như sau: 

• Tiêu đề – bảng chấm công
• Tháng
• Bộ phận chấm công 
• Định mức ngày công trong tháng

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

• Mã nhân viên
• 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
• 1 cột cuối cùng để ghi chú

Tiếp theo, bạn sẽ phải căn độ rộng của từng cột sao cho gọn gàng 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 không cần phải quá rộng. 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: bạn hãy bôi đen những 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 sao cho rộng rơi vào 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).

>>> Xem thêm: Phần mềm quản lý nhân sự HRM tốt nhất cho doanh nghiệp

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

Việc tạo ngày tháng năm trong bảng chấm công Excel rất quan trọng, hãy thực hiện theo hướng dẫn sau:

Đầu tiên, bạn phải xác định năm sử dụ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: Có nên mua máy chấm công vân tay không

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

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

• Ngày công thực tế: X
• Nửa ngày công: V
• Ngày nghỉ hưởng nguyên lương: P
• Nghỉ không lương: K
• Nghỉ ốm đau, thai sản, chế độ: O

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 

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 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 đã nhảy số được chưa.

3. 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: 

• Thứ nhất: Đồ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ù,…). Các sheet phải thống nhất với nhau về cách đặt ký hiệu, hàm, công thức.
• Thứ hai: Xác định những tiêu chí chính để tạo thành những bảng lớn. Trong mỗi bảng sẽ có những mục bảng nhỏ và chi tiết hơn, bạn cần tránh rườm rà và làm ảnh hưởng đến việc tổng hợp công lúc hết tháng.
• Thứ ba: 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 và nhớ thời gian hoàn thành. Việc này giúp bạn tiện kiểm tra hoặc khôi phục file khi cần thiết, tránh sai sót dữ liệu.

[TẢI MIỄN PHÍ] Trọn bộ 31 mẫu bảng biểu Excel và mẫu giấy tờ chấm công – tính lương mới nhất năm 2023

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

Sử dụng Excel để tạo bảng chấm công là giải pháp được nhiều doanh nghiệp vừa và nhỏ lựa chọn áp dụng. Tuy nhiên khi quy mô nhân sự gia tăng, việc tổng hợp công bằng Excel sẽ tốn thời gian và dễ phát sinh sai sót hơn. Điều này có thể gây lãng phí nguồn nhân lực, thời gian, công sức cũng như hạn chế năng suất làm việc của phòng HR trong thời đại chuyển đổi số mạnh mẽ như hiện nay.

Chính vì vậy, việc sử dụng phần mềm công nghệ thay thế Excel đang được rất nhiều doanh nghiệp ưu tiên. Trong đó, AMIS Chấm Công là một trong những giải pháp tốt nhất, nhận được đánh giá tích cực từ HR C&B và nhà quản lý.

AMIS Chấm Công là một sản phẩm nằm trong bộ giải pháp quản trị nhân sự tổng thể MISA AMIS HRM. Phần mềm hỗ trợ kết nối trực tiếp với máy chấm công, cung cấp nhiều hình thức chấm công như vân tay, khuôn mặt, GPS, QR Code, Wifi… Chỉ cần nhập dữ liệu nhân viên ban đầu, thiết lập cách thức chấm công một lần là phần mềm sẽ tự động tổng hợp công cuối tháng. HR không cần lập bảng chấm công mà chỉ cần 1 phút để xuất bảng công hoàn chỉnh, chính xác từ phần mềm.

Trước khi sử dụng AMIS Chấm Công	Sau khi dùng AMIS Chấm Công
Công ty có nhiều chi nhánh, khó khăn trong việc cập nhật dữ liệu công.	Tự động thiết lập thời gian làm việc cho nhân viên, toàn bộ dữ liệu công được đồng bộ
Xin nghỉ phép, đi muộn phải làm đơn, xin chữ ký quản lý, làm email tốn thời gian	Tự làm đơn, quản lý duyệt đơn ngay trên phần mềm, không cần giấy tờ thủ công
Tổng hợp công bằng Excel tốn rất nhiều thời gian, công sức, độ chính xác chưa cao	Chấm công đa dạng hình thức với khuôn mặt, vân tay, GPS, dễ dàng theo dõi thời gian làm việc của nhân viên
Không có cái nhìn tổng quát về tình hình chấm công, làm báo cáo mất nhiều thời gian	Tự động tạo báo cáo theo nhiều dạng khác nhau, lãnh đạo, quản lý dễ dàng theo dõi tình hình nhân viên đi làm

Dùng ngay 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,…

Đừng bỏ qua: Review chi tiết MISA AMIS HRM từ khách hàng

5. Kết luận 

Để tạo được bảng chấm công cần kết hợp khá nhiều công thức nhưng cũng không quá phức tạp. Với hướng dẫn cách làm bảng chấm công Excel như trên, hy vọng bạn có thể tạo lập được một file tổng hợp công đầy đủ và chính xác. File này sẽ giúp bạn quản lý thời gian làm việc của nhân viên trong công ty một cách chặt chẽ. Ngoài ra doanh nghiệp có thể tham khảo thêm phương án sử dụng phần mềm chấm công tự động thay cho Excel để tiết kiệm thời gian, nâng cao hiệu suất hơn nữa.