1. 問題背景與寫作目標(biāo)

這一篇繼續(xù)整理《超簡單：用 Python 讓 Excel 飛起來》第 6 章中的案例內(nèi)容，主題是 批量制作數(shù)據(jù)透視表。數(shù)據(jù)透視表本身并不陌生，很多人都會在 Excel 里通過拖字段的方式生成區(qū)域匯總、產(chǎn)品匯總、月份匯總。但真正讓人頭疼的是：當(dāng)同樣的透視表規(guī)則要在多張工作表、多個工作簿里重復(fù)執(zhí)行時，手工操作就會變成低效勞動。

比如一個工作簿里有多個銷售明細(xì)表，每張表都要按 銷售區(qū)域、產(chǎn)品名稱、銷售利潤 生成透視結(jié)果。如果手工處理，流程通常是：打開表、插入透視表、拖字段、選擇求和、調(diào)整格式、復(fù)制結(jié)果、進(jìn)入下一張表。表少還能忍，表一多就非常折磨。

這張圖展示了本文的核心主題：用 Python + Excel 自動化批量制作數(shù)據(jù)透視表。

從這張圖中我們可以看出，本文不是教你手工點擊 Excel 的“插入數(shù)據(jù)透視表”，而是把透視表規(guī)則寫進(jìn) Python 腳本里，讓程序自動完成讀取、分析、匯總和寫回。也就是說，本文的核心不是“會做一次透視表”，而是把重復(fù)制作透視表這件事標(biāo)準(zhǔn)化、自動化、可交付化。

原理說明：Excel 數(shù)據(jù)透視表的本質(zhì)，是按照一個或多個維度對明細(xì)數(shù)據(jù)進(jìn)行分組，然后對數(shù)值字段進(jìn)行求和、計數(shù)、平均等聚合操作。Python 中的 pandas 可以通過 pivot_table() 實現(xiàn)類似能力。

2. 目標(biāo)效果：一鍵生成每張表的透視結(jié)果和匯總表

在寫代碼之前，必須先明確目標(biāo)效果。否則腳本很容易寫成“能跑，但不好用”的半成品。對于這類辦公自動化腳本，我更關(guān)心最終交付物是否清晰，而不是代碼看起來多高級。

這張圖展示了腳本運行后的目標(biāo)效果：左側(cè)是多個原始數(shù)據(jù)表，中間通過一鍵生成動作，右側(cè)形成匯總透視結(jié)果。

從這張圖中我們可以看出，腳本要完成兩個層面的輸出。第一，對每張原始工作表分別生成透視結(jié)果；第二，額外生成一個 透視匯總 工作表，把所有工作表的透視結(jié)果集中展示。這樣做的好處是：既保留每張表的獨立分析結(jié)果，也方便最終匯報時集中查看。

本文設(shè)定的目標(biāo)效果如下：

1. 對一個工作簿中的每張工作表，自動生成一個透視結(jié)果區(qū)；

2. 透視結(jié)果默認(rèn)寫回當(dāng)前工作表右側(cè)空白區(qū)域，例如 J1；

3. 自動生成一個 透視匯總 工作表，把各個 sheet 的透視結(jié)果分塊集中展示；

4. 透視規(guī)則可配置，例如行字段、列字段、值字段、聚合方式；

5. 遇到空表、缺字段、數(shù)值列異常時，腳本要能跳過并輸出提示。

推薦做法：透視腳本不要只追求“生成結(jié)果”，還要考慮結(jié)果如何被人閱讀。右側(cè)寫回、匯總表集中展示、保留總計，都是為了讓結(jié)果更像一個可交付報表，而不是實驗代碼的臨時輸出。

3. 核心原理：透視表不是魔法，而是分組加交叉匯總

很多人覺得數(shù)據(jù)透視表很神奇，是因為 Excel 把底層過程隱藏得很好。實際上，透視表的本質(zhì)并不復(fù)雜：先按某些字段把數(shù)據(jù)分組，再對某個數(shù)值字段做聚合。

這張圖展示了數(shù)據(jù)透視表的本質(zhì)：從左側(cè)明細(xì)數(shù)據(jù)出發(fā)，先按地區(qū)分組，再按產(chǎn)品交叉匯總，最終得到右側(cè)的透視結(jié)果。

從這張圖中我們可以看出，原始數(shù)據(jù)中的每一行只是明細(xì)記錄，而透視表會把這些明細(xì)按“地區(qū)”和“產(chǎn)品”重新組織起來。比如華東地區(qū)的手機、筆記本、平板銷售額分別是多少，華南地區(qū)分別是多少，最后再給出合計。這就是典型的交叉匯總。

在 pandas 中，對應(yīng)的核心函數(shù)是 pivot_table()：

pd.pivot_table(
    data,
    index="銷售區(qū)域",
    columns="產(chǎn)品名稱",
    values="銷售利潤",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="總計"
)

這里幾個參數(shù)可以這樣理解：

index：行字段，相當(dāng)于 Excel 數(shù)據(jù)透視表中的“行”；

columns：列字段，相當(dāng)于 Excel 數(shù)據(jù)透視表中的“列”；

values：值字段，也就是要統(tǒng)計的數(shù)值列；

aggfunc：聚合方式，例如求和、計數(shù)、平均；

margins=True：生成總計，類似 Excel 透視表中的總計行和總計列。

原理說明：當(dāng)你把 Excel 里“拖字段”的動作翻譯成 pandas 參數(shù)后，透視表就從一個手工操作變成了一條可復(fù)用的規(guī)則。規(guī)則一旦代碼化，就可以批量執(zhí)行。

4. 實現(xiàn)流程：pandas 負(fù)責(zé)透視，xlwings 負(fù)責(zé)讀寫 Excel

這類腳本不要把所有事情都塞給一個庫。我的理解是：pandas 擅長處理數(shù)據(jù)，xlwings 擅長連接 Excel。兩者配合起來，才適合做這種“讀取 Excel 明細(xì) → 生成透視結(jié)果 → 寫回 Excel”的任務(wù)。

這張圖展示了 pandas + xlwings 的自動化分工：讀取源數(shù)據(jù)、生成透視結(jié)果、寫回 Excel 報表。

從這張圖中我們可以看出，左側(cè)是源數(shù)據(jù)表，中間是 Python 自動化引擎，右側(cè)是生成后的透視結(jié)果。pandas 主要負(fù)責(zé) pivot_table() 分析，xlwings 主要負(fù)責(zé)打開工作簿、讀取工作表、寫入結(jié)果、保存文件。

整體流程可以拆成下面幾步：

推薦做法：在企業(yè)辦公場景中，建議優(yōu)先另存為新文件，而不是直接覆蓋原文件。因為透視結(jié)果屬于加工結(jié)果，一旦覆蓋原始工作簿，后續(xù)出問題不好回退。

5. 完整代碼：批量生成透視表并寫回匯總

下面這段代碼按“可落地使用”的標(biāo)準(zhǔn)做了增強：自動跳過空表和缺列，數(shù)值列支持清洗，列字段支持可選，生成結(jié)果既寫回每張表右側(cè)，也寫入統(tǒng)一的 透視匯總 工作表。

import pandas as pd
import xlwings as xw


def clean_to_number(s: pd.Series) -> pd.Series:
    """
    將帶貨幣符號、逗號、空格的文本數(shù)字轉(zhuǎn)成數(shù)值
    例如：￥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"
):
    """
    根據(jù)配置字段生成透視表 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 = "銷售區(qū)域",
    value_col: str = "銷售利潤",
    columns_col: str | None = "產(chǎn)品名稱",
    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}：無有效數(shù)據(jù)")
                    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
                )

                # 寫回當(dāng)前工作表右側(cè)空白區(qū)域
                sht.range(write_cell).value = None
                sht.range(write_cell).options(index=True).value = pivot
                sht.autofit()

                # 寫入?yún)R總 Sheet
                title = f"【{sht.name}】透視結(jié)果：{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="產(chǎn)品銷售統(tǒng)計表.xlsx",
        index_col="銷售區(qū)域",
        value_col="銷售利潤",
        columns_col="產(chǎn)品名稱",
        aggfunc="sum",
        write_cell="J1",
        summary_sheet="透視匯總",
        start_cell="A1",
        save_as="產(chǎn)品銷售統(tǒng)計表_透視.xlsx"
    )

風(fēng)險提醒：這段腳本默認(rèn)表頭在 A1 開始，并且數(shù)據(jù)區(qū)域是連續(xù)的。如果你的 Excel 表格前面有標(biāo)題行、合并單元格、空行，expand("table") 讀取到的數(shù)據(jù)區(qū)域可能不完整，需要調(diào)整 start_cell。

原理說明：make_pivot() 函數(shù)負(fù)責(zé)生成透視表，batch_pivot_in_workbook() 函數(shù)負(fù)責(zé)批量遍歷和寫回。這樣拆開以后，后續(xù)要調(diào)整透視規(guī)則時，不需要改整個腳本。

6. 效果驗證：透視表要像交付品，而不是實驗輸出

腳本運行成功以后，不要只看控制臺輸出 [DONE]。真正要驗證的是：透視結(jié)果是否寫到正確位置，字段是否完整，總計是否正確，匯總表是否便于閱讀。

這張圖展示了比較理想的交付效果：左側(cè)保留原始明細(xì)，右側(cè)寫回透視結(jié)果，并且總計清晰可見。

從這張圖中我們可以看出，透視結(jié)果寫在右側(cè)空白區(qū)域，不會破壞原始數(shù)據(jù)。左邊是明細(xì)，右邊是結(jié)論，閱讀時可以直接對照檢查。這種布局比單獨生成一個零散結(jié)果文件更適合實際匯報。

我建議至少驗證以下幾項：

1. 每張源工作表右側(cè)是否生成了透視結(jié)果；

2. 是否生成了 透視匯總 工作表；

3. 透視表中是否包含 總計 行或列；

4. 匯總金額是否與原始明細(xì)金額合計一致；

5. 是否存在被跳過的工作表，跳過原因是否合理。

推薦做法：正式交付前，隨機抽一張工作表，用 Excel 手工做一次透視表，對照腳本生成結(jié)果。只要兩邊總計一致，基本可以證明腳本邏輯是可靠的。

7. 常見問題與踩坑記錄

批量制作數(shù)據(jù)透視表最容易踩的坑，不是 pivot_table() 不會寫，而是源數(shù)據(jù)不規(guī)范。

坑 1：字段名不一致。比如有的表叫“銷售區(qū)域”，有的表叫“區(qū)域”，還有的表叫“ 銷售區(qū)域 ”。腳本按列名匹配時會直接受影響，所以代碼中使用了 strip() 清理字段名前后空格。

坑 2：數(shù)值列不是純數(shù)字。如果銷售利潤列里有 ￥12,300、12,300元、空值、短橫線，這些內(nèi)容直接參與求和會出問題。因此代碼中增加了 clean_to_number() 做數(shù)值清洗。

坑 3：透視結(jié)果覆蓋原數(shù)據(jù)。如果寫回位置選擇不合理，例如把結(jié)果寫到 A1，就會覆蓋原始明細(xì)。建議寫到右側(cè)空白區(qū)，例如 J1 或更靠后的列。

坑 4：工作表名稱沖突。如果源工作表已經(jīng)有 透視匯總，腳本需要清空舊匯總表或重新創(chuàng)建，否則結(jié)果可能混亂。

坑 5：覆蓋保存風(fēng)險。如果直接覆蓋源文件，腳本異常時可能影響原始數(shù)據(jù)。更穩(wěn)妥的做法是使用 save_as 另存為新文件。

經(jīng)驗判斷：批量腳本最重要的不是“能跑”，而是遇到異常數(shù)據(jù)時能說明原因。比如空表、缺列、字段錯誤、金額列異常，都應(yīng)該有明確提示，而不是靜默失敗。

8. 總結(jié)與進(jìn)階建議

這一節(jié)的核心，不是記住 pd.pivot_table() 的參數(shù)，而是理解數(shù)據(jù)透視表背后的自動化邏輯：先把明細(xì)數(shù)據(jù)標(biāo)準(zhǔn)化，再按字段分組聚合，最后把結(jié)果寫回 Excel，形成可以交付的分析結(jié)果。

我認(rèn)為這篇筆記最值得帶走的經(jīng)驗有三點。

第一，透視表本質(zhì)是分組和聚合。不要把它看成 Excel 里的神秘功能。只要理解 index、columns、values、aggfunc，就能把手工拖字段轉(zhuǎn)換成代碼規(guī)則。

第二，腳本要按交付標(biāo)準(zhǔn)設(shè)計。右側(cè)寫回、匯總表集中展示、總計清晰、異常有提示，這些都比單純“生成一張表”更重要。

第三，驗證不能省。透視結(jié)果涉及金額、銷量、訂單數(shù)時，必須核對總計。如果總計對不上，說明讀取范圍、字段匹配或數(shù)值清洗至少有一個環(huán)節(jié)存在問題。

后續(xù)如果繼續(xù)升級，可以把行字段、列字段、值字段、聚合方式做成配置文件，甚至做成圖形界面。這樣就能從“讀書筆記里的腳本”升級為一個真正可復(fù)用的 Excel 自動化分析工具。

以上就是Python批量制作Excel數(shù)據(jù)透視表的詳細(xì)內(nèi)容，更多關(guān)于Python Excel數(shù)據(jù)透視表的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論