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

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

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

Excel มีตัวเลือกในตัวสำหรับเงื่อนไขที่ใช้โดยทั่วไปเช่นการหาตัวเลขที่มากกว่าหรือน้อยกว่าค่าที่กำหนดหรือค้นหาตัวเลขที่อยู่เหนือหรือต่ำกว่าค่าเฉลี่ย

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

01 จาก 04

ใช้กฎหลายข้อใน Excel

สามารถใช้กฎมากกว่าหนึ่งข้อกับข้อมูลเดียวกันเพื่อทดสอบเงื่อนไขต่างๆ ตัวอย่างเช่นข้อมูลงบประมาณอาจมีการตั้งค่าที่ใช้การเปลี่ยนแปลงการจัดรูปแบบเมื่อมีการใช้จ่ายบางระดับเช่น 50%, 75% และ 100% ของงบประมาณทั้งหมด

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

การค้นหาข้อมูลที่เกินกว่า 25% และเพิ่มขึ้น 50%

ในตัวอย่างต่อไปนี้กฎการจัดรูปแบบตามเงื่อนไขที่กำหนดเองสองกฎจะใช้กับช่วงของ เซลล์ B2 ไปยัง B5.

• กฎข้อแรกตรวจสอบเพื่อดูว่าข้อมูลใน เซลล์ A2 ไปยัง A5 มากกว่าค่าที่สอดคล้องกันใน B2 ไปยัง B5 มากกว่า 25%
• กฎที่สองตรวจสอบเพื่อดูว่ามีข้อมูลเดียวกันหรือไม่ A2: A5 เกินค่าที่สอดคล้องกันใน B2: B5 มากกว่า 50%

ดังที่เห็นได้จากภาพด้านบนถ้าเงื่อนไขข้อใดข้อหนึ่งเป็นจริงสีพื้นหลังของเซลล์หรือเซลล์ในช่วง B1: B4 จะเปลี่ยน.

• สำหรับข้อมูลที่มีความแตกต่างมากกว่า 25% สีพื้นหลังของเซลล์จะเปลี่ยนเป็นสีเขียว
• ถ้าความแตกต่างมากกว่า 50% สีพื้นหลังของเซลล์จะเปลี่ยนเป็นสีแดง

กฎที่ใช้ในการทำงานนี้จะถูกป้อนโดยใช้การจัดรูปแบบตามเงื่อนไข กฎการจัดรูปแบบใหม่ กล่องโต้ตอบ เริ่มต้นด้วยการป้อนข้อมูลตัวอย่างลงในเซลล์ A1 ถึง C5 ตามที่เห็นในภาพด้านบน

ใน ขั้นตอนที่สาม ของกวดวิชาจะเพิ่มสูตรให้กับเซลล์ C2: C4 ที่แสดงเปอร์เซ็นต์ที่แตกต่างกันระหว่างค่าในเซลล์ A2: A5 และ B2: B5 เพื่อตรวจสอบความถูกต้องของกฎการจัดรูปแบบตามเงื่อนไข

02 จาก 04

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

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

การจัดรูปแบบตามเงื่อนไขเพื่อค้นหาการเพิ่มขึ้นของเปอร์เซ็นต์มากกว่า 25

= (A2-B2) / A2> 25%

1. ไฮไลท์ เซลล์ B2 ไปยัง B5 ในแผ่นงาน
2. คลิกที่ แท็บหน้าแรก ของ ริบบิ้น.
3. คลิกที่ การจัดรูปแบบตามเงื่อนไข ไอคอนใน ริบบิ้น เพื่อเปิดเมนูแบบเลื่อนลง
4. เลือก กฎใหม่ เพื่อเปิด กฎการจัดรูปแบบใหม่ กล่องโต้ตอบ
5. จาก สไตล์: เลือกช่องทำเครื่องหมาย คลาสสิก ตัวเลือก
6. ในเมนูแบบเลื่อนลงรายการที่สองให้คลิกที่ตัวเลือกสุดท้ายใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ
7. พิมพ์ สูตร ให้ไว้ด้านบนในพื้นที่ที่ให้ไว้
8. คลิกที่ จัดรูปแบบด้วย: แบบเลื่อนลงจากนั้นเลือก ไฟแดงเติมด้วยข้อความสีแดงเข้ม ตัวเลือก
9. คลิก ตกลง เพื่อปิดกล่องโต้ตอบและกลับไปที่เวิร์กชีท
10. ณ จุดนี้สีพื้นหลังของ เซลล์ B3 และ B5 ควรเป็นสีเขียว

การจัดรูปแบบตามเงื่อนไขเพื่อหาการเพิ่มขึ้นมากกว่าร้อยละ 50

= (A2-B2) / A2> 50%

ไฮไลท์ เซลล์ B2 ไปยัง B5 ในแผ่นงาน

1. คลิกที่ แท็บหน้าแรก ของ ริบบิ้น.
2. คลิกที่ การจัดรูปแบบตามเงื่อนไข ไอคอนใน ริบบิ้น เพื่อเปิดเมนูแบบเลื่อนลง
3. เลือก กฎใหม่ เพื่อเปิด กฎการจัดรูปแบบใหม่ กล่องโต้ตอบ
4. จาก สไตล์: เลือกช่องทำเครื่องหมาย คลาสสิก ตัวเลือก
5. ในเมนูแบบเลื่อนลงรายการที่สองให้คลิกที่ตัวเลือกสุดท้ายใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ
6. พิมพ์ สูตร ให้ไว้ด้านบนในพื้นที่ที่ให้ไว้
7. คลิกที่ จัดรูปแบบด้วย: แบบเลื่อนลงจากนั้นเลือก สีเขียวอ่อนเติมด้วยข้อความสีเขียวเข้ม ตัวเลือก
8. คลิก ตกลง เพื่อปิดกล่องโต้ตอบและกลับไปที่เวิร์กชีท

สีพื้นหลังของ เซลล์ B3 ควรจะเป็นสีเขียวแสดงว่าเปอร์เซ็นต์ความแตกต่างระหว่างตัวเลขเข้า เซลล์ A3 และ B3 มีค่ามากกว่าร้อยละ 25 แต่น้อยกว่าหรือเท่ากับ 50 เปอร์เซ็นต์ สีพื้นหลังของ เซลล์ B5 ควรเปลี่ยนเป็นสีแดงแสดงว่าเปอร์เซ็นต์ความแตกต่างระหว่างตัวเลขใน เซลล์ A5 และ B5 มากกว่าร้อยละ 50

03 จาก 04

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

หากต้องการตรวจสอบว่ากฎการจัดรูปแบบตามเงื่อนไขถูกต้องหรือไม่เราสามารถป้อนสูตรลงในเซลล์ได้ C2: C5 ที่จะคำนวณความแตกต่างเปอร์เซ็นต์ที่แน่นอนระหว่างตัวเลขในช่วง A2: A5 และ B2: B5.

= (A2-B2) / A2

1. คลิกที่ เซลล์ C2 เพื่อให้เซลล์ที่ใช้งานอยู่
2. พิมพ์สูตรข้างต้นแล้วกดปุ่ม เข้าสู่ บนแป้นพิมพ์
3. คำตอบ 10% ควรปรากฏอยู่ เซลล์ C2แสดงว่าหมายเลขใน เซลล์ A2 ใหญ่กว่าตัวเลข 10% เซลล์ B2.
4. อาจจำเป็นต้องเปลี่ยนการจัดรูปแบบ เซลล์ C2 เพื่อแสดงคำตอบเป็นเปอร์เซ็นต์
5. ใช้ เติมที่จับ เพื่อคัดลอกสูตรจาก เซลล์ C2 ไปยัง เซลล์ C3 ไปยัง C5.
6. คำตอบสำหรับ เซลล์ C3 ไปยัง C5 ควรเป็น 30%, 25% และ 60%

คำตอบในเซลล์เหล่านี้แสดงให้เห็นว่ากฎการจัดรูปแบบตามเงื่อนไขมีขึ้นอย่างถูกต้องเนื่องจากความแตกต่างระหว่าง เซลล์ A3 และ B3 เป็นมากกว่าร้อยละ 25 และความแตกต่างระหว่าง เซลล์ A5 และ B5 มากกว่าร้อยละ 50

เซลล์ B4 ไม่เปลี่ยนสีเพราะความแตกต่างระหว่าง เซลล์ A4 และ B4 เท่ากับ 25 เปอร์เซ็นต์และกฎการจัดรูปแบบตามเงื่อนไขของเราระบุว่าต้องมีเปอร์เซ็นต์มากกว่า 25 เปอร์เซ็นต์เพื่อให้สีพื้นหลังเปลี่ยนเป็นสีเขียว

04 จาก 04

ลำดับความสำคัญสำหรับการจัดรูปแบบตามเงื่อนไข

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

ในตัวอย่างที่ใช้ในบทแนะนำนี้กฎข้อขัดแย้งเนื่องจากกฎทั้งสองใช้ตัวเลือกการจัดรูปแบบเดียวกันนั่นคือการเปลี่ยนสีของเซลล์พื้นหลัง

ในกรณีที่กฎข้อที่สองเป็นจริง (ความแตกต่างในค่ามากกว่า 50 เปอร์เซ็นต์ระหว่างสองเซลล์) กฎข้อแรก (ความแตกต่างในค่ามากกว่า 25 เปอร์เซ็นต์) เป็นจริงเช่นกัน

ลำดับความสำคัญของ Excel

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

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

ดังที่แสดงในภาพด้านบนกฎข้อที่สองที่ใช้ในบทแนะนำนี้มีค่าสูงกว่าในรายการและดังนั้นจึงมีความสำคัญมากกว่ากฎข้อแรก เป็นผลให้สีพื้นหลังของเซลล์ B5 เปลี่ยนเป็นสีแดง

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

การใช้กฎที่ไม่ขัดแย้งกัน

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

หากกฎการจัดรูปแบบเงื่อนไขแรกในตัวอย่างของเราจัดรูปแบบช่วงของเซลล์ B2: B5 มีเส้นขอบสีเขียวแทนสีพื้นหลังสีเขียวกฎการจัดรูปแบบสองเงื่อนไขจะไม่ขัดแย้งกันเนื่องจากทั้งสองรูปแบบสามารถใช้งานได้โดยไม่รบกวนการทำงานอื่น ๆ

ผลที่ตามมา, เซลล์ B5 จะมีทั้งเส้นขอบสีเขียวและสีพื้นหลังสีแดงเนื่องจากความแตกต่างระหว่างตัวเลขเข้า เซลล์ A5 และ B5 สูงกว่าทั้ง 25 และ 50 เปอร์เซ็นต์

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

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

หากใช้สีพื้นหลังสีเหลืองเป็นครั้งแรก เซลล์ B2 ไปยัง B5 ในตัวอย่างเมื่อมีการเพิ่มกฎการจัดรูปแบบตามเงื่อนไขแล้วเท่านั้น เซลล์ B2 และ B4 จะอยู่สีเหลือง เนื่องจากมีการใช้กฎการจัดรูปแบบตามเงื่อนไขเข้ากับเซลล์ B3 และ B5, สีพื้นหลังของพวกเขาจะเปลี่ยนจากสีเหลืองเป็นสีเขียวและสีแดงตามลำดับ