冷门函数速查与实战清单

冷门函数速查表

全书讲过的冷门函数集中在这里，按用途归类。查表时先找场景，再决定要不要回到对应篇章细看。最低版本一栏是硬门槛，发给同事前先对照。

表格里标着全版本的函数最值得优先学：不分版本、人人能开，响了就是赚到。标 2021 的那批属于能配套升级就尽量用，功能差异确实大。标 2024 的 LET 和 LAMBDA 现阶段留在自己电脑上用，别进对外文件。

按场景选函数

速查表之外，实际工作中更常见的是反过来：先有具体问题，再找该用谁。下面这张表把高频问题直接对应到函数，省去中间的思考成本。

老版本退路不是将就，它是保命方案。给外部发的文件，退路那一栏才是主线。等双方版本都升上来了，再切回首选写法也不迟。关于每一行的兼容细节，版本对照里有完整讨论。

实战清单：拿到表先问三个问题

函数是兵器，先看清战场再选。任何一张新表到手，习惯性先问三个问题，答案直接决定函数选择。第一问：这张表的行数会增长吗？第二问：这个文件只自己用还是要发出去？第三问：计算结果需要自动更新还是手动出一次就行？

行数稳定且只自己用，所有新函数放开写，XLOOKUP 配 UNIQUE 配 FILTER，怎么痛快怎么来，多条件查找的四种解法对比里那套对比足够你做选择。行数会增长，优先把区域转成表格，让结构化引用处理扩展问题，别用 OFFSET 硬扛。文件要发出去，先确认对方版本，降级写法照着老版本退路那一栏换。手动出一次结果就行，菜单操作往往比公式更快，删除重复项和分列都是十秒钟能做完的事。

具体到常见任务，清单整理如下。做唯一名单：数据稳定用 UNIQUE，要发出且对方版本旧，用辅助列加 COUNTIF 筛选复制。做条件汇总：单条件 SUMIFS，多条件 SUMIFS 嵌套或 SUMPRODUCT，超大表上数据透视表而不是公式。做查找取数：新环境 XLOOKUP，兼容环境 INDEX 加 MATCH，永远不要用 VLOOKUP 加 IF 虚拟数组那种写法。做文本拆合：TEXTSPLIT 和 TEXTJOIN 是首选，老环境分列菜单和连接符也够用。做计算性能：先检查整列引用和条件格式数量，再看有没有易失函数，顺序不能颠倒。

上手顺序建议

一群冷门函数一起学容易乱。建议按这个顺序推进，每一步练熟了再走下一步。第一步 AGGREGATE 和 DATEDIF：全版本可用，立刻就能在工作中替换掉那些笨拙的老写法，见效最快。第二步 TEXTJOIN：2019 起普及，合并文本这件事从此告别连字符地狱。第三步 INDEX 加 CHOOSE 分支：把 IF 嵌套收一收，公式可读性会直接上一个台阶。

第四步 XLOOKUP 和动态数组家族：前提是你本机版本足够。这一步学会之后，做表的思路会彻底变化——从写死结果变成写规则，数据变结果自己变。FILTER 和UNIQUE两篇值得多读两遍，动态数组是这套思路的入口。第五步 LET 和 LAMBDA：等你写过几条长到自己也看不懂的公式之后，自然会觉得需要它们，那时候学成本最低。中间遇到性能毛刺，随时翻易失函数与性能陷阱排查。

三个组合写法值得背下来

单个函数会用只是入门，把几个拼起来解决一整类问题才是实战。下面三套组合覆盖了日常八成的复杂需求，建议背熟。

第一套是动态去重加排序加取前几。给它一个原始明细区域，它能直接还你一张干净的排行榜。写法是把三层套起来：先 FILTER 按条件筛出需要的行，再用 SORT 按金额从大到小排，最后用 UNIQUE 处理重复项。一条嵌套写下来大概是这样：=SORT(UNIQUE(FILTER(A2:D500,B2:B500>1000)),4,-1)。它一次完成了三件原来要三步才能做的事，而且源数据一变动结果自动跟着变。老版本里这三件事要分别用高级筛选、排序菜单、删除重复项做三遍。

第二套是条件求和加条件计数加占比。用 SUMIFS 求某个区域的总额，用 COUNTIFS 数出对应的记录条数，两者相除就是平均单笔金额。这套写法在做分区统计时特别顺手，比拉一遍数据透视表更轻。例如 =SUMIFS(D:D,C:C,"华东")/COUNTIFS(C:C,"华东") 得到就是华东区的平均金额，改一下条件字符串就能换区域，比透视表拖字段快得多。

第三套是查找加容错加默认值。IFERROR 包住 XLOOKUP 或 INDEX 加 MATCH，查不到的时候返回指定的提示，而不是让一整列都是 #N/A。再进一步，可以把默认值设成一个后续可以筛出来的标记，比如 =IFERROR(XLOOKUP(A2,C:C,D:D),"待核对")，事后在表里筛一下待核对就能一次性找出所有对不上的记录，这比肉眼对名字可靠得多。这套写法和多条件查找的四种解法对比里的思路一脉相承。

一张表练一遍

光看不如动手。找一张手边现成的表，按下面的步骤走一遍，二十分钟能把书里的核心思路过一遍。先选中数据区域按 Ctrl 加 T 转成表格，给表起个有意义的名字，感受一下结构化引用 =SUM(表1[金额]) 的写法比 =SUM(D2:D500) 直观在哪里。然后在旁边空列写一条 =UNIQUE(表1[客户])，看唯一名单是怎么自动生成的。再往下写一条 =SUMIFS(表1[金额],表1[客户],G2)，把 G 列的唯一名单逐个算出金额小计，这时候你已经不靠透视表做出一份分类汇总了。

接着试一次 FILTER：=FILTER(表1,表1[金额]>5000)，看大额记录是怎么整行捞出来的。再试 SORT 把它按金额降序排一下。最后把区域从表格改回普通区域，用辅助列加 COUNTIF 重做一次去重，对比两套写法的理解成本。做完这一轮，动态数组、结构化引用、条件统计三件事就都有了肌肉记忆。

练习的时间投入很值。第 4 步的 LET 和 LAMBDA 先不碰，等你写过几条长到自己也看不懂的公式之后，自然会觉得需要它们，那时候学成本最低。中间遇到性能毛刺，随时翻易失函数与性能陷阱排查。

每一步的标准不是会背语法，而是碰到真实表格时手能自己伸向正确的函数。检验方法很简单：找一张上个月做过的旧表，看能不能用新学的函数重做一遍。重做完如果觉得轻松了，就是真的会了。

三个最容易翻车的地方

第一处是动态数组的溢出被挡住。写 =FILTER(A2:F500,B2:B500>100) 的时候，Excel 需要从公式所在格向右向下占用一片空间放结果。如果那片空间里已经有别的数据，屏幕上就会跳出一个 #SPILL!，意思是溢出区域被占用了。解决办法不是改公式，而是把挡住的东西清掉——通常是右边或者下边一格有个空格里藏着内容，看着空其实有数据。点一下警告图标，Excel 会告诉你具体是哪个格挡住的。这个错第一次遇到很懵，知道原因之后就一目了然。

第二处是 FIND 扫出来的位置和长度对不上。用 MID 加 FIND 拆文本时，常见错误是 =MID(A2,FIND("-",A2),3) 取出来带着分隔符。原因在于 FIND 返回的是分隔符本身的位置，要从它的下一位开始取，写法应该是 =MID(A2,FIND("-",A2)+1,3)。这个加一的坑，几乎每个学过文本函数的人都踩过。另外 FIND 区分大小写且不支持通配符，要找上半角斜杠这类特殊字符时得用 SEARCH，SEARCH 不区分大小写且支持通配符。

第三处是 SUMPRODUCT 的条件括号写错。标准写法 =SUMPRODUCT((A2:A100="华东")*(B2:B100)) 里，条件部分必须整体加括号再相乘，漏掉外层括号就会报错。想把条件连接起来用乘号表示且，用加号却不行——加号会把结果加起来，逻辑上不成立。需要或的关系时，写法是两个布尔表达式用加号连接但左右各加一个比较：=SUMPRODUCT(((A2:A100="华东")+(A2:A100="华南"))*(B2:B100))。这个括号加号组合很反直觉，是 SUMPRODUCT 最常出错的地方，用之前先记牢。

冷门函数和快捷键配合最省力

函数解决算的问题，快捷键解决操作的问题，两者配合起来做表速度能再提一档。写公式时，按 Alt 加等号能一键插入 SUM 的自动求和，光标放在数据下方即可，不用手打整个函数。按 Ctrl 加波浪号可以切换显示公式和显示结果，检查一整片公式逻辑时靠它；反过来发给同事前再按一次切回结果视图。按 F4 在编辑公式时循环切换引用方式，从相对到绝对到混合，省去手动敲美元符号。

还有几个和查找函数配套的动作值得养成习惯。Ctrl 加方向键快速跳到数据区边缘，配合 =INDEX(A:A,ROW()-1) 这类公式时定位极快。Ctrl 加 Shift 加方向键选中整片数据，转成表格时一次选中不用拖。名字框里可以直接输入名称跳转到定义区域，用名称管理器维护的表靠它穿梭。函数负责算得对，操作负责做得快，两条腿都练起来才是完整的效率体系，名称相关的技巧见名称管理器进阶。

把这本书读薄

全书二十篇压缩成三句话：动态数组让公式从算一个值变成算一片结果；查找家族的终点是 XLOOKUP，兼容线上的终点是 INDEX 加 MATCH；性能上能不用 OFFSET 和 INDIRECT 就不用。其余细节都是这三句话的展开。需要具体函数的语法和场景时，回前面速查表定位，再点进对应篇章细读。

收藏这份清单就够了

这本书讲的是那些教科书上不常提、但实际工作中很救命的函数。它们不难，难点在于没人告诉你什么时候该用哪一个。速查表解决了查的问题，场景表解决了选的问题，实战清单解决了怎么做的问题，三张表配合起来，你手上就有一套不依赖记忆的工具。忘了语法不要紧，回速查表定位函数名，再点进对应篇章看细节，两分钟就能重新上手。

最后留一句实在话：函数背得再多，不如把一两个用得滚瓜烂熟。AGGREGATE 和 XLOOKUP 这两条练到不用想就能写出来，已经能应付大多数日常需求。剩下的在遇到具体问题时再回来查，边用边记，比一次性背下来牢靠得多。工具为人服务，别反过来被工具牵着走。

常见问题

这些函数在 WPS 里都能用吗

XLOOKUP、UNIQUE、FILTER、TEXTJOIN 在新版 WPS 表格里都能用。LET 支持不稳定，LAMBDA 基本不可用。跨软件分享文件时，保守选择是只用 INDEX 加 MATCH、SUMPRODUCT、AGGREGATE、DATEDIF、TEXTJOIN 这一组，它们在两个软件里都经过验证。WPS 老版本连 TEXTJOIN 都没有，那种环境只能回到菜单和连接符。

学到什么程度算够用

判断标准是能不能覆盖自己每周遇到的表格任务。如果你日常工作是统计和查找，INDEX 加 MATCH、SUMIFS、XLOOKUP、TEXTJOIN 足以应对八成场景。动态数组属于效率倍增器，会了之后做表思路整体改变，但不会也不至于卡死。LET 和 LAMBDA 更像进阶玩具，等确实被长公式折磨过再学不迟。

动态数组和数据透视表冲突吗

不冲突，是两种思路。透视表适合做交互式统计，拖一拖出结果，适合一次性探索。动态数组适合做固定流程的自动化报表，把规则写成公式后源数据一变结果自动更新。需要反复出同一张报表的用函数，需要随时变化角度的用透视表。极少数情况两者结合：用 UNIQUE 出维度列表，再让透视表读它。

AGGREGATE 和 SUBTOTAL 有什么区别

功能定位接近但细节不同。SUBTOTAL 的忽略选项只包括隐藏行和错误值的组合有限，AGGREGATE 的选项更细，能分别控制忽略隐藏行、忽略错误值、两者都忽略，还支持更多函数比如 LARGE 和 SMALL。新表一律用 AGGREGATE，SUBTOTAL 留给兼容老文件的场合。SUBTOTAL 的另一个特点是只对明细数据生效，嵌套分类汇总时自动跳过其他 SUBTOTAL，AGGREGATE 没有这个保护，用在分类汇总结构里要留意。

冷门函数会被淘汰吗

函数只增不减，学会的不会白学。真正会变化的是推荐用法：OFFSET 曾经被广泛推荐做动态区域，现在被 INDEX 和结构化引用取代。所以学函数学机制更重要，懂了为什么，换一种工具也能迁移过去。本书每篇都给了版本和兼容信息，就是这个考虑。