Thep Excel
FILTERฟังก์ชันกรองข้อมูลแบบไดนามิก
FILTER กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return เป็น Spill Range ที่ขยายอัตโนมัติ เป็น Dynamic Array Function ที่เปลี่ยนวิธีการทำงานกับข้อมูลใน Excel รองรับเงื่อนไขหลายตัว (AND/OR) และสามารถใช้ร่วมกับ SORT UNIQUE XLOOKUP เพื่อสร้างรายงานที่อัปเดตอัตโนมัติ
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| array | Range/Array | ช่วงข้อมูลที่ต้องการกรอง (ทั้งตารางหรือบางคอลัมน์) | |
| include | Boolean Array | เงื่อนไข TRUE/FALSE ที่มีจำนวนแถวเท่ากับ array (TRUE = เอา, FALSE = ไม่เอา) | |
| [if_empty]ไม่บังคับ | Any | #CALC! | ค่าที่ return เมื่อไม่มีข้อมูลตรงเงื่อนไข (แนะนำใส่เสมอเพื่อป้องกัน error) |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
FILTER เป็น Dynamic Array Function ที่กรองข้อมูลจาก Array ตามเงื่อนไขที่กำหนด แล้ว return ผลลัพธ์เป็น Spill Range ที่ขยายอัตโนมัติ รองรับเงื่อนไขเดียวหรือหลายเงื่อนไข (AND ใช้ *, OR ใช้ +) สามารถใช้ร่วมกับ SORT, UNIQUE, INDEX เพื่อสร้างรายงานไดนามิกที่อัปเดตเองเมื่อข้อมูลเปลี่ยน ทำให้ไม่ต้องพึ่ง AutoFilter แบบ manual อีกต่อไป
How it works
สร้าง Dynamic Dashboard
กรองข้อมูลตาม dropdown ที่ผู้ใช้เลือก (Region, Product, Date) โดยไม่ต้องใช้ PivotTable
Live Search Box
ใช้ FILTER กับ SEARCH เพื่อสร้างช่องค้นหาที่กรองข้อมูลขณะพิมพ์แบบ real-time
รายการ Tasks ที่ยังไม่เสร็จ
กรอง Status = Open และ Due Date <= TODAY() เพื่อดูงานที่ต้องทำวันนี้
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | รหัสสินค้า | ชื่อสินค้า | สถานะ | ราคา |
| 2 | P001 | เมาส์ไร้สาย | ปิด | 590 |
| 3 | P002 | คีย์บอร์ด | เปิด | 1,290 |
| 4 | P003 | จอ 27 นิ้ว | ปิด | 8,900 |
| 5 | P004 | หูฟัง | เปิด | 2,450 |
| 6 | P005 | เว็บแคม | ปิด | 1,750 |
ทุกสูตรด้านล่างอ้างถึงตารางนี้ พิมพ์ตามได้เลย ผลลัพธ์จะตรงกับที่เขียนไว้
ตัวอย่างที่ 1: กรองเงื่อนไขเดียวFILTER(A2:D6, C2:C6="ปิด")
สูตรนี้เช็คทุกแถวในคอลัมน์สถานะ (C2:C6) ว่าเท่ากับ "ปิด" หรือเปล่า แล้วดึงเฉพาะแถวที่ตรงเงื่อนไขจากทั้งตาราง A2:D6 ออกมา
ในตารางมีสินค้าสถานะปิดอยู่ 3 รายการคือ P001 P003 และ P005 ผลลัพธ์เลยกระจายลงมา 3 แถวติดกัน โดยยังคงลำดับเดิมของตารางไว้
ข้อดีของ FILTER คือไม่ต้องไปซ่อนแถวเหมือน AutoFilter สูตรนี้สร้างชุดข้อมูลใหม่ขึ้นมาเลย พอข้อมูลต้นทางเปลี่ยน ผลลัพธ์ก็อัปเดตตามทันที
ตัวอย่างที่ 2: หลายเงื่อนไขแบบ ANDFILTER(A2:D6, (C2:C6="ปิด")*(D2:D6>1000))
ต้องการเงื่อนไข 2 อย่างพร้อมกันคือสถานะปิด และราคามากกว่า 1,000 บาท จึงเอาสองเงื่อนไขมาคูณกัน (*) ซึ่งทำหน้าที่เหมือน AND ใน Excel
P001 ตกไปเพราะราคาแค่ 590 ไม่ผ่านเงื่อนไขที่สอง เหลือแค่ P003 กับ P005 ที่ผ่านทั้งสองเงื่อนไข
เวลาผมเขียนเงื่อนไขซ้อนแบบนี้ ต้องครอบวงเล็บแต่ละเงื่อนไขให้ชัดเจนเสมอ ไม่งั้น Excel จะตีความลำดับการคำนวณผิดได้
ตัวอย่างที่ 3: หลายเงื่อนไขแบบ ORFILTER(A2:D6, (C2:C6="ปิด")+(D2:D6>2000))
คราวนี้อยากได้แถวที่เข้าเงื่อนไขข้อใดข้อหนึ่งก็พอ คือสถานะปิด หรือราคามากกว่า 2,000 บาท จึงใช้เครื่องหมายบวก (+) แทน OR
P001 P003 P005 เข้าเงื่อนไขจากสถานะปิด ส่วน P004 ราคา 2,450 เข้าเงื่อนไขที่สองแม้สถานะจะเปิดก็ตาม รวมได้ 4 แถวจาก 5 แถวในตาราง
สังเกตว่า P003 เข้าเงื่อนไขทั้งสองข้อ แต่ FILTER ก็ยังคืนมาแค่ครั้งเดียว ไม่ซ้ำ
ตัวอย่างที่ 4: จัดการ Empty ResultFILTER(D2:D6, D2:D6>9000, "ไม่พบข้อมูล")
ในตารางราคาสูงสุดคือ 8,900 บาท เพราะฉะนั้นเงื่อนไข "มากกว่า 9,000" ไม่มีแถวไหนผ่านเลยสักแถว
ถ้าไม่ใส่อาร์กิวเมนต์ที่สาม สูตรจะคืน #CALC! ออกมาแทน ซึ่งดูไม่เรียบร้อยเวลาโชว์ในรายงาน
พอใส่ if_empty เป็นข้อความ "ไม่พบข้อมูล" เข้าไป พอไม่มีผลลัพธ์ก็แสดงข้อความนี้แทนทันที ผมใส่อาร์กิวเมนต์นี้แทบทุกครั้งที่ FILTER ไปโผล่หน้ารายงานคนอื่นดู
ตัวอย่างที่ 5: FILTER + SORTSORT(FILTER(A2:D6, C2:C6="ปิด"), 4, -1)
ซ้อนสองฟังก์ชันเข้าด้วยกัน FILTER กรองเอาเฉพาะสถานะปิดออกมาก่อน (P001 P003 P005) แล้ว SORT เรียงผลลัพธ์นั้นตามคอลัมน์ที่ 4 คือราคา จากมากไปน้อย
ผลลัพธ์เลยได้ P003 ราคา 8,900 ขึ้นก่อน ตามด้วย P005 ราคา 1,750 แล้วปิดท้ายด้วย P001 ราคา 590
รูปแบบ SORT(FILTER(…)) แบบนี้ผมใช้บ่อยมากเวลาทำรายงาน Top N ตามเงื่อนไข เพราะเขียนสูตรเดียวจบทั้งกรองทั้งเรียง
ตัวอย่างที่ 6: Dynamic SearchFILTER(A2:D6, ISNUMBER(SEARCH("หู", B2:B6)), "ไม่พบ")
SEARCH หาคำว่า "หู" อยู่ในชื่อสินค้าแต่ละแถวหรือเปล่า ถ้าเจอจะคืนตำแหน่งตัวเลข ถ้าไม่เจอจะเป็น error ครอบด้วย ISNUMBER อีกชั้นเพื่อแปลงให้เหลือแค่ TRUE/FALSE ให้ FILTER ใช้ได้
ในตารางมีแค่ "หูฟัง" ของ P004 ที่มีคำว่า "หู" อยู่ในชื่อ ผลลัพธ์เลยได้แถวเดียว
เทคนิคนี้เอาไปทำช่องค้นหาแบบ live search ได้เลยครับ แค่เปลี่ยนคำว่า "หู" เป็นการอ้างอิงเซลล์ที่ผู้ใช้พิมพ์ค้นหา สูตรก็จะกรองผลลัพธ์ตามที่พิมพ์ทันที
FAQs
#CALC! error เกิดจากอะไร?+
เกิดเมื่อไม่มีข้อมูลตรงเงื่อนไขและไม่ได้ใส่ if_empty แก้โดยใส่ argument ที่ 3 เช่น =FILTER(data, condition, "No data")
#SPILL! error เกิดจากอะไร?+
เกิดเมื่อเซลล์ด้านล่าง/ขวาไม่ว่างทำให้ผลลัพธ์ Spill ไม่ได้ ลบข้อมูลที่ขวางหรือย้ายสูตรไปที่ว่าง
ใช้ AND/OR หลายเงื่อนไขอย่างไร?+
AND ใช้ * (คูณ) เช่น (A>10)*(B="Yes") ส่วน OR ใช้ + (บวก) เช่น (A="X")+(A="Y") ครอบด้วยวงเล็บแต่ละเงื่อนไข
FILTER กับ AutoFilter ต่างกันอย่างไร?+
FILTER เป็นสูตรที่ return ผลลัพธ์ใหม่แบบไดนามิก ไม่ซ่อนแถว ใช้กับสูตรอื่นได้ ส่วน AutoFilter ซ่อนแถวใน Table เดิม ต้องกดเลือกด้วยมือ
FILTER รองรับ Excel เวอร์ชันไหน?+
Microsoft 365, Excel 2021, Excel 2024, และ Excel for Web เท่านั้น ไม่รองรับ Excel 2019 หรือเก่ากว่า เป็น Dynamic Array Function
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ FILTER จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
CHOOSECOLS ใช้ดึงคอลัมน์ที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับคอลัมน์ สามารถดึงได้หลายคอลัมน์พร้อมกัน จัดลำดับใหม่ หรือทำซ้ำคอลัมน์เดิมได้ รองรับการนับคอลัมน์จากขวาสุดโดยใช้เลขลบ (เช่น -1 คือคอลัมน์ขวาสุด)
CHOOSEROWS ใช้ดึงแถวที่ต้องการจากตารางหรือ Array โดยระบุเลขลำดับแถว สามารถดึงได้หลายแถวพร้อมกัน จัดลำดับใหม่ หรือทำซ้ำแถวเดิมได้ รองรับการนับแถวจากล่างขึ้นบนโดยใช้เลขลบ (เช่น -1 คือแถวสุดท้าย)
DROP จะตัดข้อมูลออกตามจำนวนที่ระบุ ถ้าใส่เลขบวกจะตัดจากจุดเริ่มต้น (บน/ซ้าย) ทิ้งไป ถ้าใส่เลขลบจะตัดจากจุดสิ้นสุด (ล่าง/ขวา) ทิ้งไป ส่วนที่เหลือจะถูกนำมาแสดงผล
EXPAND ใช้ขยายขนาดของตารางข้อมูลให้ใหญ่ขึ้นตามจำนวนแถวหรือคอลัมน์ที่ระบุ หากตารางเดิมมีขนาดเล็กกว่า ส่วนที่เพิ่มขึ้นมาจะแสดงค่าเป็น #N/A (ค่าเริ่มต้น) หรือค่าที่เรากำหนดเองได้ (pad_with) มีประโยชน์มากในการปรับขนาดข้อมูลให้เท่ากันก่อนนำไปรวมด้วย VSTACK หรือ HSTACK
HSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวนอน (ต่อท้ายไปทางขวา) หากช่วงข้อมูลที่นำมารวมมีจำนวนแถวไม่เท่ากัน HSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ
IMAGE แสดงรูปภาพจาก URL ในเซลล์ Excel โดยรูปจะเป็นส่วนหนึ่งของเซลล์ สามารถกำหนดขนาด อัตราส่วน และ Alt Text ได้ เหมาะสำหรับสร้างรายการสินค้าหรือ Dashboard
INDEX คืนค่าหรือ reference ของเซลล์จากตำแหน่งที่ระบุใน range หรือ array โดยอ้างอิงจากหมายเลขแถวและคอลัมน์ที่เราระบุ มักถูกใช้ร่วมกับ MATCH เพื่อสร้าง INDEX-MATCH pattern ที่ยืดหยุ่นกว่า VLOOKUP เยอะ เพราะสามารถดึงข้อมูลจากทิศทางไหนก็ได้ และไม่พังเมื่อมีการแทรกหรือลบคอลัมน์
MATCH คืนเลขลำดับตำแหน่งของค่าที่ค้นหาในช่วงข้อมูลแถวเดียวหรือคอลัมน์เดียว รองรับการค้นหา 3 โหมด คือ Exact Match (0) ที่ไม่ต้องเรียงข้อมูล, Approximate Match แบบ Less Than or Equal (1) ที่ต้องเรียงจากน้อยไปมาก, และ Greater Than or Equal (-1) ที่ต้องเรียงจากมากไปน้อย รองรับ Wildcard (* และ ?) ในโหมด Exact Match และมักใช้คู่กับ INDEX เป็นรูปแบบ INDEX-MATCH ที่ยืดหยุ่นกว่า VLOOKUP
SORT เป็น Dynamic Array Function ที่เรียงลำดับข้อมูลจาก Array แล้ว return เป็น Spill Range ใหม่โดยไม่แก้ไขข้อมูลต้นฉบับ รองรับการเรียงตามคอลัมน์ที่ต้องการ (sort_index) ทั้งจากน้อยไปมาก (1) และมากไปน้อย (-1) รวมถึงเรียงแนวนอน (by_col=TRUE) ต่างจาก SORTBY ที่ใช้คอลัมน์ภายนอกเป็นเกณฑ์
SORTBY เรียงลำดับข้อมูลตาม Array อื่นที่กำหนด รองรับหลายระดับการเรียง (multi-level) และสามารถกำหนดลำดับเอง (custom sort order) ด้วย XMATCH ต่างจาก SORT ที่เรียงตามคอลัมน์ภายในตัวเอง SORTBY ใช้คอลัมน์ภายนอกเป็นเกณฑ์ได้
TAKE ช่วยตัดข้อมูลบางส่วนออกมาใช้งาน โดยระบุจำนวนที่ต้องการ ถ้าใส่เลขบวกจะดึงจากจุดเริ่มต้น (บน/ซ้าย) ถ้าใส่เลขลบจะดึงจากจุดสิ้นสุด (ล่าง/ขวา) คล้ายกับคำสั่ง LIMIT หรือ TOP/BOTTOM ใน Database
ฟังก์ชันใหม่ใน Excel 365 ที่ตัดแถวและคอลัมน์ว่างออกจากขอบของช่วงข้อมูล ช่วยให้การอ้างอิงช่วงกว้าง ๆ หรือทั้งคอลัมน์ปลอดภัยและไม่ดึงข้อมูลว่างเข้ามาปนกับสูตร dynamic array
UNIQUE เป็น Dynamic Array Function ที่คืนค่าที่ไม่ซ้ำจาก Array โดยสามารถตรวจซ้ำตามแถวหรือคอลัมน์ (by_col) และเลือกคืนเฉพาะค่าที่พบครั้งเดียว (exactly_once) ผลลัพธ์เป็น Spill Range ที่อัปเดตอัตโนมัติ ใช้ร่วมกับ SORT FILTER COUNTIF เพื่อสร้างรายงานไดนามิกและ dropdown ที่อัปเดตเอง
VSTACK เป็นฟังก์ชัน Dynamic Array ที่ใช้รวมข้อมูลจากหลายช่วงเข้าด้วยกันโดยนำมาเรียงต่อกันในแนวตั้ง (ต่อท้ายลงไปด้านล่าง) หากช่วงข้อมูลที่นำมารวมมีจำนวนคอลัมน์ไม่เท่ากัน VSTACK จะเติมค่า #N/A ในส่วนที่ขาดหายไปให้โดยอัตโนมัติ
ฟังก์ชัน XLOOKUP ใช้สำหรับค้นหาข้อมูลในตารางทั้งแนวตั้งและแนวนอน มีความยืดหยุ่นสูงกว่า VLOOKUP โดยสามารถค้นหาจากซ้ายไปขวา ขวาไปซ้าย และกำหนดค่าเริ่มต้นเมื่อไม่พบข้อมูลได้
COUNTBLANK นับจำนวนเซลล์ว่างในช่วงข้อมูล เหมาะสำหรับตรวจสอบความสมบูรณ์ของข้อมูล
COUNTIFS นับจำนวนเซลล์ที่ตรงกับหลายเงื่อนไขพร้อมกัน โดยใช้ AND logic หมายความว่าเงื่อนไขทุกข้อต้องเป็นจริงถึงจะนับ ซึ่งต่างจาก COUNTIF ที่มีได้แค่เงื่อนไขเดียว
.
ข้อดีคือรองรับได้ถึง 127 คู่ criteria_range/criteria ทำให้วิเคราะห์ข้อมูลซับซ้อนได้อย่างมีประสิทธิภาพ รองรับ wildcard characters (* แทนตัวอักษรกี่ตัวก็ได้, ? แทนตัวอักษรหนึ่งตัว) และ operators (>, =, <=, ) สำหรับเปรียบเทียบตัวเลขและวันที่
.
ที่ต้องระวังคือ criteria_range ทุกตัวต้องมีขนาดเท่ากันทุกประการ (rows × columns) มิฉะนั้นจะเกิด #VALUE! error ทันที COUNTIFS เป็นส่วนหนึ่งของ IFS family (SUMIFS, AVERAGEIFS, MAXIFS, MINIFS) ที่มี syntax คล้ายกัน เหมาะมากสำหรับสร้าง dashboard แบบ real-time และวิเคราะห์ KPI หลายมิติ
MODE.MULT ค้นหาค่าที่ซ้ำกันมากที่สุด (ฐานนิยม) ของข้อมูล และคืนค่าเป็น Array หากมีหลายค่าที่ซ้ำเท่าๆ กัน
PIVOTBY เป็นฟังก์ชัน Excel 365 ที่สร้าง Pivot Table แบบไดนามิก โดยกำหนด Row Fields, Column Fields และ Values ผ่านสูตร พร้อมตั้งค่าการแสดงผลรวมและการเรียงลำดับได้ทันที
ฟังก์ชัน Microsoft 365 ที่ตรวจจับภาษาของข้อความในเซลล์ แล้วคืนค่าเป็นรหัสภาษา (เช่น "th", "en", "es") ใช้คู่กับ TRANSLATE เพื่อแปลภาษาแบบอัตโนมัติ
TEXTJOIN รวมข้อความจากหลายเซลล์หรือทั้งช่วงเป็นข้อความเดียว โดยกำหนดตัวคั่นเองได้และสั่งข้ามเซลล์ว่างได้ในคำสั่งเดียว ต่างจาก CONCATENATE ที่ต้องพิมพ์ตัวคั่นและเชื่อมทีละเซลล์เอง งานที่ใช้บ่อยคือรวมที่อยู่หลายบรรทัด สร้างลิสต์คั่นด้วยคอมม่าไว้ส่งออก หรือรวมผลลัพธ์จาก FILTER ให้ออกมาเป็นข้อความเดียว
AGGREGATE เป็นฟังก์ชันสารพัดประโยชน์ที่รวม 19 ฟังก์ชันต่างๆ (SUM, AVERAGE, COUNT, MAX, MIN ฯลฯ) แต่มีความสามารถพิเศษคือ สามารถข้าม Error ได้ และข้ามแถวที่ถูกซ่อน (Hidden Rows) ได้ เหมาะสำหรับ Dashboard, รายงาน หรือข้อมูลที่อาจมีช่องว่างและข้อมูลผิดพลาด
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่