ไฮไลท์เซลล์ที่มีข้อความจากลิสต์ใน Excel: 7 เทคนิค Conditional Formatting

บทความนี้สอน การไฮไลท์เซลล์ที่มีข้อความตรงกับลิสต์ โดยใช้ Conditional Formatting ร่วมกับฟังก์ชันและสูตรต่างๆ 7 วิธี ครอบคลุมทุกกรณีการใช้งาน

ดาวน์โหลดไฟล์ฝึกปฏิบัติได้ที่นี่

7 วิธี ไฮไลท์เซลล์ที่มีข้อความจากลิสต์

1. ใช้ COUNTIF ใน Conditional Formatting

มีลิสต์ Fruit 1 (B4:B14) และ Fruit 2 (D4:D9) ต้องการไฮไลท์ผลไม้ใน Fruit 1 ที่อยู่ใน Fruit 2

ขั้นตอน:

• เลือกข้อมูล B5:B14

• Home > Conditional Formatting > New Rule

• เลือก Rule Type: Use a formula to determine which cells to format
• สูตร: =COUNTIF($D$5:$D$9,B5)
• คลิก Format → เลือกสี Fill → OK → OK

• เซลล์ที่ตรงกับลิสต์ Fruit 2 ถูกไฮไลท์

2. ใช้ MATCH Function

สูตร: =MATCH($B5,$E$5:$E$9,0)

• เลือกข้อมูล B5:C14 → Conditional Formatting > New Rule > Use formula
• ใส่สูตร → Format > เลือกสี → OK OK

• ได้ผลลัพธ์เหมือน COUNTIF

3. ใช้ SUM + COUNTIF (สำหรับค้นหาคำย่อยในข้อความยาว)

ลิสต์ Books (B4:B12) มีชื่อหนังสือยาว ลิสต์ Text (D5:D8) มีคำค้นหาสั้น ต้องการไฮไลท์หนังสือที่มีคำใน Text

สูตร: =SUM(COUNTIF(B5,"*"&$D$5:$D$8&"*"))

• เลือก B5:B12 → New Rule > Use formula → ใส่สูตร → Format → OK

• หนังสือที่มีคำจากลิสต์ Text ถูกไฮไลท์

หลักการ: COUNTIF ด้วย Wildcard *...* หาคำย่อย → SUM รวมจำนวนที่เจอ → >0 = TRUE

4. ใช้ COUNT + SEARCH

กลับมาใช้ลิสต์ผลไม้ 2 ลิสต์ สูตร: =COUNT(SEARCH(D$5:D$9,B5))

• เลือก B5:B14 → New Rule > Use formula → ใส่สูตร → Format → OK

• เซลล์ที่มีชื่อผลไม้จาก Fruit 2 ถูกไฮไลท์

หลักการ: SEARCH หาตำแหน่งข้อความ (คืน #VALUE! ถ้าไม่เจอ) → COUNT นับเฉพาะตัวเลข (ไม่จับ #VALUE!) → >0 = TRUE

5. ใช้สูตรง่าย + Drop Down List (Dynamic Highlight)

สร้าง Drop-down List ที่ D5 แล้วไฮไลท์เซลล์ที่ตรงกับค่าที่เลือก

ขั้นตอน:

• สร้าง Drop-down List ที่ D5 จากชื่อผลไม้บางชนิด

• เลือก B5:B14 → New Rule > Use formula: =$B5=$D$5

• เซลล์ที่ตรงกับค่าใน D5 ถูกไฮไลท์

• ทำงานแบบ Dynamic: เปลี่ยนค่าใน Drop-down → ไฮไลท์เปลี่ยนตามทันที

6. ใช้ ISNUMBER + FIND (Case Sensitive)

สูตร: =ISNUMBER(FIND($D$5,B5))

• สร้าง Drop-down ที่ D5 → เลือก B5:B14 → New Rule > Use formula → ใส่สูตร → Format → OK

• เซลล์ที่มีข้อความตรงกับ D5 (Case Sensitive) ถูกไฮไลท์

หลักการ: FIND หาตำแหน่ง (Case Sensitive) → ISNUMBER ตรวจสอบว่าเป็นตัวเลข (เจอ) หรือ Error (ไม่เจอ)

7. SUMPRODUCT + ISNUMBER + SEARCH (Case Insensitive, Multiple Keywords)

สูตร: =SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0

• เลือก B5:B12 (Books) → New Rule > Use formula → ใส่สูตร → Format → OK

• หนังสือที่มีคำใดคำหนึ่งจาก Text (D5:D8) ถูกไฮไลท์

🔎 แยกวิเคราะห์สูตร:

• SEARCH($D$5:$D$8,B5) → อาร์เรย์ตำแหน่งหรือ #VALUE! เช่น {#VALUE!;#VALUE!;1;5}
• ISNUMBER(...) → แปลงเป็น {FALSE;FALSE;TRUE;TRUE}
• --ISNUMBER(...) → Double unary แปลงเป็น {0;0;1;1}
• SUMPRODUCT(...)>0 → รวมได้ 2 > 0 = TRUE → ไฮไลท์

สรุป

7 วิธีไฮไลท์ข้อความจากลิสต์: 1) COUNTIF (Exact match) 2) MATCH (Exact match) 3) SUM+COUNTIF Wildcard (Partial match) 4) COUNT+SEARCH (Partial match) 5) Drop-down + Simple Formula (Dynamic) 6) ISNUMBER+FIND (Case Sensitive) 7) SUMPRODUCT+ISNUMBER+SEARCH (Multiple keywords, Case Insensitive) เลือกตามความต้องการครับ

บทความเกี่ยวข้อง

• Excel Cell Color: Add, Edit, Use & Remove
• How to Fill Color in Excel Cell Using Formula (5 Easy Ways)
• How to Highlight a Row in Excel (5 Quick Methods)
• Excel VBA to Highlight Cell Based on Value (5 Examples)
• VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)