fx
嵌套组合

Excel函数嵌套

共 - 条 · 复制后改区域

嵌套怎么算

内层函数的返回值,就是外层某个参数。Excel 从最内层往外算,一层算完再交给上一层。

1. 什么能嵌套

多数函数都能嵌,关键是内层返回类型要匹配外层该参数。

  • 数值:SUM、MATCH 行号、YEAR…
  • 文本:TEXT、LEFT、& 拼接、XLOOKUP…
  • 逻辑:比较式、AND/OR、ISNUMBER…
  • 数组:FILTER、SORT、UNIQUE…

例:INDEX(C:C, MATCH(A2,A:A,0)) —— MATCH 给行号,填进 INDEX。

2. 基本规则

  1. 由内向外:先算最深括号
  2. 括号成对:一层函数一对 ()
  3. 参数位要对:塞进需要该类型的参数
  4. 一层一事:查找 / 筛选 / 排序 / 兜错分开
  5. 别太深:实务 4~6 层,再深用辅助列或 LET

3. 怎么读、怎么写

读:从最内层看「得到什么」,再看外层怎么用。

=TAKE(SORT(FILTER(表,条件),列,-1),5)
     │     │      └ 先筛选
     │     └ 再排序
     └ 最后取前5行

写:先列步骤,再填区域;需要时外包 IFERROR / IFNA。

4. 常见套路

  • 查找+兜错:IFERROR(XLOOKUP(...),"无")
  • 定位+取值:INDEX(..., MATCH(...))
  • 筛选流水线:FILTER → SORT → TAKE
  • 条件聚合:布尔相乘 + SUM / SUMPRODUCT

常见错误:把 FILTER 数组结果塞进只接受单个数字的参数。

去做嵌套习题
查找+条件(16)
VLOOKUP 找不到兜底 =IFERROR(VLOOKUP(A2,Sheet2!A:D,4,0),"未找到") 点击展开拆解讲解

作用:在 Sheet2 精确查找 A2 并返回第4列;找不到时显示「未找到」。

拆解讲解(由外到内 / 从左到右)
  1. 最外层 IFERROR(计算, 备用值):内部报错时返回「未找到」。
  2. 内层 VLOOKUP(查找值, 区域, 列号, 匹配方式):执行查找。
  3. 查找值 A2:要找的关键字。
  4. 区域 Sheet2!A:D:被查表,查找列必须是最左 A 列。
  5. 列号 4:返回区域内第4列(D列)。
  6. 匹配方式 0:精确匹配。
  7. 组合:先 VLOOKUP;出现 #N/A 等错误时被 IFERROR 替换成文案。
双条件 INDEX+MATCH =IFERROR(INDEX(C:C,MATCH(A2&B2,A:A&B:B,0)),"") 点击展开拆解讲解

作用:用「A+B」双条件定位行,再取 C;失败返回空。

拆解讲解(由外到内 / 从左到右)
  1. 外层 IFERROR(...,""):失败返回空。
  2. INDEX(C:C, 行号):按行号取 C 列,负责「取值」。
  3. MATCH(查找值, 查找数组, 0):负责「算行号」,0=精确匹配。
  4. 查找值 A2&B2:两条件拼成复合键。
  5. 查找数组 A:A&B:B:两列同行拼接,形成可匹配键。
  6. 组合:MATCH 找复合键位置 → INDEX 取 C → IFERROR 消化错误。
  7. 注意:整列拼接慢,实务改用 A2:A1000&B2:B1000。
XLOOKUP 默认值 =XLOOKUP(A2,B:B,C:C,"无") 点击展开拆解讲解

作用:在 B 列找 A2,返回 C;找不到显示「无」。

拆解讲解(由外到内 / 从左到右)
  1. XLOOKUP(查找值, 查找列, 返回列, [未找到]):查找列不必在最左。
  2. 查找值 A2 / 查找列 B:B / 返回列 C:C:标准三件套。
  3. 第4参数「无」:未找到默认值,相当于内置兜底。
  4. 组合:单函数完成「查找+取回+兜底」。
XLOOKUP 双键拼接 =XLOOKUP(A2&B2,A:A&B:B,C:C,"无",0) 点击展开拆解讲解

作用:双条件拼键后精确查找。

拆解讲解(由外到内 / 从左到右)
  1. 查找值 A2&B2:两条件拼接。
  2. 查找数组 A:A&B:B:数据区同样按行拼接。
  3. 返回数组 C:C:命中行结果。
  4. 未找到「无」+ 匹配模式 0:精确匹配兜底。
  5. 组合:先造复合键,再交给 XLOOKUP。
数组多条件匹配 =INDEX(C:C,MATCH(1,(A:A=E2)*(B:B=F2),0)) 点击展开拆解讲解

作用:布尔相乘实现「且」条件,再取 C。

拆解讲解(由外到内 / 从左到右)
  1. INDEX(C:C,行号):最终取值。
  2. MATCH(1, 数组, 0):找第一个等于1的位置。
  3. (A:A=E2)、(B:B=F2):各自得到 TRUE/FALSE 数组。
  4. 相乘 *:两条件都真才得1,实现逻辑「且」。
  5. 组合:布尔相乘→MATCH找1→INDEX取值。
  6. 旧版可能需 Ctrl+Shift+Enter;区域请缩小。
LOOKUP 倒数技巧多条件 =IFERROR(LOOKUP(1,0/((A:A=E2)*(B:B=F2)),C:C),"") 点击展开拆解讲解

作用:多条件匹配并倾向返回最后一条。

拆解讲解(由外到内 / 从左到右)
  1. 外层 IFERROR:无匹配返回空。
  2. LOOKUP(1, 查找向量, 结果向量):找≤1的位置并返回结果。
  3. (A=E2)*(B=F2):双条件为真处为1。
  4. 0/条件:真→0,假→#DIV/0!。
  5. LOOKUP 会忽略错误,近似查找常落到最后一个有效0。
  6. 组合:0/条件制造稀疏向量→LOOKUP取末条→返回C。
按表名动态查表 =IFERROR(VLOOKUP(A2,INDIRECT("'"&B2&"'!A:D"),4,0),"") 点击展开拆解讲解

作用:B2写表名,动态拼引用再VLOOKUP。

拆解讲解(由外到内 / 从左到右)
  1. 外层 IFERROR:表名错或找不到时返回空。
  2. VLOOKUP(A2, 动态区域, 4, 0):在拼出的表里精确查找。
  3. INDIRECT(文本):把字符串变成真正引用。
  4. "'"&B2&"'!A:D":拼成 '一月'!A:D,单引号兼容中文/空格表名。
  5. 组合:拼表名→INDIRECT成区域→VLOOKUP→IFERROR。
  6. 注意:INDIRECT 易失,表多可能变慢。
OFFSET 限定区域 =IFERROR(VLOOKUP(A2,OFFSET(Sheet2!A1,0,0,100,4),4,0),"") 点击展开拆解讲解

作用:先裁出固定区域再查找,避免整列。

拆解讲解(由外到内 / 从左到右)
  1. 外层 IFERROR:失败返回空。
  2. VLOOKUP(...,4,0):在裁好的区域精确查找。
  3. OFFSET(起点,行偏,列偏,高,宽):从锚点构造矩形。
  4. Sheet2!A1 + 0,0 + 100,4:即 A1:D100。
  5. 组合:OFFSET定范围→VLOOKUP查找→IFERROR兜底。
从下往上找最后一次 =XLOOKUP(A2,B:B,C:C,,0,-1) 点击展开拆解讲解

作用:精确匹配但从下往上搜,取最后一次。

拆解讲解(由外到内 / 从左到右)
  1. XLOOKUP 六参数:查找值/查找列/返回列/未找到/匹配/搜索。
  2. 匹配模式0:精确匹配。
  3. 搜索模式-1:从下往上,命中最后一次出现。
  4. 第4参数留空:未找到仍是#N/A(需要可写"无")。
  5. 组合:精确匹配+反向搜索=取最新一条。
从上往下找第一次 =XLOOKUP(A2,B:B,C:C,,0,1) 点击展开拆解讲解

作用:精确匹配并从上往下搜,取第一次。

拆解讲解(由外到内 / 从左到右)
  1. 结构同上,仅搜索模式不同。
  2. 搜索模式1:从上往下(默认)。
  3. 对照:首条用1,末条用-1。
双表级联查找 =IFERROR(XLOOKUP(A2,B:B,C:C),IFERROR(VLOOKUP(A2,D:E,2,0),"")) 点击展开拆解讲解

作用:先查主区域,失败再查副区域。

拆解讲解(由外到内 / 从左到右)
  1. 最外层 IFERROR(主查找, 备用表达式):主失败才进备用。
  2. 主查找 XLOOKUP(A2,B:B,C:C)。
  3. 备用再套 IFERROR(VLOOKUP(A2,D:E,2,0),""):副表也失败才空。
  4. 组合:主表→副表→空,形成优先级字典。
按表头名动态列号 =IFERROR(VLOOKUP(A2,Sheet2!A:D,MATCH("数量",Sheet2!A1:D1,0),0),"") 点击展开拆解讲解

作用:列号用表头「数量」动态算出,不写死。

拆解讲解(由外到内 / 从左到右)
  1. 外层 IFERROR:兜底。
  2. VLOOKUP 负责按 A2 找行。
  3. 列号位置用 MATCH("数量", 表头行, 0) 计算。
  4. 组合:MATCH先定「返回哪列」→VLOOKUP按行取值。
  5. 好处:插入列后只要表头名不变就不用改公式。
近似匹配(需升序) =INDEX(C:C,MATCH(A2,B:B,1)) 点击展开拆解讲解

作用:在升序B列找≤A2的最大档,返回C。

拆解讲解(由外到内 / 从左到右)
  1. INDEX 取值。
  2. MATCH(...,1):近似匹配,找≤查找值的最大值。
  3. 前提:B列必须升序。
  4. 组合:MATCH定档→INDEX返回档对应值。
  5. 场景:税率表、分数等级区间。
二维交叉查找 =INDEX(Sheet2!$C$2:$G$20,MATCH(A2,Sheet2!$A$2:$A$20,0),MATCH(B2,Sheet2!$C$1:$G$1,0)) 点击展开拆解讲解

作用:行条件+列条件交叉取矩阵值。

拆解讲解(由外到内 / 从左到右)
  1. INDEX(矩阵, 行号, 列号):二维取值。
  2. 矩阵 $C$2:$G$20:数值区。
  3. 行号 MATCH(A2, 左侧行标题, 0)。
  4. 列号 MATCH(B2, 上方列标题, 0)。
  5. 组合:先定行再定列,INDEX取交叉点。
XLOOKUP 多条件布尔 =XLOOKUP(1,(A:A=E2)*(B:B=F2),C:C,"无") 点击展开拆解讲解

作用:在0/1条件数组里找1,返回对应C。

拆解讲解(由外到内 / 从左到右)
  1. XLOOKUP(1, 条件数组, C, "无")。
  2. (A=E2)*(B=F2):双条件为真处为1。
  3. 组合:布尔相乘生成查找数组→XLOOKUP找1→返回C。
  4. 优点:不必拼文本,无分隔符冲突问题。
FILTER 多条件返回列 =FILTER(C2:C100,(A2:A100=E2)*(B2:B100=F2),"无") 点击展开拆解讲解

作用:返回所有满足双条件的C值;没有则「无」。

拆解讲解(由外到内 / 从左到右)
  1. FILTER(返回区, 条件, [若空])。
  2. 返回区 C2:C100。
  3. 条件 (A=E2)*(B=F2):且;或条件改用+。
  4. 若空「无」:避免#CALC!。
  5. 只要一条可再套 INDEX(...,1) 或 TAKE(...,1)。
条件判断(12)
嵌套 IF 等级 =IF(A2>=90,"优",IF(A2>=80,"良",IF(A2>=60,"及格","不及格"))) 点击展开拆解讲解

作用:按分数从高到低输出优/良/及格/不及格。

拆解讲解(由外到内 / 从左到右)
  1. 最外层 IF(A2>=90,"优",否则…):先判最高档。
  2. 第二层 IF(A2>=80,"良",否则…):未达优再判良。
  3. 第三层 IF(A2>=60,"及格","不及格"):最后分及格与否。
  4. 组合:每层假分支里继续 IF,形成决策树。
  5. 要点:条件必须从严到宽(90→80→60),顺序反了会错档。
IFS 等级 =IFS(A2>=90,"优",A2>=80,"良",A2>=60,"及格",TRUE,"不及格") 点击展开拆解讲解

作用:IFS 扁平多分支,等同嵌套 IF。

拆解讲解(由外到内 / 从左到右)
  1. IFS(条件1,结果1, 条件2,结果2, …):按顺序判断,命中即停。
  2. 前三对:>=90/80/60 → 优/良/及格。
  3. TRUE,"不及格":TRUE 恒成立,充当默认 else。
  4. 组合:无需深层括号;仍要从高到低写条件。
AND 双条件 =IF(AND(A2>0,B2>0),"有效","无效") 点击展开拆解讲解

作用:两个值都大于0才有效。

拆解讲解(由外到内 / 从左到右)
  1. 外层 IF:把逻辑翻译成文案。
  2. AND(...):全部为真才 TRUE。
  3. A2>0 与 B2>0:两个具体条件。
  4. 组合:AND 合并条件 → IF 输出业务文字。
OR 缺项检查 =IF(OR(A2="",B2=""),"缺项","完整") 点击展开拆解讲解

作用:任一关键字段为空就提示缺项。

拆解讲解(由外到内 / 从左到右)
  1. OR(...):任一为真即 TRUE。
  2. A2=""、B2="":是否空文本。
  3. 外层 IF:缺项/完整。
  4. 组合:OR 侦测缺项 → IF 提示。
非空判断 =IF(NOT(ISBLANK(A2)),"有值","空") 点击展开拆解讲解

作用:判断单元格是否空白。

拆解讲解(由外到内 / 从左到右)
  1. ISBLANK(A2):完全空白为 TRUE。
  2. NOT(...):取反。
  3. 外层 IF:有值/空。
  4. 坑:公式返回""时看起来空,但 ISBLANK 为 FALSE。
数字才运算 =IF(ISNUMBER(A2),A2*2,"非数字") 点击展开拆解讲解

作用:确认是数字后再计算。

拆解讲解(由外到内 / 从左到右)
  1. ISNUMBER(A2):类型检测。
  2. 真分支 A2*2:通过才运算。
  3. 假分支「非数字」:拦截文本参与计算。
  4. 组合:先判类型,再做业务,避免 #VALUE!。
重复标记 =IF(COUNTIF(A:A,A2)>1,"重复","唯一") 点击展开拆解讲解

作用:按出现次数标记重复或唯一。

拆解讲解(由外到内 / 从左到右)
  1. COUNTIF(A:A,A2):统计出现次数。
  2. 比较 >1:说明还有其它相同值。
  3. 外层 IF:重复/唯一。
  4. 组合:计数→比较→标文案;可配条件格式。
SWITCH 映射 =SWITCH(A2,"A","一类","B","二类","其他") 点击展开拆解讲解

作用:按离散取值做字典映射。

拆解讲解(由外到内 / 从左到右)
  1. SWITCH(表达式, 值1,结果1, …, [默认])。
  2. A2 与 "A"/"B" 精确比对。
  3. 末项「其他」为默认。
  4. 组合:单层映射,替代一串 IF(A2="A",…)。
防除零 =IFERROR(IF(A2/B2>1,"超标","正常"),"除零") 点击展开拆解讲解

作用:先做比值判断;除零时报「除零」。

拆解讲解(由外到内 / 从左到右)
  1. 内层 A2/B2:求比值。
  2. 内层 IF(比值>1,"超标","正常"):业务判断。
  3. 外层 IFERROR(...,"除零"):捕获 #DIV/0!。
  4. 组合:先跑完整逻辑,任何报错译成「除零」。
多列齐全 =IF(AND(A2<>"",B2<>"",C2<>""),"齐全","不全") 点击展开拆解讲解

作用:三列都非空才齐全。

拆解讲解(由外到内 / 从左到右)
  1. <>"":不等于空文本。
  2. AND 连接三个非空条件。
  3. 外层 IF:齐全/不全。
  4. 组合:逐列检查→AND→IF。
LET 简化嵌套 =LET(s,A2,IF(s>=90,"优",IF(s>=60,"及格","不及格"))) 点击展开拆解讲解

作用:先命名中间量再判断。

拆解讲解(由外到内 / 从左到右)
  1. LET(名称,值, …, 最终计算):公式内变量。
  2. s,A2:把 A2 命名为 s。
  3. 最终嵌套 IF 使用 s 做等级判断。
  4. 组合:先赋值再使用,减少重复、提高可读性。
OR 数组名单 =IF(OR(A2={"甲","乙","丙"}),"名单内","名单外") 点击展开拆解讲解

作用:判断是否属于给定名单。

拆解讲解(由外到内 / 从左到右)
  1. 常量数组 {"甲","乙","丙"}:名单。
  2. A2={...}:得到一组 TRUE/FALSE。
  3. OR:任一为真即在名单内。
  4. 替代:COUNTIF({"甲","乙","丙"},A2) 往往更稳。
求和统计+条件(16)
双条件求和 =SUMIFS(C:C,A:A,E2,B:B,F2) 点击展开拆解讲解

作用:A=E2 且 B=F2 时对 C 求和。

拆解讲解(由外到内 / 从左到右)
  1. SUMIFS(求和区, 条件区1,条件1, 条件区2,条件2, …)。
  2. 第一参数 C:C:被求和的金额列。
  3. A:A,E2:第一条件。
  4. B:B,F2:第二条件。
  5. 组合:条件成对追加;全部满足的行才进入合计。
  6. 对比 SUMIF:SUMIF 只能一个条件。
SUMPRODUCT 双条件 =SUMPRODUCT((A:A=E2)*(B:B=F2)*C:C) 点击展开拆解讲解

作用:用数组相乘实现双条件求和(兼容老版本)。

拆解讲解(由外到内 / 从左到右)
  1. (A=E2)、(B=F2):条件转 0/1。
  2. 再 *C:C:只让满足条件的金额留下。
  3. SUMPRODUCT:把乘积数组加总。
  4. 组合:条件×条件×金额→求和。
  5. 务必改用有限区域,整列会很慢。
SUMPRODUCT 计数 =SUMPRODUCT((A2:A100=E2)*(B2:B100=F2)) 点击展开拆解讲解

作用:不加金额列时,等价于多条件计数。

拆解讲解(由外到内 / 从左到右)
  1. 两条件相乘得到 0/1。
  2. SUMPRODUCT 对 0/1 求和=符合条件的行数。
  3. 组合:效果类似 COUNTIFS。
多值 OR 求和 =SUM(SUMIFS(C:C,A:A,{"甲","乙","丙"})) 点击展开拆解讲解

作用:A 为甲或乙或丙时对 C 求和。

拆解讲解(由外到内 / 从左到右)
  1. SUMIFS 的条件写成数组 {"甲","乙","丙"}。
  2. 此时 SUMIFS 返回多个结果(每个值一个和)。
  3. 外层 SUM:把多结果合并成总和。
  4. 组合:内层按值分别汇总,外层再加总,实现 OR。
条件计数 =COUNTIFS(A:A,E2,B:B,">"&F2) 点击展开拆解讲解

作用:统计 A=E2 且 B>F2 的行数。

拆解讲解(由外到内 / 从左到右)
  1. COUNTIFS(条件区1,条件1, …)。
  2. A:A,E2:等于条件。
  3. B:B,">"&F2:把比较符与单元格拼成条件文本。
  4. 组合:多条件计数;数值比较必须拼接 ">"。
非空计数 =COUNTIFS(A:A,E2,B:B,"<>") 点击展开拆解讲解

作用:在 A=E2 前提下统计 B 非空。

拆解讲解(由外到内 / 从左到右)
  1. 第一条件 A=E2。
  2. 第二条件 "<>":不等于空。
  3. 组合:筛选子集后再数非空。
多条件平均 =AVERAGEIFS(C:C,A:A,E2,B:B,F2) 点击展开拆解讲解

作用:双条件下对 C 求平均。

拆解讲解(由外到内 / 从左到右)
  1. AVERAGEIFS 参数顺序同 SUMIFS:平均区在前,条件成对在后。
  2. 组合:先按条件过滤行,再对 C 平均。
条件最大 =MAXIFS(C:C,A:A,E2) 点击展开拆解讲解

作用:A=E2 的子集里取 C 最大。

拆解讲解(由外到内 / 从左到右)
  1. MAXIFS(最大区, 条件区, 条件, …)。
  2. 可继续追加条件对。
  3. 需 Excel 2019+/365。
条件最小 =MINIFS(C:C,A:A,E2) 点击展开拆解讲解

作用:A=E2 的子集里取 C 最小。

拆解讲解(由外到内 / 从左到右)
  1. 结构同 MAXIFS,聚合方式改为最小。
  2. 组合:条件过滤→取最小。
旧环境条件最大 =SUMPRODUCT(MAX((A:A=E2)*C:C)) 点击展开拆解讲解

作用:无 MAXIFS 时用数组取条件最大。

拆解讲解(由外到内 / 从左到右)
  1. (A=E2)*C:不满足条件的位置变0。
  2. MAX(...):在结果中取最大(即条件行里的最大金额)。
  3. 外层 SUMPRODUCT:在部分环境帮助正确计算数组。
  4. 组合:掩码后取 MAX,兼容旧版。
数组 IF 求和 =SUM(IF(A2:A100=E2,C2:C100,0)) 点击展开拆解讲解

作用:传统数组:条件成立取 C,否则0,再求和。

拆解讲解(由外到内 / 从左到右)
  1. IF(条件, C, 0):逐行决定取金额还是0。
  2. SUM:把数组加总。
  3. 组合:IF 造「保留/清零」数组→SUM。
  4. 365 可直接回车;旧版常需 CSE。
模糊包含求和 =SUMPRODUCT(ISNUMBER(FIND(E2,A2:A100))*C2:C100) 点击展开拆解讲解

作用:A 包含关键字 E2 时对 C 求和。

拆解讲解(由外到内 / 从左到右)
  1. FIND(E2,A):找到返回位置数字,找不到报错。
  2. ISNUMBER(...):找到→TRUE,找不到→FALSE。
  3. 再 *C:把真行的金额留下。
  4. SUMPRODUCT 求和。
  5. 组合:模糊定位→布尔化→乘金额→汇总。
年度区间求和 =SUMIFS(C:C,A:A,">="&DATE(2024,1,1),A:A,"<="&DATE(2024,12,31)) 点击展开拆解讲解

作用:日期在2024年内的 C 合计。

拆解讲解(由外到内 / 从左到右)
  1. 同一日期列 A 写两个条件,构成闭区间。
  2. DATE(2024,1,1)/DATE(2024,12,31):构造标准日期。
  3. ">="& 与 "<="&:比较符拼接。
  4. 组合:下界+上界两个条件夹住全年。
FILTER+SUM =SUM(FILTER(C2:C100,B2:B100=E2)) 点击展开拆解讲解

作用:先筛选符合条件的金额,再求和。

拆解讲解(由外到内 / 从左到右)
  1. 内层 FILTER(C, B=E2):取出满足条件的金额数组。
  2. 外层 SUM:对数组求和。
  3. 组合:筛选集合→聚合;比 SUMPRODUCT 更易读。
  4. 无匹配时 FILTER 可能报错,可加第3参数或 IFERROR。
前缀条件求和 =SUMPRODUCT((LEFT(A2:A100,2)="华北")*C2:C100) 点击展开拆解讲解

作用:A 前两字为「华北」时对 C 求和。

拆解讲解(由外到内 / 从左到右)
  1. LEFT(A,2):取前缀。
  2. ="华北":前缀条件,得 0/1。
  3. *C 后 SUMPRODUCT:条件求和。
  4. 组合:文本截取→条件化→乘金额→汇总。
AGGREGATE 忽略错误条件求和 =AGGREGATE(9,6,C2:C100/(A2:A100=E2)) 点击展开拆解讲解

作用:用除法制造错误并忽略,实现对真值行求和。

拆解讲解(由外到内 / 从左到右)
  1. AGGREGATE(函数号, 选项, 数组)。
  2. 函数号 9 = SUM。
  3. 选项 6 = 忽略错误值。
  4. C/(A=E2):条件真时÷1保留原值;假时÷0产生错误。
  5. 组合:错误当「过滤器」,AGGREGATE 求和时跳过错误行。
文本+查找(18)
取第1段 =TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)),1,99)) 点击展开拆解讲解

作用:按「-」分段,取出第1段(兼容老 Excel)。

拆解讲解(由外到内 / 从左到右)
  1. 最内层 REPT(" ",99):造出99个空格。
  2. SUBSTITUTE(A2,"-", 那串空格):把每个分隔符换成超长空格,把各段「撑开」。
  3. MID(...,1,99):从第1个字符起取99宽,正好盖住第1段。
  4. TRIM(...):去掉多余空格,留下纯文本。
  5. 组合:替换撑开→定宽截取→修剪。第n段起点=(n-1)*99+1。
取第2段 =TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)),99,99)) 点击展开拆解讲解

作用:同上技巧取第2段。

拆解讲解(由外到内 / 从左到右)
  1. SUBSTITUTE+REPT:同上,把「-」换成99空格。
  2. MID 起点改为99:窗口移到第2段。
  3. TRIM 去空格。
  4. 组合:与取第1段同一套方法,只改截取起点。
条件拼接 =TEXTJOIN(",",TRUE,IF(A2:A10=E2,B2:B10,"")) 点击展开拆解讲解

作用:把满足条件的 B 值用逗号拼成一串。

拆解讲解(由外到内 / 从左到右)
  1. IF(A=E2,B,""):满足条件保留 B,否则变空。
  2. TEXTJOIN(",", TRUE, 数组):用逗号连接;TRUE=忽略空值。
  3. 组合:先用 IF 过滤出要保留的项,再用 TEXTJOIN 合并。
  4. 需 2019+/365;旧版常需数组输入。
取连字符前 =IFERROR(LEFT(A2,FIND("-",A2)-1),A2) 点击展开拆解讲解

作用:取第一个「-」前面的内容;没有则返回原文。

拆解讲解(由外到内 / 从左到右)
  1. FIND("-",A2):定位「-」位置(区分大小写)。
  2. FIND(...)-1:前一段的长度。
  3. LEFT(A2, 长度):截取前段。
  4. IFERROR(...,A2):没有「-」时 FIND 报错,退回原文。
  5. 组合:定位→算长度→截取→错误兜底。
取连字符后 =IFERROR(MID(A2,FIND("-",A2)+1,LEN(A2)),"") 点击展开拆解讲解

作用:取第一个「-」之后全部内容。

拆解讲解(由外到内 / 从左到右)
  1. FIND 定位「-」。
  2. 起点 FIND+1:从分隔符后一位开始。
  3. MID(...,起点,LEN(A2)):一直取到足够长。
  4. IFERROR→"":无分隔符返回空。
  5. 组合:定位→后移一位→MID 截尾段。
去全部空格 =SUBSTITUTE(TRIM(A2)," ","") 点击展开拆解讲解

作用:清除首尾多余空格及中间空格。

拆解讲解(由外到内 / 从左到右)
  1. TRIM(A2):去首尾空格,并把连续中间空格压成单个。
  2. SUBSTITUTE(...," ",""):再把剩余空格全部删掉。
  3. 组合:先规范空白,再彻底清除。
日期格式文本 =TEXT(A2,"yyyy-mm-dd") 点击展开拆解讲解

作用:把日期序列号格式化成文本。

拆解讲解(由外到内 / 从左到右)
  1. TEXT(值, 格式码):按格式输出文本。
  2. yyyy-mm-dd:年-月-日。
  3. 注意:结果是文本,不宜再直接做部分日期运算。
百分比文本 =TEXT(A2,"0.00%") 点击展开拆解讲解

作用:把小数显示成百分比文本。

拆解讲解(由外到内 / 从左到右)
  1. 格式码 0.00%:两位小数百分比。
  2. 0.85 → "85.00%"。
  3. 展示用;计算请保留原数值。
千分位文本 =TEXT(A2,"#,##0.00") 点击展开拆解讲解

作用:金额千分位+两位小数的文本。

拆解讲解(由外到内 / 从左到右)
  1. #,##0.00:千分位与两位小数。
  2. 常用于拼接报表字符串。
首字母大写其余小写 =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) 点击展开拆解讲解

作用:英文大小写规范化。

拆解讲解(由外到内 / 从左到右)
  1. LEFT(A2,1):首字符。
  2. UPPER(...):首字符转大写。
  3. MID(A2,2,LEN(A2)):其余字符。
  4. LOWER(...):其余转小写。
  5. &:拼接。
  6. 组合:拆两段分别处理大小写再拼回。
TEXTBEFORE =IFERROR(TEXTBEFORE(A2,"-"),A2) 点击展开拆解讲解

作用:新函数直接取分隔符前;无则原文。

拆解讲解(由外到内 / 从左到右)
  1. TEXTBEFORE(文本, 分隔符):取前段。
  2. IFERROR(...,A2):无分隔符时退回原文。
  3. 对比旧写法:等价于 IFERROR(LEFT(...,FIND-1),A2),更短。
TEXTAFTER =IFERROR(TEXTAFTER(A2,"-"),"") 点击展开拆解讲解

作用:取分隔符后;无则空。

拆解讲解(由外到内 / 从左到右)
  1. TEXTAFTER(文本, 分隔符):取后段。
  2. 可指定第几次出现、是否从末尾数。
  3. IFERROR 处理无分隔符。
拆分数组 =TEXTSPLIT(A2,"-") 点击展开拆解讲解

作用:按「-」拆成横向溢出数组。

拆解讲解(由外到内 / 从左到右)
  1. TEXTSPLIT(文本, 列分隔符):拆成多列。
  2. 结果自动溢出到右侧单元格。
  3. 可再 INDEX/CHOOSECOLS 取某一段。
取中间段 =IFERROR(TEXTBEFORE(TEXTAFTER(A2,"-"),"-"),"") 点击展开拆解讲解

作用:取被两个「-」夹住的中间段。

拆解讲解(由外到内 / 从左到右)
  1. 内层 TEXTAFTER(A2,"-"):先丢掉第1段,剩下「中段-末段…」。
  2. 外层 TEXTBEFORE(...,"-"):再取到下一个「-」之前,即中段。
  3. IFERROR:段数不够时返回空。
  4. 组合:先削掉左边,再截到下一个分隔符。
取拆分第2项 =INDEX(TEXTSPLIT(A2,","),1,2) 点击展开拆解讲解

作用:逗号拆分后取第2段。

拆解讲解(由外到内 / 从左到右)
  1. TEXTSPLIT(A2,","):得到一行多列数组。
  2. INDEX(数组,1,2):取第1行第2列。
  3. 组合:先拆后取位。
多重清理 =SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","") 点击展开拆解讲解

作用:依次去掉空格和横线。

拆解讲解(由外到内 / 从左到右)
  1. 内层 SUBSTITUTE 去空格。
  2. 外层 SUBSTITUTE 去横线。
  3. 组合:由内而外层层清洗;要去更多字符就继续外套。
去掉末字符 =LEFT(A2,LEN(A2)-1) 点击展开拆解讲解

作用:删除最后一个字符。

拆解讲解(由外到内 / 从左到右)
  1. LEN(A2)-1:新长度。
  2. LEFT(...,新长度):截到倒数第二位。
  3. 场景:去掉末尾逗号、多余单位等。
正则提取数字(新版) =REGEXEXTRACT(A2,"[0-9]+") 点击展开拆解讲解

作用:用正则取出连续数字。

拆解讲解(由外到内 / 从左到右)
  1. REGEXEXTRACT(文本, 模式):按正则提取。
  2. [0-9]+:一段或多位数字。
  3. 需较新 Microsoft 365;旧版用查找/辅助列。
日期+条件(14)
相对今天 =IF(A2>TODAY(),"未来",IF(A2=TODAY(),"今天","过去")) 点击展开拆解讲解

作用:相对系统今天,分成未来/今天/过去。

拆解讲解(由外到内 / 从左到右)
  1. TODAY():今天的日期(无时间)。
  2. 外层 IF(A2>TODAY(),"未来",…):先判未来。
  3. 内层 IF(A2=TODAY(),"今天","过去")。
  4. 组合:与今天比较的三分支决策树。
是否本年 =IF(AND(A2>=DATE(2024,1,1),A2<=DATE(2024,12,31)),"本年","其他") 点击展开拆解讲解

作用:判断是否落在2024年闭区间。

拆解讲解(由外到内 / 从左到右)
  1. DATE(年,月,日):构造标准日期。
  2. A2>=年初 与 A2<=年末:上下界。
  3. AND:两界同时成立。
  4. IF:本年/其他。
  5. 可把2024改成 YEAR(TODAY()) 动态化。
工作日差(不含起) =NETWORKDAYS(A2,B2)-1 点击展开拆解讲解

作用:两日之间工作日间隔(常见不含起始日算法)。

拆解讲解(由外到内 / 从左到右)
  1. NETWORKDAYS(开始,结束,[假期]):含起止的工作日数。
  2. 减1:得到更接近「间隔天数」的口径。
  3. 可加假期列表参数排除法定假。
10个工作日后 =WORKDAY(A2,10) 点击展开拆解讲解

作用:从A2起向后推10个工作日。

拆解讲解(由外到内 / 从左到右)
  1. WORKDAY(开始, 天数, [假期])。
  2. 天数10:向后10个工作日(负数为向前)。
  3. 场景:交期、SLA。
距月末天数 =EOMONTH(A2,0)-A2 点击展开拆解讲解

作用:距离当月最后一天还剩几天。

拆解讲解(由外到内 / 从左到右)
  1. EOMONTH(A2,0):当月最后一天。
  2. 减 A2:日期相减得天数。
  3. 组合:先取月末,再算差额。
周岁 =DATEDIF(A2,TODAY(),"Y") 点击展开拆解讲解

作用:按完整年计算年龄。

拆解讲解(由外到内 / 从左到右)
  1. DATEDIF(开始,结束,单位)。
  2. 单位 "Y":完整年数。
  3. 结束用 TODAY():算到今天。
  4. 还可改 M/D/YM/MD/YD。
周末判断 =IF(WEEKDAY(A2,2)>5,"周末","工作日") 点击展开拆解讲解

作用:按周一=1…周日=7判断周末。

拆解讲解(由外到内 / 从左到右)
  1. WEEKDAY(日期,2):返回类型2。
  2. >5:6、7 即周六日。
  3. 外层 IF:周末/工作日。
中文日期 =TEXT(A2,"yyyy年m月d日") 点击展开拆解讲解

作用:输出中文日期文本。

拆解讲解(由外到内 / 从左到右)
  1. TEXT+中文格式码。
  2. 结果为文本。
日期区间求和 =SUMIFS(C:C,A:A,">="&E2,A:A,"<="&F2) 点击展开拆解讲解

作用:A 落在 E2~F2 时对 C 求和。

拆解讲解(由外到内 / 从左到右)
  1. 同一日期列写两个条件。
  2. ">="&E2、"<="&F2:拼接比较符。
  3. 组合:闭区间过滤后求和。
按月汇总 =SUMPRODUCT((MONTH(A2:A100)=E2)*(C2:C100)) 点击展开拆解讲解

作用:月份等于E2的金额合计。

拆解讲解(由外到内 / 从左到右)
  1. MONTH(日期列)=E2:月条件 0/1。
  2. *C 后 SUMPRODUCT。
  3. 注意:不同年份同月会混在一起,需时加 YEAR。
年月双条件 =SUMPRODUCT((YEAR(A2:A100)=2024)*(MONTH(A2:A100)=E2)*C2:C100) 点击展开拆解讲解

作用:指定年+月双条件汇总。

拆解讲解(由外到内 / 从左到右)
  1. YEAR=2024 与 MONTH=E2 两个 0/1 条件。
  2. 再 *C 求和。
  3. 组合:年、月同时约束,避免跨年混淆。
三个月后同日 =EDATE(A2,3) 点击展开拆解讲解

作用:日期加三个月。

拆解讲解(由外到内 / 从左到右)
  1. EDATE(开始, 月数):加/减整月。
  2. 月数3:向后三个月;负数向前。
  3. 月末日期会按目标月最后一天调整。
本月1号 =EOMONTH(TODAY(),-1)+1 点击展开拆解讲解

作用:计算本月第一天。

拆解讲解(由外到内 / 从左到右)
  1. EOMONTH(TODAY(),-1):上月最后一天。
  2. +1:下一天即本月1号。
  3. 组合:回上月末再进一天。
生成月份序列 =TEXT(DATE(2024,1,1)+ROW()-2,"yyyy-mm") 点击展开拆解讲解

作用:向下填充生成日期文本序列(按天递增再格式化)。

拆解讲解(由外到内 / 从左到右)
  1. DATE(2024,1,1):起点。
  2. ROW()-2:随行号产生0,1,2…偏移(放在第2行时)。
  3. 相加得到连续日期。
  4. TEXT(...,"yyyy-mm"):显示成年月。
  5. 若要每月一条,应改用 EDATE 思路,而不是 +1 天。
排名/去重/筛选(14)
排名 =RANK(A2,$A$2:$A$100) 点击展开拆解讲解

作用:返回 A2 在区域中的名次(默认降序)。

拆解讲解(由外到内 / 从左到右)
  1. RANK(数值, 引用, [顺序])。
  2. 区域用绝对引用 $A$2:$A$100:下拉时范围不动。
  3. 默认顺序0:越大名次越靠前。
  4. 并列同名次;新版建议 RANK.EQ / RANK.AVG。
无并列跳号排名 =SUMPRODUCT((A2:A100>A2)/COUNTIF(A2:A100,A2:A100))+1 点击展开拆解讲解

作用:按「有多少更大值」计算的一种排名写法。

拆解讲解(由外到内 / 从左到右)
  1. (A2:A100>A2):有多少个值比当前大(TRUE/FALSE)。
  2. COUNTIF(A2:A100,A2:A100):每个值的出现次数数组。
  3. 相除再 SUMPRODUCT:处理并列权重后的计数技巧。
  4. +1:把「更大个数」转成名次。
  5. 区域勿用整列。
出现次序 =COUNTIF($A$2:A2,A2) 点击展开拆解讲解

作用:统计到当前行为止出现了几次。

拆解讲解(由外到内 / 从左到右)
  1. 区域 $A$2:A2:顶端绝对、底端相对,下拉时扩展。
  2. COUNTIF(...,A2):数当前值出现次数。
  3. 第一次=1,第二次=2…
首次/重复 =IF(COUNTIF($A$2:A2,A2)=1,"首次","重复") 点击展开拆解讲解

作用:标记该值是否第一次出现。

拆解讲解(由外到内 / 从左到右)
  1. COUNTIF($A$2:A2,A2):累计出现次数。
  2. =1 判首次,否则重复。
  3. 组合:扩展计数 + IF 文案;常用于保留首条。
传统去重下拉 =INDEX(A:A,MATCH(0,COUNTIF($D$1:D1,A$2:A$100),0)) 点击展开拆解讲解

作用:在辅助列向下拖,依次取出还未出现过的唯一值。

拆解讲解(由外到内 / 从左到右)
  1. COUNTIF($D$1:D1, 源列表):看源值是否已出现在上方结果中。
  2. MATCH(0, 上述数组, 0):找第一个「尚未出现」(计数为0)的位置。
  3. INDEX(A:A, 位置):取出该唯一值。
  4. 组合:用已输出结果当「黑名单」,不断找下一个新值。
  5. 旧版无 UNIQUE 时的经典解,通常需数组环境。
条件去重 =UNIQUE(FILTER(A2:A100,B2:B100=E2)) 点击展开拆解讲解

作用:先按条件筛选,再去重。

拆解讲解(由外到内 / 从左到右)
  1. 内层 FILTER(A, B=E2):取出符合条件的 A。
  2. 外层 UNIQUE:去掉重复项。
  3. 组合:筛选集合→去重列表。
去重后排序 =SORT(UNIQUE(FILTER(A2:A100,B2:B100=E2)),1,1) 点击展开拆解讲解

作用:条件去重后再升序。

拆解讲解(由外到内 / 从左到右)
  1. FILTER→UNIQUE:得到不重复集合。
  2. SORT(...,1,1):按第1列升序(1;降序用-1)。
  3. 组合:筛→去重→排序三层。
多条件筛选 =FILTER(A2:D100,(B2:B100=E2)*(C2:C100>F2)) 点击展开拆解讲解

作用:返回同时满足两条件的整行。

拆解讲解(由外到内 / 从左到右)
  1. FILTER(返回区, 条件)。
  2. 返回区 A2:D100:整行保留。
  3. (B=E2)*(C>F2):且条件;或用 +。
  4. 组合:布尔掩码决定保留哪些行。
筛选后按列降序 =SORTBY(FILTER(A2:D100,B2:B100=E2),C2:C100,-1) 点击展开拆解讲解

作用:先筛选再按 C 降序。

拆解讲解(由外到内 / 从左到右)
  1. FILTER:先得到子集。
  2. SORTBY(数组, 依据, -1):按 C 列降序。
  3. 依据可用与原表对齐的列。
  4. 组合:筛→排序。
条件 Top5 =TAKE(SORT(FILTER(A2:D100,B2:B100=E2),3,-1),5) 点击展开拆解讲解

作用:条件筛选后按第3列降序取前5行。

拆解讲解(由外到内 / 从左到右)
  1. 最内 FILTER:条件过滤。
  2. SORT(...,3,-1):按第3列降序。
  3. TAKE(...,5):只取前5行。
  4. 组合:FILTER→SORT→TAKE,条件排行榜三件套。
传统条件提取 =IFERROR(INDEX(A:A,SMALL(IF(B2:B100=E2,ROW(B2:B100)),ROW(A1))),"") 点击展开拆解讲解

作用:向下拖依次列出所有 B=E2 对应的 A。

拆解讲解(由外到内 / 从左到右)
  1. IF(B=E2, ROW(...)):符合条件的行留下行号,否则 FALSE。
  2. SMALL(行号数组, k):取第 k 小行号;k 用 ROW(A1) 下拉变1,2,3…
  3. INDEX(A:A, 行号):取值。
  4. IFERROR:取完后显示空。
  5. 组合:收集行号→按序取出→INDEX 返回。
是否前三 =IF(A2>=LARGE($A$2:$A$100,3),"前三","其他") 点击展开拆解讲解

作用:判断是否达到第3高分及以上。

拆解讲解(由外到内 / 从左到右)
  1. LARGE(区域,3):第3大值(分数线)。
  2. A2>=该值:是否达标。
  3. IF:前三/其他。
  4. 并列时可能超过3个人都标前三。
按第N大取值 =INDEX(A:A,MATCH(LARGE(B:B,ROW(A1)),B:B,0)) 点击展开拆解讲解

作用:向下拖依次取第1大、第2大…对应的A。

拆解讲解(由外到内 / 从左到右)
  1. LARGE(B:B, ROW(A1)):第k大的 B 值。
  2. MATCH(...,B:B,0):找到该值首次出现行。
  3. INDEX(A:A,...):取对应 A。
  4. 注意:B 有并列时可能反复命中同一行。
GROUPBY 分组求和(新版) =GROUPBY(A2:A100,C2:C100,SUM) 点击展开拆解讲解

作用:按 A 分组对 C 求和,一次出汇总表。

拆解讲解(由外到内 / 从左到右)
  1. GROUPBY(行字段, 值, 聚合函数, …)。
  2. 行字段 A:分组依据。
  3. 值 C + SUM:每组求和。
  4. 需支持 GROUPBY 的新版 Excel;可替代部分透视表。
取整/错误处理(14)
相除保留2位 =ROUND(A2/B2,2) 点击展开拆解讲解

作用:先相除再四舍五入到2位。

拆解讲解(由外到内 / 从左到右)
  1. 内层 A2/B2:先算商。
  2. 外层 ROUND(值,2):四舍五入到2位小数。
  3. 组合:计算在内,取整展示在外。
向上取整 =ROUNDUP(A2/B2,0) 点击展开拆解讲解

作用:相除后向绝对值增大方向取整。

拆解讲解(由外到内 / 从左到右)
  1. A2/B2:先除。
  2. ROUNDUP(...,0):向上取到整数。
  3. 场景:计费「不足1按1」。
舍入到5倍数 =MROUND(A2,5) 点击展开拆解讲解

作用:舍入到最接近的5的倍数。

拆解讲解(由外到内 / 从左到右)
  1. MROUND(数值, 倍数)。
  2. 倍数5:对齐到5/10/15…
  3. 场景:包装规格、最小订购单位。
向上到0.5 =CEILING(A2,0.5) 点击展开拆解讲解

作用:向上对齐到0.5步长。

拆解讲解(由外到内 / 从左到右)
  1. CEILING(数值, 显著性)。
  2. 显著性0.5:按半元进位等。
  3. 新版也可用 CEILING.MATH。
向下到0.5 =FLOOR(A2,0.5) 点击展开拆解讲解

作用:向下对齐到0.5步长。

拆解讲解(由外到内 / 从左到右)
  1. FLOOR(数值, 显著性)。
  2. 与 CEILING 成对:一个进上、一个留下。
奇偶 =IF(MOD(A2,2)=0,"偶","奇") 点击展开拆解讲解

作用:对2取余判断奇偶。

拆解讲解(由外到内 / 从左到右)
  1. MOD(A2,2):除以2的余数。
  2. =0 为偶数。
  3. IF 输出偶/奇。
  4. 也可用于隔行、轮询分组。
向下到十位 =INT(A2/10)*10 点击展开拆解讲解

作用:抹去个位,如128→120。

拆解讲解(由外到内 / 从左到右)
  1. A2/10:缩小一位。
  2. INT(...):向下取整。
  3. *10:放大回去。
  4. 组合:缩放→取整→还原。
条件求和再取整 =ROUND(SUMIFS(C:C,A:A,E2),2) 点击展开拆解讲解

作用:先汇总再统一保留2位,减少尾差。

拆解讲解(由外到内 / 从左到右)
  1. 内层 SUMIFS:按条件求和。
  2. 外层 ROUND(...,2):对合计四舍五入。
  3. 组合:先聚合后取整,避免逐行取整再加的误差。
除法容错 =IFERROR(ROUND(A2/B2,2),0) 点击展开拆解讲解

作用:除法取整;出错返回0。

拆解讲解(由外到内 / 从左到右)
  1. 内层 ROUND(A2/B2,2):正常路径。
  2. 外层 IFERROR(...,0):除零/非数字时返回0。
  3. 组合:业务计算外包错误网。
查找容错 =IFERROR(VLOOKUP(A2,B:C,2,0),"") 点击展开拆解讲解

作用:查找失败显示空白。

拆解讲解(由外到内 / 从左到右)
  1. VLOOKUP 精确查找。
  2. IFERROR 把 #N/A 等换成 ""。
  3. 组合:查找+友好空值。
仅吞 #N/A =IFNA(VLOOKUP(A2,B:C,2,0),"") 点击展开拆解讲解

作用:只处理找不到,其它错误仍暴露。

拆解讲解(由外到内 / 从左到右)
  1. VLOOKUP 负责查找。
  2. IFNA 只拦截 #N/A。
  3. 对比 IFERROR:排错时更有利,不会掩盖 #REF! 等。
双表兜底 =IFERROR(IFERROR(VLOOKUP(A2,B:C,2,0),VLOOKUP(A2,D:E,2,0)),"") 点击展开拆解讲解

作用:主表找不到再查副表。

拆解讲解(由外到内 / 从左到右)
  1. 内层 IFERROR(主VLOOKUP, 副VLOOKUP):主失败切副表。
  2. 外层 IFERROR(...,""):两表都失败返回空。
  3. 组合:双层兜底形成主/备数据源。
ISERROR 写法 =IF(ISERROR(A2/B2),0,A2/B2) 点击展开拆解讲解

作用:旧式:先判断是否错误再决定返回值。

拆解讲解(由外到内 / 从左到右)
  1. ISERROR(A2/B2):探测是否报错。
  2. 真返回0;假返回 A2/B2。
  3. 注意:错误路径会算两次除法;新版更推荐 IFERROR。
日期文本容错 =IFERROR(DATEVALUE(A2),"") 点击展开拆解讲解

作用:文本转日期;失败返回空。

拆解讲解(由外到内 / 从左到右)
  1. DATEVALUE(文本):转日期序列号。
  2. IFERROR:格式无法识别时返回空,避免 #VALUE! 扩散。
动态数组混合(16)
条件求和 =SUM(FILTER(C2:C100,B2:B100=E2)) 点击展开拆解讲解

作用:筛选符合条件的金额后再求和。

拆解讲解(由外到内 / 从左到右)
  1. 内层 FILTER(C, B=E2):得到金额数组。
  2. 外层 SUM:聚合。
  3. 组合:先得到子集,再汇总。
  4. 无匹配时可给 FILTER 第3参数,或外包 IFERROR。
条件计数 =COUNTA(FILTER(A2:A100,B2:B100=E2)) 点击展开拆解讲解

作用:统计筛选结果个数。

拆解讲解(由外到内 / 从左到右)
  1. FILTER 取出符合条件的 A。
  2. COUNTA 数非空项。
  3. 组合:可见的「筛选集」上再计数,比 COUNTIFS 更直观。
条件平均 =AVERAGE(FILTER(C2:C100,B2:B100=E2)) 点击展开拆解讲解

作用:对筛选出的数值求平均。

拆解讲解(由外到内 / 从左到右)
  1. FILTER 取子集。
  2. AVERAGE 求平均。
  3. 空结果可能报错,注意兜底。
条件拼接名单 =TEXTJOIN(",",TRUE,FILTER(B2:B100,A2:A100=E2)) 点击展开拆解讲解

作用:把符合条件的名单拼成一行。

拆解讲解(由外到内 / 从左到右)
  1. FILTER 取出符合条件的 B(姓名等)。
  2. TEXTJOIN(",",TRUE,...):逗号连接并忽略空。
  3. 组合:筛选→拼接展示。
LET+FILTER =LET(x,FILTER(C2:C100,B2:B100=E2),SUM(x)) 点击展开拆解讲解

作用:筛选结果命名后再汇总,便于复用。

拆解讲解(由外到内 / 从左到右)
  1. LET 定义 x = FILTER(...)。
  2. 最终表达式 SUM(x)。
  3. 若还要 MAX(x)/AVERAGE(x),可在同一 LET 里复用 x,避免重复 FILTER。
LET 命名区域 =LET(a,A2:A100,b,B2:B100,SUM(FILTER(b,a=E2))) 点击展开拆解讲解

作用:给源列起短名,公式更清晰。

拆解讲解(由外到内 / 从左到右)
  1. a,A2:A100 与 b,B2:B100:命名两列。
  2. FILTER(b,a=E2):用短名写条件筛选。
  3. SUM:汇总。
  4. 改区域时只改 LET 赋值处。
LET+XLOOKUP =LET(r,XLOOKUP(A2,B:B,C:C),IF(r>0,r,0)) 点击展开拆解讲解

作用:查找一次存入变量,再做正负判断。

拆解讲解(由外到内 / 从左到右)
  1. r,XLOOKUP(...):查找结果存 r。
  2. IF(r>0,r,0):基于 r 二次判断。
  3. 组合:避免写两遍 XLOOKUP。
MAP 转大写 =MAP(A2:A10,LAMBDA(x,UPPER(x))) 点击展开拆解讲解

作用:对每个元素套函数。

拆解讲解(由外到内 / 从左到右)
  1. LAMBDA(x,UPPER(x)):定义「输入x→大写」的小函数。
  2. MAP(数组, LAMBDA):把小函数应用到每个元素。
  3. 也可直接 UPPER(A2:A10);MAP 适合更复杂逐元逻辑。
按行求和 =BYROW(A2:D10,LAMBDA(r,SUM(r))) 点击展开拆解讲解

作用:每一行传入 LAMBDA 求该行合计。

拆解讲解(由外到内 / 从左到右)
  1. LAMBDA(r,SUM(r)):r 代表一行。
  2. BYROW(区域, LAMBDA):按行迭代。
  3. 结果向下溢出一行一个合计。
按列最大 =BYCOL(A2:D10,LAMBDA(c,MAX(c))) 点击展开拆解讲解

作用:每一列取最大值。

拆解讲解(由外到内 / 从左到右)
  1. LAMBDA(c,MAX(c)):c 代表一列。
  2. BYCOL:按列迭代。
  3. 结果向右溢出。
REDUCE 累加 =REDUCE(0,A2:A10,LAMBDA(a,b,a+b)) 点击展开拆解讲解

作用:从初值0起逐个累加,返回最终和。

拆解讲解(由外到内 / 从左到右)
  1. 初值0。
  2. LAMBDA(a,b,a+b):a=累计值,b=当前元素。
  3. REDUCE:只返回最终一个结果。
  4. 等价于对该列 SUM,但可改成更复杂归约。
SCAN 累计过程 =SCAN(0,A2:A10,LAMBDA(a,b,a+b)) 点击展开拆解讲解

作用:返回每一步累计值,形成累计序列。

拆解讲解(由外到内 / 从左到右)
  1. 参数同 REDUCE。
  2. 区别:SCAN 返回过程数组,不只最终值。
  3. 场景:累计销量、累计余额。
乘法表 =MAKEARRAY(3,3,LAMBDA(r,c,r*c)) 点击展开拆解讲解

作用:生成3×3,单元格=行号×列号。

拆解讲解(由外到内 / 从左到右)
  1. MAKEARRAY(行数,列数,LAMBDA)。
  2. LAMBDA(r,c,r*c):用行列号计算每个格子。
  3. 可造示例矩阵、序号表。
两列去重纵向合并 =VSTACK(UNIQUE(A2:A50),UNIQUE(B2:B50)) 点击展开拆解讲解

作用:两列分别去重后上下拼接。

拆解讲解(由外到内 / 从左到右)
  1. UNIQUE(A)、UNIQUE(B):各自去重。
  2. VSTACK:纵向堆叠。
  3. 若要整体再去重,外再套 UNIQUE。
三列并排 =HSTACK(A2:A10,B2:B10,C2:C10) 点击展开拆解讲解

作用:多列横向拼成一张宽表。

拆解讲解(由外到内 / 从左到右)
  1. HSTACK(数组1,数组2,…)。
  2. 常与 FILTER 结果再拼辅助列。
多列压成一列去空 =TOCOL(A2:C10,1) 点击展开拆解讲解

作用:二维区域压成单列并忽略空白。

拆解讲解(由外到内 / 从左到右)
  1. TOCOL(区域, 忽略模式)。
  2. 忽略模式1:忽略空白。
  3. 场景:把散落多列名单收成一列。
跨表/序列(10)
跨表双键 =IFERROR(INDEX(Sheet2!D:D,MATCH(A2&"|"&B2,Sheet2!A:A&"|"&Sheet2!B:B,0)),"") 点击展开拆解讲解

作用:在 Sheet2 用双键匹配后返回 D。

拆解讲解(由外到内 / 从左到右)
  1. A2&"|"&B2:当前表拼复合键。
  2. Sheet2!A&"|"&Sheet2!B:对方同样拼键。
  3. MATCH(...,0):精确找行号。
  4. INDEX(Sheet2!D:D, 行号):取值。
  5. IFERROR:失败返回空。
  6. 分隔符选数据中不会出现的字符。
跨表条件求和 =SUMPRODUCT((Sheet2!A2:A100=A2)*(Sheet2!B2:B100=B2)*Sheet2!C2:C100) 点击展开拆解讲解

作用:按当前行条件对 Sheet2 金额汇总。

拆解讲解(由外到内 / 从左到右)
  1. Sheet2!A=A2、Sheet2!B=B2:跨表条件对齐。
  2. *Sheet2!C:留下金额。
  3. SUMPRODUCT 求和。
  4. 不必先合并表。
多表依次查找 =IFERROR(XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,,0),XLOOKUP(A2,Sheet3!A:A,Sheet3!C:C,,0)) 点击展开拆解讲解

作用:先查 Sheet2,失败再查 Sheet3。

拆解讲解(由外到内 / 从左到右)
  1. 外层 IFERROR(主XLOOKUP, 备XLOOKUP)。
  2. 主:Sheet2。
  3. 备:Sheet3。
  4. 可继续嵌套更多表形成优先级链。
跨表 SUMIFS =SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2) 点击展开拆解讲解

作用:标准多条件跨表求和。

拆解讲解(由外到内 / 从左到右)
  1. 求和区 Sheet2!C。
  2. 条件区指向 Sheet2,条件值来自当前表 A2/B2。
  3. 通常比 SUMPRODUCT 更清晰也更快。
按表名取固定格 =IFERROR(INDIRECT("'"&A2&"'!B2"),"") 点击展开拆解讲解

作用:A2为表名时,取该表B2。

拆解讲解(由外到内 / 从左到右)
  1. "'"&A2&"'!B2":拼出 '表名'!B2。
  2. INDIRECT:文本→引用。
  3. IFERROR:表不存在等错误时返回空。
  4. 场景:目录表批量汇总各表同一位置。
行序号 =SEQUENCE(ROWS(A2:A100),1,1,1) 点击展开拆解讲解

作用:生成与数据行数相同的1..N。

拆解讲解(由外到内 / 从左到右)
  1. ROWS(A2:A100):有多少行。
  2. SEQUENCE(行数,1,1,1):从1起步长1的单列序号。
  3. 插入删除后比手工填充更稳。
奇偶行 =IF(MOD(ROW(),2)=0,"偶行","奇行") 点击展开拆解讲解

作用:按工作表行号标记奇偶。

拆解讲解(由外到内 / 从左到右)
  1. ROW():当前行号。
  2. MOD(...,2)=0:偶数行。
  3. IF:偶行/奇行。
补零序号 =TEXT(ROW()-1,"000") 点击展开拆解讲解

作用:把行号派生序号格式化为三位数。

拆解讲解(由外到内 / 从左到右)
  1. ROW()-1:得到序号(视起始行调整)。
  2. TEXT(...,"000"):补零到3位,如001。
连续日期(显示为日期) =SEQUENCE(12,1,DATE(2024,1,1),1) 点击展开拆解讲解

作用:从指定日起连续12天。

拆解讲解(由外到内 / 从左到右)
  1. DATE(2024,1,1):起始日期序列号。
  2. SEQUENCE(12,1,起点,1):步长1天,共12个。
  3. 单元格设日期格式显示。
  4. 若要每月一条,不要用步长1,应改 EDATE。
是否存在达标 =IF(COUNTIFS(A:A,E2,B:B,">"&F2)>0,"达标","未达标") 点击展开拆解讲解

作用:判断是否存在满足条件的行。

拆解讲解(由外到内 / 从左到右)
  1. COUNTIFS(...):数有多少行达标。
  2. >0:存在性判断。
  3. IF:达标/未达标。
  4. 组合:先计数,再判断「有没有」,不是求合计。