การใช้ Advanced Filter

บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่

---

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

องค์ประกอบสำคัญมีอยู่ 3 ส่วน คือ

1. ข้อมูลต้นฉบับ หรือ List Range 2.ข้อมูลที่เป็นเงื่อนไข หรือ Criteria Range, 3.ข้อมูลหลังถูก Filter

วิธีการใช้ Advanced Filter ให้ไปที่ [Data] –> Sort & Filter –> Advanced จากนั้นจะมีเมนูดังนี้

• Action สามารถเลือก ได้2แบบคือ
  • Filter the list, in-place คือให้ Filter ที Source Data เลย ซึ่งคล้ายกับการสั่ง Filter ปกติทีเราคุ้นเคย
  • Copy to another location ให้นำข้อมูลที่กรองแล้ว Copy ออกไปไว้ที่อื่น แต่มีข้อจำกัด คือ ต้องอยู่ใน Sheet เดียวกับตอนที่ Run คำสั่ง Advanced Filter
• List Range ให้เลือก Data Source ของเรา ซึ่งสามารถอยู่ที่ไหนก็ได้
• Criteria Rangeให้เลือกว่าเรามีเงื่อนไขการคัดกรองอย่างไร ซึ่งตรงนี้เราต้องไปสร้างตารางเงื่อนไขขึ้นมาก่อน ซึ่งจะอธิบายต่อไปว่าต้องสร้างอย่างไร
• Copy To : ให้เลือกว่าจะให้ Copy ข้อมูลที่ถูก Filter แล้วไปไว้ที่ไหน ซึ่งจะสามารถเลือกได้ต่อเมื่อ Action ทีเลือกคือ Copy to another location เท่านั้น
• Unique Records Onlyเอาไว้ Check หากต้องการกำจัดข้อมูลที่ซ้ำกันออกด้วย

วิธีการสร้างตารางเงื่อนไข (Criteria Range)

ตารางเงื่อนไขนั้น สามารถแบ่งได้เป็น 2 ส่วนหลักๆ คือ

หัวตารางขอคอลัมน์ที่ต้องการกำหนดเงื่อนไข : ให้ใส่ไว้ที่บรรทัดแรกของตารางเงื่อนไข
ตัวเงื่อนไขจริงๆ : ให้ใส่ไว้ทีบรรทัดถัดๆ ไป (มีได้หลายบรรทัด) แต่ผมจะขอพูดวิธีการกรอก Criteria ก่อนนะครับ

ค่าที่จะใส่ในช่องเงื่อนไข

ตัวสีแดง คือ สิ่งที่ผมพิมพ์ลงไปใน Formula Bar ของช่อง A2 นะครับ

• หากใส่เงื่อนไขเป็นตัวเลข เราสามารถใช้เครื่องหมายเปรียบเทียบใส่ลงไปในเงื่อนไขได้เลย
  • เช่น >400 , <=40 , =60 หรือ 60 เฉยๆ ก็ได้
    
  • ถ้าเป็นวันที่ก็ใส่ลงไปได้เลยเช่นกัน เช่น >1/3/2013
    
• หากใส่เงื่อนไขเป็นข้อความ การใส่แต่ละแบบจะให้ผลต่างกันดังนี้
  • ใส่ข้อความลงไปตรงๆ จะหมายถึงให้เลือกคำที่ขึ้นต้นด้วยคำที่เรากำหนด และไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่ เช่น ใส่ว่า bat จะเจอทั้งชื่อที่เป็นคำว่าbat BATและbatmanซึ่งเปรียบเสมือนการใส่เครื่องหมายWildcard *ตามหลังโดยอัตโนมัตินั่นเอง
    • ในตัวอย่าง ถ้าใส่ c จะเจอผลลัพธ์ทุกอย่างที่ขึ้นต้นด้วย c เช่น c C cdef
      
  • หากต้องการคำที่ตรงกับทีกำหนด และไม่สนตัวพิมพ์เล็กพิมพ์ใหญ่
    ต้องเขียนว่า =”=คำที่ต้องการ” ในช่องใส่สูตร (เมื่อมองจากที่แสดงออกมาใน Sheet จะเห็นแต่คำว่า =คำที่ต้องการ )
    เช่น ใส่ว่า=“=bat” จะเจอคำว่าbatและBATแต่ไม่เจอbatmanแล้ว
    • ในตัวอย่าง ผมใส่ =”=c” จะเจอแต่ c และ C (ไม่เจอ cdef แล้ว)
       

• ใส่เครื่องหมาย Wildcard ลงไปจริงๆ ได้ด้วย เช่น
  • ? หมายถึง ตัวอักษรอะไรก็ได้ 1 ตัว
  • * หมายถึง ตัวอักษรอะไรก็ได้ กี่ตัวก็ได้
  • ~ ตามด้วย ?, *, หรือ ~ คือ ให้มองเครื่องหมายพิเศษให้เป็นข้อความธรรมดา

เช่น ผมอยากหา สินค้าทุกตัวที่มีคำว่าหนัง ผมก็สามารถใส่ Criteria ได้ว่า *หนัง* นั่นเอง (หรือใส่แค่ *หนัง ก็ได้ เพราะ Advanced Filter จะทำงานเหมือนมี  * อยู่ท้ายโดยอัตโนมัติอยู่แล้ว)

แต่ถ้าผมอยากหา สินค้าทุกตัวที่อักษรที่สองเป็น อ.อ่าง แล้วห้ามมีอย่างอื่นต่อท้ายอีก ผมต้องใช้ความรู้จากทั้งสองเรื่องมาผสมกัน นั่นคือ ต้องให้ขึ้นเครื่องหมาย = ด้วย และมี Wildcard ด้วย ดังนั้นผมต้องใส่ว่า =”=?อ”
 

• หากใส่เงื่อนไขเป็นสูตร มีเทคนิคการใส่ที่ค่อนข้างTrickyดังนี้
  • สูตรต้องให้ผลลัพธ์เป็น TRUE หรือ FALSE เท่านั้น
  • ห้ามใส่หัว Label ใน Criteria ที่มีชื่อซ้ำกับ Data จริง (ปล่อยว่างเลยก็ได้ครับ)
  • ให้อ้างอิงคอลัมน์ที่ต้องการด้วย Cell บนสุดที่เป็นส่วนของ Data (ไม่ใช่ชื่อคอลัมน์) เช่น สมมติว่าช่อง Data บรรทัดบนสุดคือแถวที่ 6 ก็จะต้องเขียนในช่อง Criteria ว่า=E6>AVERAGE($E$6:$E$30) ที่ใช้ E6 เพราะแถวที่ 6 เป็นแถวแรกที่เป็นส่วนของ Data ครับ และ E คือคอลัมน์ที่ต้องการเช็คเงื่อนไข
    
  • หากต้องการคำที่เหมือนคำทีกำหนดเป๊ะๆ แบบต้องตรงทั้งตัวพิมพ์เล็ก ตัวพิมพ์ใหญ่ คุณสามารถใช้สูตร EXACT มาช่วย เพราะ EXACT จจะออกมา TRUE เมื่อทุกอย่างต้องเท่ากันเป๊ะ ทั้งตัวพิมพ์เล็กพิมพ์ใหญ่ด้วยนั่นเอง
    เช่น =EXACT(B6, “c”) จะเจอแต่คำว่า cเท่านั้น โดยไม่เจอคำว่า C อีกต่อไป
     

ตำแหน่งของการวางเงื่อนไข

อย่างที่บอกไปแล้วว่า ใน Criteria นั้นสามารถมีได้หลายคอลัมน์ หลายบรรทัด ซึ่งมีกฎทีสำคัญ คือ

• เงื่อนไขในบรรทัดเดียวกัน จะถูกเชื่อมกันด้วยตรรกะ AND
• เงื่อนไขคนละบรรทัด จะถูกเชื่อมด้วยตรรกะ OR

ตัวอย่าง 1

ผู้ซื้อเป็น นายa  หรือ สินค้าเป็นหนังสือ เป็น OR จึงต้องเขียนแยกกันคนละบรรรทัด

ตัวอย่าง 2

ผู้ซื้อเป็น นายa และ ผู้ขายเป็น sales ง และ ราคาต่อชิ้นต้องมากกว่า 300
หรือ
ผู้ซื้อเป็น นายd และ ราคาต่อชิ้นต้องมากกว่า 300 และ ราคาต่อชิ้นต้องน้อยกว่า 500

สังเกต! จะเห็นผมใส่ Column ราคาต่อชิ้นไว้ใน Criteria ได้มากกว่า 1 ครั้งนะครับ

เทคนิคการ Filter ข้อมูลไปไว้ที่ Sheet อื่น (ทำได้ด้วยเหรอ!!)

ปกติแล้วแล้ว ในช่อง  Copy To จะไม่ยอมให้เราไปเลือกตำแหน่งที่ Sheet อื่น ส่งผลให้หลายคนคิดว่าคงไม่มีทางใช้ Auto Filter ดึงข้อมูลไปยัง Sheet อื่นได้ แต่จริงๆ แล้วมีเทคนิคที่ทำให้สามารถทำได้

เทคนิคนี้มีประโยชน์มากในสถานการณ์ที่มีหัวคอลัมน์ (หัวตาราง) ปลายทางที่ถูกกำหนดมาก่อน โดยที่อาจมีจำนวนคอลัมน์หรือการเรียงลำดับไม่เหมือนกับข้อมูลต้นฉบับ เครื่องมือ Advanced Filter มันจะโปรยข้อมูลลงไปในคอลัมน์ที่ถูกต้องให้เองได้อย่างง่ายดาย โดยที่เราไม่ต้องมานั่ง copy paste เพื่อจัดเรียงข้อมูลทีละคอลัมน์เลย 

ระวัง! การใช้วิธีนี้ต้องระวังเรื่องการสะกดคำของหัวคอลัมน์ว่าจะต้องเหมือนกับต้นฉบับเป๊ะๆ ห้ามสะกดผิด รวมถึงห้ามมีหัวคอลัมน์ที่ไม่มีในต้นฉบับด้วย ไม่งั้นมันจะขึ้น Error ตามรูป

วิธีการทำ

• เตรียมหัวคอลัมน์ใน Sheet ปลายทางให้เรียบร้อย
• ใส่ Data อะไรก็ได้ไว้ที่ Sheet ปลายทางเป็น dummy ซัก 1 ช่อง เช่น ใส่ . หรือ xxx ก็ได้
• เตรียม Criteria Range ด้วย ถ้ามี เอาไว้ที่ Sheet ปลายทางก็ได้
• คลิ๊กใช้คำสั่ง Auto Filter จาก Sheet ปลายทาง ที่ต้องการให้แสดงผลลัพธ์หลังการ Filter
• เลือก List Range กลับไปยัง Sheet ต้นทางอยู่ เลือก Criteria Range ที่ต้องการ
• เลือก Copy To ให้เลือกหัวคอลัมน์ที่ Sheet ปลายทาง
• เมื่อกด Ok ข้อมูลต้นฉบับที่ตรงตามเงื่อนไขก็จะมาทับข้อมูล dummy ของเราเอง