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


 

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

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

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

advanced-filter-01-add

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

      • ในตัวอย่าง ผมใส่ =”=c” จะเจอแต่ c และ C (ไม่เจอ cdef แล้ว)
        toomuch-result2 
  • ใส่เครื่องหมาย Wildcard ลงไปจริงๆ ได้ด้วย เช่น
    • ? หมายถึง ตัวอักษรอะไรก็ได้ 1 ตัว
    • * หมายถึง ตัวอักษรอะไรก็ได้ กี่ตัวก็ได้
    • ~ ตามด้วย ?, *, หรือ ~ คือ ให้มองเครื่องหมายพิเศษให้เป็นข้อความธรรมดา

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

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

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

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

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

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

ตัวอย่าง 1

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

ตัวอย่าง 2

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

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

advanced-filter-cri-2

เทคนิคการ 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 ของเราเอง
    advanced-filter-to-other-sheet