Định dạng có điều kiện là một tính năng mạnh mẽ của Excel tự động áp dụng định dạng như màu sắc, biểu tượng hoặc font in đậm dựa trên giá trị của bảng tính. Mặc dù Excel cung cấp các quy tắc tích hợp dễ sử dụng cho định dạng có điều kiện, các quy tắc dựa trên công thức cung cấp sự linh hoạt lớn hơn. Trong hướng dẫn này, chúng ta sẽ chỉ định dạng có điều kiện nâng cao sử dụng các quy tắc dựa trên công thức, cho phép bạn linh hoạt hơn nhiều so với các tùy chọn tích hợp tiêu chuẩn.
Tạo Quy Tắc Định Dạng Có Điều Kiện Dựa Trên Công Thức
Định dạng có điều kiện hoạt động dựa trên các điều kiện nhất định, và nó cũng cung cấp các quy tắc tùy chỉnh dựa trên công thức. Định dạng có điều kiện dựa trên công thức định nghĩa các quy tắc bằng các công thức tùy chỉnh đánh giá dữ liệu trong worksheet của bạn. Định dạng được áp dụng khi công thức trả về TRUE. Để áp dụng quy tắc dựa trên công thức, bạn sẽ cần làm theo các bước sau:
- Chọn phạm vi ô để áp dụng định dạng có điều kiện.
- Vào tab Trang Chủ >> từ Định Dạng Có Điều Kiện >> chọn Quy Tắc Mới.
- Trong hộp thoại Quy Tắc Định Dạng Mới;
- Chọn tùy chọn Sử dụng công thức để xác định ô nào cần định dạng.
- Trong hộp Định dạng giá trị nơi công thức này là đúng;
- Chèn công thức tùy chỉnh.
- Nhấp vào Định Dạng để chọn định dạng theo ý thích của bạn.
- Nhấp OK.
Hãy xem xét một tập dữ liệu bán hàng để áp dụng định dạng có điều kiện nâng cao với các quy tắc dựa trên công thức.
1. Làm Nổi Bật Hàng Dựa Trên Điều Kiện
Nếu bạn muốn làm nổi bật các hàng nơi doanh số lớn hơn một giá trị nhất định, bạn có thể sử dụng công thức so sánh sau.
Công Thức:
=$G2>=5000
Công thức này kiểm tra xem giá trị trong ô C2 (Doanh Số) có lớn hơn 5000 không. Sử dụng tham chiếu tuyệt đối ($) cho cột nếu so sánh nhất quán trên các hàng.
- Nhấp Định Dạng và đặt tùy chọn định dạng ưa thích của bạn, chúng tôi chọn màu xanh lá.
- Nhấp OK để áp dụng quy tắc.
2. Làm Nổi Bật Các Mục Nhập Bán Hàng Trùng Lặp
Bạn có thể làm nổi bật các mục nhập bán hàng trùng lặp từ tập dữ liệu của bạn để xóa các mục đó. Chèn công thức sau.
Công Thức:
=COUNTIFS($A$2:$A$17, $A2, $B$2:$B$17, $B2, $C$2:$C$17, $C2, $D$2:$D$17, $D2, $E$2:$E$17, $E2, $F$2:$F$17, $F2, $G$2:$G$17, $G2) > 1
Công thức này kiểm tra mỗi cột từ A đến G để xác định xem tổ hợp giá trị trong mỗi hàng có bị trùng lặp trong phạm vi được chỉ định hay không, sau đó nó đếm tổ hợp giá trị duy nhất của mỗi hàng trong cột A đến G. Nếu số đếm lớn hơn 1, hàng đó được coi là trùng lặp. Sau đó hàng đó được làm nổi bật là trùng lặp.
- Nhấp Định Dạng, và chọn định dạng của bạn, chúng tôi chọn nền vàng.
- Nhấp OK để áp dụng quy tắc.
3. Tô Màu Mỗi Hàng Xen Kẽ (Sọc Cebra)
Để định dạng bảng dữ liệu và làm cho nó dễ đọc, bạn có thể thêm màu cho mỗi hàng xen kẽ. Để thêm định dạng này, sử dụng công thức sau.
Công Thức:
=MOD(ROW(),2)=0
Hàm ROW() trả về số hàng hiện tại. Sau đó MOD(ROW(),2)=0 kiểm tra xem số hàng có phải là số chẵn không.
- Nhấp Định Dạng, và chọn định dạng của bạn, chúng tôi chọn nền xám nhạt.
- Nhấp OK để áp dụng quy tắc.
4. Làm Nổi Bật Ngày Quá Khứ
Nếu bạn muốn làm nổi bật các ngày đã qua ngày hôm nay để xác định các mục quá hạn, bạn có thể làm điều đó bằng công thức của hàm TODAY.
Công Thức:
=AND(ISNUMBER(A2), A2 < TODAY())
Công thức này kiểm tra xem ngày trong A2 có trước ngày hôm nay không để làm nổi bật bất kỳ ngày quá khứ nào trong tập dữ liệu của bạn.
- Nhấp Định Dạng, và chọn định dạng của bạn, chúng tôi chọn nền đỏ.
- Nhấp OK để áp dụng quy tắc.
5. Làm Nổi Bật 10% Giá Trị Doanh Số Cao Nhất
Bạn có thể làm nổi bật 10% hàng đầu doanh số của tập dữ liệu bán hàng của mình. Để làm nổi bật doanh số hàng đầu, bạn có thể sử dụng hàm PERCENTILE.
Công Thức:
=$G2>=PERCENTILE($G$2:$G$17,0.9)
PERCENTILE($G$2:$G$17,0.9) tính phần trăm thứ 90 của phạm vi, vì vậy các giá trị lớn hơn hoặc bằng phần trăm này nằm trong 10% hàng đầu. Nếu một giá trị trong cột G lớn hơn hoặc bằng phần trăm này, định dạng sẽ được áp dụng cho toàn bộ hàng.
- Nhấp Định Dạng, và chọn định dạng của bạn, chúng tôi chọn chữ in đậm với màu nền xanh lá.
- Nhấp OK để áp dụng quy tắc.
6. Kết Hợp Nhiều Điều Kiện
Bạn có thể tạo các quy tắc định dạng có điều kiện phức tạp bằng cách kết hợp nhiều điều kiện với các hàm logic. Sử dụng hàm AND/OR để Làm nổi bật ô dựa trên nhiều điều kiện.
Công Thức:
=AND($G2>=3000, $C2="East")
Công thức này làm nổi bật các hàng chỉ khi doanh số lớn hơn hoặc bằng 3000 và khu vực là đông.
- Nhấp Định Dạng, và chọn định dạng của bạn, chúng tôi chọn font in nghiêng và màu nền cam đào.
- Nhấp OK để áp dụng quy tắc.
Quản Lý Quy Tắc Định Dạng Có Điều Kiện
Để quản lý định dạng có điều kiện của bạn:
- Vào tab Định Dạng Có Điều Kiện >> chọn Quản Lý Quy Tắc.
- Ở đây bạn có thể xem, thêm, chỉnh sửa, xóa, nhân bản hoặc thay đổi độ ưu tiên của các quy tắc của bạn.
- Bạn cũng có thể sử dụng trường "Áp Dụng Cho" để sửa đổi phạm vi ô để áp dụng các quy tắc.
Kết Luận
Bạn có thể sử dụng định dạng có điều kiện nâng cao với các quy tắc dựa trên công thức trong Excel để làm nổi bật dữ liệu của mình. Nó cung cấp một cách linh hoạt để trực quan hóa dữ liệu. Dù bạn đang làm nổi bật ô dựa trên các điều kiện cụ thể, quản lý trùng lặp, kết hợp nhiều tiêu chí hoặc kiểm tra ngày đến hạn, các quy tắc dựa trên công thức này sẽ giúp bạn. Làm chủ tính năng này sẽ làm cho bảng tính của bạn hiểu rõ hơn và hấp dẫn hơn về mặt trực quan. Thử nghiệm với các công thức khác nhau để xem chúng hoạt động như thế nào và nâng cao bảng tính của bạn.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!