单笔超1万自动拆分：我用Python写了个Excel批量处理工具

今天给大家分享一个开箱即用的Python工具自动读取Excel单据按单价将大额记录拆分为多行每行金额尽量接近阈值且不超限输出结果自带专业格式美化十几秒就能搞定原本大半天的手工活。做财务、开票或者供应链的朋友大概率都遇到过这个经典痛点公司规定单笔单据、发票金额不能超过10000元但业务数据里经常出现一笔几十件、总金额数万的记录。手动拆分算每行数量、核对金额几十行原始数据能拆出上百行明细既耗时间又容易算错反复核对更是折磨人。一、拆分规则先明确通用的拆分逻辑阈值可根据自身需求调整单笔总金额 ≤ 10000元不拆分原样保留单笔总金额 10000元按固定单价拆分为多行每行金额尽可能接近10000元不超过阈值最后一行放置剩余数量拆分前后总数量、总金额完全一致数据零误差二、核心实现拆解整个工具分为三层拆分算法、数据批量处理、Excel格式美化。1. 核心拆分算法拆分的本质是一道基础算术题已知总数量、单价、金额上限求单行最大数量与拆分行数。核心逻辑步骤计算单笔总金额未超过阈值直接返回原数据用「阈值 ÷ 单价」向下取整得到单行可放置的最大数量总数量除以单行最大数量得到完整行数与剩余数量拼接完整行与余数行形成最终拆分结果额外处理了边界场景若单件单价本身高于阈值无法拆分则直接保留原行。2. Excel批量读写基于pandas实现全量数据处理读取原始Excel表格支持指定工作表逐行调用拆分算法生成明细行保留原始日期、名称、单价等字段仅更新数量与金额自动兼容文件已存在、不存在两种场景避免重复运行报错3. 自动格式美化很多脚本导出的Excel都是无格式的“裸数据”还需要手动排版。这个工具直接通过openpyxl完成美化深色表头搭配白色加粗字体层级清晰全表单元格居中对齐统一细边框预设合理列宽无需手动调整冻结首行滚动查看数据时表头始终可见导出的结果文件可直接用于汇报、发同事无需二次加工。三、完整代码与使用方法1. 安装依赖运行前先安装所需第三方库pipinstallpandas openpyxl2. 配置说明修改代码开头CONFIG字典即可适配你的表格input_path原始Excel文件路径output_path拆分结果输出路径各*_col对应你表格中的列名threshold拆分金额阈值默认10000可自由调整3. 完整可运行代码#!/usr/bin/env python3# -*- coding: utf-8 -*- 金额按单价拆分工具 规则金额 ≥ 阈值时按单价拆分为多行 每行金额尽量接近阈值不超过最后一行是余数。 importmathimportosimportpandasaspdfromopenpyxlimportload_workbookfromopenpyxl.stylesimportFont,Alignment,PatternFill,Border,Side# 配置区 CONFIG{input_path:sample.xlsx,output_path:result.xlsx,date_col:日期,name_col:名称,qty_col:数量,price_col:单价,amount_col:金额,threshold:10000,# 大于此金额才拆分sheet_name:0,# 读取第几个工作表从0开始}# defsplit_row(qty,price,threshold10000): 把 (数量, 单价) 拆成 [(数量, 金额), ...] 规则每行金额尽量接近 threshold不超过最后一行是余数 total_amountqty*price# 不超过阈值 → 不拆iftotal_amountthreshold:return[(qty,round(total_amount,2))]# 找每行最大数量floor(threshold / price)per_qtymath.floor(threshold/price)# 如果单价 阈值单行就超了 → 只能拆成1行ifper_qty1:return[(qty,round(total_amount,2))]# 完整份数 余数full_partsqty//per_qty remainderqty%per_qty result[]for_inrange(int(full_parts)):result.append((per_qty,round(per_qty*price,2)))ifremainder0:result.append((int(remainder),round(remainder*price,2)))returnresultdefprocess(input_path,date_col,name_col,qty_col,price_col,amount_col,threshold10000,sheet_name0):读取Excel并逐行拆分dfpd.read_excel(input_path,sheet_namesheet_name)rows[]for_,rindf.iterrows():qtyr[qty_col]pricer[price_col]splitssplit_row(qty,price,threshold)fornew_qty,new_amountinsplits:rows.append({date_col:r[date_col],name_col:r[name_col],qty_col:new_qty,price_col:price,amount_col:new_amount,})returnpd.DataFrame(rows)defsave(result_df,output_path,cols):保存结果并美化格式ifnotos.path.exists(output_path):withpd.ExcelWriter(output_path,engineopenpyxl)aswriter:result_df.to_excel(writer,sheet_name拆分结果,indexFalse)else:withpd.ExcelWriter(output_path,engineopenpyxl,modea,if_sheet_existsreplace)aswriter:result_df.to_excel(writer,sheet_name拆分结果,indexFalse)_beautify(output_path,拆分结果,cols)def_beautify(file_path,sheet_name,cols):Excel样式美化wbload_workbook(file_path)wswb[sheet_name]# 表头样式header_fontFont(name微软雅黑,size11,boldTrue,colorFFFFFF)header_fillPatternFill(start_color2C3E50,end_color2C3E50,fill_typesolid)thin_borderBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin))center_alignAlignment(horizontalcenter,verticalcenter)# 应用表头样式forcolinrange(1,ws.max_column1):cellws.cell(row1,columncol)cell.fontheader_font cell.fillheader_fill cell.alignmentcenter_align cell.borderthin_border# 应用内容样式forrinrange(2,ws.max_row1):forcinrange(1,ws.max_column1):cellws.cell(rowr,columnc)cell.alignmentcenter_align cell.borderthin_border# 预设列宽widths{A:14,B:20,C:10,D:10,E:12}forcol_letter,winwidths.items():ws.column_dimensions[col_letter].widthw ws.freeze_panesA2wb.save(file_path)if__name____main__:print(*60)print( 金额拆分工具)print(*60)print(f 输入文件:{CONFIG[input_path]})print(f 拆分阈值:{CONFIG[threshold]}元)print()cols[CONFIG[date_col],CONFIG[name_col],CONFIG[qty_col],CONFIG[price_col],CONFIG[amount_col]]# 执行拆分resultprocess(CONFIG[input_path],CONFIG[date_col],CONFIG[name_col],CONFIG[qty_col],CONFIG[price_col],CONFIG[amount_col],CONFIG[threshold],CONFIG[sheet_name],)# 保存结果save(result,CONFIG[output_path],cols)# 校验结果thresholdCONFIG[threshold]max_amtresult[CONFIG[amount_col]].max()print(f 原始行数 → 拆分后{len(result)}行)print(f 最大单行金额:{max_amt}元 (阈值{threshold}元))status全部符合阈值要求ifmax_amtthresholdelse存在超阈值行print(f 校验状态:{status})print()# 按名称分组打印明细print( 拆分详情:)forname,subinresult.groupby(CONFIG[name_col]):total_amtsub[CONFIG[amount_col]].sum()total_qtysub[CONFIG[qty_col]].sum()rowslen(sub)print(f{name}:{rows}行,{int(total_qty)}件, 合计{total_amt:.2f}元)for_,rinsub.iterrows():print(f └─{r[CONFIG[qty_col]]:4}×{r[CONFIG[price_col]]:6.2f}{r[CONFIG[amount_col]]:8.2f})print(f\n结果已写入:{CONFIG[output_path]}→「拆分结果」工作表)四、运行效果脚本运行后控制台会输出完整的校验信息拆分前后行数对比最大单行金额校验确认是否全部符合阈值要求按商品分组的明细清单每行数量、单价、金额清晰展示相当于自动完成了数据核对不用再手动求和校验总数。这个工具逻辑不复杂但解决的是非常高频的办公痛点。把重复、机械的拆分核对工作交给代码既能提升效率也能避免人工计算的失误。你可以根据自己的业务需求继续扩展比如支持多sheet批量处理、增加汇总统计、对接开票系统直接生成发票明细等等。