Excel Power Query 入門 — 資料清理自動化,告別手動複製貼上

由 FeiYueh 親自審稿驗證 · 最後更新於 2026-09-13

Excel Power Query 可以把每月固定重複的資料清理流程，從 30 分鐘手動複製貼上壓縮到「按一下重新整理」的 3 秒。它是 Excel 2016 以後內建的功能（在「資料」索引標籤下叫「取得及轉換資料」），不需要額外安裝、不需要寫程式，卻能記錄你所有的清理步驟並在下個月自動重播一次。多數人每天在 Exce

Excel Power Query 可以把每月固定重複的資料清理流程，從 30 分鐘手動複製貼上壓縮到「按一下重新整理」的 3 秒。它是 Excel 2016 以後內建的功能（在「資料」索引標籤下叫「取得及轉換資料」），不需要額外安裝、不需要寫程式，卻能記錄你所有的清理步驟並在下個月自動重播一次。多數人每天在 Excel 裡花掉的時間，有很大一部分是在做 Power Query 一次設定就能永久自動化的事。 手動清理資料的真實成本 資料工作者花在清理與整理資料的時間遠多於分析本身。 「資料科學家 60% 的工作時間用在清理與整理資料，57% 認為這是最不享受的工作」（來源：Forbes / CrowdFlower 資料科學家調查） 。這個比例在使用 Excel 的一般辦公室工作者身上只會更高，因為他們沒有 Python 或 SQL 可以用。 手動清理的問題不只是慢，而是不可重複。你這個月用「分割資料行」把「台北市信義區」拆成縣市與區域，下個月拿到新檔案時，整套動作要重做一次。中途如果有人接手，他不知道你上次用哪個分隔符號、哪些列被刪掉、為什麼某幾筆金額被改成 0。這種「知識只存在某個人腦袋裡」的狀態，是報表出錯後最難追查的來源。 錯誤率也是實際成本。 「88% 的試算表含有錯誤」（來源：MarketWatch 引述 Ray Panko 研究） ，而這些錯誤絕大多數來自人工輸入與複製貼上，不是公式邏輯本身。Power Query 把清理步驟固化成一段可檢視的流程，直接消掉「這次貼錯一行」這類錯誤來源。 Power Query 到底在做什麼 Power Query 是一個 ETL 工具：抽取（Extract）、轉換（Transform）、載入（Load）。你指定一個資料來源（Excel 檔、CSV、資料夾、資料庫、網頁），在編輯器裡用滑鼠點選各種轉換動作，最後把清乾淨的結果載回工作表或資料模型。 關鍵差異在於「步驟被記錄下來」。每一個動作都會寫進右側的「套用的步驟」清單，實際上是產生一段 M 語言程式碼。下個月換新檔案時，你只要把新檔案放到同一路徑、按「全部重新整理」，所有步驟會依序重跑一遍。你不必看得懂 M 語言也能用，但看得懂之後可以做更精細的控制。 它的資料量上限也遠高於工作表。Excel 工作表最多 1,048,576 列，但 Power Query 在載入到資料模型（Power Pivot）時不受此限，只受記憶體限制。 「Power BI 共用容量的單一資料集壓縮後上限為 1 GB」（來源：Microsoft Learn 官方文件） ，這個壓縮後容量通常對應到數千萬列的原始資料。 M 語言不是必修，但值得認識 M 語言（正式名稱 Power Query Formula Language）是 Power Query 背後的函數式語言。在編輯器按「進階編輯器」就能看到整段程式碼。典型的一段長這樣： = Table.SelectRows(來源, each [金額] > 0) 這行的意思是「從『來源』這張表挑出金額大於 0 的列」。多數日常操作用滑鼠點就會自動產生對應的 M 語法，你不需要背。真正需要手寫 M 的時機通常是三種：自訂條件判斷、跨資料表的複雜比對、以及把重複步驟包成自訂函數。 五個最常用的清理動作 以下五個功能覆蓋日常清理工作的大部分場景，全部在 Power Query 編輯器的功能區裡點選即可完成。 合併整個資料夾的檔案 ：「資料 → 取得資料 → 從檔案 → 從資料夾」。指定一個資料夾，Power Query 會把裡面所有結構相同的 Excel 或 CSV 檔一次讀進來並疊成一張表。每月新增一個檔案丟進資料夾，重新整理就自動納入。這是取代「開 12 個檔案複製貼上」的核心功能。 取消資料行樞紐（Unpivot） ：把橫向的「1月、2月、3月…」欄位轉成直式的「月份 / 金額」兩欄。選取要轉的欄位，右鍵「取消資料行樞紐」。橫表轉直表是樞紐分析表能正確運作的前提，手動做這件事極其耗時。 分割與合併資料行 ：依分隔符號、字元數、大小寫轉換位置切分欄位。「訂單編號-日期-客戶」一次切成三欄，且規則固定，不會像「資料剖析」那樣每次都要重設。 移除重複項與空白列 ：「首頁 → 移除資料列 → 移除重複項」。可指定依哪幾欄判斷重複，比工作表的「移除重複」更可控，而且會記錄成步驟。 合併查詢（Merge） ：等同 SQL 的 JOIN，用來取代 VLOOKUP。選兩張表的比對欄位，選擇聯結類型（左方外部、內部、完全外部等）。相比 VLOOKUP，它不會因為插入欄位而錯位，也不會在幾萬列時拖慢整個檔案。 從零開始的第一個自動化流程 用「每月銷售報表合併」當範例，這是最容易看到成效的入門情境。假設你每個月會收到一個結構相同的 CS

由 FeiYueh 親自審稿驗證 · 最後更新於 2026-09-13. Independently maintained — not AI-generated boilerplate.