Excel 365 Dynamic Dashboards

ในที่สุดก็หาทางทำให้ทั้งตัวข้อมูลและกราฟ Dynamic เปลี่ยนแปลงตามต้องการได้แล้ว

พอคลิกเลือกชื่อ Category ตรงตาราง Your Choices มุมซ้ายของชีทจะพบว่าทั้งยอดรวมและกราฟเปลี่ยนแปลงให้ทันที จะได้เลิกใช้ PivotTable ที่ต้องเสียเวลามาใช้ Filter/Slicer ตามหลังอีกต่อไป

👉 ใช้สูตร GroupBY หายอดขายรวมแบ่ง Region ตามเขต East กับ West เพื่อนำไปแสดงภาพ Sales Market Shares ด้วย Pie Chart

=GROUPBY( Region, Sales, SUM,,0,, Key(B5:B8,Category) )

👉 ใช้สูตร PivotBY เพื่อหายอดขายรวมแบ่งตามเขตและชื่อ Category ที่คลิกเลือก

=PIVOTBY( Region, Category, Sales, SUM,0,0,,0,, Key(B5:B8,Category) )

🧐 ตอนสร้างกราฟเองจากผลที่สูตรนี้หาได้ ไม่ยากเลย แต่มาพบว่าใน Excel 365 ไม่ยอมให้แก้ไขสูตร Series บนตัวเส้นกราฟได้โดยตรงอีกต่อไป ถามเจ้า Copilot อยู่ 1 คืน จึงพบว่าต้องเข้าไปแก้ใน Data Sources ของตัวกราฟแทนและที่สำคัญต้องอ้างถึงชื่อแฟ้มนี้ไว้ด้วยคู่กับชื่อสูตร Offset ที่ทำหน้าทีแยกแต่ละส่วนจาก Dynamic Array ที่ได้จากสูตร PivotBY เช่น

ส่วนของ Axis Label ต้องยืดได้หดได้ตามการคลิกเลือก Category โดยตั้งชื่อว่า CatLabel ที่มีขนาดตามค่าในเซลล์ C2 ที่นับว่าคลิกเลือก Category ไว้กี่ตัว

=OFFSET( $H$20, 0, 1, 1, $C$2)

แค่นี้ยังไม่พอตอนเข้าไปเปลี่ยนใน Data Sources ของ Axis Label ตั้งนำชื่อแฟ้มใส่นำหน้าชื่อ CaltLabel ตามนี้อีกด้วย

365Dashboard.xlsb!CatLabel

ส่วนค่าของแต่ละ Series ก็ทำแบบเดียวกัน

นี่แหละครับ Dynamic Dashboards ที่ยืดหยุ่นแบบสุดๆ สามารถดัดแปลงกราฟได้ตามใจชอบสะดวกกว่าการใช้ Pivot Chart ที่ไม่ยืดหยุ่นเอาเสียเลย

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

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

จากกราฟที่แสดงในชีทนี้ สงสัยไหมว่า ทำไมเขต East จึงทำยอดขายได้ดีกว่า West
ไม่ได้ดีกว่าแค่ในยอดรวมทั้งหมดเท่านั้น แต่ทุก Category ก็ขายได้เหนือกว่าเขต West ทั้งนั้น

อะไรคือสาเหตุที่ทำให้ยอดขายต่างกัน ทั้งๆที่ขายสินค้าเหมือนกันเช่นนั้นหรือ

โปรดติดตามตอนต่อไป ... นี่คือบทที่ต้องทิ้งไว้ให้คิดครับ