1. 什么能嵌套
多数函数都能嵌,关键是内层返回类型要匹配外层该参数。
- 数值:SUM、MATCH 行号、YEAR…
- 文本:TEXT、LEFT、& 拼接、XLOOKUP…
- 逻辑:比较式、AND/OR、ISNUMBER…
- 数组:FILTER、SORT、UNIQUE…
例:INDEX(C:C, MATCH(A2,A:A,0)) —— MATCH 给行号,填进 INDEX。
共 - 条 · 复制后改区域
内层函数的返回值,就是外层某个参数。Excel 从最内层往外算,一层算完再交给上一层。
多数函数都能嵌,关键是内层返回类型要匹配外层该参数。
例:INDEX(C:C, MATCH(A2,A:A,0)) —— MATCH 给行号,填进 INDEX。
读:从最内层看「得到什么」,再看外层怎么用。
=TAKE(SORT(FILTER(表,条件),列,-1),5)
│ │ └ 先筛选
│ └ 再排序
└ 最后取前5行
写:先列步骤,再填区域;需要时外包 IFERROR / IFNA。
常见错误:把 FILTER 数组结果塞进只接受单个数字的参数。
=XLOOKUP(A2,B:B,C:C,,0,1)
点击展开拆解讲解
作用:精确匹配并从上往下搜,取第一次。
=SUMPRODUCT((A:A=E2)*(B:B=F2)*C:C)
点击展开拆解讲解
作用:用数组相乘实现双条件求和(兼容老版本)。
=SUMPRODUCT((A2:A100=E2)*(B2:B100=F2))
点击展开拆解讲解
作用:不加金额列时,等价于多条件计数。
=COUNTIFS(A:A,E2,B:B,"<>")
点击展开拆解讲解
作用:在 A=E2 前提下统计 B 非空。
=AVERAGEIFS(C:C,A:A,E2,B:B,F2)
点击展开拆解讲解
作用:双条件下对 C 求平均。
=SUMPRODUCT(MAX((A:A=E2)*C:C))
点击展开拆解讲解
作用:无 MAXIFS 时用数组取条件最大。
=SUMPRODUCT(ISNUMBER(FIND(E2,A2:A100))*C2:C100)
点击展开拆解讲解
作用:A 包含关键字 E2 时对 C 求和。
=SUMPRODUCT((LEFT(A2:A100,2)="华北")*C2:C100)
点击展开拆解讲解
作用:A 前两字为「华北」时对 C 求和。
=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)),1,99))
点击展开拆解讲解
作用:按「-」分段,取出第1段(兼容老 Excel)。
=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)),99,99))
点击展开拆解讲解
作用:同上技巧取第2段。
=SUBSTITUTE(TRIM(A2)," ","")
点击展开拆解讲解
作用:清除首尾多余空格及中间空格。
=TEXT(A2,"0.00%")
点击展开拆解讲解
作用:把小数显示成百分比文本。
=TEXT(A2,"#,##0.00")
点击展开拆解讲解
作用:金额千分位+两位小数的文本。
=IFERROR(TEXTBEFORE(A2,"-"),A2)
点击展开拆解讲解
作用:新函数直接取分隔符前;无则原文。
=TEXTSPLIT(A2,"-")
点击展开拆解讲解
作用:按「-」拆成横向溢出数组。
=IFERROR(TEXTBEFORE(TEXTAFTER(A2,"-"),"-"),"")
点击展开拆解讲解
作用:取被两个「-」夹住的中间段。
=SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","")
点击展开拆解讲解
作用:依次去掉空格和横线。
=REGEXEXTRACT(A2,"[0-9]+")
点击展开拆解讲解
作用:用正则取出连续数字。
=NETWORKDAYS(A2,B2)-1
点击展开拆解讲解
作用:两日之间工作日间隔(常见不含起始日算法)。
=SUMIFS(C:C,A:A,">="&E2,A:A,"<="&F2)
点击展开拆解讲解
作用:A 落在 E2~F2 时对 C 求和。
=SUMPRODUCT((MONTH(A2:A100)=E2)*(C2:C100))
点击展开拆解讲解
作用:月份等于E2的金额合计。
=SUMPRODUCT((YEAR(A2:A100)=2024)*(MONTH(A2:A100)=E2)*C2:C100)
点击展开拆解讲解
作用:指定年+月双条件汇总。
=SUMPRODUCT((A2:A100>A2)/COUNTIF(A2:A100,A2:A100))+1
点击展开拆解讲解
作用:按「有多少更大值」计算的一种排名写法。
=CEILING(A2,0.5)
点击展开拆解讲解
作用:向上对齐到0.5步长。
=SUMPRODUCT((Sheet2!A2:A100=A2)*(Sheet2!B2:B100=B2)*Sheet2!C2:C100)
点击展开拆解讲解
作用:按当前行条件对 Sheet2 金额汇总。
=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2)
点击展开拆解讲解
作用:标准多条件跨表求和。