Conditional Formatting

I. Các vấn đề cơ bản

1. Giới thiệu CF

Định dạng theo điều kiện là công cụ cho phép bạn áp dụng định dạng cho một ô (cell) hay nhiều ô (range of cells) trong bảng tính và sẽ thay đổi định dạng tùy theo giá trị của ô hay giá trị của công thức.

Ví dụ như bạn có thể tạo cho định dạng của ô đó là chữ in đậm màu xanh khi giá trị của nó lớn hơn 100. Khi giá trị của ô thoả điều kiện thì các định dạng bạn tạo ra ứng với điều kiện đó sẽ được áp dụng cho ô đó. Nếu giá trị của ô không thoả điều kiện bạn tạo ra thì định dạng của ô đó sẽ áp dụng định dạng mặc định (default formatting)



Một ô có thể có 3 định dạng theo điều kiện. Ví dụ như bảng phân tích độ nhạy của Lợi nhuận bên dưới ta sẽ dùng CF cho các ô C21:K31 với 3 điều kiện:

• Nếu giá trị của ô lớn hơn 0 thì nó sẽ được thể hiện là màu xanh (lời),
• Nếu gía trị của ô là 0 thì chữ sẽ màu cam (hoà vốn),
• Và nếu giá trị của ô nhỏ hơn 0 thì chữ màu đỏ (lỗ).

Bạn chú ý rằng CF giống như việc thêm một hay nhiều công thức vào mỗi ô mỗi khi bạn sử dụng, vì vậy áp dụng CF cho một số lớn các ô có thể gây ra việc thực hiện chương trình bị chậm đi. Do đó bạn hãy chú ý khi áp dụng CF cho một số lớn các ô trong bảng tính.

Hai minh hoạ đơn giản đã trình bày chỉ là phần nổi rất nhỏ về khả năng của CF, các phần tiếp theo sẽ giúp bạn từng bước tiếp cận và làm chủ CF – một tính năng tuyệt vời của Excel.

2. CF dùng danh mục điều kiện sẵn có

Trong phần này sẽ trình bày về tuỳ chọn CF đơn giản nhất là Cell Value is trong hộp thoại CF, và kết hợp với các toán tử trong danh sách sẵn có để ra điều kiện định dạng. Hộp định dạng theo điều kiện cho Excel XP được thể hiện ở hình dưới đây.



Trong đó có các tuỳ chọn:

• Cell Value Is: căn cứ vào giá trị chứa trong ô để làm đối số so sánh và từ đó áp dụng các định dạng nếu thoã mãn điều kiện (điều kiện trả về TRUE trong kết quả so sánh).
• Formula Is: căn cứ vào kết quả tính toán của các công thức làm đối số so sánh và từ đó áp dụng các định dạng nếu thoã mãn điều kiện (kết quả so sanh là TRUE). Phần này sẽ trình bày ở mục sau.

Giải thích các toán tử so sánh:

• Between: giá trị trong ô nằm trong đoạn từ a đến b (bao gồm 2 cận trên và dưới a, b)
• Not between: giá trị trong ô không nằm trong đoạn [a, b]
• Equal to: giá trị trong ô bằng (=) với giá trị so sánh
• Not equal to: giá trị trong ô không bằng (≠) giá trị so sánh
• Greater than: giá trị trong ô lớn hơn (>) giá trị so sánh
• Less than: giá trị trong ô nhỏ hơn (<) giá trị so sánh
• Greater than or equal to: giá trị trong ô lớn hơn hoặc bằng (≥) giá trị so sánh
• Less than or equal to: giá trị trong ô nhỏ hơn hoặc bằng (≤) giá trị so sánh

Ví dụ 2.1 : Dùng CF định dạng cho các ô trong vùng B4:E12 sao cho các giá trị lớn hơn 100 sẽ tô màu chữ xanh và in đậm.

B1. Chọn vùng cần định dạng B4:E12
B2. Vào Format | chọn Conditional Formatting… hộp thoại Conditional Formating hiện ra
B3. Chọn các tuỳ chọn như hình sau:

• Tại Condition 1, chọn tuỳ chọn là Cell Value Is
• Chọn toán tử so sánh là greater than
• Nhập vào giá trị 100 tại hộp trống bên phải

B4. Nhấn nút Format… hộp thoại Format Cells xuất hiện như hình sau

• Chọn Bold (In đậm) tại Font style
• Chọn màu xanh tại Color
• Nhấn OK hoàn tất định dạng

B5. Nhấn OK để bắt đầu áp dụng CF cho các ô trong vùng đang chọn.


Ví dụ 2.2 : Dùng CF định dạng cho các ô trong vùng H4:K12 sao cho các giá trị từ 80 đến 100 sẽ tô màu chữ xanh, in đậm và nền ô màu vàng; các ô còn lại tô chữ màu tím và nền ô màu xám.

B1. Chọn vùng H4:K12 cần định dạng CF
B2. Vào Format | chọn Conditional Formatting… hộp thoại Conditional Formating hiện ra
B3. Chọn các tuỳ chọn như hình sau:

• Tại Condition 1, chọn tuỳ chọn là Cell Value Is, chọn toán tử so sánh là between và nhập vào giá trị 80 và 100 tại 2 ô trống kế bên. Nhấn nút Format:
  • Tại ngăn Font chọn Bold tại Font style, chọn tiếp màu xanh tại Color
  • Chuyển qua ngăn Patterns và chọn màu vàng tại Cell shading
  • Nhấn OK hoàn tất CF1.
• Nhấn nút Add >> để thêm điều kiện mới
• Tại Condition 2, chọn tuỳ chọn là Cell Value Is, chọn toán tử so sánh là not between và nhập vào giá trị 80 và 100 tại 2 ô trống kế bên. Nhấn nút Format:
  • Tại ngăn Font chọn màu tím tại Color
  • Chuyển qua ngăn Patterns và chọn màu xám tại Cell shading
  • Nhấn OK hoàn tất CF2.

B4. Nhấn OK để bắt đầu áp dụng CF cho các ô trong vùng đang chọn và bên dưới là kết quả




Ví dụ 2.3 : Dùng CF định dạng cho bảng kết quả phân tích độ nhạy của Lợi nhuận tại vùng C21:K31 với 3 điều kiện:

• Nếu giá trị của ô lớn hơn 0 thì nó sẽ được thể hiện là màu xanh (lời),
• Nếu gía trị của ô là 0 thì chữ sẽ màu cam (hoà vốn),
• Và nếu giá trị của ô nhỏ hơn 0 thì chữ màu đỏ (lỗ).

Bạn hãy tự thực hiện các thao tác sao cho các CF1, CF2 và CF3 như hình sau:



Liệu chúng ta có thể áp dụng nhiều hơn 3 điều kiện cho giá trị trong các ô khi sử dụng các phiên bản Excel 2003 trở về trước không? Câu trả lời cho vấn đề này là có thể, chúng ta sẽ tiếp tục thảo luận về CF trong những bài tiếp theo.

3. Thứ tự ưu tiên của các CF

Khi bạn có hơn một điều kiện áp dụng cho một ô (cell) hay một vùng (range) thì các CF này sẽ được áp dụng theo trình tự ưu tiên của chúng, các CF nằm trên sẽ có mức ưu tiên cao hơn các CF nằm dưới. Đối với Excel 2003 trở về trước thì trình tự ưu tiên là CF1 à CF2 à CF3, đối với Excel 2007 CF nào nằm trên trong danh sách sẽ ưu tiên hơn CF nằm dưới. Do vậy chúng ta phải thật cẩn thận khi sắp xếp thứ tự ưu tiên của các CF nhằm tránh các kết quả không mong muốn. Bảng tóm tắt bên dưới mô tả cách thực thi CF của Excel trong các phiên bản trước Excel 2007.

Điểm khác biệt về CF trong Excel 2007

Excel 2007 cho phép áp dụng nhiều CF vào cùng một ô (cell) hay vùng (range) nếu các CF đúng (True) trong khi Excel 2003 trở về trước thì chỉ áp dụng duy nhất một CF đúng có thứ tự ưu tiên cao hơn và bỏ qua các CF có ưu tiên thấp hơn mặc dù nó đúng. Trong Excel 2007, khi chúng ta thiết lập nhiều CF cho một vùng các ô trong bảng tính, và khi có nhiều CF đúng (TRUE) đồng thời thì khi đó có khả năng các CF này có mâu thuẫn với nhau:

• Khi các CF không mâu thuẫn với nhau: Ví dụ như CF1 sẽ áp dụng định dạng cho các ô là chữ in đậm và CF2 sẽ áp dụng định dạng cho các ô chữ màu xanh. Khi cả CF1 và CF2 đúng thì không có mâu thuẫn xảy ra và các ô thoã điều kiện sẽ được định dạng chữ màu xanh và in đậm.
• Khi các CF có mâu thuẫn với nhau: Ví dụ như CF1 sẽ áp dụng định dạng cho các ô là chữ màu đỏ và CF2 sẽ áp dụng định dạng cho các ô chữ màu xanh. Khi cả CF1 và CF2 đúng thì có mâu thuẫn xảy ra và các ô thoã điều kiện sẽ chỉ được áp dụng định dạng của CF1 là chữ màu đỏ (vì CF1 có mức ưu tiên cao hơn CF2).

Nhằm đảm bảo tính tương thích ngược với các phiên bản Excel 2003 trở về trước, khi các bạn sử dụng CF trong Excel 2007 thì nên sử dụng tuỳ chọn Stop If True trong hộp thoại Manage Rules.

• Nếu chọn Stop If True tại CF1 thì Excel chỉ kiểm tra duy nhất CF1 bỏ qua tất cả các CFs bên dưới CF1.
• Nếu chọn Stop If True tại CF2 thì Excel sẽ kiểm tra CF1 và CF2 bỏ qua tất cả các CFs bên dưới CF2.
• Nếu chọn Stop If True tại CF3 thì Excel sẽ kiểm tra CF1, CF2 va2 CF3 bỏ qua tất cả các CFs bên dưới CF3.
  
• Nếu chọn Stop If True cho tất cả các CF thì định dạng trả về trong Excel 2007 sẽ giống như định dạng trả về trong Excel của các phiên bản trước...
  

Ví dụ 3.1: Vùng địa chỉ B3:B8 được áp dụng CF với 3 điều kiện sau:

• Nếu giá trị trong ô lớn hơn 10 thì định dạng chữ in đậm
• Nếu giá trị trong ô lớn hơn 20 thì định dạng chữ màu đỏ
• Nếu giá trị trong ô lớn hơn 30 thì định dạng nền ô màu cam

Hình trên trình bày các qui định của các CF1, CF2 và CF3 và kết quả trả về chỉ là của CF1, các CF2 và CF3 mặc dù đúng nhưng bị bỏ qua. (Excel 2003 trở về trước)



Hình trên trình bày các qui định của các CF1, CF2 và CF3 và tuỳ chọn Stop If True cho CF3. Kết quả trả về chỉ là áp dụng tất cả các định dạng của CF1, CF2 và CF3 cho các ô thoã điều kiện. Nếu chọn Stop If True cho cả 3 CF1, CF2 và CF2 thì kết quả định dạng trả về sẽ giống như của Excel 2003 trở về trước.