Python 自动化 Excel 排序、去重、分组统计详细教程
在数据处理工作中，排序、去重和分组统计是三大核心操作，无论是制作报表、分析数据还是生成汇总结果，这些操作都是必不可少的。Python提供了非常便捷的方法来实现这些功能，效率远超手动操作。本文将详细介绍各种场景的实现方法，包含完整代码示例和最佳实践。
一、环境准备
1.1 安装依赖库
bash
pip install openpyxl pandas numpy
1.2 应用场景
这些操作广泛应用于各类数据处理场景：
二、数据排序
2.1 基础排序
使用pandas的sort_values方法可以轻松实现排序：
python
import pandas as pd
# 读取数据
df = pd.read_excel("销售数据.xlsx")
print("原始数据前5行：")
print(df.head())
# 按销售额升序排序（从小到大）
df_sorted_asc = df.sort_values(by="销售额", ascending=True)
print("\n按销售额升序排序：")
print(df_sorted_asc[["日期", "产品名称", "销售额"]].head())
# 按销售额降序排序（从大到小）
df_sorted_desc = df.sort_values(by="销售额", ascending=False)
print("\n按销售额降序排序：")
print(df_sorted_desc[["日期", "产品名称", "销售额"]].head())
# 排序后重置索引
df_sorted = df.sort_values(by="销售额", ascending=False).reset_index(drop=True)
2.2 多列排序
按多个字段组合排序：
python
# 先按日期升序，同日期下按销售额降序
df_multi_sort = df.sort_values(by=["日期", "销售额"], ascending=[True, False])
print("\n按日期升序、销售额降序排序：")
print(df_multi_sort[["日期", "产品名称", "销售额"]].head())
# 三个字段排序：区域升序、利润降序、销量升序
df_complex_sort = df.sort_values(
by=["区域", "利润", "销量"],
ascending=[True, False, True]
)
2.3 排序后添加排名列
python
# 按销售额降序排序并添加排名列
df_ranked = df.sort_values(by="销售额", ascending=False).reset_index(drop=True)
df_ranked["销售额排名"] = df_ranked.index + 1 # 排名从1开始
print("\n带销售额排名：")
print(df_ranked[["销售额排名", "产品名称", "销售额"]].head())
# 分组排名：每个区域内的销售额排名
df["区域内销售额排名"] = df.groupby("区域")["销售额"].rank(ascending=False, method="min")
print("\n各区域内销售额排名：")
print(df[["区域", "产品名称", "销售额", "区域内销售额排名"]].head())
> 💡 rank的method参数说明：
> – “min”：相同值排名相同，跳过后续排名（1,2,2,4）
> – “max”：相同值取最大排名（1,3,3,4）
> – “average”：相同值取平均排名（1,2.5,2.5,4）
> – “dense”：相同值排名相同，不跳过后续排名（1,2,2,3）
2.4 按自定义顺序排序
有时候需要按特定的自定义顺序排序，而不是字母或数字顺序：
python
# 自定义产品优先级顺序：产品C > 产品A > 产品B
custom_order = ["产品C", "产品A", "产品B"]
df["产品名称"] = pd.Categorical(df["产品名称"], categories=custom_order, ordered=True)
df_custom_sort = df.sort_values(by="产品名称")
print("\n按自定义产品顺序排序：")
print(df_custom_sort["产品名称"].unique())
# 自定义月份顺序
month_order = ["1月", "2月", "3月", "4月", "5月", "6月",
"7月", "8月", "9月", "10月", "11月", "12月"]
df["月份"] = pd.Categorical(df["月份"], categories=month_order, ordered=True)
df_month_sorted = df.sort_values(by="月份")
2.5 按索引排序
python
# 按索引升序
df_sort_index = df.sort_index(ascending=True)
# 按索引降序
df_sort_index_desc = df.sort_index(ascending=False)
三、数据去重
3.1 全量去重
去除完全相同的重复行：
python
# 查看重复行数
duplicate_count = df.duplicated().sum()
print(f"\n完全重复的行数：{duplicate_count}")
# 去除完全重复的行，保留第一个
df_dedup = df.drop_duplicates(keep="first")
# 保留最后一个
# df_dedup = df.drop_duplicates(keep="last")
# 删除所有重复行
# df_dedup = df.drop_duplicates(keep=False)
print(f"去重后行数：{len(df_dedup)}")
3.2 按指定字段去重
按部分字段去重，比如按订单号去重，只要订单号相同就算重复：
python
# 按订单号去重
df_order_dedup = df.drop_duplicates(subset=["订单号"], keep="first")
print(f"\n按订单号去重后行数：{len(df_order_dedup)}")
# 按多字段去重：姓名+手机号同时相同才认为重复
df_user_dedup = df.drop_duplicates(subset=["姓名", "手机号"], keep="first")
3.3 查看重复数据
先查看哪些数据重复，再决定怎么处理：
python
# 查看所有重复记录（keep=False会标记所有重复行）
duplicates = df[df.duplicated(subset=["订单号"], keep=False)]
print("\n重复的订单记录：")
print(duplicates[["订单号", "产品名称", "销售额"]].sort_values(by="订单号"))
# 统计重复出现次数
duplicate_counts = df["订单号"].value_counts()
print("\n订单号出现次数：")
print(duplicate_counts[duplicate_counts > 1].head())
3.4 重复数据处理策略
不同场景的重复数据有不同的处理方式：
python
# 1. 金额重复的取最大值
df_max_amount = df.groupby("订单号", as_index=False)["销售额"].max()
# 2. 重复记录取最新的一条
df["创建时间"] = pd.to_datetime(df["创建时间"])
df_latest = df.sort_values("创建时间", ascending=False).drop_duplicates(subset=["订单号"], keep="first")
# 3. 重复数据合并求和
df_merged = df.groupby(["订单号", "产品名称"], as_index=False).agg({
"销售额": "sum",
"销量": "sum",
"创建时间": "first"
})
3.5 特定列去重获取唯一值
python
# 获取唯一的产品列表
unique_products = df["产品名称"].unique()
print("\n所有产品：", unique_products)
# 获取唯一值和计数
product_counts = df["产品名称"].value_counts()
print("\n产品销量次数统计：")
print(product_counts)
# 保存唯一值到新文件
unique_products_df = pd.DataFrame({"产品名称": unique_products})
unique_products_df.to_excel("产品列表.xlsx", index=False)
四、分组统计
分组统计是数据分析的核心功能，可以快速生成各类汇总报表。
4.1 基础分组统计
python
# 按区域分组，统计销售额总和、平均值、最大值、最小值、计数
region_stats = df.groupby("区域").agg({
"销售额": ["sum", "mean", "max", "min", "count"]
}).round(2)
print("\n各区域销售统计：")
print(region_stats)
# 更简洁的写法，重命名列名
region_stats = df.groupby("区域", as_index=False).agg(
销售总额=("销售额", "sum"),
平均销售额=("销售额", "mean"),
订单数量=("订单号", "count"),
最高单额=("销售额", "max")
).round(2)
print("\n重命名后的统计结果：")
print(region_stats)
4.2 多字段分组
按多个维度分组统计：
python
# 按区域+产品分组统计
region_product_stats = df.groupby(["区域", "产品名称"], as_index=False).agg(
销售总额=("销售额", "sum"),
平均单价=("单价", "mean"),
总销量=("销量", "sum")
).round(2)
print("\n区域+产品分组统计：")
print(region_product_stats.head(10))
# 按月份+区域分组统计
df["月份"] = df["日期"].dt.month
month_region_stats = df.groupby(["月份", "区域"], as_index=False).agg(
月销售额=("销售额", "sum"),
订单数=("订单号", "count")
)
4.3 多个统计函数
对不同列应用不同的统计函数：
python
# 按部门分组统计
dept_stats = df.groupby("部门", as_index=False).agg({
"员工ID": "count", # 员工人数
"工资": ["mean", "sum", "max", "min"], # 工资统计
"年龄": "mean", # 平均年龄
"入职日期": "min" # 最早入职日期
})
print("\n部门统计：")
print(dept_stats)
4.4 自定义统计函数
python
# 计算销售额极差（最大值-最小值）
def sales_range(x):
return x.max() - x.min()
# 计算达成率：销售额大于目标的比例
def achievement_rate(x):
return (x > 10000).sum() / len(x) * 100
# 应用自定义函数
custom_stats = df.groupby("区域").agg({
"销售额": [
"sum",
"mean",
sales_range, # 自定义极差函数
("达成率%", achievement_rate) # 自定义达成率
]
}).round(2)
print("\n自定义统计结果：")
print(custom_stats)
4.5 分组后筛选
分组后筛选符合条件的组：
python
# 筛选销售总额大于100万的区域
region_stats = df.groupby("区域", as_index=False).agg(
销售总额=("销售额", "sum")
)
high_sales_regions = region_stats[region_stats["销售总额"] > 1000000]
print("\n销售额大于100万的区域：")
print(high_sales_regions)
# 筛选组内记录：各区域销售额前3名
top3_by_region = df.groupby("区域", group_keys=False).apply(
lambda x: x.nlargest(3, "销售额")
).reset_index(drop=True)
print("\n各区域销售额前3：")
print(top3_by_region[["区域", "产品名称", "销售额"]])
4.6 分组后应用转换
对分组内的数据进行转换，比如计算组内占比：
python
# 计算每个产品在区域内的销售额占比
df["区域总销售额"] = df.groupby("区域")["销售额"].transform("sum")
df["区域销售额占比%"] = (df["销售额"] / df["区域总销售额"] * 100).round(2)
print("\n区域内销售额占比：")
print(df[["区域", "产品名称", "销售额", "区域销售额占比%"]].head())
# 分组内排名
df["区域内排名"] = df.groupby("区域")["销售额"].rank(ascending=False, method="dense")
五、实战案例：销售报表生成
5.1 需求场景
> 有一份全年销售数据，需要生成如下报表：
> 1. 按月份统计销售趋势
> 2. 按区域统计销售业绩
> 3. 按产品统计销量排名
> 4. 各区域销售占比
> 5. 导出到Excel的多个工作表
python
import pandas as pd
from datetime import datetime
# 读取原始数据
df = pd.read_excel("全年销售数据.xlsx")
df["日期"] = pd.to_datetime(df["日期"])
df["月份"] = df["日期"].dt.strftime("%m月")
df["季度"] = df["日期"].dt.quarter.map({1: "Q1", 2: "Q2", 3: "Q3", 4: "Q4"})
# 1. 月度销售趋势
monthly_stats = df.groupby("月份", as_index=False).agg(
销售额=("销售额", "sum"),
订单数=("订单号", "count"),
客单价=("销售额", "mean")
).round(2)
# 2. 区域销售统计
region_stats = df.groupby("区域", as_index=False).agg(
销售总额=("销售额", "sum"),
订单数=("订单号", "count"),
平均单额=("销售额", "mean")
).round(2)
# 添加占比
region_stats["占比%"] = (region_stats["销售总额"] / region_stats["销售总额"].sum() * 100).round(2)
# 按销售额降序
region_stats = region_stats.sort_values(by="销售总额", ascending=False).reset_index(drop=True)
# 3. 产品销量排名
product_stats = df.groupby("产品名称", as_index=False).agg(
总销量=("销量", "sum"),
销售额=("销售额", "sum")
).sort_values(by="销售额", ascending=False).reset_index(drop=True)
product_stats["排名"] = product_stats.index + 1
# 4. 季度统计
quarter_stats = df.groupby("季度", as_index=False).agg(
销售额=("销售额", "sum"),
目标完成率=("销售额", lambda x: sum(x)/500000*100) # 季度目标50万
).round(2)
# 导出到Excel的不同工作表
output_file = f"销售报表_{datetime.now().strftime('%Y%m%d')}.xlsx"
with pd.ExcelWriter(output_file) as writer:
monthly_stats.to_excel(writer, sheet_name="月度趋势", index=False)
region_stats.to_excel(writer, sheet_name="区域统计", index=False)
product_stats.to_excel(writer, sheet_name="产品排名", index=False)
quarter_stats.to_excel(writer, sheet_name="季度统计", index=False)
print(f"✅ 报表生成完成：{output_file}")
5.2 增加格式优化
给报表添加格式，让它更美观：
python
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.formatting.rule import ColorScaleRule
wb = load_workbook(output_file)
# 通用表头样式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="3498db", end_color="3498db", fill_type="solid")
center_alignment = Alignment(horizontal="center", vertical="center")
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
# 设置表头样式
for col in range(1, ws.max_column + 1):
cell = ws.cell(row=1, column=col)
cell.font = header_font
cell.fill = header_fill
cell.alignment = center_alignment
# 调整列宽
for col in ws.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = (max_length + 2) * 1.2
ws.column_dimensions[column].width = adjusted_width
# 给销售额列添加颜色渐变
if "销售额" in [cell.value for cell in ws[1]]:
col_idx = [cell.value for cell in ws[1]].index("销售额") + 1
col_letter = chr(64 + col_idx)
ws.conditional_formatting.add(
f"{col_letter}2:{col_letter}{ws.max_row}",
ColorScaleRule(
start_type='min', start_color='ef4444',
mid_type='percentile', mid_value=50, mid_color='f59e0b',
end_type='max', end_color='10b981'
)
)
wb.save(output_file)
print("✅ 报表格式优化完成！")
六、高级操作技巧
6.1 透视表
pandas的pivot_table可以快速生成复杂的透视表：
python
# 行：区域，列：产品，值：销售额，聚合：求和
pivot = pd.pivot_table(
df,
index="区域",
columns="产品名称",
values="销售额",
aggfunc="sum",
fill_value=0,
margins=True, # 添加合计列
margins_name="合计"
).round(2)
print("\n销售透视表：")
print(pivot)
# 保存透视表
pivot.to_excel("销售透视表.xlsx")
6.2 交叉表
统计两个字段的交叉分布：
python
# 统计区域和产品的订单数量交叉分布
cross_tab = pd.crosstab(df["区域"], df["产品名称"], margins=True, margins_name="合计")
print("\n区域-产品订单数交叉表：")
print(cross_tab)
# 占比交叉表
cross_tab_pct = pd.crosstab(df["区域"], df["产品名称"], normalize="index").round(4) * 100
print("\n区域-产品占比交叉表（%）：")
print(cross_tab_pct)
6.3 分箱统计
把数值分成区间统计，比如按工资区间统计人数：
python
# 工资分箱
bins = [0, 10000, 15000, 20000, float("inf")]
labels = ["10k以下", "10k-15k", "15k-20k", "20k以上"]
df["工资区间"] = pd.cut(df["工资"], bins=bins, labels=labels, right=False)
# 统计各区间人数
salary_stats = df.groupby("工资区间", as_index=False)["员工ID"].count().rename(columns={"员工ID": "人数"})
print("\n工资区间统计：")
print(salary_stats)
6.4 滚动统计
计算移动平均等滚动指标：
python
# 按日期排序
df = df.sort_values("日期")
# 计算7天移动平均销售额
df["7天移动平均"] = df["销售额"].rolling(window=7, min_periods=1).mean().round(2)
# 计算累计销售额
df["累计销售额"] = df["销售额"].cumsum()
七、常见问题与解决方案
7.1 分组统计后列名变成多级索引
问题：groupby后列名是多级的，不好处理
解决方案：
用as_index=False参数，或者展平列名：
python
# 方法1：as_index=False
stats = df.groupby("区域", as_index=False)["销售额"].sum()
# 方法2：展平多级列名
stats.columns = ['_'.join(col).strip() for col in stats.columns.values]
7.2 排序时包含中文乱序
问题：中文排序不是按照拼音顺序
解决方案：
python
# 安装拼音库
# pip install pypinyin
from pypinyin import lazy_pinyin
# 添加拼音列排序
df["姓名拼音"] = df["姓名"].apply(lambda x: ''.join(lazy_pinyin(x)))
df = df.sort_values(by="姓名拼音").drop(columns="姓名拼音")
7.3 分组统计后数据太多显示不全
问题：分组结果太多，查看不方便
解决方案：
python
# 只显示前N条
print(region_stats.nlargest(10, "销售总额")) # 销售额前10的区域
print(region_stats.nsmallest(5, "销售总额")) # 销售额后5的区域
7.4 大数据量分组慢
问题：几百万行数据分组统计很慢
解决方案：
只保留需要的列，减少内存占用
数据类型优化，把字符串转成category类型
使用dask等并行计算库处理超大数据
超大数据建议先导入数据库用SQL统计
7.5 去重后索引不连续
问题：去重、筛选后索引不连续
解决方案：
python
df = df.reset_index(drop=True) # 重置索引
八、最佳实践
1. 操作前备份：任何修改前先备份原文件，避免操作错误丢失数据
2. 先验证再执行：排序、去重、分组后先验证结果是否符合预期，再保存到文件
3. 合理选择分组维度：避免过多维度分组导致结果过于零散
4. 处理空值：分组统计前处理空值，避免结果出现NaN
5. 命名规范：统计结果列名要清晰易懂，避免用默认的sum、mean等
6. 排序逻辑明确：多列排序时注明每个字段的升降序，方便后续维护
7. 去重策略合理：根据业务需求选择合适的去重方式，不要盲目删除所有重复
8. 结果可视化：统计结果尽量导出成带格式的报表，方便业务人员查看
排序、去重和分组统计是Excel数据处理的三大核心操作，熟练掌握这些方法可以应对绝大多数数据分析场景，大幅提升工作效率。Python的pandas库提供了非常简洁高效的API，让这些复杂操作只需要几行代码就能完成，是办公自动化的必备技能。
