อยากรู้จักแก่นของ PivotTable ต้องรู้ว่า GetPivotData พิสดารสุดๆแค่ไหน

ตรงมุมซ้ายของตารางในภาพเหล่านี้แสดงพฤติกรรมของสูตร GetPivotData ให้ดูว่า ไม่ว่าจะเปลี่ยนโครงสร้างของตาราง PivotTable ด้านล่างไปเป็นยังไง สูตรก็ยังหาค่าคงเดิมไว้เสมอ ไม่ว่าตาราง PivotTable จะยืด หด หรือย้ายตำแหน่งเซลล์ที่เคยหาค่าไว้

สาเหตุที่เป็นแบบนี้ เพราะสูตร GetPivotData ไม่ได้หาค่าจากตาราง PivotTable แต่หาค่าจาก Pivot Cache ที่มองไม่เห็น

Pivot Cache คือ ตารางคำนวณที่ Excel แอบคิดไว้ให้เรียบร้อยตั้งแต่แรกเริ่มสร้างตาราง PivotTable โดย Cache ทำให้แฟ้มใหญ่ขึ้น และจะเก็บยอดคำนวณไว้ละเอียดมากกว่าที่ตาราง PivotTable แสดงออกมาให้เห็นเสียอีก

ตราบใดที่ตาราง PivotTable ยังเลือกแสดงรายการของยอดขาย Sales ตามรายเดือน Month หรือรายภาค Region ตราบนั้นสูตร GetPivotData ก็ยังหายอดรวมออกมาให้ได้เสมอ

พอทราบข้อดี ข้อเสีย และข้อควรระวังเหล่านี้แล้ว วิธีนำไปใช้งานจะเป็นอย่างไรล่ะ

☝️ อย่าไปสนใจรูปร่างหน้าตาของ PivotTable แต่ให้ลากทุกเรื่องที่คิดว่าอยากจะนำยอดไปใช้ลงไปยำไว้ในตาราง PivotTable แล้วลิงก์ตัวเลขที่ต้องการออกไปสร้างรายงานด้วยสูตร GetPivotData

จัดการสร้าง Executive Report ตามที่หัวหน้าต้องการ หากหัวหน้าอยากหาค่าของรายการอื่นก็ให้เปลี่ยนโครงสร้างสูตร GetPivotData จากเดิมที่ในวงเล็บใส่คำว่า Jan / South แบบเป็นค่าคงที่เอาไว้

=GETPIVOTDATA("Sales",$E$9,"Month","Jan","Region","South")

แก้ใหม่เป็นการลิงก์มาจากเซลล์รับตัวแปรแทน

=GETPIVOTDATA("Sales",$E$9,"Month",B15,"Region",B16)

พูดง่าย ๆ คือ PivotTable เป็นโรงงานผลิตข้อมูล ส่วน GETPIVOTDATA เป็นสายส่งที่ดึงของออกมาใช้

Download แฟ้มไปลองกันครับ
https://docs.google.com/spreadsheets/d/1U9AU201T44DHpL4vXRVY5qvS_yHx6j6z/edit?usp=sharing&ouid=116915095293786385908&rtpof=true&sd=true