Dynamic Arrays — FILTER, SORT, UNIQUE, SEQUENCE
1. เมื่อสูตรหนึ่งตอบได้หลายค่า
บทความก่อนหน้าเราจัดการกับวันที่กันไปแล้ว คราวนี้ผมจะพาคุณมาถึงจุดพลิกโฉมของ Excel ในยุค Microsoft 365 ครับ นั่นคือ Dynamic Arrays หรือที่เรียกกันว่า สูตรแบบไดนามิก
เคยไหมครับ เวลากรองข้อมูลก็ต้องเปิด Filter ทีละคอลัมน์ เวลาหาค่าที่ไม่ซ้ำก็ต้องคัดด้วยมือ หรือเวลาเรียงข้อมูลก็ต้องกด Sort แล้วเสี่ยงทำข้อมูลต้นฉบับเละไปกับมือ ถ้าฟังดูคุ้น นี่แหละคือปัญหาที่ Dynamic Arrays เกิดมาแก้
ก่อนหน้านี้ถ้าเราอยากให้สูตรตอบหลายค่าต้องกด Ctrl+Shift+Enter ให้เป็น Array Formula และผลลัพธ์ต้องวางไว้ให้พอดีล่วงหน้า ถ้าเผลอลากเยอะไปหรือหย่อนไปก็พังทิ้ง หรือถ้าเผลอไปกดตรงกลางก็แก้ยาก ยุคของ Dynamic Arrays ต่างออกไปสิ้นเชิง เพราะคุณแค่เขียนสูตรหนึ่งครั้ง แล้ว Excel จัดการเรื่องขนาดและตำแหน่งให้เองทั้งหมด
หลักการมันง่ายมาก คือสูตรกลุ่มนี้ตอบ ได้หลายค่าพร้อมกัน และค่าที่ตอบมานั้น ไหลลงมา (spill) ครอบคลุมหลายเซลล์อัตโนมัติ โดยคุณไม่ต้องลากสูตรลงมาเองแม้แต่เซลล์เดียว แถมเมื่อข้อมูลต้นทางเปลี่ยน ผลลัพธ์ก็อัปเดตตามทันทีแบบเรียลไทม์
ชุดฟังก์ชันที่เราจะเล่นกันวันนี้มี 4 ตัวหลักครับ
- FILTER กรองข้อมูลตามเงื่อนไข อัตโนมัติ
- SORT / SORTBY เรียงลำดับข้อมูล
- UNIQUE ดึงค่าที่ไม่ซ้ำออกมา
- SEQUENCE สร้างลำดับตัวเลขหรือวันที่ต่อเนื่อง
การปรับตัวครั้งนี้อาจดูใหม่สักหน่อย โดยเฉพาะคนที่คุ้นเคยกับสูตรแบบเดิมที่ต้องลากยาวๆ แต่พอเริ่มจับทางได้แล้ว จะรู้สึกว่ามันง่ายและเร็วกว่าเยอะ เพราะเราไม่ต้องลุ้นว่าลากสูตรพลาดหรือเปล่า ไม่ต้องกังวลว่าข้อมูลต้นทางเปลี่ยนแล้วผลลัพธ์ตามไม่ทัน ระบบจัดการทุกอย่างให้เราเองตั้งแต่ต้นจนจบ รวมถึงการคำนวณใหม่เมื่อข้อมูลเปลี่ยน จึงบอกได้เต็มปากว่าเป็นเทคนิคที่คนทำงานควรมีติดมือไว้จริงๆ
เปิดไฟล์ตัวอย่าง แล้วทำไปพร้อมกันนะครับ ผมจัดไว้ 3 ชีท เริ่มจาก FILTER SORT กรองและเรียงยอดขาย ข้ามไป UNIQUE หาลูกค้าที่ไม่ซ้ำ และปิดท้ายที่ SEQUENCE สร้างลำดับและเดือนทั้งปี ให้คุณเห็นทั้งการกรอง การรวมเงื่อนไข และการสร้างชุดข้อมูลอัตโนมัติ
ลองนึกภาพตามจริงดูนะครับ แทนที่จะเปิดตารางขายแล้วมานั่งกรองกดนู่นกดนี่ หรือคัดชื่อลูกค้าที่ซ้ำกันทีละบรรทัด เราแค่เขียนสูตรเดียวแล้วปล่อยให้ Excel จัดการทั้งชุดข้อมูล ซึ่งอีกไม่กี่นาทีข้างหน้าคุณจะเริ่มเห็นภาพนี้ชัดขึ้นเรื่อยๆ
💡 เคล็ดลับ: Dynamic Arrays ใช้ได้เต็มรูปแบบบน Microsoft 365 และ Excel 2021 ถ้าใช้ Excel เวอร์ชันเก่า กด Ctrl+Shift+Enter แบบ Array Formula เก่าได้บางส่วน แต่ฟังก์ชัน FILTER, SORT, UNIQUE จะยังไม่มีครับ
📌 ข้อควรจำ: ตัวอย่างในไฟล์นี้เป็นข้อมูลปี ค.ศ. 2026 และใช้สูตรที่ตรงกับไฟล์จริง เปิดไฟล์แล้วลองปรับตัวเลขเงื่อนไขดูได้เลยครับ
2. FILTER — กรองข้อมูลแบบอัตโนมัติ
เริ่มที่ชีท FILTER SORT ครับ ครึ่งบนคือตารางขายสินค้าของร้านค้าชุมชน มีคอลัมน์ สินค้า, หมวด, ยอดขาย และ ผู้ขาย สมมติว่าคุณอยากเห็นเฉพาะสินค้าที่มียอดขายเกิน 5,000 บาท
วิธีเดิมเราก็ต้องเปิด Filter แล้วเลือกทีละค่า แต่ด้วย FILTER แค่สูตรเดียวจบ
=FILTER(A2:D9,C2:C9>5000)พารามิเตอร์ตัวแรกคือช่วงข้อมูลที่อยากให้แสดงทั้งแถว ตัวที่สองคือเงื่อนไขที่อิงจากคอลัมน์ ยอดขาย เป็นผลลัพธ์ที่ตอบมาเป็นตารางทั้งคอลัมน์ ตั้งแต่ A ไปถึง D เฉพาะแถวที่ยอดเกิน 5,000 เท่านั้น
| สินค้า | หมวด | ยอดขาย (บาท) | ผู้ขาย |
|---|---|---|---|
| ข้าวกล้อง | อาหาร | 7800 | วิภา |
| ปลากระป๋อง | อาหาร | 9600 | สุดา |
| ชาเขียว | เครื่องดื่ม | 5400 | วิภา |
| ไข่ไก่ | อาหาร | 12200 | อนุชา |
เห็นไหมครับ 4 แถวที่ผ่านเงื่อนไขถูกดึงออกมาให้ทั้งหมด โดยไม่ต้องลากสูตรแม้แต่เซลล์เดียว นี่คือหัวใจของ spill — ผลลัพธ์ไหลออกมาเองจนเต็มช่วงที่ต้องการ
ถ้าอยากให้เงื่อนไขปรับเปลี่ยนได้โดยไม่ต้องแกะสูตร ก็อ้างอิงไปที่เซลล์ที่พิมพ์เกณฑ์ไว้ เช่น ใส่ตัวเลข 5000 ไว้ที่ F1 แล้วเปลี่ยนสูตรเป็น =FILTER(A2:D9,C2:C9>F1) แค่นี้เวลาเปลี่ยนเกณฑ์ก็แค่พิมพ์ตัวเลขใหม่ใน F1 ผลลัพธ์จะเด้งตามทันที ดีกว่าค่าเลขตายตัวที่ต้องมาแก้ในสูตรทุกครั้งเลยครับ
แล้วถ้าอยากกรองเฉพาะหมวดเดียวล่ะครับ เช่น เอาเฉพาะเครื่องดื่ม สูตรก็คือ
=FILTER(A2:D9,B2:B9="เครื่องดื่ม")ส่วนใครอยากกรอง หลายเงื่อนไขพร้อมกัน ให้คูณเงื่อนไขด้วยเครื่องหมายดอกจันในวงเล็บ เช่น อยากได้สินค้าประเภทอาหารที่ยอดเกิน 3,000 บาท
=FILTER(A2:D9,(C2:C9>3000)*(B2:B9="อาหาร"))เงื่อนไขแต่ละชุดอยู่ในวงเล็บของตัวเอง แล้วคูณเข้าด้วยกัน ทำหน้าที่เหมือนคำว่า และ ให้กับ Excel ได้ผลลัพธ์เป็นแถวที่เข้าเงื่อนไขครบทั้งสองข้อ แค่นี้คุณก็กรองข้อมูลกลางไฟล์ได้โดยไม่ต้องแตะปุ่ม Filter เลยครับ
FILTER ยังมีพารามิเตอร์ตัวที่สามคือค่าที่อยากให้แสดงเมื่อไม่มีผลลัพธ์ตรงเงื่อนไข เช่น
=FILTER(A2:D9,C2:C9>99999,"ไม่พบข้อมูล")ถ้าครั้งไหนไม่มีสินค้าตัวไหนยอดเกินเกณฑ์ ก็จะขึ้นคำว่า ไม่พบข้อมูล แทน error #CALC! จึงเหมาะกับไฟล์รายงานที่ต้องมีคนมาเปิดทุกวัน แล้วไม่อยากให้เห็นเครื่องหมาย error ขวางตา
💡 เคล็ดลับ: เงื่อนไขใน FILTER ใช้เครื่องหมาย * คูณกันแปลว่า และ (ทุกเงื่อนไขต้องจริง) ส่วนถ้าอยากได้ หรือ ให้ใช้เครื่องหมาย + เช่น
(เงื่อนไข1)+(เงื่อนไข2)ครับ
3. SORT และ SORTBY — เรียงลำดับให้อัตโนมัติ
ถัดมาในชีทเดียวกันครับ หลังกรองข้อมูลออกมาได้แล้ว สิ่งที่คนส่วนใหญ่ทำต่อคือการเรียงลำดับ ไม่ว่าจะยอดจากมากไปน้อย หรือชื่อจาก ก ไป ฮ
SORT รับข้อมูลมาหนึ่งช่วงแล้วเรียงให้เลย ตัวอย่างเรียงตารางทั้งหมดตามคอลัมน์ ยอดขาย (คอลัมน์ที่ 3) จากมากไปน้อย
=SORT(A2:D9,3,-1)ตัวเลข 3 คือคอลัมน์ที่ใช้เรียง และ -1 คือจากมากไปน้อย (1 คือจากน้อยไปมาก) ผลลัพธ์คือ ไข่ไก่ 12,200 มาก่อน ตามด้วยปลากระป๋อง ข้าวกล้อง ไล่ลงไปจนถึงมาม่า 1,800
อยากเรียงตามชื่อจาก ก ไป ฮ ก็เปลี่ยนแค่เลขคอลัมน์เป็นคอลัมน์แรก เช่น =SORT(A2:D9,1,1) ได้ตารางที่เรียงชื่อสินค้าตามลำดับพจนานุกรม จะเรียงกี่คอลัมน์ต่อเนื่องกันก็ได้ เช่น =SORT(A2:D9,{3,1},{-1,1}) แปลว่าเรียงยอดจากมากไปน้อย แล้วต่อด้วยเรียงชื่อจากน้อยไปมากในกลุ่มที่ยอดเท่ากัน
ถ้าอยากได้ทั้งกรองและเรียงในสูตรเดียว ก็ซ้อนกันได้เลย
=SORT(FILTER(A2:D9,C2:C9>5000),3,-1)FILTER กรองยอดเกิน 5,000 ออกมาก่อน แล้ว SORT เอามาเรียงยอดจากมากไปน้อยให้อีกรอบ เรียกว่าจบงานในเซลล์เดียวเต็มๆ
ส่วน SORTBY ต่างจาก SORT ตรงที่เรียงตามคอลัมน์นอกช่วงข้อมูลโดยไม่ต้องรวมคอลัมน์นั้นเข้าไปด้วย เช่น
=SORTBY(A2:D9,C2:C9,-1)คือเอา A ถึง D มาเรียงตามค่าในคอลัมน์ C (ยอดขาย) ซึ่งอยู่ข้างนอกช่วงที่แสดง ลองเปิดไฟล์ดูผลลัพธ์ทั้ง SORT กับ SORTBY ในคอลัมน์ที่ผมเตรียมไว้ให้ จะเห็นว่าได้ตารางเหมือนกัน แต่ท่าทางการเขียนต่างกันเล็กน้อย
⚠️ ข้อควรระวัง: ระวังอย่าให้ช่วงการเรียงกับช่วงผลลัพธ์ทับซ้อนกันในจุดที่ทำให้ข้อมูลวน เพราะถ้าเผลอ กดล่าช้ามักได้ #REF! หรือข้อมูลเพี้ยน ให้เผื่อพื้นที่ว่างไว้ให้สูตร spill ครับ
💡 เคล็ดลับ: ลองจับ SORT กับ FILTER ต่อกันเป็นลูกโซ่ดูครับ เช่น
=SORT(FILTER(A2:D9,C2:C9>5000),3,-1)ได้ผลลัพธ์ที่ทั้งกรองและเรียงในครั้งเดียว เหมาะกับรายงานที่อยากได้ตารางพร้อมใช้โดยไม่ต้องแตะข้อมูลต้นฉบับ
4. UNIQUE — ดึงค่าที่ไม่ซ้ำแบบฉับไว
มาที่ชีท UNIQUE ครับ โจทย์นี้ตรงใจฝ่ายการตลาดและฝ่ายขาย คืออยากรู้ว่ามีลูกค้ากี่รายที่สั่งสินค้า และมีสินค้ากี่หมวด โดยไม่ต้องนับซ้ำ
สมมติตารางมีบันทึกการสั่งซื้อหลายสิบแถว และชื่อลูกค้าซ้ำกันหลายรอบ อยากได้รายชื่อที่ไม่ซ้ำสักครั้งเดียว
=UNIQUE(A2:A9)สูตรนี้จะกวาดคอลัมน์ A แล้วตอบชื่อลูกค้าทุกรายที่ไม่ซ้ำออกมา ทั้งสมชาย วิภา อนุชา สุดา เอามาใช้ทำรายชื่อสมาชิกหรือทำสรุปได้ทันที มันสบายมากตรงที่คุณไม่ต้องไปใช้ Remove Duplicates ที่จะไปแก้ข้อมูลต้นฉบับ เพราะ UNIQUE แค่คัดลอกค่ามาแสดง โดยไม่แตะข้อมูลเดิมแม้แต่นิดเดียว
ใช้กับหมวดสินค้าได้เหมือนกัน
=UNIQUE(C2:C9)ได้ค่า เครื่องดื่ม และ อาหาร แบบไม่ซ้ำ ใช้คู่กับ COUNTIF ก็ได้ผลลัพธ์ว่าลูกค้าแต่ละคนสั่งกี่ครั้ง เช่น
=COUNTIF(A2:A9,A2)นับจำนวนครั้งที่ชื่อใน A2 ปรากฏในคอลัมน์ A ทั้งหมด เป็นการเช็คความถี่การซื้อของลูกค้าแต่ละคนได้ในพริบตา แล้วก็ยังเอาไปจัดกลุ่มลูกค้าประจำหรือลูกค้าที่ซื้อบ่อยได้อีกด้วย
จุดเด่นของ UNIQUE อีกอย่างคือมันอ่านสถานะข้อมูลแบบเรียลไทม์ ถ้าคุณเพิ่มชื่อลูกค้าใหม่ลงในตารางต้นทาง ผลลัพธ์ UNIQUE จะขยายตามให้อัตโนมัติโดยไม่ต้องแก้สูตรเลย ยิ่งถ้าลูกค้าเพิ่มหลักสิบรายทั้งปี คุณก็ไม่ต้องมานั่งลบชื่อที่ซ้ำด้วยมืออีกต่อไป
อยากให้รายชื่อไม่ซ้ำออกมาแบบเรียงสวยๆ ก็จับ UNIQUE ไปใส่ใน SORT ได้เลย
=SORT(UNIQUE(A2:A9))UNIQUE ดึงชื่อที่ไม่ซ้ำออกมา แล้ว SORT เรียงจาก ก ไป ฮ ให้อัตโนมัติ เหมาะทำรายชื่อในดรอปดาวน์หรือรายงานสรุปที่อยากให้อ่านง่าย เรียกว่าใช้งานเป็นลูกโซ่ต่อกันแบบไม่จำกัด
ส่วนใครอยากให้รายชื่อที่ไม่ซ้ำวางในแนวนอนแทนแนวตั้ง ก็ใช้ TRANSPOSE ห่อไว้ได้
=TRANSPOSE(UNIQUE(C2:C9))ได้หมวดสินค้าแบบไม่ซ้ำวางเรียงกันในแถวเดียว เหมาะทำหัวตารางหรือป้ายกำกับคอลัมน์หลายชุดให้สวยและอัตโนมัติโดยไม่ต้องคัดลอกทีละเซลล์
💡 เคล็ดลับ: UNIQUE มีพารามิเตอร์ตัวที่สาม
UNIQUE(ช่วง,...,TRUE)ให้ดึงเฉพาะค่าที่ซ้ำกันมาแทน แต่เวอร์ชันเก่าบางตัวอาจไม่รองรับ ถ้าอยากได้ค่าที่ซ้ำ ลองใช้ COUNTIF คู่กับ FILTER ดูก็ได้ครับ📌 ข้อควรจำ: UNIQUE ไม่ลบข้อมูลต้นฉบับ มันแค่สร้างชุดค่าใหม่ที่ไม่มีซ้ำต่างหาก จึงปลอดภัยต่อข้อมูลจริงเสมอ ไม่เหมือน Remove Duplicates ที่แก้ไฟล์ต้นทางครับ
5. SEQUENCE — สร้างลำดับและวันที่แบบอัตโนมัติ
มาที่ชีทสุดท้าย SEQUENCE ครับ ฟังก์ชันนี้ดูเล็กแต่แกร่งมาก เพราะมันสร้างลำดับตัวเลขหรือวันที่ขึ้นมาเป็นชุดยาวๆ ในเซลล์เดียว เหมาะกับงานที่ต้องสร้างตัวเลขหรือวันซ้ำๆ เป็นสิบเป็นร้อยค่า เพราะถ้าให้พิมพ์มือคงเสียเวลาไปหลายนาที แต่มีสูตรเดียวจบในพริบตา
โครงสร้างคือ
=SEQUENCE(จำนวนแถว, จำนวนคอลัมน์, ค่าเริ่ม, ขั้น)ตัวอย่างสร้างเลข 1 ถึง 10 ลงแนวตั้ง
=SEQUENCE(10)ได้คอลัมน์ตัวเลข 1-10 ไหลลงมาเลย ส่วนถ้าอยากได้เมทริกซ์ 2 แถวคูณ 5 คอลัมน์
=SEQUENCE(2,5)จะได้เลข 1 ถึง 10 กระจายเป็นตาราง 2×5 เต็มทั้งช่วง
ส่วนที่คนทำงานบัญชีชอบคือการสร้างวันที่ทั้งปีด้วยสูตรเดียว อยากได้วันแรกของทุกเดือนตั้งแต่เดือนมกราคมถึงธันวาคม 2026
=DATE(2026,SEQUENCE(12),1)SEQUENCE(12) สร้างเลข 1 ถึง 12 มาแทนเดือน แล้ว DATE ประกอบเป็นวันที่ 1 ของแต่ละเดือน ได้ 1 ม.ค. 2026 ไปจนถึง 1 ธ.ค. 2026 ครบทั้ง 12 เดือนในสูตรเดียว ไม่ต้องพิมพ์ทีละเดือน แล้วยังมาถึงการสร้างลำดับปีด้วย
=SEQUENCE(4,1,2026,1)ได้ปี 2026 2027 2028 2029 เหมาะทำหัวคอลัมน์หลายปีในไฟล์สรุป
จะเห็นว่า SEQUENCE เป็นเหมือนโรงงานผลิตตัวเลขชนิดหนึ่ง ยิ่งจับคู่กับ DATE หรือฟังก์ชันอื่นก็ยิ่งทรงพลัง เช่น เอาไปสร้างเลขที่บิลอัตโนมัติ หรือทำแถวเป้าหมายรายเดือนทั้งปีได้ในครั้งเดียว และถ้านับย้อนกลับก็ทำได้ด้วยการใส่ขั้นเป็นค่าลบ
ตัวอย่างฝึกบิลแบบเลขต่อเนื่อง อยากได้เลข 001 ถึง 010 ก็รวม SEQUENCE เข้ากับ TEXT ได้แบบนี้
=TEXT(SEQUENCE(10),"000")ได้เลข 001 002 003 ไล่ไปจนถึง 010 อัตโนมัติ ใช้ทำเลขที่ใบเสร็จหรือลำดับรายการได้ทันทีโดยไม่ต้องพิมพ์ทีละตัว และถ้าอยากสร้างวันที่ครบทุกเดือนแบบมีวันหยุดด้วย ก็เอา SEQUENCE ไปจับคู่กับ WORKDAY หรือ NETWORKDAYS ได้ต่อเนื่องอีกชั้นหนึ่ง
⚠️ ข้อควรระวัง: ถ้าช่วงผลลัพธ์ของ SEQUENCE หรือ Dynamic Array ไปชนกับข้อมูลที่มีอยู่แล้ว จะเกิด error #SPILL! ก็แค่ลบหรือย้ายข้อมูลที่ขวางออก ให้มีพื้นที่ว่างพอให้ค่าหล่นลงมาเท่านั้นเองครับ
6. ข้อควรระวังเรื่อง spill และ #SPILL!
การจะใช้ Dynamic Arrays ให้ราบรื่น สิ่งที่ต้องเข้าใจมากที่สุดคือเรื่องของ spill range ครับ แปลว่าพื้นที่ว่างที่ผลลัพธ์จะไหลลงมา
ถ้าพื้นที่นั้นมีข้อมูลอะไรบังอยู่ Excel จะไม่กล้าเขียนทับ และจะขึ้น error ว่า
#SPILL!วิธีแก้ไม่ยากเลย แค่หาว่าอะไรขวางอยู่ระหว่างช่วงที่ผลลัพธ์จะหล่น ไปลบหรือย้ายออกให้ว่าง แล้วสูตรจะทำงานเองทันที สังเกตอีกอย่างว่า เซลล์แรกที่ใส่สูตรจะมีกรอบเส้นประล้อมรอบทั้งช่วงผลลัพธ์ให้เห็นชัดว่า spill ไปถึงไหน
อีกเรื่องที่ควรรู้คือ เราไม่ควรพิมพ์หรือลากสูตรเองทับลงไปในบริเวณที่กำลัง spill เพราะมันจะตัดการทำงานของ Dynamic Array ทันที ถ้าจะแก้ก็แก้เฉพาะสูตรที่เซลล์บนสุดตัวเดียวเท่านั้น แล้วให้ Excel กระจายผลลัพธ์ส่วนที่เหลือให้เอง
ลองเปิดชีท SEQUENCE ในไฟล์ตัวอย่างดูครับ ถ้าคุณลากสูตรทับลงไปในพื้นที่ที่ค่าหล่นอยู่ Excel จะขึ้น #SPILL! ทันที เพราะมันพยายามจะเขียนทับผลลัพธ์ของตัวเอง ซึ่งเป็นสิ่งที่ Dynamic Array ไม่อนุญาตโดยดี พอเห็นแบบนี้จะเข้าใจเลยว่าทำไมต้องเว้นที่ว่างให้ผลลัพธ์หล่นลงมาโดยไม่มีอะไรขวาง
อีกจุดที่หลายคนงง คือผลลัพธ์แบบ spill จะอ้างอิงด้วยสัญลักษณ์แฮชต่อท้าย เช่น E3# หมายถึงทั้งช่วงที่เริ่มจาก E3 เป็นต้นไป ใช้อ้างอิงทั้งก้อนข้อมูลที่กรองออกมาได้ในสูตรอื่น เช่น =SUM(F3#) รวมยอดขายทั้งก้อนที่ FILTER คัดออกมาได้ทันที แม้ไม่รู้ว่าจะมีกี่แถว นี่คือการอ้างอิงช่วงแบบไดนามิกที่ยืดหยุ่นสุดๆ
นอกจากนี้การกรองที่อ้างอิงทั้งช่วงแบบนี้ยังทำงานต่อเนื่องกับพารามิเตอร์อื่นได้ เช่น เอาไปครอบ IFERROR เพื่อกันข้อผิดพลาด หรือผูกกับ SUMIFS เพื่อสรุปยอดเฉพาะกลุ่มตามเงื่อนไข ยิ่งผูกกันหลายชั้นเท่าไหร่ ไฟล์ก็ยิ่งทำงานแทนคุณได้มากเท่านั้น ถือเป็นก้าวที่สำคัญสำหรับคนที่อยากยกระดับสูตรในไฟล์จริง
📌 ข้อควรจำ: ถ้าเจอ #SPILL! อย่าตกใจ มันไม่ใช่สูตรผิด แต่มันคือพื้นที่ไม่พอต่างหาก เปิดไฟล์ตัวอย่างแล้วลองลบข้อมูลในช่องที่ขวางดู แล้วจะเห็นว่าสูตรกลับมาทำงานเองทันทีครับ
7. สถานการณ์งานจริง — ประยุกต์รวมกันทั้งชุด
สถานการณ์ที่ 1: ฝ่ายขายสรุปรายเดือน
อยากได้ตารางสินค้าที่มียอดขายเกินเกณฑ์ 5,000 บาท เรียงจากมากไปน้อย โดยไม่ต้องไปกด Filter และ Sort ซ้ำๆ ใช้
=SORT(FILTER(A2:D9,C2:C9>5000),3,-1)จบในเซลล์เดียว พร้อมอัปเดตเองเมื่อเพิ่มข้อมูลใหม่
สถานการณ์ที่ 2: ฝ่ายการตลาดดึงรายชื่อลูกค้า
อยากได้รายชื่อลูกค้าที่ไม่ซ้ำมาทำรายชื่อส่งโปรโมชัน ใช้ UNIQUE ดึงคอลัมน์ชื่อออกมา แล้วใช้ COUNTIF นับความถี่การซื้อ เพื่อจัดกลุ่มลูกค้าประจำกับลูกค้าใหม่
สถานการณ์ที่ 3: ฝ่ายบัญชีสร้างปฏิทินทั้งปี
อยากได้วันแรกของทุกเดือนทั้งปีเพื่อทำงบรายเดือน ใช้
=DATE(2026,SEQUENCE(12),1)สร้าง 12 แถวในครั้งเดียว และใช้ SEQUENCE สร้างปีที่คาดการณ์ไปข้างหน้าเป็นหัวคอลัมน์หลายปี
สถานการณ์ที่ 4: ฝ่ายผลิตทำตารางเป้าหมายรายวัน
ใช้ SEQUENCE สร้างลำดับวันที่ของทั้งเดือนทีละวัน แล้วจับคู่กับ SUMIFS หรือ FILTER เพื่อให้แดชบอร์ดผลผลิตอัปเดตตามวันที่ทุกวันแบบอัตโนมัติ
สถานการณ์ที่ 5: ฝ่ายคลังเช็คสต็อกสินค้าหมุนเวียน
อยากเห็นเฉพาะสินค้าที่มียอดขายต่ำกว่าเกณฑ์เพื่อเข้าไปจัดโปรโมชัน ใช้ FILTER กับยอดขาย และเรียงยอดจากน้อยไปมากด้วย SORT เพื่อให้เห็นสินค้าที่ต้องจัดการก่อนเป็นอันดับแรก
8. สรุป
มาถึงตรงนี้คุณน่าจะเห็นแล้วว่า Dynamic Arrays เปลี่ยนวิธีทำงานกับข้อมูลไปอย่างสิ้นเชิงครับ
- FILTER กรองข้อมูลตามเงื่อนไข อัตโนมัติ ไม่ต้องแตะปุ่ม Filter
- SORT / SORTBY เรียงลำดับ ชัดเจนทั้งตัวข้อมูลและคอลัมน์อ้างอิง
- UNIQUE ดึงค่าที่ไม่ซ้ำโดยไม่แก้ข้อมูลต้นฉบับ
- SEQUENCE สร้างลำดับตัวเลขและวันที่ทีละชุดใหญ่
- spill + #SPILL! หลักการเรื่องพื้นที่ว่างที่ต้องเข้าใจก่อนใช้
ก่อนจบผมอยากเน้นเรื่องการทำงานเป็นทีมของทั้งชุดอีกครั้ง เพราะพลังที่แท้จริงของ Dynamic Arrays ไม่ได้อยู่ที่ฟังก์ชันเดี่ยวๆ แต่อยู่ที่การซ้อนเข้าด้วยกัน เช่น SORT(FILTER(…)), SORT(UNIQUE(…)), ไปจนถึง SEQUENCE คูณกับ DATE เราสร้างไฟล์รายงานที่กรองเอง เรียงเอง และสร้างชุดข้อมูลเองได้โดยไม่ต้องแตะข้อมูลต้นทางแม้แต่นิดเดียว ยิ่งข้อมูลโตแค่ไหน ยิ่งเห็นประโยชน์ชัดขึ้นเท่านั้นครับ
เชิงปฏิบัติแล้ว เริ่มจากหนึ่งฟังก์ชันที่คุณใช้บ่อยที่สุดก่อน เช่น FILTER สำหรับคนขาย แล้วค่อยฝึกซ้อนกับ SORT และ UNIQUE เมื่อชิน ค่อยๆ เพิ่มทีละชั้นจะรู้สึกไม่กดดัน และสิ่งที่เรียนรู้ก็เอาไปใช้จริงได้ทันทีในวันเดียวกัน
จุดที่อยากฝากไว้คือ Dynamic Arrays ทำงานแบบเรียลไทม์ เมื่อข้อมูลต้นทางเปลี่ยน ผลลัพธ์ก็เปลี่ยนตาม ไม่ต้องมานั่งรีเฟรชหรือลากสูตรใหม่ และการซ้อนสูตร เช่น SORT กับ FILTER เข้าด้วยกัน จะทำให้คุณเขียนงานที่แต่เดิมต้องทำหลายขั้นตอนจบภายในเซลล์เดียว สิ่งเดียวที่ต้องมีคือพื้นที่ว่างให้ผลลัพธ์ได้หล่นลงมาเท่านั้น
เคล็ดลับอีกอย่างคืออย่ากลัวการซ้อนสูตรหลายชั้น เพราะแต่ละชั้นแยกอ่านออกมาเป็นคำสั่งเดียวได้เสมอ เช่น อ่าน SORT(FILTER(…)) ได้ว่า กรองก่อน แล้วค่อยเรียง ดังนั้นถ้าเห็นสูตรยาวๆ ให้แยกส่วนทำความเข้าใจทีละชิ้น จะไม่งงเลยครับ ยิ่งบวกกับเรื่องการอ้างอิงแบบ E3# ที่จะเจอในบทความถัดๆ ไป เทคนิคกลุ่มนี้จะกลายเป็นอาวุธหลักของคุณในทุกไฟล์งาน
เปิดไฟล์ตัวอย่างแล้วลองไล่เล่นให้ครบทั้ง 3 ชีทดูนะครับ เริ่มจาก FILTER SORT กรองและเรียงยอดขาย ข้ามไป UNIQUE ดึงลูกค้าไม่ซ้ำ และปิดท้ายที่ SEQUENCE สร้างลำดับและเดือนทั้งปี ลองลบข้อมูลในช่องที่ขวาง spill เพื่อดูว่า #SPILL! หายไปยังไง แล้วลองเปลี่ยนเงื่อนไขใน FILTER ดู ผลลัพธ์จะขยับตามให้ทันที
ยิ่งถ้าคุณเริ่มซ้อนสูตรตั้งแต่ตอนนี้ เช่น กรองแล้วเรียง หรือดึงชื่อไม่ซ้ำแล้วเรียงต่อ ไฟล์งานของคุณจะกลายเป็นเหมือนแดชบอร์ดที่ตอบเองได้ทุกครั้งที่ข้อมูลเปลี่ยน ไม่ต้องมาเสียเวลาเปิดปิด Filter หรือคัดลอกด้วยมือซ้ำแล้วซ้ำเล่า
บทความหน้าเราจะได้รู้จัก การอ้างอิงขั้นสูง — INDIRECT, OFFSET, ADDRESS ที่จะทำให้สูตรของคุณอ้างอิงตำแหน่งแบบยืดหยุ่นถึงขั้นไม่มีที่ติ เจอกันครับ! 😊