FILTER

TLDR สรุปสั้นๆ

FILTER เป็นสูตรที่ใช้กรองข้อมูลในช่วงที่ต้องการตามเงื่อนไขที่กำหนด

คำอธิบาย

FILTER ฟังก์ชันช่วยกรองข้อมูลในช่วงที่ต้องการตามเงื่อนไขที่ได้กำหนดไว้ ผลลัพธ์สามารถออกมาหลายค่าในลักษณะของ Array ได้เลย ซึ่งช่วยให้การจัดการและวิเคราะห์ข้อมูลง่ายขึ้น และสามารถใช้ในกรณีที่ต้องการ Lookup ตารางอ้างอิงที่มีค่าเดียวกันซ้ำกันหลายรายการได้ด้วย

มีครั้งแรกในเวอร์ชันไหน

2021

รูปแบบคำสั่ง (Syntax)

=FILTER(array, include, [if_empty])

Arguments

• array (Required – range)
  ช่วงของข้อมูลที่ต้องการกรอง เป็นช่วงข้อมูลที่ต้องการใช้ฟังก์ชันกรอง ซึ่งอาจเลือกเป็นตาราง หรือ เลือกแค่คอลัมน์เดียว แถวเดียว ก็ได้
• include (Required – Boolean)
  เงื่อนไขแบบ Boolean ที่จะใช้ในการกรอง ข้อมูลแบบ TRUE/FALSE ที่ใช้ระบุเงื่อนไขในการกรองข้อมูลให้ออกมาอย่างที่ต้องการ เช่น A1:A10=’Apple’ หมายถึงกรองข้อมูลในช่วงที่เงื่อนไขนี้เป็นจริง (ซึ่ง include นี้ไม่จำเป็นต้องอยู่ในช่วง array ของข้อมูลที่จำกรองก็ได้)
• [if_empty] (Optional – string)
  ค่าแน่นอนที่จะส่งกลับหากไม่มีข้อมูลใดที่ตรงตามเงื่อนไข เช่น “No results” ถ้าเงื่อนไขทั้งหมดไม่มี.

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

• Formula:

  =FILTER(B3:C8,F3:F8>6)

  Description: กรองข้อมูลจากช่วง B2:C8 ที่มี Qty ใน column F มีค่ามากกว่า 6
  Result: ได้ Array แบบนี้ {“TX0001″,45658;”TX0003″,45691;”TX0004”,45716}
  
  
• Formula:

  =FILTER(B3:C8,F3:F8>15)

  Description: กรองข้อมูลจากช่วง B2:C8 ที่มี Qty ใน column F มีค่ามากกว่า 15
  Result: #CALC! (ไม่มีรายการใดเลยที่ Qty มากกว่า 15)
  
• Formula:

  =FILTER(B3:C8,F3:F8>15,"No Result")

  Description: กรองข้อมูลจากช่วง B2:C8 ที่มี Qty ใน column F มีค่ามากกว่า 15 แต่ถ้าไม่มีให้ขึ้น No Result
  Result: “No Result” (ค่าเดียว)
  
• Formula:

  =FILTER(B3:C8,(F3:F8>5)*(E3:E8<>"Ra"),"No Result")

  Description: กรองข้อมูลจากช่วง B2:C8 ที่มี Qty ใน column F มีค่ามากกว่า 5 และ ที่มี SalesRep ใน column E ไม่ใช่ Ra แต่ถ้าไม่มีให้ขึ้น No Result
  
  ในส่วนของ include เป็น Array ที่เป็น TRUE/FALSE หรือ เป็นเลขก็ได้ (เลข 0 คือ FALSE นอกนั้นคือ TRUE) เราจึงสามารถใช้ Boolean Logic เช่นการ * แทน AND และใช้ + แทน OR ได้
  
  Result: ได้ Array แบบนี้ {“TX0003″,45691;”TX0005”,45718}
  
  
• Formula:

  =FILTER(TxData[SalesRep],TxData[Qty]>7,"No Result")

  Description: กรองข้อมูลจาก TxData[SalesRep] ที่มี Qty มีค่ามากกว่า 7 แต่ถ้าไม่มีให้ขึ้น No Result
  Result: {“Ra”;”Ple”}
  
  
• Formula:

  =TEXTJOIN("/",TRUE,SORT(FILTER(TxData[SalesRep],TxData[Qty]>7,"No Result")))

  Description: กรองข้อมูลจาก TxData[SalesRep] ที่มี Qty มีค่ามากกว่า 7 แต่ถ้าไม่มีให้ขึ้น No Result จากนั้นเรียงน้อยไปมาก แล้วจับทั้งหมดรวมกันเป็นข้อความเดียวโดยคั่นด้วย /
  Result: “Ple/Ra”
  
• Formula:

  =MEDIAN(FILTER(TxData[SalesAmt],TxData[Qty]>5,"No Result"))

  Description: กรองข้อมูลจาก TxData[SalesAmt] ที่มี Qty มีค่ามากกว่า 5 แต่ถ้าไม่มีให้ขึ้น No Result จากนั้นหาค่ามัธยฐาน (Median) ของ SalesAmt ที่คัดกรองมาได้
  Result: 1050 ซึ่งมาจาก Median ของ {1100;800;1500;1000}
  
• Formula:

  =CHOOSECOLS(FILTER(TxData,TxData[Qty]>7,"No Result"),1,4,6)

  Description: กรองข้อมูลจาก TxData ทั้งตาราง ที่มี Qty มีค่ามากกว่า 7 แต่ถ้าไม่มีให้ขึ้น No Result จากนั้นเลือกผลลัพธ์มาแค่คแลัมน์ที่ 1, 4, 6 โดยใช้ CHOOSECOLS
  Result: ได้ Array แบบนี้ {“TX0001″,”Ra”,1100;”TX0003″,”Ple”,800}
  
  

Tips & Tricks

• ฟังก์ชัน FILTER ทำงานแบบ Dynamic Array คือการจะแสดงผลในพื้นที่ว่างต่อเนื่องจากที่เราใส่สูตรแรกแล้ว ฟังก์ชันจะทำการเติมเต็มพื้นที่โดยอัตโนมัติ
• ในส่วนของ include เป็น Array ที่เป็น TRUE/FALSE หรือ เป็นเลขก็ได้ (เลข 0 คือ FALSE นอกนั้นคือ TRUE) เราจึงสามารถใช้ Boolean Logic เช่นการ * แทน AND และใช้ + แทน OR ได้
• เราสามารถใช้ Excel Table เพื่อป้องกันการเกิด #REF! error รวมถึงสามารถจัดการกับข้อมูลที่อาจจะเพิ่มหรือลดลงในอนาคตได้ดี

ข้อควรระวัง (Cautions)

• ฟังก์ชัน FILTER จำเป็นต้องมีข้อมูลที่อยู่ภายในช่วงที่กำหนด หากไม่มีจะส่งค่าความผิดพลาด #N/A
• ควรหลีกเลี่ยงการใช้ฟังก์ชัน Volatile ที่คำนวณตลอดเวลา เช่น NOW() เป็นส่วนหนึ่งของการรวมใน include เพราะจะทำให้การคำนวณทำงานช้าลง
• ข้อมูล include ที่ใช้งานควรจะเป็น Boolean หากใช้งานกับข้อมูลประเภทอื่นอาจเจอผลลัพธ์เป็น Error

ฟังก์ชันที่เกี่ยวข้อง

• SORT
• MATCH
• INDEX
• VLOOKUP

References

• Microsoft Documentation
• Ablebits Guide
• Exceljet Guide

ขอบคุณที่เข้ามาอ่านนะครับ ❤️