Excel 365 Dynamic Dashboards : Rev 05 แนวทางใหม่ เริ่มต้นย่อยฐานข้อมูลให้เล็กลงและใช้สูตรสั้นลง

ขอให้สังเกตว่าในแฟ้ม Rev 01-04 นั้น ใช้สูตร GroupBY กับ PivotBY โดยไปสรุปข้อมูลจาก FoodData ซึ่งเป็นฐานข้อมูลทั้งหมด ซึ่งถ้ามีรายการนับแสนนับล้านรายการล่ะ Excel ต้องคำนวณช้าลงอย่างแน่นอน

ยิ่งกว่านั้นใน Rev 04 ใช้สูตร Filter กรองข้อมูลจาก FoodData ด้วยสูตรย้าวยาวตามภาพด้านซ้าย เห็นแล้วอึ้งเลยใช่ไหม นอกจากยาวแค่นั้นไม่พอ ผลที่สูตรนี้หาให้จะกระจายตัวออกไปแบบ Dynamic Array ทำให้เวลานำข้อมูลแต่ละ Column ไปใช้ต่อต้องเสียเวลาใช้สูตร ChooseCols(เลขที่ column) เพื่อดึงข้อมูลจาก Column นั้นๆ (คล้ายกับการใช้สูตร VLookup ที่เราเบื่อนับเลขนั่นไง)

แทนที่จะใช้สูตรย้าวยาว หันมาใช้สูตร Lambda เพื่อย่อสูตรให้สั้นลง โดยเริ่มต้นจากไปตั้งชื่อสูตรใหม่ชื่อ RFilter ให้ Refers to

=LAMBDA(DataRange,
IFERROR(
FILTER(DataRange,
(OrderDate>=IF($B$5=0,OrderDate,$B$5))*
(OrderDate<=IF($B$6=0,OrderDate,$B$6))*
Key($C$5:$C$6,Region)*
Key($D$5:$D$8,City)*
Key($E$5:$E$8,Category)*
Key($F$5:$F$8,Product)*
(Quantity>=IF($G$5=0,Quantity,$G$5))*
(Quantity<=IF($G$6=0,Quantity,$G$6))*
(Sales>=IF($H$5=0,Sales,$H$5))*
(Sales<=IF($H$6=0,Sales,$H$6))*
(Year>=IF($I$5=0,Year,$I$5))*
(Year<=IF($I$6=0,Year,$I$6))*
(Month>=IF($J$5=0,Month,$J$5))*
(Month<=IF($J$6=0,Month,$J$6))
),
"""")
)

เวลาจะกรองหาจาก range วันที่ OrderDate เช่น ก็ใช้สูตร
=RFilter(OrderDate)

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

พอจะนำพื้นที่แต่ละ Column ที่ได้นี้ไปใช้ ให้ตั้งชื่อ Range แบบ Dynamic Array โดยอ้างอิงกับเซลล์แรกตามด้วย # ก็จะสะดวกขึ้นมาก พร้อมจะนำไปใช้กับสูตร XLookup หรือ Sum, SumProduct, Index

โปรดติดตามในตอนต่อไป ซึ่งจะเริ่มต้นใช้ Excel 365 ให้เต็มที่

Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1MmcR9VJoDoBfiWVrU9Tb53N92qTNAHKn/view?usp=sharing