Theo dõi sinh nhật

Thông báo:

Do blogspot bị chặn bởi VNPT, nên mình sẽ không sử dụng blogspot nữa, thay vào đó là sử dụng website sau:

https://sites.google.com/site/takeyourcel/

Mong các bạn vui lòng ghé qua.

Thân!

THEO DÕI SINH NHẬT

I. Tác dụng
    Bạn được giao nhiệm vụ theo dõi ngày sinh nhật của các thành viên trong công ty, để hàng tháng có kế hoạch tổ chức sinh nhật. Nhưng nếu số nhân viên trong công ty nhiều thì việc theo dõi trở nên rất mất thời gian và ảnh hưởng tới công việc của bạn.
Với 1 vài thao tác đơn giản trên excel, việc theo dõi này trở nên rất dễ dàng và nhanh chóng.

Hình 1: Bảng mẫu theo dõi sinh nhật của công ty

Trong đó:
      - Bạn nhập bằng tay cho các cột: Họ tên, bộ phận, ngày sinh
      - Cột tháng sinh: Tính tự động
      - Cột Sinh nhật: Tính tự động
      - Cột Thông báo: Tự động thông báo theo điều kiện đã lập sẵn
      - Tô màu, định dạng các cột: Tự động thực hiện khi có thông báo
      - Ô Tháng (C2) nhập bằng tay, sau khi nhập nội dung trong ô Số người (F2) sẽ tính tự động, nội dung trong cột Thông báo sẽ tính tự động theo Tháng đã nhập.

Sau đây tôi xin hướng dẫn cách thực hiện việc này như sau:

II. Thực hiện
Đầ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:

Hình 2: Mẫu bố cục bảng

Để 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 bangr 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:

Hình 3: Phần Format cells / Bolder

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

Hình 4: Ví dụ về tên nhân viên, bộ phận, ngày sinh của từng người

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 chấm phẩy, có thể ở máy tính của bạn là dấu 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#")
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)))
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:

Hình 5: Bảng kết quả sau khi xây dựng công thức

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 (để mở chức năng này, vui lòng xem bài: Bài 8: Tùy chỉnh thanh công cụ trong Excel (áp dụng từ Excel 2010 trở lên)

Hình 6: Bảng New Formatting Rule hiện ra sau khi chọn Conditional Formatting / New Rule

Hình 7: Chọn dòng cuối cùng trong mục Select a Rule Type ở bảng New Formatting 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.

Hình 8: Bảng Format Cells hiện ra sau khi bấm vào ô Format

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

Hình 9: Bảng Conditional Formatting Rules Manager

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

III. Kết luận

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.

Bạn nào muốn xin file mẫu có thể liên hệ với mình qua email duongquan211287@gmail.com