วิธีจัดการกับภัยเงียบที่ร้ายกาจของ Pivot Table
ริจะใช้ Excel อย่าขัดขา Excel (ตอนที่ 7 ภาค 2)
เมื่อใดงานใดที่ใช้คำสั่งบนเมนูสร้างขึ้น หากมีการเปลี่ยนแปลงข้อมูลใหม่ เมื่อนั้นงานนั้นต้องใช้คำสั่งบนเมนูนั้นใหม่ซ้ำอีก แต่ Pivot Table น่ากลัวยิ่งกว่านั้นอีกเพราะเมื่อเปลี่ยนข้อมูลใหม่แล้วนำไปสร้าง Pivot Table ใหม่ จะพบว่า Excel ไม่ได้นำข้อมูลใหม่มาใช้ในตารางยอดรวมใหม่แต่อย่างใด
เมื่อใดจะสร้าง Pivot Table ซ้ำใหม่ พึงระลึกไว้ว่า ต้องสั่ง Refresh หรือ Refresh All ก่อนทุกครั้ง เพื่อทำให้ Pivot Table ปรับข้อมูลใน cache หรือระบบข้อมูลภายในให้รับรู้ถึงข้อมูลใหม่ก่อนเสมอ ตาราง Pivot ที่สร้างใหม่จึงจะแสดงข้อมูลใหม่ตาม
ถ้าใช้คำสั่งบนเมนูแล้วต้องการให้ Excel ทำงานซ้ำเองโดยอัตโนมัติ ต้องหันไปพึ่ง VBA มาช่วย เช่น
เมื่อต้องการให้ตาราง Pivot ใน Sheet1 ที่สร้างไว้ตรงเซลล์ A3 ทำการ refresh ตัวเอง ต้องใช้ VBA ตามนี้
Set pvtTable = Worksheets(“Sheet1”).Range(“A3”).PivotTable
pvtTable.RefreshTable
หากต้องการ refresh cache
Worksheets(1).PivotTables(1).PivotCache.Refresh
ที่มา
https://docs.microsoft.com/en-us/office/vba/api/excel.pivotcache.refresh?WT.mc_id=M365-MVP-4000499
หากต้องการให้ Excel แสดงรายงานว่าได้ refesh แล้วเรียบร้อย
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
MsgBox “The PivotTable connection has been updated.”
End Sub
เมื่อใดที่ต้องการให้ Excel ทำสิ่งที่ทำไม่ได้ เมื่อนั้นต้องพึ่ง VBA มาเป็นพระเอกขี่ม้าขาวมาช่วย
อย่านึกว่า VBA เป็นเรื่องยากที่เป็นขั้นสูงอะไรแบบนั้น เพียงแต่ต้องมั่นใจก่อนว่าที่ว่า Excel ทำไม่ได้นั้น Excel ทำไม่ได้แล้วจริงๆ ไม่ใช่เพราะผู้ใช้ Excel ไม่รู้ว่าจะใช้ Excel ยังไง