Hướng dẫn tạo lịch (calendar) công tác, theo dõi, chấm công tự động bằng Excel

Nhatthienkt.net - Thủ thuật Excel tuần này giới thiệu đến bạn đọc cách làm bộ lịch tự động bằng Excel, bạn có thể áp dụng thủ thuật này để tạo ngay cho mình một bảng lịch chấm công hoặc theo dõi ngày tháng cho công việc. Nó khá hữu ích, hãy cùng tôi tìm hiểu trong bài viết dưới đây.

Tôi sẽ hướng dẫn thực hiện trên Excel 2016, các phiên bản Excel khác cũng tương tự.

Hình 1 - Tạo lịch tự động với Excel 2016

Những gì là cần thiết để tạo ngay bộ lịch ngày tháng

Sử dụng các hàm ngày tháng năm (date, day, month, year)

Đầu tiên bạn cần lấy bộ công cụ DEVELOPER trong Excel

- Deleloper là bộ cung cụ nhằm hỗ trợ cho ta phát triển, sáng tạo thêm trong Excel một cách trực quan nhất. Nếu bạn sử dụng Access bạn sẽ hiểu được sự quan trọng của nó. và chắc nó là cần thiết để ta thực hiện tạo lịch tự động.

- Bấm vào vị trí trống trên thanh Ribbon chọn mục Customize the Ribbon

 - Tiếp đến tick chọn ô Developer (xem hình)

- Quan sát trên thanh Ribbon ta thấy có thêm  DEVELOPER

Bước 1: Tạo một danh sách lựa chọn các tháng (T) trong năm N

- Tạo danh sách chọn tháng với 1 sheet mới. vd tên sheet là "Tháng". Ta viết bằng Tiếng Anh nhé, vì máy tính hiểu tiếng Anh và tự động hiểu ngày của tháng trong năm tương ứng.

January
February
March
April
May
June
July
August
September
October
November
December
Ứng với Tháng 1, Tháng 2,... tháng 12.

Bước 2: Tạo thêm 1 sheet tên "ChamCong2016" chẳng hạn

- Tại bảng "ChamCong2016" chúng ta cần thiết lập, tạo bảng biểu dể theo dõi (ví dụ như Hình 1)

Bước 2.1: Tạo danh sách lựa chọn tháng

Tại vị trí B1 bấm vào Developer/Insert/Combo Box

- Kéo thả Combo Box vào ô B1

- Bấm phải chọn Forrmat Control

- Lấy dữ liệu tháng từ A1:A12 trong Sheet "Thang" được tạo ở bước 1

- Mục "Cell Link": Để B1 vì để vị trí đó phù hợp cho việc ở các bước sau

- Mục "Down line" cái này bạn muốn nó xổ xuống bao nhiêu tháng thì chọn, nó mặc định là 8

- Chú ý là các giá trị mang tính tuyệt đối (tức là cố định các ô, các hàng)

Bây giờ ta có thể xổ xuống chọn tháng

Bước 2.2. Tạo năm hành chính

Tại cột B2 bạn nhập công thức chọn năm hiện hành

=Year(today())

Bước 2.3: Ghi chú tháng bằng Tiếng Việt

- Tại ô C1 bạn nhập

 ="Tháng"&" "&B1

Bước 2.4: Tạo ngày của tháng

- Trước tiên tại ô D7 ta nhập công thức

=DATE(B2;B1;1)

Có nghĩa là lấy ngày của năm (B2) của tháng (B1) và lấy ngày đầu tiên, tức là ngày 01

- Tiếp theo tại ô E7 ta nhập công thức

=D7+1

Có nghĩa là: Những ngày tiếp theo sẽ bằng ngày thứ nhât (ngày 01 ứng ô D7) cộng thêm 1

Và kéo copy công thức đến ô AH7 ta được kết quả như hình dưới (do mình đã xử lý ở bước 2.6  nên tháng 2 sẽ thấy có 29 ngày)

Bước 2.5 Tạo thứ tương ứng với ngày trong tháng

- Để tạo thứ 2, Thứ 3, ....

- Công thức cột D8, D9,....AH9 hoàn toàn giống công thức Tại bước 2.4, tuy nhiên bạn sửa lại định dạng ô thành "dddd"

Bước 2.6: Xử lý tháng 28 hoặc 29 hoặc 30 hoặc 31 ngày

- Chúng ta biết đấy, nếu chọn tháng 2 thì chỉ có 28 ngày, năm 2016 thì có 29 ngày, các tháng 1, 3, 5, 7, 8, 10, 12 có 31 ngày. Các tháng còn lại có 30 ngày. Tuy nhiên sự đồng bộ máy tính với Excel nói riêng nó cung cấp sẵn ngày có trong tháng. Chúng ta chỉ việc điều chỉnh một chút công thức cho phù hợp.

- Với cách xử lý dưới đây sẽ cho kết quả  như hình dưới, 

- Ví dụ như hình trên nếu lựa chọn tháng 9 thì nó xuất hiện 30 ngày, ô AH tức ngày thứ 31 nó không hiện, để ô trống.

- Cách thực hiện như sau:

+ tại ô ngày thứ 29 (tức ô AF7) ta nhập công thức

=IF(DAY(AE7+1)=DAY($D$7);"";AE7+1)

Giải thích: Nếu tháng chỉ có 28 ngày thì ngày thứ 29 sẽ để trống, ngược lại nếu tháng có 29 ngày thì nó bằng ngày 28+1

+ Tại ô ngày thứ 30 (tức ô AG7) ta nhập công thức

=IF(AF7="";"";IF(DAY(AF7+1)=DAY(D7);"";AF7+1))

Giải thích: Nếu ngày thứ 29 là "trống (tức là không có)" thì ngày thứ 30 vẫn để trống. Ngược lại nếu có ngày 29  thì ngày thứ 30 sẽ bằng ngày 29+1

+ Tại ô ngày thứ 31 (tức ô AH7) ta nhập công thức

=IF(AG7="";"";IF(DAY(AG7+1)=DAY(D7);"";AG7+1))

Giải thích: Tương tự như ô AG7

- Với ô thứ theo ngày bạn nhập công thức:

Ô AF8

=IF(AF7="";"";AE8+1)

Ô AG8

=IF(AG7="";"";AF8+1)

Ô AH8

=IF(AH7="";"";AG8+1)

2.7 Tạo đánh dấu các ngày nghỉ trong tuần, ngày nghĩ Lễ theo quy định

- Với ngày nghỉ trong tuần

- Đầu tiên chọn tất cả các ô trong bảng chứa các ngày trong tháng. chọn chức năng Conditional Formatting (định dạng theo điều kiện), trong mục Condittional Formatting, chọn More Rules.

- Tại mục Format values where this formula is true [2], ta nhập hàm

 =if(weekday(D$7)=1;true;false)

- Chọn màu sắc đánh dấu [3] và [4],[5] ta được kết quả sau

Ngày nghỉ chủ nhật được đánh dấu màu đỏ và nền vàng

- Nếu nghỉ thứ 7 thì làm thêm cái nữa

 =if(weekday(D$7)=7;true;false)

- Tự động điền ngày in dữ liệu

Khi in bảng, nó tự động điền theo ngày hiện tại.

Ví dụ: Ta nhập công thức nối hàm như sau:

="Đơn vị, ngày"&" "&DAY(TODAY())&" "&"tháng"&" "&MONTH(TODAY())&" "&"năm"&" "&YEAR(TODAY())

Đến đây thôi, chắc đã định hướng cho các bạn cách tạo một bảng theo dõi, chấm công đơn giản. Với bảng tổng họp sẽ có bài viết sau.

Link File: Tại đây

Copy bài viết vui lòng ghi Nguồn: NhatThienkt.net