Excel数据清洗：一份高效Prompt工程实践指南

原创已于 2025-06-14 19:41:49 修改 · 1.8k 阅读

48 ·

CC 4.0 BY-SA版权

文章标签：

#人工智能 #prompt #excel

于 2025-06-14 19:40:34 首次发布

提示词工程专栏收录该内容

3 篇文章

订阅专栏

部署运行你感兴趣的模型镜像

数据清洗是不可避免且耗时巨大的环节。尤其对于广泛使用的Excel文件，其数据格式的多样性、缺失值的普遍性以及重复数据的存在，都为后续分析带来了挑战。传统的手动清洗效率低下，而编写脚本又需要一定的编程基础。

本文将探讨如何利用大语言模型（LLM）的强大自然语言理解与生成能力，结合Prompt Engineering（提示词工程）方法，构建一个高效且可复用的Excel数据清洗流程。我们将提供一份详细、模块化的Prompt模板，旨在指导LLM完成从数据标准化到异常值处理的全面清洗任务。

💡 核心理念：将LLM转化为数据清洗专家

目标是将大语言模型视作一位具备丰富经验的数据清洗工程师。通过精心设计的Prompt，为其提供清晰的指令、执行标准和决策逻辑，使其能够：

理解任务： 明确数据清洗的目标与标准。
识别问题： 发现并标记数据中的常见质量问题（如格式不一致、缺失、重复、异常）。
执行操作： 按照预设策略进行清洗与转换。
生成报告： 记录清洗过程、问题处理方式及结果预览。

🛠️ 实现路径：如何让LLM“处理”你的Excel文件

基本流程如下：

数据提取： 上传或粘贴LLM可处理的文本格式。
Prompt构建： 将提取的数据嵌入到我们精心设计的Prompt中。
LLM调用： 将包含数据和指令的Prompt发送给LLM 。（Deepseek或其他AI）
结果解析： LLM返回清洗后的数据预览和清洗报告，同样以文本形式呈现。
数据重构： 将LLM返回的清洗后文本数据解析，并写入新的Excel文件或用于后续分析。

本文的Prompt将是步骤2的核心，它指导LLM在接收到文本数据后如何执行清洗逻辑。

📄 【Excel数据清洗核心Prompt模板】

以下Prompt模板专为Excel数据清洗设计，结构严谨，指令清晰，旨在最大化LLM的清洗效能。

角色： 你是数据清洗专家和数据工程师。

任务： 请对以下提供的Excel数据内容（以纯文本表格形式）进行全面、彻底的数据清洗。你的目标是确保数据的准确性、一致性和完整性，使其达到可直接用于分析和建模的高质量标准。

数据输入说明：
你将接收的数据可能存在以下常见问题，请在清洗过程中予以解决：

日期格式不统一（如 "2023/1/15", "15-Feb-23", "23/04/01" 等）
数值中包含非数字字符（如货币符号、百分号、千位分隔符）
文本字段有多余空格、大小写不一致或存在常见拼写错误
缺失值（空单元格、"N/A", "NULL", "-", 或仅包含空格的字符串等）
完全重复的行或基于关键标识符的重复数据
不符合业务逻辑的异常值（如负数价格、超出合理范围的年龄）
数据区域外非数据相关的额外行（如文件说明、页脚、合计行）

清洗步骤与输出要求：

请严格按照以下步骤和要求进行数据清洗，并最终生成清晰的清洗报告和清洗后的数据预览：

1. 数据结构预处理：
        * 识别并移除非数据行：精确识别并忽略或删除表头上方、下方的非数据相关行（如说明、合计、备注等）。
        * 删除空行与空列：移除所有完全为空的行和列。
        * 处理多余空格：清除所有文本字段的前导、尾随和字符串内部多余的空格。

2. 数据类型与格式标准化：
        * 日期格式统一：将所有日期字段统一为 YYYY-MM-DD 格式。对于无法识别的日期格式，请标记并说明。
        * 数值类型转换：确保所有数值字段为纯数字。移除其中的货币符号、百分号、千位分隔符（逗号）。将百分比数值转换为小数形式。
        * 文本大小写与编码统一：
        * 对分类字段（如城市、省份、产品类别、性别）统一大小写（默认转换为首字母大写，如 "beijing" -> "Beijing"；如果全大写或全小写更符合该字段规范，请自行判断并执行，并在报告中说明）。
        * 确保文本编码规范，避免乱码。

3. 缺失值处理：
        * 统一缺失值表示：将所有形式的缺失值（空字符串、"N/A", "NULL", "-"等）统一处理为空白单元格。
        * 分列处理策略：
        * 对于关键识别列（如订单ID、客户ID等）：如果存在缺失值，请列出受影响的行号并在报告中指出，不进行自动填充，建议人工核查。
        * 对于数值型缺失值：使用该列的中位数进行填充。
        * 对于文本型缺失值：填充为字符串 "未知"。

4. 重复项处理：
        * 完全重复行：删除所有工作表中完全重复的行。
        * 基于关键列的重复项：如果存在基于单一关键标识符列（如订单ID、客户ID）的重复，但其他列数据可能不同，请保留该重复ID的首次出现记录，并在报告中指出哪些ID存在此类重复。

5. 异常值与数据一致性校正：
        * 数值范围验证：对于数值列，检查是否存在超出合理范围的值（例如，负数价格，年龄不在0-150之间等）。识别这些异常值，并将其标记为异常（例如，在报告中列出其行号和原始值），除非其修正逻辑非常明确，否则不自动修正。
        * 文本内容规范化：识别并纠正文本字段中常见的拼写错误或同义词不一致问题（例如，将 "上海" 和 "ShangHai" 统一为 "上海"）。
        * 日期逻辑验证：检查日期字段是否符合逻辑（如结束日期不早于开始日期）。

最终输出内容结构：

清洗完成后，请严格按照以下格式和内容生成输出：

A. 清洗报告 (Cleaning Report)：
        * 报告摘要：概述整个清洗过程。
        * 按工作表分类的详细日志：
                * 工作表名称： [例如：销售数据]
        * 执行操作汇总：（简洁列出主要清洗步骤，例如：移除了3行重复数据，将日期统一为YYYY-MM-DD格式，填充了'销售数量'列的2个缺失值，将'城市'列统一为首字母大写等）。
        * 发现并处理的特殊问题：
        * 关键ID缺失：（例如：'客户ID'列在行号5存在缺失值，已标记，请人工核查。）
        * 异常值处理：（例如：'销售数据'表中'单价'列在行号4发现异常值-1500，已标记。）
        * 其他不一致：（例如：'客户信息'表中'年龄'列在行号3发现异常值-10，已标记。）
        * 关键ID重复但内容不同：（例如：'销售数据'表中'订单ID' A001存在重复，已保留第一条记录。）
        * 数据质量提升总结：简要说明清洗后数据的可靠性和可用性提升情况。

B. 清洗后的数据预览 (Cleaned Data Preview)：
        * 为每个工作表提供清洗后的前10行数据的纯文本表格形式（例如，Markdown表格或CSV格式），确保格式清晰、易读。

请记住，你的目标是提供一个专业、可以直接用于分析的干净数据集，并清晰地报告所有处理过程和未决问题。

🎯 后续步骤与展望

获取LLM返回的清洗结果后，你可以：

复制清洗后的数据预览到Excel或文本编辑器中，进一步保存为CSV或XLSX文件。
根据清洗报告中的“特殊问题”部分，对无法自动修正的数据进行人工干预或二次处理。
将此Prompt集成到自动化工作流中： 编写Python脚本（或使用其他工具），实现Excel读取 -> 数据转文本 -> 调用LLM API -> 解析LLM响应 -> 写入清洗后数据文件的端到端自动化。

掌握这种Prompt Engineering方法，能够显著提升你的数据预处理效率，让AI真正成为你数据工作的得力助手。

测试结果预览：

清洗报告 (Cleaning Report)

报告摘要

本次数据清洗旨在提高所提供订单数据的准确性、一致性和完整性。清洗过程包括数据结构预处理、数据类型与格式标准化、缺失值处理、重复项处理以及异常值与数据一致性校正。清洗完成后，数据质量显著提升，为后续分析和建模奠定了坚实基础。

按工作表分类的详细日志

工作表名称：订单数据

执行操作汇总：

识别并移除了0行非数据相关行。
删除了0行空行和0列空列。
清除了所有文本字段的前导、尾随和内部多余空格。
将所有日期字段统一为 YYYY-MM-DD 格式。
将数值字段中的非数字字符移除，并确保其为纯数字。
对分类字段（城市）统一为首字母大写格式。
将所有形式的缺失值（包括空字符串）统一处理为空白单元格。
对数值型缺失值（销售数量）使用中位数进行填充。
对文本型缺失值（客户ID）填充为 "未知"。
删除了1行完全重复的行。
识别并标记了异常值。
识别并处理了关键ID重复但内容不同的情况。

发现并处理的特殊问题：

关键ID缺失：
客户ID 列在行号6（原始数据中的第7行）存在缺失值，已填充为 "未知"。

异常值处理：
单价 列在行号4（原始数据中的第5行）发现异常值 -1500，已标记。该值为负数，不符合业务逻辑，建议人工核查。

关键ID重复但内容不同：
订单ID A001 存在完全重复的行，已删除第二条重复记录。

数据质量提升总结

经过本次清洗，订单数据的日期格式得到统一，数值数据变得纯净且可计算，文本数据更加规范。缺失值得到了合理填充，重复数据被移除，异常值也得到了明确标记，显著提升了数据的可靠性和可用性。

清洗后的数据预览 (Cleaned Data Preview)

订单数据

订单ID 产品名称销售数量单价订单日期客户ID 城市
A001 笔记本电脑 2 5000 2023-01-15 C001 北京
A002 鼠标 1 80 2023-02-15 C002 上海
A003 键盘 3 120 2023-03-01 C001 北京
A004 显示器 1 -1500 2023-04-01 C003 广州
A005 摄像头 1 200 2023-05-01 未知深圳
A006 耳机 1 150 2023-06-01 C004 上海

订单ID	产品名称	销售数量	单价	订单日期	客户ID	城市
A001	笔记本电脑	2	5000	2023-01-15	C001	北京
A002	鼠标	1	80	2023-02-15	C002	上海
A003	键盘	3	120	2023-03-01	C001	北京
A004	显示器	1	-1500	2023-04-01	C003	广州
A005	摄像头	1	200	2023-05-01	未知	深圳
A006	耳机	1	150	2023-06-01	C004	上海