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