Excel 函数 → pandas 对照 ·检验练习册（14 题）

配套Python小心得（5）与 Excel函数练习_销售数据分析.xlsx。

原笔记本把 19 个对照点讲了一遍，这本用来检验你到底记住了几个。

每题都是同一个套路：左边给 Excel 公式，右边你写 pandas;最后两题反过来考（给 pandas 代码，说出 Excel 函数名）。

覆盖的 19 个对照点

0. 数据准备（先运行这一格）

from pathlib import Pathimport pandas as pdFILE = "Excel函数练习_销售数据分析.xlsx"ifnot Path(FILE).exists():raise FileNotFoundError(f"找不到 {FILE}。请把数据文件放到本笔记本同一目录"f"（当前目录：{Path.cwd()}）"    )df = pd.read_excel(    FILE, sheet_name="数据源", dtype={"订单号": str})df = df.dropna(subset=["订单号"]).reset_index(drop=True)df["日期"] = pd.to_datetime(df["日期"])df["金额"] = df["数量"] * df["单价"]# 相当于 Excel H 列的公式 =F*Gdf["月份"] = df["日期"].dt.strftime("%Y-%m")# 相当于 TEXT(日期, "yyyy-mm")print("形状：", df.shape)print("各列类型：")print(df.dtypes.to_string())print("数据覆盖：", df["月份"].unique().tolist())df

形状： (20, 10)各列类型：订单号            object日期     datetime64[ns]销售员            object地区             object产品             object数量              int64单价              int64金额              int64状态             object月份             object数据覆盖： ['2024-01', '2024-02']

数据规模：20 行 × 10 列，日期覆盖 2024-01-05 至 2024-02-23。

完整数据见配套 Excel函数练习_销售数据分析.xlsx 的 数据源 工作表。

题 1 · 四个汇总数（SUM / COUNTA / AVERAGE）

任务：填 summary 字典，键固定为 total / orders / qty / avg_price（均价保留 2 位小数）。对应原笔记本第 1～4 节。

summary = {"total": int(df["金额"].sum()),"orders": len(df),"qty": int(df["数量"].sum()),"avg_price": round(float(df["单价"].mean()), 2),}print(summary)assert summary == {"total": 113840,"orders": 20,"qty": 233,"avg_price": 1490.0,}, ("四个数还没对上，""看看是 .sum()、len 还是 mean() 用错了")print("题 1 通过")

{'total': 113840, 'orders': 20, 'qty': 233, 'avg_price': 1490.0}题 1 通过

题 2 ·最大 / 最小金额，以及它落在哪张单上（MAX / MIN / INDEX+MATCH）

Excel：

• =MAX（数据源！$H$2：$H$21）、=MIN（数据源！$H$2：$H$21）
• 找出最大金额对应的订单号：=INDEX（数据源！$A$2：$A$21， MATCH（MAX（数据源！$H$2：$H$21）， 数据源！$H$2：$H$21， 0））

任务:p eak = {“max”： （金额， 订单号）， “min”： （金额， 订单号）}。

提示：Series.idxmax（） 返回最大值所在的行索引，再用它去 df.loc[行索引， “订单号”]。

peak = {"max": (        df["金额"].max(),        df.loc[df["金额"].idxmax(), "订单号"],    ),"min": (        df["金额"].min(),        df.loc[df["金额"].idxmin(), "订单号"],    ),}print(peak)assert peak == {"max": (22500, "D2024017"),"min": (1440, "D2024013"),}, ("金额或订单号错了：""idxmax 拿的是行号，不是订单号")print("题 2 通过")

{'max': (22500, 'D2024017'), 'min': (1440, 'D2024013')}题 2 通过

题 3 ·数量 × 单价再求和（SUMPRODUCT）

Excel：=SUMPRODUCT（数据源！$F$2：$F$21， 数据源！$G$2：$G$21）

任务：算出 product_sum（整数），并顺手验证它等于 df[“金额”].sum（）。

提示：两个 Series 直接相乘就是逐项相乘，再 .sum（）。

product_sum = int((df["单价"] * df["数量"]).sum())print(product_sum)assert product_sum == 113840, ("和 SUMPRODUCT 的结果差太多了，""检查是不是先求和再相乘")assert product_sum == int(df["金额"].sum()), ("应该和金额合计一致（因为 金额 = 数量 × 单价）")print("题 3 通过")

113840题 3 通过

题 4 · 条件计数（COUNTIF / COUNTIFS）

任务：counts = {"退货": ..., "赵敏退货": ..., "张伟华东": ...}。

提示：条件计数 = 布尔序列的 .sum()；多条件用 & 连接，每个条件都要加括号。

counts = {"退货": int((df["状态"] == "退货").sum()),"赵敏退货": int(        ((df["销售员"] == "赵敏") & (df["状态"] == "退货")).sum()    ),"张伟华东": int(        ((df["销售员"] == "张伟") & (df["地区"] == "华东")).sum()    ),}print(counts)assert counts == {"退货": 2,"赵敏退货": 1,"张伟华东": 5,}, ("计数不对：检查 & 两边有没有括号、""条件值有没有写错")print("题 4 通过")

{'退货': 2, '赵敏退货': 1, '张伟华东': 5}题 4 通过

题 5 · 单条件求和 + 分类汇总（SUMIF）

Excel：=SUMIF(数据源!2:21, "华东", 数据源!2:21)

任务：

1. east_total = 华东销售额（单个数字）
2. region_total = 四个地区各卖了多少钱（字典，键是地区名）——这一步 Excel 里要拖四个 SUMIF 或做透视表

提示：df.loc[条件, "金额"].sum()；分类汇总是 groupby("地区")["金额"].sum()。

east_total = df.loc[df["地区"] == "华东", "金额"].sum()region_total = {"华东": df.loc[df["地区"] == "华东", "金额"].sum(),"华南": df.loc[df["地区"] == "华南", "金额"].sum(),"华北": df.loc[df["地区"] == "华北", "金额"].sum(),"西南": df.loc[df["地区"] == "西南", "金额"].sum(),}region_total = df.groupby("地区")["金额"].sum().to_dict()region_total = {    k: int(v)for k, v in df.groupby("地区")["金额"].sum().items()}print(east_total)print(region_total)assert east_total == 53640, ("华东销售额不对：SUMIF 的第三参数是求和区域，""对应 loc 的列")assert region_total == {"华东": 53640,"华南": 22500,"华北": 26900,"西南": 10800,}, "四个地区加起来应该等于总金额 113840"print("题 5 通过")

53640{'华东': 53640, '华北': 26900, '华南': 22500, '西南': 10800}题 5 通过

题 6 · 多条件求和（SUMIFS）

Excel：=SUMIFS(数据源!2:21, 数据源!2:21, "华东", 数据源!2:21, "笔记本")

任务：

1. east_nb：华东 + 笔记本（两条件）
2. south_monitor_ok：华南 + 显示器 + 正常（三条件）
3. zw_feb：张伟 + 2024-02（两条件，用 月份 列）

提示：df.loc[(条件1) & (条件2) & (条件3), "金额"].sum()。

east_nb = df.loc[    (df["地区"] == "华东") & (df["产品"] == "笔记本"),"金额",].sum()south_monitor_ok = df.loc[    (df["地区"] == "华南")    & (df["产品"] == "显示器")    & (df["状态"] == "正常"),"金额",].sum()zw_feb = df.loc[    (df["销售员"] == "张伟") & (df["月份"] == "2024-02"),"金额",].sum()print(east_nb, south_monitor_ok, zw_feb)assert east_nb == 45000, ("华东笔记本应该是三笔 13500 + 9000 + 22500")assert south_monitor_ok == 9600, ("华南显示器且正常：别忘了把退货那笔排除")assert zw_feb == 31140, ("张伟 2024-02：三笔 7200 + 1440 + 22500")print("题 6 通过")

45000 9600 31140题 6 通过

题 7 ·条件平均（AVERAGEIF）

任务：平均值 = {“笔记本均价”： ...， “华北均额”： ...， “退货均额”： ...}，都保留 2 位小数。

提示：条件筛选后的 .mean（）;注意 AVERAGEIF 是“满足条件的行的平均”，不是“总和 / 总行数”。

averages = {"笔记本均价": df.loc[df["产品"] == "笔记本", "单价"].mean(),"华北均额": df.loc[df["地区"] == "华北", "金额"].mean(),"退货均额": df.loc[df["状态"] == "退货", "金额"].mean(),}print(averages)assert averages == {"笔记本均价": 4500.0,"华北均额": 5380.0,"退货均额": 3120.0,}, ("均值不对：先筛行再 mean，""别拿总和除总订单数")print("题 7 通过")

{'笔记本均价': 4500.0, '华北均额': 5380.0, '退货均额': 3120.0}题 7 通过

题 8 ·按月汇总（SUMIF + TEXT）

Excel：=SUMIF（数据源！$J$2：$J$21， “2024-02”， 数据源！$H$2：$H$21）（J 列是 =TEXT（B2， “yyyy-mm”））

任务：

1. feb_total：2024-02 销售额（用 月份 列）
2. feb_total_v2：不建月份列，直接用日期筛（df[“日期”].dt.month == 2）
3. month_total：两个月各多少（字典，键 “2024-01” / “2024-02”）

提示：strftime（“%Y-%m”）对应 TEXT（...， “yyyy-mm”）;d t.month 返回 1~12 的整数。

feb_total = int(df.loc[df["月份"] == "2024-02", "金额"].sum())feb_total_v2 = int(df.loc[df["日期"].dt.month == 2, "金额"].sum())month_total = {    k: int(v)for k, v in df.groupby("月份")["金额"].sum().items()}print(feb_total, feb_total_v2, month_total)assert feb_total == feb_total_v2 == 77140, ("两个口径应该一致；""不一致说明月份列或 dt.month 筛错了")assert month_total == {"2024-01": 36700,"2024-02": 77140,}, "两个月合计应等于 113840"print("题 8 通过")

77140 77140 {'2024-01': 36700, '2024-02': 77140}题 8 通过

题 9 ·按键查值（VLOOKUP / IFERROR）

Excel：

• =VLOOKUP（“D2024007”，数据源！$A$2：$H$21， 8， FALSE）
• =IFERROR（VLOOKUP（“D9999”， ...）， “查无此单”）

任务：

1. amount_7：D2024007 的金额（整数）
2. not_found：查 D9999，查不到返回 “查无此单”
3. batch：批量查 [“D2024001”， “D2024007”， “D9999”]，返回金额列表，查不到的位置放 “查无此单”

提示：把订单号设成索引后，就是“按键取值”：.loc[键， “列”];单个查用 Series.get（键， 默认值）（等价 IFERROR）;批量查要自己循环。

by_id = df.set_index("订单号")["金额"]# print(type(by_id))              # <class 'pandas.core.series.Series'># print(by_id.dtype)              # int64# print(by_id.shape)              # (20,)# print(by_id.ndim)               # 1# print(by_id.name)               # 金额# print(by_id.index)# Index(['D2024001', 'D2024002', ...], dtype='object', name='订单号')# print(by_id.index.dtype)        # object# print(by_id.index.name)         # 订单号# print(by_id.index.is_unique)    # True# print(isinstance(by_id, pd.Series))   # Trueamount_7 = int(by_id.loc["D2024007"])not_found = by_id.get("D9999", "查无此单")batch = [    int(by_id[k]) if k in by_id.index else"查无此单"for k in ["D2024001", "D2024007", "D9999"]]print(amount_7, not_found, batch)assert amount_7 == 2700, ("第 8 列是金额；用索引取值就不用数第几列了")assert not_found == "查无此单", ("VLOOKUP 查不到会报 #N/A，""pandas 用 get 的默认值兜住")assert batch == [13500, 2700, "查无此单"], ("批量查：存在的给金额，不存在的位置放默认文案")print("题 9 通过")

2700 查无此单 [13500, 2700, '查无此单']题 9 通过

题 10 ·分档（IF 嵌套 / pd.cut）

Excel：=IF（H2>=10000， “大单”， IF（H2>=3000， “中单”， “小单”））

任务：

1. 给 df 新增一列 档位，取值 大单 / 中单 / 小单
2. level_count：各档订单数（字典）

提示 :p d.cut（df[“金额”]， [0， 3000， 10000， float（“inf”）]， labels=[“小单”， “中单”， “大单”]， right=False）。right=False 是左闭右开，才能和 Excel 的 >= 对齐;inf 兜住上限。

# 给 df 增加 "档位" 列（可以就地赋值，也可以另存一列）df["档位"] = pd.cut(    df["金额"],    bins=[0, 3000, 10000, float("inf")],    labels=["小单", "中单", "大单"],    right=False,)level_count = {    k: int(v)for k, v in df["档位"].value_counts().items()}print(level_count)assert df["档位"].notna().all(), ("档位列还有空值：cut 的区间没覆盖到全部金额")assert level_count == {"小单": 9,"中单": 8,"大单": 3,}, "档位计数不对：检查 right=False 和分界点 3000/10000"print("题 10 通过")

{'小单': 9, '中单': 8, '大单': 3}题 10 通过

题 11 ·四舍五入与百分比（ROUND）

任务：wan（2 位）、return_rate（4 位）、rate_text（形如 “5.48%” 的字符串）。

提示：round（float（x）， 2）;百分比文本用 f-string 的 .2%。

wan = round(float(df["金额"].iloc[0] / 10000), 2)return_rate = round(    float(        df.loc[df["状态"] == "退货", "金额"].sum()        / df["金额"].sum()    ),4,)rate_text = f"{return_rate:.2%}"print(wan, return_rate, rate_text)assert wan == 1.35, "13500 元 = 1.35 万元"assert return_rate == 0.0548, ("退货率 = 退货金额 / 总金额，再保留 4 位")assert rate_text == "5.48%", ("百分比文本用 f-string 的 .2% 格式")print("题 11 通过")

1.35 0.0548 5.48%题 11 通过

题 12 ·从订单号里取信息（MID / 切片）

Excel：

• 取年份：=MID（数据源！$A$2， 2， 4） → 2024
• 取流水序号：=MID（数据源！$A$2， 6， 3） → 001，再 =VALUE（...） 变成 1

任务：

1. year_first：第一张单的年份（字符串 “2024”）
2. seq_first：第一张单的流水序号（整数 1）
3. year_count：各年份订单数（字典，键是年份字符串）

提示：Excel 的 MID（文本， 起始位， 长度） 从 1 开始数;Python 切片从 0 开始，所以 MID 的第 2 位要写 [1：5]。整列处理用 .str[1：5]。

year_first = df["订单号"].str[1:5].iloc[0]seq_first = int(df["订单号"].str[5:].iloc[0])year_count = {    k: int(v)for k, v in df["订单号"].str[1:5].value_counts().items()}print(year_first, seq_first, year_count)assert year_first == "2024", ("MID 从第 2 位开始取 4 位，对应 str[1:5]")assert seq_first == 1, ("后 3 位是 '001'，转成整数才是 1")assert year_count == {"2024": 20}, ("整列取年份用 .str[1:5]，再 value_counts()")print("题 12 通过")

2024 1 {'2024': 20}题 12 通过

题 13（综合）·销售员汇总表

Excel 里的做法：四个销售员各拖一套 SUMIF / COUNTIF / COUNTIFS / AVERAGEIF，再排序、设置百分比格式。

任务：生成摘要：

• 行索引是销售员，按 销售额从高到低排序
• 列：销售额、订单数、退货数、退货率（保留 4 位）
• 再写出 销售员汇总_练习.xlsx（工作表名 汇总）

提示：groupby（“销售员”）.agg（...），size 用来数订单;退货数是条件计数，需要一个 lambda;sort_values 排序;to_excel 写文件。

summary = (    df.groupby("销售员")    .agg(        销售额=("金额", "sum"),        订单数=("金额", "size"),        退货数=("状态", lambda s: int((s == "退货").sum())),    )    .assign(退货率=lambda d: (d["退货数"] / d["订单数"]).round(4))    .sort_values("销售额", ascending=False))summary["销售额"] = summary["销售额"].astype(int)print(summary.to_string())summary.to_excel("销售员汇总_练习.xlsx", sheet_name="汇总")print(summary)assert summary isnotNone, "summary 还是 None"assert list(summary.index) == ["张伟", "王强", "李娜", "赵敏"], ("没有按销售额降序排列")assert list(summary.columns) == ["销售额", "订单数", "退货数", "退货率"], ("列名或列顺序不对")assert (    summary.loc["张伟", "销售额"] == 53640and summary.loc["张伟", "订单数"] == 5)assert (    summary.loc["赵敏", "退货数"] == 1and summary.loc["赵敏", "退货率"] == 0.2)assert Path("销售员汇总_练习.xlsx").exists(), "还没写出 Excel"assert int(summary["销售额"].sum()) == 113840, ("四个人的销售额加起来应等于总金额")print("题 13 通过")

       销售额  订单数  退货数  退货率销售员张伟   53640    5    0  0.0王强   26900    5    0  0.0李娜   22500    5    1  0.2赵敏   10800    5    1  0.2       销售额  订单数  退货数  退货率销售员张伟   53640    5    0  0.0王强   26900    5    0  0.0李娜   22500    5    1  0.2赵敏   10800    5    1  0.2题 13 通过