เชี่ยวชาญ Excel Conditional Formatting: ใช้สูตรเพื่อเน้นข้อมูลได้อย่างมีประสิทธิภาพ

Conditional Formatting กลายเป็นส่วนสำคัญของประสบการณ์ Microsoft Excel ฟีเจอร์นี้ช่วยให้เราระบุค่าที่เฉพาะเจาะจงในกลุ่มข้อมูลขนาดใหญ่หรือแบบสุ่มได้ เราสามารถไม่เพียงระบุค่าบางค่า แต่ยังระบุได้ว่าค่าไหนอยู่ในช่วงที่กำหนดหรือไม่ ข้อความ ตัวเลข ข้อผิดพลาด เป็นต้น ด้วย Conditional Formatting ซึ่งตรงกับชื่อของมัน คือ การจัดรูปแบบเซลล์ใน Excel ตามเงื่อนไขว่าจริงหรือเท็จ ในบทเรียนนี้ เราจะพูดถึง Conditional Formatting พร้อมสูตรใน Excel

วิดีโอสอนการใช้ Conditional Formatting กับสูตรใน Excel

ในวิดีโอฉบับนี้:

• คุณจะได้เรียนรู้วิธีจัดรูปแบบเซลล์โดยใช้ Conditional Formatting แบบสูตร
• คุณจะได้รู้จัก ฟังก์ชัน ISTEXT
• และคุณจะได้เรียนรู้อีกครั้งวิธีใช้ Absolute Referencing ในสูตร

คุณสามารถดาวน์โหลดสมุดงานที่ใช้สาธิตได้จากลิงก์ดาวน์โหลดด้านล่าง

วิธีใช้ Conditional Formatting กับสูตรใน Excel

หัวใจหลักของการใช้ Conditional Formatting กับสูตรใน Excel ค่อนข้างตรงไปตรงมา เราต้องเลือกฟีเจอร์ Conditional Formatting แล้วแทนที่จะเลือกตัวเลือกที่กำหนดไว้ล่วงหน้า เราต้องเลือก New Rule แล้วเลือกใช้สูตรเพื่อป้อนเข้าไป

จำไว้ว่า สูตรที่นี่หมายถึงเงื่อนไขที่เราป้อนในกล่อง Conditional Formatting หากเงื่อนไขเป็นจริง รูปแบบจะถูกนำไปใช้ และในทางกลับกัน

นี่คือการสาธิตโดยละเอียดของ Conditional Formatting กับสูตรใน Excel

ขั้นตอน:

• ขั้นแรก เลือกช่วงเซลล์ที่คุณต้องการจัดรูปแบบด้วยสูตร
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• จากนั้นเลือก Conditional Formatting จากส่วนกลุ่ม Styles
• หลังจากนั้น เลือก New Rule จากเมนูแบบเลื่อนลง

• ผลลัพธ์คือ กล่อง New Formatting Rule จะปรากฏขึ้น
• จากนั้นคุณสามารถป้อนสูตรในฟิลด์ภายใต้ Format values where this formula is true:

• คุณยังสามารถเลือกรูปแบบการจัดรูปแบบได้โดยคลิกที่ Format
• เมื่อคุณทำตามขั้นตอนทั้งหมดข้างต้นและรูปแบบการจัดรูปแบบของเซลล์เสร็จสิ้น คลิก OK บนกล่องนี้

ตัวอย่างเช่น เราป้อนสูตรดังต่อไปนี้ตามที่แสดงในรูปหลังจากทำตามขั้นตอนทั้งหมดนี้

ช่วงข้อมูลบนสเปรดชีตคือ B3:B6 ตอนนี้จะมีลักษณะดังนี้

ที่นี่ เราใส่เงื่อนไขว่าค่าเซลล์สูงกว่า 3 ดังนั้น ในช่วงที่เลือกซึ่งมีค่าสูงกว่า 3 อยู่ รูปแบบจะถูกนำไปใช้

21 ตัวอย่างการใช้ Conditional Formatting กับสูตรใน Excel

ในส่วนนี้ เราจะสาธิตตัวอย่างต่างๆ ของการใช้สูตรในฟีเจอร์ Conditional Formatting ใน Excel ความเป็นไปได้ของการใช้ฟีเจอร์นี้และการรวมแต่ละอย่างเข้าด้วยกันมีมากมาย เราพยายามครอบคลุมสูตรหลักบางส่วนที่เราคิดว่าคุณอาจพบว่ามีประโยชน์ หากคุณต้องการตัวใดตัวหนึ่งอย่างเฉพาะเจาะจง คุณสามารถหาได้ในตารางด้านบน

1. จัดรูปแบบค่าข้อความ

ก่อนอื่น ให้พิจารณาชุดข้อมูลนี้ที่มีทั้งค่าตัวเลขและข้อความ

ด้วยความช่วยเหลือของ Conditional Formatting กับสูตร เราสามารถแยกค่าข้อความใน Excel ได้อย่างง่ายดาย เพื่อทำเช่นนั้น เราต้องใช้ ฟังก์ชัน ISTEXT ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถจัดรูปแบบโดยใช้ฟังก์ชันนี้ใน Conditional Formatting ใน Excel ได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=ISTEXT(B5)

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

คุณจะสังเกตว่า Excel จะจัดรูปแบบข้อความทั้งหมดในชุดข้อมูลหลังจากใช้สูตรนี้ใน Conditional Formatting

2. เน้นเซลล์ที่เท่ากับเซลล์อื่น

ตอนนี้มาดูตัวอย่างอีกอย่างของการจับคู่ค่า

มีชุดข้อมูลขนาดใหญ่ในชุดข้อมูลนี้ เราจะจัดรูปแบบเฉพาะค่าที่ตรงกับค่าเซลล์ E5 เท่านั้น

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถทำได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=B5=$E$5

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

นี่คือวิธีที่เราสามารถเน้นเซลล์ที่เท่ากับเซลล์อื่นได้โดยใช้ Conditional Formatting ด้วยความช่วยเหลือของสูตรใน Excel

3. Conditional Formatting ใน Excel ตามเซลล์อื่น

ตอนนี้มาถึงจุดที่แตกต่างออกไปเล็กน้อยจากการเท่ากัน เราจะจัดรูปแบบชุดข้อมูลตามว่ามันมากกว่าหรือน้อยกว่าค่าของเซลล์อื่นในส่วนนี้ และเราจะเน้นพวกเขาโดยใช้วิธีสูตร Conditional Formatting ใน Excel

เราจะทำการนี้บนชุดข้อมูลต่อไปนี้

เราจะจัดรูปแบบช่วง C5:C9 ตามค่าของเซลล์ E5

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถทำได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=C5>$E$5

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK ชุดข้อมูลจะเป็นแบบนี้

• เช่นเดียวกัน คุณสามารถทำซ้ำกระบวนการเดียวกันสำหรับค่าที่ต่ำกว่าและสุดท้ายจะได้ชุดข้อมูลแบบนี้

นี่คือวิธีที่เราสามารถใช้ Conditional Formatting กับสูตรตามค่าของเซลล์อื่นใน Excel

4. Conditional Formatting ใช้สูตร IF ใน Excel

ตอนนี้เราจะดูว่าสามารถใช้สูตรที่มี ฟังก์ชัน IF เป็นสูตร Conditional Formatting ใน Excel ได้อย่างไร เราไม่สามารถใช้ฟังก์ชันนี้โดยตรงในกล่อง Conditional Formatting ได้ แต่เราสามารถใช้ฟังก์ชันเพื่อรับค่า ซึ่งเราสามารถใช้เพื่อจัดรูปแบบเซลล์เพิ่มเติมโดยใช้ Conditional Formatting ใน Excel

มารับชุดข้อมูลสำหรับตัวอย่างนี้ก่อน นี่คือชุดที่เราจะทำงาน

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ E5 และเขียนสูตรต่อไปนี้

=IF(D5>C5,"Profit","Loss")

• จากนั้นกด Enter บนแป้นพิมพ์ของคุณ

• ตอนนี้คลิกและลากจุดจับเติม (fill handle) ไปจนถึงท้ายคอลัมน์เพื่อทำซ้ำสูตรสำหรับเซลล์ที่เหลือ

• ต่อไป เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=E5="Profit"

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

ชุดข้อมูลจะกลายเป็นแบบนี้

นี่คือวิธีที่เราสามารถใช้ ฟังก์ชัน IF สำหรับสูตร Conditional Formatting ใน Excel

5. เน้นเซลล์โดยใช้หลายเงื่อนไข

ตอนนี้มากลับไปที่ชุดข้อมูลแรก

ตอนนี้เราจะเน้นเซลล์ทั้งหมดที่เป็น 5, 6 หรือมีข้อความ "cat"

เพื่อทำเช่นนั้นในกล่องสูตร Conditional Formatting ใน Excel เราต้องการ ฟังก์ชัน OR

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถทำได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=OR(B5=5,B5=6,B5="cat")

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

ตอนนี้ดูที่ชุดข้อมูล

นี่คือวิธีที่เราสามารถใช้ ฟังก์ชัน OR ในสูตร Conditional Formatting ใน Excel สำหรับหลายเงื่อนไข

6. เน้นแถวที่ซ้ำกัน

ต่อไป เราจะเน้นเซลล์ในแถวทั้งหมดที่แถวทั้งหมดตรงกับอีกแถวหนึ่ง มันมีลูกเล่นนิดหน่อย เราจะต้องใช้ฟังก์ชัน COUNTIF และ CONCATENATE สำหรับเรื่องนี้

มารับชุดข้อมูลตัวอย่างสำหรับการสาธิต

ที่นี่ เราเห็นว่าแถวที่ 3 และแถวที่ 6 ตรงกันสมบูรณ์ ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถใช้ฟังก์ชันเหล่านี้เพื่อเน้นค่าซ้ำในคอลัมน์ใน Excel ได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ F5 และเขียนสูตรต่อไปนี้

=CONCATENATE(B5,C5,D5,E5)

• จากนั้นกด Enter

• ตอนนี้เลือกเซลล์อีกครั้งและคลิกและลากจุดจับเติมเพื่อเติมสูตรสำหรับอ้างอิงของเซลล์ที่เหลือ

• ดูค่อนข้างยุ่งเหยิง แต่ละชั่วคราว ข้ามไปก่อน ตอนนี้เลือกช่วง B5:B10
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=COUNTIF($F$5:$F$10,$F5)>1

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK ชุดข้อมูลจะเป็นแบบนี้

• ตอนนี้ คลิกขวาที่หัวคอลัมน์ F และเลือก Hide จากเมนูบริบท

นี่จะเป็นมุมมองสเปรดชีตสุดท้ายหลังจากนั้น

7. เน้นค่าเซลล์ที่มีสูตร

ตอนนี้มาดูชุดข้อมูลที่มีสูตรเพื่อทำให้เสร็จสมบูรณ์

เราจะใช้ Conditional Formatting โดยใช้สูตรเพื่อเน้นเซลล์เหล่านี้ เพื่อเหตุผลนี้ เราต้องใช้ ฟังก์ชัน ISFORMULA

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถทำได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=ISFORMULA(B5)

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

ชุดข้อมูลจะเป็นแบบนี้

8. เน้นยอดขายจากภูมิภาคเฉพาะ

ในตัวอย่างนี้ เราจะเน้นยอดขายจากชุดข้อมูลที่เป็นของภูมิภาคเฉพาะ มารับชุดข้อมูลต่อไปนี้

ตอนนี้ สมมติว่าเราต้องการเน้นรายการที่มาจาก Arizona เพื่อทำเช่นนั้น ทำตามขั้นตอนเหล่านี้

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=$D5="Arizona"

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

Excel จะตอนนี้ทำเครื่องหมายยอดขายจาก Arizona เป็นผลลัพธ์ของขั้นตอนเหล่านี้

9. เน้นความแตกต่างของคอลัมน์

เรายังสามารถเน้นแถวที่มีคอลัมน์ต่างจากแถวที่อยู่ติดกันได้ ตัวอย่างเช่น มาดูตัวอย่างต่อไปนี้

เพื่อเน้นพวกเขาโดยใช้ Conditional Formatting กับสูตรใน Excel ทำตามขั้นตอนเหล่านี้

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=$B5<>$C5

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

นี่จะเป็นผลลัพธ์สุดท้ายของขั้นตอนเหล่านี้

10. ใช้สูตรเพื่อเน้นค่าที่หายไป

ในส่วนนี้ เราจะใช้สูตรเพื่อเน้นค่าที่หายไปใน Conditional Formatting ใน Excel มารับชุดข้อมูลต่อไปนี้เป็นตัวอย่าง

มีค่าที่หายไปบางส่วนใน List 2 ที่มีอยู่ใน List 1 เราจะเน้นพวกเขา ทำตามขั้นตอนเหล่านี้เพื่อดูว่าทำอย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=COUNTIF($D$5:$D$10,$B5)=0

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

Excel จะเน้นเซลล์สำหรับสูตร Conditional Formatting นี้

11. สร้างกล่องค้นหาสimple เพื่อเน้นเซลล์

ตอนนี้มาลองตัวอย่างสนุกๆ ที่ผสมผสานรูปแบบต่างๆ นี่คือชุดข้อมูลสำหรับนั้น

ที่นี่ เราจะใส่ค่าในเซลล์ E4 และ Excel จะเน้นพวกเขาในช่วงก่อนหน้า ทั้งหมดด้วยวิธี Conditional Formatting ด้วยสูตร เราจะต้องการการผสมผสานของฟังก์ชัน ISNUMBER และ SEARCH สำหรับวิธีนี้

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถทำได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=ISNUMBER(SEARCH($E$4,B5))

• ต่อไป เลือกรูปแบบที่คุณต้องการ

• สุดท้าย คลิกที่ OK

ตอนนี้ ดูที่ชุดข้อมูล ข้อความที่มีคำว่า game จะถูกทำเครื่องหมาย

ถ้าเราเปลี่ยนค่าในเซลล์ E4 เช่น "health" ไฮไลท์จะเปลี่ยนไป

12. เน้นค่าที่น้อยกว่าค่าเฉลี่ย

ตอนนี้มาดูชุดข้อมูลจากก่อนหน้านี้อีกครั้ง

เราจะใช้การประยุกต์ใช้พื้นฐานของ Conditional Formatting กับสูตรที่นี่ใน Excel - เราจะเน้นค่าที่น้อยกว่าค่าเฉลี่ยในคอลัมน์ E เราต้องการ ฟังก์ชัน AVERAGE สำหรับจุดประสงค์นี้

ทำตามขั้นตอนเหล่านี้เพื่อดูว่าเราสามารถทำได้อย่างไร

ขั้นตอน:

• ขั้นแรก เลือกเซลล์ทั้งหมดในชุดข้อมูลยกเว้นส่วนหัว
• จากนั้นไปที่แท็บ Home บนริบบอนของคุณ
• หลังจากนั้น เลือก Conditional Formatting จากส่วนกลุ่ม Styles
• ต่อไป เลือก New Rule จากเมนูแบบเลื่อนลง

• ในกล่อง Formatting Rule ขั้นแรก เลือกตัวเลือก Use a formula to determine which cells to format ภายใต้ Select a Rule Type
• จากนั้น ใส่สูตรต่อไปนี้ใน Format values where this formula is true

=E5