2天前｜ 1125次觀看

ChatGPT怎麼整理Excel資料？清理欄位、統一格式，樞紐分析不出錯｜8組提示詞範本

Excel或Google Sheets的欄位內容沒統一，樞紐分析被拆成好幾類、加總還算不出來？本文分享使用ChatGPT檢查欄位問題、建立清理規則、產生公式，並將混亂資料轉換成一致格式，附8組可直接複製的提示詞範本，同樣適用。本文節錄自《ChatGPT上工了! 最強AI活用術》。

文／施威銘研究室

本文目錄（點擊可快速前往）

請ChatGPT幫你清理Excel資料、統一欄位格式
輸入指令請ChatGPT做檢查
範例：清理訂單資料中的欄位與格式
- 檢查資料、給予清理規則
- 產生清理公式
常見清理任務的指令範本：檢查欄位、金額、統一日期格式
使用ChatGPT外掛時的注意事項

【在Excel或Google Sheets使用ChatGPT外掛】
- 工具：Excel或Google Sheets
- 外掛：ChatGPT for Excel／ChatGPT for Google Sheets
- Excel安裝：常用→增益集→ 搜尋ChatGPT→ 新增
- Google Sheets安裝：擴充功能→外掛程式→取得外掛程式→ 搜尋ChatGPT→安裝
- 登入：用OpenAI帳號登入。免費版及各訂閱方案皆可使用

注意：類似的外掛程式很多，下載時請確認公司名稱為「OpenAI」（圖／104職場力小編截圖） — **注意：**類似的外掛程式很多，下載時請確認公司名稱為「OpenAI」（圖／104職場力小編截圖）

請ChatGPT幫你清理Excel資料、統一欄位格式

資料表在進行排序、篩選、加總、查找或樞紐分析前，通常需要先確認欄位與格式是否一致。即使資料看起來可以閱讀，只要欄位內容不統一，後續分析就可能出現錯誤。例如，同一欄地區中同時出現「台北」、「臺北市」、「Taipei」；同一欄日期中混用「2026/1/5」、「1月5日」、「2026-0105」；同一欄金額中同時出現「1200」、「1,200」、「$1,200」、「1200元」。這些內容對人來說容易理解，但在試算表中可能被判斷為不同分類、不同格式，甚至不同資料型態。

ChatGPT可以協助使用者檢查欄位問題、建立清理規則、產生公式，並將混亂資料轉換成一致格式。用在資料整理的前置階段，幫助使用者判斷哪些欄位需要統一、哪些內容可能會影響計算，以及應該採用哪些功能來處理。

輸入指令請ChatGPT做檢查

使用這項功能時，可以先選取需要檢查的資料範圍，例如A1:F20，再於ChatGPT外掛中輸入清理指令。比較穩定的做法是先請外掛檢查問題，再請它建立規則，最後才請它產生公式或建議處理方式。可以在外掛中輸入：

【提示詞】
請檢查目前選取的表格有哪些欄位與格式問題。請用表格列出欄位名稱、問題類型、可能影響與建議清理方式。請特別檢查日期、金額、分類名稱、空格與付款狀態是否一致。

如果已經知道要清理的欄位，也可以更精準地輸入：

【提示詞】
金額欄位在（自行選取或填寫）欄，內容可能包含 $、,、元等符號。請提供一個可以放在新欄位的公式，將這些內容轉成可加總的純數字。

先檢查問題以避免ChatGPT誤改資料；再建立清理規則可以讓我們確認標準；最後產生公式，則能把清理結果放在新欄位中檢查，而不是直接覆蓋原始資料。

範例：清理訂單資料中的欄位與格式

檢查資料、給予清理規則

假設工作表中有一份簡單的訂單資料：

訂單編號	地區	訂單日期	金額	付款狀態	商品類別
A001	台北	2026/01/05	1200	已付款	辦公用品
A002	臺北市	1月6日	850元	已付	辦公用品
A003	Taipei	2026-01-07	$2,300	Paid	電腦週邊
A004	新北	2026.01.08	1,500	未付款	電腦週邊

這張表看起來仍然可以閱讀，但若要做統計，會出現幾個問題。地區欄位中「台北」、「臺北市」、「Taipei」可能代表同一地區，但在樞紐分析中會被拆成不同分類。訂單日期欄位混用斜線、中文日期、短橫線與句點格式，可能影響排序與月份統計。金額欄位混入「元」、「$」與逗號，可能造成加總失敗。付款狀態中的「已付款」、「已付」、「Paid」也需要統一。商品類別中的「電腦週邊」與「電腦周邊」雖然意思接近，但試算表會將它們視為不同類別。我們可以先選取這份資料，然後在ChatGPT外掛中輸入：

【提示詞】
請檢查目前選取的訂單表有哪些資料清理問題。請用表格列出欄位、問題類型、可能影響與建議處理方式。請不要直接修改資料，先提出清理建議。

外掛可能回傳類似以下內容：

欄位	問題類型	可能影響	建議處理方式
地區	同一地區有多種寫法	樞紐分析或篩選時會被拆成不同分類	建立對照表，將台北、臺北市、Taipei統一為臺北市
訂單日期	日期格式不一致	可能無法正確排序、依月份統計或製作趨勢圖	統一為 yyyy-mm-dd 格式
金額	數字混入符號與文字	加總、平均或比較時可能出錯	移除 $、,、元後轉為純數字
付款狀態	分類命名不一致	篩選與統計時結果分散	將已付款、已付、Paid 統一為已付款
商品類別	同義分類用字不同	分類統計會被拆成兩組	將電腦週邊與電腦周邊統一為同一標準名稱

接著，可以在外掛中請ChatGPT建立清理規則：

【提示詞】
請根據剛才檢查出的問題，建立一張清理規則表。欄位請包含：原始欄位、標準格式、建議方法、是否需要人工確認。

ChatGPT可能會回傳：

原始欄位	標準格式	建議方法	是否需要人工確認
地區	台北市、新北市等標準縣市名稱	建立對照表，再用XLOOKUP或VLOOKUP轉換	是
訂單日期	yyyy-mm-dd	使用日期轉換公式或先統一分隔符號	部分需要
金額	純數字	使用SUBSTITUTE、VALUE或REGEXREPLACE清除符號	否
付款狀態	已付款、未付款	建立對照表或使用SWITCH / IF統一	否
商品類別	固定分類名稱	建立對照表或使用取代功能	是

這張規則表可以放在工作表旁邊，作為後續清理依據。需要人工確認的項目，通常是分類標準或語意判斷。例如「Taipei」是否一定要轉成「台北市」，要看資料來源是否可能包含「台北地區」或其他更寬的區域定義；「電腦週邊」與「電腦周邊」是否統一，也要看團隊採用哪一種標準用字。

產生清理公式

確認清理規則後，可以請ChatGPT 外掛針對特定欄位產生公式。比較推薦的做法，是在原始欄位旁新增「清理後」欄位，而不是直接覆蓋原始資料。例如可以新增「清理後金額」、「標準付款狀態」、「標準地區」等欄位，方便檢查公式結果是否正確。我們在這邊以「統一金額格式」、「統一付款狀態」為例子做示範。

1. 統一金額格式：若金額在D欄，可以在外掛中輸入：

【提示詞】
金額欄位在D欄，可能出現1200、850元、$2,300、1,500這幾種格式。請提供一個可放在新欄位的公式，將它轉成可以加總的純數字。

Excel可能會出現以下公式：

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"$",""),"元",""),",",""))

這個公式用多層SUBSTITUTE逐一移除$、元與逗號，再用VALUE轉成數字。它的優點是容易理解，缺點是每新增一種要移除的符號，就需要增加一層替換。如果資料來源長期會出現不同格式，使用對照表、PowerQuery或更進階的清理工具可能更適合。

2. 統一付款狀態：原始資料中可能有「已付」「Paid」「未繳」「Unpaid」，我們希望統一成「已付款」或「未付款」。這時就可以先請ChatGPT建立一張對照表，再讓ChatGPT提供函數，把原始值轉成標準值。

【提示詞】
請在原始資料旁建立一份「付款狀態對照表」，包含「原始值」與「標準值」兩個欄位，並依照下列規則填入：
●「已付款」、「已付」、「Paid」統一對應為「已付款」。
●「未付款」、「未付」、「Unpaid」統一對應為「未付款」。
請保留原始資料，不要直接修改或覆蓋原欄位。

接著，請ChatGPT依照對照表寫的轉換規則，在原始資料旁新增「整理後的付款狀態」欄位，將E欄中格式不一致的付款狀態統一為標準值。對照表位於H2:I7，其中H欄為原始值，I欄為對應的標準值。

【提示詞】
E欄為「付款狀態」，其中包含多種不同寫法。請參考H2:I7的對照表，提供一個可填入新欄位的公式，將 E 欄的付款狀態轉換為對應的標準值。若找不到相符的原始值，請顯示 "需確認"。

本次實際測試中，ChatGPT提供的公式如下：

=IFERROR(VLOOKUP(E2,$H$2:$I$7,2,FALSE),"需確認")

這個公式會先讀取E2儲存格中的付款狀態，並到H2:I7的對照表中尋找完全相同的原始值。找到後，會回傳對照表第二欄的標準值，例如將「已付」或「Paid」轉換成「已付款」。公式中的$用來固定對照表範圍，因此向下複製公式時，查找範圍不會跟著移動。如果找不到相符的付款狀態，IFERROR就會讓儲存格顯示「需確認」。

可以看到ChatGPT已經在G欄新增一個欄位，用來顯示統一判斷後的付款狀態（圖／旗標科技）

若使用的是Microsoft365或新版Excel，也可以使用XLOOKUP：

=XLOOKUP(E2,$H$2:$H$7,$I$2:$I$7,"需確認",0)

這個公式會取得E2的付款狀態，並在H2的「原始值」中尋找完全相同的內容；找到後，回傳同一列I2中的「標準值」。若找不到相符內容，就顯示「需確認」。最後的0代表必須完全符合，而$則用來固定對照表範圍，讓公式向下複製時不會移動。但是部分舊版Excel還有Google Sheets都不支援XLOOKUP，假如出現 #NAME?，就得改用VLOOKUP版本。

常見清理任務的指令範本：檢查欄位、金額、統一日期格式

在Excel或GoogleSheets中使用ChatGPT外掛清理資料時，可以依照問題類型輸入不同指令。若資料問題不明確，先要求外掛檢查；若問題已經明確，再要求它產生公式或清理規則。

使用需求	可輸入到外掛的指令
檢查欄位問題	請檢查目前選取的表格有哪些欄位與格式問題，並指出哪些問題會影響排序、篩選、加總或樞紐分析
建立清理規則	請根據目前表格建立一份欄位清理規則表，包含欄位名稱、問題類型、標準格式與建議處理方法
統一分類名稱	請檢查目前選取欄位中哪些值可能代表同一意思，並建立標準化對照表。若不確定，請標註需要人工確認
清理金額欄位	請提供Excel與Google Sheets公式，將目前金額欄位中含有貨幣符號、逗號與文字單位的內容轉成純數字
清理日期欄位	請檢查目前日期欄位有哪些格式不一致，並建議如何統一為yyyy-mm-dd。若部分資料缺少年份，請標註需要補充規則
清除多餘空格	請提供公式，清除目前文字欄位中的前後空格、連續空格與不可見字元
拆分混合欄位	請根據目前欄位內容，判斷是否需要拆分成多個欄位，並提供Excel與Google Sheets可用方法
檢查重複資料	請根據目前表格欄位，建議應該用哪些欄位判斷重複紀錄，並說明理由

這些指令可以依表格內容調整。若外掛輸出的解法說明不明確，可以要求它「引用具體欄位名稱與範例值」；假如公式出錯又不知道怎麼調整，可以用「先用輔助欄位分步處理，再提供合併公式」提示詞處理。

使用ChatGPT外掛時的注意事項

使用ChatGPT 外掛清理欄位與格式時，應先保留原始資料。比較安全的做法，是新增「清理後」欄位，或另存一份工作表，再將公式結果放入新欄位中檢查。若直接覆蓋原始欄位，一旦清理規則有誤，就不容易回頭確認資料原貌。這一點在金額、日期、客戶資料、庫存資料與交易紀錄中特別重要。

清理後也建議進行抽樣比對。可以挑選幾筆正常資料、空白資料、格式特殊資料與找不到對應值的資料，檢查清理前後是否符合預期。例如付款狀態使用對照表轉換時，若某個原始值沒有出現在對照表中，公式最好顯示「需確認」，而不是直接保留原值或顯示空白。這樣可以讓尚未被清理規則涵蓋的資料浮現出來，方便後續補充對照表。

清理公式也需要測試，尤其是日期與金額欄位，只要資料中有少數特殊格式，公式就可能失效。例如金額欄位如果出現負數、括號表示負值、百分比、小數點或不同幣別，原本只移除符號的公式可能不夠用。日期欄位若出現缺少年份、民國年、西元年混用，或文字日期與數字日期混雜，也需要先確認轉換規則，不能只依賴單一公式。

如果試算表裡的資料會定期更新，建議把清理規則整理在工作表中，例如建立一張「對照表」或「清理規則」工作表，記錄原始值、標準值、處理方法與是否需要人工確認。ChatGPT外掛可以協助產生規則與公式，但清理規則最好留在試算表內，方便日後重複使用、檢查與交接。

節錄自：旗標科技《ChatGPT上工了! 最強AI活用術: ChatGPT Work×GPT 5.6》／施威銘研究室著

AI ChatGPT Excel 工作術生產力工具