Cách Tạo Bảng Chấm Công Excel Tự Động Theo Tháng

Hôm nay, Tin học Đại Dương sẽ hướng dẫn bạn cách bảng chấm công Excel tự động theo tháng. Nhân sự có thể áp dụng  bảng mẫu này để chấm công nhân viên trong công ty một cách nhanh chóng. Đồng đồng, người đi làm có thể sử dụng làm bảng báo cáo tổng hợp tự động để làm thời gian biểu, theo dõi tiến độ công việc cũng như quản lý các đầu mục, đối tác một cách hiệu quả hơn.

1. Bảng chấm công Excel tự động – Báo cáo tổng hợp theo tháng – Kết quả đạt được

Các kiến thức bạn sẽ nhận được khi tạo bảng chấm công Excel:

• Sử dụng chức năng Data Validation để tạo Drop List (danh sách thả xuống theo Tháng).
• Sử dụng hàm DATE để hiển thị thời ngày đầu của tháng TỪ NGÀY.
• Dùng hàm EOMONTH để hiển thị ngày cuối tháng ĐẾN NGÀY.
• Sử dụng hộp thoại Format Cells để định dạng Ngày Tháng Năm.
• Sử dụng hàm IF để nhập nhập các ngày trong tháng (ngày 1 đến ngày 31).
• Dùng hàm WEEKDAY để định dạng ngày trong tuần ở dạng số.
• Sử dụng hàm SUM, MAX, MIN để tính tổng, giá trị lớn nhất, giá trị nhỏ nhất.
• Dùng chức năng Conditional Formatting để tô đậm ngày Chủ nhật.

2. Hướng dẫn chi tiết cách tạo bảng chấm công trong Excel

2.1 Chuẩn bị dữ liệu cho bảng chấm công Excel

Sheet 1 (báo cáo): Bạn tạo bảng dữ liệu thô như trong hình.

Sheet 2 (tháng): Nhập tháng 1 đến tháng 12 như trong hình.

Chuẩn bị dữ liệu cho bảng chấm công Excel

2.2 Tạo danh sách thả xuống theo Tháng

Tạo Drop List theo Tháng giúp bạn chuyển đổi và xem báo cáo tổng hợp các tháng một cách nhanh chóng nhất.

Data >> Data Validation >> Chức năng Data Validation.

Hộp thoại Data Validation hiện ra >> Tại mục Allow, chọn List >> Tại mục Source, bạn chọn vùng dữ liệu từ tháng 1 đến tháng 12 ($A$3:$A$14) >> OK.

2.3 Nhập công thức cho ô Từ Ngày

Đầu tiên, chúng ta sẽ làm ô Từ ngày cho tháng 1 trước. Ngày bắt đầu của tháng 1 là ngày 01/01/2024.

Bạn nhập công thức như sau: = DATE (YEAR(TODAY()));RIGHT(O1;2)*1;1)

Công thức của hàm Date: =DATE(year;month;day). Trong đó:

• Year: Ta sử dụng Year(today) để lấy năm hiện tại.
• Month: Ta cần lấy dữ liệu tháng, đó chính là 2 ký tự bên phải ô O1 nên sử dụng hàm Right (O1;2).
• Giải thích lấy 2 ký tự từ ô O1:  tháng 1-9 lấy 1 ký tự là được rồi nhưng tháng 11, 12 có 2 ký tự nên mình lấy 2 ký tự là ok nhất.
• *1: Do dữ liệu ở ô O1 đang là dạng chuỗi nên mình phải nhân với 1 để chuyển về dạng số.
• Day: Ngày bắt đầu của tháng là ngày 1 nên mình nhập số 1.

2.4 Nhập công thức cho ô Đến Ngày cho bảng chấm công Excel

Ô Đến Ngày có nghĩa là ngày kết thúc của tháng. Trong năm có tháng 30 ngày, 31 ngày, 29 ngày. Làm thủ công sẽ tốn thời gian để chỉnh lại. Thay vào đó, bạn có thể nhập công thức cho ngày cuối cùng của tháng cho bảng chấm công Excel như sau:

=EOMONTH (V1;0)

Kết quả trả về đang ở dạng số. Vì vậy, bạn cần vào Format Cells để định dạng Ngày Tháng Năm cho ô Đến Ngày nhé!

Giải thích:

Cú pháp =EOMONTH(start_date; months). Trong đó:

• Start_date: là ngày tham chiếu – ngày bất kỳ sử dụng làm mốc để tìm ra ngày cuối tháng.
• Months: nghĩa là số tháng trước hoặc sau start_date. Giá trị âm cho ngày trong quá khứ, giá trị dương cho ra ngày trong tương lai. Số tháng ở đây có nghĩa là số tháng sẽ được cộng thêm vào ngày tham chiếu của bạn. Nếu số tháng là 0 thì tháng ở ngày tham chiếu sẽ giữ nguyên. Số tháng là 1 thì ngày tham chiếu sẽ cộng thêm 1. Số tháng là -1 thì số tháng của ngày tham chiếu sẽ trừ đi 1. Số tháng là -2 thì số tháng của ngày tham chiếu sẽ trừ đi 2.
• Cụ thể: Ô V1 có ngày là 01/01/2024

= EOMONTH (V1;0) => Lấy ngày 01/01/2024 làm mốc thời gian, số tháng là 0 nên vẫn giữ nguyên tháng của mốc tham chiếu là tháng 1. Kết quả trả về là 31/01/2024.

Nếu = EOMONTH (V1;1) => Lấy ngày 01/01/2024 làm mốc thời gian. Số tháng là 1 nên tháng của ngày tham chiếu sẽ cộng thêm 1 là tháng 2, ngày cuối cùng của tháng 2 là ngày 29. Vậy kết quả trả về là ngày 29/02/2024.

2.5 Nhập các ngày trong tháng dưới dạng số

Nhập ngày cho ô B4 (ngày 01): Ngày đầu tiên của tháng 1 là ngày 1 nên tại đây, bạn chỉ cần nhập =V1 là được. Kết quả trả về là ##### nên bạn cần vào Format Cells để định dạng ngày lại.

Hộp thoại Format cells >> Custom >> Tại mục Type, nhập “dd” >> OK.

Nhập ngày cho ô B5 (ngày 02): Bạn nhập công thức =IF(B4<$AC$1;B4+1;””)

Giải thích: Nếu ô B4 nhỏ hơn ngày cuối tháng (ô AC1) thì kết quả trả về là giá trị ô B4 cộng thêm 1 (ngày 2 là ngày 1+1, ngày 3 là ngày 2+1, ngày 4 là ngày 3+1…), còn nếu là ngày cuối cùng rồi thì bỏ trống.

Nhập ngày cho các ô còn lại: Bạn để con chuột vào góc phải bên dưới ô C4, có hình “dấu cộng màu đen” hiện ra. Bạn chỉ cần kéo ngang qua các ô còn lại để điền ngày cho các ô còn lại là xong.

2.6 Điền Thứ cho các ngày trong tháng cho bảng chấm công Excel

Để điền Thứ cho các ngày trong tháng cho bảng chấm công Excel, bạn dùng hàm WEEKDAY. Hàm WEEKDAY là hàm trả về ngày trong tuần ở dạng số, các thứ trong tuần tương ứng là 1-7

= WEEKDAY(B4) => Kết quả là về là số 6. Có nghĩa là 01/01/2024 là thứ 6 trong tuần.

= WEEKDAY(C4) => Kết quả là về là số 7. Có nghĩa là 02/01/2024 là thứ 7 trong tuần.

= WEEKDAY(D4) => Kết quả là về là số 1. Có nghĩa là 02/01/2024 là ngày Chủ nhật.

Trong bài: Nếu ô B1 là 1 thì trả về là Chủ Nhật, còn lại sẽ trả về Thứ + số ngày

2.7 Tô đậm ngày Chủ Nhật cho bảng chấm công tự động Excel

Ví dụ bạn nhân sự dùng bảng báo cáo tổng hợp tự động theo tháng này để làm bảng chấm công cho nhân viên trong công ty. Thông thường, ngày Chủ nhật, nhân viên sẽ được nghỉ làm nên nhân sự phải “highlight” ngày này. Như vậy, nhân sự sẽ lưu ý hơn và tô đậm ngày Chủ nhật:

Home >> Conditional Formatting >> Highlight Cells Rules >> Text that Contains.

Hộp thoại Text that Contains hiện ra. Tại Format cells that contain the text, bạn nhập “Chủ nhật”. Tại With, bạn chọn màu để tô cho ngày Chủ nhật, rồi nhấn OK là xong.

2.8 Tổng hợp SUM, MAX, MIN

Hướng dẫn tính SUM, MAX, MIN cho bảng chấm công Excel:

• Để tính tổng các dữ liệu các ngày trong tháng, bạn sử dụng hàm =SUM()
• Để tìm dữ liệu lớn nhất trong các ngày, bạn sử dụng hàm =MAX()
• Để tìm dữ liệu NHỎ nhất trong các ngày, bạn dùng hàm =MIN()

Hy vọng các thông tin trên có thể giúp bạn biết cách lập bảng chấm công Excel tự động theo tháng nhanh chóng và tăng hiệu suất làm việc. Nếu bạn còn bất kỳ thắc mắc nào về các kiến thức tin học văn phòng, bạn có thể liên hệ Tin học Đại Dương nhé! Trung tâm luôn sẵn sàng giải đáp thắc mắc và hỗ trợ bạn!

—————————————————

          Tin Học Đại Dương

Học thật – Thi thật – Giá trị thật 

 Fanpage:Tin Học Đại Dương

 Website: tinhocdaiduong.vn

 Hotline: 028 37222786

 Cơ sở 1: Số 25, Đường 23, Phường Linh Chiểu, Tp Thủ Đức, HCM (0932413041)

 Cơ sở 2: Tầng 4, Nhà Văn Hóa Sinh Viên, Khu Đại Học Quốc Gia, Tp Thủ Đức, HCM (0979076051)