《超简单：用 Python 让 Excel 飞起来》读书笔记：第6章案例06 批量制作数据透视表

原创于 2026-06-29 00:15:00 发布 · 532 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#python #excel #开发语言 #django #pygame

《超简单：用Python让Excel飞起来》专栏收录该内容

68 篇文章

订阅专栏

🔥 个人主页： 杨利杰YJlio

❄️ 个人专栏： 《Sysinternals实战教程》《Windows PowerShell 实战》《WINDOWS教程》《IOS教程》

《微信助手》《锤子助手》《Python》《Kali Linux》

《那些年未解决的Windows疑难杂症》

🌟 让复杂的事情更简单，让重复的工作自动化

在这里插入图片描述

《超简单：用 Python 让 Excel 飞起来》读书笔记：第6章案例06 批量制作数据透视表

1. 问题背景与写作目标
2. 目标效果：一键生成每张表的透视结果和汇总表
3. 核心原理：透视表不是魔法，而是分组加交叉汇总
4. 实现流程：pandas 负责透视，xlwings 负责读写 Excel
5. 完整代码：批量生成透视表并写回汇总
6. 效果验证：透视表要像交付品，而不是实验输出
7. 常见问题与踩坑记录
8. 总结与进阶建议

1号标题图

1. 问题背景与写作目标

这一篇继续整理《超简单：用 Python 让 Excel 飞起来》第 6 章中的案例内容，主题是 批量制作数据透视表。数据透视表本身并不陌生，很多人都会在 Excel 里通过拖字段的方式生成区域汇总、产品汇总、月份汇总。但真正让人头疼的是：当同样的透视表规则要在多张工作表、多个工作簿里重复执行时，手工操作就会变成低效劳动。

比如一个工作簿里有多个销售明细表，每张表都要按 销售区域、产品名称、销售利润 生成透视结果。如果手工处理，流程通常是：打开表、插入透视表、拖字段、选择求和、调整格式、复制结果、进入下一张表。表少还能忍，表一多就非常折磨。

这张图展示了本文的核心主题：用 Python + Excel 自动化批量制作数据透视表。

请添加图片描述

从这张图中我们可以看出，本文不是教你手工点击 Excel 的“插入数据透视表”，而是把透视表规则写进 Python 脚本里，让程序自动完成读取、分析、汇总和写回。也就是说，本文的核心不是“会做一次透视表”，而是把重复制作透视表这件事标准化、自动化、可交付化。

原理说明：Excel 数据透视表的本质，是按照一个或多个维度对明细数据进行分组，然后对数值字段进行求和、计数、平均等聚合操作。Python 中的 pandas 可以通过 pivot_table() 实现类似能力。

2号标题图

2. 目标效果：一键生成每张表的透视结果和汇总表

在写代码之前，必须先明确目标效果。否则脚本很容易写成“能跑，但不好用”的半成品。对于这类办公自动化脚本，我更关心最终交付物是否清晰，而不是代码看起来多高级。

这张图展示了脚本运行后的目标效果：左侧是多个原始数据表，中间通过一键生成动作，右侧形成汇总透视结果。

请添加图片描述

从这张图中我们可以看出，脚本要完成两个层面的输出。第一，对每张原始工作表分别生成透视结果；第二，额外生成一个 透视汇总 工作表，把所有工作表的透视结果集中展示。这样做的好处是：既保留每张表的独立分析结果，也方便最终汇报时集中查看。

本文设定的目标效果如下：

1. 对一个工作簿中的每张工作表，自动生成一个透视结果区；

2. 透视结果默认写回当前工作表右侧空白区域，例如 J1；

3. 自动生成一个 透视汇总 工作表，把各个 sheet 的透视结果分块集中展示；

4. 透视规则可配置，例如行字段、列字段、值字段、聚合方式；

5. 遇到空表、缺字段、数值列异常时，脚本要能跳过并输出提示。

推荐做法：透视脚本不要只追求“生成结果”，还要考虑结果如何被人阅读。右侧写回、汇总表集中展示、保留总计，都是为了让结果更像一个可交付报表，而不是实验代码的临时输出。

3号标题图

3. 核心原理：透视表不是魔法，而是分组加交叉汇总

很多人觉得数据透视表很神奇，是因为 Excel 把底层过程隐藏得很好。实际上，透视表的本质并不复杂：先按某些字段把数据分组，再对某个数值字段做聚合。

这张图展示了数据透视表的本质：从左侧明细数据出发，先按地区分组，再按产品交叉汇总，最终得到右侧的透视结果。

请添加图片描述

从这张图中我们可以看出，原始数据中的每一行只是明细记录，而透视表会把这些明细按“地区”和“产品”重新组织起来。比如华东地区的手机、笔记本、平板销售额分别是多少，华南地区分别是多少，最后再给出合计。这就是典型的交叉汇总。

在 pandas 中，对应的核心函数是 pivot_table()：

pd.pivot_table(
    data,
    index="销售区域",
    columns="产品名称",
    values="销售利润",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="总计"
)

这里几个参数可以这样理解：

index：行字段，相当于 Excel 数据透视表中的“行”；

columns：列字段，相当于 Excel 数据透视表中的“列”；

values：值字段，也就是要统计的数值列；

aggfunc：聚合方式，例如求和、计数、平均；

margins=True：生成总计，类似 Excel 透视表中的总计行和总计列。

原理说明：当你把 Excel 里“拖字段”的动作翻译成 pandas 参数后，透视表就从一个手工操作变成了一条可复用的规则。规则一旦代码化，就可以批量执行。

4号标题图

4. 实现流程：pandas 负责透视，xlwings 负责读写 Excel

这类脚本不要把所有事情都塞给一个库。我的理解是：pandas 擅长处理数据，xlwings 擅长连接 Excel。两者配合起来，才适合做这种“读取 Excel 明细 → 生成透视结果 → 写回 Excel”的任务。

这张图展示了 pandas + xlwings 的自动化分工：读取源数据、生成透视结果、写回 Excel 报表。

请添加图片描述

从这张图中我们可以看出，左侧是源数据表，中间是 Python 自动化引擎，右侧是生成后的透视结果。pandas 主要负责 pivot_table() 分析，xlwings 主要负责打开工作簿、读取工作表、写入结果、保存文件。

整体流程可以拆成下面几步：

推荐做法：在企业办公场景中，建议优先另存为新文件，而不是直接覆盖原文件。因为透视结果属于加工结果，一旦覆盖原始工作簿，后续出问题不好回退。

5号标题图

5. 完整代码：批量生成透视表并写回汇总

下面这段代码按“可落地使用”的标准做了增强：自动跳过空表和缺列，数值列支持清洗，列字段支持可选，生成结果既写回每张表右侧，也写入统一的 透视汇总 工作表。

import pandas as pd
import xlwings as xw


def clean_to_number(s: pd.Series) -> pd.Series:
    """
    将带货币符号、逗号、空格的文本数字转成数值
    例如：￥12,300 -> 12300
    """
    s = s.astype(str).str.strip()
    s = s.str.replace(",", "", regex=False)
    s = s.str.replace(r"[¥￥$ ]", "", regex=True)
    s = s.str.replace(r"[^0-9\.\-]", "", regex=True)
    return pd.to_numeric(s, errors="coerce")


def make_pivot(
    df: pd.DataFrame,
    index_col: str,
    value_col: str,
    columns_col: str | None,
    aggfunc: str = "sum"
):
    """
    根据配置字段生成透视表 DataFrame
    """
    tmp = df.copy()

    need_cols = [index_col, value_col] + ([columns_col] if columns_col else [])
    missing_cols = [c for c in need_cols if c not in tmp.columns]

    if missing_cols:
        raise KeyError(f"缺少必要列：{missing_cols}")

    tmp[value_col] = clean_to_number(tmp[value_col]).fillna(0)

    pivot = pd.pivot_table(
        tmp,
        index=index_col,
        columns=columns_col if columns_col else None,
        values=value_col,
        aggfunc=aggfunc,
        fill_value=0,
        margins=True,
        margins_name="总计"
    )

    try:
        if columns_col and "总计" in pivot.columns:
            pivot = pivot.sort_values(by="总计", ascending=False)
        elif not columns_col:
            pivot = pivot.sort_values(by=value_col, ascending=False)
    except Exception:
        pass

    return pivot


def batch_pivot_in_workbook(
    input_xlsx: str,
    index_col: str = "销售区域",
    value_col: str = "销售利润",
    columns_col: str | None = "产品名称",
    aggfunc: str = "sum",
    write_cell: str = "J1",
    summary_sheet: str = "透视汇总",
    start_cell: str = "A1",
    save_as: str | None = None
):
    """
    批量为一个工作簿中的所有工作表生成透视表
    """
    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False
    app.screen_updating = False

    try:
        wb = app.books.open(input_xlsx)

        try:
            sum_sht = wb.sheets[summary_sheet]
            sum_sht.clear()
        except Exception:
            sum_sht = wb.sheets.add(summary_sheet, before=wb.sheets[0])

        write_row = 1

        for sht in wb.sheets:
            if sht.name == summary_sheet:
                continue

            try:
                rng = sht.range(start_cell).expand("table")

                if rng.value is None:
                    print(f"[SKIP] {sht.name}：空表")
                    continue

                df = rng.options(pd.DataFrame).value

                if df is None or df.empty:
                    print(f"[SKIP] {sht.name}：无有效数据")
                    continue

                df.columns = [str(c).strip() for c in df.columns]

                pivot = make_pivot(
                    df,
                    index_col=index_col,
                    value_col=value_col,
                    columns_col=columns_col,
                    aggfunc=aggfunc
                )

                # 写回当前工作表右侧空白区域
                sht.range(write_cell).value = None
                sht.range(write_cell).options(index=True).value = pivot
                sht.autofit()

                # 写入汇总 Sheet
                title = f"【{sht.name}】透视结果：{index_col} × {columns_col or '无列字段'} / {value_col}({aggfunc})"
                sum_sht.range((write_row, 1)).value = title

                try:
                    sum_sht.range((write_row, 1)).api.Font.Bold = True
                except Exception:
                    pass

                write_row += 1
                sum_sht.range((write_row, 1)).options(index=True).value = pivot
                write_row = sum_sht.range((write_row, 1)).expand("table").last_cell.row + 2

                print(f"[OK] {sht.name}：已生成透视表 -> {write_cell}")

            except Exception as e:
                print(f"[SKIP] {sht.name}：{e}")
                continue

        try:
            sum_sht.autofit()
        except Exception:
            pass

        if save_as:
            wb.save(save_as)
            print(f"[DONE] 已另存为：{save_as}")
        else:
            wb.save()
            print(f"[DONE] 已覆盖保存：{input_xlsx}")

        wb.close()

    finally:
        app.quit()


if __name__ == "__main__":
    batch_pivot_in_workbook(
        input_xlsx="产品销售统计表.xlsx",
        index_col="销售区域",
        value_col="销售利润",
        columns_col="产品名称",
        aggfunc="sum",
        write_cell="J1",
        summary_sheet="透视汇总",
        start_cell="A1",
        save_as="产品销售统计表_透视.xlsx"
    )