Bài viết: Sử dụng định dạng có điều kiện (Conditional Formatting) để làm nổi bật ngày tháng trong Ex (2 người xem)

[kyo]

SỬ DỤNG ĐỊNH DẠNG CÓ ĐIỀU KIỆU (CONDITIONAL FORMATTING) ĐỂ LÀM NỔI BẬT NGÀY THÁNG TRONG EXCEL


Các hàm về Ngày tháng trong Excel có thể thực hiện các phép toán đối với ngày tháng như cộng hoặc trừ và tự động trả kết quả cho bảng tính. Hàm NOW tính toán giá trị dựa trên ngày hiện tại là 1 ví dụ tuyệt vời về các hàm Ngày tháng. Tuy nhiên, chúng ta vẫn cần thêm những cải tiến để ngày càng hoàn thiện hơn. Và việc kết hợp các hàm về Ngày tháng với Định dạng có điều kiện (Conditional Formatting) sẽ là 1 bước tiến xa để đến gần hơn với mức "trên cả tuyệt vời".

Những vấn đề cơ bản đối với Định dạng có điều kiện cho Ngày tháng:

Mở hộp thoại Định dạng có điều kiện cho Ngày tháng bằng cách : Home >> Conditional Formatting >> Highlight Cell Rules >> A Date Occurring.

Bạn có thể chọn các tùy chọn ngày tháng, từ hôm qua đến tháng sau.

Có 10 lựa chọn ngày tháng tạo ra 10 quy tắc dựa trên ngày hiện tại. Nếu bạn cần thêm những quy tắc khác (ví dụ, 2 tháng sau ngày hiện tại), bạn có thể tự tạo ra quy tắc mới cho riêng bạn. Bài viết này sẽ hướng dẫn bạn từng bước một cho một vài định dạng theo ngày tháng

I. NGÀY CUỐI TUẦN THẬT NỔI BẬT

Khi thiết kế một lịch tự động, bạn không cần phải tô màu các ngày cuối tuần. Với công cụ định dạng có điều kiện, bạn có thể tự động thay đổi màu sắc của ngày cuối tuần bằng cách dựa theo định dạng của hàm WEEKDAY. Giả sử bạn có bảng lịch chưa có định dạng có điều kiện:

Để thay đổi màu sắc của những ngày cuối tuần, mở Conditional Formatting >> New Rule.

Trong hộp thoại tiếp theo, chọn Use a formula to determine which cell to format.

Trong ô Format values where this formula is true, gõ công thức WEEKDAY để xác định xem ô nào là Thứ Bảy (6) và Chủ Nhật (7) : =WEEKDAY(B$8,2)>5

Tham số 2 được hiểu là Thứ 7 = 6 và Chủ Nhật = 7. Tham số này rất hữu ích cho việc kiểm tra những ngày cuối tuần.

Lưu ý: Trong trường hợp này, bạn phải khóa các tham chiếu của dòng để Định dạng có điều kiện làm việc cách chính xác cho các ô khác trong bảng lịch này. Sau đó, tùy chỉnh định dạng cho điều kiện của bằng cách nhấp vào nút Format, chọn 1 màu để tô đầy (trong ví dụ này là màu cam) và nhấn OK

Bây giờ, bạn vào Conditional Formatting >> Manage Rules

Chọn This Worksheet để xem các quy tắc toàn bảng tính thay vì lựa chọn mặc định. Trong Applies to, thay đổi phạm vi tương ứng với lựa chọn ban đầu của bạn khi tạo quy tắc. Bây giờ bạn sẽ thấy 1 màu khác nhau cho những ngày cuối tuần.

II. NGHỈ LỄ VUI VẺ

Để làm phong phú thêm bảng tính trước đó, bạn cũng có thể tô màu ngày lễ. Muốn tô màu, bạn cần có 1 cột với các ngày lễ bạn muốn làm nổi bật trong bảng tính (không nhất thiết phải ở cùng một sheet). Với ví dụ của trên, ta có những ngày lễ trong cột AH (liên quan đến năm 2015 ở ô B2). Một lần nữa, mở Conditional Formatting >> New Rule.

Trong trường hợp này, ta sử dụng công thức COUNTIF để đếm số ngày nghỉ lễ trong tháng hiện tại.
=COUNTIF($AH$8:$AH$16,B$8)

Sau đó, trong hộp thoại Manage Rules, chọn $B$8:$AF$16. Nếu bạn muốn làm nổi bật những ngày nghỉ hơn những ngày cuối tuần, bạn di chuyển các quy tắc ngày lễ lên đầu danh sách.

Và đây là kết quả:

Bạn thử thay đổi Tháng và Năm để xem như thế nào nhé.

III. QUÁ HẠN RỒI

Có nhiều bạn sinh viên quên trả sách cho thư viện dù đã quá hạn trả. Làm thế nào để phân loại theo thời gian quá hạn của các bạn? Phân loại theo màu sắc quả là ý kiến không tồi. Ta sẽ phân loại như sau:

- Màu vàng : 1 - 2 tháng
- Màu cam : 3 - 4 tháng
- Màu tím : >4 tháng

Bây giờ, chúng ta sẽ xây dựng 3 quy tắc định dạng có điều kiện, sử dụng hàm DATEDIF tương ứng cho 3 trường hợp:
=DATEDIF($B2,$E$2,"m")>1
=DATEDIF($B2,$E$2,"m")>2
=DATEDIF($B2,$E$2,"m")>4

Và kết quả là:

IV. ĐỔI MÀU

Thay vì chọn một màu khác nhau thiết lập cho từng giai đoạn trong khung thời gian,thử làm việc với các tùy chọn đổ màu để tô màu các ô.
Đầu tiên, đi vào 1 cột mới (cột E), tính toán theo thời gian số tuổi của từng thành viên với hàm DATEDIF và tham số "y".
=DATEDIF($D2,TODAY(),"y")

Sau đó chọn Conditional Formatting >> New Rule, chọn Format all cells based on their value và chỉnh các thông số như hình:

Kết quả là 1 thang màu được đổ từ màu cam sang màu xanh lá qua màn vàng. Càng gần đến 0, màu cam càng nhiều hơn, màu vàng sẽ nhiều hơn nếu gần với 18 và màu xanh lá sẽ đậm hơn nếu qua khỏi ngưỡng 30.

Bây giờ bạn thử thay đổi ngày sinh và trải nghiệm sự thay đổi của màu sắc nhé. Chúc bạn thành công với bài viết này.

Nguyễn Bảo Khanh.

Một số bài viết có liên quan:
1/ Chiêu số 19: Đánh dấu những ô chứa công thức bằng Conditional Formatting
2/ Chiêu số 18: Điều khiển Conditional Formating bằng checkbox
3/ Chiêu số 11: Tìm dữ liệu xuất hiện 2 hoặc nhiều lần bằng công cụ Conditional Formating

 

[Noble]

Có chiêu nào làm công thức sắp xếp học lực cho học sinh không bác ơi? Giúp em

 

[voquangduy]

=DATEDIF($B2,$E$2,”m”)>1
=DATEDIF($B2,$E$2,”m”)>2
=DATEDIF($B2,$E$2,”m”)>4

Cái này đúng phải là =DATEDIF($D2,$E$2,”m”)>1 chứ anh Kyo nhỉ