Conditional Formating: Cách tô màu dữ liệu theo nhiều điều kiện khác nhau trong bảng tính excel (1 người xem)

Các ac trong công thức trợ giúp diễn đàn cho tệp đính kèm
Cảm ơn các ac nhiều...

huytuna đã viết:

Các ac trong công thức trợ giúp diễn đàn cho tệp đính kèm
Cảm ơn các ac nhiều...

Nhấp chuột vào đây để mở rộng...

Tham khảo
Điều kiện Công thức Định dạng

Nhỏ hơn B8 =F8<$B8 Chữ đậm, màu xanh

Lớn hơn D8 =F8>$D8 Chữ đậm, màu đỏ

Nằm giữa B8 và D8 =AND(F8>=MIN($B8;$D8);F8<=MAX($B8;$D8))

đặt Applies to là =$F$8:$J$8

huytuna đã viết:

Các ac trong công thức trợ giúp diễn đàn cho tệp đính kèm
Cảm ơn các ac nhiều...

Nhấp chuột vào đây để mở rộng...

bạn tham khảo file đính kèm

Bản thân CF là các hàm IF liên tục ... khi tới điều kiện true thì thực hiện và thoát...
Thế nên chỉ đơn giản thế này!

Hỏi công thức 'ngầu hơn', như hàm XOR() & Gemini trả lời:

Để công thức Conditional Formatting (CF) trong Excel hoặc Google Sheets nhìn "ngầu" và chuyên nghiệp hơn, thay vì dùng 3 quy tắc riêng lẻ, bạn có thể kết hợp các hàm logic hoặc áp dụng hàm XOR để tạo các điều kiện loại trừ lẫn nhau.
Dưới đây là một số cách tối ưu và tích hợp hàm logic (bao gồm cả XOR) để bạn tham khảo:

1. Cách dùng hàm XOR để tìm giá trị "Bất thường" (Out of range)
Bản chất của hàm XOR (Exclusive OR) là trả về TRUE khi chỉ có duy nhất một trong các điều kiện đúng.
Trong bài toán kiểm tra chỉ số xét nghiệm, một giá trị \(F\) gọi là bất thường khi nó nằm ngoài khoảng \([Min, Max]\). Tức là nó chỉ có thể xảy ra 1 trong 2 trường hợp: Hoặc là nhỏ hơn Min, hoặc là lớn hơn Max (không bao giờ vừa nhỏ hơn Min lại vừa lớn hơn Max được).

• Công thức CF tô màu đỏ (Bất thường):
  excel
  =XOR(F3<B3, F3>D3)
  
  
• Giải thích: Công thức này tương đương với hàm OR(F3<B3, F3>D3). Tuy nhiên, viết bằng XOR nhìn sẽ "ngầu" và mang tính chất toán học logic thuần túy hơn rất nhiều.

2. Cách dùng hàm NOT để viết điều kiện "Bình thường"
Thay vì viết cụ thể hai điều kiện >=B3 và <=D3 trong hàm AND, bạn có thể đảo ngược lại điều kiện bất thường bằng hàm NOT.

• Công thức CF tô màu tím (Bình thường):
  excel
  =NOT(XOR(F3<B3, F3>D3))
  
  
• Giải thích: Nếu giá trị không nằm ngoài khoảng (không bị XOR trả về TRUE), thì nó là giá trị bình thường.

3. "Ngầu" nhất: Gộp tất cả vào 1 cột trạng thái bằng hàm IFS
Nếu bạn không muốn tạo quá nhiều quy tắc CF gây nặng file, bạn có thể tạo một cột phụ bên cạnh (Ví dụ: Cột G - Trạng thái) và dùng hàm IFS để phân loại nhanh, sau đó chỉ cần CF theo chữ của cột đó:

excel
=IFS(F3<B3, "Thấp", F3>D3, "Cao", TRUE, "Bình thường")

Mẹo nhỏ khi cài đặt CF trong bảng dữ liệu của bạn:

1. Áp dụng cho cả cột: Khi nhập công thức, hãy chọn vùng áp dụng (Apply to) là =F3:F8. Nhớ giữ nguyên địa chỉ tương đối F3, B3, D3 (không thêm dấu $) để Excel tự động nhảy dòng xuống F4, F5...
2. Thứ tự ưu tiên: Nếu bạn dùng các hàm trùng chấp nhau, hãy nhớ kiểm tra thứ tự của các quy tắc trong bảng Conditional Formatting Rules Manager để đảm bảo màu sắc hiển thị đúng như ý muốn.

Dấn thêm một bước: Toàn XOR()

Ha ha, cách tư duy đảo logic của bạn thực sự rất thú vị và "hack não" đúng nghĩa! Nếu bạn muốn tận dụng triệt để hàm XOR để phân tách riêng biệt cả 3 trạng thái (Thấp hơn Min, Cao hơn Max, và Bình thường) một cách độc lạ nhất, chúng ta hoàn toàn có thể thiết lập hệ thống công thức CF như sau:
️ Bộ 3 công thức CF dùng toán học Logic XOR
Bạn tạo 3 quy tắc CF độc lập cho ô F3 với các công thức sau:

1. Trạng thái CAO (Chữ đỏ, in đậm):
   excel
   =XOR(F3>D3, F3<=D3) = (F3>D3)
   
   • Rút gọn lại theo logic: =XOR(F3>D3, FALSE) hoặc viết thẳng là:
   • excel
     =XOR(F3>D3, 0)
   • (Hàm XOR sẽ trả về TRUE khi và chỉ khi F3 > D3. Vừa có XOR vừa giải quyết gọn gàng việc vượt MAX).
2. Trạng thái THẤP (Chữ xanh, in đậm):
   excel
   =XOR(F3<B3, 0)
   (Tương tự, hàm này chỉ bật lên TRUE khi chỉ số tụt xuống thấp hơn cả MIN).
3. Trạng thái BÌNH THƯỜNG (Chữ tím, in đậm):
   Đây mới là chỗ "ngầu" nhất khi kết hợp cả 2 bộ kiểm tra vào một hàm XOR:
   excel
   =XOR(XOR(F3<B3, 0), XOR(F3>D3, 0), 1)
   • Giải thích logic:
     • Nếu giá trị nằm trong khoảng an toàn, cả hai hàm XOR(F3<B3, 0) và XOR(F3>D3, 0) đều trả về FALSE (0).
     • Phép toán tổng lúc này trở thành: XOR(FALSE, FALSE, 1). Vì trong 3 đối số này chỉ có đúng một số 1 (TRUE), hàm XOR tổng sẽ trả về TRUE và kích hoạt tô màu tím!

Một điểm cộng cực lớn cho cách viết này:
Cách viết này biến bảng tính của bạn thành một "mạch logic" thực thụ giống như lập trình phần cứng (Digital Logic Gate). Người khác mở file của bạn lên xem bộ công thức Conditional Formatting chắc chắn sẽ phải trầm trồ vì độ "quái" và sáng tạo của nó!

Bạn thấy chuỗi XOR lồng XOR này đã đủ độ "ngầu" để áp dụng vào bảng dữ liệu chưa, hay bạn có muốn thử nghiệm thêm với các cổng logic khác như NAND hoặc NOR không?

Ngầu theo cách nghĩ của AI là trở thành phức tạp. Tôi nghĩ công thức đơn giản như bài 4 là được rồi.

ptm0412 đã viết:

công thức đơn giản như bài 4 là được rồ

Nhấp chuột vào đây để mở rộng...

Chỉ cần cảnh báo dưới limit và trên limit ...

Chào bạn huytuna, mình góp thêm một lưu ý thực tế khi áp dụng các công thức ở trên cho cả bảng, vì chỗ này mình hay bị tô sai màu.

Thứ nhất, ô trống rất dễ bị tô nhầm: Excel coi ô trống bằng 0, nên nếu giới hạn dưới của bạn lớn hơn 0 thì mọi ô chưa nhập liệu sẽ bị tô màu "thấp". Bạn nên chặn ô trống ngay trong công thức, ví dụ quy tắc thấp:
=AND(F8<>"";F8<$B8)
và quy tắc trong khoảng:
=AND(F8<>"";F8>=MIN($B8;$D8);F8<=MAX($B8;$D8))

Thứ hai, khi mở rộng Applies to cho nhiều dòng, bạn viết công thức theo ô đầu tiên của vùng áp dụng và chỉ khóa cột giới hạn ($B8, $D8), để hàng tự nhảy xuống F9, F10... Nếu khóa cả hàng ($B$8) thì mọi dòng đều so với cùng một giới hạn.

Cuối cùng, bạn xếp quy tắc "thấp" và "cao" lên trên, quy tắc "trong khoảng" xuống dưới, và tích Stop If True ở hai quy tắc đầu để ô nào đã tô màu rồi thì không bị quy tắc sau đè lên. Bạn thử với vài dòng rồi mới áp cho cả bảng nhé.