Format Conditional

[handung107]

Lê Văn Duyệt​

Ở đây sẽ mô tả công cụ định dạng theo điều kiện. Công cụ mạnh này được đưa vào phiên bản Excel97. Nó không có trong những phiên bản trước.

1/ Định dạng theo điều kiện là gì?

Định dạng theo điều kiện (Conditional Formatting -CF) là một 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à đậm 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ư nếu giá trị của ô lớn hơn 200, thì nó sẽ được thể hiện là màu đỏ, nhưng nếu gía trị ở giữa khoảng 100 và 200 thì nó sẽ được thể hiện là màu xanh.

Bạn chú ý rằng định dạng theo điều kiện 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 định dạng theo điều kiện 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 cho một số lớn các ô.

2/ Định dạng theo điều kiện đơn giản

Định dạng theo điều kiện đơn giản nhất là sử dụng lựa chọn (Option) Cell Value is trong hộp định dạng theo điều kiện (CF dialog box), và sử dụng một trong các toán tử so sánh sẵn có. Hộp định dạng theo điều kiện cho Excel 2000 được thể hiện ở hình dưới đây.

Hộp thoại này chỉ ra điều kiện định dạng cho ký tự trong ô có màu đỏ khi giá trị trong ô nằm giữa hai giá trị là 10 và 20. Ngoài toán tử between còn có các toán tử so sánh khác như greater than hay less than…

Để áp dụng định dạng theo điều kiện cho một ô hay nhiều ô, đầu tiên bạn chọn khoảng mà bạn muốn được định dạng theo điều kiện, sau đó bạn mở hộp thoại CF từ menu Format. Kế tiếp bạn thay đổi toán tử between bằng các toán tử nào mà bạn muốn. Kế tiếp bạn nhập các giá trị cho điều kiện này. Kế tiếp bạn nhấn (click) vào nút Format trên hộp thoại để chọn định dạng. Không phải tất cả các định dạng đều có thể áp dụng cho định dạng theo điều kiện. Ví dụ, bạn không thể thay đổi Font chữ hay kích thước Font chữ trong định dạng theo điều kiện. Cuối cùng bạn nhấn OK để áp dụng định dạng của bạn. Bạn có thể thêm vào định dạng theo điều kiện thứ hai hay thứ ba. Mỗi một định dạng theo điều kiện có thể có định dạng khách nhau.

3/ Thứ tự của các điều kiện

Khi bạn có hơn một điều kiện cho một ô, chỉ có định dạng theo điều kiện đầu tiên đúng được áp dụng. Còn những định dạng theo điều kiện khác không có giá trị. Ví dụ, giả sử rằng bạn có 3 định dạng theo điều kiện cho ô A1 như sau:

1) Bold Text (chữ đậm) khi giá trị lớn hơn 10
2) Red Text (chữ màu đỏ) khi giá trị lớn hơn 20
3) Gray Background (màu nền xám) khi giá trị lớn hơn 30

Trong trường hợp này, nếu giá trị của ô A1 là 100 thì text trong ô A1 sẽ được in đậm nhưng không màu đỏ và nền không có màu xám, bởi vì điều kiện đầu tiên được thoả mãn, các điều kiện còn lại không có giá trị. Vì vậy bạn phải đặt điều kiện của bạn theo một thứ tự đúng.

1) Gray Background (màu nền xám) khi giá trị lớn hơn 30
2) Red Text (chữ màu đỏ) khi giá trị lớn hơn 20
3) Bold Text (chữ đậm) khi giá trị lớn hơn 10

Trong ví dụ này , ô A1 sẽ xuất hiện với nền màu xám nếu giá trị lớn hơn 30, xuất hiện với màu đỏ nếu giá trị trong khoảng 21 và 30, xuất hiện với chữ in đậm nếu giá trị trong khoảng 11 và 20 và định dạng mặc định nếu giá trị trong khoảng 0 và 10.

Các đìêu kiện không bao giời kết hợp với nhau. Điều này có nghĩa trong ví dụ ở trên, nếu giá trị ô là 40 thì ô A1 sẽ xuất hiện với màu nền xám (điều kiện 1), nhưng không xuất hiện với chữ đỏ (điều kiện 2) hay không xuất hiện với chữ được in đậm (điều kiện 3).

Logic của định dạng theo điều kiện có thể được mô tả như sau:

If Condition1 = True Then
Apply Format1
Else
If Condition2 = True Then
Apply Format2
Else
If Condition3 = True Then
Apply Format3
Else
Apply DefaultFormat
End If
End If
End If

Logic của định dạng theo điều kiện không phải là

If Condition1 = True Then
Apply Format1
End If
If Condition2 = True Then
Apply Format2
End If
If Condition3 = True Then
Apply Format3
End If
Hiểu được sự khác biệt giữa hai cấu trúc logic ở trên rất là quan trọng.

 

[handung107]

4/ Sử dụng công thức trong định dạng theo điều kiện

Ngoài việc sử dụng so sánh Cell Value Is , bạn có thể xây dựng những công thức riêng của bạn để xác định khi nào thì định dạng theo điều kiện được áp dụng. Để sử dụng công thức riêng (custom formula) bạn thay đổi Cell Value Is sang Formula Is trong hộp thoại CF, và đưa công thức của bạn vào text box vừa xuất hiện. Công thức của bạn nên trả về giá trị True hay False. Nếu công thức bạn trả về giá trị True thì định dạng theo điều kiện được áp dụng. Nếu công thức của bạn trả về giá trị False thì định dạng theo điều kiện sẽ không được áp dụng.

Một thuận lợi của việc sử dụng công thức riêng (custom formula) trong định dạng theo điều kiện là cho phép bạn thay đổi định dạng của một ô dựa trên giá trị của một ô khác. Ví dụ, bạn muốn ô A1 xuất hiện màu đỏ khi ô B1 lớn hơn 10, bạn có thể sử dụng công thức sau =IF(B1>10,TRUE,FALSE) , hay đơn giản hơn =B1>10 . Bạn có thể sử dụng bất kỳ công thức nào của Excel, ngoại trừ một sô trường hợp sau:

Trong công thức không thể tham chiếu đến một vùng ở một worksheet hay workbook khác.
Bạn không thể sử dụng những hàm trong module Add-in. Nhưng bạn có thể sử dụng các hàm dựa trên VBA trong công thức bạn lập. (a VBA based function)

5/ Địa chỉ Tuyệt đối và Tương đối trong định dạng theo điều kiện

Khi bạn sử dụng công thức người dùng (custom formular) trong định dạng theo điều kiện, bạn cần nhận thức được sự khác nhau giữa địa chỉ tuyệt đối và địa chỉ tương đối. Nếu bạn sử dụng định dạng theo điều kiện để áp dụng cho nhiều ô (range of cells), thì bạn sử dụng địa chỉ tương đối. Ví dụ, giả sử rằng chúng ta muốn áp dụng định dạng theo điều kiện cho khoảng A1:A10, sẽ được in đậm nếu giá trị trong khoảng B1:B10 lớn hơn 10. Chúng ta có thể dùng công thức =B1>10 để làm điều này. Tức là giả sử ô B2 có giá trị là 11 thì giá trị trong ô A2 sẽ được in đậm. Đó là điều mà chúng ta thường muốn. Tuy nhiên giả sử rằng chúng ta muốn định dạng khoảng A1:A10 in đậm nếu giá trị ô B1 lớn hơn 10, tức là mỗi ô trong khoảng A1:A10 sẽ luôn luôn được so sánh với B1. Trong trường hợp này chúng ta phải sử dụng công thức =$B$1>10.

 

[handung107]

6/ Sử dụng tên trong định dạng theo điều kiện

Như đã nói ở trên, các hàm người dùng (custom functions) trong định dạng theo điều kiện không thể tham chiếu đến các ô trong một worksheet khác trong cùng một workbooks. Tuy nhiên bạn có thể khắc phục điều này bằng cách sử dụng tên. Định nghĩa một tên tham chiếu đến một khoảng worksheet khác, và sử dụng tên đó trong công thức của bạn (với chú ý các địa chỉ tuyệt đối và tương đối như đã nói ở phần trên.

Ví dụ, giả sử rằng bạn muốn ô A1 trên Sheet1 màu đỏ nếu giá trị bạn nhập vào trong ô A1 không có trong danh sách các giá trị trong khoảng B1:B10. Nếu bạn lập công thức như sau thì sẽ bị báo lỗi COUNTIF(Sheet2!$B$1:$B$10,A1)=0 . Để khắc phục điều này bạn đặt tên Mylist tham chiếu đến khoảng =Sheet2!$B$1:$B$10 và sử dụng tên trong công thức của bạn như sau: =COUNTIF(MyList,A1)=0

7/ Áp dụng định dạng theo điều kiện để phát hiện nhập trùng số liệu

Highligh khi nhập trùng số liệu

Giả sử rằng tôi muốn khi tôi nhập dữ liệu vào trong vùng có tên là range1 (giả sử range1 tham chiếu đến vùng Sheet1!$A$1:$A$10), nếu dữ liệu tôi bị trùng thì nó sẽ in đậm hay tô nền cho tôi (bạn chọn kiểu format bằng cách click nút Format) thì tôi có thể nhập vào công thức sau =IF(COUNTIF(Range1, A1)>1,TRUE,FALSE)


Hy vọng rằng một số kiến thức trên sẽ giúp ích một phần nào công việc của bạn.

Lê Văn Duyệt

Mọi ý kiến góp ý xin bạn liên lạc về:

levanduyet@yahoo.com

 

[handung107]

Dùng Conditional Formatting để Thay đổi định Dạng ô Hiện Hành

Có người đặt trường hợp là làm thế nào để thay đổi màu trong một ô (ô này nằm trong một khoảng nào đó, theo yêu cầu của người sử dụng) khi người dùng di chuyển tơi. Tôi xin giới thiệu một cách là dùng Conditional Formatting

Đầu tiên để làm được điều này tôi cần có một hàm để trả về hàng hiện tại hay cột hiện tại của ô hiện tại và hàm này được đặt trong module1. Đoạn mã đó như sau: Option Explicit
Function AC(Row As Boolean) As Long
' Trả về hàng hay cột của ô hiện hành
AC = 0
On Error Resume Next
If Row Then
AC = ActiveCell.Row
Else
AC = ActiveCell.Column
End If
End Function




Chúng ta cũng cần sự trợ giúp của Worksheet_SelectionChange, với phương thức Calculate của ActiveSheet. Đoạn mã trong module của worksheet như sau:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
Sau đó bạn chọn khoảng mà bạn muốn định dạng, chọn Format | Conditional Formatting... và điền vào như hình sau:



Sau đó bạn chọn OK. Khi đó nếu bạn di chuyển trong khoảng này thì ô hiện hành sẽ thay đổi màu mỗi khi bạn di chuyển.

Lược dịch từ internet.

Chúc các bạn thành công.

Lê Văn Duyệt

 

[handung107]

Format Conditional Add 6 điều kiện

Dựa vào 6 điều kiện mà chúng ta có thể thay đổi font của cell như sau:

6 điều kiện đó thể hiện như sau:

- Nếu giá trị nhỏ hơn hay bằng 0, font sẽ là màu đỏ.

- Nếu gía trị lớn hơn 0 nhưng nhỏ hơn hay bằng 20, font sẽ là màu xanh lá cây.

- Nếu giá trị lớn hơn 20 nhưng nhỏ hơn 31, font sẽ là màu xanh.

- Nếu giá trị giữa 31 và 40 thì font màu nâu vàng nhạt.

- Nếu giá trị giữa 41 và 50 thì font màu Grey.

- Nếu giá trị lớn hơn hay bằng 51 thì font màu nâu.

Để thực hiện được điều này bạn chọn Format>Format Cells - Number và dùng Custom Format như sau:

[Red][<=0]0;[Green][<=20]0;[Blue]0

Chọn OK và trở về Excel. Bây giờ cũng với các ô vừa chọn ở trên (chú ý là bạn đừng chọn các ô khác) bạn vào Format>Conditional Formatting và chọn format criteria như dưới đây:

1) Condition 1 - Điều kiện 1: Giá trị ô từ 31 và 40. Chọn Format và chọn màu Tan cho màu của font.

2) Condition 2 - Điều kiện 2: Giá trị ô từ 41 và 50. Chọn Format và chọn Grey-50% for the Font color.

3) Condition 3 - Điều kiện 3: Giá trị ô lớn hơn hay bằng 51. Chọn Format và chọn màu của font là màu nâu.

Bạn làm như trên bạn sẽ có thể thay đổi màu của font dựa vào 6 điều kiện.