ARTICLE · 1333240
EXCEL高级操作
Python小心得（5）是基础，这次EXCEL高级操作练习没有堆函数词典，而是用一套完整的订单数据，把日常报表里最容易混的几组函数串起来：条件求和、条件计数、加权平均、阶梯查找、排名和环比。
工作簿一共 5 张表：
明细|12 笔订单，6 个计算列
多条件统计|8个条件统计场景
查找与排名|业绩榜、提成、排名
嵌套实战|月度汇总、环比、评级
练习|8 道题，附答案
01｜先把手写数据变成可计算的列
明细表 15 列，前 9 列是原始数据：订单号、日期、区域、业务员、产品、数量、单价、成本单价、渠道。
12 笔订单覆盖 9—10 月、3 个区域、5 名业务员、4 种产品、2 种渠道。
后 6 列全部由公式生成：
销售额 = 数量 × 单价，成本 = 数量 × 成本单价，毛利 = 销售额 − 成本。
三步都用同一行的数据，后一步引用前一步的结果，不要把整条计算链塞进一个公式。
毛利率：除法要有兜底
=IFERROR(L3/J3,0)第一行 2,432 ÷ 10,392 = 23.40%。
只要销售额为 0，L3/J3 就会返回 #DIV/0！，整列汇总跟着报错。IFERROR（...，0） 把错误压成 0。
业绩等级：第一个嵌套
=IF(M3>=0.3,"优",IF(M3>=0.25,"良","一般"))三档判断：≥ 30% 为“优”，≥ 25% 为“良”，其余为“一般”。
第一行 23.40%，结果是“一般”；第二行 34.83%，是“优”。
嵌套 IF 从上往下执行，命中即停。阈值必须从大到小写。要是把 >=0.25 放前面，35% 也会先命中 25% 这档，永远到不了“优”。
月份：把日期压成文本
=TEXT(B3,"yyyy-mm")结果是文本 2026-09，不是日期。
这一步是为后面的统计服务：SUMIFS 条件写 “2026-10” 就能按月筛选，不必再写一对 “>=2026-10-01”、“<=2026-10-31” 的日期条件。
合计行
=SUM(F3:F14)数量、销售额、成本、毛利分别求和，整体毛利率继续兜底：
=IFERROR(L15/J15,0)结果：数量 316、销售额 58,334、成本 42,093、毛利 16,241、整体毛利率 27.84%。
02|条件一多，SUMIF 就不够用了
这一节的 8 个场景，按函数分组看。
SUMIFS：多条件求和
10 月华东销售额：
=SUMIFS(明细!$J$3:$J$14,明细!$C$3:$C$14,"华东",明细!$O$3:$O$14,"2026-10")结果 19,148。
SUMIFS 的顺序是：求和区、条件区 1、条件 1、条件区 2、条件 2。
而 SUMIF 是：条件区、条件、求和区。
求和区一个在最前，一个在最后，这是最高频的翻车点。
COUNTIFS：多条件计数
渠道订单里毛利率超过 25% 的笔数：
=COUNTIFS(明细!$I$3:$I$14,"渠道",明细!$M$3:$M$14,">0.25")结果 6。
华北 9 月订单数：
=COUNTIFS(明细!$C$3:$C$14,"华北",明细!$O$3:$O$14,"2026-09")结果 2。
比较符必须写进引号。">0.25" 是条件，>0.25 是错误写法。
SUMPRODUCT：布尔乘法
全公司加权平均单价：
=SUMPRODUCT(明细!$F$3:$F$14,明细!$G$3:$G$14)/SUM(明细!$F$3:$F$14)结果 184.60。
加权平均 = Σ（数量 × 单价）÷ Σ 数量。不能直接对单价列求 AVERAGE，否则 3 台显示器和 60 个鼠标会变成同等权重。
华东且渠道的订单数：
=SUMPRODUCT((明细!$C$3:$C$14="华东")*(明细!$I$3:$I$14="渠道"))结果 2。
条件成立得 TRUE，不成立得 FALSE;TRUE 乘 TRUE 等于 1，只要一个不成立就是 0。乘上销售额再求和，就等于“只统计同时满足条件的行”。
MAXIFS / MINIFS：条件极值
华南最大单笔：
=MAXIFS(明细!$J$3:$J$14,明细!$C$3:$C$14,"华南")结果 3,975。
华东最小单笔：
=MINIFS(明细!$J$3:$J$14,明细!$C$3:$C$14,"华东")结果 3,180。
MAXIFS / MINIFS 需要 Excel 2019 或 Microsoft 365。老版本退回数组写法：
=SUMPRODUCT(MAX((明细!$C$3:$C$14="华南")*明细!$J$3:$J$14))AVERAGEIF：条件平均
显示器平均毛利率：
=AVERAGEIF(明细!$E$3:$E$14,"显示器",明细!$M$3:$M$14)结果 23.60%。
第三个参数不能省。省略后函数会对条件区自己求平均，而条件区是产品名称，对文本求平均没有意义。
这一节的三条铁律
比较符、通配符一律进引号：">0.25"、"张*"、"<>已完成"。 SUMIFS 求和区在第一位，SUMIF 求和区在最后一位。 统计区域加 $ 锁死，如 $J$3：$J$14;否则往下拖公式会整体偏移。
03｜查找与排名：从取数到取人
每人业绩：SUMIF
=SUMIF(明细!$D$3:$D$14,A4,明细!$J$3:$J$14)以姓名作为条件，把销售额加总。A4 是相对引用，往下拖自动换人。
结果：
排名用 RANK。EQ：
=RANK.EQ(B4,$B$4:$B$9,0)第三参数 0 是降序。写 1 就是升序，适合“用时最短”“成本最低”。有并列值时，会得到相同名次并跳号，如 1、2、2、4。
阶梯提成：VLOOKUP 近似匹配
费率表：
=ROUND(B4*VLOOKUP(B4,$H$22:$I$25,2,1),2)第四参数写 1 或 TRUE，就是近似匹配：找“不超过查找值的最大值”所在行。
18,768 → 15,000 档 → 5% → 938.40 13,952 → 10,000 档 → 4% → 558.08 8,745 → 5,000 档 → 3% → 262.35
硬前提：第一列必须从小到大升序。顺序一乱，结果全错，还不报错。
第四参数写 0 是精确匹配，8,745 在费率表里找不到，直接返回 #N/A。
等级：三层嵌套 IF
=IF(B4>=15000,"金牌",IF(B4>=8000,"银牌",IF(B4>=5000,"铜牌","达标")))阈值从高到低。赵敏 8,745 命中第二层，是“银牌”。
占比：分母锁死
=B4/SUM($B$4:$B$9)分子跟着行走，分母锁死。六个人加起来正好 100%。
INDEX+MATCH：不再受“从左往右”限制
VLOOKUP 只能让查找列待在最左边。INDEX+MATCH 没有这个限制。
业绩第一名：
=INDEX($A$4:$A$9,MATCH(MAX($B$4:$B$9),$B$4:$B$9,0))MAX 找到 18,768;MATCH 找到它在 B4：B9 的位置;INDEX 回到 A 列取姓名，得到“张伟”。
业绩第 3 名：
=INDEX($A$4:$A$9,MATCH(LARGE($B$4:$B$9,3),$B$4:$B$9,0))把 MAX 换成 LARGE（区域，3），得到“赵敏”。
陈杰的提成：
=INDEX($D$4:$D$9,MATCH("陈杰",$A$4:$A$9,0))从 A 列找位置，再去 D 列取同一行的值，得到 558.08。以后在 A 列左边插列，这个公式不用改。
MATCH 第三参数 0 是精确匹配，查姓名时不能省。
双条件求和与条件计数
陈杰卖键盘的销售额：
=SUMPRODUCT((明细!$D$3:$D$14="陈杰")*(明细!$E$3:$E$14="键盘")*明细!$J$3:$J$14)结果 3,560。
业绩超过 6,000 的人数：
=COUNTIF($B$4:$B$9,">6000")结果 3 人：张伟、陈杰、赵敏。
04｜嵌套实战：一个格子算三件事
月度对比：
本月销售额与毛利
=SUMIFS(明细!$J$3:$J$14,明细!$O$3:$O$14,$A3)=SUMIFS(明细!$L$3:$L$14,明细!$O$3:$O$14,$A3)同一套月份条件，只换求和列。
毛利率
=IFERROR(C3/B3,0)环比：找上一月
=IFERROR(B3/INDEX($B$3:$B$4,MATCH(A3,$A$3:$A$4,0)-1)-1,"")以 10 月这行为例，从里往外拆：
MATCH（“2026-10”，$A$3：$A$4,0） 返回 2; 2-1=1，上一月在第 1 行； INDEX（$B$3：$B$4,1） 取出 26,364; 31,970/26,364-1=0.2126，环比增长 21.26%。
-1 就是“上一行”。比直接写 B3 更耐改：中间插入新月份，公式会跟着走。
综合评级：IF + AND
=IF(E3="","新月份",IF(AND(E3>=0.2,D3>=0.28),"双达标","待改进"))先排除空值，再用 AND 合并两个条件：环比 ≥ 20% 毛利率， ≥ 28%。
10 月两个都过线，评为“双达标”。
一个边界情况
9 月是基期，MATCH 返回 1,1-1=0。INDEX（区域，0） 不报错，而是返回整列，公式最终落到 0。
所以 9 月显示环比 0、评级“待改进”，而不是公式里写的“新月份”。
要让基期正确显示，把 F3 改成：
=IF(MATCH(A3,$A$3:$A$4,0)=1,"新月份",IF(AND(E3>=0.2,D3>=0.28),"双达标","待改进"))先想清楚边界，再写正常情况。
05｜8 道练习：先做，再看答案
答案在“练习”表的 D、E 两列。建议先遮住，用明细数据自己写一遍。
1. 10 月华东销售额
=SUMIFS(明细!$J$3:$J$14,明细!$C$3:$C$14,"华东",明细!$O$3:$O$14,"2026-10")19,148
2. 渠道订单中毛利率超过 30% 的笔数
=COUNTIFS(明细!$I$3:$I$14,"渠道",明细!$M$3:$M$14,">0.3")3
3. 全公司加权平均单价
=SUMPRODUCT(明细!$F$3:$F$14,明细!$G$3:$G$14)/SUM(明细!$F$3:$F$14)184.60
4. 业绩第 2 名是谁
=INDEX(查找与排名!$A$4:$A$9,MATCH(LARGE(查找与排名!$B$4:$B$9,2),查找与排名!$B$4:$B$9,0))陈杰
5. 赵敏的提成
=ROUND(查找与排名!B6*VLOOKUP(查找与排名!B6,查找与排名!$H$22:$I$25,2,1),2)262.35
6. 赵敏的等级
=IF(查找与排名!B6>=15000,"金牌",IF(查找与排名!B6>=8000,"银牌",IF(查找与排名!B6>=5000,"铜牌","达标")))银牌
7. 键盘在渠道的销售额
=SUMPRODUCT((明细!$E$3:$E$14="键盘")*(明细!$I$3:$I$14="渠道")*明细!$J$3:$J$14)4,272
8. 10 月相对 9 月的环比
=IFERROR(嵌套实战!B4/嵌套实战!B3-1,"")21.26%
第 8 题直接引用两个算好的格子，适合月份位置固定的报表;第 4 题那种 INDEX+MATCH 写法，更适合月份会增删的场景。
06｜8 个容易翻车的细节
SUMIFS 和 SUMIF 参数顺序相反。 比较符、通配符要进引号。 统计区域要加 $，否则往下拖会偏移。 VLOOKUP 近似匹配的表必须升序。 精确查找第四参数写 0。 MATCH 返回位置，不是值;INDEX 再用位置取值。 TEXT 出来的月份是文本，条件要写成同样的文本格式。 IFERROR 是兜底，不是遮羞布——逻辑写错也会被它藏起来。
07｜一张函数地图
08｜把练习变成你的月报模板
这份练习真正练的是三件事：
先把数据整理成可计算的列：销售额、成本、毛利、毛利率、月份； 再用条件函数汇总：SUMIFS、COUNTIFS、SUMPRODUCT; 最后用查找和判断做展示：INDEX+MATCH、VLOOKUP、嵌套 IF。
