วิธีใช้ฟังก์ชัน 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! ⚠️ไม่ว่าจะเป็นแนวนอนหรือแนวตั้งก็ตาม