AI 清洗 Excel，最危险的不是报错，而是悄悄改错：一套可验收的完整工作流

AI 告诉你：“表格已经清洗完成。”

你打开文件，日期格式统一了，金额列也整齐了，空白单元格似乎都被处理好了。但仔细检查才发现：

• 01/02/2025 被直接解释成了 2025 年 2 月 1 日；
• 空金额被自动补成 0；
• (299.00) 这个负数变成了正数；
• 长期 被强行转换成日期；
• 1.3k 有时变成 1300，有时仍然是文本。

这类错误比程序报错更危险。因为它们看起来合理、不会弹窗，却可能直接进入财务统计、销售报表和经营决策。

真正可靠的 AI 数据清洗，不能只靠一句“帮我整理这个 Excel”。你需要的是一套能够回答以下问题的工作流：

改了什么？为什么改？哪些数据没有把握？出错后能否定位、回滚和重新执行？

一句话清洗，为什么总会失控

假设一份订单表中同时出现这些数据：

配图 1：原始 Excel 中日期、金额和空值混乱的局部截图。姓名、手机号、订单号应全部脱敏。

问题并不只是“AI 不够聪明”，而是 Excel 单元格存在三层信息：

1. 底层值：例如日期序列号 45659；

2. 显示格式：Excel 可能把它显示为 2025/1/2；

3. 业务语义：— 可能代表缺失，也可能代表不适用。

如果不提前定义规则，AI 只能根据上下文猜测。于是最常见的三类事故就出现了：

• 歧义日期被自动选择为美式或英式格式；
• 金额单位、币种或负号在转换中丢失；
• 空值被擅自补成 0、平均值或其他“看起来合理”的内容。

表格变整齐，不等于数据变正确。无法验收的清洗结果，本质上仍然不可交付。

配图 2：同一批数据直接交给 AI 清洗后的对比截图，重点标出日期误判、空金额补零和括号负数误改。

先写字段规则，再让 AI 动数据

正确顺序不是“先清洗，再检查”，而是先定义什么叫正确，再执行转换。

可以为每个字段建立一张“字段规则卡”：

配图 3：字段规则表截图，包括目标类型、空值策略、自动转换条件和复核条件。

规则至少要回答六个问题：

1. 目标类型是什么；

2. 允许哪些输入格式；

3. 空值如何处理；

4. 哪些情况可以自动转换；

5. 哪些情况禁止猜测；

6. 异常应使用什么标签。

例如，01/02/2025 并不能仅凭文本确定是 1 月 2 日还是 2 月 1 日。正确做法不是要求 AI“选一个最可能的”，而是输出：

{
"row_id": 1024,
"field": "order_date",
"raw_value": "01/02/2025",
"clean_value": null,
"status": "review",
"reason_code": "AMBIGUOUS_DATE",
"reason": "无法确定为1月2日或2月1日",
"confidence": 0.42
}

这里的 confidence 只适合用来排序，不能当作经过校准的真实正确概率。

同时，任何字段都不要直接覆盖。至少保留：

• raw_value：原始值；
• clean_value：清洗结果；
• status：pass、review 或 reject；
• reason：处理依据；
• confidence：模型判断参考值。

配图 4：增加 raw_value、clean_value、status、reason、confidence 后的结果表。

把 AI 从“自由发挥”改成“结构化判断”

可以直接复用下面这份提示词模板：

你是数据清洗审核器，不是自由补全助手。

任务要求：
1. 只能根据提供的字段规则判断，不得使用未经声明的假设。
2. 不确定时必须输出 review，不得自行补值。
3. 必须完整保留 raw_value。
4. 只能输出固定 JSON，不得输出解释性文字。
5. 必须提供 reason_code 和 reason。
6. 不得直接修改源文件。
7. 空值不得默认补 0。
8. 歧义日期不得自行决定月日顺序。
9. 金额的币种、单位或正负号不明确时，必须进入 review。

允许的 status：
pass：规则明确，可安全转换；
review：存在歧义，需要人工确认；
reject：违反字段规则，且无法进入最终结果。

这一步的核心，是把 AI 的任务从“替我清洗表格”，缩小成：

根据规则，对单条数据进行判断，并返回机器可验证的 JSON。

自动通过与异常队列：让 AI 只处理它擅长的部分

一套稳定的流水线应该有两个通道。

自动通过通道

适合处理确定性较高的数据：

• ¥1,299.00 转为 1299.00；
• (299.00) 转为 -299.00；
• 45659 按 Excel 1900 日期系统转换为 2025-01-02；
• N/A、—、null 统一为空值，但保留原始标记；
• 50万 在字段已明确为人民币金额时转为 500000。

异常队列

以下记录不应自动写回：

• 01/02/2025：月日歧义；
• 1.3k：业务没有定义 k 是否等于千；
• 金额只有数字，但币种无法确认；
• 关键字段为空；
• 金额超过业务允许范围；
• 订单总金额与明细合计不一致；
• 日期早于系统上线时间或晚于当前业务周期。

异常队列还应按风险排序。例如，金额异常的优先级通常高于客户备注格式异常，跨字段冲突又可能高于单字段格式问题。

人工并不是重新检查全部数据，而是只处理模型无法安全判断的记录。

配图 5：exception_queue.xlsx 按“日期歧义”“币种缺失”“金额超范围”“空值不可补”等类型筛选。

一段可落地的 Python 清洗骨架

下面的示例覆盖原值保留、空值标准化、确定性金额转换、Excel 日期转换、AI JSON 判断、异常导出和抽样验收。

import os
import re
import json
import hashlib
from decimal import Decimal, InvalidOperation
from datetime import datetime, timedelta

import pandas as pd
from openai import OpenAI

df = pd.read_excel(
"orders.xlsx",
dtype=str,
keep_default_na=False
)

1. 永远保留原始字段
df["amount_raw"] = df["amount"]
df["order_date_raw"] = df["order_date"]

NULL_MARKERS = {"", "N/A", "NA", "null", "NULL", "-", "—"}

def normalize_null(value):
text = str(value).strip()
return None if text in NULL_MARKERS else text

df["amount_normalized"] = df["amount"].map(normalize_null)
df["order_date_normalized"] = df["order_date"].map(normalize_null)

2. 确定性金额清洗
def clean_amount(value, allow_k=False, field_currency="CNY"):
if value is None:
return None, "review", "AMOUNT_MISSING"

text = str(value).strip()
negative = text.startswith("(") and text.endswith(")")
if negative:
text = text[1:-1].strip()

# 出现其他币种符号时，不擅自换算
if any(symbol in text for symbol in ["$", "€", "£"]):
return None, "review", "CURRENCY_UNCONFIRMED"

text = text.replace("¥", "").replace("￥", "").replace(",", "")
multiplier = Decimal("1")

if text.lower().endswith("k"):
if not allow_k:
return None, "review", "UNIT_K_UNDEFINED"
multiplier = Decimal("1000")
text = text[:-1]
elif text.endswith("万"):
if field_currency != "CNY":
return None, "review", "CURRENCY_UNCONFIRMED"
multiplier = Decimal("10000")
text = text[:-1]

if not re.fullmatch(r"[+-]?\d+(\.\d+)?", text):
return None, "review", "INVALID_AMOUNT_FORMAT"

try:
amount = Decimal(text) * multiplier
if negative:
amount = -amount
return str(amount.quantize(Decimal("0.00"))), "pass", "RULE_CONVERTED"
except InvalidOperation:
return None, "review", "AMOUNT_PARSE_FAILED"

3. Excel 日期序列号转换
def excel_serial_to_date(value):
text = str(value).strip()

if not re.fullmatch(r"\d+(\.0+)?", text):
return None, "review", "NOT_EXCEL_SERIAL"

serial = int(float(text))
if serial <= 0 or serial > 100000:
return None, "review", "DATE_SERIAL_OUT_OF_RANGE"

result = datetime(1899, 12, 30) + timedelta(days=serial)
return result.strftime("%Y-%m-%d"), "pass", "EXCEL_SERIAL_CONVERTED"

4. 调用 AI，只接收 JSON
client = OpenAI(
api_key=os.environ["AI_API_KEY"],
base_url=os.environ["AI_BASE_URL"]
)

SYSTEM_PROMPT = """
你是数据清洗审核器。只能依据字段规则判断。
不确定时输出 review，不得猜测或补值。
保留 raw_value，只返回 JSON。
字段必须包含 row_id、field、raw_value、clean_value、
status、reason_code、reason、confidence。
不得直接修改源文件。
"""

def ai_judge(payload):
response = client.chat.completions.create(
model=os.environ["AI_MODEL"],
temperature=0,
response_format={"type": "json_object"},
messages=[
{"role": "system", "content": SYSTEM_PROMPT},
{"role": "user", "content": json.dumps(
payload, ensure_ascii=False
)}
]
)
return json.loads(response.choices[0].message.content)

5. 导出异常队列
假设前序处理已生成 status、reason_code、reason 等列
review_df = df[df["status"].isin(["review", "reject"])].copy()
review_df.to_excel("exception_queue.xlsx", index=False)

6. 分层抽样：正常记录随机抽，风险记录重点抽
pass_df = df[df["status"] == "pass"]
risk_df = df[
(df["confidence"].astype(float) < 0.8)
| (df["reason_code"].isin([
"AMBIGUOUS_DATE",
"AMOUNT_MISSING",
"CURRENCY_UNCONFIRMED",
"AMOUNT_OUT_OF_RANGE"
]))
]

random_sample = pass_df.sample(
n=min(300, len(pass_df)),
random_state=42
)

risk_sample = risk_df.sample(
n=min(200, len(risk_df)),
random_state=42
)

validation_sample = pd.concat(
[random_sample, risk_sample]
).drop_duplicates()

validation_sample.to_excel("validation_sample.xlsx", index=False)

7. 生成基础验收统计
total = len(df)
auto_pass = int((df["status"] == "pass").sum())
exceptions = int(df["status"].isin(["review", "reject"]).sum())

report = pd.DataFrame([
{"metric": "处理总量", "value": total},
{"metric": "自动通过数", "value": auto_pass},
{"metric": "自动通过率", "value": auto_pass / total if total else 0},
{"metric": "异常数", "value": exceptions},
{"metric": "异常率", "value": exceptions / total if total else 0},
])

report.to_excel("validation_report.xlsx", index=False)

8. 记录源文件哈希，证明输入文件未被替换
def file_sha256(path):
sha = hashlib.sha256()
with open(path, "rb") as f:
for block in iter(lambda: f.read(1024 * 1024), b""):
sha.update(block)
return sha.hexdigest()

print("source_sha256:", file_sha256("orders.xlsx"))

如果所用接口不支持 response_format，也不要退回自然语言输出。可以采用“JSON Schema 校验＋失败重试”的方式，拒绝无法解析的结果。

如果你已经准备好字段规则和异常队列模板，下一步就是接入批处理脚本。可以前往 api.884819.xyz 获取 API 接入信息，先使用脱敏后的 50～100 行样本测试 JSON 稳定性，再逐步扩大批量，切勿把唯一一份原始 Excel 直接交给模型处理。

抽样复核，不是随便看几行

抽样最容易犯的错误，是从清洗后的表格里随机翻十几行，觉得“看起来没问题”就交付。

可靠的验收至少要分三层：

1. 正常记录随机抽样：发现系统性误改；

2. 规则边界重点抽样：检查日期边界、金额上下限和特殊格式；

3. 异常修复提高抽样比例：人工修复过的数据风险更高。

验收报告至少应包含以下指标：

• 字段有效率＝符合目标格式的记录数 ÷ 总记录数；
• 自动通过率＝无需人工处理的记录数 ÷ 总记录数；
• 异常率＝进入异常队列的记录数 ÷ 总记录数；
• 抽样错误率＝抽样中错误记录数 ÷ 抽样记录数；
• 误改率＝原本正确却被错误修改的记录数 ÷ 被修改记录数；
• 异常漏检率＝复核发现但系统未标记的异常数 ÷ 实际异常数。

高自动通过率不代表高质量。误改率和异常漏检率，往往比“处理了多少行”更重要。

以一份 10,000 行的模拟订单表为例：假设 8,900 行自动通过，1,100 行进入异常队列，那么自动通过率为 89%，异常率为 11%。

但这两个数字不能证明结果已经可靠。假设后续分层抽取 500 行，发现 3 行错误，则该次抽样错误率为：

3 ÷ 500 = 0.6%

这只是示例计算，并非通用合格线。是否允许交付，要看字段风险：客户备注出现少量格式问题，和财务金额、发票税额出现错误，显然不能使用同一标准。关键金额字段甚至可以设置为零容忍错误数。

配图 6：验收报告截图，展示处理总量、自动通过率、异常率、抽样错误率、误改率和验收结论。

而且，一次抽样合格不代表永久可靠。以下变化发生后，都应重新验收：

• 字段规则调整；
• 提示词发生变化；
• 模型或接口配置变化；
• 数据来源系统变化；
• 新增币种、地区或日期格式；
• 人工复核标准发生变化。

把一次清洗，升级成可重复运行的流水线

完整流程应该是：

flowchart LR
A[冻结原始文件] --> B[读取原始值]
B --> C[执行确定性规则]
C --> D[AI判断复杂值]
D --> E{状态分流}
E -->|pass| F[候选清洗结果]
E -->|review/reject| G[异常队列]
G --> H[人工复核]
F --> I[分层抽样]
H --> I
I --> J[验收报告]
J --> K[交付或退回重跑]

每次执行至少记录：

• 原始文件哈希；
• 字段规则版本；
• 提示词版本；
• 使用的模型名称；
• 处理时间；
• 程序版本；
• 人工修改人、修改时间与修改理由。

最终交付物也不应该只有一个“洗干净的 Excel”，而应包括四份文件：

1. source_locked.xlsx：冻结的原始文件；

2. cleaned.xlsx：通过验收的清洗结果；

3. exception_queue.xlsx：异常与人工处理记录；

4. validation_report.xlsx：指标、样本和验收结论。

这样，即使交付后发现问题，也能定位究竟是原始数据、字段规则、模型判断还是人工复核出了偏差，并据此回滚和重新执行。

真正可靠的 AI 数据清洗，不是得到一个更整齐的 Excel，而是能够证明：

哪些数据被改了，为什么改，哪些数据没把握，以及为什么最终结果可以使用。

先用小样本跑通“读取—判断—异常分流—验收”四步，再到 api.884819.xyz 配置批量调用。平台使用用户名和密码即可注册，不需要邮箱验证；内置 AI 对话，注册后可直接使用。国产模型如 Deepseek、千问等完全免费，没有月租和订阅，其他模型按量付费。

下一篇将继续解决一个更容易被忽略的问题：AI 清洗了几十万行 Excel 后，怎样设计分层抽样，才能用较少的人工复核发现高风险错误？届时会给出可直接运行的 Python 抽样脚本、风险权重设置方法，以及一份自动生成的 Excel 验收报告模板。

#AI教程 #Excel清洗 #数据处理 #Python #人工智能 #Prompt技巧 #8848AI