กลับไปหน้า Excel
ทริคExcel

Dynamic Arrays

Dynamic Arrays คือสูตรเดียวที่คืนหลายค่าและเทผลลง (spill) เช่น FILTER, SORT, UNIQUE ใช้สรุปข้อมูลสด ๆ

เริ่มอ่านตรงนี้ก่อน

อธิบายแบบง่าย

Dynamic Arrays คือเรื่องที่ช่วยให้เราใช้จัดตาราง สูตร PivotTable และเตรียมข้อมูลให้เป็นระเบียบก่อนต่อยอดไป Power BIได้ถูกทางขึ้น

ระดับ

เหมาะกับคนที่เริ่มลงมือทำแล้ว

ใช้เมื่อไหร่

คุณต้องการรายการที่กรองหรือเรียงสด ๆ ที่อัปเดตตามข้อมูลต้นทางทันที

อ่านแล้วทำอะไรต่อ

กรองเฉพาะแถวที่ยอดเกิน 1000 เอาชื่อที่ไม่ซ้ำ แล้วเรียงตามตัวอักษร ด้วยสูตรเดียว

เห็นภาพ: ทริคนี้เป็นยังไง

Dynamic Arrays — สูตรเดียว ผลลัพธ์ล้น (spill) หลายเซลล์
fx =FILTER(A2:B5, B2:B5>500)

ข้อมูล

ลูกค้ายอดซื้อ
สมชาย1200
มาลี450
วิชัย980
ฝน300

ผลลัพธ์ spill — พิมพ์สูตรแค่เซลล์เดียว

…
…

ขอบเขียวซ้าย = เซลล์ที่พิมพ์สูตร แถวที่เหลือ Excel เติมให้เอง

พิมพ์ FILTER เซลล์เดียว — ผลลัพธ์ล้น (spill) ลงหลายแถวเอง (เล่นอัตโนมัติ)

ใช้ตอนไหน?

  • คุณต้องการรายการที่กรองหรือเรียงสด ๆ ที่อัปเดตตามข้อมูลต้นทางทันที
  • คุณต้องการดึงรายการค่าที่ไม่ซ้ำ เช่น รายชื่อลูกค้าหรือหมวดหมู่
  • คุณอยากลดการก๊อปปี้สูตรซ้ำหลายเซลล์ให้เหลือสูตรเดียว

ความหมาย

Dynamic Array (อาเรย์ที่ขยายเองได้) คือสูตรเดียวที่คืนผลลัพธ์ได้หลายเซลล์พร้อมกัน แล้วเทผล (spill) ลงในช่วงที่ติดกันโดยอัตโนมัติ ฟังก์ชันยอดนิยมคือ FILTER (กรอง), SORT (เรียง) และ UNIQUE (เอาค่าไม่ซ้ำ)

หน้าตาของสูตร spill

เขียนสูตรเดียวในเซลล์เดียว ผลลัพธ์จะเทลงเซลล์ข้างเคียงเอง อ้างถึงทั้งช่วงด้วยเครื่องหมาย # ต่อท้าย

=SORT(UNIQUE(FILTER(Tbl[Customer], Tbl[Amount]>1000)))
=A2#

ตัวอย่างการใช้

รายชื่อลูกค้าที่ซื้อเกินเกณฑ์

กรองเฉพาะแถวที่ยอดเกิน 1000 เอาชื่อที่ไม่ซ้ำ แล้วเรียงตามตัวอักษร ด้วยสูตรเดียว

=SORT(UNIQUE(FILTER(Sales[Customer], Sales[Amount]>1000)))