กรองข้อมูลด้วย FILTER

ฟังก์ชัน FILTER จะส่งคืนเฉพาะแถวของช่วงข้อมูลที่ตรงตามเงื่อนไขที่คุณกำหนด แทนที่จะต้องซ่อนแถวหรือคัดลอกข้อมูลที่ตรงกันด้วยตนเอง FILTER จะกระจายแถวที่ตรงกันออกมาโดยอัตโนมัติ

ฟังก์ชันนี้เป็นแบบไดนามิก เมื่อข้อมูลของคุณเปลี่ยนแปลง ผลลัพธ์ที่กรองจะแตกต่างตามทันที จึงเหมาะอย่างยิ่งสำหรับรายงานแบบสดที่แสดงข้อมูลที่ตรงกันในปัจจุบันเสมอ

FILTER รับอาร์กิวเมนต์ได้สูงสุดสามรายการ:

=FILTER(array, include, [if_empty])

• array คือช่วงข้อมูลที่คุณต้องการส่งคืน
• include คือการทดสอบเชิงตรรกะที่ให้ผลเป็น TRUE หรือ FALSE สำหรับแต่ละแถว
• if_empty คือค่าที่ไม่บังคับ ซึ่งจะแสดงเมื่อไม่มีข้อมูลใดตรงกัน

การทดสอบ include ต้องมีความสูงเท่ากับ array เพื่อให้แต่ละแถวได้รับค่า TRUE หรือ FALSE

สมมติว่า A2:A10 เก็บชื่อพนักงานขาย และ B2:B10 เก็บภูมิภาคของพนักงานเหล่านั้น หากต้องการแสดงเฉพาะชื่อในภูมิภาค East:

=FILTER(A2:A10, B2:B10="East")

การทดสอบ B2:B10="East" จะสร้างคอลัมน์ค่า TRUE และ FALSE FILTER จะเก็บแถวที่ผลลัพธ์เป็น TRUE แล้วกระจายผลลัพธ์ออกมา

array อาจมีความกว้างมากกว่าหนึ่งคอลัมน์ หากต้องการส่งคืนทั้งชื่อและยอดขายของภูมิภาค East ให้ระบุ array เป็นช่วงข้อมูลทั้งหมด:

=FILTER(A2:C10, B2:B10="East")

FILTER จะส่งคืนทุกคอลัมน์ของแถวที่ตรงกัน โดยกระจายออกมาเป็นตารางขนาดเล็ก การทดสอบ include ยังคงพิจารณาเฉพาะคอลัมน์เงื่อนไขเดียว

เงื่อนไขไม่ได้จำกัดอยู่แค่ข้อความ หากต้องการส่งคืนทุกแถวที่ยอดขายใน C2:C10 มากกว่า 500:

=FILTER(A2:C10, C2:C10>500)

ตัวดำเนินการเปรียบเทียบ เช่น มากกว่า น้อยกว่า และไม่เท่ากับ สามารถใช้ภายในอาร์กิวเมนต์ include ได้เช่นเดียวกับการทดสอบเชิงตรรกะทั่วไป

หากต้องการให้ตรงตามสองเงื่อนไขพร้อมกัน ให้คูณการทดสอบเข้าด้วยกัน การคูณทำหน้าที่เหมือน AND เนื่องจาก TRUE มีค่าเป็น 1 และ FALSE มีค่าเป็น 0 ดังนั้นแถวจะผ่านก็ต่อเมื่อทั้งสองเงื่อนไขมีค่าเป็น 1

=FILTER(A2:C10, (B2:B10="East")*(C2:C10>500))

สูตรนี้จะส่งคืนแถวของภูมิภาค East ที่มียอดขายมากกว่า 500 ด้วย ให้ใส่การทดสอบแต่ละรายการไว้ในวงเล็บ

หากต้องการให้ผ่านเมื่อเงื่อนไขใดเงื่อนไขหนึ่งเป็นจริง ให้บวกการทดสอบเข้าด้วยกัน การบวกทำหน้าที่เหมือน OR เพราะผลรวมจะมีค่าอย่างน้อย 1 เมื่อมีการทดสอบใดเป็น TRUE

=FILTER(A2:C10, (B2:B10="East")+(B2:B10="West"))

สูตรนี้จะส่งคืนแถวจากภูมิภาค East หรือ West แถวที่ได้คะแนน 1 หรือ 2 จะถูกเก็บไว้ ส่วนแถวที่ได้คะแนน 0 จะถูกตัดออก

หากไม่มีแถวใดตรงตามเงื่อนไข FILTER จะส่งคืนข้อผิดพลาด #CALC! เป็นค่าเริ่มต้น อาร์กิวเมนต์รายการที่สามซึ่งไม่บังคับจะป้องกันปัญหานี้ด้วยการแสดงข้อความที่อ่านเข้าใจง่ายแทน:

=FILTER(A2:C10, B2:B10="South", "No matches found")

หากไม่มีภูมิภาค South เซลล์จะแสดงข้อความแทนข้อผิดพลาด ควรเพิ่มค่า if_empty ในรายงานจริงเสมอ

สำหรับรายงานแบบโต้ตอบ ให้เปรียบเทียบกับเซลล์แทนการระบุค่าตายตัว หาก E1 เก็บภูมิภาคที่ผู้ใช้เลือก:

=FILTER(A2:C10, B2:B10=E1, "No matches")

เมื่อเปลี่ยน E1 เป็น West รายการที่กระจายออกมาจะรีเฟรชเป็นแถวของ West ทันที นี่คือพื้นฐานของแดชบอร์ดที่ควบคุมด้วยรายการแบบดรอปดาวน์

FILTER จะส่งคืนข้อมูลที่ตรงกันตามลำดับเดิม หากต้องการเรียงลำดับ ให้ซ้อน FILTER ไว้ภายใน SORT หากต้องการแสดงแถวของภูมิภาค East โดยเรียงยอดขายจากมากไปน้อย:

=SORT(FILTER(A2:C10, B2:B10="East"), 3, -1)

SORT จะเรียงตารางที่กระจายออกมาตามคอลัมน์ที่สาม โดย -1 หมายถึงเรียงจากมากไปน้อย การรวมฟังก์ชันการกระจายลักษณะนี้เป็นวิธีที่ใช้กันทั่วไปและมีประสิทธิภาพมาก

FILTER ใช้งานได้ทั้งใน Excel 365 และ Google Sheets โดยมีไวยากรณ์เกือบเหมือนกัน ใน Google Sheets คุณสามารถส่งหลายเงื่อนไขเป็นอาร์กิวเมนต์แยกกันได้ แทนที่จะคูณเงื่อนไขเข้าด้วยกัน:

=FILTER(A2:C10, B2:B10="East", C2:C10>500)

Sheets จะถือว่าอาร์กิวเมนต์เพิ่มเติมแต่ละรายการเป็นเงื่อนไข AND ใน Excel ให้ใช้วิธีคูณเพื่อทำ AND และบวกเพื่อทำ OR ภายในอาร์กิวเมนต์ include เดียว

คุณได้เรียนรู้การส่งคืนเฉพาะแถวที่ตรงกันด้วย FILTER:

• ไวยากรณ์คือ =FILTER(array, include, [if_empty])
• การทดสอบ include ต้องมีความสูงเท่ากับ array
• ให้ คูณการทดสอบเพื่อทำ AND และ บวกการทดสอบเพื่อทำ OR
• เพิ่มข้อความ if_empty เพื่อหลีกเลี่ยงข้อผิดพลาด #CALC!
• ซ้อน FILTER ไว้ใน SORT เพื่อเรียงลำดับผลลัพธ์

ถัดไป คุณจะเรียนรู้การแยกรายการค่าที่ไม่ซ้ำกันด้วย UNIQUE