ฟังก์ชัน Dynamic array เป็นฟีเจอร์ที่มีประโยชน์และทรงพลังที่สุด แทนที่จะเขียนสูตรซับซ้อนที่คุณคัดลอกลงหลายร้อยแถว คุณเขียนสูตรเดียวที่ "spill" ผลลัพธ์ลงในเซลล์ให้มากพอตามต้องการ เมื่อข้อมูลของคุณเปลี่ยน ฟังก์ชันเหล่านี้อัปเดตแบบ real-time นี่ทำให้รายงานและสรุปมีประสิทธิภาพมากขึ้นและพลาดน้อยลง
ในบทช่วยสอนนี้ เราแสดง 5 วิธีที่ฟังก์ชัน dynamic array (FILTER, UNIQUE, SORT) จะเปลี่ยนวิธีการทำงานของคุณ
Dynamic Arrays & Spilling ใน Excel
Dynamic arrays: ใส่สูตรในเซลล์ และ Excel จะเติมหรือ spill ผลลัพธ์ไปยังเซลล์ข้างเคียงโดยอัตโนมัติ ถ้าขนาดผลลัพธ์เปลี่ยน (แถวมากขึ้น น้อยลง) ช่วงที่ spill จะขยายหรือหดตัวอัตโนมัติ คุณจะรู้จักช่วงที่ spill เพราะ:
- สูตรอยู่ที่เซลล์ซ้ายบนสุดอย่างเดียว
- เซลล์อื่นๆ แสดงขอบบาง และถ้าคุณคลิกพวกมัน จะเห็นสูตรเป็นสีเทา
- คุณสามารถอ้างอิงช่วง spill ทั้งหมดเป็น A2# (สัญลักษณ์แฮช)
Dynamic arrays พร้อมใช้งานใน Microsoft 365 (Excel for Microsoft 365), Excel 2021 และใหม่กว่า
1. สร้างรายการไม่ซ้ำสำหรับสรุปโดยไม่มี Duplicate
ก่อน dynamic arrays การลบ duplicate ต้องใช้ "Remove Duplicates" หรือสูตรซับซ้อน UNIQUE สร้างรายการ unique ที่ spill ได้สำหรับสรุปเร็ว เหมาะสำหรับ dropdowns, validation lists, และแดชบอร์ดที่ไม่ใช้ pivot
รายการ Region ที่ไม่ซ้ำ:
- เลือกเซลล์และใส่สูตรต่อไปนี้
สูตรนี้ spill รายการสินค้าที่ไม่ซ้ำ
Unique Combinations:
ดึงการจับคู่ที่ไม่ซ้ำของ Region และ Salesperson:
นี่คืน spill สองคอลัมน์แสดงการจับคู่ที่ไม่ซ้ำทุกคู่
นับ Order ที่ไม่ซ้ำ:
- รวม UNIQUE กับ COUNTA สำหรับสรุป
สูตรนี้คำนวณจำนวน order ที่ไม่ซ้ำทั้งหมด ถ้าคุณเพิ่มแถวใหม่ในชุดข้อมูล รายการจะ refresh อัตโนมัติ
ค่าที่ปรากฏเพียงครั้งเดียว:
หาค่าที่เกิดขึ้นเพียงครั้งเดียว
อาร์กิวเมนต์ที่สาม (TRUE) คืนเฉพาะค่าที่ไม่ซ้ำ
ใช้ UNIQUE สำหรับ Data Validation (Dropdowns):
- เลือกเซลล์ที่คุณต้องการ dropdown
- ไปที่แท็บ Data >> เลือก Data Validation
- ตั้ง Allow เป็น List
- ใน Source พิมพ์:
ตอนนี้ dropdown จะแสดง region ที่ไม่ซ้ำปัจจุบันเสมอตามข้อมูลของคุณ
Grouped Summary ใช้ UNIQUE:
คุณสามารถจับคู่ UNIQUE กับ SUMIF เพื่อสร้าง grouped summary แบบไดนามิก
คำนวณรายได้รวมของภูมิภาคต่างๆ:
=SUMIF(B2:B61, I15#, G2:G61)
สูตรนี้อ้างอิงรายการ region ที่ spill ทั้งหมด (I15#) SUMIF คืนรายได้รวมสำหรับแต่ละพื้นที่ เพิ่มหรือเปลี่ยนแถวใน Sales และ region พร้อม totals จะปรับตัวอัตโนมัติ
ตอนนี้ ลองเปลี่ยน region และ Total Revenue จะอัปเดตอัตโนมัติตามการเลือก
2. กรองข้อมูลโดยอัตโนมัติสำหรับรายงานไดนามิก
การกรองแบบดั้งเดิมต้องการฟิลเตอร์ด้วยมือหรือสูตรซับซ้อน ฟังก์ชัน FILTER() เป็นเครื่องมือทรงพลังที่สุดสำหรับ dynamic arrays มันดึงเฉพาะแถวที่ตรงเงื่อนไขและ spill ผลลัพธ์เป็นช่วงแบบตาราง ใช้ฟังก์ชันนี้ คุณสามารถสร้างรายงานที่อัปเดตทันที
กรอง Sales ตาม Region:
- เพื่อแสดงพฤติกรรมไดนามิกของ FILTER ใช้ drop-down สำหรับ region
=FILTER(A2:G61, B2:B61="East")
- เพื่อทำให้ไดนามิกมากขึ้น ให้อ้างอิงเซลล์เกณฑ์จาก drop-down
=FILTER(A2:G61, B2:B61=I4)
นี่ spill ทุกแถวที่ Region เป็น "East" มันเป็น mini report ที่ขยายหรือหดตัวถ้าคุณเพิ่มหรือลบข้อมูล เปลี่ยน I4 เป็น "North" และรายงานอัปเดตอัตโนมัติ—ไม่ต้อง VBA หรือ refresh ด้วยมือ
หลายเกณฑ์:
กรองสำหรับ East region และจำนวนเกิน $1,000:
=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")
เครื่องหมายคูณ (*) ทำงานเป็น AND คุณสามารถใช้บวก (+) สำหรับ OR conditions
คุณสามารถสร้างแดชบอร์ดโต้ตอบที่ผู้ใช้เลือกเกณฑ์จาก dropdowns
3. สร้างรายการจัดเรียงอัตโนมัติด้วย SORT() และ SORTBY()
การจัดเรียงเดิมหมายถึงคัดลอกข้อมูลหรือใช้ตาราง SORT สร้างมุมมองที่จัดเรียงแล้วแบบไดนามิก spill ได้ ทุกครั้งที่คุณเพิ่ม ลบ หรืออัปเดตชุดข้อมูล มันจะจัดเรียงข้อมูลอัตโนมัติ
Auto-Sorted Sales Leaderboard:
สูตรนี้จัดเรียงช่วงข้อมูลทั้งหมดตามคอลัมน์ 7 (Amount) เรียงลำดับมากไปน้อย (-1) ข้อมูลต้นฉบับไม่ถูกเปลี่ยน เพิ่มยอดขายสูงสุดใหม่ และมันจะปรากฏในตำแหน่งที่ถูกต้องอัตโนมัติ
Multiple Sort Levels:
จัดเรียงตาม Salesperson แล้วตาม Amount:
=SORT(A2:G61, {3,7}, {1,-1})
วงเล็บปีกกาสร้าง array: จัดเรียงตามคอลัมน์ 3 ascending แล้วคอลัมน์ 7 descending
จัดเรียงตามเกณฑ์ที่ต่างกัน:
ฟังก์ชัน SORTBY ให้คุณจัดเรียงช่วงหนึ่งจากค่าในอีกช่วงหนึ่ง
จัดเรียงช่วงทั้งหมดตามชื่อ salesperson พร้อมเก็บคอลัมน์ทั้งหมด:
=SORTBY(A2:G61, C2:C61, 1)
4. รวม FILTER และ UNIQUE สำหรับสรุป Unique เฉพาะเจาะจง
สำหรับสรุปขั้นสูง คุณสามารถรวมฟังก์ชันเพื่อกรองก่อน แล้วค่อย uniquify spill รายการสะอาดที่อัปเดตอัตโนมัติ รวมฟังก์ชันเพื่อสร้างรายงานที่ดูแลตัวเอง
- เลือกเซลล์และใส่สูตรต่อไปนี้
=UNIQUE(FILTER(D2:D61, B2:B61="North"))
นี่กรองสินค้าจากภาคเหนือ แล้ว spill เฉพาะที่ไม่ซ้ำ
- ต่อไป ใส่ฟังก์ชัน SORT เพื่อจัดเรียงสรุป
=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))
ตอนนี้สูตร spill รายการสินค้าที่ไม่ซ้ำและจัดเรียงแล้ว
นี่แทนที่สูตร array ที่เยอะอย่าง {=INDEX(…)} สำหรับ unique filtered lists เปลี่ยนข้อมูลหรือเกณฑ์ และมัน spill อัปเดตอย่างราบรื่นสำหรับรายงานอย่าง regional product inventories
5. หน้าสรุปแบบไดนามิก ขับเคลื่อนด้วยเกณฑ์ (รวม FILTER, UNIQUE, SORT)
ตอนนี้ รวมฟังก์ชันเหล่านี้เป็น mini summary/reporting page ที่อัปเดตจากเซลล์เกณฑ์เพียงไม่กี่เซลล์
สร้าง region-level dashboard: region dropdown (ขับโดย UNIQUE), filtered region list (FILTER), และ top-products-by-region list (FILTER + SORT)
ขั้นตอนที่ 1: Region Dropdown ใช้ UNIQUE
สร้างรายการ region ที่ไม่ซ้ำและใช้สร้าง drop-down
ขั้นตอนที่ 2: Region-Level Sales Details
=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")
สูตรนี้กรองข้อมูลการขายตาม region เปลี่ยน region จาก drop-down และตารางขายจะอัปเดตอัตโนมัติ
ขั้นตอนที่ 3: Top Products ใน Region ที่เลือก
ระบุว่าสินค้าอะไรขายดีที่สุดใน region ที่เลือก
- สร้างตารางเล็กกับหัวเรื่อง Product และ Total Revenue
- ใน L4 ดึงสินค้าที่ไม่ซ้ำที่ขายใน region ที่เลือก:
=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))
นี่ spill รายการสินค้าสำหรับ region นั้น
- ใน M4 คำนวณรายได้รวมต่อสินค้าใน region นั้น:
=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)
นี่คืน spilled list ของรายได้รวมตรงกับแต่ละสินค้าใน L4#
- เพื่อแสดงจัดเรียงตามรายได้ (descending) ให้จัดเรียงสอง spilled columns พร้อมกัน:
=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)
ที่นี่ CHOOSE({1,2}, L4#, M4#) สร้าง array สองคอลัมน์ (Product และ Total Revenue) 2 หมายถึง "จัดเรียงตามคอลัมน์ที่ 2 (Total Revenue)" -1 หมายถึง descending
Dynamic Top Products in [Selected Region] Report:
- เปลี่ยน region dropdown ใน B4 สังเกตว่าสรุปทั้งหมดอัปเดต
- เพิ่มข้อมูลใหม่ใน Sales และมันจะเพิ่มเข้าไปในรายงาน
- ไม่มีสูตรคัดลอก ไม่มีการจัดเรียงด้วยมือ ไม่มี PivotTable refresh
สรุป
บทช่วยสอนนี้สาธิต 5 วิธีที่ฟังก์ชัน dynamic array (FILTER, UNIQUE, SORT) เปลี่ยนวิธีการทำงานของคุณ ฟังก์ชัน Dynamic array กำจัดงานเบ็ดเสียของการดูแลสเปรดชีต คุณโฟกัสที่การวิเคราะห์แทนการคัดลอกสูตรและแก้ reference ที่พัง รายงานอัปเดตตัวเอง แดชบอร์ดอัปเดตอัตโนมัติ เมื่อคุณเริ่มใช้ฟังก์ชันเหล่านี้ คุณจะพบว่ามันเหมาะสำหรับสรุปและแดชบอร์ด—คุณสามารถสร้างรายงานที่อัปเดตทันทีโดยไม่ต้อง refresh ด้วยมือ ไม่มีสูตร array ซับซ้อน และไม่ต้องมี helper columns
