Excel 數據清洗公式提示詞（拆分合並單元格內容、去空格與不可見字符、文本型數字轉換、統一格式）
從系統、網頁、問卷導出的表格亂七八糟（一格里塞了姓名電話地址、數字無法求和、有看不見的空格、日期格式五花八門、同一個值寫法不同）時用：貼幾行樣例，AI 判斷每一列的問題，給出清洗公式或快捷操作，並告訴你什麼時候該改用 Power Query。
通用大模型 對話模型通用
你是一名擅長整理髒數據的 Excel 高手。請幫我清洗下面的數據。 - 軟件：[軟件]（例：Excel 365、WPS） - 數據樣例（10 行左右，包括有問題的行，原樣粘貼）： [數據樣例] - 想要的結果：[想要的結果]（例：拆成姓名、手機號、省、市四列，金額可以求和） - 數據量與是否需要重複處理：[數據量與頻率]（例：3000 行，每週都要處理一次） 請按列逐一分析： 1. 這一列有哪些問題：前後空格與不可見字符（例如從網頁複製帶來的不間斷空格）、全角半角混用、文本型數字、數字中夾帶單位或貨幣符號、日期格式不統一、同一值的不同寫法、多個資訊擠在一格。 2. 對應的處理方法，優先級： - 能用「分列」「快速填充」「查找替換」一次性解決的，先給操作方法； - 需要保留原數據、可重複使用的，給出公式（如 TRIM、CLEAN、SUBSTITUTE、TEXTSPLIT、TEXTBEFORE、TEXTAFTER、VALUE、NUMBERVALUE、LEFT、MID、FIND 等），說明我的軟件版本是否支持； - 每週都要重複處理時，建議改用 Power Query，並說明步驟。 3. 拆分規則：資訊擠在一格時，先歸納出分隔規律（逗號、空格、固定位置、關鍵字），對沒有規律的行單獨標出，不要硬拆。 4. 統一寫法：給出一張對照表的做法（原寫法 → 標準寫法），用查找函數替換，而不是一個個手工修改。 5. 清洗後的校驗：用公式檢查是否還有異常（例如手機號長度不是 11 位、金額不是數字、日期超出合理範圍）。 輸出：每列的問題與處理方法表，可以直接複製的公式，以及清洗後的校驗公式。處理手機號等個人資訊時，提醒我注意保密。
高亮處換成你自己的內容：[軟件]、[數據樣例]、[想要的結果]、[數據量與頻率]
ChatGPT Plus 充值
已被複製 0 次
使用說明
怎麼填變量：[數據樣例] 一定要包含「有問題的行」，只貼乾淨的行，AI 看不出要處理什麼。數據涉及姓名、手機號時，可以先把真實資訊替換成假的再發給 AI，只要格式保持一致即可。
常見坑：
- 用 TRIM 去不掉網頁複製過來的空格。那通常是不間斷空格（編碼為 160），需要先用 SUBSTITUTE 把它替換成普通空格。
- 文本型數字看起來正常，求和結果卻是 0。用 VALUE 轉換，或者用「分列」直接完成轉換。
- 想用一個複雜公式把所有不規則數據一次拆好，結果總有幾十行拆錯。先按規律處理大部分，剩下的少數單獨標出來人工處理。
追問技巧：追問「這些清洗步驟每週都要做一次，幫我改寫成 Power Query 的步驟」（可配合 1104 號提示詞），或「清洗後的數據怎麼檢查重複的客戶」。
示例輸出
示例，僅供參考（節選）
| 列 | 問題 | 處理方法 |
|---|---|---|
| 客戶資訊 | 「張三 13800000000 浙江省杭州市」擠在一格，用空格分隔 | Excel 365 用 =TEXTSPLIT(TRIM(A2), " ")；舊版本用「數據 → 分列 → 按空格」 |
| 金額 | 混有「元」、千分位逗號、全角數字、不間斷空格 | 見下方公式 |
| 城市 | 「杭州」「杭州市」「杭州 」並存 | 建對照表後用 XLOOKUP 統一 |
=NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(ASC(B2), CHAR(160), " ")), "元", ""), ",", ""))解釋：ASC 把全角字符轉為半角（WPS 與 Excel 均支持，以實際為準）；SUBSTITUTE(..., CHAR(160), " ") 把不間斷空格換成普通空格；去掉「元」和千分位逗號後，用 NUMBERVALUE 轉為數字。
校驗：=SUMPRODUCT(--NOT(ISNUMBER(C2:C3001))) 統計轉換後仍不是數字的行數，結果應為 0。
同款作品
用這條提示詞做出來的作品；原作者會因此獲得積分
還沒有同款，來做第一個。
0 條評論
還沒有評論，來搶沙發～