在日常办公中，经常需要从包含多个工作表的Excel工作簿中提取某个数值列的最大值和最小值。如果手工操作，不仅效率低下，还容易漏表、选错区域，更关键的是难以追溯极值来源于哪张表哪条记录。本文提供一套基于Python的自动化方案，利用pandas和xlwings实现批量统计，并输出每张表的极值、行索引、关键字段以及全局极值。
## 目标效果：可追溯的极值统计
单纯的数字意义有限，更实用的输出应包含三层：
- 单表极值：每张工作表的最大值和最小值
- 行级定位：极值所在的行索引和对应的关键字段（如产品名称）
- 全局汇总：一个独立的“极值汇总”工作表，展示全局最大值/最小值及其来源工作表
这样打开结果文件即可直接看到结论，不需要再回头翻查原始数据。
## 实现思路
整个流程分为五步：
1. 打开Excel工作簿并遍历所有工作表
2. 跳过汇总表、空表及缺少目标列的工作表
3. 读取目标列并进行数据清洗（去除货币符号、逗号、空格等），转换为数值类型
4. 计算最大值、最小值及其行索引，同时提取关键字段
5. 将每张表的统计结果写入原表指定位置，并在工作簿最前面创建汇总表
## 完整代码
以下代码使用pandas进行数据处理，xlwings与Excel交互。运行前请确保已安装依赖：
pip install pandas xlwings
代码中的关键参数包括：
- input_xlsx：原始Excel文件路径
- value_col：需要统计极值的列名
- key_col：可选关键字段列名（如产品名称），用于追溯记录
- summary_sheet：汇总表名称
- write_cell：每张原始表中写入统计结果的起始单元格
- start_cell：数据区域起始单元格
- save_as：另存为路径，建议使用新文件避免覆盖原数据
- import pandas as pd
- import xlwings as xw
- def clean_to_number(s: pd.Series) -> pd.Series:
- """
- 将Excel中可能带有逗号、货币符号、空格的文本数字转换为真正的数值。
- 例如：￥12,345.67 -> 12345.67，9,876.5 -> 9876.5，空值或异常字符 -> NaN
- """
- 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 batch_min_max(
- input_xlsx: str,
- value_col: str = "销售利润",
- key_col: str | None = None,
- summary_sheet: str = "极值汇总",
- write_cell: str = "J1",
- start_cell: str = "A1",
- save_as: str | None = 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)
- rows = []
- for sht in wb.sheets:
- if sht.name == summary_sheet:
- continue
- 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
- if value_col not in df.columns:
- print(f"[SKIP] {sht.name}: 缺少列：{value_col}")
- continue
- num = clean_to_number(df[value_col])
- if num.isna().all():
- print(f"[SKIP] {sht.name}: {value_col} 全部无法转换为数值")
- continue
- max_val = float(num.max(skipna=True))
- min_val = float(num.min(skipna=True))
- max_idx = int(num.idxmax(skipna=True))
- min_idx = int(num.idxmin(skipna=True))
- max_key = df.loc[max_idx, key_col] if (key_col and key_col in df.columns) else ""
- min_key = df.loc[min_idx, key_col] if (key_col and key_col in df.columns) else ""
- stat_df = pd.DataFrame({
- "指标": [
- "最大值",
- "最小值",
- "最大值所在行索引",
- "最小值所在行索引",
- "最大值关键字段",
- "最小值关键字段"
- ],
- "数值": [
- max_val,
- min_val,
- max_idx,
- min_idx,
- max_key,
- min_key
- ],
- })
- sht.range(write_cell).options(index=False).value = stat_df
- sht.autofit()
- rows.append({
- "工作表": sht.name,
- f"{value_col}最大值": max_val,
- f"{value_col}最小值": min_val,
- "最大值行索引": max_idx,
- "最小值行索引": min_idx,
- "最大值关键字段": max_key,
- "最小值关键字段": min_key,
- })
- print(f"[OK] {sht.name}: max={max_val}, min={min_val}")
- if not rows:
- raise RuntimeError("未得到任何极值结果，请检查字段名或数据区域。")
- summary_df = pd.DataFrame(rows)
- global_max_row = summary_df.loc[summary_df[f"{value_col}最大值"].idxmax()]
- global_min_row = summary_df.loc[summary_df[f"{value_col}最小值"].idxmin()]
- try:
- sum_sht = wb.sheets[summary_sheet]
- sum_sht.clear()
- except Exception:
- sum_sht = wb.sheets.add(summary_sheet, before=wb.sheets[0])
- sum_sht.range("A1").value = "全局最大值"
- sum_sht.range("B1").value = float(global_max_row[f"{value_col}最大值"])
- sum_sht.range("C1").value = "来自工作表"
- sum_sht.range("D1").value = global_max_row["工作表"]
- sum_sht.range("A2").value = "全局最小值"
- sum_sht.range("B2").value = float(global_min_row[f"{value_col}最小值"])
- sum_sht.range("C2").value = "来自工作表"
- sum_sht.range("D2").value = global_min_row["工作表"]
- summary_df_sorted = summary_df.sort_values(
- by=f"{value_col}最大值",
- ascending=False
- )
- sum_sht.range("A4").options(index=False).value = summary_df_sorted
- try:
- sum_sht.range("A1:D2").api.Font.Bold = True
- 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_min_max(
- input_xlsx="产品销售统计表.xlsx",
- value_col="销售利润",
- key_col="产品名称",
- summary_sheet="极值汇总",
- write_cell="J1",
- start_cell="A1",
- save_as="产品销售统计表_极值统计.xlsx"
- )
如果表中没有关键字段列，请将 key_col 设为 None，否则会取不到数据。字段名必须与Excel表头完全一致（包括空格和括号）。
## 关键点解析
代码中同时使用了 max()、min()、idxmax() 和 idxmin()。后两个函数返回的是DataFrame内部的索引，不是Excel行号。如果数据从第2行开始（表头在第1行），则Excel行号 = DataFrame索引 + 2。若存在合并单元格或复杂表头，需要自行调整映射关系。
建议始终输出统计结论和数据来源，即使是最简单的极值统计，也应附带上工作表名和行索引，方便后续快速定位。
## 效果验证
脚本运行结束后，打开输出的Excel文件，检查：
1. 是否存在新工作“极值汇总”
2. 汇总表上方显示全局最大值和最小值及其来源工作表
3. 下方列出每张工作表的明细，包含最大值、最小值、行索引和关键字段
4. 原始工作表中在指定位置（如J1）写入了该表的极值统计
如果汇总表为空，请优先检查：目标列名是否正确、数据区域是否从A1开始、目标列是否全为异常文本、工作表是否为空。
## 常见问题与避坑
1. 字段名不一致：打印每张表的列名确认，print(df.columns.tolist())
2. 数字是文本：clean_to_number函数会尝试清洗常见格式，但如果包含特殊字符或中文单位，仍可能被转为NaN，建议预处理数据
3. 行索引与行号混淆：注意DataFrame索引与Excel实际行号的差异
4. 覆盖原文件风险：建议固定使用 save_as 参数另存为新文件，保护原始数据
5. 空表和缺列：代码已做跳过处理，但控制台会输出跳过原因，请留意排查
## 总结
批量统计Excel极值的核心不是MAX/MIN函数本身，而是构建一套可重复、可追溯、可验证的自动化流程。建议将本脚本作为模板，扩展支持多列统计、Top N、条件筛选、多工作簿批处理等场景，形成自己的Excel自动化工具箱。 |
发表于 
发表于