บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขี
จะดีแค่ไหนหากเราสามารถกำหนดเงื่อนไขการใส่ Format ของ Cell ได้ตามที่ต้องการ เช่น
- กำหนดให้สีในช่องเปลี่ยนไปโดยขึ้นอยู่กับตัวเลขที่อยู่ข้างในว่ามากหรือน้อย
- กำหนดให้ตัวอักษรเปลี่ยนสีเป็นสีแดง เมื่อเลขในช่องน้อยกว่าค่าที่กำหนด
- กำหนดให้เลข Top 20% ของข้อมูลทั้งหมด โดดเด่นออกมา
- ทำให้ช่องที่มีคำว่า “แมว” เป็นสีส้ม
- อื่นๆ อีกมากมาย
นี่เป็นตัวอย่างความสามารถของสิ่งที่เรียกว่า Conditional Formatting (การกำหนดรูปแบบตามเงื่อนไข) ที่ Excel มีให้เราใช้ครับ ซึ่งอยู่ใน [Home] –> Styles –> Conditional Formatting โดยสิ่งที่มีให้เลือกเป็นมาตรฐานมีดังนี้
- Highlight Cells Rules : ช่องที่มีค่า มากกว่า/น้อยกว่า/มีคำที่กำหนด/หาค่าที่ซ้ำกัน
- Top/Bottom Rules : ช่องที่เป็น Top 10 (เลขเปลี่ยนได้) ในเรื่องต่างๆ
- Data Bars : ใส่กราฟแท่งเข้าไปเป็น Background ของช่องนั้นๆ ว่ามีค่ามากหรือน้อย
- Color Scales ใส่สี Background ของช่องนั้นๆ ตามค่ามากน้อย
- Icon Sets : ใส่เครื่องหมายต่างๆ เช่น ลูกศร ขึ้น/ลง ตามแต่ค่าที่อยู่ข้างในว่ามากหรือน้อย
- New Rule…. : อันนี้ไว้กำหนดเงื่อนไขแบบ Advance ซึ่งจะพูดถึงในบทหลังๆ ครับ
วิธีการใช้งาน Conditional Formatting
- เลือก Cell/Range ที่ต้องการให้รูปแบบเปลี่ยนไปตามเงื่อนไข (Conditional Formatting)
- เลือกเงื่อนไขที่ต้องการ เช่น ผมเลือก Highlight Cells Rules –> Greater Than…–>
- ใส่เลข 5000 ซึ่งหมายถึงจะใส่ Format ให้กับช่องที่มีค่ามากกว่า 5000
- ตรงนี้จะใส่เป็นสูตรก็ได้นะครับ เช่น =sum($D$2:$D$3) แปลว่าให้ จะใส่ Format ให้กับช่องที่มีค่ามากกว่า D2+D3 ซึ่งคือ 1000+2000 หรือ 3000 นั่นเอง
Tips : สังเกตว่า มีการใส่ $ เพื่อ Lock สูตรไว้ด้วย ไม่งั้นสูตรจะเลื่อน เดี๋ยวจะอธิบายต่อไปอีกทีว่าเมื่อไหร่ที่ต้องใส่ หรือไม่ต้องใส่เครื่องหมาย $
- เลือก Format ที่ต้องการว่าจะให้เปลี่ยน Format ไปเป็นรูปแบบใด ซึ่งจะมี Format เบื้องต้นให้เลือก ประมาณ 6 แบบ เช่น Light Red Fill with Dark Red Text ดังรูปข้างล่าง แต่ถ้าหากไม่พอใจ สามารถกด Custom Format… เพื่อกำหนดรูปแบบที่ต้องการได้
- หากต้องการใช้เงื่อนไขในขั้นตอนที่ 2 ที่ไม่มีให้เลือกในรูปแบบมาตรฐาน อ่านต่อในหัวข้อ การทำ Conditional Formatting ขั้นสูงได้เลย ครับ
การทำ Conditional Formatting ขั้นสูง
การเขียน Conditional Formatting ขั้นสูงนั้น จะเป็นการกำหนดเงื่อนไข ซึ่งใช้กรณีที่ไม่มีให้เลือกในเมนู Conditional Format แบบมาตรฐาน เช่น มีการอ้างอิงเงื่อนไขโดยไปดูที่ Cell อื่นแทนที่จะดูค่าจาก Cell ที่ต้องการจะทำการเปลี่ยน Format ตามแบบมาตรฐานที่เคยอธิบายใน Level ก่อนหน้านี้
โดยที่วิธีการกำหนดเงื่อนไขจะใช้การใส่สูตรด้วยตัวเอง มีขั้นตอนดังนี้
- เลือก Range ที่ต้องการเปลี่ยน Format
- กด Conditional Formatting => New Rule => Use a formula to determine which cells to format
- ในช่องให้ใส่สูตรที่ หากว่าเป็นจริง จึงจะใช้ Format ที่กำหนดไว้
- กด Format… เพื่อกำหนดรูปแบบที่ต้องการใช้
ซึ่งมีหลักการใส่สูตรที่สำคัญมากๆ คือ
ตรงนี้เป็นสิ่งที่คนส่วนใหญ่จะสับสนมากเวลาใช้ Conditional Formatting ในแบบ Formula ขอให้ตั้งใจให้ดีครับ หลักการคิดคือ ให้มองเหมือนการเขียนสูตรเป็นลักษณะ Relative Reference นั่นคือ
- ช่อง Active Cell (ซึ่งปกติจะเป็นช่อง Cell ซ้ายบนสุดของ Range ที่กำลังเลือก) คือ ช่องที่จะใช้สูตรที่เราระบุ ใน ระบุใน Conditional Formatting เป๊ะๆ เลย
- แต่ถ้าเป็นช่องอื่นใน Range ที่เลือกไว้ จะเหมือนกับการ Copy สูตรจาก Active Cell ไป Paste เลย (ซึ่งปกติแล้วเวลาคลิ๊กไปที่ Cell ต่างๆ สูตรใน Conditional Formatting จะถูกใส่เครื่องหมาย $ ไว้โดยอัตโนมัติ เพื่อไม่ให้สูตรเลื่อนไปไหน )
- ดังนั้น ถ้าคุณต้องการให้สูตรเลื่อน คุณอาจต้องปลด เครื่องหมาย $ ออกในทิศทางการเลื่อนที่จำเป็น คุณจึงต้องวางแผนให้ดีว่าจะเขียนสูตรอ้างอิงแบบใด จึงจะได้ผลลัพธ์ตามต้องการ เพราะบางที Cell Reference บางตัวก็ต้อง Lock ไว้ บางตัวก็ต้องเลื่อน (คล้ายกับการเขียนสูตรปกติแหละครับ)
เพื่อให้เห็นภาพชัดๆ มาดูตัวอย่างกันดีกว่าครับ
ตัวอย่าง
สมมติผมมีข้อมูลอยู่ตารางหนึ่ง คือช่อง A1:D7 ตามรูปข้างล่าง แล้วผมต้องการ Highlight ใส่พื้นหลังสีแดง ตัวอักษรขาว และตัวหนา ให้กับทุกบรรทัดที่ เพศเป็น F ผมจะต้องทำดังนี้
- เลือกบริเวณข้อมูลที่ต้องการใส่ Format ซึ่งในที่นี้คือ A1:D7 สังเกตว่า Active Cell คือ A1
- กด Conditional Formatting => New Rule => Use a formula to determine …
- ตอนใส่สูตร ต้องมองในมุมมองจากช่องที่เป็น Active Cell ก่อน (ในที่นี้คือ A1)
จึงต้องใส่สูตรว่า=$B1=”F” (อย่าลืมว่าสูตรต้องให้ผลเป็นTRUE จึงจะทำตามเงื่อนไข)- ที่ต้องใส่สูตรเป็นคอลัทน์ B แถวที่ 1 เพราะให้มองเงื่อนไขในช่อง B ในแถวตัวเอง ซึ่ง Active Cell ตอนแรกอยู่แถวที่ 1 การมองไปยังแถวตัวเองจึงต้องเลือกแถวที่ 1
- ที่ต้องใส่ $ หน้า B เพราะ ทุกช่อง ใน A1:D7 จะต้องมองเงื่อนไขไปที่คอลัมน์ B เหมือนกัน สรุปคือ สูตรต้องไม่เลื่อนคอลัมน์แต่ต้องเลื่อนบรรทัด จึงอ้างอิงโดย $B1
- เลือก Format ที่ต้องการ สรุปแล้วจะได้ว่า
เมื่อ OK จะได้ผลลัพธ์ตามต้องการ นั่นคือ
วิธีการทำให้มองไม่เห็นค่าที่ Error
บางครั้ง ผลลัพธ์ที่เป็น Error ก็เป็นสิ่งที่หลีกเลี่ยงไม่ได้ เช่น เมื่อใช้ฟังก์ชั่นพวก Lookup หรือพวกค้นหาข้อมูล แล้วหาข้อมูลไม่เจอแล้วจะขึ้น #N/A ในบทนี้คุณได้เรียนรู้เรื่องการใช้ Conditional Formatting ไปแล้ว ซึ่งคุณน่าจะพอเดาได้แล้วล่ะว่า Conditional Formatting สามารถช่วยเรื่องนี้ได้อย่างมาก แต่นอกเหนือจาก Conditional Formatting แล้วยังมีวิธีอื่นอีกมากมาย
ซึ่งผมจะขอสรุปวิธีทำให้มองไม่เห็น Error แบบเน้นๆ เลย ดังนี้
- ใช้สูตรตระกูล IF : เช่น สามารถใช้ฟังก์ชั่น IF ผสมกับ ISERROR หรือจะใช้ IFERROR เลยก็ได้ ถ้าเช็คแล้วว่า Error ก็ให้แสดงค่าเป็นอย่างอื่น เช่น 0 หรือ Blank (“”)
- ใช้ Conditional Formatting : เช่น อาจกำหนดให้ว่าถ้าค่าในช่องนั้นๆ เป็น Error ให้เปลี่ยนตัวอักษรเป็นสีขาว หรือสีเดียวกับพื้นหลัง เพื่อให้มองไม่เห็นตัวอักษร เป็นต้น
- กรณีค่า Error เกิดใน PivotTable : สามารถไปตั้งค่าใน PivotTable Options ให้แสดงค่า Error เป็น Blank ได้ ดังนี้
- กรณีที่ต้องการให้ Print แล้วไม่เห็นค่า Error : สามารถตั้งค่าให้แสดงค่า Error เป็นค่าอย่างอื่น เช่น Blank ได้เช่นกัน
