Tính tổng giá trị trong các ô cf2 em sử dụng công thức nào
Định dạng theo điều kiện (Conditional Formatting - CF) trong Excel
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ề Nội dung I. CF các vấn đề cơ bản II. Các ví dụ về CF và các kỹ thuật nâng cao III. Sử dụng CF của Excel 2007 & 2010 Thanh Phong Lần chỉnh sửa cuối: 11/8/10
I. Các vấn đề cơ bản của CF
I. Các vấn đề cơ bản
1. Giới thiệu 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ả đ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:
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. 2. CF dùng danh mục điều kiện sẵn có 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:
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:
B4. Nhấn nút Format… hộp thoại Format Cells xuất hiện như hình sau
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:
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:
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:
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. Thanh Phong
I. Các vấn đề cơ bản (tt)
3. Thứ tự ưu tiên của các CF 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.
Đ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:
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.
Ví dụ 3.1: Vùng địa chỉ B3:B8 được áp dụng CF với 3 điều kiện sau:
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. (Excel 2003 trở về trước)
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. Happy day! Thanh Phong
4. CF sử dụng các công thức làm điều kiện
4. CF sử dụng các công thức làm điều kiện
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:
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
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 (
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á.
Để 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
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:
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:
II. Các ví dụ về CF và các kỹ thuật nâng cao
II. Các ví dụ về CF và các kỹ thuật nâng cao A. Các ví dụ cơ bản
1. Các vấn đề thường gặp
a. Ẩn các lỗi (Hide Errors)
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
b. Tô màu nền các ô rỗng (ISBLANK) 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
Ghi chú nhóm hàm IS Dùng để kiểm tra kiểu giá trị trong ô hoặc các tham chiếu
c. Ẩn bớt các giá trị trùng (Hide Duplicate Values) 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
d. Tô màu các giá trị trùng lặp trong cột (Highlight Duplicates in Column) 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
TP.
1. CF và các vấn đề thường gặp (tt)
A. Các ví dụ cơ bản
1. Các vấn đề thường gặp (tt)
e. Tô màu các phần tử thuộc danh sách (Highlight Items in a List)
B5. Nhập vào công thức: =COUNTIF($C$2:$C$4,A2) f. Tô màu các con số trùng với các con số cho trước 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 1B4. 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
g. Tô màu các ngày sắp đến hạn (Highlight Upcoming Expiry Dates) 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 B2. Vào Format | Conditional Formatting B3. Chọn Formula Is tại Condition 1B4. 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.
h. Ẩn nội dung các ô khi in (Hide Cell Contents When Printing) 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 1B4. 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
i. Tô màu nền cách dòng (Shade Alternating Rows) 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 1B4. Nhập vào công thức: =MOD(ROW(),2) B5. Nhấn nút Format, vào ngăn Patterns chọn màu nền xanh nhạt B6. Nhấn OK, và nhấn tiếp OK
j. Tô màu nền nhóm N dòng cách quãng (Shade Bands of Rows) 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 4 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(INT((ROW()-1)/4)+1,2) 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
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.
l. Tạo các hình nền màu (Create Coloured Shapes) 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 IsB11. Nhập vào công thức: =$B3>30 B12. Nhấn nút Format, và chọn màu xanh cho font chữ, sau đó nhấn nút OK. B13. Nhấn nút OK
TP.
2. Một số ví dụ về CF với dữ liệu Date – Time
2. Một số ví dụ về CF với dữ liệu Date – Time
a. Tô màu các ngày lớn hơn ngày 18/09/2000 (trong ô B24) một năm =$A2>DATE(YEAR($B$24)+1,MONTH($B$24),DAY($B$24)) b. Tô màu nền các ngày từ 1/1/2001 đến ngày 30/4/2001 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
c. 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) 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)) Hoặc dùng công thức sau: =($G2>(TODAY()-WEEKDAY(TODAY())) * ($G2<=(TODAY()-WEEKDAY(TODAY())+7)) B3. Chọn màu nền xanh
d. Tô màu nền các ngày cuối tuần (tuần bắt đầu là ngày chủ nhật) 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
e. Tô màu nền cho các ngày lễ Chúng ta có thể đặt tên cho vùng dữ liệu chứa các ngày lễ 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
f. Tô màu các cột là ngày chủ nhật hàng tuần B1. Nhập ngày đầu của tháng vào ô U3B2. 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:AZ21B5. 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
g. Sử dụng hàm DateDif và CF để tô nền các thành viên nhỏ hơn 18 tuổi B1. Chọn vùng BB2:BC12B2. 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
h. Kết hợp TODAY và MONTH tô nền những người có sinh nhật trong tháng 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
(Tài liệu đang trong quá trình biên soạn và tổng hợp, nếu có ví dụ mới sẽ bổ sung sau).
3. Một số ví dụ về CF với dữ liệu dạng Text
3. Một số ví dụ về CF với dữ liệu dạng Text
a. Tô màu nền các ô chứa chuỗi đơn cần tìm b. Tô màu nền các ô có chứa chuỗi con cần tìm B1. Chọn vùng D110 (F1:F10) B2. Vào CF chọn Formula Is và nhập công thức:
=FIND("Mai",F1) hoặc dùng =LEN(F1)<>LEN(SUBSTITUTE(F1,“Mai”,””)) B3. Chọn nền màu xanh
c. 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 B1. Chọn vùng H1:H10 (J1:J10) B2. Vào CF chọn Formula Is và nhập công thức:
=COUNTIF(H1,”H*”) --> tô nền các ô có ký tự bắt đầu là ký tự H
=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
d. Tô màu cả dòng khi có một ô thuộc dòng có chứa chuỗi cần tìm 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:=CountIf(18:18,”Mai”) --> Tô màu cả dòng khi có ô chứa chuỗi đơn =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
e. Tô màu các dòng có lượng hàng tồn kho B1. Chọn vùng L2:O10 B2. Vào CF chọn Formula Is và nhập vào công thức=$O2=”Có” B3. Chọn nền màu xanh
(Tài liệu đang trong quá trình biên soạn và tổng hợp, nếu có ví dụ mới sẽ bổ sung thêm).
4. Các ví dụ CF sử dụng hàm thống kê
4. Các ví dụ CF sử dụng hàm thống kê
a. Tìm và tô màu nền của ô chứa giá trị lớn nhất trong vùng số liệu =A1=MAX($A$1:$C$10) b. 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 B1. Chọn vùng E1:G10 B2. Vào CF, chọn Formula Is và nhập vào công thức=E1>=LARGE($E$1:$G$10) B3. Chọn Format và chọn màu nền xanh trong ngăn Patterns
c. Tìm và tô màu nền của ô chứa giá trị nhỏ nhất trong vùng số liệu B1. Chọn vùng I1:K10 B2. Vào CF, chọn Formula Is và nhập vào công thức=I1>=MIN($I$1:$K$10) B3. Chọn Format và chọn màu nền xanh trong ngăn Patterns
d. 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 B1. Chọn vùng M1:O10 B2. Vào CF, chọn Formula Is và nhập vào công thức=M1=MIN(IF($M$1:$O$10<>0,$M$1:$O$10)) B3. Chọn Format và chọn màu nền xanh trong ngăn Patterns
e. 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 B1. Chọn vùng Q1:S10 B2. Vào CF, chọn Formula Is và nhập vào công thức=Q1<=SMALL($Q$1:$S$10,3) B3. Chọn Format và chọn màu nền xanh trong ngăn Patterns
f. Tìm và tô nền của ô chứa giá trị lớn hơn giá trị trung bình cộng B1. Chọn vùng U1:W10 B2. Vào CF, chọn Formula Is và nhập vào công thức=U1>AVERAGE($U$1:$W$10) B3. Chọn Format và chọn màu nền xanh trong ngăn Patterns
TP.
B. CF nâng cao
B. CF nâng cao
a. Tô màu các ô chứa công thức
B1. Tạo name tên là CellHasFormula có Refers to là =GET.CELL(48,A1) (Lưu ý chọn ô A1 trước khi đặt Name).
B3. Vào CF, chọn Formula Is và nhập vào công thức =CellHasFormula Sẽ trình bày cách dùng UDF sau. b. Tô màu các ô đặt ở chế độ unlock B1. Chọn vùng C1:C10 B2. Vào CF, chọn Formula Is và nhập vào công thức=CELL(“protect”,C1)=0 B6. Chọn nút Format vào ngăn Patterns chọn màu nền xanh B7. Nhấn nút OK hai lần để hoàn tất, khi đó các ô có thuộc tích unlock sẽ được tô màu nền xanh
c. Tìm các số có sai biệt lớn hơn khoảng ±5% Ví dụ chúng ta có kết quả kinh doanh của 2 năm 2006 và 2007 với doanh số các tháng như bảng sau: Hãy dùng CF tô màu nền xanh cho các ô bên cột doanh số năm 2007 có sự biến động lớn hơn khoảng ±5% so với doanh số của năm 2006. B1. Chọn vùng G2:G13 B2. Vào CF, tại Formula Is nhập vào công thức sau:=OR((G2/F2)-1>5%,(G2/F2)-1<-5%) B3. Nhấn nút Format và Patterns và chọn nền màu xanh. B4. Nhấn OK hai lần để hoàn tất
d. Tìm nhóm 5 có giá trị cao nhất/ thấp nhất B1. Chọn vùng J1:J20 B2. Vào CF, chọn Formula Is và nhập vào công thức sau
e. Tìm nhóm 10% có giá trị cao nhất/ thấp nhất Cũng tương tự như câu d tuy nhiên số lượng phần tử tính theo dạng phần trăm (%). B1. Chọn vùng N1:N20 B2. Vào CF, chọn Formula Is và nhập vào công thức sau
TP.
B. CF nâng cao (tt) B. CF nâng cao (tt)
f. Tạo sơ đồ GANTT
B1. Tạo vùng dữ liệu thô như hình minh hoạ R1:V13 =AND(W$1>=$T2,W$1<=$U2) g. CF khi có nhiều hơn 3 điều kiện Bảng màu của Excel Excel chỉ nhận biết màu theo tên của các màu từ Color 1 đến 8 (Black, White, Red, Green, Blue, Yellow, Magenta, và Cyan). Trong số 56 ô màu thì chỉ liệt kê có 40 màu trên bảng màu của Excel (chia làm 2 nhóm: nhóm trên 40 màu, nhóm dưới 16 màu thông dụng). Các cặp mã màu sau trên bảng màu là giống nhau: 11 & 25, 5 & 32, 14 & 31, 8 & 28, 9 & 30, 13 & 29, 18 & 54, 20 &34, 7 & 26, và 6 & 27.
Dưới đây là bảng quy định mã cho định dạng tùy biến: ĐỊNH DẠNG KIỂU CHO GIÁ TRỊ LÀ SỐ
Ví dụ: Thay đổi màu của giá trị trong ô dựa vào 6 điều kiện như sau:
[Red][<=0]0;[Green][<=20]0;[Blue]0 B2. Sau khi nhấn OK chấp nhận định dạng tuỳ biến thì vào Format | chọn Conditional Formatting.. • Chọn Cell Value Is tại Condition 1 | chọn điều kiện là between và nhập vào hai giá trị 31 và 40 vào hai ô trống kế bên. Nhấn nút Format | chọn ngăn Font | và chọn màu vàng tại Color. Nhấn OK hoàn tất CF1. • Nhấn nút Add >> để thêm CF2. Chọn Cell Value Is tại Condition 2 chọn điều kiện là between và nhập vào hai giá trị 41 và 50 vào hai ô trống kế bên. Nhấn nút Format | chọn ngăn Font | và chọn màu hồng tại Color. Nhấn OK hoàn tất CF2. • Nhấn nút Add >> để thêm CF3. Chọn Cell Value Is tại Condition 3 chọn điều kiện là greater than or equal to và nhập vào giá trị 51 vào ô trống kế bên. Nhấn nút Format | chọn ngăn Font | và chọn màu tím tại Color. Nhấn OK hoàn tất CF3. B3. Nhấn OK hoàn tất
Thanh Phong
III. CF trong Excel 2007
III. Định dạng theo điều kiện trong Excel 2007
Phần này trình bày cách định dạng theo điều kiện trong Excel 2007. Định dạng theo điều kiện đã được cải tiến rất nhiều trong phiên bản Excel 2007 và công cụ trở nên rất hữu ích trong việc mô hình hoá dữ liệu số. Trong một số trường hợp bạn có thể dùng CF thay cho đồ thị.
Danh mục định dạng theo điều kiện thiết lập sẵn:
Việc định dạng theo điều kiện có thể được thực hiện nhanh chóng thông qua các tập định dạng thông dụng được thiết lập trước hay người dùng có thể tạo qui luật định dạng mới tại New rules và quản lý các qui luật định dạng bằng công cụ Manage Rules…
Hộp thoại New Formatting Rule
Hộp thoại Conditional Formatting Rules Manager Lựa chọn qui luật CF Để áp dụng một qui luật CF cho cell (ô) hoặc range (vùng), trước tiên hãy chọn các ô sau đó vào Home è Styles è Conditional Formatting è chọn lệnh CF. Có một số lựa chọn như:
Định dạng theo điều kiện trong Excel 2007
Các qui luật có thể áp dụng khi sử dụng CF tự tạo Định dạng theo điều kiện sử dụng hình ảnh
Phần này trình bày một số kiểu định dạng theo điều kiện với các định dạng bằng hình ảnh trực quan và sinh động như data bars, color scales, và icons sets.
Định dạng theo điều kiện này hiển thị đồ thị hình thanh trực tiếp trong ô bảng tính. Chiều dài của thanh đồ thị dựa trên giá trị số đang chứa trong ô với sự so sánh tương đối với các giá trị khác trong vùng định dạng.
Ví dụ: Sử dụng Data Bars để vẽ đồ thị hình thanh trực tiếp trong các ô để so sánh GPD dự tính của các nước Châu Á năm 2009.
Cách làm: 2.Vào Home | Styles | Conditional Formatting | Data Bars | chọn Orange Data Bars
Định dạng theo điều kiện sử dụng Data Bars Data Bars chỉ cung cấp 6 màu cơ bản, để có nhiều lựa chọn hơn ta nhấn vào More Rules… Hộp thoại New Formatting Rule cung cấp cho bạn thêm một số tùy chọn như: Các tùy chọn bổ sung cho Data Bars Lưu ý: Excel luôn luôn hiển thị Data Bar cho tất cả các ô trong vùng định dạng, kể cả các ô chứa giá trị 0 (zero). Ô chứa giá trị nhỏ nhất trong vùng định dạng luôn luôn có chiều dài thanh đồ thị bằng 10% chiều rộng của ô. Excel không cung cấp bất kỳ giao diện người dùng nào cho phép hiệu chỉnh giá trị này, do vậy chúng ta phải hiệu chỉnh nó bằng VBA để cho các thanh đồ thị thể hiện được chính xác hơn. Nhấp phải chuột lên tên sheet đang áp dụng định dạng Data Bars rồi chọn View Code và nhập đoạn mã sau vào cửa sổ Code của Sheet:
Điều chỉnh chiều dài các thanh đồ thị
Public Sub AdjustDataBars() Range("B5:B59").FormatConditions(1).PercentMin = 0 Range("B5:B59").FormatConditions(1).PercentMax = 100 End Sub Sau khi đoạn mã trên được thực thi, các thanh đồ thị sẽ thể hiện giá trị chính xác hơn. Data Bars trong phiên bản Excel 2010 sắp xuất bản có rất nhiều cải tiến và cung cấp nhiều tùy chọn hơn so với Data Bars trong phiên bản Excel 2007. Phiên bản mới cho phép chúng ta kẽ khung cho các thanh đồ thị, thể hiện các thanh đồ thị giá trị âm và cho phép chúng ta điều chỉnh chiều dài thanh đồ thị bằng giao diện người dùng rất dễ dàng:
Data Bars trong Excel 2010 Thanh Phong
Sử dụng Color Scales
Sử dụng Color Scales
Ví dụ:
- Format Style: chọn kiểu 3 màu là 3-Color Scale - Minimun: Lowest Value – giá trị thấp nhất trong vùng số liệu đang chọn - Maximun: Highest Value – giá trị cao nhất trong vùng số liệu đang chọn - Midpoint: chọn Percent là 5% Câu hỏi: Tại sao có những ô giá trị dưới 5% mà vẫn tô nền màu vàng ngã sang xanh chứ không phải là màu vàng ngã sang màu đỏ?Thanh Phong
Sử dụng Icon Sets Một kiểu định dạng theo điều kiện khác đó là dùng các bộ icon để minh họa cho các giá trị đang chứa trong ô. Excel 2007 dựng sẵn 17 bộ icon với số lượng icon trong mỗi bộ là từ 3 đến 5.
Xét bảng số liệu về giá cổ phiếu REE trong một tháng qua như bảng sau. Chúng ta sẽ sử dụng Icon Set để định dạng sự thay đổi của giá tại cột E.
Chúng ta sẽ định dạng sao cho khi % thay đổi tăng thì thêm vào phía trước của ô hình mũi tên hướng lên màu xanh. Khi % thay đổi không đổi (0) thì thêm vào phía trước của ô hình mũi tên nằm ngang màu vàng và nếu thay đổi giảm thì thêm vào hình mũi tên hướng xuống màu đỏ. B1. Chọn vùng địa chỉ E3:E22 B2. Vào Home | nhóm Styles | chọn Conditional Formatting | chọn Icon Sets | chọn kiểu 3 Arrows (Colored).
B5. Nhấn OK hoàn tất
Chỉ cho hiển thị một icon trong bộ Icon?
Trong một số trường hợp, chúng ta chỉ muốn sử dụng một icon trong bộ icon. Excel không trực tiếp cung cấp tùy chọn này, tuy nhiên chúng ta có thể làm được điều này bằng cách sử dung 2 phép định dạng chồng lên nhau. Ví dụ như, chúng ta sẽ định dạng cho cột H trong bảng số liệu trên bằng cách thêm vào hình lá cờ với điều kiện “Khối lượng” lớn hơn hoặc bằng một triệu đơn vị. Các bước thực hiện như sau:
B1. Chọn vùng số liệu H3:H22 của cột “Khối lượng” B2. Vào Home | nhóm Styles | chọn Conditional Formatting | chọn Icon Sets | chọn kiểu 3 Flags. B3. Vào Home | nhóm Styles | chọn Conditional Formatting | chọn Manage Rules… để mở hộp thoại Conditional Formatting Rules Manager | chọn Rule Icon Set và nhấn nút Edit Rule… để tiến hành hiệu chỉnh. Tại hộp Value của dòng có icon lá cờ màu xanh, bạn chọn >= 1000000 và chọn Type lại là Number. Các dòng khác không cần điều chỉnh. Nhấn nút OK để đóng hộp thoại Edit Formatting sau khi hiệu chỉnh xong.
B5. Tại hộp Conditional Formatting Rules Manager, bạn chọn Stop If True tại Rule Cell Value sau đó nhấn nút OK.
Lần chỉnh sửa cuối: 26/5/10
Chuyên đề: Định dạng theo điều kiện trong Excel 97-2010
Chuyên đề: Định dạng theo điều kiện trong Excel 97-2010Các bạn thân mến, Sau một thời gian dài từ khi chủ đề này được hình thành, hôm nay tôi xin tổng hợp lại và phát hành chuyên đề mang tên "Định dạng theo điều kiện trong Excel 1997-2010". 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 2010 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 2010. 2.1.1.11. Tô màu nền cách dòng trong danh sách đang áp dụng Filter (Shade Alternating Filtered Rows)....................................................................... 25 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
Thanh Phong Lần chỉnh sửa cuối: 11/8/10 |