1. 問題背景：文件夾里一堆報表，手工匯總真的很低效

這篇文章整理的是《超簡單：用 Python 讓 Excel 飛起來》第6章案例03：對多個工作簿中的工作表分別進(jìn)行分類匯總。這個案例很典型，也很貼近真實辦公場景：一個文件夾里有很多 Excel 文件，每個文件里又有多張工作表，領(lǐng)導(dǎo)希望你按“銷售區(qū)域”匯總“銷售利潤”。

如果手工操作，大概就是打開一個工作簿、切換一張工作表、做一次分類匯總、復(fù)制結(jié)果、保存，再繼續(xù)下一張表。工作簿數(shù)量一多，這件事就不再是“辦公技能”，而是純粹的重復(fù)消耗。

真正危險的地方不只是慢，而是容易錯。手工匯總經(jīng)常會出現(xiàn)漏表、漏文件、選錯區(qū)域、金額列被當(dāng)成文本、臨時文件被誤處理等問題。這個時候，Python 的價值就很明確：把重復(fù)動作抽象成流程，讓腳本穩(wěn)定執(zhí)行。

這張圖展示了本文的整體主題：多個工作簿分類匯總，通過 Python、pandas、xlwings 把人工匯總流程自動化。

從這張圖中可以看出，左側(cè)是大量待處理的 Excel 工作簿，中間是 Python + pandas + xlwings 的自動化處理鏈路，右側(cè)是最終生成的匯總結(jié)果。也就是說，本文不是講一個單點函數(shù)，而是講一個可以遷移到真實辦公場景的批量處理框架。

2. 適用場景：什么時候適合用這個腳本

這個案例適合下面幾類場景。只要你的工作表結(jié)構(gòu)比較統(tǒng)一，就可以直接套用這個思路。

• 一個文件夾里有多個 .xlsx 工作簿；
• 每個工作簿里有多張工作表；
• 每張工作表都有相同或相近的表頭結(jié)構(gòu)；
• 需要按某個字段分組，比如“銷售區(qū)域”“客戶名稱”“部門”“產(chǎn)品類型”；
• 需要對某個數(shù)值字段求和，比如“銷售利潤”“銷售額”“數(shù)量”“成本”；
• 希望把匯總結(jié)果寫回原工作表右側(cè)區(qū)域，方便查看和復(fù)核。

推薦做法：先在測試文件夾中復(fù)制 2～3 個樣例文件試運行，確認(rèn)結(jié)果沒問題后，再處理正式數(shù)據(jù)。

不建議直接在原始報表上跑批量覆蓋腳本。批量操作速度很快，錯起來也很快。最好保留原始文件，把處理結(jié)果輸出到新文件夾。

這張圖展示了腳本運行后的目標(biāo)效果：運行一次腳本后，工作簿中的每張表都會在右側(cè)生成匯總區(qū)。

從這張圖中可以看出，匯總區(qū)從 J1 開始寫入，原始明細(xì)數(shù)據(jù)仍然保留在左側(cè)。這樣的布局有兩個好處：第一，不破壞原始數(shù)據(jù)；第二，打開任意一張工作表，都能直接看到當(dāng)前表的分類匯總結(jié)果。

3. 核心原理：把“重復(fù)勞動”拆成一條批處理流水線

手工匯總看起來步驟很多，但拆開之后其實只有一條固定流水線：掃描文件夾 → 打開工作簿 → 遍歷工作表 → 讀取數(shù)據(jù) → 分組匯總 → 寫回保存。

這里面三個庫各有分工：

• os：負(fù)責(zé)文件系統(tǒng)層面的處理，比如掃描文件夾、拼接路徑、判斷擴(kuò)展名；
• xlwings：負(fù)責(zé)打開 Excel、訪問工作簿和工作表、把結(jié)果寫回 Excel；
• pandas：負(fù)責(zé)把表格數(shù)據(jù)變成 DataFrame，然后用 groupby() 做分類匯總。

這張圖展示了完整批處理流程，從掃描文件夾到最終寫回保存。

從這張圖中可以看出，腳本不是直接“匯總一個表”，而是逐層處理：先處理文件夾，再處理工作簿，再處理工作表。只要這條流程搭好，后面不管是分類匯總、批量篩選、批量排序，還是批量拆分，都只是替換中間的數(shù)據(jù)處理邏輯。

4. 操作前準(zhǔn)備：環(huán)境、目錄和數(shù)據(jù)字段

4.1 安裝依賴

這個案例主要依賴 `pandas` 和 `xlwings`。如果本機(jī)沒有安裝，可以先執(zhí)行下面的命令：

pip install pandas xlwings

補(bǔ)充說明：xlwings 在 Windows 環(huán)境中通常依賴本機(jī)已安裝的 Microsoft Excel，因為它是通過 Excel 應(yīng)用來進(jìn)行自動化操作的。

4.2 建議目錄結(jié)構(gòu)

為了降低誤操作風(fēng)險，我建議把原始報表和輸出結(jié)果分開放：

項目目錄
├─ 銷售表
│  ├─ 銷售數(shù)據(jù)_1.xlsx
│  ├─ 銷售數(shù)據(jù)_2.xlsx
│  └─ 銷售數(shù)據(jù)_3.xlsx
└─ 輸出結(jié)果

推薦保留原始文件不動，把處理后的工作簿另存到“輸出結(jié)果”文件夾。這樣即使腳本邏輯寫錯，也不會直接破壞原始數(shù)據(jù)。

4.3 字段要求

本文示例默認(rèn)每張工作表中至少有兩個字段：

銷售區(qū)域
銷售利潤

如果你的字段叫“地區(qū)”“利潤”“銷售金額”，只需要修改代碼里的參數(shù)即可，不要硬改源數(shù)據(jù)字段。

5. 完整代碼：批量處理多個工作簿中的所有工作表

下面這份代碼是偏實戰(zhàn)的版本。它不是只演示 `groupby()`，而是把真實辦公中容易踩坑的地方也考慮進(jìn)去：跳過 `~$` 臨時文件、跳過空表、校驗字段、清洗金額、另存輸出、最終退出 Excel。

這張圖展示了核心代碼邏輯：os 負(fù)責(zé)掃描文件，pandas 負(fù)責(zé)數(shù)據(jù)處理和分組匯總，xlwings 負(fù)責(zé)寫回 Excel。

從這張圖中可以看出，代碼不是孤立的一段腳本，而是一條數(shù)據(jù)通道：Excel 文件進(jìn)入腳本，經(jīng)過 DataFrame 處理，再把匯總結(jié)果寫回 Excel。這種結(jié)構(gòu)比單純復(fù)制粘貼代碼更重要。

import os
import pandas as pd
import xlwings as xw


def clean_to_number(series: pd.Series) -> pd.Series:
    """
    將可能帶有貨幣符號、逗號、空格、文本前綴的金額列清洗為數(shù)值。
    例如：
    ￥12,345.67 -> 12345.67
    profit: 6543.21 -> 6543.21
    """
    series = series.astype(str).str.strip()
    series = series.str.replace(",", "", regex=False)
    series = series.str.replace(r"[￥￥$ ]", "", regex=True)
    series = series.str.replace(r"[^0-9\.\-]", "", regex=True)
    return pd.to_numeric(series, errors="coerce")


def summarize_one_sheet(
    df: pd.DataFrame,
    group_col: str = "銷售區(qū)域",
    value_col: str = "銷售利潤"
) -> pd.DataFrame:
    """
    對單張工作表數(shù)據(jù)進(jìn)行分類匯總。
    按 group_col 分組，對 value_col 求和。
    """
    if group_col not in df.columns:
        raise KeyError(f"缺少分組列：{group_col}")

    if value_col not in df.columns:
        raise KeyError(f"缺少匯總列：{value_col}")

    temp = df.copy()

    # 先清洗成數(shù)值，再匯總，避免字符串求和或排序錯誤
    temp[value_col] = clean_to_number(temp[value_col]).fillna(0)

    result = (
        temp.groupby(group_col, dropna=False)[value_col]
        .sum()
        .reset_index()
        .rename(columns={
            group_col: "銷售區(qū)域",
            value_col: "銷售利潤匯總"
        })
        .sort_values("銷售利潤匯總", ascending=False)
    )

    return result


def batch_summary_workbooks(
    input_folder: str,
    output_folder: str,
    group_col: str = "銷售區(qū)域",
    value_col: str = "銷售利潤",
    start_cell: str = "A1",
    write_cell: str = "J1"
) -> None:
    """
    批量處理多個工作簿中的所有工作表：
    1. 掃描 input_folder 下的 Excel 文件
    2. 遍歷每個工作簿中的所有工作表
    3. 讀取表格數(shù)據(jù)為 DataFrame
    4. 按指定字段分類匯總
    5. 將結(jié)果寫回每張工作表的 write_cell 位置
    6. 保存到 output_folder
    """
    os.makedirs(output_folder, exist_ok=True)

    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False
    app.screen_updating = False

    try:
        for file_name in os.listdir(input_folder):
            # 跳過 Excel 臨時文件和非 xlsx 文件
            if file_name.startswith("~$"):
                continue

            if not file_name.lower().endswith(".xlsx"):
                continue

            input_path = os.path.join(input_folder, file_name)
            output_path = os.path.join(output_folder, file_name)

            print(f"\n[OPEN] 正在處理工作簿：{input_path}")

            wb = app.books.open(input_path)
            success_count = 0
            skip_count = 0

            try:
                for sht in wb.sheets:
                    try:
                        rng = sht.range(start_cell).expand("table")

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

                        df = rng.options(pd.DataFrame, header=1, index=False).value

                        if df is None or df.empty:
                            print(f"  [SKIP] {sht.name}：無有效數(shù)據(jù)")
                            skip_count += 1
                            continue

                        summary_df = summarize_one_sheet(
                            df,
                            group_col=group_col,
                            value_col=value_col
                        )

                        # 清理舊匯總區(qū)，避免上一次結(jié)果殘留
                        sht.range(write_cell).resize(100, 3).clear_contents()

                        # 寫回匯總結(jié)果，不寫入 DataFrame 索引
                        sht.range(write_cell).options(index=False).value = summary_df
                        sht.autofit()

                        print(f"  [OK] {sht.name}：已匯總到 {write_cell}")
                        success_count += 1

                    except Exception as e:
                        print(f"  [SKIP] {sht.name}：{e}")
                        skip_count += 1

                wb.save(output_path)
                print(f"[DONE] 已保存：{output_path}，成功 {success_count} 張表，跳過 {skip_count} 張表")

            finally:
                wb.close()

    finally:
        app.quit()
        print("\n[ALL DONE] 所有工作簿處理完成")


if __name__ == "__main__":
    batch_summary_workbooks(
        input_folder=r"銷售表",
        output_folder=r"輸出結(jié)果",
        group_col="銷售區(qū)域",
        value_col="銷售利潤",
        start_cell="A1",
        write_cell="J1"
    )

6. 關(guān)鍵判斷：為什么必須先清洗數(shù)值再分類匯總

很多新手寫分類匯總代碼時，會直接這樣寫：

df.groupby("銷售區(qū)域")["銷售利潤"].sum()

這句代碼在干凈數(shù)據(jù)里沒問題，但真實 Excel 報表經(jīng)常不是干凈數(shù)據(jù)。銷售利潤列可能長這樣：

￥12,345.67
$8,765.50
9,100元
profit: 6,543.21
  4321

這些內(nèi)容人眼看著像數(shù)字，但程序讀進(jìn)來可能是字符串。如果不先清洗，pandas 的求和結(jié)果可能不可信，甚至直接報錯。

這張圖展示了“先清洗數(shù)值，再分類匯總”的核心邏輯。

從這張圖中可以看出，左側(cè)是帶符號、帶文本、格式不統(tǒng)一的臟數(shù)據(jù)，中間經(jīng)過數(shù)值清洗，右側(cè)才能得到可信的分類匯總結(jié)果。這一步是整篇文章中最關(guān)鍵的技術(shù)判斷：不是所有看起來像數(shù)字的單元格，讀進(jìn) Python 后都是真的數(shù)值。

我的建議：只要是財務(wù)、銷售、金額、數(shù)量類字段，在做匯總前都先統(tǒng)一做數(shù)值轉(zhuǎn)換。多寫幾行清洗代碼，比后面返工查錯省時間。

7. 運行效果驗證：怎么判斷腳本真的成功了

腳本運行后，不能只看控制臺沒有報錯。真正的驗證至少要看三層。

7.1 驗證輸出文件是否生成

先確認(rèn) `輸出結(jié)果` 文件夾中是否生成了對應(yīng)的 Excel 文件。

輸出結(jié)果
├─ 銷售數(shù)據(jù)_1.xlsx
├─ 銷售數(shù)據(jù)_2.xlsx
└─ 銷售數(shù)據(jù)_3.xlsx

如果文件數(shù)量和輸入文件數(shù)量一致，說明工作簿層面的批處理基本正常。

7.2 驗證每張工作表是否寫入?yún)R總區(qū)

打開任意輸出文件，切換到不同工作表，查看 `J1` 起始位置是否出現(xiàn)兩列匯總結(jié)果：

銷售區(qū)域      銷售利潤匯總
華東          24691.34
華南          8765.50
華北          9100.00

如果每張工作表都有獨立匯總區(qū)，說明工作表遍歷和寫回邏輯正常。

7.3 抽樣核對匯總金額

建議隨機(jī)選一張表，用 Excel 透 視表或者篩選求和核對一兩個區(qū)域的銷售利潤。腳本結(jié)果和手工核對一致，才算真正可信。

不要只相信“腳本跑完了”。腳本跑完只代表流程執(zhí)行完成，不代表數(shù)據(jù)一定正確。

8. 常見問題與踩坑記錄

8.1 為什么腳本會跳過某些工作表？

常見原因有三個：空表、表頭不在 A1、缺少指定字段。代碼里已經(jīng)做了異常捕獲，所以不會因為一張表異常導(dǎo)致整個批處理停止。

推薦做法：如果你的真實表頭從 A2 或 B3 開始，把 `start_cell="A1"` 改成真實表頭位置。

8.2 為什么要跳過~$開頭的文件？

`~$` 開頭的文件通常是 Excel 打開的臨時鎖定文件，不是真正的數(shù)據(jù)文件。批處理時如果不跳過，可能會出現(xiàn)打開失敗、權(quán)限錯誤、內(nèi)容異常等問題。

這類臨時文件不應(yīng)該參與數(shù)據(jù)處理。

8.3 為什么推薦輸出到新文件夾，而不是直接覆蓋原文件？

因為分類匯總屬于批量寫操作，寫錯一處可能影響很多文件。輸出到新文件夾后，可以先對比檢查，確認(rèn)無誤再替換原始文件。

這本質(zhì)上是把“處理”和“確認(rèn)”拆開，降低批量誤操作風(fēng)險。

8.4 為什么 Excel 有時會殘留進(jìn)程？

如果腳本運行中途異常退出，而沒有執(zhí)行 `app.quit()`，Excel 進(jìn)程可能殘留在后臺。本文代碼使用 `try/finally`，就是為了保證無論中途是否報錯，最后都盡量退出 Excel。

寫 xlwings 腳本時，finally 收尾不是可選項，是基本規(guī)范。

9. 總結(jié)提升：這不是一個腳本，而是一套辦公自動化套路

這一節(jié)的核心，不是記住某一行代碼，而是理解一套可復(fù)用的辦公自動化套路：先定位文件，再定位工作簿，再定位工作表，最后把數(shù)據(jù)讀入 DataFrame 進(jìn)行處理。

這套思路可以繼續(xù)擴(kuò)展：

• 按“客戶名稱”分類匯總銷售額；
• 按“部門”統(tǒng)計費用；
• 按“產(chǎn)品類型”匯總訂單數(shù)量；
• 把每個工作簿的匯總結(jié)果再合并成一個總表；
• 把匯總結(jié)果自動生成圖表或日報。

Python 辦公自動化真正有價值的地方，不是替你點幾下鼠標(biāo)，而是把重復(fù)規(guī)則沉淀成穩(wěn)定流程。

最后提醒一句：批量處理腳本一定要先用樣例數(shù)據(jù)驗證，再處理正式數(shù)據(jù)。尤其是涉及覆蓋保存、金額匯總、財務(wù)報表、資產(chǎn)臺賬這類數(shù)據(jù)時，不要拿原始文件直接試錯。

到此這篇關(guān)于Python自動化實現(xiàn)對多個Excel工作簿中的工作表進(jìn)行分類匯總的文章就介紹到這了,更多相關(guān)Python Excel工作表分類匯總內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論