Hiển thị kết quả tìm kiếm từ 1 đến 5 trên tổng số: 5

Ðề tài: Format Conditional

  1. #1
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,201 lần trong 1,302 bài viết

    Format Conditional

    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.


  2. #2
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,201 lần trong 1,302 bài viết
    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.


  3. #3
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,201 lần trong 1,302 bài viết
    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


  4. #4
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,201 lần trong 1,302 bài viết

    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


  5. #5
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,201 lần trong 1,302 bài viết

    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.


Thông tin về chủ đề này

Users Browsing this Thread

Hiện có 1 người đang xem đề tài này. (0 thành viên và 1 khách)

Đề tài tương tự

  1. Hỏi về conditional format
    Viết bởi kchixinhdep trong chuyên mục Hỏi đáp những vấn đề khác
    Trả lời: 4
    Bài mới gởi: 29-01-14, 06:36 PM
  2. Cần giúp đỡ về conditional format.
    Viết bởi cr140n trong chuyên mục Thành viên giúp nhau
    Trả lời: 4
    Bài mới gởi: 16-10-12, 01:57 PM
  3. Giúp em về Conditional format với
    Viết bởi cuongsinh1 trong chuyên mục Lập Trình với Excel
    Trả lời: 17
    Bài mới gởi: 15-08-12, 09:34 PM
  4. Dùng Conditional Format thế nào cho trường hợp này???
    Viết bởi duyhoa83 trong chuyên mục Những vấn đề chung
    Trả lời: 7
    Bài mới gởi: 04-10-09, 09:37 PM
  5. Format Conditional
    Viết bởi handung107 trong chuyên mục Cấu trúc dữ liệu Excel và việc Xử lý dữ liệu
    Trả lời: 4
    Bài mới gởi: 10-06-06, 07:35 PM

Bookmarks

Bookmarks

Quyền Sử Dụng Ở Diễn Ðàn

  • Bạn không thể đăng đề tài mới
  • Bạn không thể đăng trả lời
  • Bạn không thể đăng file đính kèm.
  • Bạn không thể sửa bài viết.
  •  

Mudim v0.8 Tắt VNI Telex Viqr Tổng hợp
Chính tả Bỏ dấu kiểu mới  [Bật/Tắt (F9)]