影刀RPA Excel数据透视实战：自动汇总分析你的业务数据

影刀RPA Excel数据透视实战自动汇总分析你的业务数据作者林焱为什么要做 Excel 数据透视自动化每周或每月运营和数据分析的人要做这样的事拿到一张原始数据表几百到几千行按维度分组统计按部门、按产品、按日期生成汇总报表发给领导手动做一次打开 Excel → 插入透视表 → 拖拖拖 → 截图 → 粘贴到报告。这件事每周重复影刀 Python 可以把全程自动化。工具选择用 Python pandas 实现数据透视Excel 自带的透视表功能很强但自动化难度高需要模拟操作。更好的方式是用 Python 的pandas库直接在代码层面完成聚合计算结果写入 Excel。pipinstallpandas openpyxl xlsxwriter数据透视的核心操作pandas的数据透视函数pivot_table()店群矩阵自动化突破运营极限基本语法importpandasaspd dfpd.read_excel(数据.xlsx)结果pd.pivot_table(df,values销售额,# 要计算的值列index部门,# 行维度分组依据columns产品类别,# 列维度可选aggfuncsum,# 聚合方式sum/mean/count/max/minfill_value0# 空值填充)实战1按部门统计月度销售额原始数据格式日期部门销售员产品销售额订单数2024-06-01销售一部张三A产品50003………………Python 脚本importpandasaspdfromopenpyxlimportload_workbookfromopenpyxl.stylesimportFont,PatternFill,Alignmentfromopenpyxl.utils.dataframeimportdataframe_to_rowsimportjson input_file{input_file}output_file{output_file}month{month}# 例如 2024-06# 读取数据dfpd.read_excel(input_file)# 筛选指定月份df[日期]pd.to_datetime(df[日期])df[月份]df[日期].dt.strftime(%Y-%m)df_monthdf[df[月份]month]# 透视1按部门汇总销售额和订单数dept_summarypd.pivot_table(df_month,values[销售额,订单数],index部门,aggfunc{销售额:sum,订单数:sum},marginsTrue,# 添加合计行margins_name合计)dept_summary.columns[订单总数,销售总额]dept_summarydept_summary.reset_index()# 透视2按部门产品二维汇总dept_productpd.pivot_table(df_month,values销售额,index部门,columns产品,aggfuncsum,fill_value0,marginsTrue,margins_name合计)# 透视3按日期统计每日销售趋势df_month[日期_str]df_month[日期].dt.strftime(%m-%d)daily_trenddf_month.groupby(日期_str)[销售额].sum().reset_index()daily_trend.columns[日期,销售额]# 写入 Excel多 Sheetwithpd.ExcelWriter(output_file,engineopenpyxl)aswriter:dept_summary.to_excel(writer,sheet_name部门汇总,indexFalse)dept_product.to_excel(writer,sheet_name部门×产品交叉)daily_trend.to_excel(writer,sheet_name每日趋势,indexFalse)df_month.to_excel(writer,sheet_name原始数据,indexFalse)print(f报表已生成{output_file})影刀调用今月 获取当前日期(yyyy-MM) 输入文件 C:/data/sales_raw.xlsx 输出文件 C:/reports/ 今月 _月报.xlsx 执行Python脚本( 脚本: 透视脚本, 参数: { input_file: 输入文件, output_file: 输出文件, month: 今月 } ) // 透视完成后自动发邮件或企微推送 发送邮件( 收件人: bosscompany.com, 主题: 今月 月度销售报告, 正文: 请查看附件, 附件: 输出文件 )实战2多文件合并再透视每个销售员一个 Excel 文件需要合并后再做汇总分析importpandasaspdimportglobimportos input_dir{input_dir}output_file{output_file}# 合并所有 Excel 文件all_filesglob.glob(os.path.join(input_dir,*.xlsx))dfs[]forfileinall_files:dfpd.read_excel(file)# 加上来源文件名作为识别列df[来源文件]os.path.basename(file)dfs.append(df)# 合并所有数据combined_dfpd.concat(dfs,ignore_indexTrue)print(f合并完成共{len(combined_df)}行数据)# 透视分析summarypd.pivot_table(combined_df,values销售额,index[部门,销售员],aggfunc[sum,mean,count],marginsTrue,margins_name总计)summary.columns[销售总额,平均单笔销售额,成交笔数]# 排名top10combined_df.groupby(销售员)[销售额].sum().sort_values(ascendingFalse).head(10)withpd.ExcelWriter(output_file,engineopenpyxl)aswriter:combined_df.to_excel(writer,sheet_name合并数据,indexFalse)summary.to_excel(writer,sheet_name汇总分析)top10.reset_index().to_excel(writer,sheet_name销售员TOP10,indexFalse)print(f分析报告已生成{output_file})实战3给透视结果添加格式颜色/加粗/条件格式纯数字表格不够直观加上格式让领导看得更清楚fromopenpyxlimportload_workbookfromopenpyxl.stylesimportFont,PatternFill,Alignment,numbersfromopenpyxl.formatting.ruleimportColorScaleRule,DataBarRule wbload_workbook(output_file)wswb[部门汇总]# 标题行加粗蓝色背景header_fillPatternFill(start_color2196F3,end_color2196F3,fill_typesolid)header_fontFont(boldTrue,colorFFFFFF)forcellinws[1]:cell.fillheader_fill cell.fontheader_font cell.alignmentAlignment(horizontalcenter)# 销售额列添加数据条条件格式data_barDataBarRule(start_typemin,start_valueNone,end_typemax,end_valueNone,color2196F3)ws.conditional_formatting.add(fB2:B{ws.max_row},data_bar)# 金额列格式化为货币forrowinws.iter_rows(min_row2,min_col2,max_col2):forcellinrow:cell.number_format¥#,##0.00wb.save(output_file)print(格式设置完成)踩坑记录坑1透视结果里日期列变成数字pivot_table输出时datetime 类型在某些情况下会变成 Excel 序列号如 45000 这种数字。解决方法在写入 Excel 前先把日期列转为字符串df[日期]df[日期].dt.strftime(%Y-%m-%d)坑2中文列名在 pivot_table 后变成 MultiIndex当aggfunc传多个函数时如{销售额: [sum, mean]}列名会变成多层索引难以操作。解决方法temu店群自动化报活动案例# 展平多层列名summary.columns[_.join(col).strip()forcolinsummary.columns.values]坑3合并文件时列名不一致不同销售员的 Excel 文件里同一个字段名可能有大小写差异“销售额” vs “销 售额” vs “销售额元”。解决方法合并前做列名统一处理df.rename(columns{销售额元:销售额,销 售额:销售额,Sales Amount:销售额},inplaceTrue)坑4大文件处理时内存溢出几十万行数据全部读入内存加上多个 pivot_table内存占用飙升。解决方法先按条件筛选再透视或者用chunksize分批读取# 只读需要的列减少内存占用dfpd.read_excel(file,usecols[日期,部门,销售额,订单数])小结影刀 Python pandas 做数据透视的完整流程影刀负责文件获取和流程调度找文件、传参数、触发执行、发结果Python 负责数据计算pandas 的 pivot_table、groupby、mergeopenpyxl 负责格式美化颜色、加粗、条件格式这个组合可以处理 95% 的 Excel 数据分析自动化需求。作者林焱飞行社 RPA 实践记录