【有想法】系列之:用python把成本核算从需要数天缩到数分钟

📌 本文已收录进【有想法】系列 【系列全称】：python的世界里，不怕有想法就怕没想法 【系列定位】：用Python解决工作中的重复性劳动 【专栏名称】：【有想法】Python自动化实战

采购是我工作的一部分，每个月都要和供应商核算成本。上一期写的外购合同录入系统，其实是我采购工作里最轻松的活儿——真正复杂的，是跟供应商敲定成本。今天要写的这个脚本，就是用来啃这根最硬的骨头。

【有想法】系列 · 第3期

没错，上面那段就是我的真实写照；在经历了轻松的               脚本                ，今带大家来见识一下我那嘴硬的骨头是怎么啃下来的？！

这个涉及的表格多，要考虑的数据和因素也特别杂，我经常把自己绕懵。自从学了Python、尝到自动化的甜头后，我就一直惦记着把这块最难啃的骨头啃下来。这个脚本确实是我目前写过最复杂的，前前后后折腾了好几天才终于跑通。

【关于我】

工作负责采购和业务绩效提成的计算，整天和Excel打交道。工作里遇到的重复劳动，能自动化绝不手敲。如果你也有类似需求，欢迎交流。

【正文】

 先来展示一下成果——这就是我忙了好几天的劳动成果，倾注了无数心血，也消耗了无数脑细胞。

下面来看看，我花了这么大力气搞的脚本到底是个啥。

脚本能干什么

我把脚本取名为“数据处理助手”。之所以做成三个独立的按钮，是因为这个工作需要和同事接力完成——中间要换电脑操作。如果只给我自己用，可能双击就直接跑完了，也就不会有这么漂亮的界面了。

所需文件：含“未结算”字样的Excel文件，以及本脚本。

① 生成前期文件

从“未结算”表里提取“订单编号”列，其他列只留表头，生成“有待导入系统的前期文件”。这个文件用来导入业务平台，换回“已导出的前期文件”。

② 计算&判断（核心）

“未结算”表中的内容是供货商提供的，需要核对其中的数据及相关信息——生成前期文件的目的就在于此。

• 合并“未结算”表和第①步生成的已导出表，自动调换其中两列的位置（因为平台导出的列顺序与“未结算”表是反的，为了方便，我在脚本里直接做了调换）。
• 在“未结算”表的右侧自动写入16个业务公式（AU-BJ）、6个公式（CB~CG），以及一列判断公式（CA）。
• 按合同编码（BW列）+ AF列非空进行分组，组内对数量、出厂基准价、标准产品目录价、定制化附加成本等列求和，结果只保留每组第一行。
• 单独按合同编码清除非首行的BP列（实际合同额）。
• 条件格式自动涂色：    
• CE列（毛利率）≤5%且非空时，整行浅蓝色。
• BD和BH（双方数据计算所得的成本价）同时为“一致”时，整行浅粉色。
• 最终生成“未结算_已计算.xlsx”，包含所有公式和数据。

③ 生成导入系统文件

最终目的，其实就是为了生成导入系统的文件。前面第②步已经确认了数据，第③步就简单了：只需从已计算的文件中提取“订单编号”、“成本价”、“数量”、“采购合同号”、“币种”，再加上固定的供应商和部门，生成“导入系统文件”。

同时，如果订单编号包含“再利用”，备注填“再利用已考虑”；且，整行浅蓝色高亮。

 该文件夹中除了未结算是原始的表格，还有一个脚本；其余都是脚本生成的。

亮点

 浅绿色的是护眼色，是没有做涂色标识的，是需要人为去核实的； 浅蓝色是毛利率低于5%的，这是我需要重点关注的； 浅粉色的是两个一致，意味着供货商和我们计算的结果是一致的，标识为浅粉色，对于这种颜色，我完全可以不用看的；

怎么用

1. 环境准备
   ：pip install openpyxl
2. 准备文件
   ：将脚本与未结算表、已导出表放在同一文件夹。
3. 运行
   ：双击脚本 → 依次点击三个按钮。
4. 打包
   ：pyinstaller --onefile --windowed 脚本.py 生成exe，发给同事直接用。

核心代码片段

这次不贴具体公式了（太长），只展示让脚本稳定下来的关键选择。

最重要的选择：固定列号——这个决定，是让这次脚本画上圆满句号的前提。

用固定列号的代价是：模板的列顺序绝对不能变。好处是：再也不用担心表头名称是“合同编号”还是“合同编码”这种细微差别了。在稳定的业务环境中，硬编码往往比动态映射更可靠。

最核心的部分：分组求和和条件判断，其他都是体力活。

曲折过程：我被“万能通用”害惨了

这个脚本前后改了十几版，不是因为逻辑难，而是我总想写“通用代码”。

第一版：动态找列，结果列永远找不对 我写了一个漂亮的函数，根据表头名称自动返回列号。结果表头里有“合同编号”和“合同编码”两个不同的名字，还有“订单号”和“订单编号”，一不留神没分清，公式写进去全乱套。

我其实特别抵触用固定列，一开始一直坚持用表头来对应，试了好几回都不行。最后我妥协了，选择了固定列。如果一开始就放弃动态映射，应该就不会那么费劲。以后要灵活一些——对于这种模板不会变的业务，完全可以采用固定列的思路，不要没苦硬吃。

第二版：分组逻辑搞错了 一开始想按合同编码分组求和，于是写了个简单的按BW列分组。后来发现不对，应该是“同一合同编码下，AF列有数字的行才能作为一组”。于是改。再后来，我觉得BP列要单独按合同编码清除非首行，又改。最后发现其实是两组逻辑：一组给公式列，一组给BP列。把两组合在一起搞，越搞越乱。后来拆分开，各自独立处理，才跑通。

第三版：公式写进去不计算 我用 ws.               cell (r, col).value = “=AG2-BT2” 这样的字符串写公式，打开Excel一看，单元格里显示的是字符串，不是公式。后来才发现，openpyxl写入公式时，字符串必须以 = 开头，并且单元格格式要设置为普通。这个小问题又卡了半小时。

第四版：条件格式整行不生效 我想给整行涂色，写了个 ws.conditional_formatting.add(“A2:Z100”, FormulaRule(formula=[…]))，结果只涂了第一列。查文档才知道，公式里必须用 $A2 这样的混合引用，才能作用于整行。改成 $A2:$Z2 后，终于成功了。

自我反省

1.不要迷信“通用：固定列号在业务稳定的场景下就是最优解。为了通用而动态查找，反而引入更多               bug 。

2.分组逻辑先拆开再合并：不同业务规则应该分开实现，不要揉在一起。不要过于苛求完美。

3.写公式前先测试字符串：在openpyxl里，公式就是一个以 = 开头的字符串。先在控制台打印出来看一眼，往往能发现少了个括号或者多了个空格。

4.条件格式的公式要用绝对列引用：=AND($BD2="一致",$BH2=“一致”) 才能涂整行，=AND(BD2=“一致”,BH2=“一致”) 只涂当前列。

几点说明

① 为什么用固定列号 因为公司的Excel模板列顺序是固定的（AF永远是32列），这样写最简单，不会因为表头名称有细微差别（比如“合同编号”vs“合同编码”）而找不到列。

② 两种分组逻辑

A类分组：用于公式列（AU_{BJ等）和辅助列（CJ}CQ），组内只保留第一行的计算结果。

BW分组：单独清除非首行的BP列（实际合同额），因为BP列不参与A类分组。

③ 列宽自适应 生成文件时会自动调整列宽，避免数字显示为“####”。

这个脚本让我明白：稳定比通用重要，可维护比优雅重要。 别再为了“万一以后会变”而过度设计了。

最后

这是【有想法】系列的第3篇文章。 👉 系列其他文章：用Python将Excel手工处理从几小时压缩到1分钟，我是怎么做的？【有想法】系列之：用Python把合同录入从半小时缩到1分钟

有问题欢迎留言交流