Excel数据清洗操作指南(手工 + AI 双模式清洗十种常见场景的数据 )
- 2026-11-06 21:34:41
本手册覆盖 Excel 数据清洗最常见的 10 个场景，每个场景提供两种方式，可按需选用：
• 方式一：
手工操作——纯鼠标点击，适合数据量小、临时处理；
• 方式二：
AI 提示词——复制粘贴给 AI，适合数据量大、结构混乱的表格。
（提示词里的 [方括号] 需替换成你的实际情况；内置三条保护规则：不覆盖原表、结果写入新增 Sheet、生成清洗报告。涉及删除的操作（去重、删空行），先统计数量确认后再执行，避免误删。）
十种常见场景（手工 + AI 双模式）
本公众号后台发送消息“数据清洗”可以下载表格进行练习哦~
场景操作速查表
场景 | 手工核心操作 | AI 一键处理 |
删除重复行 | 数据 → 删除重复值 | 按指定列去重 |
去除空格 | Ctrl + H 或 =TRIM() | 清理空格与特殊字符 |
文本转数字 | 分列 → 常规 | 文本型数字转数值 |
日期规范化 | 单元格格式 / 分列 | 多格式日期统一 |
拆列 | 分列或 Ctrl + E | 按分隔符拆多列 |
合列 | =A2&B2 | 多列合并为一列 |
处理空值 | F5 → 定位 → 空值 | 逐列策略填充或删除 |
统一大小写 | =UPPER() / =LOWER() | 批量统一文本大小写 |
清除格式 | 清除 → 清除格式 | 整体格式规范化 |
核对与保护 | 数据验证下拉列表 | 数据校验与防错设置 |
新手建议：手工操作先学会Ctrl + E（智能填充）和F5 定位两大效率工具；数据量大时优先选择 AI 方式。
适用于订单、客户名单等存在重复记录的数据。
方式一：手工操作
（1）一键去重（最快）
1. 选中数据区域的任意一个单元格
2. 点击菜单栏【数据 】→ 【删除重复值】
3. 弹出窗口里勾选需要判断的列（比如只按订单号去重，就只勾订单号）
4. 点「确定」，Excel 会提示发现了 X 个重复值，已将其删除
（2）把重复项标出来看（推荐先看再删）
1. 选中数据区域（含表头）
2. 点击 开始 → 条件格式 → 突出显示单元格规则 → 重复值
3. 所有重复的行会被标红，先肉眼确认，再用方法一去重
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：删除重复数据。
规则：
1. 判断重复的依据：[例如“订单号”这一列，或“整行完全一致”]。
2. 重复的保留第一条，其余删除。
3. 删除前先统计有多少行重复，告诉我数量。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 完成后告诉我：原表多少行、删除多少行、剩余多少行。
适用于姓名、地址、备注等文本列混入空格、换行和乱码的情况。
方式一：手工操作
（1）查找替换（最快）
1. 选中整列数据
2. 按 Ctrl + H 打开查找和替换
3. 查找内容：输入一个空格（按一下空格键）
4. 替换为：什么都不填
5. 点「全部替换」
小提示：此方法会把所有空格都删掉，包括姓名中间的空格；只想删首尾空格请用
（2）TRIM 函数（只删首尾空格，最安全）
1. 在数据旁边新建一列，第一个单元格输入 =TRIM(A2)（A2 换成你的数据位置）
2. 按回车，双击单元格右下角的黑色小方块，公式自动填充整列
3. 选中这一列 → 复制 → 在原列右键 → 选择性粘贴 → 值（把公式换成纯文本）
4. 删除辅助列
小提示：数据里有全角空格（看起来像空格但删不掉）时：查找内容按 Ctrl 不放再按空格键输入，替换为留空。
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：清理文本中的空格和特殊字符。
规则：
1. 对 [姓名、地址、备注 等列] 去除首尾空格。
2. 字符串中间的多余空格合并为单个空格（如“张 三”→“张三”）。
3. 同时去除换行符、制表符和乱码符号。
4. [可选] 其他列只去除首尾空格，不影响中间内容。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 完成后列出：哪些列被清理、大约清理了多少个单元格。
数字左上角有绿色小三角，或者数字靠左显示（真数字靠右），求和总是 0，这就是文本型数字。
方式一：手工操作
（1）分列法（最经典）
1. 选中该列
2. 点击 数据 → 分列 → 下一步 → 下一步
3. 列数据格式选「常规」→ 完成
（2）乘 1 法
1. 在空白单元格输入数字 1，复制它
2. 选中要转换的列 → 右键 → 选择性粘贴 → 乘
3. 删掉那个 1
（3）错误提示法
1. 选中整列，点左上角黄色感叹号图标
2. 选择「转换为数字」
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：把 [金额、销量 等列] 的文本型数字转换为真正的数值类型，使其能正常求和。
规则：
1. 若文本中带货币符号（￥、$）、千分位逗号、“元”等，一并去掉再转数值。
2. 转换后无法解析的单元格标记出来，不要静默删除，单独列给我确认。
3. 转换完成后检查这几列是否都能正常求和。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 报告哪些单元格转换失败及原因。
适用于同列日期存在多种写法、无法排序和筛选的情况。
方式一：手工操作
（1）设置单元格格式
1. 选中日期列
2. 右键 → 设置单元格格式（或按 Ctrl + 1）
3. 左侧选「日期」，右侧挑一个格式，如 2024-01-05
4. 点「确定」
（2）文本日期（如 20240105）转标准日期
1. 选中该列 → 点击 数据 → 分列
2. 第一步、第二步直接点「下一步」
3. 第三步选「日期」→ 选 YMD → 完成
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：统一 [入职日期 等日期列] 的日期格式。
规则：
1. 该列目前存在多种格式（如 2024/1/5、20240105、2024年1月5日），请全部解析为标准日期。
2. 统一输出为 YYYY-MM-DD 格式（如 2024-01-05）。
3. 解析失败的单元格标记出来给我确认，不要直接删除。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 报告共转换多少行、多少行解析失败。
适用于一列中含多段信息（如地址、全名）需要拆开的情况。
方式一：手工操作
（1）分列（按符号拆）
1. 选中该列
2. 点击 数据 → 分列 → 分隔符号 → 下一步
3. 勾选「其他」，输入 -（或逗号、空格等）
4. 下一步 → 完成
注意：分列会覆盖右边相邻列，操作前先确认右边有空列，或先插入几列空白列。
（2）智能填充 Ctrl+E（超好用，强烈推荐）
1. 在右边空白列第一格手动输入要提取的结果，如手机号 13800138000
2. 选中这一格和下面要填充的单元格（或按 Ctrl + E）
3. Excel 会自动识别规律，批量填充出所有结果
小提示：Ctrl+E 适合：提取身份证号中的生日、拆分姓名和电话、合并格式等，只要有规律它都能猜出来。
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：拆分 [完整地址 列]，拆成 [省、市、区] 三列。
规则：
1. 分隔符是 [-，例如“北京市-朝阳区-望京街道”]，按此拆分。
2. 拆出的列分别命名为 [省、市、区]，放在原列右侧。
3. 拆不完整（缺少部分）的行标记出来给我确认，不要丢弃数据。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 展示拆分前后的对照示例（前 5 行即可）。
适用于姓与名、省与市等多列需要拼成一列的情况。
方式一：手工操作
方法：使用 & 连接符
1. 在空白列第一格输入 =A2&" "&B2（把 A 和 B 合并，中间加空格）
2. 回车后下拉填充
3. 复制这列 → 右键 → 选择性粘贴 → 值，删掉原列
小技巧：
• =A2&B2 表示无缝拼接
• =A2&"-"&B2 表示中间加横线
• 日期或数字合并会变乱码，先对它们用 TEXT 函数：=A2&TEXT(B2,"yyyy-mm-dd")
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：把 [姓] 和 [名] 两列合并为一个“姓名”列。
规则：
1. 合并后 [中间加一个空格 / 不加分隔 / 中间加“-”]。
2. 如果待合并列里有空值，处理策略：[例如“空值合并时自动忽略，不留多余分隔符”]。
3. 合并结果放新列“姓名”，不删除原列。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 展示前 5 行合并效果给我确认。
适用于存在空白单元格、需要删除或填充的情况。
方式一：手工操作
方法：F5 定位批量处理
1. 选中整个数据区域
2. 按 F5（或 Ctrl + G）→ 点「定位条件」
3. 勾选「空值」→ 确定
4. 所有空格子已被选中，现在可以直接处理：
• 输入内容后按 Ctrl + 回车，一次性填到所有空格
• 或右键 → 删除 → 整行，删掉所有含空值的行
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：处理数据中的空值。
规则（请逐列处理）：
1. [订单号] 列为关键字段，该列有空值的行：[删除该行 / 标记给我确认]。
2. [年龄] 列：用整列的中位数填充。
3. [备注] 列：空值填充为“无”。
4. [电话] 列：先统计缺失数量，告诉我再决定是否处理。
5. 先统计每列的空值数量并列出，确认无误后再执行。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 给出处理前后每列空值数量的对比表。
适用于邮箱、拼音姓名等大小写不统一的情况。
方式一：手工操作
目的 | 公式 |
全变大写 | =UPPER(A2) |
全变小写 | =LOWER(A2) |
每个单词首字母大写 | =PROPER(A2) |
1. 输入公式后回车
2. 双击右下角黑色小方块下拉填充
3. 复制 → 选择性粘贴为值
4. 删除辅助列
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请清洗 [文件名] 这个 Excel 文件。
任务：统一 [邮箱、姓名 等列] 的大小写。
规则：
1. [邮箱] 列全部转为小写。
2. [姓名拼音] 列转为每个单词首字母大写（如 zhang san → Zhang San）。
3. 其余列如有大小写不统一的情况一并列出给我看。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 完成后展示前 10 行转换效果。
适用于粘贴进来的数据带各种颜色、边框等杂乱格式的情况。
方式一：手工操作
1. 选中数据区域
2. 点击 开始 → 清除 → 清除格式（只清格式不动数据）
3. 如果格式乱到没法看：先复制数据 → 右键 → 选择性粘贴 → 值和数字格式，贴到新区域
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请处理 [文件名] 这个 Excel 文件。
任务：清理表格的多余格式，让数据干净规整。
规则：
1. 清除单元格的填充色、边框、超链接等多余格式，保留数据内容。
2. 全表使用统一字体（如微软雅黑）、统一字号。
3. 表头加粗、设置底色，列宽自动适配内容。
4. 数字列统一格式：[如保留两位小数、千分位分隔]。
要求：
- 原始数据保持不变，清洗结果写入新增的“清洗结果”Sheet。
- 处理后整体风格统一、专业，可直接打印或汇报使用。
清洗完成后，做这几步防止以后再录错。
方式一：手工操作
核对重复值
1. 选中数据区域
2. 点击 开始 → 条件格式 → 重复值，重复行会标红
设置下拉列表，防止乱录
1. 选中要限制输入的列
2. 点击 数据 → 数据验证（有效性）→ 允许选「序列」
3. 来源里输入可选值，用英文逗号隔开，如 在职,离职,休假
4. 确定后，单元格出现下拉箭头，只能选列表里的值
冻结表头，方便检查
1. 点击 视图 → 冻结窗格 → 冻结首行
方式二：交给 AI 处理
把下面提示词粘贴给 AI，替换 [方括号] 里的内容即可。
请处理 [文件名] 这个 Excel 文件。
任务：为表格添加防错设置和数据校验。
规则：
1. [状态] 列设置下拉列表，可选值：在职、离职、休假，防止录入错误。
2. [年龄] 列设置数据验证：只允许 0~120 的整数，超范围提示错误。
3. [邮箱] 列校验格式是否包含 @，不含的标出来给我确认。
4. 检查 [订单号] 列是否有重复值，列出重复的行号。
要求：
- 原始数据保持不变，处理结果写入新增的“校验结果”Sheet。
- 每项检查给出结论和数据。
表里同时存在多种问题时，推荐交给 AI 一次做完；手工方式则按上面十个场景依次执行即可。
把下面提示词粘贴给 AI，替换 [方括号] 里的内容：
请完整清洗 [文件名] 这个 Excel 文件，执行以下操作：
1. 探查数据：先读取并分析，向我汇报表头结构、行列数、每列类型、空值数量、重复行数量、明显的异常数据。
2. 逐项清洗：
- 删除重复行（依据：[列名]）
- 清理 [列名] 的空格和特殊字符
- 修正 [列名] 的格式（文本数字、日期、大小写）
- 拆分 [列名] 为 [A、B] 两列
- 填充/处理空值（策略：[说明]）
- 标记异常值（如年龄超出 0~120、金额为负）
3. 每个步骤先说明将做什么、影响多少行，再执行。
要求：
- 原始数据原样保留，所有清洗结果写入“清洗结果”Sheet。
- 最后生成一份清洗报告：每步做了什么、改了哪些列、删了多少行、剩余多少行、哪些数据异常需要我人工确认。
