เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Highlights : บทความแนะนำExcel Pivot TablePower QueryExcel VBA

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 1

ปฏิเสธไม่ได้ว่า Pivot Table เป็นเครื่องมือที่ใช้สรุปข้อมูลได้สุดยอดและง่ายมากๆ แต่ปัญหาหลักๆ ของการทำ Pivot ไม่ได้เกิดขึ้นขณะทำ Pivot แต่อยู่ที่ขั้นตอนเตรียมข้อมูลก่อนจะเอาไป Pivot ต่างหาก

ซึ่ง Power Query ก็เข้ามาตอบโจทย์นี้ได้ และถ้าใครได้ลองใช้แล้วคงพบว่ามันเป็นเครื่องมือที่เตรียมข้อมูลได้สุดยอดมากๆ (ผมว่ามันเหมือนกับความรู้สึกที่เราได้รู้จักกับ Pivot Table ใหม่ๆนั่นแหละ)

อย่างไรก็ตาม ทั้ง Pivot Table และ Power Query ต่างก็ยังมีข้อจำกัดสำคัญ คือ มันจะต้องมีการ Refresh ก่อน ถึงจะดูดข้อมูลที่อัปเดทล่าสุดมาจัดการต่อ แต่จะทำยังไงให้มันอัปเดทอัตโนมัติ มาดูกันครับ

ความสัมพันธ์ของ Pivot Table กับ Power Query Refresh

ถ้าเราผูก Pivot Table กับ Power Query เอาไว้ด้วยกันแล้ว โดยวิธีใดวิธีหนึ่ง คือ

  1. เอา Power Query Close & Load to… Pivot Table
    วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 2
  2. หรือ สร้าง Pivot Table จาก External Connection ที่เป็น Power Query โดยตรง (ไม่ใช่ Data ที่เป็น Table/Range ปกติ)
    วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 3

เราจะสามารถกด 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

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 4

ตัว Power Query ก็สามารถกด คลิ๊กขวา Property ได้เช่นกัน

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 5

วิธีที่ 3 : ตั้ง Auto Refresh ทุกๆ xx นาที

วิธีนี้เราจะตั้งได้ที่ตัว Query ที่เอาไปผูกกับ Pivot Table เอาไว้ โดยให้ติ๊กดังนี้ และ set เวลาที่ต้องการ (ในที่นี้ผม set ทุกๆ 1 นาที)

refresh every xx minutes

พอครบ 1 นาที เจ้า Query นี้จะอัปเดทตัวเอง ซึ่งทำให้ Pivot Table อัปเดทไปด้วย เช่น เดิมเป็นแบบนี้

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 6

ผมไปแก้ลูกค้าเบอร์ 1 ให้เป็นไต้หวัน แทนที่จะเป็นไทย

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 7

พอรอครบ 1 นาทีปุ๊ป ใน Pivot Table ผลลัพธ์จะเปลี่ยนเองเลย

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 8

วิธีที่ 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 ดู

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 9

จากนั้นไปที่ 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 ที่เราบันทึกไว้ จะได้ดังรูป

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 10

ทีนี้เมื่อไหร่ก็ตามที่เราคลิ๊กที่ชีทอื่นอยู่ แล้วกลับมาคลิ๊กที่ Sheet PivotResult เมื่อไหร่ (เรียกว่า Activate) มันจะสั่ง RefreshAll ทันที!!

เพิ่ม Event ที่จะทำให้เกิดการ Refresh

จริงๆ Event ที่คลิ๊กที่ Sheet PivotResult แล้วอัปเดท มันก็ดูเข้าท่าดีนะ แต่ว่าถ้าเกิดคนใช้งานไม่มาคลิ๊กที่ Sheet มันก็ไม่อัปเดทน่ะสิ…

ดังนั้นเราต้องมาคิดดีๆ ว่านอกเหนือจากการคลิ๊ก Activate Sheet สรุปแล้ว จะให้อัปเดทตอนไหนบ้าง? (อันนี้แล้วแต่เลยนะ อาจไม่ต้องใส่ทุกอัน ผมแสดงให้ดูหลายๆแบบละกัน)

ไอเดีย 1 : อัปเดทเมื่อเราแก้ข้อมูลในตารางต้นทาง

การอัปเดทเมื่อแก้ไขข้อมูลในตาราง แบบที่ง่ายที่สุดสามารถใช้ Event Sheet Change ได้ (แปลว่ามีการแปลี่ยนแปลงอะไรบางอย่างใน Sheet นั้นๆ ที่เราเลือก)

เช่น ผมสั่ง RefreshAll เมื่อมีการ Change ใน Sheet ข้อมูลหลัก

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 11
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 ออกไปซะ

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 12

จากนั้นลอง 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

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 13

จากนั้นคลิ๊กขวาที่ Query ผลลัพธ์ แล้วกด Refresh

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 14

จากนั้นกด Developer –> Stop Recording เพื่อหยุดการบันทึก Code

แก้ไข Action ให้ยืดหยุ่นขึ้น

แล้วกด Alt+F11 เพื่อไปดู Code ของ Action ที่เกิดขึ้นใน Module 1 จะพบว่ามี Code นี้เกิดขึ้นมาต่อจาก RefreshAll (ถ้าทำไว้)

วิธีทำให้ Pivot Table และ Power Query อัปเดทโดยไม่ต้องกด Refresh เอง 15

ซึ่งถ้าดูรูปแบบแล้วจะพบว่า คำสั่งคือ

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 ใครอ่านจบแล้วทำแล้วติดขัดอะไรตรงไหน ก็สอบถามได้เลยครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 1.6K
  •  
  •  
  •  
  •  
  • 1.6K
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply