Hướng dẫn cách tạo bảng chấm công chuyên nghiệp trên Excel

Khám phá cách tạo bảng chấm công tiện lợi và hiệu quả trên Excel để quản lý nhân sự một cách chuyên nghiệp!

1. Hướng dẫn tạo bảng chấm công trên Microsoft Excel

Trước khi bắt đầu tạo file Excel, hãy đặt mình vào tình huống và suy nghĩ về cấu trúc của bảng chấm công bạn muốn tạo. Bạn cần xác định số lượng sheet, nội dung và cách hoạt động của bảng.

Dựa trên các dữ kiện trên, một mô hình bảng chấm công cơ bản có thể như sau:

• Bảng gồm 13 sheet, mỗi sheet tương ứng với 1 tháng và một sheet danh sách nhân viên.
• Các ký hiệu chấm công có thể được tùy chỉnh phù hợp với người sử dụng.
• Bảng chấm công sẽ hiển thị đầy đủ thông tin về ngày và thứ trong tháng, với màu sắc phân biệt các ngày cuối tuần.
• Chấm công hàng ngày và tự động tính tổng số ngày công cuối tháng.
• Tên nhân viên được tự động nhập từ danh sách nhân viên.
• Khả năng liên kết các tháng với nhau để thuận tiện trong quản lý.

Với cấu trúc này, ban đầu chỉ cần 2 sheet là đủ. Đó là:

• 1 sheet chứa danh sách nhân viên (DSNV).
• 1 sheet là tháng mẫu - thường là tháng 1 (được chuẩn bị kỹ lưỡng, cung cấp đầy đủ thông tin cho 1 tháng, các tháng sau có thể sao chép và sửa tên lại cho phù hợp).

2. Danh sách nhân viên

Trong sheet này, chủ yếu sẽ chứa thông tin cá nhân của nhân viên bao gồm: Họ và tên, mã nhân viên (để tránh sự trùng lặp), ngày sinh, quê quán, số CMND, ngày vào làm,...

Chú ý: Để tạo liên kết với các sheet khác, hãy để trống khoảng từ 2-3 dòng ở trên cùng. Bên trái của bảng để dành 1 cột cho các ghi chú bổ sung (nếu cần).

Để định dạng các cột là Ngày/Tháng/Năm, bạn cần thực hiện các bước sau:

Bước 1: Trong cột ngày sinh, hãy bôi đen toàn bộ cột (click vào đầu cột và kéo chuột xuống dưới).

Bước 2: Click chuột phải và chọn Định dạng Ô > sau đó chọn Tùy chọn.

Bước 3: Sau khi chọn Tùy chọn, tìm định dạng dd-mm-yyyy để định dạng cột thành ngày/tháng/năm > bấm OK để hoàn tất.

3. Mẫu bảng chấm công (tháng 1)

Bước 1: Tạo cấu trúc bảng chấm công với các nội dung sau:

• Tiêu đề - Bảng chấm công, Tháng, Bộ phận chấm công, Định mức công trong tháng.
• Các cột 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 - số ngày lớn nhất trong tháng), 4 - 5 cột quy đổi ra công ngày, 1 cột để ghi chú.

Bước 2: Điều chỉnh độ rộng của các cột sao cho phù hợp và dễ nhìn. Các cột ngày có thể co lại để tiết kiệm không gian, còn cột tên và mã nhân viên thì cần hiển thị đầy đủ.

• Cách điều chỉnh độ rộng: Bôi đen các cột ngày và cột quy đổi > Chuột phải > chọn Độ rộng cột và thiết lập kích thước là 3,13 (tương ứng 30 pixel)> bấm OK.

Bước 3: Sau khi bấm OK, bạn đã hoàn tất cấu trúc bảng chấm công như hình dưới.

4. Tạo ngày tháng cho bảng chấm công

Bước 1: Trước hết, hãy quyết định năm sử dụng cho bảng chấm công. Ví dụ, trong hình, chúng ta sẽ chọn năm 2023.

Bước 2: Tại ô xác định tháng chấm công, nhập hàm sau để xác định ngày: '=ngày($D$1;1;1)'.

• Lưu ý: Dấu phân cách trong hàm có thể là ';' hoặc ',' tùy theo cài đặt máy tính của bạn.
• Chức năng của hàm Ngày: Xác định giá trị ngày tháng theo thứ tự
  năm, tháng, ngày trong hàm.

Bước 3: Sau khi nhập hàm, chuột phải vào ô xác định tháng chấm công > chọn Định dạng ô > chọn Tùy chỉnh > nhập ['tháng 'mm' năm 'yyyy] (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 + dấu cách + năm + dấu cách + dấu nháy kép + yyyy) vào ô Kiểu > nhấn OK để hoàn tất.

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

Bước 5: Ô ngày 2 với công thức là '= ô ngày 1 + 1' > Bôi đen từ ngày 2 đến ngày 31 và nhấn tổ hợp phím Ctrl + R.

Bước 6: Bôi đen tất cả các ô và chuột phải > chọn Định dạng ô > chọn Tùy chọn > nhập giá trị dd (chỉ hiển thị ngày) vào ô Kiểu > nhấn OK.

Bước 7: Tại ô dưới ô ngày (E10), nhập hàm như sau: '=CHOOSE(WEEKDAY(E9);'Chủ nhật';'Thứ hai';'Thứ ba';'Thứ tư';'Thứ năm';'Thứ sáu';'Thứ bảy')'.

• Weekday (E9): lấy giá trị thứ tự trong tuần của ô E9. Nếu không có quy định về thứ tự ngày trong tuần, hàm weekday sẽ trả về theo thứ tự Chủ nhật, thứ 2, thứ 3,... thứ 7 và trả về giá trị từ 1 đến 8.
• Choose: hàm chọn giá trị trả về. Giá trị đầu tiên trong hàm là căn cứ xác định. Các giá trị tiếp theo là nội dung tương ứng với giá trị đầu tiên.

Giải thích thêm: Trong ví dụ này, ngày 01/01/2023 khi sử dụng hàm weekday sẽ trả về giá trị là 1, sử dụng hàm choose sẽ tương ứng với Chủ nhật. Hàm weekday không tự trả về thứ theo tiếng Việt, nên phải kết hợp 2 hàm này để xác định thứ trong tuần.

Bước 8: Copy công thức từ ô đầu tiên (ngày đầu tiên) đến ô cuối cùng (ngày cuối cùng) > Chọn biểu tượng Orientation > Chỉnh dữ liệu dọc là xong.

5. Điều chỉnh tháng không đủ 31 ngày

Sau các bước trên, chúng ta đã hoàn thành việc chỉnh sửa một tháng bình thường. Tuy nhiên, không phải tháng nào cũng có đủ 31 ngày (ví dụ sau tháng 1). Để tránh hiển thị ngày của tháng khác vào tháng này, ta thực hiện các bước sau:

Bước 1: Bắt đầu từ ngày 29, chúng ta sẽ điều chỉnh hàm như sau: =NẾU(NGÀY(AF9+1)=NGÀY(E9);'';AF9+1).

• Nội dung hàm: Nếu giá trị ngày của ô AF9 + 1 bằng giá trị ngày của ô E9, sẽ trả về giá trị rỗng; nếu không, sẽ trả về giá trị của ô AF9 + 1 (Nghĩa là nếu tháng 2 có 28 ngày, ngày thứ 29 sẽ là ngày 01/03. Còn nếu tháng 2 có 29 ngày, hàm sẽ hiển thị ngày 29).

Bước 2: Tại ô AH9 (ngày 30), sử dụng hàm sau: =NẾU(AG9='';'';NẾU(NGÀY(AG9+1)=NGÀY(E9);'';AG9+1)).

• IF đầu tiên: Nếu ô AG9 là rỗng, thì ô AH9 cũng sẽ là rỗng (nghĩa là vào trường hợp tháng 2 có 28 ngày thì không có ngày thứ 30).
• IF thứ 2: tương tự với hàm IF tại ô AG9, tức là nếu tháng 2 có 29 ngày, thì không hiển thị ngày 30.

Bước 3: Tại ô AI9 (ngày 31), sử dụng hàm sau: =NẾU(AH9='';'';NẾU(NGÀY(AH9+1)=NGÀY(E9);'';AH9+1)). Nội dung hàm:

• IF thứ 1: Nếu tại ô AH9 là rỗng, thì ô AI9 cũng sẽ là rỗng (tức là vào trường hợp không có ngày 30 thì cũng không có ngày thứ 31).
• IF thứ 2: trường hợp tháng chỉ có 30 ngày thì không có ngày 31.

Lưu ý: Khi các ô AG9, AH9, AI9 là rỗng thì hàm CHOOSE ở các ô AG10, AH10, AI10 sẽ phát sinh lỗi. Nhưng không ảnh hưởng nhiều đến bảng tính nên không cần lo lắng.

6. Phương pháp tự đổi màu tự động cho ngày Chủ nhật

Bước 1: Bôi đen nội dung trong bảng bao gồm ngày trong tháng và phần chấm công của nhân viên > nhấp vào Định dạng có điều kiện.

Bước 2: Trong mục Định dạng có điều kiện, chọn Luật mới > chọn dòng cuối cùng là Sử dụng một công thức để xác định các ô cần định dạng.

 Bước 3: Nhấp vào mục Định dạng các giá trị mà công thức này là đúng, nhập hàm sau: =NẾU(E$10='Chủ nhật';1;0).

• Nội dung hàm: Nếu ngày trong tuần là ô E10 là 1 (tức là chủ nhật) thì là đúng, không thì sẽ sai. Áp dụng định dạng của Định dạng có điều kiện. Đặt cố định cho E$10 là cố định hàng 10, tính từ cột E.

Bước 4: Sau khi đặt hàm, chọn vào ô Định dạng > có thể chọn font chữ, màu chữ tại tag Font, màu nền tại tag Fill, kẻ đường viền ô tại tag Border > Nhấn Ok khi đã thiết lập màu xong.

Bước 5: Khi hoàn tất định dạng, nhấn OK để thu được bảng dữ liệu đã tô màu cho các ngày là Chủ nhật như hình dưới.

7. Đặt ký hiệu chấm công

Việc lựa chọn ký hiệu chấm công là tùy thuộc vào sở thích của bạn, nhưng cần đảm bảo các ký hiệu rõ ràng, dễ phân biệt và không trùng lặp. Dưới đây là một số ví dụ về ký hiệu chấm công:

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

Sau khi đã liệt kê các ký hiệu, bạn sẽ thực hiện các bước sau:

Bước 1: Trong cột Ngày làm đủ công, bạn sẽ sử dụng hàm sau: =COUNTIF($E11:$AI11,$G$33). 

• Nội dung hàm: Đếm số lần xuất hiện của giá trị ô G34 trong vùng E11 đến AI11. Giá trị ô G34 là ký hiệu của ngày làm đủ công, vùng E11 đến AI11 biểu thị số ngày công trong tháng của người đầu tiên (hàng 11), cố định cột E và AI để khi sao chép công thức không bị ảnh hưởng tới vùng chấm công.

Bước 2: Tiến hành tương tự với các ô còn lại, sử dụng các công thức như sau:

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

Bước 3: Đối với ô cuối cùng là ô Tổng ngày công. Cách tính sẽ phụ thuộc vào từng tổ chức khác nhau, dưới đây là một ví dụ:

• Tổng ngày công = Ngày công thực tế + Nửa ngày công x 0,5 + Nghỉ có lương + Nghỉ ốm, thai sản.

Bước 4: Sau khi hoàn thành đặt hàm, bạn chỉ cần sao chép công thức xuống cho các nhân viên khác và bảng chấm công sẽ được tự động cập nhật như dưới đây.

Bước 5: Bạn đã hoàn thành bảng chấm công cho tháng 1. Để hoàn tất cho các tháng tiếp theo trong năm, chỉ cần sao chép bảng này ra và điều chỉnh lại tháng là được.

Cuối cùng, bạn đã hoàn thành một bảng chấm công đầy đủ. Với hướng dẫn chi tiết trên, bạn có thể tạo bảng mà không lo lắng về việc tính toán các ngày cuối tháng. Nếu cần thêm thông tin, hãy để lại bình luận bên dưới nhé.

Bạn đang muốn sở hữu một chiếc laptop linh hoạt, giúp bạn xử lý mọi công việc văn phòng một cách nhanh chóng và hiệu quả? Hãy truy cập ngay vào đường link dưới đây để khám phá các dòng laptop tại Mytour với giá cả phải chăng nhất!

MUA LAPTOP VĂN PHÒNG TẠI Mytour 

Xem thêm:

• Cách chèn ảnh hàng loạt vào Excel một cách nhanh chóng giúp bạn tăng hiệu suất làm việc.
• Cách tách dữ liệu số từ ô dữ liệu kết hợp trong Microsoft Excel mà bạn có thể chưa biết.
• Mẹo xử lý dữ liệu số không cần sử dụng hàm trong Excel một cách đơn giản mà hiệu quả.