1. 問(wèn)題背景與寫(xiě)作目標(biāo)

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

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

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

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

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

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

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

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

從這張圖中我們可以看出，腳本要完成兩個(gè)層面的輸出。第一，對(duì)每張?jiān)脊ぷ鞅矸謩e生成透視結(jié)果；第二，額外生成一個(gè) 透視匯總 工作表，把所有工作表的透視結(jié)果集中展示。這樣做的好處是：既保留每張表的獨(dú)立分析結(jié)果，也方便最終匯報(bào)時(shí)集中查看。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

margins=True：生成總計(jì)，類(lèi)似 Excel 透視表中的總計(jì)行和總計(jì)列。

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

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

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

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

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

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

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

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

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

import pandas as pd
import xlwings as xw


def clean_to_number(s: pd.Series) -> pd.Series:
    """
    將帶貨幣符號(hào)、逗號(hào)、空格的文本數(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="總計(jì)"
    )

    try:
        if columns_col and "總計(jì)" in pivot.columns:
            pivot = pivot.sort_values(by="總計(jì)", 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 = "銷(xiāo)售區(qū)域",
    value_col: str = "銷(xiāo)售利潤(rùn)",
    columns_col: str | None = "產(chǎn)品名稱(chēng)",
    aggfunc: str = "sum",
    write_cell: str = "J1",
    summary_sheet: str = "透視匯總",
    start_cell: str = "A1",
    save_as: str | None = None
):
    """
    批量為一個(gè)工作簿中的所有工作表生成透視表
    """
    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}：無(wú)有效數(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
                )

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

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

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

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

6. 效果驗(yàn)證：透視表要像交付品，而不是實(shí)驗(yàn)輸出

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

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

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

我建議至少驗(yàn)證以下幾項(xiàng)：

1. 每張?jiān)垂ぷ鞅碛覀?cè)是否生成了透視結(jié)果；

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

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

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

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

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

7. 常見(jiàn)問(wèn)題與踩坑記錄

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最新評(píng)論