Định dạng có điều kiện dựa trên công thức chính trong Excel

Đị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 các định dạng như màu sắc, biểu tượng hoặc phông chữ đậm dựa trên các giá trị của bảng tính. Mặc dù Excel cung cấp các quy tắc dựng sẵn dễ sử dụng để định dạng có điều kiện, nhưng các quy tắc dựa trên công thức mang lại sự linh hoạt cao hơn. Trong hướng dẫn này, chúng tôi sẽ trình bày định dạng có điều kiện nâng cao bằng cách sử dụng các quy tắc dựa trên công thức, giú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 xác định các quy tắc bằng cách sử dụng các công thức tùy chỉnh để đánh giá dữ liệu trong trang tính 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 cần thực hiện theo các bước nhất định:

• Chọn phạm vi ô để áp dụng định dạng có điều kiện.
• Truy cập Trang chủ tab>> từ Định dạng có điều kiện>> chọn Quy tắc mới .
• Trong Quy tắc định dạng mới hộp thoạ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 Các giá trị định dạng trong đó công thức này đúng hộp;
    • Chèn công thức tùy chỉnh.
  • Nhấp vào Định dạng để chọn định dạng bạn muốn.
  • Nhấp vào 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. Đánh dấu các hàng dựa trên điều kiện

Nếu muốn đánh dấu các hàng có 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 hay không. Sử dụng tham chiếu tuyệt đối ($) cho các cột nếu phép so sánh nhất quán giữa các hàng.

• Nhấp vào Định dạng và đặt các tùy chọn định dạng ưa thích của bạn, chúng tôi đã chọn màu xanh lục.
• Nhấp vào OK để áp dụng quy tắc.

2. Đánh dấu các mục bán hàng trùng lặp

Bạn có thể đánh dấu các mục nhập bán hàng trùng lặp khỏi tập dữ liệu của mình để xóa các mục nhập đó. 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 từng 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 đã chỉ định hay không, sau đó tính tổ hợp giá trị duy nhất của mỗi hàng trong cột A đến G. Nếu tổng số lớn hơn 1 thì hàng đó được coi là trùng lặp. Sau đó, hàng được đánh dấu là trùng lặp.

• Nhấp vào Định dạng và chọn định dạng của bạn, chúng tôi đã chọn màu tô màu vàng.
• Nhấp vào OK để áp dụng quy tắc.

3. Tô màu mọi hàng khác (Sọc ngựa vằn)

Để định dạng bảng dữ liệu và dễ đọc bạn có thể thêm màu cho mỗi hàng khác. Để thêm định dạng này, hã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 hay không.

• Nhấp vào Định dạng và chọn định dạng của bạn, chúng tôi đã chọn màu nền màu xám nhạt.
• Nhấp vào OK để áp dụng quy tắc.

4. Đánh dấu ngày đã qua

Nếu bạn muốn đánh dấu những ngày đã qua ngày hôm nay để xác định các mục quá hạn, bạn có thể thực hiện bằng cách sử dụng công thức HÔM NAY chức năng.

Công thức:

=AND(ISNUMBER(A2), A2