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

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

ตัวเลือกการจัดรูปแบบจะใช้เมื่อเซลล์ที่เลือกเท่านั้นที่เป็นไปตามเงื่อนไขที่ตั้งไว้

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

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

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

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

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

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

ตัวอย่าง: การค้นหาข้อมูลที่เกินกว่า 25% และเพิ่มขึ้น 50% เมื่อใช้การจัดรูปแบบตามเงื่อนไข

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

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

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

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

กฎที่ใช้ในการทำงานนี้

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

จะถูกป้อนโดยใช้ กล่องโต้ตอบ New Formatting Rule ในการจัดรูปแบบ ตามเงื่อนไข

การป้อนข้อมูลบทแนะนำ

1. ป้อนข้อมูลลงในเซลล์ A1 ถึง C5 ดังที่แสดงในภาพด้านบน

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

การตั้งค่ากฎการจัดรูปแบบแบบพิเศษ

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

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

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

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

1. เมื่อเลือกเซลล์ B2 ถึง B5 ให้ทำซ้ำตามขั้นตอนที่ 1 ถึง 6 ข้างต้น
2. พิมพ์สูตร: = (A2-B2) / A2> 50% ในพื้นที่ที่จัดเตรียมไว้
3. คลิกปุ่ม รูปแบบ เพื่อเปิดกล่องโต้ตอบ Format Cells
4. คลิกที่แท็บ เติม และเลือกสีเติมสีแดง
5. คลิก OK สองครั้งเพื่อปิดกล่องโต้ตอบและกลับไปที่ แผ่นงาน
6. สีพื้นหลังของเซลล์ B3 ควรเป็นสีฟ้าแสดงว่าเปอร์เซ็นต์ความแตกต่างระหว่างตัวเลขในเซลล์ A3 และ B3 มากกว่า 25% แต่น้อยกว่าหรือเท่ากับ 50%
7. สีพื้นหลังของเซลล์ B5 ควรเปลี่ยนเป็นสีแดงแสดงว่าเปอร์เซ็นต์ความแตกต่างระหว่างจำนวนในเซลล์ A5 และ B5 มากกว่า 50%

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

การคำนวณความแตกต่าง%

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

1. คลิกที่เซลล์ C2 เพื่อทำให้เซลล์เป็นเซลล์ที่ใช้งานอยู่
2. พิมพ์สูตร = (A2-B2) / A2 แล้วกดปุ่ม Enter บนแป้นพิมพ์
3. คำตอบ 10% ควรปรากฏในเซลล์ C2 ซึ่งระบุว่าตัวเลขในเซลล์ A2 มีขนาดใหญ่กว่าจำนวนเซลล์ในเซลล์ B2 ถึง 10%
4. อาจจำเป็นต้อง เปลี่ยนการจัดรูปแบบ ในเซลล์ C2 เพื่อแสดงคำตอบเป็นเปอร์เซ็นต์
5. ใช้ หมายเลขอ้างอิงเติม เพื่อคัดลอกสูตรจากเซลล์ C2 ไปยังเซลล์ C3 ถึง C5
6. คำตอบสำหรับเซลล์ C3 ถึง C5 ควรเป็น 30%, 25% และ 60%
7. คำตอบในเซลล์เหล่านี้แสดงให้เห็นว่ากฎการจัดรูปแบบตามเงื่อนไขสร้างขึ้นถูกต้องเนื่องจากความแตกต่างระหว่างเซลล์ A3 และ B3 มีค่ามากกว่า 25% และความแตกต่างระหว่างเซลล์ A5 และ B5 มากกว่า 50%
8. เซลล์ B4 ไม่ได้เปลี่ยนสีเนื่องจากความแตกต่างระหว่าง เซลล์ A4 และ B4 เท่ากับ 25% และกฎการจัดรูปแบบตามเงื่อนไขของเราระบุว่าต้องมีเปอร์เซ็นต์มากกว่า 25% สำหรับเปลี่ยนสีพื้นหลังเป็นสีน้ำเงิน

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

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

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

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

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

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

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

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

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

ดังที่แสดงในภาพด้านบนกฎข้อที่สองที่ใช้ในบทแนะนำนี้ (= (A2-B2) / A2> 50%) สูงกว่าในรายการและดังนั้นจึงมีความสำคัญมากกว่ากฎข้อแรก

เป็นผลให้สีพื้นหลังของเซลล์ B5 เปลี่ยนเป็นสีแดง

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

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

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

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

หากกฎการจัดรูปแบบเงื่อนไขแรกในตัวอย่างของเรา (= (A2-B2) / A2> 25%) จัดรูปแบบ ช่วง ของเซลล์ B2: B5 โดยมีเส้นขอบสีฟ้าแทนที่จะเป็นสีพื้นหลังสีน้ำเงินกฎการจัดรูปแบบตามเงื่อนไขทั้งสองจะไม่ขัดแย้งกัน ทั้งสองรูปแบบสามารถใช้โดยไม่รบกวนการทำงานอื่น ๆ

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

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

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

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

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