获课：xingkeit.top/17157/

硬核技术分享：VBA 宏循环嵌套 GPT 对话，一键清洗海量表格数据

数据清洗是数据分析中最枯燥也最耗时的环节。去年年底，我所在团队收到一批来自业务部门的客户反馈表格——上百个Excel文件，每个文件包含数千条非结构化的文本描述，格式混乱、缩写遍地、中英文混杂、同一概念有多种表述。传统的数据清洗方式需要人工逐条核对，团队计划用两周完成这批数据的标准化，但实际执行一周后发现进度严重滞后，且不同人的清洗标准不统一，数据一致性堪忧。

我们急需一种自动化方案，但问题在于：这种文本清洗任务需要语义理解——比如识别“收货地址不详”和“地址错误”实际上是同一类问题，传统规则引擎无法覆盖所有变体。而直接购买大模型API服务批量处理又面临数据安全合规的问题（客户信息不能上传公网）。最终我们摸索出一条务实的工程路径：用VBA宏控制Excel遍历所有文件，对每条待清洗数据调用本地部署的开源大模型进行语义标准化处理。这套方案最终将两周的工作量压缩到了半天，且清洗质量的一致性远超人工处理。以下是技术思路与关键经验。

为什么是VBA + 本地大模型，而不是Python + 云端API？

坦白说，这个技术组合看起来有些“复古”。但选择它有非常现实的理由。

第一，业务部门的原始数据全部在Excel中，且各部门长期使用VBA宏做日常自动化，用VBA作为控制层可以将脚本直接嵌入工作簿，业务同事拿到文件双击运行即可，无需安装Python环境或配置复杂的开发环境。第二，数据涉及客户姓名、电话、地址等敏感信息，绝对不能出内网。我们在内网服务器上部署了经过量化的Qwen2.5 7B模型，通过HTTP接口对内网应用开放调用，所有数据在本地GPU上完成处理，满足合规要求。

这套方案的架构极为轻量：VBA宏作为“总调度”，遍历文件夹中的所有Excel文件，逐行读取待清洗字段，构造Prompt发送到本地大模型服务接口，接收返回的标准化结果并写回Excel。每个步骤的执行状态都会记录在日志工作表中，便于排查中断点。

Prompt设计：清洗规则的一次性编码

在这个方案中，清洗规则不是写在代码里的正则表达式，而是写在Prompt里的自然语言指令。这是一个根本性的思路转变——当清洗规则需要调整时，你不需要修改VBA代码再重新分发脚本，只需要更新Prompt模板中的描述即可。

我们的Prompt模板经过多轮迭代后形成了稳定版本，结构大致包含几个层次：首先明确告诉模型需要完成的清洗任务是什么（如“将客户反馈描述标准化为统一的问题分类”）；然后给出分类标准列表，列举所有可能的标准化类别及其定义；接着给出几个经典示例，展示如何从原始文本映射到标准类别；最后强调格式约束——只输出类别编号和标准描述，不添加额外解释，严格遵循示例格式。

这个Prompt设计被证明是整个系统的灵魂。它把业务规则从代码中剥离出来，变成了可自然语言迭代的知识资产。业务方确认清洗规则有误时，不需要等开发排期改代码，直接在Prompt模板中修改描述即可。

并发与健壮性设计：别让宏跑崩Excel

VBA原生不支持多线程，加上本地大模型的推理速度有上限（简单分类每条约几百毫秒），这意味着整个清洗过程是串行执行的，万条量级的数据处理总耗时在一小时左右。这个速度可以接受，但必须考虑中途中断后如何续跑。

我们做了一套简洁的“断点续跑”机制。VBA宏在执行每条数据清洗前，先检查该行是否已有清洗结果标记，如果有则跳过。这样即使Excel意外关闭或网络波动导致某次请求超时，重新运行宏时会自动从第一条未完成的数据继续，不会重复处理已完成的数据。配合每处理50条数据自动保存一次工作簿的机制，将意外中断可能造成的进度损失降到了最低。

另一个必须处理的问题是HTTP请求的超时和重试。我们为VBA中的HTTP请求设置了较长的超时时间，并在请求失败时实施简单重试策略——第一次失败等待后重试，第二次失败记录错误并跳过该条数据，在日志中标注为“需人工复核”，避免单条数据问题阻塞整个流程。

清洗质量评估：一致性远超预期

上线后的第一轮测试，我们从数据集中随机抽取了五百条清洗结果，由业务专家进行人工复核。结果显示，分类准确率超过九成。更重要的是，相同语义的原始描述（如“缺货”“库存不足”“没货了”）被稳定地归入了同一标准类别，而这种一致性在人工清洗中几乎无法保证。

唯一的“重灾区”是那些原始信息极度缺失或语义模糊的记录，例如仅包含“不好”两个字的反馈，大模型也无法准确归类。这类数据被自动标记为“需人工复核”，数量占比约百分之几，完全在可接受范围内。

这套方案的适用边界

必须诚实地指出这套方案的适用边界。它适合的场景是：数据量大但单条清洗逻辑清晰、存在语义变体但标准化目标明确、数据敏感不能出内网。它不太适合的场景是：需要跨字段复杂推理的数据清洗（如结合客户历史订单做综合判断）、对实时响应要求极高的场景（串行处理吞吐量有限）、以及完全无规则的开放性文本（大模型也无法从无意义信息中提炼出结构化数据）。

另外，这套方案成功的前提是本地大模型的部署和调用链路的稳定。如果你们组织内部还没有建立起本地模型服务的工程能力，VBA这头的脚本写得再好也无法运转。

写在最后

VBA + 本地大模型的组合，本质上是一种工程实用主义的体现。它没有追求技术上的“高级感”，而是精准回应了真实业务场景中的几个核心约束——数据在Excel里、敏感不能上网、需要语义理解但预算有限。当技术工具的选择回到“解决实际问题”这个原点时，旧工具和新技术之间完全可以产生意想不到的化学反应。这套方案已经在我们的多个内部项目中复制使用，如果你也正在被海量非结构化表格数据困扰，不妨从这个思路切入试试。