CONDITIONAL FORMATTING - 123doc

CF trong Excel là một công cụ mạnh giúp chúng ta đị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… CF khi áp dụng vào các ô cell nó sẽ đè

GPE - Tháng 7/ 2010

Nội dung

1 Các vấn đề cơ bản 5

1.1 Giới thiệu 5

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

1.3 Thứ tự ưu tiên của các CF 11

1.4 CF sử dụng các công thức làm điều kiện 13

1.4.1 Địa chỉ Tuyệt đối và Tương đối trong công thức của CF 14

1.4.2 Công thức mảng (array formula) trong CF 15

1.4.3 Sử dụng tên (Name) trong CF 15

1.4.4 Sử dụng Date và Time trong CF 16

1.5 Thêm, sao chép, thay đổi và xoá CF 17

1.5.1 Thêm CF 17

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

1.5.3 Thay đổi hoặc xoá CF 17

1.6 Dùng Go To tìm kiếm các ô có áp dụng CF 18

1.7 Một số vấn đề thường gặp 19

1.7.1 CF không áp dụng đúng 19

1.7.2 Các thiết lập trong hộp thoại CF biến mất 19

1.7.3 Không thể sử dụng một số kiểu định dạng trong CF 19

1.7.4 Các định dạng màu Font và tô bóng cho ô tự động thay đổi 19

2 Các ví dụ về CF và các kỹ thuật nâng cao 20

2.1 Các ví dụ cơ bản 20

2.1.1 Các vấn đề thường gặp 20

2.1.1.1 Ẩn các lỗi (Hide Errors) 20

2.1.1.2 Tô màu nền các ô rỗng (ISBLANK) 20

2.1.1.3 Ẩn bớt các giá trị trùng (Hide Duplicate Values) 21

2.1.1.4 Tô màu các giá trị trùng lặp trong cột (Highlight Duplicates in Column) 22

2.1.1.5 Tô màu các phần tử thuộc danh sách (Highlight Items in a List) 22

2.1.1.6 Tô màu các con số trùng với các con số cho trước 23

2.1.1.7 Tô màu các ngày sắp đến hạn (Highlight Upcoming Expiry Dates) 23

2.1.1.8 Ẩn nội dung các ô khi in (Hide Cell Contents When Printing) 24

2.1.1.9 Tô màu nền cách dòng (Shade Alternating Rows) 24

2.1.1.10 Tô màu nền nhóm N dòng cách quãng (Shade Bands of Rows) 25

2.1.2.2 Tô màu nền các ngày từ 1/1/2001 đến ngày 30/4/2001 28

2.1.2.3 Tô màu nền các ngày trong tuần hiện hành (tuần bắt đầu là ngày chủ nhật) 28

2.1.2.4 Tô màu nền các ngày cuối tuần (tuần bắt đầu là ngày chủ nhật) 29

2.1.2.5 Tô màu nền cho các ngày lễ 29

2.1.2.6 Tô màu các cột là ngày chủ nhật hàng tuần 30

2.1.2.7 Sử dụng hàm DateDif và CF để tô nền các thành viên nhỏ hơn 18 tuổi 31

2.1.2.8 Kết hợp TODAY và MONTH tô nền những người có sinh nhật trong tháng 31

2.1.3 Một số ví dụ về CF với dữ liệu dạng Text 32

2.1.3.1 Tô màu nền các ô chứa chuỗi cần tìm 32

2.1.3.2 Tô màu nền các ô có chứa chuỗi con cần tìm 32

2.1.3.3 Tô màu nền các ô chứa chuỗi có các chữ bắt đầu giống chuỗi cần tìm 33

2.1.3.4 Tô màu cả dòng khi có một ô thuộc dòng có chứa chuỗi cần tìm 34

2.1.3.5 Tô màu các dòng có lượng hàng tồn kho 35

2.1.4 Các ví dụ CF sử dụng hàm thống kê 36

2.1.4.1 Tìm và tô màu nền của ô chứa giá trị lớn nhất trong vùng số liệu 36

2.1.4.2 Tìm và tô màu nền của ô chứa giá trị lớn nhất, nhì, ba trong vùng số liệu 36

2.1.4.3 Tìm và tô màu nền của ô chứa giá trị nhỏ nhất trong vùng số liệu 37

2.1.4.4 Tìm và tô màu nền của ô chứa giá trị nhỏ nhất khác không (0) trong vùng số liệu 37

2.1.4.5 Tìm và tô màu nền của ô chứa giá trị nhỏ nhất, nhì, ba trong vùng số liệu 37

2.1.4.6 Tìm và tô nền của ô chứa giá trị lớn hơn giá trị trung bình cộng 38

2.2 Các ví dụ nâng cao 38

2.2.1 Tô màu các ô chứa công thức 38

2.2.2 Tô màu các ô đặt ở chế độ unlock 39

2.2.3 Tìm các số có sai biệt lớn hơn khoảng ±5% 39

2.2.4 Tìm nhóm 5 có giá trị cao nhất/ thấp nhất 40

2.2.5 Tìm nhóm 10% có giá trị cao nhất/ thấp nhất 41

2.2.6 Tạo sơ đồ GANTT 42

2.2.7 CF khi có nhiều hơn 3 điều kiện 43

3 Định dạng theo điều kiện trong Excel 2007 & 2010 47

3.1 Giới thiệu 47

3.2 Các qui luật có thể áp dụng khi sử dụng CF tự tạo 49

3.3.2 Sử dụng color scales 52

3.3.3 Sử dụng Icon Sets 54

3.3.4 Chỉ cho hiển thị một icon trong bộ Icon? 57

3.4 Định dạng theo điều kiện với công thức làm điều kiện 59

3.4.1 Tô màu các ngày cuối tuần 60

3.4.2 Tô màu nền cách dòng 61

3.4.3 Tô màu nền trắng đen kiểu carô 62

3.4.4 Hiển thị giá trị tổng khi tất cả số liệu được nhập đầy đủ 63

3.5 Một số thao tác cần thiết khi sử dụng định dạng theo điều kiện 64

3.5.1 Quản lý các qui luật định dạng theo điều kiện 64

3.5.1.1 Thêm mới qui luật định dạng 65

3.5.1.2 Hiệu chỉnh qui luật định dạng đang có 65

3.5.1.3 Sắp xếp thứ tự áp dụng các qui luật định dạng 66

3.5.2 Sao chép định dạng theo điều kiện 67

3.5.3 Xóa định dạng theo điều kiện 67

3.5.4 Tìm kiếm các ô có áp dụng CF trong bảng tính 68

Trần Thanh Phong | Các vấn đề cơ bản 5

Định dạng theo điều kiện (Conditional Formatting - CF) được Microsoft bổ sung vào Excel kể từ phiên bản Microsoft Excel 97 CF trong Excel là một công cụ mạnh giúp chúng ta đị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…

CF khi áp dụng vào các ô (cell) nó sẽ đè lên các định dạng thông thường của ô về màu sắc, kiểu thể hiện văn bản và số… Tuy nhiên nếu chúng ta xoá bỏ CF của các ô thì định dạng đã có trước kia của các

ô này sẽ được phục hồi

CF trong các phiên bản 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) và điều này đã được khắc phục trong phiên bản Excel 2007 với số lượng điều kiện cho phép là 64

Tài liệu này sẽ cố gắng trình bày về CF thật bao quát, nhằm giúp các bạn có thể áp dụng được CF trong tất cả phiên bản Excel hiện có cũng như một số kỹ thuật CF bằng VBA Tài liệu cũng đề cập thật chi tiết

về các điểm mới của CF trong phiên bản mới nhất Excel 2007

Tài liệu có sử dụng lại một số bài viết và ví dụ thực tế về CF trên diễn đàn GPE (handung107, levanduyet, …) Rất mong nhận được ý kiến đóng góp của tất cả các bạn, góp ý xin gửi về ttphong@giaiphapexcel.com

Đị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:

Trần Thanh Phong | Các vấn đề cơ bản 6

 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

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)

Trần Thanh Phong | Các vấn đề cơ bản 7

 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

Trần Thanh Phong | Các vấn đề cơ bản 8

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:

o Tại ngăn Font chọn Bold tại Font style, chọn tiếp màu xanh tại Color

o Chuyển qua ngăn Patterns và chọn màu vàng tại Cell shading

o 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:

o Tại ngăn Font chọn màu tím tại Color

o Chuyển qua ngăn Patterns và chọn màu xám tại Cell shading

o Nhấn OK hoàn tất CF2

Trần Thanh Phong | Các vấn đề cơ bản 9

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:

Trần Thanh Phong | Các vấn đề cơ bản 10

Lưu ý các đối số trong điều kiện so sánh có thể dùng tham chiếu đến địa chỉ thay cho các hằng số

Ví dụ 2.4 Ví dụ ta có bảng dữ liệu B36:F40 như hình dưới, hãy dùng CF tô màu vàng cho các ô chứa giá trị bằng với giá trị trong ô F42 Sử dụng địa chỉ tham chiếu thay cho hằng số trong các điều kiện so

sánh

B1 Chọn vùng B36:F40 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:

 Chọn tuỳ chọn là Cell Value Is, chọn toán tử so sánh là greater than và nhập vào tham chiếu

=F42 tại ô trống kế bên Nhấn nút Format, chọn ngăn Patterns và chọn màu vàng tại Cell shading, nhấn OK hoàn tất

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

Trần Thanh Phong | Các vấn đề cơ bản 11

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

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

Nếu Trả về Và nếu Trả về Và nếu Trả về Thì thực thi

CF1 True CF2 True hoặc False CF3 True hoặc False CF1, bỏ qua CF2 và CF3

CF1 False CF2 True CF3 True hoặc False CF2, bỏ qua CF3

Đ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:

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

Trần Thanh Phong | Các vấn đề cơ bản 12

 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 Nhằm đảm bảo tính tương thích ngược với các phiên

bản Excel cũ thì bạn nên chọn Stop If True tạ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

Trần Thanh Phong | Các vấn đề cơ bản 13

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

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

Trần Thanh Phong | Các vấn đề cơ bản 14

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

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

Trần Thanh Phong | Các vấn đề cơ bản 15

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)

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

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

Trần Thanh Phong | Các vấn đề cơ bản 16

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

Sheet

CFI.4

Sheet Ref

Trần Thanh Phong | Các vấn đề cơ bản 17

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

B1 Chọn các ô có CF mà ta muốn sao chép

B2 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

 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á

Trần Thanh Phong | Các vấn đề cơ bản 18

 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)

Trần Thanh Phong | Các vấn đề cơ bản 19

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ếu bạ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… )

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 20

Chúng ta có thể dùng CF để kiểm tra các lỗi trong công thức của các ô trên bản tính và thay đổi định dạng của chúng Ví dụ sau bạn sẽ thấy khi các ô ở cột A chứa số 0 thì kết quả ở cột C sẽ có lỗi #DIV/0 B1 Chọn vùng C2:C5

B2 Vào Format | Conditional Formatting B3 Chọn Formula Is trong Condition 1 B4 Nhập vào công thức sau:

=ISERROR(C2) : kiểm tra tất cả các lỗi

=ISNA(C2) : chỉ kiểm tra lỗi #N/A thôi

=ISERR(C2) : kiểm tra các lỗi ngoại trừ lỗi #N/A

B5 Nhấn nút Format, chọn màu chữ trùng với màu của ô (ở đây là màu trắng) B6 Nhấn OK, và nhấn tiếp OK

B1 Chọn vùng A1:C10 B2 Vào Format | Conditional Formatting B3 Chọn Formula Is trong Condition 1 B4 Nhập vào công thức: =ISBLANK(A1) B5 Nhấn nút Format, chọn màu nền xanh trong ngăn Patterns B6 Nhấn OK, và nhấn tiếp OK

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 21

Ghi chú nhóm hàm IS

Dùng để kiểm tra kiểu giá trị trong ô hoặc các tham chiếu

ISBLANK Giá trị kiểm tra là rỗng (empty cell)

ISERR Giá trị kiểm tra là các lỗi ngoại trừ lỗi #N/A

ISERROR Giá trị kiểm tra là các lỗi (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!)

ISLOGICAL Giá trị kiểm tra là giá trị luận lý

ISNA Giá trị kiểm tra là lỗi #N/A (value not available)

ISNONTEXT Giá trị kiểm tra không phải kiểu TEXT (lưu ý ô rỗng (blank cell) xem như không thuộc

kiểu TEXT)

ISNUMBER Giá trị kiểm tra là các con số

ISREF Giá trị kiểm tra là các tham chiếu

ISTEXT Giá trị kiểm tra là các chuỗi (kiểu TEXT)

Ví dụ như hình bên dưới, chúng ta sẽ dùng CF định dạng màu chữ của các giá trị trùng trong cột A (Region) bằng cách tô màu trắng để ẩn chúng đi cho dễ theo dõi

B1 Chọn vùng A2:A5 B2 Vào Format | Conditional Formatting B3 Chọn Formula Is tại Condition 1 B4 Nhập vào công thức =A2=A1 B5 Nhấn chọn Format, chọn màu chữ sao cho trùng với màu của ô (màu trắng) B6 Nhấn OK, và nhấn tiếp OK

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 22



B1 Chọn vùng A2:A11 B2 Vào Format | Conditional Formatting B3 Chọn Formula Is tại Condition 1 B4 Nhập vào công thức: =COUNTIF($A$2:$A$11,A2)>1 B5 Nhấn nút Format, chọn màu chữ là màu xanh và in đậm B6 Nhấn OK, và nhấn tiếp OK

B1 Tạo danh sách C2:C4 (giả sử đặt tên là CodeList) B2 Chọn vùng số liệu A2:A7

B3 Vào Format | Conditional Formatting B4 Chọn Formula Is từ Condition 1 B5 Nhập vào công thức: =COUNTIF($C$2:$C$4,A2)

hoặc nếu dùng Name cho danh sách thì nhập vào: =COUNTIF(CodeList,A2)

B6 Nhấn nút Format, chọn màu nền là xanh nhạt B7 Nhấn OK, và nhấn tiếp click OK

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 23

Trong ví dụ này chúng ta dùng CF để tô màu nền các các ô chứa các con số trong vùng B2:G4 (vùng chứa các con số của vé số) nếu nó xuất hiện trong vùng B6:G6 (vùng kết quả xổ số)

B1 Chọn vùng B2:G4 B2 Vào Format | Conditional Formatting B3 Chọn Formula Is tại Condition 1 B4 Nhập vào công: =COUNTIF($B$6:$G$6,B2) B5 Nhấn nút Format , chọn màu nền là xanh nhạt B6 Nhán OK, và nhấn tiếp OK

Tô màu các khoản phải trả sắp đến hạn trong 30 ngày nữa Trong ví dụ này các ngày hết hạn chứa trong vùng A2:A4

B1 Chọn vùng A2:A4

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 24

B2 Vào Format | Conditional Formatting B3 Chọn Formula Is tại Condition 1 B4 Nhập vào công thức: =AND(A2-TODAY()>=0,A2-TODAY()<=30) B5 Nhấn nút Format, chọn màu xanh in cho chữ

B6 Nhấn OK và nhấn tiếp OK

Bạn có thể dùng CF để ẩn nội dung các ô khi in ấn Trong ví dụ này chúng ta sẽ tô màu trắng cho chữ trong các ô B2:F4 khi ô H1 chứa ký tự x Muốn in các ô bị ẩn thì xoá ký tự x torng H1

B1 Chọn vùng B2:F4 B2 Vào Format | Conditional Formatting B3 Chọn Formula Is tại Condition 1 B4 Nhập vào công thức: =$H$1="x"

B5 Nhấn nút Format , chọn định dạng các chữ màu trắng B6 Nhấn OK, và nhấn tiếp OK

B1 Nhấp chuột vào nút Select All để chọn tất cả bảng tính B2 Vào Format | Conditional Formatting

B3 Chọn Formula Is tại Condition 1

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 25

 Muốn tô màu nền cách N dòng thì dùng công thức: =MOD(ROW(),N)=0

 Muốn tô màu nền cách N cột thì dùng công thức: =MOD(COLUMN(),N)=0

Bạn có thể dùng CF để tô màu nền cho một nhóm dòng trên bảng tính Ví dụ này sẽ minh hoạ cách tô màu nền cách quãng 3 dòng

B1 Nhấn chuột lên nút Select All để chọn toàn bộ sheet B2 Vào Format | Conditional Formatting

B3 Chọn Formula Is tại Condition 1 B4 Nhập vào công: =MOD(ROW(),6)<3 B5 Nhấn nút Format, vào ngăn Patterns chọn màu nền là màu xám B6 Nhấn OK, và nhấn tiếp OK

B1 Chọn vùng A2:B29

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 26

B2 Vào Format | Conditional Formatting B3 Tại Condition 1 chọn Formula Is B4 Nhận vào công thức: =MOD(SUBTOTAL(3,$A$1:$A2),2) B5 Nhấn chuột vào nút Format , vào ngăn Patterns, chọn màu nền xám B6 Nhấn OK, và nhấn tiếp OK

B7 Khi áp dụnh Auto Filter danh sách thì các hàng vẫn được tô nền cách dòng

 Muốn tô màu nền cách N dòng thì dùng công thức: =MOD(SUBTOTAL(3,$A$1:$A2),N)

Bạn có thể dùng CF kết hợp font chữ để tạo nên các hình nền màu trong các ô Trong ví dụ này chúng ta

sẽ tô hình nền màu trong vùng C3:C7 tuỳ thuộc vào giá trị trong các ô ở cột B bên cạnh Nếu giá trị nhỏ hơn 10 thì ô bên cột C cạnh bên sẽ là hình tròn màu đỏ, nếu giá trị lớn hơn 30 thì hiện hình vuông màu xanh, còn lại thì hiện hình thoi màu vàng

B1 Trong ô C3 nhập vào công thức:

=IF(B3="","",IF(B3<10,"l",IF(B3>30,"n","t")))

B2 Chép công thức xuống cho các ô C4:C7 B3 Định dạng vùng C3:C7 với font chữ Wingding với màu vàng B4 Chọn vùng C3:C7

B5 Vào Format | Conditional Formatting B6 Tại Condition 1 chọn Formula Is

B7 Nhập vào công thức:

=$B3<10

B8 Nhấn nút Format và chọn màu font chữ là màu đỏ, sau đó nhấn nút OK

B9 Nhấn nút Add, B10 Tại Condition 2 chọn tiếp Formula Is

B11 Nhập vào công thức:

=$B3>30

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 27

B1 Chọn vùng A1:C10 B2 Nhập chữ i vào ô E1 B3 Vào CF, chọn Formula Is và nhập vào công thức =$E$1=”i"

B4 Nhấn nút Format, chọn Italic tại Font Style B5 Nhấn OK và nhấn tiếp OK lần nữa

B1 Chọn vùng A2:B21 B2 Vào CF và nhập vào công thức: =$A2>DATE(YEAR($B$24)+1,MONTH($B$24),DAY($B$24)) B3 Định dạng nền màu xanh

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 28

B1 Chọn vùng D2:E21

B2 Vào CF và nhập vào công thức: =AND($D2>=DATE(2001,1,1),$D2<=DATE(2001,4,30))

B3 Định dạng nền màu xanh

B1 Chọn vùng G2:H21 B2 Vào CF và nhập vào công thức:

=AND($G2>(TODAY()-WEEKDAY(TODAY())),$G2<=(TODAY()-WEEKDAY(TODAY())+7))

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 29

B1 Chọn vùng N2:O21 B2 Vào CF và nhập vào công thức: =OR(WEEKDAY($N2)=1,WEEKDAY($N2)=7) B3 Chọn màu nền xanh

Chúng ta có thể đặt tên cho vùng dữ liệu chứa các ngày lễ

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 30

B1 Chọn vùng Q2:Q21 B2 Vào CF và nhập vào công thức: =ISNUMBER(MATCH($Q2,$S$2:$S$4,0)) B3 Chọn màu nền là màu xanh

B1 Nhập ngày đầu của tháng vào ô U3 B2 Tại ô V3 nhập vào =U3, tại ô W3 nhập vào =V3+1 , sao chép W3 cho các ô X3:AZ3 B3 Định dạng vùng V3:AZ3 là dd (chỉ hiển thị ngày)

B4 Chọn vùng V3:AZ21 B5 Vào CF chọn Formula Is và nhập vào công thức: =WEEKDAY(V$3)=1

B6 Chọn nền màu xanh

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 31

B2 Vào CF và nhập vào công thức: =DATEDIF($BC2,TODAY(), “Y”)<=18

B3 Chọn màu nền xanh

B1 Chọn vùng BE2:BF12

B2 Vào CF và nhập vào công thức: =MONTH($BF2)=MONTH(TODAY())

B3 Chọn màu nền xanh

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 32

B1 Chọn vùng A1:B10 B2 Vào CF chọn Formula Is và nhập công thức:

 Không phân biệt chữ HOA và thường thì dùng:

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 33

 Có phân biệt chữ HOA và thường thì dùng

=FIND("Mai",F1) hoặc dùng

=LEN(F1)<>LEN(SUBSTITUTE(F1,“Mai”,””))

B3 Chọn nền màu xanh

B1 Chọn vùng H1:H10 (J1:J10) B2 Vào CF chọn Formula Is và nhập công thức:

 Không phân biệt chữ HOA và thường thì dùng:

=COUNTIF(H1,”H*”)  tô nền các ô có ký tự bắt đầu là ký tự H

 Có phân biệt chữ HOA và thường thì dùng

=FIND("H",J1)=1  tô nền các ô có ký tự bắt đầu là ký tự H B3 Chọn nền màu xanh

Trần Thanh Phong | Các ví dụ về CF và các kỹ thuật nâng cao 34

B1 Chọn các dòng 18 đến 27 (dòng 35 đến 44) B2 Vào CF chọn Formula Is và nhập công thức:

 Ô chứa chuỗi đơn

=CountIf(18:18,”Mai”)  Tô màu cả dòng khi có ô chứa chuỗi đơn

 Ô chứa chuỗi con

=CountIf(35:35,”*Lan*”)  Tô màu cả dòng khi có ô chứa chuỗi con B3 Chọn nền màu xanh