Python自动生成Excel组合图表(双轴)的完整实现

脚本专家

在实际数据分析与汇报中，常常需要在一张图表内同时展示数量与趋势——例如工单数量（柱形图）与解决率（折线图）。若分两张图，读者需来回对照；若强行共用同一纵轴，量级差异大的数据会使折线被压扁。Python 的 matplotlib 库配合 pandas 读取 Excel 数据，可以快速生成可复用的组合图表（Combo Chart），并直接输出为 PNG 图片用于报告。本文从基本同轴组合图入手，重点讲解双 y 轴组合图的实现，并提供一个从 Excel 一键读取、自动出图的脚本模板，最后总结常见坑与验证标准。

2. # 1. 同坐标轴组合图：适合量级相近的数据
3. import matplotlib.pyplot as plt
4. import numpy as np
5. plt.rcParams["font.sans-serif"] = ["SimHei"]
6. plt.rcParams["axes.unicode_minus"] = False
8. months = ["1月", "2月", "3月", "4月", "5月", "6月"]
9. tickets = [120, 98, 135, 110, 150, 160]      # 柱形：工单数量
10. avg_time = [80, 75, 90, 82, 88, 95]          # 折线：处理效率评分
12. x = np.arange(len(months))
13. plt.figure(figsize=(9, 6))
14. plt.bar(x, tickets, width=0.6, label="工单数量", alpha=0.75)
15. plt.plot(x, avg_time, marker="o", linewidth=2, label="处理效率评分")
16. plt.xticks(x, months)
17. plt.title("组合图：工单数量 + 处理效率评分")
18. plt.xlabel("月份")
19. plt.ylabel("数值")
20. plt.grid(axis="y", linestyle="--", alpha=0.4)
21. plt.legend()
22. plt.tight_layout()
23. plt.show()

复制代码

上述代码中，柱形图与折线图共享同一个 y 轴。只有当两组数据的数量级相近（如 80~160 与 80~95）时，这种图表才能清晰传达信息。若一组数据为 0~10000，另一组为 0~100，折线会被压成一条几乎无起伏的线，此时应改用双 y 轴。

2. # 2. 双 y 轴组合图：解决单位与量级差异
3. import matplotlib.pyplot as plt
4. import numpy as np
5. plt.rcParams["font.sans-serif"] = ["SimHei"]
6. plt.rcParams["axes.unicode_minus"] = False
8. months = ["1月", "2月", "3月", "4月", "5月", "6月"]
9. tickets = [120, 98, 135, 110, 150, 160]    # 柱形：工单数量
10. solve_rate = [92, 88, 95, 90, 96, 97]      # 折线：解决率（%）
12. x = np.arange(len(months))
13. fig, ax1 = plt.subplots(figsize=(9, 6))
15. # 左轴：柱形图
16. ax1.bar(x, tickets, width=0.6, alpha=0.75, label="工单数量")
17. ax1.set_xlabel("月份")
18. ax1.set_ylabel("工单数量（单）")
19. ax1.set_xticks(x)
20. ax1.set_xticklabels(months)
21. ax1.grid(axis="y", linestyle="--", alpha=0.4)
23. # 右轴：折线图
24. ax2 = ax1.twinx()
25. ax2.plot(x, solve_rate, marker="o", linewidth=2, linestyle="--", label="解决率（%）")
26. ax2.set_ylabel("解决率（%）")
27. ax2.set_ylim(0, 100)   # 百分比指标锁定范围
29. # 合并图例（重要：双轴需从两个对象分别取 handles 和 labels）
30. h1, l1 = ax1.get_legend_handles_labels()
31. h2, l2 = ax2.get_legend_handles_labels()
32. ax1.legend(h1 + h2, l1 + l2, loc="upper left")
34. plt.title("组合图：工单数量（柱） + 解决率（折线）")
35. plt.tight_layout()
36. plt.show()

复制代码

使用 ax1.twinx() 创建共享 x 轴的第二个 y 轴。左轴承载数量，右轴承载百分比。关键点：右轴必须显式设定 y 轴范围（set_ylim(0, 100)），否则 matplotlib 自动缩放会夸大微小波动。另外，图例必须合并两个 axes 的 handles 和 labels，否则只显示柱形图的图例。

2. # 3. 企业可交付版：从 Excel 读取数据，一键生成组合图并保存 PNG
3. import pandas as pd
4. import numpy as np
5. import matplotlib.pyplot as plt
6. from pathlib import Path
8. plt.rcParams["font.sans-serif"] = ["SimHei"]
9. plt.rcParams["axes.unicode_minus"] = False
11. def excel_to_combo_chart(
12.     xlsx_path: str,
13.     sheet: str,
14.     x_col: str,
15.     bar_col: str,
16.     line_col: str,
17.     out_png: str = "out/combo.jpg",
18.     line_is_percent: bool = True
19. ) -> str:
20.     """
21.     从 Excel 读取数据生成组合图（柱形+折线，双y轴）并保存为PNG
22.     :param xlsx_path: Excel文件路径
23.     :param sheet: 工作表名
24.     :param x_col: 用作x轴标签的列名（如"月份"）
25.     :param bar_col: 柱形图数据列名
26.     :param line_col: 折线图数据列名
27.     :param out_png: 输出图片路径
28.     :param line_is_percent: 折线数据是否为百分比（如果是则锁定右轴0-100）
29.     :return: 输出图片路径
30.     """
31.     df = pd.read_excel(xlsx_path, sheet_name=sheet)
32.     x_labels = df[x_col].astype(str).tolist()
33.     bar = pd.to_numeric(df[bar_col], errors="coerce").fillna(0).tolist()
34.     line = pd.to_numeric(df[line_col], errors="coerce").fillna(0).tolist()
35.     x = np.arange(len(x_labels))
37.     fig, ax1 = plt.subplots(figsize=(10, 6))
38.     ax1.bar(x, bar, width=0.6, alpha=0.75, label=bar_col)
39.     ax1.set_xlabel(x_col)
40.     ax1.set_ylabel(bar_col)
41.     ax1.set_xticks(x)
42.     ax1.set_xticklabels(x_labels)
43.     ax1.grid(axis="y", linestyle="--", alpha=0.4)
44.     # 柱形图数据标签
45.     for i, v in enumerate(bar):
46.         ax1.text(i, v, str(int(v)), ha="center", va="bottom")
48.     ax2 = ax1.twinx()
49.     ax2.plot(x, line, marker="o", linewidth=2, linestyle="--", label=line_col)
50.     ax2.set_ylabel(line_col)
51.     if line_is_percent:
52.         ax2.set_ylim(0, 100)
54.     h1, l1 = ax1.get_legend_handles_labels()
55.     h2, l2 = ax2.get_legend_handles_labels()
56.     ax1.legend(h1 + h2, l1 + l2, loc="upper left")
58.     plt.title(f"组合图：{bar_col}（柱） + {line_col}（折线）")
59.     plt.tight_layout()
61.     out_png = Path(out_png)
62.     out_png.parent.mkdir(parents=True, exist_ok=True)
63.     plt.savefig(out_png, dpi=200)
64.     plt.close()
65.     return str(out_png)
67. if __name__ == "__main__":
68.     png = excel_to_combo_chart(
69.         xlsx_path="report.xlsx",
70.         sheet="数据",
71.         x_col="月份",
72.         bar_col="工单数量",
73.         line_col="解决率",
74.         out_png="out/组合图_工单数量_解决率.jpg",
75.         line_is_percent=True
76.     )
77.     print("已输出：", png)

复制代码

此函数为完整自动化流程：pandas 读取 Excel 指定列，清洗数值（非数值填充为0），建立双y轴，生成图片并保存。使用时只需维护 report.xlsx 内的数据，每次运行即可获得最新组合图。

常见坑与验证要点：
1. 右轴范围：百分比指标务必锁定 0～100，避免自动缩放造成视觉误导。
2. 单位标注：左右轴必须分别写明单位（如“单”、“%”），否则图表失去可读性。
3. 图例合并：双轴图表需从 ax1 和 ax2 分别获取 handles 和 labels 后合并，再调用一次 legend()，否则折线图例可能缺失。
4. 数据标签：柱形图顶部的数据标签不宜过多，避免遮挡。
5. 验收标准：生成 PNG 后检查标题是否说明主题、左右轴单位是否明确、图例是否完整、坐标范围是否合理、数据标签是否影响阅读。真正的验证不是“图有没有生成”，而是“图能否支持你想表达的判断”。

组合图不是为了让图表更复杂，而是在一张图内同时呈现“量 + 趋势 + 质量”。理解了业务关联性（如工单量与解决率是否共同增长），才能正确选择使用同轴还是双轴。上述代码已可直接复制到 Python 环境中运行，适用于桌面支持月报、销售数据分析、KPI 看板等场景。

热心网友6

感谢楼主分享，这个双轴组合图的实现非常清晰实用。之前手动在Excel里调双轴图总要对齐半天，用python自动生成确实省事很多。那个合并图例的细节特别关键，之前自己试的时候经常漏掉右轴的图例，踩坑了。不知道楼主有没有遇到过双轴标签重叠的问题，如何调整布局？

热心网友6

楼主这个教程写得非常清楚，尤其是双 y 轴和图例合并的部分，正好解决了我之前做报表时折线被压扁的痛点。不过最后那个 `excel_to_combo_chart` 函数定义好像没贴完，后面是打算怎么读取 Excel 并传参的？另外如果 Excel 里有多余的空行或者列名不标准，有没有什么容错处理？期待楼主分享完整版。

热心网友6

感谢分享这个教程，写得很清晰！我之前做双轴图时经常遇到图例合并和右轴范围的问题，看完第 2 节的示例终于知道怎么规范处理了。特别是 `set_ylim(0, 100)` 这一步，以前没注意，确实能避免折线被过度拉伸误导。第三部分的通用函数也很有参考价值，回头试试改造成自己的数据模板。