Conditional Formatting เป็นฟีเจอร์ทรงพลังของ Excel ใช้ Format อัตโนมัติ (สไตล์สี ไอคอน ตัวหนา) ตามค่าใน Spreadsheet 雖然 Excel มี Built-in Rules ใช้ง่าย แต่ Formula-based Rules ให้ความยืดหยุ่นมากกว่า บทช่วยสอนนี้สอน Advanced Conditional Formatting ด้วย Formula-based Rules ให้ความยืดหยุ่นสูงกว่า Built-in Options มาก
สร้าง Formula-based Conditional Formatting Rule
Conditional Formatting ทำงานตามเงื่อนไข และมี Formula-based Customized Rules Formula-based CF กำหนด Rules ด้วย Custom Formulas ประเมินข้อมูลใน Worksheet Format จะ Apply เมื่อ Formula คืน TRUE ขั้นตอน Apply Formula-based Rule:
- เลือกช่วงเซลล์ที่จะ Apply Conditional Formatting
- แท็บ Home >> Conditional Formatting >> New Rule
- ใน New Formatting Rule dialog:
- เลือก Use a formula to determine which cells to format
- ใน Format values where this formula is true box:
- ใส่ Custom Formula
- คลิก Format เลือก Format ที่ต้องการ
- คลิก OK
ใช้ Sales Dataset สาธิต Advanced CF ด้วย Formula-based Rules
1. Highlight Rows ตามเงื่อนไข
Highlight แถวที่ Sales มากกว่าค่าที่กำหนด ใช้ Comparison Formula:
สูตร:
=$G2>=5000
สูตรตรวจสอบค่าใน C2 (Sales) มากกว่า 5000 ใช้ Absolute Reference ($) สำหรับคอลัมน์ถ้าเปรียบเทียบเหมือนกันทุกแถว
- คลิก Format ตั้ง Format ที่ชอบ เราเลือกสีเขียว
- คลิก OK Apply Rule
2. Highlight Duplicate Sales Entries
Highlight Duplicate Sales Entries เพื่อลบออก ใช้สูตร:
สูตร:
=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
สูตรตรวจสอบแต่ละคอลัมน์ A-G ว่า Combination ของค่าในแต่ละแถวซ้ำใน Range ที่กำหนด นับ Combination ที่ Unique ใน A-G ถ้า Count > 1 แถวนั้นเป็น Duplicate จะ Highlight
- คลิก Format เลือก Format เราเลือก Fill สีเหลือง
- คลิก OK Apply
3. สีสลับแถว (Zebra Striping)
Format ตารางให้อ่านง่าย ใส่สีสลับแถว ใช้สูตร:
สูตร:
=MOD(ROW(),2)=0
ROW() คืน Row Number ปัจจุบัน MOD(ROW(),2)=0 ตรวจสอบว่า Row Number เป็นคู่
- คลิก Format เลือก Format เราเลือก Fill สีเทาอ่อน
- คลิก OK Apply
4. Highlight วันที่ผ่านมา (Past Dates)
Highlight วันที่เก่ากว่าวันนี้ ระบุรายการ Overdue ใช้ Formula ของ TODAY function
สูตร:
=AND(ISNUMBER(A2), A2 < TODAY())
สูตรตรวจสอบวันที่ใน A2 ก่อนวันนี้ Highlight วันที่ผ่านมาใน Dataset
- คลิก Format เลือก Format เราเลือก Fill สีแดง
- คลิก OK Apply
5. Highlight Top 10% Sales Values
Highlight Top 10% ของ Sales ใน Dataset ใช้ PERCENTILE Function
สูตร:
=$G2>=PERCENTILE($G$2:$G$17,0.9)
PERCENTILE($G$2:$G$17,0.9) คำนวณ Percentile 90th ของ Range ค่าที่มากกว่าหรือเท่าอยู่ใน Top 10% ถ้าค่าใน G มากกว่าหรือเท่า Percentile นี้ Formatting จะ Apply ทั้งแถว
- คลิก Format เลือก Format เราเลือก Bold Text พร้อม Fill สีเขียว
- คลิก OK Apply
6. รวมหลายเงื่อนไข (Combining Multiple Conditions)
สร้าง CF Rules ซับซ้อนโดยรวมหลายเงื่อนไขด้วย Logical Functions ใช้ AND/OR Highlight ตามหลายเงื่อนไข
สูตร:
=AND($G2>=3000, $C2=”East”)
สูตร Highlight แถวก็ต่อเมื่อ Sales ≥ 3000 และ Region เป็น East
- คลิก Format เลือก Format เราเลือก Italic Font และ Fill สีพีช
- คลิก OK Apply
จัดการ Conditional Formatting Rules
จัดการ CF Rules:
- แท็บ Conditional Formatting >> Manage Rules
- ที่นี่ View, Add, Edit, Delete, Duplicate, เปลี่ยน Priority ของ Rules
- ใช้ Field “Applies to” แก้ไข Cell Range ให้ Rules
สรุป
ใช้ Advanced Conditional Formatting ด้วย Formula-based Rules ใน Excel Highlight ข้อมูลได้ยืดหยุ่น ไม่ว่าจะ Highlight ตามเงื่อนไขเฉพาะ จัดการ Duplicates รวมหลาย Criteria ตรวจสอบ Due Dates Formula-based Rules ช่วยให้ Spreadsheet มี Insight และดูน่าสนใจมากขึ้น Mastering ฟีเจอร์นี้จะทำให้ Spreadsheet ดีกว่า ลองทดลองสูตรต่างๆ ดูการทำงานและปรับปรุง Spreadsheet ของคุณ
Get FREE Advanced Excel Exercises with Solutions!
