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.

Cập nhật bài viết:

Chia sẻ file excel theo dõi chấm công cán bộ công chức năm 2018

Chào các bạn, chúng ta lại gặp nhau trong một ứng dụng Excel mới. Đó là cùng nhau tìm hiểu và xây dựng biểu mẫu chấm công theo dõi cán bộ công chức (CBCC), người lao động trong cơ quan một cách tự động và dể dàng thực hiện.

Công tác văn phòng phải theo dõi và tham mưu kịp thời với lãnh đạo rất nhiều lĩnh vực trong đó có cả công tác quản lý cán bộ trong cơ quan. Vậy là công chức văn phòng chắc bạn đã từng chấm công (nói đúng hơn là theo dõi CBCC và người lao động vì không có cơ chế trừ lương hay phạt kỷ luật gì) mục đích cuối cùng là để đánh giá tác phong, nề nếp cũng như biết được hoặt động trong một năm của một đối tượng được theo dõi.

Việc theo dõi, chấm công CBCC được báo cáo hằng tháng được xem là phù hợp và kịp thời. Chính vì vậy mà mình cũng viết sẵn file Excel theo dõi CBCC được thống kê kết quả theo từng tháng.

Những gì được cập nhật trong file Excel theo dõi CBCC 2018

• Tất cả mọi thứ được tự động hóa
• Theo dõi tháng nào thì chọn tháng đó (giao diện: lựa chọn tháng theo dõi đặt trên cùng nên dể dàng chọn thực hiện)
• Chỉ khai báo một lần duy nhất, tất cả dữ liệu được tự động thêm vào bảng.
• Không sử dụng bất kỳ Macro hay VBA nên file này siêu nhẹ chạy tốt trên bất cứ máy tính nào.
• Tự động hiện thị nếu có theo dõi đối tượng trùng nhau
• Tự động hiện thị các ngày nghỉ (thứ 7, chủ nhật) bằng hình thức tô màu vàng và xanh mờ giúp bạn tránh nhập dữ liệu những ngày nghỉ.
• tự động hiện thị ngày tháng và thứ của ngày tháng đó theo năm bạn khai báo (ở trên mình khai báo năm 2018)

Tìm hiểu về file Theo Doi CBCC 2018 VP_IT.soft

1. Khai báo thông tin ban đầu

Bạn nhập vào mục DM Cán Bộ và nhập dữ liệu theo hướng dẫn.

Tại đây sẽ có một số lưu ý:

• Năm tài chính (hành chính): Bạn nhập năm muốn theo dõi, chấm công. Mình để 2018.
• Mã CB: Mã này được bạn thiết lập hoặc theo gợi ý của mình sẵn trong file. Mã này phục vụ tất cả các bảng chấm công nên bạn khai báo sao cho dể nhớ.
• Họ tên và chức vụ bạn nhập theo thực tế của đơn vị, nhập chính xác vì dữ liệu này được lấy tự động trong bảng chấm công.

2. Thực hiện theo dõi, chấm công theo tháng

Ví dụ trong tháng 1.

– Đầu tiên bạn nhấp chọn tháng 1

– Chọn đối tượng CBCC để theo dõi

Chương trình đã cài đặt sẵn lựa chọn theo danh sách xổ xuống Mã CB theo mã được khai báo ở bước 1 (Khai báo thông tin ban đầu), tại đây bạn chỉ được phép chọn đối tượng cần theo dõi. Khi chọn Mã CB thì cột Họ và tên, Chức vụ sẽ tự động hiện thị thông tin tương ứng như được khai báo ở Bước 1.

Lưu ý: Nếu bạn thấy Cột Mã CB hiện màu hồng có nghĩa bạn đã theo dõi trùng đối tương, bạn dể dàng loại bỏ đối tượng trùng.

Cột Họ và tên, Chức vụ được hiện thị tự động nên mình khóa để hạn chế sai xót về lỗi cấu trúc dữ liệu và bạn sẽ không thể nhập dữ liệu cho cột này. 

– Làm thế nào để nhập chấp công.

Mình khai báo và xây dựng công thức theo quy ước sau, NẾU bạn muốn thay đổi thì liên hệ mình để hướng dẫn.

• K: Không phép, không lý do
• T: Tập huấn
• HO: Họp, đi họp
• HC: Học, tham gia học theo phê duyệt của lãnh đạo
• O: Đau ốm
• V: Vắng buổi, có nghĩa là chỉ đi làm buổi sáng hoặc buổi chiều

Với mỗi đối tượng theo dõi nếu phát sinh các trường hợp vắng với những lý do được quy ước ở trên thì bạn chỉ việc nhập vào ô theo đúng ngày và đối tượng

Ví dụ: Cán bộ Nguyễn Thị N (mã CB: HU21) có các trường hợp vắng như sau:

Ngày 2/1: bị đau ốm                   Ta nhập ký hiệu: O

Ngày 10/1: Đi họp                      Ta nhập ký hiệu: HO
Ngày 15/1: Vắng một buổi         Ta nhập ký hiệu: V
Ngày 23/1: Vắng không lý do    Ta nhập ký hiệu: K

Như vậy chương trình sẽ thông kê kết quả trong tháng 1 của Cán bộ Nguyễn Thị N (mã CB: HU21) như sau:

Tổng ngày vắng là: 4 ngày và có các lý do được nêu

Nếu không tính vắng buổi thì là 3 ngày, vì trong nhiều trường hợp có thể vắng buổi.

Trên đây là mình vừa giới thiệu chương trình theo dõi, chấm công CBCC, người lao động ở cơ quan. Nếu bạn thích hãy chia sẻ bài viết này và để lại mail dưới bài viết, mình sẽ kiểm tra và gửi cho bạn.

Cám ơn vì xem qua bài viết, chúc các bạn một ngày tốt lành.

Link File: Tại đây 

Link google drive