Ngoài việc sử dụng tuỳ chọn Cell Value Is và các phép so sánh, 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ì CF được áp dụng. Để sử dụng công thức riêng trong CF, bạn hãy thay đổi tuỳ chọn Cell Value Is sang Formula Is trong hộp thoại CF, và đưa công thức của bạn vào khung trống nhập liệu bên cạnh. Kết quả công thức của bạn nên trả về giá trị luận lý True (1) hay False (0). Nếu công thức bạn trả về giá trị True thì CF sẽ được áp dụng. Nếu công thức của bạn trả về giá trị False thì CF sẽ không được áp dụng.
Các công thức phải bắt đầu là dấu bằng (=), trong công thức không thể tham chiếu trực tiếp đến một ô hay một vùng ở một worksheet hay workbook khác (Excel 2007 cho phép điều này). Chúng ta có thể định nghĩa tên (name) tham chiếu đến dữ liệu trên các vùng của các sheet 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 tự định nghĩa dựa trên bằng VBA trong công thức điều kiện.
Ngoài ra bạn còn có thể nhập vào một ô trên sheet hiện hành tham chiếu đến dữ liệu trong một sheet của workbook khác sau đó khi khai báo điều kiện cho CF thì bạn chỉ cần tham chiếu đến ô giữ địa chỉ này.
Ví dụ 4.1 . Bạn muốn tham chiếu đến dữ liệu trong ô A5 trong Sheet1 của workbook Baocao chẳng hạn, thì tại Sheet đang thực hiện CF bạn chọn một ô nào đó nhập vào địa chỉ như sau: =[Baocao.xls]Sheet1!A5 và khi khai báo đối số điều kiện cho CF bạn chỉ cần tham chiếu đến ô giữ địa chỉ này.
Một thuận lợi của việc sử dụng công thức trong CF 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ụ 4.2 . Hãy tô chữ màu đỏ cho các ô tại A13:A18 khi các ô tương ứng ở cột B lớn hơn 10
B1. Chọn vùng A13:A18
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:
- Chọn Formula is và nhập
- Tại ô trống kế bên có thể sử dụng công thức =IF(B13>10,TRUE,FALSE), hay đơn giản hơn =B13>10 hay =$B13>10 hay =B13:B18>10… đều được. Cách dùng địa chỉ trong công thức của CF sẽ được trình bày trong phần sau.
- Nhấn nút Format và chọn màu đỏ trong ngăn Font của hộp thoại Format Cells
- Nhấn OK hoàn tất chọn màu cho chữ
B4. Nhấn OK hoàn tất CF.
Địa chỉ Tuyệt đối và Tương đối trong công thức của CF
Khi bạn sử dụng công thức trong CF, 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ụ 4.3. Giả sử rằng chúng ta muốn áp dụng định dạng theo điều kiện cho vùng A13:A18, sẽ được tô chữ màu đỏ nếu giá trị trong vùng B13:B18 lớn hơn 10. Chúng ta có thể dùng công thức =B13>10 để làm điều này. Tức là giả sử ô B14 có giá trị là 11 thì giá trị trong ô A14 sẽ được tô chữ đỏ. Đó 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 A13:A18 tô chữ màu đỏ nếu giá trị ô B13 lớn hơn 10, tức là mỗi ô trong vùng A13:A10 sẽ luôn luôn được so sánh với ô B13. Trong trường hợp này chúng ta phải sử dụng công thức =$B$13>10.
Khi dùng điều kiện =$B$13>10 --> kết quả trả về False --> áp dụng CF này cho toàn vùng A13:A18 --> không có ô nào được tô chữ màu đỏ (đối chiếu với ví dụ 4.2 để thấy sự khác biệt).
Công thức mảng (array formula) trong CF
CF thực thi các công thức theo cách thức thực thi của công thức mảng trong bảng tính, do vậy bạn có thể sử dụng các công thức mảng trong CF. Tuy nhiên, kết thúc công thức mảng trong CF bạn không cần nhấn tổ hợp phím Ctrl+Shift+Enter để kết thúc công Excel luôn xử lý các công thức trong CF theo cách thức của công thức mảng trong bảng tính.
Sử dụng tên (Name) trong CF
Như đã nói ở trên, 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ụ 4.4. 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
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ụ 4.5. 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).
B1. Chọn vùng B28:B37 và vào hộp thoại CF
B2. Chọn Formula Is và nhập vào công thức =A28>39133
B3. Nhấn nút Format, vào ngăn Pattern, chọn màu xanh
B4. Nhấn OK hai lần để hoàn tất CF.
5. Thêm, sao chép, thay đổi và xoá CF
Thêm CF
B1. Chọn các ô cần bổ sung CF
B2. 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
B3. 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, …
B4. Nhấn nút Add..và lặp lại các bước 1, 2 và 3 để thêm CF mới.
Sao chép định dạng của CF cho các ô khác
B1. Chọn các ô có CF mà ta muốn sao chép
B2. Nhấn nút Format Painter ([IMG]file:///C:/DOCUME%7E1/ttphong/LOCALS%7E1/Temp/msohtmlclip1/01/clip_image001.gif[/IMG]) 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.
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.
Để chọn tất cả các ô đang có áp dụng CF bạn làm các bước sau:
B1. Chọn một ô bất kỳ trên sheet (hoặc chọn một ô đang áp dụng CF)
B2. Vào thanh Edit | chọn Go To (Ctrl+G)
B3. Nhấn vào nút Special…
B4. Nhấn vào tuỳ chọn Conditional formats. Khi đó có 2 tuỳ chọn kèm theo tại Data validation
- Chọn All: kết quả sẽ chọn tất cả các ô có áp dụng CF
- Chọn Same: kết quả sẽ trả về tất cả các ô có áp dụng CF giống với ô mà bạn chọn ở bước 1. Nếu ô chọn ở bước 1 không có áp dụng CF thì sẽ có thông báo không tìm thấy
7. Một số vấn đề thường gặp
CF không áp dụng đúng
Khi thấy kết quả định dạng trả về của CF không đúng bạn hãy kiểm tra lại:
- Kiểm lại việc áp dụng nhiều CF cho ô hay vùng: Nếu bạn áp dụng nhiều CF cho một ô hay vùng và nếu có nhiều hơn một CF là đúng (True) thì Excel chỉ áp dụng CF đúng đầu tiên có thứ tự ưu tiên cao.
- Kiểm tra xem các điều kiện của CF có giao nhau hay không:Nếu các điều kiện trong các CF bị trùng hay giao nhau một phần thì Excel cũng chỉ áp dụng điều kiện đúng đầu tiên. Ví dụ như bạn xác định điều kiện để tô màu nền vàng tại CF1 cho các ô có giá trị từ 100 đến 200, và tại CF2 bạn lại qui định áp dụng tô màu nền đỏ cho các giá trị nhỏ hơn 120. Khi đó các ô chứa giá trị từ 100 đến 119.999999999999 vẫn sẽ áp dụng định dạng của CF1.
- Kiểm tra lại các địa chỉ tham chiếu:Nếubạn dùng các công thức trong điều kiện của các CF thì hãy địa chỉ tham chiếu này có khả năng bị sai.
- Bạn kiểm tra lại xem các ô mà bạn đang chọn có thể đang áp dụng nhiều loại CF khác nhau vì hộp thoại CF chỉ có thể hiển thị thông tin thiết lập của một loại CF tại một thời điểm mà thôi.
- Bạn hãy chọn lại các ô khác và vào lại hộp thoại CF lần nữa.
- Chúng ta không thể định dạng độ cao dòng hoặc độ rộng cột bằng CF
Khi chúng ta áp dụng CF cho các ô thì Excel sẽ áp dụng các định dạng cho ô phụ thuộc vào giá trị trong các ô hay kết quả luận lý trả về từ các công thức. Do vậy bạn hãy kiểm tra lai:
- Nếu CF dùng tuỳ chọn Cell Value Is cho giá trị trong các ô đang chọn thì Excel xem giá trị chứa trong các ô là các chuỗi ASCII. Do vậy, các định dạng như in đậm, tô màu chữ có thể thay đổi nếu nội dung của ô thay đổi.
- Định dạng của các CF (nếu CF đúng) sẽ đè lên định dạng thông thường của Excel (các định dạng dùng lệnh Fotmat | Cell… ).
No comments:
Post a Comment