Định dạng theo điều kiện (Conditional Formatting) trong Excel 2010

Định dạng có điều kiện – Conditional Formatting (viết tắt là CF) là chương trình được Microsoft bổ sung vào Excel kể từ phiên bản Microsoft Excel 97, được xem là một công cụ hữu ích giúp người dùng định dạng các chuỗi văn bản trong các ô, các giá trị và các ô về màu sắc, kiểu mẫu nền, kẻ khung…

Thao tác với các bảng trong Excel luôn đòi hỏi người sử dụng phải có hiểu biết về thủ thuật với tính năng bảng trong Excel để giúp cho công việc được thuận lợi và nhanh hơn. Nếu bạn không có những thủ thuật đó, việc ứng dụng các chức năng trong Exel sẽ tiêu tốn của bạn rất nhiều thời gian.

Sử dụng định dạng có điều kiện CF, các ô (cell) trong excel sẽ không còn định dạng như thông thường nữa mà sẽ thay đổi về màu sắc, kiểu thể hiện văn bản và số…  Khi người dùng xoá bỏ CF thì định dạng đã có trước kia của các ô này sẽ được phục hồi.

Nếu như CF trong các phiên bản Excel từ Excel 2003 trở về trước có một số giới hạn về số lượng điều kiện (tối đa là 3), thì đến Excel 2010 số lượng điều kiện cho phép là 64. Hãy cùng tìm hiểu công cụ tuyệt vời này trong phiên bản 2010 của Excel nhé.

1.  Giới thiệu chung về định dạng có điều kiện 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ả mãn đ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 mục “Lợi nhuận” ở bảng 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 giá 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.

2.  Thực hiện định dạng có điều kiện

Ta thực hiện theo 2 bước đơn giản:

Bước 1: Thiết lập một điều kiện để điều khiển việc thay đổi định dạng trong các ô

Bước 2:  Nhập dữ liệu. Nếu điều kiện bạn cài đặt phù hợp với dữ liệu thì việc định dạng sẽ được áp dụng.

Ví dụ: Sử dụng định dạng có điều kiện để định dạng các ô theo bảng sau:

–  Lựa chọn vùng bạn muốn để áp dụng định dạng có điều kiện. Trong ví dụ này, vùng được chọn là A1:C5.

–  Từ menu Format, kích chuột vào Conditional Formatting.

–  Theo minh họa trên, chúng ta sẽ làm nổi bật tất cả các giá trị từ 4 tới 6, nên nhập các số vào các trường hiểu chỉnh.

–  Nếu kích nút OK, thì việc định dạng sẽ không áp dụng cho các giá trị đó. Do vậy, bạn kích nút Format.

–  Hộp thoại Format Cells hiển thị, từ đó chúng ta có thể chỉ định cách dữ liệu được hiển thị.

–  Từ mục Color: lựa chọn một màu, chọn màu đỏ cho ví dụ trên.

–  Khi chọn xong, kích nút OK. Hộp thoại Conditional Formatting hiển thị lại.

–  Để thêm những định dạng có điều kiện khác, kích nút Add. Nếu không thì kích nút OK để bỏ qua hộp thoại.

3.  Thêm  định dạng có điều kiện

Bước 1: Chọn các ô cần bổ sung CF.

Bước 2:  Chọn tuỳ chọn là Cell Value Is hay Formula Is tuỳ bạn và nhập các đối số cần thiết của CF vào.

Bước 3:  Nhấn nút Format và chọn định dạng phù hợp với yêu cầu: tô màu chữ, nền, mẫu nền, kẻ khung, …

Bước 4:  Nhấn nút Add.. .và lặp lại các bước 1, 2 và 3 để thêm CF mới.

4.  Sao chép định dạng của CF cho các ô khác

Bước 1: Chọn các ô có CF mà ta muốn sao chép.

Bước 2:  Nhấn nút Format Painter trên thanh thực đơn Formatting và quét vào các ô mà ta muốn áp dụng CF đang sao chép.

5.  Thay đổi hoặc xoá CF

–  Thay đổi định dạng của CF: nhấn vào nút Format của CF mà bạn muốn thay đổi định dạng trong hộp thoại Conditional Formatting (mỗi CF sẽ có một nút Format riêng). Nhấn các nút Clear để xoá bỏ định dạng củ và chọn lại định dạng mới (hoặc chọn luôn định dạng mới cũng được).

–  Xoá các CF: nhấn vào nút Delete trong hộp thoại Conditional Formatting và chọn các CF mà bạn muốn xoá, sau đó nhấn nút OK để xoá.

Khi bạn muốn xoá nhanh tất cả các CF và tất cả các định dạng khác trong các ô đang chọn thì vào thanh thực đơn Edit | chọn Clear | chọn tiếp Formats.

6.  Sử dụng tên (Name) trong CF

Các công thức trong CF không thể tham chiếu đến các ô trong một sheet khác của cùng một workbook. Tuy nhiên bạn có thể khắc phục điều này bằng cách sử dụng tên (name). Định nghĩa một tên tham chiếu đến một vùng của sheet 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 ô A22 trong sheet tên CFI.4 màu đỏ đậm nếu giá trị bạn nhập vào trong ô A22 không có trong danh sách các giá trị trong vùng A1:A10 nằm trong sheet tên là Ref. Nếu bạn lập công thức như sau thì sẽ bị báo lỗi =COUNTIF(Ref!$A$1:$A$10,A22)=0. Để khắc phục điều này bạn đặt tên Mylist tham chiếu đến vùng =Ref!$A$1:$A$10 và sử dụng tên này trong công thức của bạn như sau: =COUNTIF(MyList,A22)=0

7.  Sử dụng Date và Time trong CF

Dates và times trong CF được xử lý dưới dạng các con số tuần tự. Ví dụ như bạn muốn so sánh giá trị trong các ô với ngày 17/02/2007 thì chính là bạn so sánh với con số tuần tự là 39130.

Ví dụ: Hãy tô nền màu xanh cho các ô thuộc vùng số liệu B28:B37 với điều kiện Ngày ở vùng A28:A37 tương ứng phải lớn hơn ngày 20/02/2007 (có số tuần tự là 39133).

Bước 1: Chọn vùng B28:B37 và vào hộp thoại CF.

Bước 2:  Chọn Formula Is và nhập vào công thức =A28>39133.

Bước 3: Nhấn nút Format, vào ngăn Pattern, chọn màu xanh.

Bước 4: Nhấn OK hai lần để hoàn tất CF.

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