สำหรับคนรัก Power Query นำข้อมูลไปทำ PivotTable ต้องแยก Refresh หรือ Refresh ALL

Refresh เป็นทั้งจุดอ่อนและจุดแข็ง เมื่อเทียบกับการใช้สูตรพวก GroupBY / PivotBY ที่จะคำนวณหาคำตอบใหม่ให้ทันที ซึ่งการ Refresh จะเกิดการคำนวณใหม่ให้เฉพาะเมื่อต้องการเท่านั้น ทำให้ช่วยทำให้การทำงานกับข้อมูลจำนวณหลายล้านรายการเกิดขึ้นอย่างมีประสิทธิภาพมากขึ้น แต่ที่แย่หน่อยก็ตอนที่ต้องคอยสั่ง Refresh นี่แหละครับ ถ้าผู้ใช้งานลืม Refresh ก็จะได้รายงานที่ยังคงเป็นของเก่า

แล้วถ้าสั่งให้ Refresh All ล่ะ ตามตำราจะอธิบายว่าระบบจะไป Refresh Query ก่อนแล้วจึงตามไป Update Pivot ซึ่งอาจเจอว่าในการใช้งานจริงไม่เป็นแบบนั้น กลับกลายเป็น Update Pivot ก่อนแล้วจึงไป Query ใหม่ตามทีหลัง ทำไม ...

Copilot อธิบายมาตามนี้

สาเหตุที่ Refresh All อัปเดต Pivot ก่อน Query

เมื่อกด Refresh All Excel จะสั่งให้

1. รีเฟรช Connections/Queries
2. รีเฟรช Pivot Tables (ผ่าน PivotCache)

แต่ถ้า Power Query แต่ละอันตั้งค่าเป็น Enable background refresh (Default) มันจะรันแบบ Asynchronous คือสั่งให้ดึงข้อมูลไปก่อน แต่ไม่รอให้เสร็จ จึงกลายเป็นว่า Excel พยายามรีเฟรช PivotCache ทันทีที่เจอสัญญาณว่า “นี่คือคำสั่ง Refresh All” ทั้งที่ Query ยังดึงข้อมูลไม่จบ ทำให้เกิดอาการ Pivot Table ไม่อัปเดตตาม หรือดูเหมือนรีเฟรชก่อน source เสร็จ

---

วิธีแก้ไขเบื้องต้น

• ปิด Background Refresh ของทุก Query

  1. ไปที่ Data → Queries & Connections
  2. คลิกขวาที่แต่ละ Query → Properties…
  3. เอาเครื่องหมายถูกหน้า Enable background refresh ออก

• ตั้งให้รีเฟรชครบจบก่อนด้วย VBA (ถ้าใช้ Macro)

  Sub RefreshAllSync()
      ActiveWorkbook.RefreshAll
      ' รอ Query ด้านหลังเสร็จ
      Application.CalculateUntilAsyncQueriesDone
      ' แปะโค้ดรีเฟรช PivotCache เพิ่มเติมถ้าต้องการ
  End Sub
  

• กรณีมี Pivot หลายอันที่แชร์ Cache เดียวกัน

  For Each pc In ActiveWorkbook.PivotCaches
      pc.Refresh
  Next
  

---

ใครเคยเจอปัญหานี้?

ชุมชน Excel หลายคนรายงานว่า

• กด Refresh All ครั้งแรก → Query ทำงานแบบ background
• Pivot ไม่อัปเดตจนกว่าจะกด Refresh All ซ้ำอีกครั้ง หรือคลิกขวา Refresh ทีละ Pivot
• ปิด Background Refresh แล้วปัญหานี้หายไปทันที

---

เทคนิคเสริม

• ถ้าลง Query เป็น Load to Data Model แล้วใช้ Power Pivot สร้าง PivotTable ระบบจะ Sync ในลำดับที่ถูกต้อง เพราะ Data Model จะถือเป็นแหล่งเดียว ไม่ต้องพะวงเรื่อง Background Refresh
• ถ้าต้องการให้รีเฟรชอัตโนมัติเมื่อเปิดไฟล์
  1. คลิกขวาที่ PivotTable → PivotTable Options…
  2. ที่แท็บ Data → ติ๊ก Refresh data when opening the file

---

สำหรับแนวทางที่ผมแนะนำ

1. แยกแฟ้มที่ใช้ Query ออกจากแฟ้มที่ใช้ Pivot ไปเลยตั้งแต่แรก
2. เมื่อต้องการข้อมูลใหม่ ให้เปิดแฟ้มไป Refresh Query
3. ลอกตารางที่ได้มาใส่ลงไปในแฟ้มที่ทำ PivotTable
4. สั่ง Refresh PivotTable

แม้จะไม่ได้ทำงานแบบต่อเนื่องกันให้เอง แต่ทำแบบนี้ชัดเจนกว่า ทำให้สามารถแชร์ข้อมูลที่ Query มาให้ใช้ร่วมกันได้ด้วย

ส่วนวิธีลอกข้อมูลมาใช้นั้น อาจทำแบบ Manual โดยไปสั่ง Copy > Paste หรือใช้ Macro Recorder บันทึกขั้นตอนนี้ไว้แล้วกดปุ่มเดียวก็จะได้ข้อมูลใหม่มาใช้แล้ว

ส่วนการสั่ง Refresh ก็สามารถใช้ Macro Recorder บันทึกไว้เช่นกัน