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

ก่อนหน้านี้เรามีการอธิบาย ถึงการจัดรูปแบบข้อมูลตามเงื่อนไข (Conditional Formatting) ด้วย Hightlight, Data Bar, Icon Set ในโปรแกรม Excel ไปแล้วถึง 3 ตอน

ในบทความนี้ ขอนำเสนอการจัดรูปแบบข้อมูลตามเงื่อนไข (Conditional Formatting) ด้วยสูตร (Formula) เพื่อให้คุณๆ มีวิธีในการจัดรูปแบบข้อมูลในโปรแกรม Excel ได้ตรงกับเงื่อนไขมากขึ้น  

ซึ่งการนำสูตรเข้ามาช่วยในการทำ Conditional Formatting นั้น คุณสามารถพบได้ใน Excel 2016, 2013 และ 2010 เท่านั้น


วิธีการ ใช้สูตรในการจัดรูปแบบตามเงื่อนไข  
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร 
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule  ดังรูปที่ 1

รูปที่ 1 แสดงเมนูการสร้าง Conditional Formatting ด้วยสูตร

4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. 
6. กำหนดรูปแบบของข้อความที่ปุ่ม Format 

เมื่อคุณรู้วิธีการสร้าง Conditional Formatting ด้วยสูตรแล้ว เรามาดูตัวอย่างการประยุกต์ใช้ ดังนี้ 

วิธีการการเปรียบเทียบค่า (ข้อความและตัวเลข) ใน Excel ด้วย Conditional Formatting โดยใช้สูตรต่างๆ
ปกติคุณสามารถใช้คุณลักษณะของการกำหนดรูปแบบข้อความหนึ่ง (Conditional Formatting แบบ Hightlight) ในการจัดการเซลล์ (ตัวอักษร) โดยมีเงื่อนไขว่าค่าในเซลล์นั้น ต้องมีค่ามากกว่า หรือ น้อยกว่า หรือ เท่ากับ ค่า (ตัวเลข) ที่คุณระบุ (The value you specify) ตัวอื่น เช่น 

รูปที่ 1  แสดงตัวอย่างผลลัพธ์ของการใช้เครื่องมือ Conditional Formatting 

จะเห็นว่าการ Hightlight ที่เกิดจากการใส่เงื่อนไขลงไป จะปรากฎที่เซลล์ที่เป็นเงื่อนไขเท่านั้น โดยที่คุณจะ ไม่ สามารถใช้คุณลักษณะ (Conditional Formatting แบบ Hightlight) นี้ กับกรณีการจัดรูปแบบเซลล์หนึ่ง กับค่าในเซลล์อื่น (A cell's value in another column) ได้ 

แล้วถ้าต้องการจัดรูปแบบ โดยเปรียบเทียบเซลล์หนึ่งกับค่าในเซลล์อื่นละ !!!!!

ต่อไปนี้เป็นตัวอย่างที่ใช้สูตรเข้ามาประยุกต์ใช้ เพื่อให้เหมาะสมตามเงื่อนไขต่าง ๆ มากขึ้น

ตัวอย่างที่ 1 การเขียนสูตร (Formula) ให้เงื่อนไขทำการเปรียบเทียบคอลัมน์หนึ่งกับค่าหนึ่ง ซึ่งในตัวอย่างนี้จะให้ทำ Conditional Formatting ที่ Product Column โดยให้ตรวจสอบเงื่อนไขว่าค่าใน In Stock Column มีค่ามากกว่า 0 หรือไม่ 
เมื่อทำตามโจทย์นี้ คุณต้องได้ผลเหมือนตาราง ดังรูปที่ 2

รูปที่ 2 แสดงตัวอย่างการใช้ Formula ใน Conditional Formatting

ในที่นี้ให้คุณประยุกต์ตามหัวข้อ "วิธีการใช้สูตรในการจัดการรูปแบบตามเงื่อนไข" ดังนี้
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร ซึ่งโนโจทย์นี้จะเลือก A13:A19
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule
4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. โดยในโจทย์นี้ให้ใส่สูตรนี้ =$B13>0 ..... อ่านมาถึงจุดนี้ ถ้าใคร งง ว่าต้อง $B13 ให้ไปศึกษาได้ที่ "การอ้างอิงเซลล์ใน Excel"

6. กำหนดรูปแบบของข้อความที่ปุ่ม Format
7. คลิกที่ปุ่ม OK ... 


วิธีการ Hightlight ทั้งแถว ใน Excel ด้วย Conditional Formatting โดยใช้สูตรต่างๆ

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

ตัวอย่างที่ 2 การเขียนสูตร (Formula) ให้โปรแกรมทำการเปรียบเทียบระหว่างคอลัมน์กับคอลัมน์ ซึ่งในตัวอย่างนี้จะให้ทำ Conditional Formatting โดยดูจากเงื่อนไขว่าค่าใน In Stock Column มีค่าน้อยกว่า Sold Column หรือไม่ ดังรูปที่ 3

รูปที่ 3 แสดงตารางที่ใช้ Formula ใน Conditional Formatting

ประยุกต์ตามหัวข้อ "วิธีการใช้สูตรในการจัดการรูปแบบตามเงื่อนไข" ดังนี้
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร ซึ่งโนโจทย์นี้จะเลือก A13:C19 (ในที่นี้กำหนดให้ทำ Conditional Formatting ทั้ง Row ทำให้ข้อต้องเลือกทั้งตาราง)
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule
4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. โดยในโจทย์นี้ให้ใส่สูตรนี้ =$B13<$C13 ..... ต้องการรู้ว่าการอ้างอิงเซลล์นี้คืออะไร อ่านที่ "การอ้างอิงเซลล์ใน Excel"
6. กำหนดรูปแบบของข้อความที่ปุ่ม Format
7. คลิกที่ปุ่ม OK ... 

จากทั้ง 2 ตัวอย่างข้างต้น การเขียนสูตรในการตรวจสอบได้แค่สูตรเดียว แต่ถ้าคุณต้องการตรวจสอบมากกว่า 1 สูตร จะทำอย่างไรละ ....

ตัวอย่างที่ 3 การเขียนสูตรโดยใช้ AND, OR เข้ามาช่วย ซึ่งในตัวอย่างนี้จะให้ทำ Conditional Formatting ที่ตาราง โดยมีเงื่อนไขว่า In Stock Column ต้องน้อยกว่า Sold Column และ Manufacturing Column ต้องผลิตใน Thailand หรือ Singapore เท่านั้น
เมื่อทำตามโจทย์นี้ คุณต้องได้ผลเหมือนตาราง ดังรูปที่ 4

รูปที่ 4 แสดงผลที่ใช้ Formula ใน Conditional Formatting

ประยุกต์ตามหัวข้อ "วิธีการใช้สูตรในการจัดการรูปแบบตามเงื่อนไข" ดังนี้
1. เลือกเซลล์ที่ต้องการจะทำ Conditional Formatting ด้วยสูตร ซึ่งโนโจทย์นี้จะเลือก A2:D8 (ในที่นี้กำหนดให้ทำ Conditional Formatting ทั้ง Row ทำให้ข้อต้องเลือกทั้งตาราง)
2. คลิกที่ Conditional Formatting ที่ Home Tab >> เลือก New Rule.. 
3. ปรากฏหน้าต่าง New Formatting Rule
4. ในส่วน Select a Rule Type: ให้เลือก Use a formula to determine which cells to format. 
5. ใส่สูตรที่ช่อง Format values where the formula is true. โดยในโจทย์นี้ให้ใส่สูตรนี้ =AND($B2<$C2,OR($D2="Thailand",$D2="Singapore")) ..... ถ้าสงสัยว่าทำไมต้องมี $หน้าชื่อเซลล์ ให้อ่านที่ "การอ้างอิงเซลล์ใน Excel"
6. กำหนดรูปแบบของข้อความที่ปุ่ม Format
7. คลิกที่ปุ่ม OK ...

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