3.1 场景引入每周五手动做销售汇总报表每到周五下午你是不是也在做这件事打开本周的销售明细用数据透视表算一遍各部门的总销售额、各产品的销量排名然后手动插入柱状图展示趋势再插个饼图看占比……一套操作下来一个半小时过去了。更让人崩溃的是下周同样的流程再来一遍。如果能把这个过程自动化——双击运行脚本Excel自动生成图表自动画好——是不是就能把每周这一小时省下来这一讲我们就来实现这个目标。3.2 数据汇总pandas聚合分析3.2.1 groupby基础分组计算groupby是pandas最强大的功能之一它的作用类似于Excel的数据透视表。基本语法df.groupby(分组列)[计算列].聚合函数()常见聚合函数函数作用示例sum()求和df.groupby(部门)[销售额].sum()mean()求平均df.groupby(产品)[单价].mean()count()计数df.groupby(地区)[订单号].count()max()最大值df.groupby(月份)[销售额].max()min()最小值df.groupby(月份)[销售额].min()std()标准差df.groupby(产品)[销量].std()实战示例假设我们有如下销售数据销售明细.xlsx日期部门产品销售额销量2026-01-05销售一部A12500252026-01-05销售二部B9800182026-01-06销售一部C1520032...............import pandas as pd df pd.read_excel(销售明细.xlsx) # 1. 各部门总销售额 dept_summary df.groupby(部门)[销售额].sum() print(dept_summary) # 输出 # 部门 # 销售一部 285000 # 销售二部 246000 # 销售三部 198000 # Name: 销售额, dtype: int64 # 2. 各产品总销量排名取Top5 product_top5 ( df.groupby(产品)[销量] .sum() .sort_values(ascendingFalse) .head(5) ) print(product_top5) # 3. 多维度汇总各部门各产品的销售额 detail_summary df.groupby([部门, 产品])[销售额].sum() print(detail_summary)3.2.2 一次性计算多个指标用agg()可以同时对同一列计算多个统计量# 对销售额同时计算总和、平均值、最大值、最小值 summary df.groupby(部门)[销售额].agg([sum, mean, max, min, count]) print(summary) # 输出 # sum mean max min count # 部门 # 销售一部 285000 14250.0 25000 3200 20 # 销售二部 246000 12300.0 21000 2800 20 # 销售三部 198000 9900.0 18500 1500 20也可以对不同列用不同的聚合函数summary df.groupby(部门).agg({ 销售额: [sum, mean], 销量: [sum, mean], 日期: count # 相当于统计订单数量 }) print(summary)3.2.3 reset_index把分组结果变回普通表格groupby的结果默认以分组列为索引。如果想把它恢复成普通DataFrame方便后续保存到Excel需要用reset_index()# 不加reset_index时 dept_summary df.groupby(部门)[销售额].sum() # 部门是索引销售额是一列 # 加reset_index后 dept_summary_df df.groupby(部门)[销售额].sum().reset_index() # 部门和销售额都是普通列 print(dept_summary_df) # 部门 销售额 # 0 销售一部 285000 # 1 销售二部 246000 # 2 销售三部 1980003.2.4 实战生成汇总报表现在我们把前面学的内容串起来生成一份完整的汇总报表import pandas as pd def create_summary_report(input_file, output_file): 从销售明细生成汇总报表 df pd.read_excel(input_file) # 1. 部门汇总 dept_summary ( df.groupby(部门)[销售额] .agg([sum, mean, max, min, count]) .reset_index() ) dept_summary.columns [部门, 总销售额, 平均销售额, 最高销售额, 最低销售额, 订单数] # 2. 产品Top10 product_summary ( df.groupby(产品)[[销售额, 销量]] .sum() .sort_values(销售额, ascendingFalse) .head(10) .reset_index() ) product_summary.columns [产品, 总销售额, 总销量] # 3. 每日趋势 df[日期] pd.to_datetime(df[日期]) daily_trend ( df.groupby(df[日期].dt.date)[销售额] .sum() .reset_index() ) daily_trend.columns [日期, 日销售额] # 保存到Excel多个Sheet with pd.ExcelWriter(output_file, engineopenpyxl) as writer: dept_summary.to_excel(writer, sheet_name部门汇总, indexFalse) product_summary.to_excel(writer, sheet_name产品Top10, indexFalse) daily_trend.to_excel(writer, sheet_name每日趋势, indexFalse) print(f汇总报表已生成{output_file}) return dept_summary, product_summary, daily_trend # 使用 create_summary_report(销售明细.xlsx, 销售汇总报表.xlsx)3.3 图表化matplotlib openpyxl 嵌入Excel光有数字不够直观图表才能让数据说话。我们用matplotlib生成图表再用openpyxl嵌入到Excel中。3.3.1 matplotlib基础首先安装matplotlib第1讲已装过。以下是三种最常用的办公图表柱状图适合比较不同类别的数值import matplotlib.pyplot as plt import matplotlib # 设置中文字体解决中文乱码 matplotlib.rcParams[font.sans-serif] [SimHei, Microsoft YaHei] matplotlib.rcParams[axes.unicode_minus] False # 准备数据 categories [销售一部, 销售二部, 销售三部] values [285000, 246000, 198000] # 画图 plt.figure(figsize(10, 6)) bars plt.bar(categories, values, color[#2196F3, #4CAF50, #FF9800]) plt.title(各部门销售额对比, fontsize16, pad20) plt.xlabel(部门, fontsize12) plt.ylabel(销售额元, fontsize12) # 在柱子上标数值 for bar, val in zip(bars, values): plt.text(bar.get_x() bar.get_width()/2, bar.get_height() 3000, f{val:,}, hacenter, vabottom, fontsize10) plt.tight_layout() plt.savefig(部门销售额柱状图.png, dpi200, bbox_inchestight) plt.show()饼图适合看占比plt.figure(figsize(8, 8)) labels [销售一部, 销售二部, 销售三部] sizes [285000, 246000, 198000] colors [#2196F3, #4CAF50, #FF9800] plt.pie(sizes, labelslabels, colorscolors, autopct%1.1f%%, startangle90, pctdistance0.85) plt.title(各部门销售额占比, fontsize16, pad20) # 画成环形图中间空心 centre_circle plt.Circle((0,0), 0.70, fcwhite) fig plt.gcf() fig.gca().add_artist(centre_circle) plt.tight_layout() plt.savefig(部门占比饼图.png, dpi200, bbox_inchestight) plt.show()折线图适合看趋势import pandas as pd plt.figure(figsize(12, 6)) dates pd.date_range(2026-01-01, 2026-01-31) sales [12000, 13500, 11000, 14800, 16200, 9800, 10500, 13000, 14500, 15800, 17200, 11800, 12500, 14000, 15500, 16800, 11200, 12800, 14200, 16000, 17500, 11500, 13200, 14600, 16500, 17800, 12200, 13800, 15000, 17000, 18200] plt.plot(dates, sales, markero, linewidth2, markersize4, color#2196F3) plt.title(1月份日销售额趋势, fontsize16, pad20) plt.xlabel(日期, fontsize12) plt.ylabel(销售额元, fontsize12) plt.grid(True, alpha0.3) plt.xticks(rotation45) plt.tight_layout() plt.savefig(日销售额趋势图.png, dpi200, bbox_inchestight) plt.show()3.3.2 把图表嵌入Excel用openpyxl可以把图片插入到Excel的指定位置from openpyxl import load_workbook from openpyxl.drawing.image import Image def insert_chart_to_excel(excel_file, chart_image, sheet_name, cell_position): 将图表图片插入到Excel的指定Sheet和单元格位置 wb load_workbook(excel_file) # 如果Sheet不存在则创建 if sheet_name not in wb.sheetnames: wb.create_sheet(sheet_name) ws wb[sheet_name] # 插入图片 img Image(chart_image) # 调整图片大小可选 img.width 600 img.height 350 ws.add_image(img, cell_position) wb.save(excel_file) print(f图表已插入 {sheet_name}!{cell_position}) # 使用示例 insert_chart_to_excel(销售汇总报表.xlsx, 部门销售额柱状图.png, 部门汇总, E2) insert_chart_to_excel(销售汇总报表.xlsx, 部门占比饼图.png, 部门汇总, E22) insert_chart_to_excel(销售汇总报表.xlsx, 日销售额趋势图.png, 每日趋势, E2)3.3.3 直接用openpyxl生成原生图表更高级如果你不想先生成图片再插入openpyxl也支持直接在Excel中创建可编辑的图表对象from openpyxl import Workbook from openpyxl.chart import BarChart, PieChart, LineChart, Reference from openpyxl.utils.dataframe import dataframe_to_rows def create_native_charts(data_file, output_file): 直接在Excel中创建原生图表可编辑、可刷新 df pd.read_excel(data_file) # 先准备汇总数据 dept_summary df.groupby(部门)[销售额].sum().reset_index() # 写入Excel wb Workbook() ws wb.active ws.title 汇总数据 # 将数据写入工作表 for r in dataframe_to_rows(dept_summary, indexFalse, headerTrue): ws.append(r) # 创建柱状图 chart1 BarChart() chart1.type col chart1.title 各部门销售额 chart1.y_axis.title 销售额 chart1.x_axis.title 部门 data Reference(ws, min_col2, min_row1, max_rowlen(dept_summary)1) cats Reference(ws, min_col1, min_row2, max_rowlen(dept_summary)1) chart1.add_data(data, titles_from_dataTrue) chart1.set_categories(cats) chart1.shape 4 ws.add_chart(chart1, D2) # 创建饼图 chart2 PieChart() chart2.title 各部门销售额占比 chart2.add_data(data, titles_from_dataTrue) chart2.set_categories(cats) ws.add_chart(chart2, D20) wb.save(output_file) print(f原生图表已生成{output_file})3.4 实战一键生成完整销售报表现在我们把所有内容整合在一起实现一键生成完整销售报表的功能import pandas as pd import matplotlib.pyplot as plt import matplotlib from openpyxl import load_workbook from openpyxl.drawing.image import Image import os # 设置中文字体 matplotlib.rcParams[font.sans-serif] [SimHei, Microsoft YaHei] matplotlib.rcParams[axes.unicode_minus] False def generate_full_report(input_file, output_file): 一键生成完整销售报表数据汇总 图表 print( * 50) print(开始生成销售汇总报表...) print( * 50) # 1. 读取数据 print([1/5] 读取原始数据...) df pd.read_excel(input_file) print(f 共 {len(df)} 条记录) # 2. 数据汇总 print([2/5] 生成汇总数据...) # 部门汇总 dept_summary ( df.groupby(部门)[销售额] .agg([sum, mean, max, min, count]) .reset_index() ) dept_summary.columns [部门, 总销售额, 平均销售额, 最高销售额, 最低销售额, 订单数] # 产品Top10 product_top10 ( df.groupby(产品)[[销售额, 销量]] .sum() .sort_values(销售额, ascendingFalse) .head(10) .reset_index() ) # 每日趋势 df[日期] pd.to_datetime(df[日期]) daily_trend ( df.groupby(df[日期].dt.date)[销售额] .sum() .reset_index() ) daily_trend.columns [日期, 日销售额] # 3. 保存汇总数据到Excel print([3/5] 保存汇总数据到Excel...) with pd.ExcelWriter(output_file, engineopenpyxl) as writer: dept_summary.to_excel(writer, sheet_name部门汇总, indexFalse) product_top10.to_excel(writer, sheet_name产品Top10, indexFalse) daily_trend.to_excel(writer, sheet_name每日趋势, indexFalse) # 4. 生成图表 print([4/5] 生成图表...) # 柱状图各部门销售额 plt.figure(figsize(10, 6)) bars plt.bar(dept_summary[部门], dept_summary[总销售额], color[#2196F3, #4CAF50, #FF9800, #9C27B0, #F44336][:len(dept_summary)]) plt.title(各部门销售额对比, fontsize14) plt.xlabel(部门) plt.ylabel(销售额元) for bar, val in zip(bars, dept_summary[总销售额]): plt.text(bar.get_x() bar.get_width()/2, bar.get_height() 3000, f{val:,}, hacenter, vabottom, fontsize9) plt.tight_layout() plt.savefig(temp_bar.png, dpi200, bbox_inchestight) plt.close() # 饼图各部门占比 plt.figure(figsize(8, 8)) plt.pie(dept_summary[总销售额], labelsdept_summary[部门], autopct%1.1f%%, startangle90, colors[#2196F3, #4CAF50, #FF9800, #9C27B0, #F44336][:len(dept_summary)]) plt.title(各部门销售额占比, fontsize14) centre_circle plt.Circle((0,0), 0.65, fcwhite) fig plt.gcf() fig.gca().add_artist(centre_circle) plt.tight_layout() plt.savefig(temp_pie.png, dpi200, bbox_inchestight) plt.close() # 折线图每日趋势 plt.figure(figsize(12, 5)) plt.plot(daily_trend[日期], daily_trend[日销售额], markero, linewidth2, markersize4, color#2196F3) plt.title(日销售额趋势, fontsize14) plt.xlabel(日期) plt.ylabel(销售额元) plt.grid(True, alpha0.3) plt.xticks(rotation45) plt.tight_layout() plt.savefig(temp_line.png, dpi200, bbox_inchestight) plt.close() # 5. 插入图表到Excel print([5/5] 插入图表到Excel...) wb load_workbook(output_file) # 插入到部门汇总Sheet ws_dept wb[部门汇总] img_bar Image(temp_bar.png) img_bar.width 550 img_bar.height 330 ws_dept.add_image(img_bar, G2) img_pie Image(temp_pie.png) img_pie.width 450 img_pie.height 380 ws_dept.add_image(img_pie, G22) # 插入到每日趋势Sheet ws_trend wb[每日趋势] img_line Image(temp_line.png) img_line.width 650 img_line.height 270 ws_trend.add_image(img_line, D2) wb.save(output_file) # 清理临时图片 for temp_img in [temp_bar.png, temp_pie.png, temp_line.png]: if os.path.exists(temp_img): os.remove(temp_img) print( * 50) print(f✅ 报表生成完成) print(f 保存路径{output_file}) print(f 包含Sheet部门汇总、产品Top10、每日趋势) print(f️ 包含图表柱状图、饼图、折线图) print( * 50) # 使用 generate_full_report(销售明细.xlsx, 完整销售报表.xlsx)3.5 常见问题QAQ1图表中的中文显示为方框/乱码A这是因为matplotlib默认字体不支持中文。解决方法import matplotlib.pyplot as plt plt.rcParams[font.sans-serif] [SimHei] # 或者 Microsoft YaHei plt.rcParams[axes.unicode_minus] False # 解决负号显示问题如果系统中没有这些字体可以安装# 查看系统支持的字体 import matplotlib.font_manager as fm fonts [f.name for f in fm.fontManager.ttflist] print([f for f in fonts if Hei in f or Ya in f])Q2插入Excel的图片太大/太小A调整Image对象的width和height属性img Image(chart.png) img.width 600 # 宽度像素 img.height 360 # 高度像素 ws.add_image(img, A1)Q3groupby之后怎么排序A用sort_values()# 按销售额降序排列 dept_sorted df.groupby(部门)[销售额].sum().sort_values(ascendingFalse)Q4想把汇总结果发给领导但领导电脑没装PythonA不用担心生成的Excel是标准格式任何电脑都能打开。你还可以在第10讲学到如何把脚本打包成exe双击就能运行。3.6 课后练习基础题找一份你自己的数据比如考勤记录、项目进度表用groupby做分组汇总并生成至少一张图表。进阶题改造generate_full_report函数增加一个参数report_type支持选择生成日报周报月报三种模式每种模式的汇总维度和图表不同。挑战题实现一个动态报表功能——每次运行脚本时自动对比本期数据和上一期的数据变化环比并在图表上用不同颜色标识增长和下降。下讲预告Excel处理完了下一个头疼的格式是什么PDF第4讲我们将学习如何从PDF中批量提取表格和文字把那些只能看不能改的PDF变成可编辑的Excel数据。本讲完整代码已整理为L03_report_with_charts.py可在专栏配套资源包中找到。