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 条评论
还没有评论，来抢沙发～