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

脚本专家 发表于前天 11:00

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

# 1. 同坐标轴组合图：适合量级相近的数据
import matplotlib.pyplot as plt
import numpy as np
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False

months = ["1月", "2月", "3月", "4月", "5月", "6月"]
tickets =    # 柱形：工单数量
avg_time =       # 折线：处理效率评分

x = np.arange(len(months))
plt.figure(figsize=(9, 6))
plt.bar(x, tickets, width=0.6, label="工单数量", alpha=0.75)
plt.plot(x, avg_time, marker="o", linewidth=2, label="处理效率评分")
plt.xticks(x, months)
plt.title("组合图：工单数量 + 处理效率评分")
plt.xlabel("月份")
plt.ylabel("数值")
plt.grid(axis="y", linestyle="--", alpha=0.4)
plt.legend()
plt.tight_layout()
plt.show()

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

# 2. 双 y 轴组合图：解决单位与量级差异
import matplotlib.pyplot as plt
import numpy as np
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False

months = ["1月", "2月", "3月", "4月", "5月", "6月"]
tickets = # 柱形：工单数量
solve_rate =    # 折线：解决率（%）

x = np.arange(len(months))
fig, ax1 = plt.subplots(figsize=(9, 6))

# 左轴：柱形图
ax1.bar(x, tickets, width=0.6, alpha=0.75, label="工单数量")
ax1.set_xlabel("月份")
ax1.set_ylabel("工单数量（单）")
ax1.set_xticks(x)
ax1.set_xticklabels(months)
ax1.grid(axis="y", linestyle="--", alpha=0.4)

# 右轴：折线图
ax2 = ax1.twinx()
ax2.plot(x, solve_rate, marker="o", linewidth=2, linestyle="--", label="解决率（%）")
ax2.set_ylabel("解决率（%）")
ax2.set_ylim(0, 100) # 百分比指标锁定范围

# 合并图例（重要：双轴需从两个对象分别取 handles 和 labels）
h1, l1 = ax1.get_legend_handles_labels()
h2, l2 = ax2.get_legend_handles_labels()
ax1.legend(h1 + h2, l1 + l2, loc="upper left")

plt.title("组合图：工单数量（柱） + 解决率（折线）")
plt.tight_layout()
plt.show()

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

# 3. 企业可交付版：从 Excel 读取数据，一键生成组合图并保存 PNG
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from pathlib import Path

plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False

def excel_to_combo_chart(
xlsx_path: str,
sheet: str,
x_col: str,
bar_col: str,
line_col: str,
out_png: str = "out/combo.jpg",
line_is_percent: bool = True
) -> str:
"""
从 Excel 读取数据生成组合图（柱形+折线，双y轴）并保存为PNG
:param xlsx_path: Excel文件路径
:param sheet: 工作表名
:param x_col: 用作x轴标签的列名（如"月份"）
:param bar_col: 柱形图数据列名
:param line_col: 折线图数据列名
:param out_png: 输出图片路径
:param line_is_percent: 折线数据是否为百分比（如果是则锁定右轴0-100）
:return: 输出图片路径
"""
df = pd.read_excel(xlsx_path, sheet_name=sheet)
x_labels = df.astype(str).tolist()
bar = pd.to_numeric(df, errors="coerce").fillna(0).tolist()
line = pd.to_numeric(df, errors="coerce").fillna(0).tolist()
x = np.arange(len(x_labels))

fig, ax1 = plt.subplots(figsize=(10, 6))
ax1.bar(x, bar, width=0.6, alpha=0.75, label=bar_col)
ax1.set_xlabel(x_col)
ax1.set_ylabel(bar_col)
ax1.set_xticks(x)
ax1.set_xticklabels(x_labels)
ax1.grid(axis="y", linestyle="--", alpha=0.4)
# 柱形图数据标签
for i, v in enumerate(bar):
   ax1.text(i, v, str(int(v)), ha="center", va="bottom")

ax2 = ax1.twinx()
ax2.plot(x, line, marker="o", linewidth=2, linestyle="--", label=line_col)
ax2.set_ylabel(line_col)
if line_is_percent:
   ax2.set_ylim(0, 100)

h1, l1 = ax1.get_legend_handles_labels()
h2, l2 = ax2.get_legend_handles_labels()
ax1.legend(h1 + h2, l1 + l2, loc="upper left")

plt.title(f"组合图：{bar_col}（柱） + {line_col}（折线）")
plt.tight_layout()

out_png = Path(out_png)
out_png.parent.mkdir(parents=True, exist_ok=True)
plt.savefig(out_png, dpi=200)
plt.close()
return str(out_png)

if __name__ == "__main__":
png = excel_to_combo_chart(
   xlsx_path="report.xlsx",
   sheet="数据",
   x_col="月份",
   bar_col="工单数量",
   line_col="解决率",
   out_png="out/组合图_工单数量_解决率.jpg",
   line_is_percent=True
)
print("已输出：", png)

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

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

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

热心网友6 发表于前天 11:05

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

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

热心网友6 发表于前天 11:05

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

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

热心网友6 发表于前天 11:05

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

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

页: [1]

红客联盟 - 由08小组运营's Archiver