Power Query範例:ChatGPT協助Excel資料正規化,從報表分析到建立查詢

Power Query 範例適合用來整理合併儲存格、月份橫向排列的 Excel 報表,不僅能清理資料、轉換表格結構,還能保存處理步驟,方便日後更新。贊贊小屋以觀光統計報表為例,請 ChatGPT 逐步教學 Excel 2016 的操作,從原始資料分析到建立查詢。

Power Query範例特色圖:中間機器人連到兩邊,左側標示AI直接完成並顯示試算表,右側標示AI逐步教學並以五個箭頭相連的節點表示,副標題為從AI整理Excel到學會操作

一、Power Query 自動更新:不用每月重新整理 Excel

這次對話裡最值得先看的,是 ChatGPT 在整套方案最後補上的一段說明。標題是「十二、我再補充一個很重要的觀念」,內容指出使用 Excel 2016 時,未來如果每個月都要更新這份觀光遊客資料,不需要重新操作前面列出的 16 個步驟,因為 Power Query 會記住設定過的每一個處理步驟。AI 把未來的更新流程寫成四件事:將新的原始資料放到指定位置、確認原始欄位結構沒有改變、回到 Excel,最後點選「資料」→「全部重新整理」,就能重新產生整理後的資料。

同一則回覆的下半部,接著預告「第一階段完成後,第二階段我們要做什麼?」,答案是「建立樞紐分析表」,利用整理好的正規化資料,實際建立各縣市遊客人次排名與各觀光類型遊客人次統計等分析。也就是說,整理資料並不是最終目的,而是替後面的樞紐分析打底:第一階段先把報表整理成適合分析的結構,第二階段才進入彙總與比較。

這種「整理一次、之後重複使用」的做法,可以用資料處理常見的 ETL 流程來理解。ETL 是 Extract、Transform、Load 三個英文字的縮寫,也就是擷取、轉換與載入。以這份報表為例,從政府網站取得 Excel 檔案是擷取;補齊空白分類、拆分中英文名稱、把月份欄位轉成資料列,屬於轉換;最後把整理結果放進 Excel 工作表,就是載入。Power Query 的特色,是把轉換的過程保存成一個個查詢步驟,之後可以重新執行,而不只是記錄一次滑鼠操作。

不過,「全部重新整理」並不是對任何新檔案都有效。查詢記住的是當初設定的資料來源與處理規則:如果檔案名稱或存放路徑改變,查詢就找不到資料;如果新報表增減了欄位、更改了欄位名稱,或調整了版面結構,原本的步驟也可能出錯,甚至得到不正確的結果。因此 AI 提醒的「確認原始欄位結構沒有改變」,並不是形式上的一步,而是重新整理能否成功的前提。Power Query 可以重複執行資料處理的流程,但這個流程能用多久,仍取決於原始資料是否穩定。

ChatGPT 回覆的操作說明畫面,內容說明使用 Excel 2016 時,Power Query 會記住處理步驟,未來更新資料可以點選資料、全部重新整理,並預告第二階段是建立樞紐分析表 Power Query資料更新流程:放入新的原始資料、確認欄位結構、回到Excel、全部重新整理,條件是來源與欄位結構必須相容

二、ChatGPT 產生 Excel 範本:直接下載整理結果

這段對話發生在 ChatGPT 提出三階段方案之後。AI 在回覆的最後建議:「我建議我們就從第一階段開始:直接幫你把這份原始 Excel 轉換成正規化資料表,並提供可以下載、實際操作的 Excel 範本。」贊贊小屋沒有多加說明,只在輸入框回覆了「第一階段」四個字。

接下來,ChatGPT 連續顯示五次「已分析」,處理時間為 1 分 1 秒,最後出現標題「七、第一階段完成:正規化 Excel 範本」,並附上檔案「觀光遊客人次_正規化範本.xlsx」,旁邊標示「Excel 活頁簿|可直接開啟編輯」與「已建立」。從結果來看,AI 把「第一階段」理解成請它直接完成轉換,所以交出來的是已經整理好的檔案,而不是一套可以自己照著做的方法。

要注意的是,這個過程裡贊贊小屋並沒有打開 Excel 操作 Power Query,資料是 ChatGPT 自己處理、自己產生檔案的。因此這份範本只能算是 AI 提供的整理結果,不是 Power Query 的實作成果,也不代表贊贊小屋已經學會了其中的操作。

這就是「AI 直接處理 Excel」與「用 Power Query 建立流程」兩種做法的差別。前者的目標是快速取得結果,使用者只需要交代需求,但如果規則要修改,通常得再向 AI 說明一次,也需要另外確認 AI 到底做了哪些處理;後者的目標是建立流程,每一個轉換步驟都保存在查詢裡,可以逐步檢查、修改,資料更新後也能再次執行。兩者沒有誰比較好,要看工作需求:只做一次性的分析,讓 AI 直接整理很有效率;每個月都要處理相同格式的報表,就比較適合建立 Power Query 流程。後者背後的觀念叫做可重現性:同一份原始資料、同一組明確的處理規則,應該得到相同的結果。

ChatGPT 回覆畫面,顯示第一階段完成的正規化 Excel 範本檔案,檔案已建立並標示可直接開啟編輯 兩種Excel資料整理方式的比較:左側是AI直接產生Excel,適合一次性整理;右側是Power Query建立流程,適合重複工作

三、Excel 正規化範本:認識 4 張資料工作表

ChatGPT 在檔案旁邊說明,這份 Excel 包含四張工作表:「使用說明」記錄資料結構及操作說明,「據點主檔」是 418 筆據點基本資料,「人次明細」是 6,266 筆年度、月份、人次資料,「年度比較原表」則保留原始累計、差值及成長率。這四張工作表的分工很清楚:說明、據點基本資料、逐月人次與原始對照數字,各放在自己的位置。

右側的檔案預覽目前停在「年度比較原表」,欄位依序是「據點編號」「據點中文名稱」「115年1-8月累計(原表)」「114年1-8月累計(原表)」與「差值(原表)」。第一筆資料是編號 S0001 的陽明山遊客中心,115 年 1 至 8 月累計 376,404 人次,114 年同期為 491,100 人次,差值是 -114,696;下一筆 S0002 的陽明書屋則是 52,958 與 55,038,差值 -2,080。欄位名稱最後都加上「(原表)」,表示這些數字是保留原始報表的累計值,用來和整理後的明細對照。

回覆的下方,ChatGPT 還提醒兩個「請特別留意」的地方。第一,原始說明記載 417 處,但依有月份數值的資料列整理出 418 筆,AI 目前先全部保留,尚未擅自刪除;第二,115 年的分類曾經調整,所以年度之間的比較需要小心;這一點在原始報表的資料使用說明中也看得到,註記提到 115 年調整了 263 處據點分類,並有據點刪除、新增與合併。這兩點都說明,範本產生了,不等於資料已經完全正確。418 筆與官方說明的 417 處為什麼不同,還需要回頭核對原始資料才能確定;6,266 筆也只是這次 AI 輸出的明細筆數,並不是已經獨立驗證過的最終數字。

把據點基本資料與逐月人次分成不同工作表,是前一篇〈正規化意思〉介紹過的資料正規化常見設計。據點主檔保存名稱、縣市與分類這類很少變動的資料,相當於描述「是誰」的維度資料;人次明細保存每個據點在某個年月的統計數字,相當於記錄「發生了什麼」的事實資料,兩者可以用據點編號串起來。要確認整理結果是否可靠,可以挑一個據點檢查:例如把陽明山遊客中心整理後 115 年 1 至 8 月的明細加總,看看是否等於原始報表的累計 376,404。如果不同,就要追查是否有月份遺漏、重複列或資料類型錯誤。檔案產生成功與資料驗證通過,是兩個不同的階段。

ChatGPT 提供的正規化範本檔案預覽,顯示年度比較原表的據點編號、115年與114年1至8月累計與差值欄位 正規化範本的4張工作表:使用說明、據點主檔、人次明細與年度比較原表,並以據點編號關聯,再核對整理後明細合計與原始累計

四、ChatGPT 提示詞:要求 Power Query 逐步教學

看到 AI 直接交出整理好的 Excel,贊贊小屋提醒 ChatGPT,先前不是說要用 Power Query,並重新提出需求:

傳給ChatGPT的問題你先不是說要用Power Query?請幫我詳細列出怎麼操作,要使用哪個命令,怎麼設定及流程。

這段提問同時帶出 ChatGPT 先前回覆中的「Power Query 的主要處理步驟」。對照保存的原始對話,這份清單是 AI 在規劃階段列出的 8 個步驟:

1. 將原始資料匯入 Power Query。
2. 取消合併儲存格造成的空白分類,補齊類型與上層遊憩區。
3. 拆分中英文名稱及縣市。
4. 將 115 年與 114 年月份資料整理成相同欄位。
5. 使用「取消資料行樞紐」將 1 月~12 月轉換成月份資料列。
6. 將年度、月份、遊客人次整理成獨立欄位。
7. 排除空白月份,不要將空白轉成零。
8. 載入 Excel 工作表,建立樞紐分析表。

要特別說明的是,這 8 項是 AI 提出的處理計畫,不代表已經完成。從贊贊小屋的提問可以看出,真正想要的是操作方法:要使用哪個命令、怎麼設定,以及整個流程怎麼走。AI 先前雖然說過要用 Power Query,後來卻直接交出整理好的範本,兩者並不一致,所以才需要這一次追問。

這個轉折也說明,AI 通常會依照對話內容推斷使用者想完成什麼工作。當回覆只有「第一階段」時,AI 可以合理地理解成「請做出第一階段的成果」;但如果使用者的目的是學會操作,就需要把期待說清楚。這次提問已經要求了命令、設定與流程三件事;若想讓 AI 的教學更完整,還可以補充使用的 Excel 版本、每一步完成後應該看到的結果,以及為什麼要這樣處理。提示詞不只要告訴 AI 要做什麼,也要說明希望透過什麼方式完成。

贊贊小屋重新向 ChatGPT 提問的對話畫面,要求列出使用 Power Query 的命令、設定與流程 提示詞改變AI的工作方式:要求完成時AI交付整理範本,要求教學時AI改為逐步教學命令、設定與流程

五、Power Query 教學:從產生成品改為逐步操作

ChatGPT 接受了提醒,回覆「你說得對,我們第一階段應該以 Power Query 作為主要工具,而不是直接把資料整理好交給你。」接著說明,這次會改用教學方式,帶贊贊小屋在自己的 Excel 2016 裡實際操作,而且每一步都會交代五件事:

1. 要點選哪個功能或命令。
2. 要選取哪些欄位。
3. 設定視窗要怎麼選。
4. 執行後應該看到什麼結果。
5. 為什麼要這樣處理。

AI 另外承諾,會依照贊贊小屋上傳的原始檔欄位位置說明,不使用假設性的範例欄位。回覆隨後進入新的段落「第一階段:使用 Power Query 正規化觀光遊客人次資料」,第一個小標是「一、先了解原始資料的欄位位置」,ChatGPT 表示重新檢查了原始 Excel,指出工作表名稱是「累計明細表- 以類型分」,欄位標題位於第 3 列,並開始列出主要欄位。

這五個項目構成了一份比較完整的操作教學。只知道要按哪個按鈕,屬於程序性知識,回答「如何操作」;知道為什麼要這樣處理,屬於概念性知識,回答「為什麼這樣做」。例如為什麼月份要從欄位變成資料列,如果只記住命令名稱,換了一份報表就不一定知道怎麼辦;理解原因,才比較容易把同一種方法用到其他資料。「執行後應該看到什麼結果」也很重要,因為它提供了檢查點:如果實際畫面和預期不同,就應該先查明原因,而不是繼續往下一步操作。

不過,這裡只是 AI 宣告了教學方式與承諾。每一項說明是否正確、每個步驟是否真的能在自己的 Excel 2016 裡做出相同的結果,仍然要等實際操作後才能確認。

ChatGPT 回覆畫面,表示改以 Power Query 教學,並說明每一步會交代的內容與依原始檔欄位位置操作 AI操作教學的5個要點:選擇命令、確認欄位、設定視窗、核對預期結果與理解處理原因

六、Excel 原始資料欄位:確認觀光統計報表結構

在開始操作之前,ChatGPT 先用一張表格列出原始報表從 A 欄到 AH 欄的內容,並標明每一欄預計怎麼處理:

Excel 欄位 原始內容 處理方式
A 類型 向下填滿
B 觀光遊憩區 拆分上下層及中英文
C 縣市 拆分中英文
D:O 115 年 1~12 月 取消樞紐分析
P 115 年 1~8 月累計 保留於比較資料
Q:AB 114 年 1~12 月 取消樞紐分析
AC 114 年 1~8 月累計 保留於比較資料
AD 差值 保留於比較資料
AE 成長率 保留於比較資料
AF 計算方式 保留
AG 資料來源 保留
AH 統計範圍面積 保留

這張表可以分成幾個群組來看。A 到 C 欄是類型、觀光遊憩區與縣市,用來辨識每一列屬於哪個據點,處理重點在補齊分類與拆開中英文;D 到 O 欄與 Q 到 AB 欄分別是 115 年與 114 年的月份人次,是要轉成資料列的主體,AI 在處理方式寫的是「取消樞紐分析」,對應前面步驟中提到的「取消資料行樞紐」,而 Microsoft 繁體中文說明把這項轉換稱為「取消樞紐分析欄」(Unpivot Columns),不同版本的介面文字可能略有差異;P、AC、AD、AE 這四欄是累計、差值與成長率,保留作為比較資料;最後的 AF 到 AH 是計算方式、資料來源與統計範圍面積,屬於說明統計背景的資訊,也一併保留。這份報表的 115 年只有 1 月至 8 月有資料,所以 9 月到 12 月的欄位雖然存在,卻是空白。

像這樣在動手以前先盤點欄位,其實就是在建立一份資料字典(Data Dictionary):記錄每個欄位叫什麼名稱、代表什麼意思、屬於哪種資料類型,以及資料從哪裡來。它能避免一個常見的錯誤,就是把所有看起來是數字的欄位都當成可以直接加總的資料。單月人次、累計人次與成長率雖然都以數字呈現,統計意義卻完全不同;累計人次是各月份加總而來的另一種統計值,如果和單月人次放進同一個欄位,就會重複計算,所以才需要保留在比較資料,而不是併入「遊客人次」。

資料類型也值得在這個階段先想清楚:年份與月份適合當成整數,遊客人次是數值,而據點編號即使看起來像數字,例如 S0001,也應該當成文字識別碼。另外要提醒的是,這裡的欄位位置是這一份官方報表實際的配置,並不代表其他報表也使用相同的欄位。換一份資料時,一樣需要重新檢查,再決定每一欄的處理方式。

ChatGPT 列出原始報表 A 欄到 AH 欄內容與處理方式的表格畫面 Excel原始報表的4種欄位:A至C據點辨識、D至O及Q至AB月份人次、P及AC至AE比較資料、AF至AH統計背景,並提醒單月人次與累計人次不可重複加總

七、Excel 合併儲存格:補齊據點上下層分類

ChatGPT 接著指出一個重要問題:原始資料的 A 欄與 B 欄有合併儲存格,因此並不是每一筆資料都有完整的分類名稱。它以原始報表的片段為例,整理出下面這樣的對照:

類型 觀光遊憩區 縣市
國家(自然)公園 陽明山國家公園 空白
空白 陽明山遊客中心 臺北市
空白 陽明書屋 臺北市
空白 大屯遊憩區 臺北市
空白 玉山國家公園 空白
空白 塔塔加遊憩區 南投縣

接著 AI 提出「我們要把它整理成」的目標結構:每一列都同時有類型、上層遊憩區、據點名稱與縣市。例如陽明山遊客中心、陽明書屋與大屯遊憩區,整理後類型都是「國家(自然)公園」,上層遊憩區都是「陽明山國家公園」,縣市都是「臺北市」。原本靠版面合併才看得出來的階層關係,變成每一列都自己帶著完整的分類資料。

合併儲存格是 Excel 報表常見的排版方式,讓讀者不用在每一列重複閱讀相同的分類名稱,但這種排版對資料分析並不友善,例如健保級距表的合併儲存格與空白字元,同樣得先整理乾淨才方便查詢分析。Power Query 讀取這類資料時,合併區域通常只有左上角的儲存格有值,其他位置會變成空值(null),所以需要使用「填滿」→「向下」(Fill Down),把上方的分類補到下面的空白列。這就是階層式資料轉成扁平資料的基本做法:類型這一欄可以直接向下填滿,每一列就都有了分類。

問題在於,這份報表的 B 欄同時混合了兩種不同的列:上層的遊憩區,例如「陽明山國家公園」,以及真正統計遊客人次的據點,例如「陽明山遊客中心」。因此不能把整個 B 欄直接向下填滿,應該先辨認哪些列是上層遊憩區,另外建立「上層遊憩區」欄位再填滿,最後排除只有分類名稱、沒有人次的群組列。前面的範例中,上層遊憩區的縣市欄是空白,據點列則有縣市,這是一個可以利用的線索,但它並不是絕對的判斷標準:如果某個真正的據點恰好缺少縣市資料,只看縣市是否空白,就可能被誤認成上層分類而遭到刪除。較穩妥的做法,是同時檢查據點名稱、縣市、月份人次與原始報表的分組結構,再決定每一列的角色。

ChatGPT 說明原始資料 A 欄與 B 欄有合併儲存格,並以陽明山國家公園等據點整理出完整分類結構的畫面 合併儲存格的兩種處理:A欄類型依分類結構向下填滿,B欄遊憩區與據點先辨識資料列身分再建立上層遊憩區欄位,縣市空白不能單獨判斷資料列身分

八、Excel 2016 Power Query:從活頁簿建立查詢

說明完資料結構,ChatGPT 終於進入第一個實際的 Excel 操作。回覆的標題是「二、建立 Power Query 查詢」,標示為「操作步驟 01」,主題是「將原始 Excel 資料匯入 Power Query」,先請贊贊小屋開啟原始 Excel 檔案,再進行「步驟 1:選擇資料來源」。在 Excel 2016 的上方功能區,依序是:

1. 點選「資料」。
2. 找到「新增查詢」或「取得及轉換」區域。
3. 點選「從檔案」。
4. 選擇「從活頁簿」。

回覆下方另外附了一張英文介面的 Excel 示意畫面,用數字 1 到 4 與箭頭,依序指向功能區的「Data」、「New Query」、「From File」與「From Workbook」,對應上面中文步驟的「資料」、「新增查詢」、「從檔案」與「從活頁簿」。要注意的是,這是 ChatGPT 提供的操作說明與示意畫面,內容只到選擇資料來源為止,還沒有進入選取檔案之後的導覽器或 Power Query 編輯器,也不是已經完成匯入的實際結果。Microsoft 現行的繁體中文說明,把匯入 Excel 活頁簿的路徑寫成「資料」→「取得資料」→「檔案」→「Excel 活頁簿」,與 AI 使用的「新增查詢」→「從檔案」→「從活頁簿」文字並不相同,所以贊贊小屋後續實際操作時,仍需要以自己 Excel 中文介面上看到的命令為準。

Power Query 早期是以增益集的形式提供給 Excel 2010 與 2013 使用,從 Excel 2016 起,Windows 版已經把它整合進「資料」索引標籤的「取得及轉換」(Get & Transform)群組。依 Microsoft 的說明,Excel 2016 以上的 Windows 單機版與 Microsoft 365 都有這項功能,只是單機版支援的資料來源比 Microsoft 365 少,不過匯入 Excel 活頁簿與表格或範圍這兩種來源,單機版都在支援清單內。因此使用 Windows 版 Excel 2016 的讀者,不需要另外安裝增益集,就可以從這個位置開始。這次選擇的是「從檔案」→「從活頁簿」,用意是從另一份 Excel 檔案,也就是原始報表建立查詢;如果資料已經在目前開啟的活頁簿裡,則常見的做法是選取資料範圍後使用「從表格/範圍」。依照前面的欄位盤點,要處理的資料就在原始檔的「累計明細表- 以類型分」工作表裡。

最後還要分清楚兩個容易混淆的名稱。Power Query 是資料轉換工具,負責把資料整理成適合分析的形狀;樞紐分析表(PivotTable)則是分析與彙總工具,負責把整理好的資料依照不同角度分類統計。前面 8 個處理步驟中的「取消資料行樞紐」,也就是 Microsoft 繁體中文說明所稱的「取消樞紐分析欄」(Unpivot Columns),是 Power Query 的轉換功能,把橫向展開的寬表轉成逐列紀錄的長表;最後一步建立的「樞紐分析表」則是另一項功能。名稱裡都有「樞紐」,用途卻完全不同。

Excel 英文介面示意圖,以數字 1 到 4 標示 Data、New Query、From File 與 From Workbook 的選取順序 兩種樞紐功能有什麼不同:Power Query的取消樞紐分析欄用於寬表轉長表,Excel樞紐分析表用於分類彙總資料

從 AI 幫忙整理 Excel,到自己學會 Power Query

回頭看這次的對話,最有意思的地方並不是 Power Query 的某一個命令,而是 AI 的角色改變了。一開始,ChatGPT 依照「第一階段」的指示,直接交出整理完成的 Excel 範本,效率很高,卻沒有回答「要怎麼自己做」這個問題;經過贊贊小屋的追問,它才改成說明命令、欄位、設定、預期結果與原因,一步一步帶著使用者操作。同樣是 AI,因為提問的方式不同,扮演的是代工,還是老師。

這也讓我們看到使用 AI 處理 Excel 的兩種價值。如果只需要一次性的結果,讓 AI 直接產生檔案確實省時間;但如果資料每個月都會更新,又希望自己理解並掌握整理規則,就需要請 AI 把方法說清楚,再由自己在 Excel 裡建立可以重複執行的 Power Query 流程。不論採用哪一種,AI 交出的檔案與說明都還需要檢查,418 筆與 417 處的差異、年度分類的調整、縣市空白不一定代表上層分類,這些都提醒我們,整理完成不等於資料正確。

目前這份紀錄停在建立查詢的起點:資料結構已經盤點完成,處理步驟也已經規劃,接下來要在 Excel 2016 裡匯入原始報表、補齊分類、取消樞紐分析欄,最後載入結果並核對數字。

讓AI完成工作,也教會自己方法:AI幫我完成可取得整理結果,AI教我操作可理解並掌握方法,兩種方法都需要驗證資料正確性
發布日期:

贊贊小屋 AI 工具教學:

線上課程:ChatGPT課程

ChatGPT 課程特色圖:用 AI 學會 Excel 與 VBA 自動化

分類:AI工具、ChatGPT Excel