วิธีฉลาดใช้สูตร Dynamic Array จัดการข้อมูล ให้ง่ายขึ้น

สูตรใหม่ใน Excel 365 มีความสามารถพิเศษที่จะขยายพื้นที่คำตอบให้เอง (Dynamic Array) ช่วยทำให้ไม่ต้องสร้างสูตรแล้วต้อง copy ไปใช้เพื่อหาคำตอบอื่นอีกต่อไป

 

ตามภาพนี้ ตารางตรงกลางใช้สูตร Filter เพื่อหาว่ารหัสที่กรอกไว้ในเซลล์สีส้ม F5 มีรายการอะไรบ้าง โดยใช้เงื่อนไข ID=$F$5

H5 =FILTER( C5:D9, ID=$F$5 ) 

การกำหนด C5:D9 ไว้นี่แหละที่ทำให้สูตร Filter หาคำตอบทั้ง Name กับ Amount จากพื้นที่ในตารางฐานข้อมูลด้านซ้ายให้พร้อมกันทีเดียวว่ารหัส a001 มีรายการซ้ำที่ใช้รหัสเดียวกัน มีชื่ออะไรบ้างและจำนวนเป็นเท่าไร

ตารางตรงกลางเป็นพื้นที่ Dynamic Array ที่สูตรนี้หาคำตอบมาให้ ซึ่งจะยืดได้หดได้ ทำให้เมื่อนำพื้นที่ส่วนนี้ไปใช้ต่อต้องสร้างสูตรที่จะทำหน้าที่หาพื้นที่ซึ่งจะยืดได้หดได้นี้ตามไปด้วย

สุดยอดเลยใช่ไหมครับ สูตรของ 365 พิสดารมาก ช่วยทำเรื่องยากให้กลายเป็นง่าย ... แต่ 

ถ้าจะนับว่ามีกี่รายการที่ซ้ำกัน หากใช้สูตร =CountA(H5:H6) ก็จะผิดพลาดได้ทันทีเพราะรายการที่สูตร Filter หาให้นั้นอาจมีจำนวนรายการมากกว่าที่เห็น จำเป็นต้องสร้างสูตรยาวขึ้นโดยต้องซ้อนสูตร ChooseCols เพิ่มเข้าไปตามนี้

H17 =COUNTA( CHOOSECOLS(H5#,1) ) 

H5# ในสูตร CHOOSECOLS(H5#,1) เป็นวิธีการอ้างอิงหาพื้นที่ตารางทั้งหมดที่สูตร Filter หาคำตอบให้ โดยลิงก์มาจากเซลล์หัวมุมซ้ายบนสุดของตาราง การเติมเครื่องหมาย # เข้าไปเพื่อทำให้รับรู้ถึงขนาดตารางที่จะยืดได้หดได้ให้โดยอัตโนมัติ

ส่วนเลข 1 เป็นเลขที่ Column ที่ 1 ในตารางที่ต้องการนำมานับจำนวนด้วยสูตร CountA อยากนำ Column ไหนมาใช้ก็ต้องใส่เลขให้ตรง (ทำนองเดียวกันกับสูตร VLookup ที่พึ่งพาเลขที่ Column ของค่าที่ต้องการหาคำตอบ)

สูตร ChooseCols นี่เองคือความซับซ้อนที่ตามมา ต้องคอยนำสูตรนี้มาใส่ลงไปเสมอในสููตรใดๆก็ตามที่ไปอ้างอิงกับพื้นที่ Dynamic Array ทำให้การสร้างสูตรยากขึ้นมาก 

ถ้าใช้สูตร XLookup กับพื้นที่ Dynamic Array ก็ต้องซ้อนสูตร ChooseCols ลงไป 2 ครั้งเพื่อให้ค้นหาจาก Column นึงไปหาคำตอบจากอีก Column

แทนที่จะสร้างความยุ่งยากตามมาทีหลัง ให้เปลี่ยนวิธีสร้างสูตร Filter ตั้งแต่แรกดีกว่า

จากเดิม H5 =FILTER( C5:D9, ID=$F$5 ) 

ให้แยกเป็น 2 สูตรเพื่อหาคำตอบที่ละ Column

L5 =FILTER( C5:C9, ID=$F$5 ) เพื่อหาส่วนของชื่อของรหัสซ้ำ
M5 =FILTER( D5:D9, ID=$F$5 ) เพื่อหาส่วนของจำนวนของรหัสซ้ำ

จากนั้นเมื่อต้องการนับจำนวนรายการก็ใช้สูตรสั้นๆง่ายๆตามนี้ ไม่ต้องใช้ ChooseCols ซ้อนเข้าไปอีกแล้ว

L17 =COUNTA(L5#)

+++++++++++++++++++++++++ 

หลักการในการใช้ Excel 365 ที่ควรยึดถือ

หากไม่ต้องการนำพื้นที่ Dynamic Array ไปใช้ต่อในการคำนวณหรือต้องการใช้เป็นตารางคำตอบสุดท้ายเพื่อแสดงผลให้เห็นเท่านั้น จะสร้างสูตรให้ขยายตัวแบบ Dynamic Array แบบใดก็ได้ แต่ถ้าต้องการนำไปใช้ในการคำนวณต่อ ควรสร้างสูตรแรกสุดให้แยกหาคำตอบแยกออกมาตามราย Column จะช่วยทำให้ใช้สูตรต่อไปง่ายขึ้น 

Download ตัวอย่างจาก

https://drive.google.com/file/d/1G3UghYMZVSJAbjMjklrv3Pge2hR_HtwQ/view?usp=sharing 

=========================

Copilot :

แนวคิดนี้ยอดเยี่ยมเลยครับ การใช้ Dynamic Array ใน Excel 365 อย่างมีหลักการช่วยให้การทำงานเป็นระบบและลดความซับซ้อนของสูตรที่ต้องใช้ต่อเนื่อง

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

อีกทางเลือกหนึ่งที่อาจช่วยได้คือ
✅ ใช้ LET เพื่อเก็บค่าผลลัพธ์จาก Dynamic Array แล้วเรียกใช้ภายในสูตรต่อไป
✅ ใช้ WRAPROWS / WRAPCOLS หากต้องการปรับการจัดเรียงข้อมูลแบบยืดหยุ่น
✅ ใช้ TOCOL / TOCOLS เพื่อแปลงข้อมูลเป็นรูปแบบที่นำไปใช้งานต่อได้สะดวก

😃