Python Excel 统计人数频次生成分类汇总方法
用pandas对Excel数据做频次统计：value_counts数人数、groupby分组求和、再把结果写回新sheet做成分类汇总表，搭配COUNTIF公式让结果实时联动。
场景痛点
HR 表、报名表、客户表里有"性别""部门""城市"这种字段，领导一句"给我按部门数一下人数"，就得手动数据透视表。下个月数据又来了，透视表刷新一遍格式又乱。更烦的是还要算"每个部门男女人数"这种二维交叉。用Python读进来 groupby 一下，结果写回新 sheet，再套上 COUNTIF 公式，后续业务方在原表加数据，汇总也能跟着更新。
用到的库
pip install pandas openpyxl
完整代码
# -*- coding: utf-8 -*-
"""
读取人员表，按部门/性别统计人数频次，生成分类汇总 sheet
"""
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
HEADERS = ["工号", "姓名", "部门", "性别", "城市"]
def make_source(path: str):
"""造一份人员原始数据"""
rows = [
[1001, "张三", "技术部", "男", "北京"],
[1002, "李四", "技术部", "女", "北京"],
[1003, "王五", "技术部", "男", "上海"],
[1004, "赵六", "产品部", "女", "上海"],
[1005, "钱七", "产品部", "男", "广州"],
[1006, "孙八", "产品部", "女", "广州"],
[1007, "周九", "市场部", "男", "北京"],
[1008, "吴十", "市场部", "女", "深圳"],
[1009, "郑十一", "技术部", "男", "深圳"],
[1010, "王十二", "市场部", "女", "广州"],
]
df = pd.DataFrame(rows, columns=HEADERS)
df.to_excel(path, sheet_name="人员明细", index=False)
print("原始数据已写入：", path)
def summarize(path: str):
df = pd.read_excel(path, sheet_name="人员明细")
# 1) 按部门统计人数
by_dept = df.groupby("部门").size().reset_index(name="人数")
print("\n按部门：")
print(by_dept)
# 2) 按性别统计人数
by_gender = df["性别"].value_counts().reset_index()
by_gender.columns = ["性别", "人数"]
print("\n按性别：")
print(by_gender)
# 3) 部门 × 性别 交叉表
pivot = pd.crosstab(df["部门"], df["性别"], margins=True, margins_name="合计")
print("\n部门×性别交叉表：")
print(pivot)
# ===== 写回汇总 sheet =====
with pd.ExcelWriter(path, engine="openpyxl", mode="a",
if_sheet_exists="replace") as writer:
by_dept.to_excel(writer, sheet_name="分类汇总",
startrow=1, index=False)
# 交叉表写在同一 sheet 右侧
pivot.to_excel(writer, sheet_name="分类汇总",
startrow=1, startcol=4)
# ===== 加标题 + 用 COUNTIF 让汇总随明细实时更新 =====
wb = load_workbook(path)
ws = wb["分类汇总"]
ws["A1"] = "按部门统计人数"
ws["A1"].font = Font(bold=True, size=12)
ws["E1"] = "部门 × 性别 交叉表"
ws["E1"].font = Font(bold=True, size=12)
# 在 H 列加一个"动态 COUNTIF 版"：明细加人后自动重算
ws["H1"] = "动态人数（COUNTIF）"
ws["H1"].font = Font(bold=True, size=12)
ws["H2"] = "部门"
ws["I2"] = "人数"
depts = by_dept["部门"].tolist()
for i, dept in enumerate(depts, start=3):
ws.cell(row=i, column=8, value=dept)
# COUNTIF(人员明细!C:C, 本格)
ws.cell(row=i, column=9,
value=f'=COUNTIF(人员明细!C:C,H{i})')
# 表头蓝底
for cell_ref in ["A2", "E2", "H2", "I2"]:
ws[cell_ref].fill = PatternFill("solid", start_color="305496")
ws[cell_ref].font = Font(bold=True, color="FFFFFF")
wb.save(path)
print("\n分类汇总已写入：", path)
def main():
file_path = "hr_summary.xlsx"
make_source(file_path)
summarize(file_path)
if __name__ == "__main__":
main()
代码讲解
groupby("部门").size()按部门分组数行数，等价于 SQL 的SELECT 部门, COUNT(*) FROM ... GROUP BY 部门。reset_index(name="人数")把结果转成干净的两列 DataFrame。df["性别"].value_counts()是"数数"最快的写法，自动按出现次数降序排列；.reset_index()把索引转成列，再改列名。pd.crosstab(df["部门"], df["性别"], margins=True, margins_name="合计")直接出交叉表：行是部门、列是性别，最后一行/列是合计，比手搓两个 groupby 省事。- 写回 Excel 用
pd.ExcelWriter(mode="a", if_sheet_exists="replace")追加 sheet，不覆盖原来的"人员明细"。startrow、startcol控制写入位置，把三张表并排或上下排开。 - 关键的"动态联动"思路：Python 算出来的是"当前快照"，业务方以后往明细加人，快照不会自动变。所以 H、I 列再加一份
=COUNTIF(人员明细!C:C, H3)公式版，明细一更新，这一列自动重算，两全其美。 - 样式部分用蓝底白字表头，让汇总表看起来不像脏数据。
运行结果
生成 hr_summary.xlsx。打开后"人员明细"sheet 是 10 行员工数据；"分类汇总"sheet 分三块：A 列起是按部门人数，E 列起是部门×性别交叉表，H 列起是 COUNTIF 动态人数。控制台打印三张表的数值，例如技术部 4 人、产品部 3 人、市场部 3 人；男 5 女 5。在明细里再加一行"技术部 男"，打开 Excel 看 I 列，技术部人数会自动变成 5。
注意事项
pd.ExcelWriter(mode="a")必须配if_sheet_exists="replace"，否则同名 sheet 已存在时会报错。value_counts()默认降序，想按部门名排序就.sort_index()；空值 NaN 默认排在最后，要单独dropna()或fillna("未知")。- 中文字段名在
crosstab里完全没问题，但写公式时跨 sheet 引用中文名要加单引号，本例人员明细!C:C没有空格和特殊字符，可以不加；稳妥起见加'人员明细'!C:C。 - COUNTIF 只做"精确匹配计数"。要算"包含某关键词的人数"，公式改成
COUNTIF(范围, "*关键词*")。 - 数据量大（十万行以上）时，先 pandas groupby 出快照再写回，别把所有统计都交给 COUNTIF，否则 Excel 打开会卡。
- 分类汇总表一般要冻结首行、加筛选，openpyxl 里
ws.freeze_panes = "A3"就能冻结标题和表头两行。
更新时间：2026-09-15 09:11:50