ฉลาดสร้าง Excel 365 Dashboards แนวใหม่ จะใช้สูตร Filter ก่อน GroupBy หรือ GroupBy ก่อน Filter ดีกว่ากัน

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

แต่ก่อนโน้นต้องเสียเวลาไปสร้างสูตร Offset เพื่อเลือกดึงเฉพาะช่วงรายการมาใช้ พอถึงยุคที่ใช้ Excel 365 มีสูตรใหม่ๆให้ใช้ทำงาน โดยเฉพาะอย่างยิ่งให้ใช้แทน PivotTable แถมไม่ต้องแตะ VBA อีกต่อไป

ตัวอย่างนี้มีฐานข้อมูลที่เก็บไว้หลายปี นับวันยิ่งมีจำนวนรายการเพิ่มขึ้นไปเรื่อยๆ

ภาพบน ใช้สูตร Filter ช่วยกรองข้อมูลให้ลดเหลือแค่รายการของปี 2019 เท่านั้นก่อน พอลดจำนวนรายการเหลือเฉพาะปีเดียวแล้วจึงใช้สูตร GroupBY หายอดขายแต่ละเดือน

หรือ จะเลือกใช้อีกแบบ

ภาพล่าง ใช้สูตร GroupBy เพื่อสรุปยอดขายแต่ละเดือนของทุกปีขึ้นมาก่อนแล้วจึงใช้สูตร Filter กรองให้เหลือแค่ปี 2019 ปีเดียว

😵‍💫 คิดว่าจะใช้สูตร Filter ก่อน GroupBy หรือ GroupBy ก่อน Filter ดีกว่ากันครับ

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

ตัวอย่างนี้ผมหาทางใช้ Dynamic Array ร่วมกับการใช้ Table ช่วยให้สูตรทำงานรองรับกับจำนวนรายการที่เพิ่มขึ้นในอนาคต จะได้เลิกใช้สูตรที่อ้างอิงทั้ง column B:B แบบนี้กันเสียที และทำให้กราฟที่จะนำไปแสดงเป็น Dashboards กลายเป็น Dynamic Chart ตามไปให้เอง

ลองคลิกเปลี่ยนเลขปีในเซลล์ Q2 ที่ใส่พื้นสีเขียวไว้จะพบว่ากราฟปรับตามให้ทันที

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

ถ้าเลือกใช้สูตร Filter ก่อน GroupBy น่าจะเหมาะกว่าครับ เพราะแม้ตัวฐานข้อมูลเหลือเฉพาะรายการในปีที่ต้องการ แต่ยังมีข้อมูลเรื่องอื่นสามารถนำไปใช้ต่อได้อีก

แต่ถ้าเลือกใช้ GroupBy ก่อน จะทำลายฐานข้อมูลให้เหลือแค่ยอดขายในแต่ละปีเท่านั้น ทำให้หมดโอกาสนำไปใช้หาเรื่องอื่น

ไม่ใช่แค่สำหรับการทำ Dashboard เท่านั้น แนะนำให้ใช้สูตร Filter ก่อนที่จะนำข้อมูลไปใช้ต่อเสมอครับ จะช่วยให้ PivotTable หรือสูตรใดๆทำงานเร็วขึ้น

การอ้างอิงพื้นที่จากตารางที่ได้จากสูตร Filter จะเป็น Dynamic Array ในตัว ให้ใช้สูตร =Cell# ซึ่งเป็นเซลล์หัวมุมซ้ายสุดเซลล์เดียว เมื่อจะแตกออกมาเป็นแต่ละ Column ให้ใช้สูตร ChooseCols ช่วย

กรณีใช้สูตร Filter ก่อน GroupBy

I3 =FILTER( SalesTBL, YEAR(Date)=Q2 )

SalesTBL เป็นพื้นที่รายการในตารางฐานข้อมูลทั้งหมด
YEAR(Date)=Q2 เป็นเงื่อนไขหาตำแหน่งรายการที่มีปีเท่ากับเลขปีในเซลล์ Q1

P5 =GROUPBY( MONTH( CHOOSECOLS(I3#,6) ), CHOOSECOLS(I3#,5), SUM,0,0)

CHOOSECOLS(I3#,6) เลือก column ที่ 6 ที่เป็นวันเดือนปีออกมาใช้จัดกลุ่มโดยหาเลขเดือนต่อด้วยสูตร Month
CHOOSECOLS(I3#,5) เลือก column ที่ 5 ที่เป็นยอดขายมาใช้หายอดขายรวม

กรณีใช้ GroupBy ก่อน Filter
.
I3 =GROUPBY( HSTACK( YEAR(Date), MONTH(Date) ), Sales, SUM )
HSTACK( YEAR(Date), MONTH(Date) ) กำหนดเงื่อนไขให้จัดกลุ่มตามรายปีกับรายเดือน
.
P5 =FILTER( I3#, CHOOSECOLS(I3#,1)=Q2 )
จัดการกรองตารางที่มีพื้นที่เริ่มจากเซลล์ I3 โดยใช้เงื่อนไขเทียบเลขปีจาก Column 1 ว่าตรงกับเลขปีที่ต้องการในเซลล์ Q2