本文已收录进【有想法】系列【系列全称】python的世界里不怕有想法就怕没想法【系列定位】用Python解决工作中的重复性劳动【专栏名称】【有想法】Python自动化实战采购是我工作的一部分每个月都要和供应商核算成本。上一期写的外购合同录入系统其实是我采购工作里最轻松的活儿——真正复杂的是跟供应商敲定成本。今天要写的这个脚本就是用来啃这根最硬的骨头。【有想法】系列 · 第3期没错上面那段就是我的真实写照在经历了轻松的脚本今带大家来见识一下我那嘴硬的骨头是怎么啃下来的这个涉及的表格多要考虑的数据和因素也特别杂我经常把自己绕懵。自从学了Python、尝到自动化的甜头后我就一直惦记着把这块最难啃的骨头啃下来。这个脚本确实是我目前写过最复杂的前前后后折腾了好几天才终于跑通。【关于我】工作负责采购和业务绩效提成的计算整天和Excel打交道。工作里遇到的重复劳动能自动化绝不手敲。如果你也有类似需求欢迎交流。【正文】先来展示一下成果——这就是我忙了好几天的劳动成果倾注了无数心血也消耗了无数脑细胞。下面来看看我花了这么大力气搞的脚本到底是个啥。脚本能干什么我把脚本取名为“数据处理助手”。之所以做成三个独立的按钮是因为这个工作需要和同事接力完成——中间要换电脑操作。如果只给我自己用可能双击就直接跑完了也就不会有这么漂亮的界面了。所需文件含“未结算”字样的Excel文件以及本脚本。① 生成前期文件从“未结算”表里提取“订单编号”列其他列只留表头生成“有待导入系统的前期文件”。这个文件用来导入业务平台换回“已导出的前期文件”。② 计算判断核心“未结算”表中的内容是供货商提供的需要核对其中的数据及相关信息——生成前期文件的目的就在于此。合并“未结算”表和第①步生成的已导出表自动调换其中两列的位置因为平台导出的列顺序与“未结算”表是反的为了方便我在脚本里直接做了调换。在“未结算”表的右侧自动写入16个业务公式AU-BJ、6个公式CB~CG以及一列判断公式CA。按合同编码BW列 AF列非空进行分组组内对数量、出厂基准价、标准产品目录价、定制化附加成本等列求和结果只保留每组第一行。单独按合同编码清除非首行的BP列实际合同额。条件格式自动涂色CE列毛利率≤5%且非空时整行浅蓝色。BD和BH双方数据计算所得的成本价同时为“一致”时整行浅粉色。最终生成“未结算_已计算.xlsx”包含所有公式和数据。③ 生成导入系统文件最终目的其实就是为了生成导入系统的文件。前面第②步已经确认了数据第③步就简单了只需从已计算的文件中提取“订单编号”、“成本价”、“数量”、“采购合同号”、“币种”再加上固定的供应商和部门生成“导入系统文件”。同时如果订单编号包含“再利用”备注填“再利用已考虑”且整行浅蓝色高亮。该文件夹中除了未结算是原始的表格还有一个脚本其余都是脚本生成的。亮点浅绿色的是护眼色是没有做涂色标识的是需要人为去核实的浅蓝色是毛利率低于5%的这是我需要重点关注的浅粉色的是两个一致意味着供货商和我们计算的结果是一致的标识为浅粉色对于这种颜色我完全可以不用看的怎么用环境准备pip install openpyxl准备文件将脚本与未结算表、已导出表放在同一文件夹。运行双击脚本 → 依次点击三个按钮。打包pyinstaller --onefile --windowed 脚本.py生成exe发给同事直接用。核心代码片段这次不贴具体公式了太长只展示让脚本稳定下来的关键选择。最重要的选择固定列号——这个决定是让这次脚本画上圆满句号的前提。# 固定列号节选 COL_AF 32 # 对外实际销售价格 COL_AG 33 # 数量 COL_AH 34 # 币种 COL_BW 75 # 合同编码 # ... 一直定义到95列用固定列号的代价是模板的列顺序绝对不能变。好处是再也不用担心表头名称是“合同编号”还是“合同编码”这种细微差别了。在稳定的业务环境中硬编码往往比动态映射更可靠。最核心的部分分组求和和条件判断其他都是体力活。# 按合同编码BW列分组组内对数量AG、价格AH等求和只保留首行 groups {} for row in range(2, ws.max_row1): bw ws.cell(row, bw_col).value if bw not in groups: groups[bw] [] groups[bw].append(row) for bw, rows in groups.items(): ag_sum sum(safe_float(ws.cell(r, ag_col).value) for r in rows) ah_sum sum(safe_float(ws.cell(r, ah_col).value) for r in rows) # 写入第一行 ws.cell(rows[0], aux_ag_col, valueag_sum) ws.cell(rows[0], aux_ah_col, valueah_sum) # 其余行清空 for r in rows[1:]: ws.cell(r, aux_ag_col, valueNone) ws.cell(r, aux_ah_col, valueNone)曲折过程我被“万能通用”害惨了这个脚本前后改了十几版不是因为逻辑难而是我总想写“通用代码”。第一版动态找列结果列永远找不对我写了一个漂亮的函数根据表头名称自动返回列号。结果表头里有“合同编号”和“合同编码”两个不同的名字还有“订单号”和“订单编号”一不留神没分清公式写进去全乱套。我其实特别抵触用固定列一开始一直坚持用表头来对应试了好几回都不行。最后我妥协了选择了固定列。如果一开始就放弃动态映射应该就不会那么费劲。以后要灵活一些——对于这种模板不会变的业务完全可以采用固定列的思路不要没苦硬吃。第二版分组逻辑搞错了一开始想按合同编码分组求和于是写了个简单的按BW列分组。后来发现不对应该是“同一合同编码下AF列有数字的行才能作为一组”。于是改。再后来我觉得BP列要单独按合同编码清除非首行又改。最后发现其实是两组逻辑一组给公式列一组给BP列。把两组合在一起搞越搞越乱。后来拆分开各自独立处理才跑通。第三版公式写进去不计算我用 ws.cell(r, col).value “AG2-BT2” 这样的字符串写公式打开Excel一看单元格里显示的是字符串不是公式。后来才发现openpyxl写入公式时字符串必须以 开头并且单元格格式要设置为普通。这个小问题又卡了半小时。第四版条件格式整行不生效我想给整行涂色写了个 ws.conditional_formatting.add(“A2:Z100”, FormulaRule(formula[…]))结果只涂了第一列。查文档才知道公式里必须用 $A2 这样的混合引用才能作用于整行。改成A 2 : A2:A2:Z2 后终于成功了。自我反省1.不要迷信“通用固定列号在业务稳定的场景下就是最优解。为了通用而动态查找反而引入更多bug。2.分组逻辑先拆开再合并不同业务规则应该分开实现不要揉在一起。不要过于苛求完美。3.写公式前先测试字符串在openpyxl里公式就是一个以 开头的字符串。先在控制台打印出来看一眼往往能发现少了个括号或者多了个空格。4.条件格式的公式要用绝对列引用AND(B D 2 一致 , BD2一致,BD2一致,BH2“一致”) 才能涂整行AND(BD2“一致”,BH2“一致”) 只涂当前列。几点说明①为什么用固定列号因为公司的Excel模板列顺序是固定的AF永远是32列这样写最简单不会因为表头名称有细微差别比如“合同编号”vs“合同编码”而找不到列。②两种分组逻辑A类分组用于公式列AUBJ等和辅助列CJCQ组内只保留第一行的计算结果。BW分组单独清除非首行的BP列实际合同额因为BP列不参与A类分组。③列宽自适应生成文件时会自动调整列宽避免数字显示为“####”。这个脚本让我明白稳定比通用重要可维护比优雅重要。 别再为了“万一以后会变”而过度设计了。最后这是【有想法】系列的第3篇文章。 系列其他文章用Python将Excel手工处理从几小时压缩到1分钟我是怎么做的【有想法】系列之用Python把合同录入从半小时缩到1分钟有问题欢迎留言交流
【有想法】系列之：用python把成本核算从需要数天缩到数分钟
发布时间：2026/5/27 21:26:46
本文已收录进【有想法】系列【系列全称】python的世界里不怕有想法就怕没想法【系列定位】用Python解决工作中的重复性劳动【专栏名称】【有想法】Python自动化实战采购是我工作的一部分每个月都要和供应商核算成本。上一期写的外购合同录入系统其实是我采购工作里最轻松的活儿——真正复杂的是跟供应商敲定成本。今天要写的这个脚本就是用来啃这根最硬的骨头。【有想法】系列 · 第3期没错上面那段就是我的真实写照在经历了轻松的脚本今带大家来见识一下我那嘴硬的骨头是怎么啃下来的这个涉及的表格多要考虑的数据和因素也特别杂我经常把自己绕懵。自从学了Python、尝到自动化的甜头后我就一直惦记着把这块最难啃的骨头啃下来。这个脚本确实是我目前写过最复杂的前前后后折腾了好几天才终于跑通。【关于我】工作负责采购和业务绩效提成的计算整天和Excel打交道。工作里遇到的重复劳动能自动化绝不手敲。如果你也有类似需求欢迎交流。【正文】先来展示一下成果——这就是我忙了好几天的劳动成果倾注了无数心血也消耗了无数脑细胞。下面来看看我花了这么大力气搞的脚本到底是个啥。脚本能干什么我把脚本取名为“数据处理助手”。之所以做成三个独立的按钮是因为这个工作需要和同事接力完成——中间要换电脑操作。如果只给我自己用可能双击就直接跑完了也就不会有这么漂亮的界面了。所需文件含“未结算”字样的Excel文件以及本脚本。① 生成前期文件从“未结算”表里提取“订单编号”列其他列只留表头生成“有待导入系统的前期文件”。这个文件用来导入业务平台换回“已导出的前期文件”。② 计算判断核心“未结算”表中的内容是供货商提供的需要核对其中的数据及相关信息——生成前期文件的目的就在于此。合并“未结算”表和第①步生成的已导出表自动调换其中两列的位置因为平台导出的列顺序与“未结算”表是反的为了方便我在脚本里直接做了调换。在“未结算”表的右侧自动写入16个业务公式AU-BJ、6个公式CB~CG以及一列判断公式CA。按合同编码BW列 AF列非空进行分组组内对数量、出厂基准价、标准产品目录价、定制化附加成本等列求和结果只保留每组第一行。单独按合同编码清除非首行的BP列实际合同额。条件格式自动涂色CE列毛利率≤5%且非空时整行浅蓝色。BD和BH双方数据计算所得的成本价同时为“一致”时整行浅粉色。最终生成“未结算_已计算.xlsx”包含所有公式和数据。③ 生成导入系统文件最终目的其实就是为了生成导入系统的文件。前面第②步已经确认了数据第③步就简单了只需从已计算的文件中提取“订单编号”、“成本价”、“数量”、“采购合同号”、“币种”再加上固定的供应商和部门生成“导入系统文件”。同时如果订单编号包含“再利用”备注填“再利用已考虑”且整行浅蓝色高亮。该文件夹中除了未结算是原始的表格还有一个脚本其余都是脚本生成的。亮点浅绿色的是护眼色是没有做涂色标识的是需要人为去核实的浅蓝色是毛利率低于5%的这是我需要重点关注的浅粉色的是两个一致意味着供货商和我们计算的结果是一致的标识为浅粉色对于这种颜色我完全可以不用看的怎么用环境准备pip install openpyxl准备文件将脚本与未结算表、已导出表放在同一文件夹。运行双击脚本 → 依次点击三个按钮。打包pyinstaller --onefile --windowed 脚本.py生成exe发给同事直接用。核心代码片段这次不贴具体公式了太长只展示让脚本稳定下来的关键选择。最重要的选择固定列号——这个决定是让这次脚本画上圆满句号的前提。# 固定列号节选 COL_AF 32 # 对外实际销售价格 COL_AG 33 # 数量 COL_AH 34 # 币种 COL_BW 75 # 合同编码 # ... 一直定义到95列用固定列号的代价是模板的列顺序绝对不能变。好处是再也不用担心表头名称是“合同编号”还是“合同编码”这种细微差别了。在稳定的业务环境中硬编码往往比动态映射更可靠。最核心的部分分组求和和条件判断其他都是体力活。# 按合同编码BW列分组组内对数量AG、价格AH等求和只保留首行 groups {} for row in range(2, ws.max_row1): bw ws.cell(row, bw_col).value if bw not in groups: groups[bw] [] groups[bw].append(row) for bw, rows in groups.items(): ag_sum sum(safe_float(ws.cell(r, ag_col).value) for r in rows) ah_sum sum(safe_float(ws.cell(r, ah_col).value) for r in rows) # 写入第一行 ws.cell(rows[0], aux_ag_col, valueag_sum) ws.cell(rows[0], aux_ah_col, valueah_sum) # 其余行清空 for r in rows[1:]: ws.cell(r, aux_ag_col, valueNone) ws.cell(r, aux_ah_col, valueNone)曲折过程我被“万能通用”害惨了这个脚本前后改了十几版不是因为逻辑难而是我总想写“通用代码”。第一版动态找列结果列永远找不对我写了一个漂亮的函数根据表头名称自动返回列号。结果表头里有“合同编号”和“合同编码”两个不同的名字还有“订单号”和“订单编号”一不留神没分清公式写进去全乱套。我其实特别抵触用固定列一开始一直坚持用表头来对应试了好几回都不行。最后我妥协了选择了固定列。如果一开始就放弃动态映射应该就不会那么费劲。以后要灵活一些——对于这种模板不会变的业务完全可以采用固定列的思路不要没苦硬吃。第二版分组逻辑搞错了一开始想按合同编码分组求和于是写了个简单的按BW列分组。后来发现不对应该是“同一合同编码下AF列有数字的行才能作为一组”。于是改。再后来我觉得BP列要单独按合同编码清除非首行又改。最后发现其实是两组逻辑一组给公式列一组给BP列。把两组合在一起搞越搞越乱。后来拆分开各自独立处理才跑通。第三版公式写进去不计算我用 ws.cell(r, col).value “AG2-BT2” 这样的字符串写公式打开Excel一看单元格里显示的是字符串不是公式。后来才发现openpyxl写入公式时字符串必须以 开头并且单元格格式要设置为普通。这个小问题又卡了半小时。第四版条件格式整行不生效我想给整行涂色写了个 ws.conditional_formatting.add(“A2:Z100”, FormulaRule(formula[…]))结果只涂了第一列。查文档才知道公式里必须用 $A2 这样的混合引用才能作用于整行。改成A 2 : A2:A2:Z2 后终于成功了。自我反省1.不要迷信“通用固定列号在业务稳定的场景下就是最优解。为了通用而动态查找反而引入更多bug。2.分组逻辑先拆开再合并不同业务规则应该分开实现不要揉在一起。不要过于苛求完美。3.写公式前先测试字符串在openpyxl里公式就是一个以 开头的字符串。先在控制台打印出来看一眼往往能发现少了个括号或者多了个空格。4.条件格式的公式要用绝对列引用AND(B D 2 一致 , BD2一致,BD2一致,BH2“一致”) 才能涂整行AND(BD2“一致”,BH2“一致”) 只涂当前列。几点说明①为什么用固定列号因为公司的Excel模板列顺序是固定的AF永远是32列这样写最简单不会因为表头名称有细微差别比如“合同编号”vs“合同编码”而找不到列。②两种分组逻辑A类分组用于公式列AUBJ等和辅助列CJCQ组内只保留第一行的计算结果。BW分组单独清除非首行的BP列实际合同额因为BP列不参与A类分组。③列宽自适应生成文件时会自动调整列宽避免数字显示为“####”。这个脚本让我明白稳定比通用重要可维护比优雅重要。 别再为了“万一以后会变”而过度设计了。最后这是【有想法】系列的第3篇文章。 系列其他文章用Python将Excel手工处理从几小时压缩到1分钟我是怎么做的【有想法】系列之用Python把合同录入从半小时缩到1分钟有问题欢迎留言交流
