เคยสงสัยไหมครับว่า ทำไมเวลาข้อมูลในฐานข้อมูลเปลี่ยน ข้อมูลใน Pivot Table กลับไม่เปลี่ยนตาม?

ทำไม Calculated Field ที่สร้างจาก Pivot Table 1 ถึงไปโผล่ใน Pivot Table 2 ด้วย?

ทำไม Slicer ที่สร้างจาก Pivot Table 1 ถึงสามารถควบคุม Pivot Table 2, Pivot Table 3 ได้?

นั่นเป็นเพราะ Pivot Cache !!

Pivot Cache คือ “ตัวกลาง” ที่เชื่อมต่อกับฐานข้อมูล และ Pivot Table

โดยข้างนึงเชื่อมต่อกับฐานข้อมูล

อีกข้างนึงเชื่อมต่อกับ Pivot Table (และ Pivot Chart) ที่สร้างขึ้นทั้งหมด

พูดง่ายๆก็คือ Pivot Table กับฐานข้อมูลไม่ได้เชื่อมต่อกัน

ทำไมคนส่วนใหญ่ถึงไม่รู้จัก Pivot Cache น่ะหรือครับ?
เพราะ Pivot Cache ถูกทำให้มองไม่เห็น
ต่อให้หา ก็หามันไม่เจอ
เพราะมันอยู่ในหน่วยความจำ

Pivot Cache เกิดขึ้นตอนสร้าง Pivot Table ตารางแรก
พอสร้าง Pivot Table 2, Pivot Table 3 ทุกตารางจะเชื่อมต่อกับ Pivot Cache นี้
เราไม่ได้ทำ เพราะ Excel จัดการให้เสร็จสรรพ
นั่นจึงเป็นเหตุผลว่า ทำไม Calculated Field ที่สร้างใน Pivot Table 1 ถึงไปโผล่ในทุกๆตาราง
ทำไมสร้าง Slicer ใน Pivot Table 1 แล้วสามารถบังคับ Pivot Table 2 และ Pivot Table 3 ได้
เพราะ Calculated Field และ Slicer ไม่ได้ผูกติดกับ Pivot Table 1
แต่ผูกติดกับ Pivot Cache นั่นเอง !

แล้วจะมีโอกาสที่มี Pivot Cache 2 อันไหม?
ถ้าเราใช้ MS Excel เวอร์ชันตั้งแต่ 2007 ขึ้นไป และสร้าง Pivot Table ด้วยวิธีปกติ โอกาสเกิดขึ้นเป็นศูนย์เลยก็ว่าได้
เพราะ Excel จะบังคับให้ทุก Pivot Table เชื่อมกับ Pivot Cache เพียงอันเดียว
(ก่อนหน้านี้ Excel มีออปชันให้สร้าง Pivot Cache มากกว่า 1 อันได้ แต่ปัญหาตามมาเป็นกระบุง)

แล้วถ้าต้องการสร้าง Pivot Cache 2 อัน จะทำได้ไหมน่ะหรือครับ?
ทำได้ครับ แต่ต้องสร้าง Pivot Table ด้วย Pivot Wizard
(ไม่ได้สร้างด้วยวิธีปกติ)
ซึ่งปุ่ม Pivot Wizard นี้ถูก Microsoft เอาออกไปจากริบเบินแล้ว
ถ้าจะเรียกขึ้นมา ต้องดึงจาก All Command

หรือเรียกใช้โดยการกด Alt+D+P จะมีหน้าต่างนี้โผล่ขึ้นมา

แต่ถ้าไม่ใช่เคสจำเป็นจริงๆ (เช่น ต้องการสร้าง Calculated Items และ Group Selection จากฐานข้อมูลเดียวกัน) ไม่แนะนำให้ใช้ครับ
เพราะนอกจากทำให้ไฟล์ใหญ่ขึ้นแล้ว
“กระบุง” ที่เคยเกิดจะกลับมาอีกครั้งครับ ^__^

ถ้าตารางฐานข้อมูลเกิดการอัพเดต การอัพเดตนั้นจะไม่มีผลใดๆกับ Pivot Table เลย
เพราะสิ่งที่อัพเดตคือฐานข้อมูล ไม่ใช่ Pivot Cache
แล้วจะอัพเดต Pivot Cache ได้ยังไงน่ะหรือครับ?
การอัพเดต Pivot Cache ก็คือการคลิกขวาที่ Pivot Table ใดๆ แล้วเลือก Refresh นั่นเอง
(อาจกด Alt+F5 ก็ได้ แต่อย่ากดผิดเป็น Alt+F4 ล่ะเพราะนั่นคือการปิดโปรแกรมนะ ^^)
ทำครั้งเดียว แล้วจะมีผลกับทุก Pivot Table

จริงๆแล้วก็แค่คลิกเดียว แต่ในทางปฏิบัติมีโอกาสลืมสูง (มาก)
กลายเป็นว่า ข้อมูลใน Pivot Table กับฐานข้อมูลอาจไม่ตรงกัน
นั่นจึงเป็นจุดอ่อนข้อใหญ่ของ Pivot Table

อันที่จริง ยังมีวิธีแก้ไขจุดอ่อนนี้
ที่มักทำกันก็คือ ใช้ VBA ช่วย
เชื่อมการอัพเดต Pivot Cache เข้ากับเหตุการณ์บางอย่าง (เช่น Selection Change)
แต่ก็ต้องแลกมากับการ Enable Macro ซึ่งบางคนอาจไม่ชอบ
และบางหน่วยงานก็ป้องกันไม่ให้เปิดไฟล์ใดๆที่มี VBA เสียด้วยสิ…

แม้ว่าเรื่องการอัพเดต Pivot Cache นี้จะเป็นจุดอ่อนของ Pivot Table
แต่ถ้าลองชั่งกับความรวดเร็วและความสะดวกในการใช้งาน จุดอ่อนนี้กลายเป็นเรื่องขี้ผงไปเลย
เพียงแต่… อย่าลืมกด Refresh ละกันครับ ^__^

อ้อ! ถ้าสร้าง Pivot Table แล้วฐานข้อมูลนั้นจะไม่ถูกอัพเดตอีก ลบตารางฐานข้อมูลนั้นทิ้งไปเลยก็ได้
เพราะถ้าเก็บไว้ทั้งฐานข้อมูล และทั้ง Pivot Cache ไฟล์จะมีขนาดใหญ่โดยไม่จำเป็น
(ลบเลย ไม่ต้องกลัว)
แต่ถ้าฐานข้อมูลนั้นมีโอกาสถูกอัพเดต ก็ให้เก็บฐานข้อมูลไว้อีกไฟล์นึง แล้วแยกเก็บ Pivot Table ไว้อีกไฟล์นึง
ถ้าฐานข้อมูลนั้นเกิดการอัพเดต ค่อยเปิดทั้ง 2 ไฟล์แล้วคลิก Refresh เพื่ออัพเดต Pivot Cache ก็ได้ครับอย่า “เก็บเธอไว้ทั้งสองคน” เพราะไฟล์จะใหญ่เกินไป ^^

จะเอา Pivot Cache ออกจาก Pivot Table ได้ยังไงน่ะหรือครับ?
ทำได้ครับ โดยคลิกขวาที่ Pivot Table/ Pivot Table Options

เลือกแท็ป Data แล้วยกเลิกการเลือก Save source data with file (ไม่ต้องติ๊ก)

จากนั้นให้เซฟไฟล์ เป็นอันเสร็จพิธี
ถ้าส่ง Pivot Table นี้ (ที่ไม่มี Pivot Cache) ให้กับคนอื่น จะเสมือนว่าส่งตารางที่เป็น Paste Value ไปให้
คนรับไฟล์จะไม่สามารถปรับอะไรใน Pivot Table ได้เลย เสมือนหนึ่งว่าให้ดูได้อย่างเดียว
ใช้เป็นเทคนิคป้องกันข้อมูลรั่วไหลได้ครับ ^__^

.

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^