การจัดรูปแบบตามเงื่อนไขตามสูตรหลักใน Excel

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

การสร้างกฎการจัดรูปแบบตามเงื่อนไขตามสูตร

การจัดรูปแบบตามเงื่อนไขจะทำงานตามเงื่อนไขบางประการ และยังมีกฎที่กำหนดเองตามสูตรอีกด้วย การจัดรูปแบบตามเงื่อนไขตามสูตรจะกำหนดกฎโดยใช้สูตรแบบกำหนดเองที่ประเมินข้อมูลในเวิร์กชีตของคุณ การจัดรูปแบบจะถูกนำไปใช้เมื่อสูตรส่งกลับค่า TRUE หากต้องการใช้กฎตามสูตร คุณจะต้องทำตามขั้นตอนบางอย่าง:

• เลือกช่วงเซลล์เพื่อใช้การจัดรูปแบบตามเงื่อนไข
• ไปที่หน้าแรก แท็บ>> จาก การจัดรูปแบบตามเงื่อนไข>> เลือก กฎใหม่ .
• ในกฎการจัดรูปแบบใหม่ กล่องโต้ตอบ;
  • เลือกตัวเลือก ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ .
  • ใน ค่ารูปแบบที่สูตรนี้เป็นจริง กล่อง;
    • แทรกสูตรที่กำหนดเอง
  • คลิกที่ รูปแบบ เพื่อเลือกรูปแบบที่คุณต้องการ
  • คลิก ตกลง .

ลองพิจารณาชุดข้อมูลการขายเพื่อใช้การจัดรูปแบบตามเงื่อนไขขั้นสูงกับกฎตามสูตร

1. ไฮไลต์แถวตามเงื่อนไข

หากคุณต้องการเน้นแถวที่มียอดขายมากกว่าค่าที่กำหนด คุณสามารถใช้สูตรการเปรียบเทียบต่อไปนี้ได้

สูตร:

=$G2>=5000

สูตรนี้จะตรวจสอบว่าค่าในเซลล์ C2 (ยอดขาย) มากกว่า 5,000 หรือไม่ ใช้การอ้างอิงแบบสัมบูรณ์ ($) สำหรับคอลัมน์หากการเปรียบเทียบสอดคล้องกันในแถวต่างๆ

• คลิก รูปแบบ และตั้งค่าตัวเลือกการจัดรูปแบบที่คุณต้องการ เราก็เลือกสีเขียว
• คลิก ตกลง เพื่อใช้กฎ

2. เน้นรายการการขายที่ซ้ำกัน

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

สูตร:

=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 เพื่อพิจารณาว่าชุดค่าผสมในแต่ละแถวซ้ำกันภายในช่วงที่ระบุหรือไม่ จากนั้นจะนับชุดค่าผสมเฉพาะของแต่ละแถวในคอลัมน์ A ถึง G หากจำนวนมากกว่า 1 จะถือว่าแถวนั้นซ้ำกัน จากนั้นแถวจะถูกเน้นว่าซ้ำกัน

• คลิก รูปแบบ และเลือกการจัดรูปแบบของคุณ เราเลือกการเติมสีเหลือง
• คลิก ตกลง เพื่อใช้กฎ

3. ระบายสีทุกแถว (ลายทางม้าลาย)

หากต้องการจัดรูปแบบตารางข้อมูลและทำให้อ่านง่าย คุณสามารถเพิ่มสีลงในแถวอื่นๆ ได้ หากต้องการเพิ่มรูปแบบนี้ ให้ใช้สูตรต่อไปนี้

สูตร:

=MOD(แถว(),2)=0

ฟังก์ชัน ROW() ส่งกลับหมายเลขแถวปัจจุบัน จากนั้น MOD(ROW(),2)=0 จะตรวจสอบว่าหมายเลขแถวเป็นเลขคู่หรือไม่

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

4. เน้นวันที่ที่ผ่านมา

หากคุณต้องการเน้นวันที่ที่เลยวันที่ของวันนี้เพื่อระบุรายการที่เกินกำหนด คุณสามารถทำได้โดยใช้สูตรของ TODAY ฟังก์ชั่น

สูตร:

=AND(ISNUMBER(A2), A2 <วันนี้())

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

• คลิก รูปแบบ และเลือกการจัดรูปแบบของคุณ เราเลือกการเติมสีแดง
• คลิก ตกลง เพื่อใช้กฎ

5. เน้นมูลค่าการขายสูงสุด 10%

คุณสามารถเน้นยอดขาย 10% แรกของชุดข้อมูลการขายของคุณได้ หากต้องการเน้นยอดขายสูงสุด คุณสามารถใช้ฟังก์ชัน PERCENTILE ได้

สูตร:

=$G2>=PERCENTILE($G$2:$G$17,0.9)

PERCENTILE($G$2:$G$17,0.9) จะคำนวณเปอร์เซ็นไทล์ที่ 90 ของช่วง ดังนั้นค่าที่มากกว่าหรือเท่ากับค่านี้จะอยู่ใน 10% แรก หากค่าในคอลัมน์ G มากกว่าหรือเท่ากับเปอร์เซ็นไทล์นี้ การจัดรูปแบบจะมีผลกับทั้งแถว

• คลิก รูปแบบ และเลือกการจัดรูปแบบของคุณ เราได้เลือกข้อความตัวหนาพร้อมสีเติมสีเขียว
• คลิก ตกลง เพื่อใช้กฎ

6. การรวมหลายเงื่อนไข

คุณสามารถสร้างกฎการจัดรูปแบบตามเงื่อนไขที่ซับซ้อนได้โดยการรวมเงื่อนไขต่างๆ เข้ากับฟังก์ชันทางตรรกะ การใช้ฟังก์ชัน AND/OR เพื่อเน้นเซลล์ตามเงื่อนไขต่างๆ

สูตร:

=AND($G2>=3000, $C2=”ตะวันออก”)

สูตรนี้จะเน้นแถวเฉพาะในกรณีที่ยอดขายมากกว่าหรือเท่ากับ 3,000 และภูมิภาคอยู่ทางทิศตะวันออก

• คลิก รูปแบบ และเลือกการจัดรูปแบบของคุณ เราได้เลือกแบบอักษรตัวเอียงและสีเติมสีพีช
• คลิก ตกลง เพื่อใช้กฎ

การจัดการกฎการจัดรูปแบบตามเงื่อนไข

ในการจัดการการจัดรูปแบบตามเงื่อนไขของคุณ:

• ไปที่การจัดรูปแบบตามเงื่อนไข แท็บ>> เลือก จัดการกฎ .
• ที่นี่คุณสามารถดู เพิ่ม แก้ไข ลบ ทำซ้ำ หรือเปลี่ยนลำดับความสำคัญของกฎของคุณได้
• คุณยังสามารถใช้ปุ่ม “นำไปใช้กับ” ได้อีกด้วย เพื่อแก้ไขช่วงเซลล์เพื่อใช้กฎ

บทสรุป

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