1. Tầm quan trọng

Trong công tác quản lý nhân sự, ngoài việc chấm công, tính lương, quản lý hợp đồng… với vô vàn những thứ đau đầu nhức óc, bạn có thể thư giãn với bài viết này.

Như 1 chiếc đồng hồ báo thức, nó giúp bạn có kế hoạch để tổ chức sinh nhật cho các thành viên trong công ty.

Bằng việc làm này, bạn có thể dễ dàng tạo được thiện cảm trong mắt mọi người, nhờ vậy mà công việc của bạn cũng sẽ thuận lợi hơn.

2. Kết quả cần đạt được

Bạn sẽ có 1 cái bảng như sau:

Cứ mỗi khi đổi số tháng tại ô D2, trạng thái thông báo sẽ được thay đổi 1 cách tự động.

Giờ chúng ta hãy xem cách làm như thế nào nhé!

3. Cách làm

Đầu tiên bạn tạo form bảng dữ liệu, gồm:
– Tên bảng: Để dòng đầu tiên, có thể merge cell từ cột A đến cột G
– Dòng thứ 2 để ghi 2 cột: Tháng và Số người
– Cách ra 1 dòng để dễ co dãn khoảng cách phần bảng so với tên
– Dòng thứ 4 bạn dùng để ghi tiêu đề của các cột trong bảng: Số thứ tự, Họ và tên, Chức vụ (phòng ban), Ngày sinh, Tháng, Sinh nhật, Thông báo
– Các dòng tiếp theo để ghi theo từng người

Ta có mẫu như sau:

Để thực hiện việc kẻ khung của bảng, ta thực hiện như sau:
– Bôi đen phần bảng, gồm từ ô A4 đến ô G50 (ví dụ bảng tới dòng thứ 50)
– Chọn Format Cells
– Trong bảng Format Cells, bạn chọn phần Border
– Để chọn đường kẻ, bạn chọn mục Line phía bên trái, để kẻ đường viền, bạn bấm vào các ô thể hiện vị trí đường kẻ tại mục Border. Mục Presets giúp bạn thực hiện kẻ nhanh với các nội dung:
+ None: bỏ đường kẻ
+ Outline: Kẻ viền bên ngoài bảng
+ Inside: Kẻ viền bên trong bảng
Mô tả cụ thể theo hình sau:

Bạn có thể bôi đen riêng từng phần và kẻ theo ý muốn cho đến khi ưng ý.

Sau khi kẻ xong, bạn nhập 1 vài nội dung: tên nhân viên, ngày sinh vào bảng

Sau đây ta sẽ thực hiện việc xây dựng công thức cho những cột, ô còn lại: (Lưu ý dấu ngăn cách các phần trong hàm của tôi là dấu phẩy, có thể ở máy tính của bạn là dấu chấm phẩy)

* Cột Tháng sinh (Cột E):
Ta nhập công thức sau đây ở ô E5, các ô tiếp theo sẽ copy công thức xuống:
=RIGHT(“0″&MONTH(D5),2)
Hoặc =TEXT(MONTH(D5),”0#”)

Chú ý: “0” ở đây là số không, chứ không phải là chữ O
2 công thức trên có kết quả giống nhau, đều mang ý nghĩa là : Lấy giá trị tháng của cột Ngày sinh, định dạng dưới dạng 2 chữ số, nếu nhỏ hơn 10 thì có số 0 ở trước. Bạn chỉ nên dùng 1 trong 2 công thức.

* Cột Sinh nhật (Cột F)
Ta nhập công thức sau đây ở ô F5, các ô tiếp theo sẽ copy công thức xuống:
=IF(D5=””,””,DATE(YEAR(TODAY()),MONTH(D5),DAY(D5)))

Lưu ý: Hàm today() để xác định ngày hiện tại. Khi đặt trong hàm YEAR để xác định năm thì bạn phải lưu ý những chỗ đóng mở ngoặc

YEAR ( TODAY() )   => Tôi sử dụng dấu cách để làm rõ các dấu ngoặc, tránh bị nhầm lẫn
Tôi sẽ phân tích cụ thể ý nghĩa của công thức này như sau:
+ Dùng hàm if, nếu giá trị ở ô D5 (ngày sinh) là rỗng (không có giá trị gì), thì sẽ trả về giá trị rỗng cho ô sinh nhật (nếu không có ngày sinh thì sẽ không có sinh nhật)
+ Còn nếu điều trên là sai (tức là D5 khác rỗng, có ngày sinh), thì ngày sinh nhật sẽ tính như sau:
Hàm date: trả về giá trị ngày tháng, theo thứ tự trong hàm: năm, tháng, ngày
Năm: là Year(today()) tức là năm hiện tại. Lưu ý hàm today() sẽ trả về giá trị là ngày, tháng, năm hiện tại. Do đó ta thêm hàm Year để chỉ lấy năm hiện tại thôi.
Tháng: là tháng của D5 (ngày tháng năm sinh) -> chỉ lấy giá trị tháng
Ngày: là ngày của D5 (ngày tháng năm sinh) -> Chỉ lấy giá trị ngày

* Cột Thông báo (Cột G)
Ta nhập công thức sau đây ở ô F5, các ô tiếp theo sẽ copy công thức xuống:
=IF(D5=””,””,IF(TODAY()>F5,”Đã xong”,IF(TODAY()+7>=F5,”Chuẩn bị tổ chức”,IF(MONTH(D5)=$C$2,”Trong tháng có SN”,””))))

Tôi sẽ phân tích cụ thể ý nghĩa của công thức này như sau:
+ Hàm if thứ nhất: Nếu D5 là rỗng, thì sẽ nhận giá trị rỗng (không có ngày sinh thì không thông báo)
+ Hàm if thứ 2: sẽ bắt đầu thực hiện khi hàm if thứ 1 nhận giá trị logic của trường hợp sai (tức là D5 không rỗng, có ngày sinh):
– Today()>F5: nếu ngày hiện tại lớn hơn ngày sinh nhật, thì thông báo là Đã xong. Từ Đã xong là ký tự, nên được đặt trong dấu nháy kép để excel hiểu.
+ Hàm if thứ 3: Để thực hiện thông báo với điều kiện khác, được đặt trong trường hợp hàm if thứ 2 nhận giá trị logic là sai (tức là những trường hợp ngày hiện tại nhỏ hơn ngày sinh nhật, chưa đến ngày sinh nhật)
– Today()+7>=F5: nếu ngày hiện tại mà cộng thêm 7 nhỏ hơn hoặc bằng ngày sinh nhật (tức là cách ngày sinh nhật trong vòng 1 tuần), thì sẽ hiện thông báo là “Chuẩn bị tổ chức”
+ Hàm if thứ 4: Thực hiện thông báo khác, với trường hợp sai của hàm if thứ 3 (tức là trường hợp không phải cách sinh nhật 1 tuần, không phải trường hợp đã quá ngày sinh nhật)
– Month(D5)=$C$2: nếu tháng sinh bằng với tháng được chọn ở ô C2, thì sẽ hiện thông báo Trong tháng có sinh nhật
Trường hợp sai, thì sẽ nhận giá trị rỗng (tức là ngoài tất cả những khả năng nêu trên, thì đều trả về giá trị rỗng, không có thông báo  => chỉ còn sót lại trường hợp chưa đến sinh nhật, và không phải cách ngày sinh 1 tuần, không phải có sinh nhật trong tháng được chọn ở C2 => trường hợp này không cần thông báo)

* Ô đếm tổng số người có sinh nhật trong tháng (ô F2)
=COUNTIF($E$5:$E$50,$C$2)
Nội dung của hàm: Đếm số lần xuất hiện của giá trị tháng được chọn (ô C2) trong nội dung của cột Tháng sinh (từ ô E5 đến ô E50)

Sau khi đã xây dựng công thức ở các cột và ô xong, ta sẽ có kết quả như sau:

Tuy nhiên, để thuận tiện cho việc theo dõi, ta sẽ làm thêm chức năng tự động tô màu, thay đổi định dạng cho những trường hợp có thông báo. Ta thực hiện như sau:

– Bôi đen bảng dữ liệu từ ô A5 đến ô G50, chọn chức năng Conditional Formatting, sau đó chọn New Rule

Tại đây bạn nhập công thức để làm điều kiện như sau:
=IF($G5=”Đã xong”,TRUE,FALSE)
Ý nghĩa: Nếu giá trị trong cột G, bắt đầu từ ô G5 trở đi, có giá trị là Đã xong, thì là đúng, còn nếu không phái thì là sai.

Tiếp theo bạn bấm vào ô Format ở phía dưới, chọn định dạng theo ý muốn.

Tại đây, bao gồm:
Number: Định dạng kiểu dữ liệu
Font: Định dạng font chữ, kích cỡ, màu chữ…
Border: Kẻ khung
Fill: Màu nền của ô
Thường thì chỉ cần định dạng tô màu chữ, tô đậm, và tô màu nền cho ô.

Làm tương tự với các thông báo khác, mỗi lần thông báo bạn lại tạo 1 Rule mới trong Conditional Formatting

=IF($G5=”Chuẩn bị tổ chức”,TRUE,FALSE)
=IF(MONTH($D5)=$C$2,TRUE,FALSE)

Sau khi hoàn thành, bạn có thể kiểm tra các rule bằng cách bấm vào Conditional Formatting, chọn Manager Rule

Lưu ý bạn nên sắp xếp thứ tự các Rule theo đúng thứ tự xuất hiện trong hàm If đã viết ở cột Thông báo (đã xong ở đầu tiên, chuẩn bị ở thứ 2, có SN trong tháng thứ 3)

Cách sắp xếp: Bạn bấm rule cần sắp xếp, bấm vào nút mũi tên chỉ lên trên hoặc xuống dưới ở cạnh nút Delete Rule (Phía trên chữ Applies to) trong bảng trên để di chuyển theo thứ tự như ý muốn.

Ta được kết quả như sau:

Như vậy đã hoàn thành xong bảng theo dõi sinh nhật.
Bây giờ bạn thử thay đổi Tháng tại ô C2, hoặc thay đổi ngày sinh của các nhân viên sẽ thấy thông báo hiện ra có chính xác hay không

4. Nhận xét

Muốn thêm dòng, bạn chỉ cần copy công thức từ dòng trên xuống các dòng dưới đã thêm.

Chức năng Conditional Formatting hỗ trợ việc định dạng ô theo các điều kiện mà bạn thiết lập, giúp việc thông báo trên bảng tính trở nên tự động và dễ nhìn.

Việc xử lý các hàm ngày tháng sẽ dùng nhiều tới hàm Date, today, month, year… Ô nào đã chứa giá trị ngày thì bạn có thể sử dụng trực tiếp ô đó vào các phép tính + , – ngày tháng lẫn nhau.

Chúc các bạn áp dụng tốt kiến thức này vào công việc của mình.

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online