Python 玩转 Excel 案例【第 17 期】

📌 案例说明

原始数据表（销售数据.xlsx）：

这是销售数据分析中非常常见的场景：需要按区域和月份汇总销售额，并计算完成率、排名等关键指标。

原始数据是明细级别的销售记录（每个区域每月有多条记录），我们需要：

1. 1. 按区域和月份汇总销售额
2. 2. 添加总计列和总计行
3. 3. 计算每个区域的完成率（实际销售额/目标销售额）
4. 4. 对区域进行业绩排名
5. 5. 按排名排序输出

在 Excel 中，这类需求通常使用数据透视表 + 手动添加公式来完成，操作繁琐且容易出错。

本期案例将跟大家一起学习如何用 Python 一键完成销售业绩分析报告。

核心操作：数据透视 + 指标计算 + 数据排序

• • 关键函数：pivot_table()、rank()、map()、concat()
• • 核心逻辑：使用透视表汇总数据，自动添加完成率和排名，按业绩排序输出

📜 完整代码

"""
销售业绩分析 - 按区域和月份汇总销售额
自动计算完成率、排名，并排序输出
"""
import pandas as pd

# 读取数据
df = pd.read_excel("销售数据.xlsx")

# 1. 创建透视表
pivot = df.pivot_table(
    values="销售额",
    index="区域",
    columns="月份",
    aggfunc="sum",
    fill_value=0
)

# 2. 添加总计列和总计行
pivot["总计"] = pivot.sum(axis=1)
pivot.loc["总计"] = pivot.sum()

# 3. 添加完成率（只对非总计行计算，总计行留空）
targets = {"华东": 1000000, "华南": 800000, "华北": 600000, "华西": 500000}
pivot["完成率"] = float('nan')
non_total_mask = pivot.index != "总计"
pivot.loc[non_total_mask, "完成率"] = (
    pivot.loc[non_total_mask, "总计"] / 
    pivot.index[non_total_mask].map(targets).fillna(1) * 100
).round(1)

# 4. 添加排名（只对非总计行计算排名）
pivot["排名"] = float('nan')
pivot.loc[non_total_mask, "排名"] = pivot.loc[non_total_mask, "总计"].rank(
    ascending=False, method="min"
)

# 5. 按排名排序
sorted_data = pivot.drop("总计").sort_values("排名")
pivot = pd.concat([sorted_data, pivot.loc[["总计"]]])

# 6. 保存
pivot.to_excel("销售业绩分析.xlsx")
print("✅ 报告已生成")

运行结果（销售业绩分析.xlsx）：

💡 亮点：一键完成从明细数据到分析报告的全流程，自动计算完成率和排名，并按业绩排序输出。

第一步：导入所需库

import pandas as pd

代码解释：

• • import pandas as pd
  导入 pandas 库，这是 Python 数据分析的核心武器。它提供了 read_excel() 读取 Excel、pivot_table() 创建透视表、rank() 计算排名等强大功能。

第二步：读取数据

df = pd.read_excel("销售数据.xlsx")

代码解释：

• • pd.read_excel()
  读取 Excel 文件，返回 DataFrame（数据表格）对象。
• • df
  存储销售明细数据，包含区域、月份、销售额三列。

💡 小贴士：read_excel() 默认读取第一个工作表。如果文件有多个 Sheet，可以用 sheet_name 参数指定，如

 pd.read_excel("文件.xlsx", sheet_name="Sheet2")

第三步：创建数据透视表

pivot = df.pivot_table(
    values="销售额",
    index="区域",
    columns="月份",
    aggfunc="sum",
    fill_value=0
)

这行代码是整个案例的核心，将明细数据转换为汇总报表。我们来逐步拆解：

3.1 透视表的作用

原始数据是明细记录（每个区域每月有多条记录），透视表可以：

• • 按区域分组
• • 按月份分列
• • 对销售额进行汇总

3.2 参数详解

3.3 执行过程示意

原始数据：

区域    月份    销售额
华东    1月    25795
华东    1月    10860
华东    1月    48158
...（共120行）

透视后：

区域     1月       2月       3月
华东    266076    285426    255575
华南    224727    273610    231571
华北    329973    315448    272525
华西    314681    286162    291086

💡 小贴士：pivot_table() 是 pandas 中最强大的函数之一，可以轻松实现 Excel 数据透视表的功能。如果需要对多个数值列进行汇总，可以传入列表：values=["销售额", "数量"]。

第四步：添加总计列和总计行

pivot["总计"] = pivot.sum(axis=1)
pivot.loc["总计"] = pivot.sum()

这两行代码为报表添加总计，是数据分析的标准操作。

4.1 添加总计列

pivot["总计"] = pivot.sum(axis=1)

• • pivot["总计"]：创建一个新的”总计”列
• • axis=1：水平方向操作（对每一行求和）
• • 计算每个区域在所有月份的销售额总和

执行后：

4.2 添加总计行

pivot.loc["总计"] = pivot.sum()

• • pivot.loc["总计"]：创建索引为”总计”的新行
• • pivot.sum()：对每一列求和（默认 axis=0）
• • 将每列的总和赋值给总计行

执行后：

第五步：添加完成率

targets = {"华东": 1000000, "华南": 800000, "华北": 600000, "华西": 500000}
pivot["完成率"] = float('nan')
non_total_mask = pivot.index != "总计"
pivot.loc[non_total_mask, "完成率"] = (
    pivot.loc[non_total_mask, "总计"] / 
    pivot.index[non_total_mask].map(targets).fillna(1) * 100
).round(1)

这是计算业绩完成率的关键代码，我们逐步拆解：

5.1 准备工作：定义目标和初始化列

targets = {"华东": 1000000, "华南": 800000, "华北": 600000, "华西": 500000}
pivot["完成率"] = float('nan')

• • targets = {...}
  创建了一个字典，用于存储每个区域的销售目标（单位：元）。
• • 键（Key）：区域名称，如 "华东"
• • 值（Value）：该区域的业绩目标，如 1000000（100万）
  这个字典稍后会作为map方法的“查找表”，用于获取每个区域对应的目标值。
• • pivot["完成率"] = float('nan')
  在 pivot 表中新增一个名为 "完成率" 的列，并用 NaN（Not a Number，即空值）进行初始化。

5.2 创建布尔掩码

non_total_mask = pivot.index != "总计"

先理解 pivot.index：

index 是 DataFrame 的行标签。当前 pivot 表的行标签就是区域名称：

华东、华南、华北、华西、总计

pivot.index != "总计" 就是把这5个标签逐一和 "总计" 比较：

最终得到：[True, True, True, True, False]

这个布尔序列就是掩码，后面用 pivot.loc[non_total_mask, ...] 时，只有 True 的行会被选中（即排除总计行）。

5.3 获取实际销售额

pivot.loc[non_total_mask, "总计"]

• • 从非总计行中提取”总计”列的值
• • 返回一个 Series，包含各区域的实际销售额

执行结果：

华东    807077
华南    729908
华北    917946
华西    891929
Name: 总计, dtype: int64

5.4 获取目标值

pivot.index[non_total_mask].map(targets).fillna(1)

• • pivot.index[non_total_mask]：获取非总计行的区域名称
• • .map(targets)：将区域名映射为目标销售额（字典查找）
• • .fillna(1)：如果某个区域不在 targets 中，用 1 填充（避免除零）

map() 方法回顾：

map() 是 pandas 中用于数据映射的方法，将一个 Series 中的每个值按照规则转换成另一个值。

# 基本用法
df["列名"].map(字典)  # 字典映射
df["列名"].map(函数)  # 函数映射

# 示例
targets = {"华东": 1000000, "华南": 800000}
pivot.index.map(targets)

执行过程：

💡 记忆口诀：map 做映射，字典是桥梁，用键找值，找不到返回 NaN。

5.5 计算完成率

(实际销售额 / 目标值 * 100).round(1)

• • 为什么要 ×100？
  完成率通常用百分比表示。
  实际销售额 ÷ 目标值 得到的是小数（如 0.807），×100 后变成百分数（80.7），更符合业务阅读习惯。
• • .round(1) 是什么？
  保留 1 位小数。
  80.7 已经是一位小数，如果除不尽的情况，比如 80.6666，就会四舍五入为 80.7。

5.6 执行过程示意

💡 小贴士：使用 fillna(1) 处理缺失目标值的区域，虽然会产生很大的完成率，但能避免程序报错，也提示我们需要检查数据。

第六步：添加排名

pivot["排名"] = float('nan')
pivot.loc[non_total_mask, "排名"] = pivot.loc[non_total_mask, "总计"].rank(
    ascending=False, method="min"
)

这行代码为区域添加业绩排名，我们详细解析：

6.1 rank() 方法参数

6.2 排名方法对比

假设销售额为：[100, 90, 90, 80]

6.3 执行过程

# 实际销售额 Series
pivot.loc[non_total_mask, "总计"]
# 华北    917946
# 华西    891929
# 华东    807077
# 华南    729908

# 降序排名
.rank(ascending=False, method="min")
# 华北    1
# 华西    2
# 华东    3
# 华南    4

6.4 为什么用布尔掩码？

pivot.loc[non_total_mask, "排名"] = ...

• • 只对非总计行计算排名
• • 总计行的排名保持为 NaN（空白）
• • 避免总计行参与排名（它会是第1名）

第七步：按排名排序

sorted_data = pivot.drop("总计").sort_values("排名")
pivot = pd.concat([sorted_data, pivot.loc[["总计"]]])

这两行代码将区域按排名排序，总计行放在最后。

7.1 删除总计行并排序

sorted_data = pivot.drop("总计").sort_values("排名")

• • pivot.drop("总计")：删除总计行（默认 axis=0，删除行）
• • .sort_values("排名")：按排名列升序排序（1, 2, 3, 4…）

💡 小技巧：sort_values() 默认 ascending=True（升序），不写参数时排名 1,2,3… 自然排在最前，无需额外设置。

7.2 重新拼接总计行

pivot = pd.concat([sorted_data, pivot.loc[["总计"]]])

• • pivot.loc[["总计"]]：用双括号确保返回 DataFrame（不是 Series）
• • pd.concat()：垂直拼接（axis=0 默认），将总计行放在最后

7.3 为什么用双括号？

pivot.loc["总计"]      # 返回 Series（一行数据）
pivot.loc[["总计"]]    # 返回 DataFrame（一行一行的数据表格）

如何区分行和列？

• • loc["总计"] – 单参数时，默认操作行，取的是”总计”这一行
• • loc[:, "总计"] – 两个参数时，逗号后是列，取的是”总计”这一列
• • loc[["总计"]] – 双括号还是操作行，只是返回 DataFrame 格式

执行结果对比：

# 单括号 - 取行，返回 Series
pivot.loc["总计"]
# 1月      1135457
# 2月      1160646
# 3月      1050757
# 总计     3346860
# Name: 总计, dtype: int64

# 双括号 - 取行，返回 DataFrame
pivot.loc[["总计"]]
#       1月       2月       3月      总计
# 总计  1135457  1160646  1050757  3346860

# 取列 - 返回 Series
pivot.loc[:, "总计"]
# 华东    807077
# 华南    729908
# 华北    917946
# 华西    891929
# 总计    3346860
# Name: 总计, dtype: int64

• • concat() 需要拼接两个 DataFrame
• • 单括号返回 Series，无法直接拼接
• • 双括号保持 DataFrame 格式，可以顺利拼接

💡 技巧：看逗号！有逗号且列名在后 → 取列；无逗号 → 取行。要接 concat，就用双括号取行。

第八步：保存结果

pivot.to_excel("销售业绩分析.xlsx")
print("✅ 报告已生成")

代码解释：

• • to_excel()
  将 DataFrame 保存为 Excel 文件。
• • "销售业绩分析.xlsx"
  输出文件名。
• • print("✅ 报告已生成")
  输出成功提示，让用户知道程序执行完成。

⚠️ 注意：这里没有设置 index=False，因为区域列本身就是索引，我们需要保留它作为报表的第一列。

📚 本期核心知识点

📍 知识点 1：pivot_table() 数据透视表

pivot = df.pivot_table(
    values="销售额",
    index="区域",
    columns="月份",
    aggfunc="sum",
    fill_value=0
)

📍 知识点 2：rank() 排名计算

series.rank(ascending=False, method="min")

记忆口诀：rank 排座次，降序大在前，并列取最小。

📍 知识点 3：布尔掩码筛选

non_total_mask = pivot.index != "总计"
pivot.loc[non_total_mask, "列名"] = ...

• • 创建布尔掩码标记需要操作的行
• • 用 .loc 结合掩码进行条件赋值
• • 避免循环，提高效率

📍 知识点 4：map() 数据映射

pivot.index[non_total_mask].map(targets).fillna(1)

• • 作用：将索引值映射为目标值（字典查找）
• • 链式操作：映射后立即用 fillna() 处理缺失值

📍 知识点 5：concat() 拼接数据

pivot = pd.concat([df1, df2])

• • 作用：垂直或水平拼接多个 DataFrame
• • 默认 axis=0：垂直拼接（增加行）
• • axis=1：水平拼接（增加列）

🔄 本案例核心流程

① 读取明细数据 → ② 创建透视表 → ③ 添加总计列和总计行 → ④ 计算完成率 → ⑤ 计算排名 → ⑥ 按排名排序 → ⑦ 保存报告

读取销售明细 (df)
        ↓
pivot_table 透视
        ↓
添加总计列 (sum axis=1)
添加总计行 (loc["总计"])
        ↓
计算完成率 (map + fillna)
        ↓
计算排名 (rank)
        ↓
排序 (drop + sort_values + concat)
        ↓
保存到新Excel (to_excel)

📦 资源获取提示

关注「码农自习室」，后台回复关键词 PythonExcel案例，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！

❤️ 支持我们

如果本文对你有帮助，欢迎点赞 + 关注 + 点亮小红心，你的支持就是我们持续创作的最大动力～