WPS表格Power Query批次整合與清洗多來源資料完整操作流程
功能定位與變更脈絡
2025 年 12 月版 WPS 表格將 Power Query（官方譯名「查詢與連線」）直接內嵌於「資料」頁籤，無需額外外掛。核心價值在於把「多來源、多格式、多週期」的報表彙整，轉為可重複執行的「一鍵重新整理」流程，徹底取代過去「手動貼上＋VLOOKUP」的碎片化操作。
與 Microsoft 365 相比，WPS 版本免費開放基礎 ETL 功能（合併、篩選、填滿、群組），但進階「M 語法編輯器」仍標註為「進階付費」；若企業需國密合規，可選本地離線套件，關閉雲端資料落地。
這次改版也一併把「查詢與連線」窗格預設固定在右側，步驟順序與相依圖一目瞭然，對初次使用者而言，等同把「錄製巨集」的門檻降到「錄製篩選」等級；老用戶則可透過「匯入 Excel 查詢」直接移植原有 .oqy 範本，降低異質辦公室混用成本。
痛點導入：營運者的一天
場景：社群電商小編每日 09:00 需彙整「Shopline 訂單 CSV＋Google Analytics 試算表＋ERP 資料庫」三份異質資料，手動對齊 SKU 與 UTM 參數，平均耗時 45 分鐘；若遇欄位增刪，需重拉樞紐，錯誤率 8%。
導入 Power Query 後，把三個來源設為「連線-only」模式，清洗步驟錄製一次，次日僅需「資料→全部重新整理」2 分鐘完成，且錯誤率降至 0.3%。以下流程將以此案例為主軸，展示從比對、決策到落地的完整操作。
示例：若 ERP 臨時在「出庫時間」欄位前新增「揀貨耗時」，舊公式會因偏移而報錯；Power Query 因採欄位名稱綁定，僅需在編輯器內一次性對應，後續重新整理不受影響，這也是錯誤率大幅下降的主因。
決策樹：何時該用 Power Query 而非樞紐或公式
| 維度 | Power Query | 傳統公式 |
|---|---|---|
| 資料來源數 | ≥2 且異質 | 1 或同質 |
| 更新頻率 | 每日/每週 | 一次性 |
| 欄位變動 | 經常增刪 | 固定 |
| 多人協作 | 需版本隔離 | 可手動鎖定 |
若三個條件同時滿足「≥2 來源＋高頻更新＋欄位漂移」，優先導入 Power Query；否則維護成本反而高於一次性公式。
經驗性觀察：當資料量低於 5 千行且欄位固定，公式搭配「格式化為表格」已足夠；一旦來源超過兩個、且每週需更新 3 次以上，Power Query 的「步驟溯源」與「一鍵重新整理」就能把人力成本拉到負值，ROI 在 2 週內轉正。
平台差異與最短入口
Windows 桌面（11.2.0.12345）
開啟 WPS 表格→上方功能區「資料」→左側「查詢與連線」群組→「取得資料」→下拉可看「從檔案/資料庫/線上服務」。
macOS（11.2.0.12345）
路徑相同，但「從 SQL Server」需額外安裝「開放式連線套件」；若提示「驅動缺失」，請在 WPS 官網搜尋「ODBC 驅動 for mac」。
Android / iOS
行動版僅提供「檢視與手動重新整理」；新增查詢需回桌面端完成，否則僅能以「唯讀連線」開啟。
補充：Linux 目前尚無原生用戶端，經驗性觀察可透過 Wine 8.0 安裝 Windows 版，但 ODBC 驅動需額外綁定 UnixODBC，屬於社群非官方方案，企業若需正式支援，建議優先採用 Web Office 或本地虛擬桌面。
批次整合：從零建立三來源查詢
步驟 1 建立 CSV 連線
- 資料→取得資料→從檔案→從文字/CSV→選取 Shopline 匯出檔「orders.csv」。
- 預覽彈窗中，確認分隔符號為「逗號」→檔案原始格式選「65001: Unicode (UTF-8)」→「轉換資料」。
步驟 2 清理欄位
Power Query 編輯器開啟後，刪除無關欄位「付款明細 JSON」：右鍵該欄→移除。將「訂單日期」資料類型改為「日期」→點選左側圖示即可批量設定。
步驟 3 加入 GA 試算表
首頁功能區「新增查詢」→從線上服務→Google 試算表→登入 Google→選取「GA4 每日管道報表」→同樣點「轉換資料」。
提示：WPS 採用 OAuth2 PKCE 流程，權杖僅存於本地加密資料庫，符合 GDPR 與國密雙證要求；IT 部門可透過「金山雲保密域」後台一鍵撤銷。
步驟 4 合併查詢
在左側查詢窗格，選取「orders」→「合併查詢」→選取 GA 查詢→勾選「UTM Campaign」為關聯鍵→聯類型選「左外」。
步驟 5 載入至工作表
關閉並載入至→選「僅建立連線」→勾「新增至資料模型」；若後續要做 1000 萬行級透視，建議直接進資料模型，避免 1,048,576 列硬限制。
補充：若 ERP 來源為 SQL，步驟 3 可改選「從資料庫→從 SQL Server」，串連字串支援 Trusted_Connection 與 Kerberos，IT 只需開 1433 白名單即可；首次連線時勾「加密連線」可通過等保 2.0 傳輸審計。
自動化清洗：常用轉換對照表
| 髒資料範例 | 轉換操作 | 背後原理 |
|---|---|---|
| 「NT$1,234」 | 替換值→尋找「NT$」→留白；資料類型改「整數」 | 移除幣別符號後才能彙總 |
| 「 王大明 」 | 格式→修剪 | 刪除首尾空格，避免樞紐錯列 |
| 「2025/12/19 15:00」 | 欄位→日期→僅保留日期 | 時間維度歸一，方便日群組 |
| 空白列 | 首頁→移除列→移除空白列 | 減少 5–10% 容量，加速載入 |
經驗性觀察：若來源為電商平台 CSV，「金額」與「數量」常夾帶全形符號；建議在「取代值」步驟一次把「NT$」「,」「 "」全列清單寫入，後續若平台改符號，只需回來編輯此步驟即可全 Query 生效，維護成本最低。
常見分支與回退方案
分支 A：欄位名稱變動
經驗性觀察：Shopline 每月第一個週一會在 CSV 新增「物流子狀態」欄。處理方式：在「來源步驟」之後插入「使用第一個資料列作為標頭」→再「選取欄位」時改用「依位置」而非「依名稱」，即可免疫標頭漂移。
分支 B：資料夾批次合併
若每日產生「sales_20251219.csv」這類時間戳檔案，選「從資料夾」而非「從單一檔案」→篩選「副檔名 is .csv」→「合併並載入」即可一鍵追加；但注意資料夾內若有格式錯誤檔案，會導致整批失敗，建議先在「範例檔案」步驟做 Schema 檢查。
回退方案
右側「查詢設定」窗格→點任一步驟右鍵「復原至此步驟」即可時光回溯；若已載出工作表，可在「資料→查詢與連線」→右鍵「刪除」並選「僅保留值」，瞬間斷開連線避免循環重新整理。
進階回退：若誤刪步驟且已存檔，可在「檔案→資訊→管理版本」找回「自動儲存」副本；或事先把查詢定義另存 .pq 檔，透過「匯入查詢」復原，適合多人共用範本時作為「基準版本」保護。
FAQ 與邊界
Q1：免費版有 100 MB 單檔限制嗎？
A：2025 年 12 月政策，本機檔案無容量上限；唯「從 Web API」單次回應超過 100 MB 需登入 WPS 帳號並開啟「加速模式」。
Q2：重新整理頻率最短可設多少？
A：桌面端最小單位為 1 分鐘；網頁版因瀏覽器節流，最短 5 分鐘。經驗性觀察：設 1 分鐘且來源>3 個時，CPU 佔用會維持 8–12%，筆電需接電源。
Q3：是否支援 Power Pivot DAX？
A：尚無；WPS 資料模型僅提供「彙總/群組/計算欄位」等基礎量值，如需 DAX 請改用 Power BI Desktop 或 Excel。
Q4：可以排程自動重新整理嗎？
A：桌面端需搭配 Windows 工作排程器呼叫「wps /refresh」參數；Web 版則在「檔案→自動化」設定，最短 5 分鐘觸發一次。
Q5：行動版能否編輯查詢步驟？
A：否，僅支援手動「重新整理」與「僅保留值」中斷連線；新增或修改步驟需回桌面端。
Q6：離線環境如何啟用？
A：安裝「本地離線套件」後，在「選項→隱私」關閉「允許雲端服務」即可；但 OAuth 連線將無法使用，需改採資料夾或資料庫直連。
Q7：是否支援 Power Query 資料流程協同？
A：目前僅能透過「匯出 .pq」與「共用範本」方式人工合併；官方路線圖 2026 Q4 預計推出「金山資料流程」雲端服務，屆時才支援多人即時共編。
Q8：重新整理失敗如何除錯？
A：先查看「查詢設定」黃色驚嘆號，再點「移至錯誤」；常見原因為欄位遺失或資料類型不符，亦可開啟「查詢診斷」取得詳細持續時間與 API 回應碼。
Q9：能直接寫回資料庫嗎？
A：Power Query 為唯讀；若需寫回，請在 WPS 巨集內呼叫 ADO 或使用「資料→匯出至 CSV」再由 ETL 伺服器撈取。
Q10：免費版與專業版效能差異？
A：官方文件指出，兩者在本地核心引擎相同；專業版主要解鎖「M 語法編輯器」與「Web API 加速通道」，巨量情境下載時間可差 20–30%，但日常 <50 萬行感受不明顯。
驗證與觀測方法
- 開啟「檔案→選項→進階」→勾「查詢效能診斷」，重新整理後會在相同目錄產生「QueryDiagnostics_xxx.zip」。
- 用內建「診斷工具」檢視「資料來源持續時間」與「擴展時間」；若後者>前者 3 倍，代表清洗步驟過重，建議刪除不必要「變更類型」步驟。
- 觀測指標：100 萬行 CSV 本地重新整理，目標 <15 秒；每增加 10 個「取代值」步驟，約增加 1.2 秒線性成本。
進階：可將診斷 ZIP 解壓後匯入 Power BI，建立「Query 效能儀表板」，持續追蹤每日重新整理趨勢；若「擴展時間」呈線性上升，代表步驟需要重構，例如把多個「取代值」合併為「轉換表」對照，即可把 O(n) 降至 O(1)。
適用／不適用場景清單
- 適用：日更 >200 筆訂單、欄位經常漂移、需多人協作唯讀報表。
- 不適用：一次性去重、<1 萬行且欄位固定、需即時雙向寫回資料庫（Power Query 為唯讀）。
- 合規邊界：涉密機房若禁用 OAuth，請改用「從本機資料夾」+「離線排程腳本」方式，避免雲端權杖交換。
額外注意：「即時股價」或「秒級監控」情境因最短重新整理僅 1 分鐘，且為批量載入，不具備串流低延遲特性；若需秒級更新，應改用 WebSocket 推播或專業即時資料庫，而非 Power Query。
最佳實踐檢查表
[ ] 步驟命名採「動作_欄位」格式，如「移除_空白列」
[ ] 關閉「自動偵測資料類型」選項，避免錯判
[ ] 使用「參數」設定日期區間，方便後續僅增量重新整理
[ ] 每季匯出「查詢 JSON」備份至 Git，方便差異比對
補充：在「檔案→選項→查詢」可關閉「自動偵測資料類型」，避免日期欄被誤判為文字；若跨區 CSV 常見小數點分隔符錯亂，可預設「地區」為「英文 (美國)」再手動調整，減少「變更類型」步驟數量。
案例研究
A. 50 人電商團隊：每日 3 萬行訂單整合
做法：把 Shopline、91APP、GA4 三 CSV 丟入共用資料夾，Power Query 設定「從資料夾」合併；以「訂單編號」為鍵，左聯 GA4 的 UTM 來源。使用「參數」過濾最近 30 天，避免全量載入。
結果：重新整理時間從 18 分鐘降至 42 秒；行銷部每日可提前 1 小時取得 ROAS 報表，促銷檔期反應速度提升 2 倍。
復盤：初期忘記關閉「自動偵測欄位類型」，導致金額被視為文字，樞紐彙總失效；後續把「變更類型」放在最後一道，並鎖定欄位名稱，徹底杜絕漂移。
B. 300 人製造業：每小時產線 IoT 品質檢測
做法：機台即時拋轉 CSV 至 NAS，WPS 以「從資料夾」每小時重新整理；透過「群組」計算每批料號的 Ppk 值，再載入至資料模型，產線 QC 以樞紐呈現趨勢圖。
結果：原本手動蒐集 6 小時的報表縮至 4 分鐘；當 Ppk < 1.33 自動標紅，工程師可即時調機，報廢率下降 0.8%。
復盤：IoT 欄位偶爾多送「感測器版本號」，導致 Schema 不合；後續在「範例檔案」步驟鎖定必要欄位，其餘選「忽略」，即可達到 Schema 寬容度平衡。
監控與回滾
異常信號
重新整理時間較上日增加 50% 以上、查詢結果列數異常浮動 >5%、出現「DataFormat.Error」或「Token Expired」提示。
定位步驟
- 開啟「查詢設定」→黃色驚嘆號→「移至錯誤」。
- 檢視「查詢診斷」→比對「資料來源持續時間」與「擴展時間」。
- 若為 HTTP 403/401，先更新 OAuth 權杖；若為欄位遺失，比對「範例檔案」與實際檔案 Schema。
回退指令
右鍵查詢→「復原至此步驟」；若已載出至工作表，再點「刪除」並選「僅保留值」即可斷開連線。Git 備份者可用「查詢→匯入→從檔案」還原 .pq。
演練清單
[ ] 每季執行「復原至此步驟」演練，確保新人知道時光回溯入口
[ ] OAuth 到期前 7 天，先手動「撤銷」再「重新登入」，驗證權杖更新流程
[ ] 在測試環境放置「格式錯誤」CSV，確認「從資料夾」合批失敗時，能正確定位並隔離
術語表
ETL：Extract-Transform-Load，萃取、轉換、載入的縮寫，首次出現於「功能定位」段落。
M 語法：Power Query 的函式語言，用於撰寫高階轉換，首次出現於「功能定位」段落。
UTM：Urchin Tracking Module，GA 用來標註流量來源的參數，首次出現於「痛點導入」段落。
左外聯：Left Outer Join，合併查詢時保留左表全部資料，首次出現於「步驟 4」。
Schema：資料結構描述，首次出現於「分支 B」。
Ppk：Process Performance Index，製程能力指標，首次出現於案例研究 B。
OAuth2 PKCE：一種無後端的授權碼流程，首次出現於「步驟 3」提示塊。
國密：中國商用密碼算法標準，首次出現於「功能定位」段落。
查詢診斷：WPS 內建的效能記錄功能，首次出現於「驗證與觀測方法」。
資料模型：WPS 內部的欄位式資料庫，用於突破 104 萬列限制，首次出現於「步驟 5」。
ADO：ActiveX Data Objects，用於 VBA 連接資料庫，首次出現於 FAQ Q9。
WebSocket：全雙工通訊協定，用於即時推播，首次出現於「不適用場景」補充。
查詢 JSON：Power Query 步驟的匯出格式，副檔名 .pq，首次出現於「最佳實踐檢查表」。
DAX：Data Analysis Expressions，Power BI 與 Excel 的公式語言，首次出現於 FAQ Q3。
Git：分散式版本控制系統，首次出現於「最佳實踐檢查表」。
風險與邊界
1. 唯讀限制：Power Query 無法把結果寫回來源資料庫，若需回寫，請改用 ADO 或後端 ETL。
2. 記憶體天花板：32 位元 WPS 仍受 2 GB 位址限制，超過 500 萬行建議改用 64 位元並啟用「資料模型」。
3. OAuth 斷線：Google 權杖最長 60 天，若遇「Token Expired」需手動重新授權；離線機房應改走「服務帳戶＋金鑰檔」。
4. 檔案鎖定：CSV 若被其他程式獨佔開啟，重新整理會報「File in Use」；建議把產生與讀取目錄分離，透過「完成旗標檔」通知。
5. 替代方案：若需即時秒級推送、或雙向讀寫，請評估專業 ETL 工具（如 Kettle、Airbyte）或直接接入 BI 平台。
未來版本展望
官方公開路線圖透露，2026 Q2 將加入「WPS AI 資料洞察」按鈕，自動掃描 Query 步驟並建議「刪除重複排序」或「索引合併」；此外，「M 語法編輯器」預計下放至免費版，但進階函式（如 Table.Schema）仍保留給專業版。
總結：WPS 表格 Power Query 在 2025 年 12 月已覆蓋 90% 日常 ETL 需求，對中小企業而言，能以零授權成本完成「多來源批次清洗＋自動更新」。只要避開「即時寫回」與「巨量 API」兩大坑，即可在 2 小時內落地一套可維護的數據流水線，為後續 BI 視覺化與 AI 洞察打好基礎。