夜雨聆风每个跟数据打交道的人都知道一个痛苦的真相：80%的时间花在清洗数据上，只有20%的时间用在分析上。Excel打开一看——日期格式不统一、有空行有空列、数字里混着文本、重复记录、编码乱码……光把这些理顺就要一天。
数据清洗不是体力活，是判断活。哪些缺失值该删除、哪些该填充、哪些是异常值该剔除——这些判断决定了最终分析结果的准确性。AI能帮你的不是自动判断，而是把机械重复的清洗工作自动化，把判断权留给你。
一、数据清洗的5个标准步骤
步骤1：数据诊断
先搞清楚你的数据"脏"在哪里。常见问题分6类：
- 缺失值
：某些单元格为空（可能是真缺失，也可能是0被误录为空） - 格式不统一
：日期有"2024-01-01"也有"2024/1/1"还有"1月1日" - 数据类型错误
：数字列里混入了文本（如"约100"），日期列变成了文本 - 重复记录
：完全重复或部分重复（同一个人录入两次） - 异常值
：明显不合理的数据（如年龄300岁、月薪负数） - 编码问题
：中文乱码、全半角混用、多余空格
步骤2：缺失值处理
根据缺失原因选择策略：
原则： 缺失率<5%可删除行，5-30%考虑填充，>50%考虑删除列。
步骤3：格式标准化
统一所有字段的格式：
日期统一为 YYYY-MM-DD金额统一为数字类型（去掉"¥""元"等符号） 文本去除首尾空格、统一全半角 分类变量统一大小写（如"Male"/"male"统一为"男"）
步骤4：去重
- 完全重复
：所有字段完全一致 → 直接删除 - 部分重复
：关键字段一致（如身份证号相同）→ 保留最新记录或合并信息
步骤5：异常值检测
3种常用方法：
- 3σ原则
：超过均值±3个标准差的数据视为异常（适合正态分布） - IQR法
：超出Q1-1.5×IQR到Q3+1.5×IQR范围的数据视为异常（适合偏态分布） - 业务规则
：根据业务逻辑判断（如年龄<0或>120必定异常）
二、4个可复制Prompt模板
Prompt 1：数据诊断
把数据字段信息发给AI（推荐通义千问，免费且表格处理能力强）：
我有一份Excel数据需要清洗，数据字段如下： 字段名 | 数据类型 | 示例值 | 备注 [粘贴你的字段信息，如：] 订单号 | 文本 | ORD20240101 | 部分记录缺失 下单日期 | 日期 | 2024/1/1 | 格式不统一，有2024-01-01和2024/1/1 客户姓名 | 文本 | 张三 | 有多余空格 订单金额 | 数字 | 299.00 | 部分含"¥"符号 支付方式 | 文本 | 微信 | 大小写不统一（微信/微信支付/WECHAT） 产品类别 | 文本 | 数码 | 部分缺失 数据总量：5000行 请诊断这份数据可能存在的问题，按以下格式输出： 1. 缺失值问题（哪些字段缺失，建议处理方式） 2. 格式不统一问题（哪些字段需要标准化，怎么标准化） 3. 重复记录风险（哪些字段组合可作为唯一标识判断重复） 4. 异常值风险（哪些数值字段可能出现异常值，用什么方法检测） 5. 其他问题（编码、空格、数据类型等）Prompt 2：生成Excel清洗公式
我的Excel数据有以下问题，请给出对应的Excel公式解决方案： 问题1：A列日期格式不统一，有"2024-01-01"、"2024/1/1"、"1月1日"三种格式 问题2：B列金额含"¥"符号和中文"元"，需要提取纯数字 问题3：C列客户姓名首尾有多余空格，部分名字中间有连续空格 问题4：D列支付方式需要统一映射（微信/微信支付/WECHAT→"微信"，支付宝/ALIPAY→"支付宝"） 问题5：E列有重复值，需要标记重复行（保留第一次出现） 请对每个问题给出： 1. Excel公式（兼容Office 2019以上版本） 2. 操作步骤（在哪个单元格输入、如何下拉填充） 3. 公式说明（每部分公式的含义） 注意：公式不要用新版函数（如LET、LAMBDA），保证兼容性。Prompt 3：Python清洗脚本
如果数据量大（>10万行），Excel公式会很卡，用Python更高效：
请用Python写一个数据清洗脚本，处理以下Excel数据： 文件路径：[填写] Sheet名：[填写] 需要执行的清洗步骤： 1. 读取Excel文件 2. 删除完全重复的行（基于所有列） 3. 处理缺失值： - [字段A]：缺失率<5%，删除缺失行 - [字段B]：缺失率10-30%，用该字段中位数填充 - [字段C]：缺失率>50%，删除整列 4. 格式标准化： - 日期列统一为YYYY-MM-DD格式 - 金额列去掉符号并转为float类型 - 文本列去除首尾空格、统一为半角 5. 异常值检测： - [数值字段]：用IQR法检测，标记异常值但保留（新增一列"异常标记"） 6. 输出清洗后的数据到新Excel文件 7. 输出清洗报告（原数据量、清洗后数据量、各步骤处理了多少条） 要求： - 使用pandas库 - 添加注释说明每步操作 - 输出清洗前后的数据量对比Prompt 4：异常值分析
我有一列数值数据需要做异常值检测，数据特征如下： 字段名：[如"月消费金额"] 数据量：[如3000条] 数据范围：0-50000（大致） 已知问题：有少量异常大值（可能录入错误），也有负数（不合理） 请用IQR法进行异常值分析： 1. 计算Q1、Q3、IQR 2. 确定异常值范围（Q1-1.5×IQR 到 Q3+1.5×IQR） 3. 给出Python代码：计算异常值数量、占比、列出前10个异常值 4. 给出处理建议： - 哪些异常值可能是录入错误（建议修正或删除） - 哪些可能是真实异常值（建议保留但标记） 5. 如果数据分布明显偏态（非正态），请同时给出3σ法和IQR法的对比结果 输出完整的Python代码和预期结果说明。三、工具选择
推荐组合：
小数据量：通义千问生成Excel公式 → Excel执行 大数据量：通义千问生成Python脚本 → 本地Python执行 复杂文本：Kimi上传文件 → 直接让AI分析清洗
四、完整实操案例
场景： 电商运营拿到一份5000行订单数据，需要清洗后做销售分析。
原始问题：
下单日期3种格式混用 订单金额部分含"¥"符号 客户姓名有空格 支付方式不统一 产品类别缺失率35% 12条重复记录 3条订单金额为负数
清洗流程：
用Prompt 1诊断 → 确认6类问题 用Prompt 2生成Excel公式 → 逐列标准化格式 用Excel条件格式标记重复行 → 删除12条重复 产品类别缺失率35% → 按订单金额分组填充（高金额订单归"数码"，低金额归"日用"） 3条负数订单 → 联系业务确认是退款记录 → 单独标记不删除 清洗后数据量：4985行（删除12条重复+3条无效记录）
耗时： AI辅助下40分钟完成，传统手动方式需要半天。
五、常见错误
错误1：直接删除所有缺失值
问题：可能删除了大量有效数据，特别是缺失率高的字段 正确做法：先分析缺失原因和缺失率，分类处理
错误2：用均值填充所有缺失值
问题：均值填充会拉平数据分布，影响分析结果 正确做法：数值型用中位数（抗异常值），分类型用众数，时间序列用前向填充
错误3：不做异常值检测直接分析
问题：几条异常值就能把均值和标准差带偏 正确做法：至少用IQR法扫一遍，标记可疑值
错误4：清洗后不保留原始数据
问题：清洗可能出错，需要回溯 正确做法：原始文件保留不动，清洗结果另存为新文件
局限性说明
AI生成的Excel公式可能不兼容旧版Office（2016以下），需要手动调整 Python清洗脚本需要本地安装pandas库（ pip install pandas openpyxl）缺失值填充策略需要业务知识判断，AI建议仅供参考 异常值是否为"真异常"需要业务确认，AI只能识别统计异常 复杂数据清洗（如地址解析、姓名脱敏）可能需要专业工具
扫码或搜索微信号 lingshu202688，加入灵枢OPC社群
每周二免费会员日（腾讯会议15:00-17:00），AI技能现场答疑
