Hướng dẫn bạn cách tạo bảng chấm công trên Microsoft Excel chi tiết từ A-Z

Việc chấm công là cần thiết để quản lý có thể theo dõi sự hiện diện của bạn. Việc tạo bảng chấm công không chỉ giúp nâng cao tính chuyên nghiệp trong quản lý nhân sự mà còn hỗ trợ bạn trong công việc hàng ngày. Nếu bạn chưa biết cách thiết lập bảng chấm công trên Microsoft Excel, hãy cùng Phúc Anh theo dõi bài viết ngay sau đây nhé!

Cách Tạo Bảng Chấm Công Trong Microsoft Excel

Trước khi bắt đầu tạo file Excel, hãy hình dung trước nội dung mà bảng chấm công sẽ bao gồm. Bạn cần xác định số lượng sheet, thông tin cần thiết và cách thức hoạt động của bảng.

Dưới đây là một mô hình bảng chấm công cơ bản:

• Số lượng sheet: 13 sheet, mỗi sheet đại diện cho một tháng và một sheet riêng cho danh sách nhân viên.
• Ký hiệu chấm công: Có thể thay đổi tùy theo yêu cầu của người dùng.
• Nội dung bảng: Ghi rõ từng ngày và thứ trong tháng. Cuối tuần (thứ 7 và chủ nhật) sẽ có màu sắc khác biệt để dễ nhận biết.
• Chấm công hàng ngày: Nhập dữ liệu cho từng ngày trong tháng. Cuối tháng, bảng sẽ tự động tính tổng số ngày làm việc.
• Tên nhân viên: Lấy từ danh sách nhân viên có sẵn.
• Liên kết các tháng: Có thể tạo liên kết giữa các sheet để thuận tiện trong thao tác.

Mặc dù mô hình này có 13 sheet, bạn chỉ cần bắt đầu với 2 sheet:

- 1 sheet danh sách nhân viên (DSNV).

- 1 sheet tháng mẫu (thường là tháng 1, hoàn chỉnh với đầy đủ thông tin; các tháng sau có thể sao chép và đổi tên cho phù hợp).

Bước 1: Tạo Sheet Danh Sách Nhân Viên

Trong sheet này, bạn sẽ nhập các thông tin cần thiết của nhân viên, bao gồm: họ và tên, mã nhân viên (để tránh nhầm lẫn khi có trùng tên), ngày sinh, quê quán, số chứng minh thư, và ngày bắt đầu làm việc.

Lưu ý:

Hãy để lại khoảng 2-3 dòng ở đầu sheet để có thể liên kết với các sheet khác. Bên trái nên có một cột trống để có thể thêm ghi chú nếu cần thiết.

Bước 2: Định Dạng Cột Ngày/Tháng/Năm

Để định dạng cho cột ngày sinh, bạn thực hiện các bước sau:

Bước 1: Chọn toàn bộ cột ngày sinh bằng cách di chuyển con trỏ đến đầu cột và nhấn vào đó để bôi đen.

Bước 2: Nhấn chuột phải và chọn "Format Cells", sau đó chuyển đến tab "Custom".

Bước 3: Trong danh sách định dạng, tìm kiểu dữ liệu dd-mm-yyyy để thiết lập cột thành định dạng ngày/tháng/năm. Nhấn "OK" để hoàn tất.

Bước 3: Tạo Sheet Tháng Mẫu (Tháng 1)

Bước 1: Thiết lập khung cho bảng chấm công với các thông tin sau:

• Tiêu đề: "Bảng chấm công", "Tháng", "Bộ phận", và "Định mức công trong tháng".
• Các cột cần có bao gồm: mã nhân viên, tên nhân viên, các ngày trong tháng (31 cột cho 31 ngày, là số ngày tối đa trong tháng), từ 4 đến 5 cột để ghi lại số công, và 1 cột để ghi chú.

Bước 2: Điều chỉnh kích thước của các cột cho phù hợp và dễ quan sát. Các cột cho ngày trong tháng có thể được thu nhỏ vừa đủ để chấm công, trong khi cột ghi công không cần quá rộng. Tuy nhiên, cột tên nhân viên cần đủ rộng để hiển thị thông tin đầy đủ.

Cách điều chỉnh kích thước cột: Chọn tất cả các cột cho ngày trong tháng và cột ghi công, nhấn chuột phải, chọn "Column Width", và thiết lập kích thước là 3.13 (tương đương với 30 pixels). Nhấn "OK" để hoàn tất.

Bước 3: Sau khi bấm "OK", bạn đã hoàn thành việc tạo khung cho bảng chấm công.

Bước 4: Tạo Ngày Tháng Trong Bảng Chấm Công

Bước 1: Đầu tiên, xác định năm mà bạn sẽ sử dụng, ví dụ như năm 2023.

Bước 2: Tại ô xác định tháng chấm công, bạn cần nhập công thức sau để tính toán ngày: =DATE($D$1,1,1).

Lưu ý: Dấu phân cách giữa các thành phần trong hàm có thể là ; hoặc ,, tùy thuộc vào cài đặt máy của bạn. Hàm DATE sẽ xác định giá trị ngày tháng theo thứ tự: năm, tháng, và ngày.

Bước 3: Sau khi nhập xong hàm, nhấn chuột phải vào ô xác định tháng chấm công, chọn "Format Cells", sau đó chọn "Custom". Nhập giá trị ["tháng "mm" năm "yyyy] vào ô "Type" và nhấn "OK" để hoàn tất.

Bước 4: Tại ô ngày đầu tiên của tháng, nhập công thức = ô xác định chấm công (ví dụ ô B4).

Bước 5: Đối với ô ngày 2, bạn sẽ sử dụng công thức = ô ngày 1 + 1. Sau đó, chọn từ ô ngày 2 đến ngày 31 và nhấn tổ hợp Ctrl + R để sao chép công thức.

Bước 6: Chọn tất cả các ô và nhấn chuột phải, chọn "Format Cells", nhấn vào "Custom", và nhập giá trị dd vào ô "Type" để chỉ hiển thị ngày. Nhấn "OK" để hoàn tất.

Bước 7: Tại ô bên dưới ô ngày (ví dụ E10), nhập công thức sau: =CHOOSE(WEEKDAY(E9),"Chủ nhật","Thứ hai","Thứ 3","Thứ 4","Thứ 5","Thứ 6","Thứ 7").

• Giải thích: Hàm WEEKDAY(E9) sẽ lấy giá trị ngày trong tuần của ô E9. Nếu không có quy định về thứ tự, hàm sẽ tự động trả về thứ tự từ Chủ nhật đến thứ Bảy với các giá trị từ 1 đến 7.
• Hàm CHOOSE sẽ chọn giá trị trả về dựa trên kết quả của hàm WEEKDAY. Ví dụ, nếu ngày 01/01/2023 là Chủ nhật, hàm WEEKDAY sẽ trả về 1, và hàm CHOOSE sẽ tương ứng trả về "Chủ nhật".

Bước 8: Cuối cùng, sao chép công thức từ ô đầu tiên (ngày đầu tháng) và kéo xuống đến ô cuối cùng (ngày cuối tháng). Chọn biểu tượng "Orientation" và điều chỉnh dữ liệu nằm dọc để hoàn thành.

Bước 5: Điều Chỉnh Cho Những Tháng Không Có 31 Ngày

Sau khi đã thiết lập xong bảng cho một tháng đầy đủ 31 ngày, chúng ta cần điều chỉnh cho các tháng không đủ ngày (như tháng 2) để tránh hiển thị các ngày không thuộc tháng đó. Dưới đây là các bước cần thực hiện:

Bước 1: Bắt đầu từ ngày 29, điều chỉnh công thức tại ô tương ứng như sau: =IF(DAY(AF9+1)=DAY(E9),"",AF9+1).

• Giải thích hàm: Công thức kiểm tra nếu ngày của ô AF9 cộng thêm 1 bằng ngày của ô E9. Nếu đúng, ô sẽ hiển thị giá trị rỗng; nếu không, sẽ hiển thị giá trị của ô AF9 cộng 1. Điều này có nghĩa rằng nếu tháng 2 có 28 ngày, ngày 29 sẽ là 01/03. Nếu tháng 2 có 29 ngày, hàm vẫn hiển thị ngày 29.

Bước 2: Tại ô AH9 (ngày 30), bạn sẽ sử dụng công thức sau: =IF(AG9="","",IF(DAY(AG9+1)=DAY(E9),"",AG9+1)).

• Giải thích hàm: Công thức đầu tiên kiểm tra nếu ô AG9 là rỗng, thì ô AH9 cũng sẽ rỗng (nghĩa là tháng 2 với 28 ngày sẽ không có ngày 30). Công thức thứ hai tương tự, đảm bảo rằng nếu tháng 2 có 29 ngày, sẽ không hiển thị ngày 30.

Bước 3: Đối với ô AI9 (ngày 31), áp dụng công thức: =IF(AH9="","",IF(DAY(AH9+1)=DAY(E9),"",AH9+1)).

• Giải thích hàm: Nếu ô AH9 rỗng, ô AI9 cũng sẽ rỗng (nghĩa là nếu không có ngày 30 thì cũng không có ngày 31). Công thức thứ hai đảm bảo rằng nếu tháng chỉ có 30 ngày, ô sẽ không hiển thị ngày 31.

Lưu ý: Khi các ô AG9, AH9, AI9 rỗng, hàm CHOOSE trong các ô AG10, AH10, AI10 có thể gặp lỗi. Tuy nhiên, điều này không ảnh hưởng đến bảng tính tổng thể, vì vậy bạn không cần phải lo lắng.

Bước 6: Cách Tự Động Đổi Màu Cho Ngày Chủ Nhật

Bước 1: Chọn toàn bộ nội dung trong bảng, bao gồm cả các ngày trong tháng và phần chấm công của nhân viên. Sau đó, nhấp vào "Conditional Formatting".

Bước 2: Trong danh sách "Conditional Formatting", chọn "New Rule". Tiếp theo, chọn tùy chọn cuối cùng là "Use a formula to determine which cells to format".

Bước 3: Trong ô "Format values where this formula is true", nhập công thức: =IF(E$10="Chủ nhật",1,0).

• Giải thích hàm: Công thức này kiểm tra nếu ô E10 có giá trị là "Chủ nhật". Nếu đúng, giá trị sẽ là 1 (đúng), nếu không thì là 0 (sai). Đảm bảo cố định hàng 10 với E$10 để áp dụng cho các ô khác trong cột.

Bước 4: Sau khi nhập công thức, nhấn vào nút "Format". Tại đây, bạn có thể tùy chỉnh kiểu chữ, màu chữ trong tab "Font", màu nền trong tab "Fill", và thiết lập đường viền ô trong tab "Border". Nhấn "OK" khi bạn đã hoàn tất thiết lập màu sắc.

Bước 7: Thiết Lập Ký Hiệu Chấm Công

Việc chọn ký hiệu chấm công có thể dựa trên sở thích cá nhân, nhưng cần tuân thủ nguyên tắc là các ký hiệu phải rõ ràng, dễ phân biệt và không được trùng lặp. Dưới đây là một số ký hiệu gợi ý cho các loại chấm công:

• Ngày công thực tế (đi làm đủ số ngày): X
• Nửa ngày công (đi làm nửa ngày): V
• Ngày nghỉ có lương (đi học, họp, nghỉ phép): P
• Nghỉ không lương: K
• Ốm đau, thai sản: O

Sau khi đã xác định các ký hiệu, bạn thực hiện các bước sau:

Bước 1: Tại cột Ngày công thực tế, nhập công thức: =COUNTIF($E11:$AI11,$G$33).

• Giải thích hàm: Công thức này sẽ đếm số lần xuất hiện của ký hiệu trong ô G34 trong vùng từ E11 đến AI11. Ô G34 chứa ký hiệu cho ngày công đủ, và vùng E11 đến AI11 là số ngày công của nhân viên đầu tiên (hàng 11). Cố định cột E và AI để công thức không bị ảnh hưởng khi sao chép.

Bước 2: Áp dụng quy trình tương tự cho các ô khác với các công thức cụ thể như sau:

• Ô Nửa công: =COUNTIF($E11:$AI11,$G$34)
• Ô Nghỉ hưởng lương: =COUNTIF($E11:$AI11,$G$35)
• Ô Nghỉ không lương: =COUNTIF($E11:$AI11,$G$36)
• Ô Ốm đau, thai sản: =COUNTIF($E11:$AI11,$G$37)

Bước 3: Đối với ô Tổng ngày công, công thức sẽ tùy thuộc vào cách tính của từng tổ chức. Ví dụ có thể là:

• Tổng ngày công = Ngày công thực tế + (Nửa công x 0.5) + Ngày nghỉ có lương + Ngày ốm đau, thai sản.

Bước 4: Sau khi hoàn thành việc đặt công thức, bạn có thể sao chép công thức này cho các nhân viên khác để hoàn thiện bảng chấm công.

Bước 5: Cuối cùng, bạn đã hoàn thành bảng chấm công cho tháng 1. Việc còn lại là sao chép sheet này cho các tháng còn lại trong năm.

Trên đây mình đã hướng dẫn chi tiết bạn cách tạo bảng chấm công trên Microsoft Excel từ A-Z. Chúc bạn thực hiện thành công và hãy tiếp tục theo dõi Phúc Anh để cập nhật những mẹo văn phòng thú vị nhất!