วิธีใช้ฟังก์ชัน FILTER() ของ Excel

ภาพรวม

ฟังก์ชัน FILTER() ใช้ดึง (extract) เอาข้อมูลเฉพาะที่ตรงกับเงื่อนไขที่กำหนดกลับมาแบบไดนามิค (dynamic) หรือเปลี่ยนไปตามข้อมูลต้นฉบับอัตโนมัติ ดังนั้นถ้าข้อมูลหรือเงื่อนไขเปลี่ยนไป ฟังก์ชันนี้ก็จะให้ข้อมูลชุดใหม่ที่ตรงกับเงื่อนไขออกมา ซึ่งทำให้เรามีความยึดหยุ่นในการเรียกข้อมูลออกมาดูหรือใช้งานได้ โดยที่ไม่ต้องไปยุ่งกับต้นแหล่งข้อมูลฉบับเลย

หากต้องการไฟล์ตัวอย่างไปทดลองดู เปิดดูและดาวน์โหลด ได้เลยครับ

การใช้งานสูตร

=FILTER(array,included_criteria,[if_empty])
  • array – ให้ใส่ range หรือ array ของข้อมูลที่เราต้องการจะกรอง หรือข้อมูลต้นฉบับนั่นแหละ
  • included_criteria – ระบุเงื่อนไขที่ต้องการจะกรอง
  • if_empty – หากไม่พบข้อมูลจะให้แสดงเป็นอะไร (ไม่จำเป็นต้องใส่)

ตัวอย่างการใช้งาน

สมมติเรามีข้อมูลดังตารางด้านล่างนี้

ชื่อตำแหน่งแผนกเงินเดือน
สมชายผู้จัดการการตลาด50,000
อรอนงค์นักวิเคราะห์การเงิน40,000
สมหญิงผู้ช่วยผู้จัดการการตลาด45,000
สมศักดิ์วิศวกรวิศวกรรม35,000
ปวีณาพนักงานขายการขาย30,000
ธนพลนักวิเคราะห์การเงิน42,000
วราภรณ์ผู้ช่วยผู้จัดการการเงิน38,000
กิตติวิศวกรวิศวกรรม37,000
จิราพรพนักงานขายการขาย32,000
ปรีชาวิศวกรวิศวกรรม36,000

และเราอยากรู้ว่ามีใครที่เงินเดือนมากกว่า 40,000 บาท สูตรที่ใช้จะเป็นดังนี้

=FILTER(A2:D11,D2:D11>40000)

ซึ่งเงื่อนไขต่าง ๆ ที่เราใส่ไปก็คือ เราระบุตัวดำเนินการเปรียบเทียบ (Comparison operators) ของ Excel ลงไปเลย ไม่ว่าจะเป็น

Comparison operatorsคำอธิบาย
>มากกว่า
>=มากกว่าหรือเท่ากับ
<น้อยกว่า
<=น้อยกว่าหรือเท่ากับ
<>ไม่เท่ากับ
=เท่ากับ

กรณีไม่พบข้อมูล

หรือหากเราจะลองดูว่าไม่พบข้อมูลให้แสดงเป็น “Nothing matched!” โดยกำหนดเงื่อนไขเป็นคนที่เงินเดือนเกิน 50,000 ซึ่งในตัวอย่างไม่มี (มีแต่พอดีเป๊ะ)

=FILTER(A2:D11,D2:D11>50000,"Nothing matched!")

เงื่อนไขที่เป็นข้อความ

สำหรับเงื่อนไขที่เป็นข้อความควรจะใช้ = หากต้องการให้ตรงกับข้อความที่กำหนด หรือไม่ก็ <> ก็คือเอาเฉพาะที่ไม่ตรงกับเงื่อนไข อย่างเช่น หากเราจะดูเฉพาะที่เป็น “ผู้จัดการ” สูตรจะเป็นดังนี้

=FILTER(A2:D11,B2:B11="ผู้จัดการ","Nothing matched!")

หรือหากดูเฉพาะที่ “ไม่ใช่” ผู้จัดการ สูตรก็จะเป็น

=FILTER(A2:D11,B2:B11<>"ผู้จัดการ","Nothing matched!")

จาก 2 ตัวอย่างที่ผ่านมาจะสังเกตเห็นว่า ถ้าเงื่อนไขที่เป็นข้อความ ข้อมูลกับเงื่อนไขต้องตรงกันแบบเป๊ะ ๆ เลยนะครับ อย่าง “ผู้ช่วยผู้จัดการ” ก็คือไม่ใช่ “ผู้จัดการ”

หรือแม้แต่เว้นวรรค (space) ไม่เหมือนกันก็คือ ไม่เท่ากัน นะ 😁

เงื่อนไขที่ตรงกับข้อความแค่บางส่วน (substring match)

อาจมีบางครั้งเหมือนกัน ที่เราต้องการกำหนดเงื่อนไข แค่บางส่วน เช่น ทุกคนที่ชื่อตำแหน่งมีคำว่า “ผู้จัดการ” ⚠ แต่ว่า FIlTER() ไม่รองรับการใช้อักขระแทนอย่าง ? หรือ * ในเงื่อนไข

ดังนั้นในการใช้งาน จึงต้องใช้ร่วมกับฟังก์ชัน SEARCH() หรือ FIND() และ ISNUMBER() ร่วมกัน อย่างเช่น

=FILTER(A2:D11,ISNUMBER(SEARCH("ผู้จัดการ",B2:B11)),"Nothing matched!")

หรือในทางกลับกัน หากเราอยากดูเฉพาะพนักงาน ไม่เอาคนที่ตำแหน่งมีคำว่า “ผู้จัดการ” เราก็จะใส่ฟังก์ชัน NOT() หน้า ISNUMBER() ไป ดังนี้

=FILTER(A2:D11,NOT(ISNUMBER(SEARCH("ผู้จัดการ",B2:B11))),"Nothing matched!")

💡หากใช้เงื่อนไขเป็นภาษาอังกฤษและต้องการแยกความแตกต่างระหว่างตัวพิมพ์เล็กกับตัวพิมพ์ใหญ่ให้ใช้ฟังก์ชัน FIND() แทนนะครับ

กรองแบบหลายเงื่อนไข – และ (multiple critiria – AND)

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

=FILTER(A2:D11,(C2:C11="การขาย")*(D2:D11>30000),"Nothing matched!")

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

โดยวิธีแบบนี้ข้อมูลที่ถูกกรองออกมาจะต้องตรงกับเงื่อนไขทั้งหมดที่ใส่ไป ซึ่งในตัวอย่างก็คือ คนที่อยู่แผนก การขาย และมีเงินเดือนมากกว่า 30,000 เท่านั้น

กรองแบบหลายเงื่อนไข – หรือ (multiple critiria – OR)

หรือในบางครั้งเราอาจต้องการข้อมูลที่ตรงกับบางเงื่อนไขเท่านั้น เช่น อยากดูพนักงานที่ตำแหน่งมีคำว่า ผู้จัดการ หรือมีเงินเดือนมากกว่า 40,000

โดยการใช้งานก็จะคล้าย ๆ กับตัวอย่างก่อนหน้านี้ เพียงแต่เราเปลี่ยนจากเครื่องหมายดอกจันทร์เป็นเครื่องหมายบวกแทน

=FILTER(A2:D11,(ISNUMBER(SEARCH("ผู้จัดการ",B2:B11)))+(D2:D11>40000),"Nothing matched!")

จากผลลัพธ์จะเห็นว่ามี 1 คน (วราภรณ์) ที่เงินเดือนน้อยกว่า 40,000 ด้วย นั่นก็เพราะตำแหน่งเขามีคำว่าผู้จัดการ ซึ่งตรงกับเงื่อนไขแรก ส่วนอีกคนหนึ่ง (ธนพล) ในตำแหน่งก็ไม่ได้มีคำว่าผู้จัดการ แต่ข้อมูลเขาไปตรงกับเงื่อนไขที่สอง

ข้อควรทราบในการใช้งาน

  • ฟังก์ชัน FILTER() สามารถกรองข้อมูลจาก arrray หรือ range ทั้งแบบแนวตั้ง (vertical) และแนวนอน (horizontal) 💡
  • หากขนาด array ที่จะกรองกับเงื่อนไขใดเงื่อนไขหนึ่งไม่เท่ากัน ฟังก์ชันจะแสดงค่าเป็น #VALUE! ⚠️
  • หากเกิดข้อผิดพลาดในการคำนวณ (error) ในเงื่อนไข ฟังก์ชันก็จะแสดงค่าตาม error นั้น ๆ ⚠️
  • ถ้าในเซลล์ (cell) รอบ ๆ ที่เราใส่สูตรมีข้อมูลอยู่แล้ว ฟังก์ชันจะแสดงเป็น #SPILL! ⚠️ไม่ว่าจะเป็นแนวนอนหรือแนวตั้งก็ตาม

Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.