ปฏิเสธไม่ได้ว่า Pivot Table เป็นเครื่องมือที่ใช้สรุปข้อมูลได้สุดยอดและง่ายมากๆ แต่ปัญหาหลักๆ ของการทำ Pivot ไม่ได้เกิดขึ้นขณะทำ Pivot แต่อยู่ที่ขั้นตอนเตรียมข้อมูลก่อนจะเอาไป Pivot ต่างหาก
ซึ่ง Power Query ก็เข้ามาตอบโจทย์นี้ได้ และถ้าใครได้ลองใช้แล้วคงพบว่ามันเป็นเครื่องมือที่เตรียมข้อมูลได้สุดยอดมากๆ (ผมว่ามันเหมือนกับความรู้สึกที่เราได้รู้จักกับ Pivot Table ใหม่ๆนั่นแหละ)
อย่างไรก็ตาม ทั้ง Pivot Table และ Power Query ต่างก็ยังมีข้อจำกัดสำคัญ คือ มันจะต้องมีการ Refresh ก่อน ถึงจะดูดข้อมูลที่อัปเดทล่าสุดมาจัดการต่อ แต่จะทำยังไงให้มันอัปเดทอัตโนมัติ มาดูกันครับ
สารบัญ
ความสัมพันธ์ของ Pivot Table กับ Power Query Refresh
ถ้าเราผูก Pivot Table กับ Power Query เอาไว้ด้วยกันแล้ว โดยวิธีใดวิธีหนึ่ง คือ
- เอา Power Query Close & Load to… Pivot Table
- หรือ สร้าง Pivot Table จาก External Connection ที่เป็น Power Query โดยตรง (ไม่ใช่ Data ที่เป็น Table/Range ปกติ)
เราจะสามารถกด Refresh ที่ Pivot Table ตัวเดียวก็พอ เพราะมันจะดูเองว่าปลายทางนั้นต้องการข้อมูลจากไหน แล้วมันจะ Refresh ต้นทางเอง
ดังนั้นบทความนี้จะมาหาวิธีที่จะทำให้เราไม่ต้องมานั่งกด Refresh เองว่าจะทำยังไงได้บ้าง? เอาล่ะมาดูกันเลย
ป.ล. จริงๆ เว็บฝรั่งอันนี้เขียนไว้ดีมาก แต่มีหลายประเด็นที่ยังติดปัญหากับ Power Query อยู่ ผมจึงขอเขียนเพิ่มเติมให้ครอบคลุมยิ่งขึ้นอีกครับ
วิธีที่ 1 : ให้คนอื่นกดให้ (เช่น ลูกน้อง)
วิธีนี้ง่ายสุดแล้วล่ะ 555 แต่ก็มีโอกาสผิดพลาดเหมือนกันนะ เช่น ลืม Refresh หรือกด Refresh ไม่ถูกวิธี (เช่น Refresh ผิดที่) ดังนั้นใครใช้วิธีนี้อยู่ ก็อย่าลืมลองศึกษาวิธีอื่นนะครับ
วิธีที่ 2 : ตั้ง Auto Refresh ตอนเปิดไฟล์
ทั้ง Pivot Table และ Power Query เอง สามารถตั้งค่าให้มีการ Auto Refresh ตอนเปิดไฟล์ Excel ขึ้นมาได้ ซึ่งทำง่ายมากนั่นคือ
Pivot Table
คลิ๊กขวาที่ Pivot Table -> Pivot Table Options -> Data -> ติ๊ก Refresh data when opening the file
ตัว Power Query ก็สามารถกด คลิ๊กขวา Property ได้เช่นกัน
วิธีที่ 3 : ตั้ง Auto Refresh ทุกๆ xx นาที
วิธีนี้เราจะตั้งได้ที่ตัว Query ที่เอาไปผูกกับ Pivot Table เอาไว้ โดยให้ติ๊กดังนี้ และ set เวลาที่ต้องการ (ในที่นี้ผม set ทุกๆ 1 นาที)
พอครบ 1 นาที เจ้า Query นี้จะอัปเดทตัวเอง ซึ่งทำให้ Pivot Table อัปเดทไปด้วย เช่น เดิมเป็นแบบนี้
ผมไปแก้ลูกค้าเบอร์ 1 ให้เป็นไต้หวัน แทนที่จะเป็นไทย
พอรอครบ 1 นาทีปุ๊ป ใน Pivot Table ผลลัพธ์จะเปลี่ยนเองเลย
วิธีที่ 4 เขียน VBA ให้ Auto Refresh
ถ้าการสั่ง Auto Refresh ทุกๆ xx นาทีมันยังไม่ Work (เช่น ถี่มากไป refresh โดยไม่จำเป็น หรือ refresh ไม่ทันใจ) งั้นก็ให้ VBA มัน Auto Refresh ให้ละกัน!
ซึ่งวิธีนี้ทำได้หลากหลายมาก จึงยาวที่สุดเลย (แต่แค่วิธีบนๆ ก็ช่วยได้แล้วล่ะ)
Concept ของ VBA
concept สำคัญคือ เราสามารถเช็คว่า ถ้ามี Event บางอย่างเกิดขึ้น เราจะสั่งให้ทำ Action บางอย่างได้ (และเจ้า action นี่แหละที่ปกติแล้ว record macro เอาได้) ดังนั้น
- Event คือ มีการแก้ข้อมูลใน Table ต้นทางตัวใดตัวหนึ่ง
- Action ในที่นี้เป็นไปได้ 2 แบบ คือ
- Refresh Pivot Table (ตัวไหนก็ได้ ที่ดึงค่าจาก Query ผลลัพธ์)
- Refresh เจ้า Query ที่ผูกกับ Pivot Table (แล้ว Pivot ก็จะอัปเดทเอง)
เดี๋ยวเราจะมาทำส่วน Action ดูก่อน
เนื่องจากผมไม่รู้ว่า Code VBA ต้องเขียนยังไง ดังนั้นเราลอง Record Macro ดู โดยไปที่ Developer –> Record Macro เพื่อให้มันสร้าง Code ให้เราเอง
Refresh Pivot Table
ลองกด Record Macro แล้วตั้งชื่อว่า RefreshAll ดู
จากนั้นไปที่ Data -> Refresh All
แล้วกดปุ่ม Stop Recording ที่ Developer -> Stop Recording เพื่อหยุดการบันทึก
หมายเหตุ : ถ้าใครลอง Record Macro แล้วลองกด Refresh ที่ Pivot Table ตัวที่สร้างจาก Power Query จะพบว่า Macro มันไม่บันทึก action การ Refresh ที่ Pivot Table ให้เลย แต่ถ้าเราลอง Record Macro การ Refresh Pivot Table ที่ไม่ได้สร้างจาก Power Query มันจะบันทึก Macro ได้ตามปกติ
ทดสอบ Event การเลือก Sheet ผลลัพธ์
ไอเดียของส่วนนี้คือ เมื่อไหร่ก็ตามที่เราคลิ๊กที่ดู Sheet ผลลัพธ์ จะให้เกิดการ Refresh ทันที (การคลิ๊กที่ sheet ผลลัพธ์แสดงว่ากำลังอยากดูผลสรุป)
ดังนั้นเดี๋ยวเราจะสร้าง event ที่เรียกว่า activate ขึ้นมา ซึ่งคำว่า activate คือ เหตุการณ์ที่เราคลิ๊กที่ชีทอื่นอยู่ แล้วเอา Mouse มาคลิ๊กที่ Sheet ที่เรากำหนดนั่นเอง
วิธีทำ คือ ให้เรากด Alt+F11 เพื่อเข้าไปใน VBA Editor จากนั้นดับเบิ้ลคลิ๊กที่ Sheet PivotResult แล้วเปลี่ยน Dropdown เป็น Worksheet แล้วเลือกด้านขวาเป็น Activate (ซึ่งความหมายคือ event ที่มีการเลือก Sheet นี้ให้ activate ขึ้นมา)
จากนั้นใส่ Code คำว่า Call RefreshAll (ชื่อ Macro ของเราที่บันทึกไว้มะกี๊) เข้าไป เพื่อให้มันสั่ง Run Macro ที่เราบันทึกไว้ จะได้ดังรูป
ทีนี้เมื่อไหร่ก็ตามที่เราคลิ๊กที่ชีทอื่นอยู่ แล้วกลับมาคลิ๊กที่ Sheet PivotResult เมื่อไหร่ (เรียกว่า Activate) มันจะสั่ง RefreshAll ทันที!!
เพิ่ม Event ที่จะทำให้เกิดการ Refresh
จริงๆ Event ที่คลิ๊กที่ Sheet PivotResult แล้วอัปเดท มันก็ดูเข้าท่าดีนะ แต่ว่าถ้าเกิดคนใช้งานไม่มาคลิ๊กที่ Sheet มันก็ไม่อัปเดทน่ะสิ…
ดังนั้นเราต้องมาคิดดีๆ ว่านอกเหนือจากการคลิ๊ก Activate Sheet สรุปแล้ว จะให้อัปเดทตอนไหนบ้าง? (อันนี้แล้วแต่เลยนะ อาจไม่ต้องใส่ทุกอัน ผมแสดงให้ดูหลายๆแบบละกัน)
ไอเดีย 1 : อัปเดทเมื่อเราแก้ข้อมูลในตารางต้นทาง
การอัปเดทเมื่อแก้ไขข้อมูลในตาราง แบบที่ง่ายที่สุดสามารถใช้ Event Sheet Change ได้ (แปลว่ามีการแปลี่ยนแปลงอะไรบางอย่างใน Sheet นั้นๆ ที่เราเลือก)
เช่น ผมสั่ง RefreshAll เมื่อมีการ Change ใน Sheet ข้อมูลหลัก
Private Sub Worksheet_Change(ByVal Target As Range)
Call RefreshAll
End Subแล้วผมก็ทำแบบนี้ให้ครบทุก sheet ที่เป็นแหล่งข้อมูลของเรา สมมติมีแหล่งข้อมูล 3 sheet ก็ทำแบบนี้ 3 รอบ แค่นี้มันก็จะอัปเดททันทีเมื่อมีการแก้ข้อมูลต้นทางแล้วล่ะ
ไอเดีย 2 : อัปเดทหลังจากคลิ๊กหนีไปที่ชีทอื่น
แทนที่จะอัปเดททุกครั้งที่มีการแก้ข้อมูล (เช่น แก้ cell นึงก็อัปเดท แก้อีก cell ก็อัปเดท) ซึ่งทำให้อัปเดทเยอะเกินความจำเป็น เราอาจะใช้วิธีการที่ว่าเมื่อเราคลิ๊กไปที่ชีทอื่นเมื่อไหร่ ให้อัปเดททันที ซึ่งสามารถใช้ Event ระดับ Sheet ที่ชื่อ Deactivate ได้
โดยไปเลือกที่ Sheet ที่เป็นแหล่งข้อมูลแล้วใส่ code นี้
Private Sub Worksheet_Deactivate()
Call RefreshAll
End Subอย่าลืมทำให้ครบทุก sheet ที่เป็นแหล่งข้อมูลนะ
ไอเดีย 3 : อัปเดทก่อน Save ไฟล์
เพื่อกันลืม refresh แล้วเผลอ save ไฟล์ไป ดังนั้นเดี๋ยวเราจะใช้ Event ระดับ Workbook ที่ชื่อว่า BeforeSave มาจัดการ ดังนี้
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call RefreshAll
End Subแต่ปรากฎว่าเวลาใช้จริงมัน Error ทั้งนี้เพราะว่าใน Query ของเรามันมีการตั้งค่า Enable Background Refresh เอาไว้ ทำให้ Excel Refresh ไปทำงานไป (ไม่รอ Refresh ให้จบก่อน)
ดังนั้นวิธีการแก้ไขคือให้ไปเอา Enable Background Refresh ออกไปซะ
จากนั้นลอง Save ไฟล์ดูจะเห็นว่าไม่ Error แล้วล่ะ
แก้เรื่องการ Refresh
การ Refresh All แม้ว่ามันจะง่ายและสะดวกมากๆ แต่ว่าบางทีมันอาจทำการ Refresh เยอะเกินความจำเป็นไปและทำให้ทำงานช้า ดังนั้นเดี๋ยวเราจะลองมาหาทาง Refresh เฉพาะสิ่งที่เราต้องการดูบ้าง
Refresh Pivot Table ทุกตัว
จริงๆ อันนี้ต้องเรียกว่า Refresh PivotCache ทุกตัวจะดีกว่า ซึ่งสามารถทำได้ดังนี้
Sub RefreshAllPivot()
For Each pivCache In ThisWorkbook.PivotCaches
pivCache.Refresh
Next pivCache
End Subระวัง! อย่างไรก็ตาม ถ้าหากมี PivotTable ตัวใดตัวหนึ่งที่สร้างมาจาก Power Query มันจะหา PivotCache ไม่เจอทำให้ Error ซึ่งเดี๋ยวเราจะหาทางจัดการต่อไป
Refresh เฉพาะบาง Pivot Table
ดังนั้นในส่วนนี้เราจะลองหาทาง Refresh เฉพาะ Pivot Table ที่เราต้องการ
จริงๆ แล้วต้องบอกว่ามันทำได้แค่ Refresh แค่บาง Pivot Cache มากกว่า เพราะถ้ามี การนำ Pivot Cache ตัวเดียว แล้วนำไปสร้าง PivotTable หลายๆ ตัว…
เวลากดคลิ๊กขวา Refresh Pivot Table ตัวเดียวแล้ว จะทำให้ Pivot Table ทุกตัวที่ผูกกับ Pivot Cache นั้นมันจะถูก Refresh ทั้งหมดเลย
ดังนั้นเราจึงไม่สามารถ Refresh PivotTable ตัวเดียวได้ แต่เราสามารถ Refresh PivotCache ตัวเดียวได้ โดยใช้ Code นี้ (ดัดแปลงมาจากการ Record Macro ได้)
Worksheets("ชื่อชีทที่มีpivottable").PivotTables("ชื่อpivottable").PivotCache.Refreshระวัง! อย่างไรก็ตาม ถ้า PivotTable นั้นเป็นตัวที่ source มาจาก Power Query มันจะหา PivotCache ไม่เจอทำให้ Error ซึ่งเดี๋ยวเราจะหาทางจัดการต่อไป
Refresh เฉพาะบาง Query
ด้วยสาเหตุที่การ Refresh ที่ PivotCache จะทำไม่ได้หาก PivotTable นั้นมี source data จาก Power Query
ดังนั้นวิธีการ Refresh เฉพาะ Pivot Cache ตัวนั้นก็คือการ Refresh ตัว Query นั่นเอง ดังนั้นเราจะมาดูวิธี Refresh Query กันครับ
Refresh ที่ Query ผลลัพธ์
เนื่องจากเราไม่รู้ code ดังนั้นเดี๋ยวลองกด Record Macro โดยตั้งชื่อว่า RefreshQuery แล้วกด ok
จากนั้นคลิ๊กขวาที่ Query ผลลัพธ์ แล้วกด Refresh
จากนั้นกด Developer –> Stop Recording เพื่อหยุดการบันทึก Code
แก้ไข Action ให้ยืดหยุ่นขึ้น
แล้วกด Alt+F11 เพื่อไปดู Code ของ Action ที่เกิดขึ้นใน Module 1 จะพบว่ามี Code นี้เกิดขึ้นมาต่อจาก RefreshAll (ถ้าทำไว้)
ซึ่งถ้าดูรูปแบบแล้วจะพบว่า คำสั่งคือ
ActiveWorkbook.Connections(“Query – ชื่อQuery“).Refresh
ดังนั้นเพื่อให้เรียก Refesh ง่ายขึ้นเราจะแก้ให้มันรับ input เฉพาะชื่อ Query เข้าไปใน Sub เพื่อให้เราสามารถสั่ง Query อื่นได้ง่ายขึ้น ดังนี้
Sub RefreshQuery(QueryName As String)
ActiveWorkbook.Connections("Query - " & QueryName).Refresh
End Subดังนั้นเวลาเรียกใช้ก็จะเป็นแบบนี้
Call RefreshQuery("CombinedTable")ทีนี้เวลา Refresh ใน Event เราก็ต้องเป็น Call RefreshQuery(“ชื่อQuery”) แทน ซึ่งจะทำให้สามารถเปลี่ยน Query ที่จะสั่ง Refresh ได้ง่ายขึ้นแค่ใส่ชื่อใหม่ที่ต้องการ
ทำให้ Refresh เฉพาะบาง Query ตามลำดับที่ต้องการ
Sub RefreshQueryBatch()
'จะ Refresh Query ไหนบ้างก็ใส่ไป
Call RefreshQuery("CombinedTable")
Call RefreshQuery("xxxลำดับถัด2")
Call RefreshQuery("xxxลำดับถัด3")
End Subดังนั้นเราสามารถเรียก Code Batch ได้ทีเดียวง่ายๆ ใน Event ต่างๆ ดังนี้
Call RefreshQueryBatchแค่นี้เราก็จะสามารถ Refresh Query ได้ตามต้องการแล้วล่ะครับ ^^
และแล้วก็จบแล้วล่ะ สำหรับบทความสอนวิธี Refresh Pivot Table และ Power Query ใครอ่านจบแล้วทำแล้วติดขัดอะไรตรงไหน ก็สอบถามได้เลยครับ
