แปลงสเปรดชีตของคุณ:5 ฟังก์ชันอาร์เรย์แบบไดนามิก (FILTER, UNIQUE, SORT)

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

ในบทช่วยสอนนี้ เราจะแสดง 5 วิธีที่ฟังก์ชันอาร์เรย์แบบไดนามิก (FILTER, UNIQUE, SORT) จะเปลี่ยนวิธีการทำงานของคุณ

อาร์เรย์ไดนามิกและการหกใน Excel

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

• สูตรอยู่ในเซลล์ด้านซ้ายบนเท่านั้น
• เซลล์อื่นๆ จะแสดงเส้นขอบสีอ่อน และหากคุณคลิกเซลล์เหล่านั้น คุณจะเห็นสูตรเป็นสีเทา
• คุณสามารถอ้างถึงช่วงที่หกทั้งหมดเป็น A2# (สัญลักษณ์แฮช)

อาร์เรย์แบบไดนามิกพร้อมใช้งานใน Microsoft 365 (Excel สำหรับ Microsoft 365), Excel 2021 และใหม่กว่า

1. สร้างรายการที่ไม่ซ้ำสำหรับบทสรุปโดยไม่ซ้ำกัน

ก่อนอาร์เรย์แบบไดนามิก การลบรายการที่ซ้ำกันจำเป็นต้องมี "ลบรายการที่ซ้ำกัน" หรือสูตรที่ซับซ้อน UNIQUE สร้างรายการที่ไม่ซ้ำแบบกระจายได้เพื่อการสรุปอย่างรวดเร็ว เหมาะอย่างยิ่งสำหรับดรอปดาวน์ รายการตรวจสอบ และแดชบอร์ดที่ไม่มี Pivot
แสดงรายการภูมิภาคที่ไม่ซ้ำ:

• เลือกเซลล์และแทรกสูตรต่อไปนี้

สูตรนี้แสดงรายการผลิตภัณฑ์ที่ไม่ซ้ำใคร

ชุดค่าผสมที่ไม่ซ้ำ:
รับการผสมผสานที่เป็นเอกลักษณ์ของภูมิภาคและพนักงานขาย:

ซึ่งจะคืนค่าการหกคอลัมน์สองคอลัมน์ที่แสดงชุดค่าผสมที่ไม่ซ้ำกันทุกรายการ

นับคำสั่งซื้อที่ไม่ซ้ำ:

• รวม UNIQUE กับ COUNTA เพื่อการสรุป

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

ค่าที่ปรากฏเพียงครั้งเดียว:
ค้นหาค่าที่เกิดขึ้นเพียงครั้งเดียว

อาร์กิวเมนต์ที่สาม (05 ) ส่งคืนเฉพาะค่าที่ไม่ซ้ำกัน

ใช้ UNIQUE สำหรับการตรวจสอบข้อมูล (แบบเลื่อนลง):

1. เลือกเซลล์ที่คุณต้องการให้ดรอปดาวน์
2. ไปที่ ข้อมูล แท็บ>> เลือก การตรวจสอบข้อมูล .
3. ตั้งค่า อนุญาต ไปที่ รายการ .
4. ใน แหล่งที่มา , พิมพ์:

ตอนนี้เมนูแบบเลื่อนลงจะแสดงภูมิภาคที่ไม่ซ้ำกันในปัจจุบันตามข้อมูลของคุณเสมอ
สรุปแบบกลุ่มโดยใช้ UNIQUE:
คุณสามารถจับคู่ UNIQUE กับ SUMIF เพื่อสร้างสรุปการจัดกลุ่มแบบไดนามิก
คำนวณรายได้รวมของภูมิภาคต่างๆ:

=SUMIF(B2:B61, I15#, G2:G61)

สูตรนี้อ้างอิงถึงรายการภูมิภาคที่หกทั้งหมด (17 ). SUMIF ส่งคืนรายได้รวมสำหรับแต่ละพื้นที่ เพิ่มหรือเปลี่ยนแถวในการขาย จากนั้นภูมิภาคและผลรวมจะปรับโดยอัตโนมัติ

ตอนนี้ ให้เปลี่ยนภูมิภาคแล้วรายได้รวมจะอัปเดตโดยอัตโนมัติตามการเลือก

2. กรองข้อมูลสำหรับรายงานแบบไดนามิกโดยอัตโนมัติ

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

• หากต้องการแสดงพฤติกรรมแบบไดนามิกของฟังก์ชัน FILTER ให้ใช้เมนูแบบเลื่อนลงสำหรับภูมิภาค

=FILTER(A2:G61, B2:B61="East")

• หากต้องการทำให้เป็นแบบไดนามิกมากขึ้น ให้อ้างอิงเซลล์เกณฑ์จากเมนูแบบเลื่อนลง

=FILTER(A2:G61, B2:B61=I4)

ซึ่งครอบคลุมทุกแถวที่ภูมิภาคคือ "ตะวันออก" เป็นรายงานขนาดเล็กที่ขยายหรือย่อหากคุณเพิ่มหรือลบข้อมูล เปลี่ยน 20 เป็น "ทิศเหนือ" และรายงานจะอัปเดตโดยอัตโนมัติ โดยไม่จำเป็นต้องรีเฟรช VBA หรือด้วยตนเอง

สิ่งนี้จะกำจัดสำเนาข้อมูลแบบคงที่ รายงานของคุณสะท้อนถึงแหล่งที่มาเสมอ
หลายเกณฑ์:
ตัวกรองสำหรับภูมิภาคตะวันออกและจำนวนเงินมากกว่า $1,000:

=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")

เครื่องหมายดอกจัน (36 ) ทำงานเป็น AND คุณสามารถใช้เครื่องหมายบวก (40 ) สำหรับเงื่อนไขหรือ
คุณสามารถสร้างแดชบอร์ดแบบโต้ตอบที่ผู้ใช้เลือกเกณฑ์จากดรอปดาวน์

3. สร้างรายการเรียงลำดับอัตโนมัติโดยใช้ SORT() และ SORTBY()

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

สูตรนี้จัดเรียงช่วงข้อมูลทั้งหมดตามคอลัมน์ 7 (จำนวน) จากมากไปน้อย (57 ). ข้อมูลต้นฉบับยังคงไม่ถูกแตะต้อง เพิ่มยอดขายสูงสุดใหม่และจะปรากฏในตำแหน่งที่ถูกต้องโดยอัตโนมัติ

การเรียงลำดับหลายระดับ:
จัดเรียงตามพนักงานขาย จากนั้นตามจำนวน:

=SORT(A2:G61, {3,7}, {1,-1})

วงเล็บปีกกาจะสร้างอาร์เรย์:จัดเรียงตามคอลัมน์ 3 จากน้อยไปหามาก จากนั้นคอลัมน์ 7 จากมากไปน้อย

จัดเรียงตามเกณฑ์ที่แตกต่างกัน:
ฟังก์ชัน SORTBY ช่วยให้คุณสามารถเรียงลำดับช่วงหนึ่งตามค่าในช่วงอื่นได้
จัดเรียงช่วงทั้งหมดตามชื่อพนักงานขายโดยคงคอลัมน์ทั้งหมดไว้:

=SORTBY(A2:G61, C2:C61, 1)

4. รวม FILTER และ UNIQUE เพื่อสรุปข้อมูลที่ไม่ซ้ำแบบกำหนดเป้าหมาย

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

• เลือกเซลล์และแทรกสูตรต่อไปนี้

=UNIQUE(FILTER(D2:D61, B2:B61="North"))

วิธีนี้จะกรองผลิตภัณฑ์จากภาคเหนือ จากนั้นจึงกรองผลิตภัณฑ์ที่ไม่ซ้ำออก

• ถัดไป เพิ่มฟังก์ชัน SORT เพื่อจัดเรียงข้อมูลสรุป

=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))

ตอนนี้สูตรได้กระจายรายการผลิตภัณฑ์ที่เรียงลำดับและไม่ซ้ำกัน

ซึ่งจะแทนที่สูตรอาร์เรย์ที่ยุ่งยาก เช่น {=INDEX(…)} สำหรับรายการที่กรองที่ไม่ซ้ำกัน เปลี่ยนข้อมูลหรือเกณฑ์ และจะกระจายการอัปเดตอย่างราบรื่นสำหรับรายงาน เช่น สินค้าคงคลังของผลิตภัณฑ์ในระดับภูมิภาค

5. หน้าสรุปแบบไดนามิกที่ขับเคลื่อนด้วยเกณฑ์ (รวมตัวกรอง ไม่ซ้ำกัน เรียงลำดับ)

ตอนนี้ ให้รวมฟังก์ชันเหล่านี้ลงในหน้าสรุป/การรายงานขนาดเล็กที่อัปเดตจากเซลล์เกณฑ์บางเซลล์
สร้างแดชบอร์ดระดับภูมิภาค:รายการแบบเลื่อนลงของภูมิภาค (ขับเคลื่อนโดย UNIQUE) รายการภูมิภาคที่กรอง (FILTER) และรายการผลิตภัณฑ์ยอดนิยมตามภูมิภาค (ตัวกรอง + เรียงลำดับ)

ขั้นตอนที่ 1:ดรอปดาวน์ภูมิภาคโดยใช้ UNIQUE

รายการภูมิภาคที่ไม่ซ้ำกันได้ถูกสร้างขึ้นและใช้เพื่อสร้างรายการแบบเลื่อนลง

ขั้นตอนที่ 2:รายละเอียดการขายระดับภูมิภาค

=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")

สูตรนี้กรองข้อมูลการขายตามภูมิภาค เปลี่ยนภูมิภาคจากเมนูแบบเลื่อนลง จากนั้นตารางการขายจะอัปเดตโดยอัตโนมัติ

ขั้นตอนที่ 3:ผลิตภัณฑ์ยอดนิยมในภูมิภาคที่เลือก

ระบุว่าผลิตภัณฑ์ใดขายได้มากที่สุดในภูมิภาคที่เลือก

• สร้างตารางขนาดเล็กที่มีส่วนหัว Product และ Total Revenue
• ใน L4 รับผลิตภัณฑ์ที่ไม่ซ้ำใครที่จำหน่ายในภูมิภาคที่เลือก:

=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))

ซึ่งจะทำให้รายการผลิตภัณฑ์สำหรับภูมิภาคนั้นรั่วไหล

• ใน M4 ให้คำนวณรายได้รวมต่อผลิตภัณฑ์ในภูมิภาคนั้น:

=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)

ซึ่งจะส่งคืนรายการรายได้รวมที่หกซึ่งตรงกับแต่ละผลิตภัณฑ์ใน 68 .

• หากต้องการแสดงโดยจัดเรียงตามรายได้ (จากมากไปน้อย) ให้จัดเรียงคอลัมน์ที่หกสองคอลัมน์เข้าด้วยกัน:

=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)

ที่นี่ 73 สร้างอาร์เรย์สองคอลัมน์ (ผลิตภัณฑ์และรายได้รวม) 80 หมายถึง “เรียงตามคอลัมน์ที่ 2 (รายได้รวม)”; 90 หมายถึงลำดับจากมากไปน้อย

รายงานผลิตภัณฑ์ยอดนิยมแบบไดนามิกใน [ภูมิภาคที่เลือก]:

• เปลี่ยนเมนูแบบเลื่อนลงของภูมิภาคใน B4 โปรดสังเกตว่าข้อมูลสรุปทั้งหมดได้รับการอัปเดต
• เพิ่มข้อมูลใหม่ลงในการขาย และจะถูกเพิ่มลงในรายงาน
• ไม่มีการคัดลอกสูตร ไม่มีการเรียงลำดับด้วยตนเอง ไม่มีการรีเฟรช PivotTable