做数据分析的朋友对下面这个场景一定不陌生：
周一早上9点，你准时收到上周的销售数据表。打开那份“销售业绩看板”，发现折线图还是上周的数据。你不得不复制新数据、调整数据源范围、改一下标题日期……一套操作下来，10分钟过去了。如果老板突然说：“把华东区的数据单独拉出来看看”，你可能又得折腾半天。
真正的自动化，不是“一键运行”，而是“无需触碰”。
今天这一讲，我们将深入对比 VBA 和 Python 在实现“图表自动生成与更新”上的不同路径。我们会通过一个具体的“销售看板”场景，拆解两者的底层逻辑，并给出可直接套用的代码。读完你会发现，VBA胜在“原生便捷”，而Python胜在“无所不能”。
一、场景设定：动态销售看板的需求拆解
假设我们在 D:\Sales\` 目录下有一个名为Sales_Data.xlsx` 的文件，数据结构如下：
日期 | 区域 | 产品类别 | 销售额 | 利润 |
|---|
2024-01-01 | 华东 | 电子产品 | 15000 | 4500 |
... | ... | ... | ... | ... |
我们的目标是生成一个看板（Dashboard），包含：
月度销售额趋势折线图（随数据更新自动拉长）。
各区域销售额对比柱状图。
自动化：每次新增数据后，只需点击一个按钮（或运行一次脚本），图表自动刷新，无需手动调整数据源。
二、传统艺能：VBA 的原生图表绑定
VBA（Visual Basic for Applications）是Excel内置的宏语言。它的核心优势在于对象模型，你可以直接控制Excel里的每一个单元格、每一个形状（Shape）。
1. VBA 实现逻辑：Shapes.AddChart
VBA创建图表通常有两种思路：一是录制宏然后修改，二是直接写代码定义数据源。这里我们采用更专业的第二种方式。
核心思路：
2. VBA 代码示例
按下 Alt + F11打开VBE编辑器，插入模块，粘贴以下代码：
Sub UpdateSalesDashboard() Dim ws As Worksheet Dim lastRow As Long Dim chartObj As ChartObject Set ws = ThisWorkbook.Sheets("Sheet1") ' 1. 找到数据的最后一行（动态获取） lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' 2. 清除旧图表，防止重复叠加（生产环境常用技巧） On Error Resume Next ws.ChartObjects("SalesTrendChart").Delete ws.ChartObjects("RegionBarChart").Delete On Error GoTo 0 ' 3. 创建销售额趋势折线图 Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=50, Height:=300) With chartObj .Name = "SalesTrendChart" .Chart.SetSourceData Source:=ws.Range("A1:A" & lastRow & ",D1:D" & lastRow) .Chart.ChartType = xlLine .Chart.HasTitle = True .Chart.ChartTitle.Text = "月度销售额趋势" .Chart.Axes(xlCategory).HasTitle = True .Chart.Axes(xlCategory).AxisTitle.Text = "日期" .Chart.Axes(xlValue).HasTitle = True .Chart.Axes(xlValue).AxisTitle.Text = "销售额" End With ' 4. 创建区域销售额柱状图（这里需要用到数据透视表逻辑或SUMIF，为简化演示，假设B列是区域，D列是销售额） ' 实际应用中，建议先创建透视表再画图，这里演示直接引用汇总后的数据 Dim pTable As PivotTable Dim pCache As PivotCache ' 清理旧透视表 On Error Resume Next ws.PivotTables("RegionPivot").TableRange2.Clear On Error GoTo 0 ' 创建透视缓存和数据表 Set pCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("A1:E" & lastRow)) Set pTable = pCache.CreatePivotTable(TableDestination:=ws.Range("G1"), TableName:="RegionPivot") With pTable .PivotFields("区域").Orientation = xlRowField .AddDataField .PivotFields("销售额"), "求和项:销售额", xlSum End With ' 基于透视表创建柱状图 Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=500, Top:=380, Height:=300) With chartObj .Name = "RegionBarChart" .Chart.SetSourceData Source:=ws.Range("G1:H" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row) .Chart.ChartType = xlColumnClustered .Chart.HasTitle = True .Chart.ChartTitle.Text = "各区域销售额对比" End With MsgBox "看板更新完成！"End Sub
3. VBA 方案的优缺点分析
优点：
原生集成：图表就是Excel图表，用户可以直接双击编辑格式，交互性强。
刷新机制：只要数据源是动态的（如使用表格Table或动态命名区域），有时甚至不需要重画图表，只需 ActiveWorkbook.RefreshAll即可。
门槛低：对于只使用Office办公套件的团队，VBA是唯一选择。
缺点：
样式老旧：Excel自带的图表美化能力有限，做出来的图往往带有浓厚的“Excel风”，不够现代。
处理大数据慢：当数据量超过10万行，VBA操作单元格会非常卡顿。
逻辑固化：难以实现复杂的统计变换（如非参数回归、复杂聚类可视化）。
三、进阶之路：Python 的图表生成策略
Python 在数据处理和可视化领域的地位毋庸置疑。针对Excel图表的自动生成，我们有两条技术路线：openpyxl（操作Excel内部对象）和 matplotlib（生成图片嵌入）。
路线 A：openpyxl —— 在 Excel 里画“原生图”
openpyxl允许你在Python中模拟VBA的操作，直接创建Excel原生的图表。
Python (openpyxl) 实现
首先安装库：pip install openpyxl
from openpyxl import load_workbookfrom openpyxl.chart import LineChart, Reference, BarChartimport pandas as pddef update_dashboard_openpyxl(file_path): wb = load_workbook(file_path) ws = wb.active # 找到最后一行 last_row = ws.max_row # 1. 创建折线图 line_chart = LineChart() line_chart.title = "月度销售额趋势 (OpenPyXL)" line_chart.x_axis.title = "日期" line_chart.y_axis.title = "销售额" # 定义数据引用范围 # 注意：openpyxl的行号和列号是从1开始的 data = Reference(ws, min_col=4, min_row=1, max_col=4, max_row=last_row) cats = Reference(ws, min_col=1, min_row=2, max_row=last_row) line_chart.add_data(data, titles_from_data=True) line_chart.set_categories(cats) # 将图表放置在指定位置 ws.add_chart(line_chart, "G2") # 2. 创建柱状图（需要先处理数据，这里借助pandas进行分组汇总，再用openpyxl画图） df = pd.read_excel(file_path) region_sales = df.groupby('区域')['销售额'].sum().reset_index() # 将汇总结果写回新的sheet，供图表使用 if 'Summary' not in wb.sheetnames: ws_summary = wb.create_sheet('Summary') else: ws_summary = wb['Summary'] # 清空旧数据 ws_summary.delete_rows(1, ws_summary.max_row) # 写入新数据 for r in pd.DataFrame_to_rows(region_sales, index=False, header=True): ws_summary.append(r) bar_chart = BarChart() bar_chart.title = "各区域销售额对比" bar_chart.x_axis.title = "区域" bar_chart.y_axis.title = "销售额" data_bar = Reference(ws_summary, min_col=2, min_row=1, max_row=ws_summary.max_row) cats_bar = Reference(ws_summary, min_col=1, min_row=2, max_row=ws_summary.max_row) bar_chart.add_data(data_bar, titles_from_data=True) bar_chart.set_categories(cats_bar) ws.add_chart(bar_chart, "G20") wb.save(file_path) print("OpenPyXL: 看板更新完成！")if __name__ == '__main__': update_dashboard_openpyxl(r'D:\Sales\Sales_Data.xlsx')
点评：这种方式生成的图表和VBA生成的几乎一模一样，属于Excel原生图表。适合需要保留Excel交互编辑能力的场景。
路线 B：matplotlib —— 生成“出版级”图片嵌入
这是Python最强的杀招。matplotlib（配合seaborn）能生成质量极高、样式极其丰富的图表。我们可以将生成的图片插入到Excel中。
Python (matplotlib + xlsxwriter/pandas) 实现
xlsxwriter引擎在处理图表和图片插入时性能更好，且支持更多现代图表类型。
import pandas as pdimport matplotlib.pyplot as pltimport seaborn as snsfrom io import BytesIOdef update_dashboard_matplotlib(file_path): # 读取数据 df = pd.read_excel(file_path) # 设置绘图风格（这比Excel漂亮多了） sns.set_style("whitegrid") plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文标签 plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号 # 1. 绘制趋势图 fig1, ax1 = plt.subplots(figsize=(10, 5)) # 按日期聚合 daily_sales = df.groupby('日期')['销售额'].sum() ax1.plot(daily_sales.index, daily_sales.values, marker='o', linestyle='-', color='#1f77b4') ax1.set_title('月度销售额趋势 (Python)', fontsize=16, fontweight='bold') ax1.set_xlabel('日期') ax1.set_ylabel('销售额') ax1.tick_params(axis='x', rotation=45) plt.tight_layout() # 保存到内存 imgstream1 = BytesIO() plt.savefig(imgstream1, format='png', dpi=150) imgstream1.seek(0) plt.close(fig1) # 2. 绘制柱状图 fig2, ax2 = plt.subplots(figsize=(10, 5)) region_sales = df.groupby('区域')['销售额'].sum().sort_values(ascending=False) bars = ax2.bar(region_sales.index, region_sales.values, color=sns.color_palette("husl", len(region_sales))) ax2.set_title('各区域销售额对比', fontsize=16, fontweight='bold') ax2.set_xlabel('区域') ax2.set_ylabel('销售额') # 在柱子上添加数值标签 for bar in bars: yval = bar.get_height() ax2.text(bar.get_x() + bar.get_width()/2, yval + 0.05, round(yval, 2), ha='center', va='bottom') plt.tight_layout() imgstream2 = BytesIO() plt.savefig(imgstream2, format='png', dpi=150) imgstream2.seek(0) plt.close(fig2) # 3. 将图片插入Excel # 使用xlsxwriter作为引擎创建一个新的文件（注意：xlsxwriter不能直接修改现有文件，通常需要配合pandas覆盖写入或使用openpyxl的图片插入功能） # 此处演示使用openpyxl插入图片，因为它可以追加到现有文件 from openpyxl import load_workbook from openpyxl.drawing.image import Image wb = load_workbook(file_path) if 'Dashboard' not in wb.sheetnames: ws = wb.create_sheet('Dashboard') else: ws = wb['Dashboard'] # 清除旧图片 for img in list(ws._images): ws._images.remove(img) # 插入图片1 img1 = Image(imgstream1) img1.anchor = 'A1' ws.add_image(img1) # 插入图片2 img2 = Image(imgstream2) img2.anchor = 'A25' ws.add_image(img2) wb.save(file_path.replace('.xlsx', '_Dashboard.xlsx')) print("Matplotlib: 看板更新完成！已生成新文件。")if __name__ == '__main__': update_dashboard_matplotlib(r'D:\Sales\Sales_Data.xlsx')
点评：这种方法的视觉效果远超VBA。你可以轻松实现渐变色、数据标注、双坐标轴、甚至3D效果。唯一的缺点是，插入的是图片，用户无法在Excel中直接修改图表的数据源（但这往往是好事，防止手滑改坏报表）。
四、VBA vs Python：全方位对照
为了帮你做出技术选型，我将两者在多维度进行了对比：
维度 | VBA (Shapes.AddChart) | Python (openpyxl) | Python (matplotlib/seaborn) |
|---|
图表性质 | Excel原生对象 | Excel原生对象 | 静态图片 (PNG/JPG) |
美观程度 | ⭐⭐ (经典商务风) | ⭐⭐ (同左) | ⭐⭐⭐⭐⭐ (科研/期刊级) |
交互性 | 高 (可筛选、可编辑数据) | 高 (同左) | 无 (不可编辑，除非重新跑代码) |
图表类型 | 基础及部分高级图表 | 基础及部分高级图表 | 极丰富 (热力图、雷达图、等高线等) |
数据处理 | 弱 (依赖单元格公式/透视表) | 强 (结合Pandas) | 极强 (结合Pandas/Numpy/Scikit-learn) |
运行环境 | 仅限Windows + Excel | 跨平台 (Win/Mac/Linux) | 跨平台 |
适用场景 | 简单报表、需要频繁手工调整的模板 | 需要保留Excel格式的自动化流程 | 数据大屏、邮件日报、精美汇报PPT |
核心结论：
五、避坑指南与实战经验
关于动态数据源：
关于中文乱码：
关于性能：
部署建议：
六、总结
从VBA的 Shapes.AddChart到 Python 的 plt.savefig，我们看到的不仅是工具的更替，更是数据分析思维的升级。
VBA教会我们如何与Office生态共生，它像一把瑞士军刀，小巧却锋利；而Python则是一间现代化的工厂，它能处理海量数据，产出精美的可视化作品。
在实际工作中，我不建议完全抛弃VBA。最佳的策略是：用Python做数据清洗和复杂可视化，用VBA做最后的Excel格式微调或按钮触发。两者结合，才是职场效率的最高境界。
课后习题
为了检验大家的学习成果，请完成以下5道选择题：
在VBA中，为了确保图表数据源能随着数据的增加而自动扩展，以下哪种方法最为推荐？
A. 手动修改代码中的行号
B. 使用 UsedRange.Rows.Count获取行数
C. 将数据区域转换为“表格” (ListObject)
D. 使用 Offset函数定义动态名称
使用 Python 的 matplotlib 库生成图表后，将其插入到 Excel 文件中，该图表在 Excel 中属于什么性质的对象？
A. Excel原生图表对象，可以双击编辑数据
B. 一张静态图片 (如 PNG)，无法直接修改数据
C. OLE 嵌入对象，需要调用 Python 解释器
D. SVG矢量图，可以无限放大不失真
关于 openpyxl 和 matplotlib 在生成图表时的区别，下列说法正确的是？
A. openpyxl 生成的图表美观度远高于 matplotlib
B. matplotlib 可以直接修改 Excel 文件中的单元格公式
C. openpyxl 生成的是 Excel 原生图表，matplotlib 生成的是图片
D. matplotlib 只能生成折线图和柱状图，无法生成其他类型
在 VBA 代码中，On Error Resume Next在这段示例中的主要作用是什么？
A. 忽略所有运行时的错误，防止程序崩溃
B. 在删除图表前，防止因图表不存在而导致的报错
C. 加速代码的运行速度
D. 自动修复错误的图表数据源
如果你的数据源有 50 万行，主要目标是生成一个包含复杂统计关系（如相关性热力图）的日报，且不要求接收者修改图表数据，最优的技术选型组合是？
A. VBA + Excel 原生图表
B. Python (Pandas + Seaborn) + 图片嵌入 Excel
C. 仅在 Excel 中使用 Power Query 和原生图表
D. VBA + Python 混合编程，全部使用原生图表
参考答案
C
B
C
B
B
📌 有偿数据分析咨询开放
表格自动化｜Power BI｜帆软 BI｜Python小工具定制｜VBA小工具定义｜AI 智能体｜数据清洗｜项目方案｜技术指导
遇到数据难题，直接私信沟通需求。
