วิธีใช้ฟังก์ชัน Excel FILTER

Office 365 นำเสนอคุณสมบัติที่ยอดเยี่ยม เช่น XLOOKUP, SORT และ FILTER

เมื่อกรองข้อมูลใน Excel ในโลกก่อน Office 365 ส่วนใหญ่เราใช้ตัวกรองในตัวของ Excel หรือตัวกรองขั้นสูงส่วนใหญ่หรือสูตร SUMPRODUCT ที่ซับซ้อนซึ่งมักจะเป็นวิธีแก้ปัญหาที่ซับซ้อน หากคุณต้องกรองส่วนหนึ่งของชุดข้อมูล

แต่ด้วยฟีเจอร์ FILTER ใหม่ ทำให้ตอนนี้ง่ายต่อการกรองส่วนของชุดข้อมูลตามเงื่อนไขอย่างรวดเร็ว

ในบทช่วยสอนนี้ ฉันจะแสดงให้คุณเห็นว่าฟีเจอร์ FILTER ใหม่นั้นยอดเยี่ยมเพียงใด และสิ่งที่มีประโยชน์บางอย่างที่คุณสามารถทำได้

แต่ก่อนจะเข้าสู่ตัวอย่าง มาดูไวยากรณ์ของฟังก์ชัน FILTER กันก่อน

หากคุณต้องการรับฟีเจอร์ใหม่เหล่านี้ใน Excel คุณสามารถอัปเกรดเป็น Office 365(เข้าร่วมโปรแกรมภายในเพื่อเข้าถึงคุณสมบัติ/สูตรทั้งหมด)

ฟังก์ชันตัวกรอง Excel – ไวยากรณ์

ต่อไปนี้เป็นไวยากรณ์ของฟังก์ชัน FILTER:

=FILTER(อาร์เรย์,รวม,[if_empty])

• แถว - นี่คือช่วงของเซลล์ที่คุณมีข้อมูลและต้องการกรองข้อมูลบางส่วนจากข้อมูลนั้น
• ประกอบด้วย - เป็นเงื่อนไขที่บอกฟังก์ชันที่บันทึกเพื่อกรอง
• [ถ้า_ว่างเปล่า] – นี่เป็นพารามิเตอร์ทางเลือกที่คุณสามารถระบุสิ่งที่จะส่งกลับหากฟังก์ชัน FILTER ไม่พบผลลัพธ์โดยค่าเริ่มต้น (เมื่อไม่ได้ระบุ) จะส่งกลับ #CALC!ความผิดพลาด

ตอนนี้เรามาดูตัวอย่างที่น่าทึ่งของฟังก์ชันตัวกรองและสิ่งที่เคยทำได้ซึ่งเคยซับซ้อนมากเมื่อไม่มีตัวกรองนี้

ตัวอย่างที่ 1: กรองข้อมูลตามเงื่อนไขเดียว (ภูมิภาค)

สมมติว่าคุณมีชุดข้อมูลดังตัวอย่างด้านล่าง และคุณต้องการกรองระเบียนทั้งหมดในสหรัฐอเมริกาเท่านั้น

นี่คือสูตร FILTER ที่ทำสิ่งนี้:

=ตัวกรอง($A$2:$C$11,$B$2:$B$11="US")

สูตรข้างต้นใช้ชุดข้อมูลเป็นอาร์เรย์ และเงื่อนไขคือ $B$2:$B$11=”US”

เงื่อนไขนี้จะทำให้ฟังก์ชัน FILTER ตรวจสอบทุกเซลล์ในคอลัมน์ B (เซลล์ที่มีช่วง) และกรองเฉพาะระเบียนที่ตรงกับเงื่อนไขนี้

นอกจากนี้ ในตัวอย่างนี้ ฉันใส่ข้อมูลต้นฉบับและข้อมูลที่กรองแล้วลงในแผ่นงานเดียวกัน แต่คุณยังสามารถใส่ไว้ในแผ่นงานแยกกัน หรือแม้แต่สมุดงาน

ฟังก์ชันตัวกรองส่งกลับอาร์เรย์ของผลลัพธ์แบบไดนามิก (หมายความว่าแทนที่จะส่งคืนค่า จะคืนค่าอาร์เรย์ที่ล้นไปยังเซลล์อื่น)

สำหรับสิ่งนี้ คุณต้องมีพื้นที่ที่ผลลัพธ์ว่างเปล่ามีบางอย่างอยู่แล้วในเซลล์ใดๆ ในช่วง (E2:G5 ในตัวอย่างนี้) และฟังก์ชันจะให้ข้อผิดพลาด #SPILL แก่คุณ

นอกจากนี้ เนื่องจากนี่คืออาร์เรย์แบบไดนามิก คุณจึงไม่สามารถเปลี่ยนแปลงผลลัพธ์บางส่วนได้คุณสามารถลบช่วงทั้งหมดด้วยผลลัพธ์หรือเซลล์ E2 (ที่ป้อนสูตร)ทั้งสองนี้จะลบอาร์เรย์ผลลัพธ์ทั้งหมดแต่คุณไม่สามารถเปลี่ยนเซลล์ใด ๆ (หรือลบออก)

ในสูตรด้านบน ฉันฮาร์ดโค้ดค่าช่วงแล้ว แต่คุณสามารถใส่ค่าช่วงลงในเซลล์และอ้างอิงเซลล์นั้นด้วยค่าช่วงได้

ตัวอย่างเช่น ในตัวอย่างด้านล่าง ฉันมีค่าช่วงในเซลล์ I2 แล้วอ้างอิงในสูตร:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

ทำให้สูตรมีประโยชน์มากขึ้น ตอนนี้คุณเพียงแค่ต้องเปลี่ยนค่าช่วงในเซลล์ I2 แล้วตัวกรองจะเปลี่ยนโดยอัตโนมัติ

คุณยังสามารถมีดรอปดาวน์ในเซลล์ I2 ซึ่งคุณสามารถเลือกได้และจะอัปเดตข้อมูลที่กรองทันที

ตัวอย่างที่ 2: กรองข้อมูลตามเกณฑ์เดียว (มากกว่าหรือน้อยกว่า)

คุณยังสามารถใช้ตัวดำเนินการเปรียบเทียบในฟังก์ชันตัวกรองและแยกระเบียนทั้งหมดที่มากกว่าหรือน้อยกว่าค่าที่กำหนดได้

ตัวอย่างเช่น สมมติว่าคุณมีชุดข้อมูลที่แสดงด้านล่าง และคุณต้องการกรองระเบียนทั้งหมดที่มียอดขายมากกว่า 10000 รายการ

สูตรต่อไปนี้สามารถทำได้:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

อาร์กิวเมนต์อาร์เรย์อ้างอิงถึงชุดข้อมูลทั้งหมด ในกรณีนี้เงื่อนไข ($C$2:$C$11>10000)

สูตรจะตรวจสอบแต่ละระเบียนเพื่อหาค่าในคอลัมน์ Cถ้าค่ามากกว่า 10000 ค่านั้นจะถูกกรอง มิฉะนั้น จะถูกละเว้น

ถ้าคุณต้องการรับระเบียนทั้งหมดที่น้อยกว่า 10000 คุณสามารถใช้สูตรต่อไปนี้:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

คุณยังสามารถสร้างสรรค์ผลงานได้มากขึ้นด้วยสูตร FILTERตัวอย่างเช่น ถ้าคุณต้องการกรองเรกคอร์ดสามอันดับแรกตามยอดขาย คุณสามารถใช้สูตรต่อไปนี้:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

สูตรข้างต้นใช้ฟังก์ชัน LARGE เพื่อรับค่าที่ใหญ่เป็นอันดับสามในชุดข้อมูลจากนั้นใช้ค่านั้นในเงื่อนไขของฟังก์ชัน FILTER เพื่อรับระเบียนทั้งหมดที่มียอดขายมากกว่าหรือเท่ากับค่าที่ใหญ่เป็นอันดับสาม

ตัวอย่างที่ 3: การกรองข้อมูลโดยใช้หลายเงื่อนไข (AND)

สมมติว่าคุณมีชุดข้อมูลต่อไปนี้ และคุณต้องการกรองระเบียนทั้งหมดในสหรัฐอเมริกาด้วยมูลค่าการขายที่มากกว่า 10000

นี่เป็นเงื่อนไข "และ" คุณต้องตรวจสอบสองสิ่ง - ภูมิภาคต้องอยู่ในสหรัฐอเมริกา และยอดขายต้องมากกว่า 10000 รายการหากตรงตามเงื่อนไขเดียวเท่านั้น ผลลัพธ์ไม่ควรถูกกรอง

นี่คือสูตรตัวกรองที่จะกรองระเบียนที่มีสหรัฐอเมริกาเป็นภูมิภาคและมียอดขายมากกว่า 10000 รายการ:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

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

หากไม่มีบันทึกที่ตรงกัน ฟังก์ชันจะคืนค่า #CALC!ความผิดพลาด.

หากคุณต้องการส่งคืนสิ่งที่มีความหมาย (ไม่ใช่ข้อผิดพลาด) คุณสามารถใช้สูตรดังนี้:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

ที่นี่ ฉันใช้ "ไม่พบ" เป็นพารามิเตอร์ที่สาม ซึ่งใช้เมื่อไม่พบบันทึกที่ตรงกัน

ตัวอย่างที่ 4: การกรองข้อมูลโดยใช้หลายเกณฑ์ (OR)

คุณยังสามารถแก้ไขพารามิเตอร์ "มี" ในฟังก์ชัน FILTER เพื่อตรวจสอบเงื่อนไข OR (โดยที่เงื่อนไขใดๆ อาจเป็นจริงได้)

ตัวอย่างเช่น สมมติว่าคุณมีชุดข้อมูลที่แสดงด้านล่าง และคุณต้องการกรองระเบียนที่ประเทศคือสหรัฐอเมริกาหรือแคนาดา

นี่คือสูตรในการทำสิ่งนี้:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

โปรดทราบว่าในสูตรข้างต้น ฉันแค่เพิ่มสองเงื่อนไขโดยใช้ตัวดำเนินการบวกเนื่องจากแต่ละเงื่อนไขเหล่านี้คืนค่าอาร์เรย์ของ TRUE และ FALSE ฉันจึงสามารถเพิ่มอาร์เรย์รวมที่จะเป็น TRUE ได้หากตรงตามเงื่อนไขอย่างใดอย่างหนึ่ง

อีกตัวอย่างหนึ่งอาจเป็นเมื่อคุณต้องการกรองระเบียนทั้งหมดที่ประเทศคือสหรัฐอเมริกา หรือมูลค่าการขายมากกว่า 10000

สูตรต่อไปนี้จะทำสิ่งนี้:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

หมายเหตุ: เมื่อใช้เงื่อนไข AND ในฟังก์ชัน FILTER ให้ใช้ตัวดำเนินการการคูณ (*) และเมื่อใช้เงื่อนไข OR ให้ใช้ตัวดำเนินการบวก (+)

ตัวอย่างที่ 5: กรองข้อมูลสำหรับบันทึกที่สูงกว่า/ต่ำกว่าค่าเฉลี่ย

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

ตัวอย่างเช่น สมมติว่าคุณมีชุดข้อมูลที่แสดงด้านล่าง และคุณต้องการกรองระเบียนทั้งหมดด้วยมูลค่าการขายที่สูงกว่าค่าเฉลี่ย

คุณสามารถทำได้ด้วยสูตรต่อไปนี้:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

อีกครั้งสำหรับค่าเฉลี่ยที่ต่ำกว่า คุณสามารถใช้สูตรต่อไปนี้:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

ตัวอย่างที่ 6: กรองเฉพาะระเบียนคู่ (หรือระเบียนคี่)

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

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

สมมติว่าคุณมีชุดข้อมูลดังนี้ และฉันเพียงต้องการแยกระเบียนออกจากชุดข้อมูลนี้เท่านั้น

นี่คือสูตรในการทำสิ่งนี้:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

สูตรข้างต้นใช้ฟังก์ชัน MOD เพื่อตรวจสอบหมายเลขแถว (กำหนดโดยฟังก์ชัน ROW) ของแต่ละระเบียน

สูตร MOD(ROW(A2:A11)-1,2)=0 คืนค่า TRUE เมื่อหมายเลขแถวเป็นคู่ และ FALSE เมื่อเป็นเลขคี่โปรดทราบว่าฉันได้ลบ 2 ออกจากส่วน ROW(A11:A1) เนื่องจากระเบียนแรกอยู่ในแถวที่สอง ซึ่งจะปรับหมายเลขแถวให้ถือว่าแถวที่สองเป็นระเบียนแรก

ในทำนองเดียวกัน คุณสามารถกรองระเบียนคี่ทั้งหมดด้วยสูตรต่อไปนี้:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

ตัวอย่างที่ 7: การเรียงลำดับข้อมูลที่กรองโดยใช้สูตร

การใช้ฟังก์ชัน FILTER ร่วมกับฟังก์ชันอื่นๆ ช่วยให้เราทำสิ่งต่างๆ ได้มากขึ้น

ตัวอย่างเช่น ถ้าคุณใช้ฟังก์ชัน FILTER เพื่อกรองชุดข้อมูล คุณสามารถใช้ฟังก์ชัน SORT เพื่อรับผลลัพธ์ที่เรียงลำดับได้

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

สูตรต่อไปนี้จะทำสิ่งนี้:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

ฟังก์ชันด้านบนใช้ฟังก์ชัน FILTER เพื่อรับข้อมูลในคอลัมน์ C ที่มียอดขายมากกว่า 10000จากนั้นใช้อาร์เรย์ที่ส่งคืนโดยฟังก์ชัน FILTER ในฟังก์ชัน SORT เพื่อจัดเรียงข้อมูลตามยอดขาย

พารามิเตอร์ที่สองในฟังก์ชัน SORT คือ 3 ซึ่งจะเรียงลำดับตามคอลัมน์ที่สามพารามิเตอร์ที่สี่คือ -1 ซึ่งเรียงลำดับข้อมูลจากมากไปหาน้อย

ดังนั้นนี่คือ 7 ตัวอย่างการใช้ฟังก์ชัน FILTER ใน Excel

หวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์.