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

作者：林焱

为什么要做 Excel 数据透视自动化

每周或每月，运营和数据分析的人要做这样的事：

1. 拿到一张原始数据表（几百到几千行）
2. 按维度分组统计（按部门、按产品、按日期）
3. 生成汇总报表发给领导

手动做一次：打开 Excel → 插入透视表 → 拖拖拖 → 截图 → 粘贴到报告。
这件事每周重复，影刀 + Python 可以把全程自动化。

工具选择：用 Python pandas 实现数据透视

Excel 自带的透视表功能很强，但自动化难度高（需要模拟操作）。更好的方式是用 Python 的pandas库，直接在代码层面完成聚合计算，结果写入 Excel。

pipinstallpandas openpyxl xlsxwriter

数据透视的核心操作

pandas的数据透视函数：pivot_table()

基本语法：

importpandasaspd df=pd.read_excel("数据.xlsx")结果=pd.pivot_table(df,values="销售额",# 要计算的值列index="部门",# 行维度（分组依据）columns="产品类别",# 列维度（可选）aggfunc="sum",# 聚合方式：sum/mean/count/max/minfill_value=0# 空值填充)

实战1：按部门统计月度销售额

原始数据格式

Python 脚本

importpandasaspdfromopenpyxlimportload_workbookfromopenpyxl.stylesimportFont,PatternFill,Alignmentfromopenpyxl.utils.dataframeimportdataframe_to_rowsimportjson input_file="{input_file}"output_file="{output_file}"month="{month}"# 例如 "2024-06"# 读取数据df=pd.read_excel(input_file)# 筛选指定月份df["日期"]=pd.to_datetime(df["日期"])df["月份"]=df["日期"].dt.strftime("%Y-%m")df_month=df[df["月份"]==month]# 透视1：按部门汇总销售额和订单数dept_summary=pd.pivot_table(df_month,values=["销售额","订单数"],index="部门",aggfunc={"销售额":"sum","订单数":"sum"},margins=True,# 添加合计行margins_name="合计")dept_summary.columns=["订单总数","销售总额"]dept_summary=dept_summary.reset_index()# 透视2：按部门+产品二维汇总dept_product=pd.pivot_table(df_month,values="销售额",index="部门",columns="产品",aggfunc="sum",fill_value=0,margins=True,margins_name="合计")# 透视3：按日期统计每日销售趋势df_month["日期_str"]=df_month["日期"].dt.strftime("%m-%d")daily_trend=df_month.groupby("日期_str")["销售额"].sum().reset_index()daily_trend.columns=["日期","销售额"]# 写入 Excel（多 Sheet）withpd.ExcelWriter(output_file,engine="openpyxl")aswriter:dept_summary.to_excel(writer,sheet_name="部门汇总",index=False)dept_product.to_excel(writer,sheet_name="部门×产品交叉")daily_trend.to_excel(writer,sheet_name="每日趋势",index=False)df_month.to_excel(writer,sheet_name="原始数据",index=False)print(f"报表已生成：{output_file}")

影刀调用

今月 = 获取当前日期("yyyy-MM") 输入文件 = "C:/data/sales_raw.xlsx" 输出文件 = "C:/reports/" + 今月 + "_月报.xlsx" 执行Python脚本( 脚本: 透视脚本, 参数: { "input_file": 输入文件, "output_file": 输出文件, "month": 今月 } ) // 透视完成后自动发邮件或企微推送 发送邮件( 收件人: "boss@company.com", 主题: 今月 + "月度销售报告", 正文: "请查看附件", 附件: 输出文件 )

实战2：多文件合并再透视

每个销售员一个 Excel 文件，需要合并后再做汇总分析：

importpandasaspdimportglobimportos input_dir="{input_dir}"output_file="{output_file}"# 合并所有 Excel 文件all_files=glob.glob(os.path.join(input_dir,"*.xlsx"))dfs=[]forfileinall_files:df=pd.read_excel(file)# 加上来源文件名作为识别列df["来源文件"]=os.path.basename(file)dfs.append(df)# 合并所有数据combined_df=pd.concat(dfs,ignore_index=True)print(f"合并完成，共{len(combined_df)}行数据")# 透视分析summary=pd.pivot_table(combined_df,values="销售额",index=["部门","销售员"],aggfunc=["sum","mean","count"],margins=True,margins_name="总计")summary.columns=["销售总额","平均单笔销售额","成交笔数"]# 排名top10=combined_df.groupby("销售员")["销售额"].sum().sort_values(ascending=False).head(10)withpd.ExcelWriter(output_file,engine="openpyxl")aswriter:combined_df.to_excel(writer,sheet_name="合并数据",index=False)summary.to_excel(writer,sheet_name="汇总分析")top10.reset_index().to_excel(writer,sheet_name="销售员TOP10",index=False)print(f"分析报告已生成：{output_file}")

实战3：给透视结果添加格式（颜色/加粗/条件格式）

纯数字表格不够直观，加上格式让领导看得更清楚：

fromopenpyxlimportload_workbookfromopenpyxl.stylesimportFont,PatternFill,Alignment,numbersfromopenpyxl.formatting.ruleimportColorScaleRule,DataBarRule wb=load_workbook(output_file)ws=wb["部门汇总"]# 标题行加粗+蓝色背景header_fill=PatternFill(start_color="2196F3",end_color="2196F3",fill_type="solid")header_font=Font(bold=True,color="FFFFFF")forcellinws[1]:cell.fill=header_fill cell.font=header_font cell.alignment=Alignment(horizontal="center")# 销售额列添加数据条（条件格式）data_bar=DataBarRule(start_type="min",start_value=None,end_type="max",end_value=None,color="2196F3")ws.conditional_formatting.add(f"B2:B{ws.max_row}",data_bar)# 金额列格式化为货币forrowinws.iter_rows(min_row=2,min_col=2,max_col=2):forcellinrow:cell.number_format='¥#,##0.00'wb.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"]}），列名会变成多层索引，难以操作。

解决方法：

# 展平多层列名summary.columns=["_".join(col).strip()forcolinsummary.columns.values]

坑3：合并文件时列名不一致

不同销售员的 Excel 文件里，同一个字段名可能有大小写差异（“销售额” vs “销 售额” vs “销售额（元）”）。

解决方法：合并前做列名统一处理：

df.rename(columns={"销售额（元）":"销售额","销 售额":"销售额","Sales Amount":"销售额"![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/4ce95ba8a1e745839c32d84c8ff12b9d.png#pic_center)},inplace=True)

坑4：大文件处理时内存溢出

几十万行数据全部读入内存，加上多个 pivot_table，内存占用飙升。

解决方法：先按条件筛选再透视，或者用chunksize分批读取：

# 只读需要的列，减少内存占用df=pd.read_excel(file,usecols=["日期","部门","销售额","订单数"])

小结

影刀 + Python pandas 做数据透视的完整流程：

1. 影刀负责文件获取和流程调度（找文件、传参数、触发执行、发结果）
2. Python 负责数据计算（pandas 的 pivot_table、groupby、merge）
3. openpyxl 负责格式美化（颜色、加粗、条件格式）

这个组合可以处理 95% 的 Excel 数据分析自动化需求。

作者：林焱｜飞行社 RPA 实践记录