Excel高效动态函数,你知道几个?

现代 Excel 真正的分水岭不是会不会透视表

而是有没有开始用「返回一片区域」的公式

第一个前提：动态数组就是「一个公式返回一片区域」。写完按回车，结果会自动溢出到右边和下面的空白格，整片区域是一个整体，要改只改左上角那一格。这是它和传统公式最根本的区别，也是后面所有写法的前提。

第二个前提：它有版本门槛，而且门槛不止一道。动态数组需要 Microsoft 365 或 Excel 2021 及以上；而 2024 年底才正式推出的 GROUPBY、PIVOTBY 与正则函数族（REGEXTEST、REGEXEXTRACT、REGEXREPLACE），目前在 Excel 2019 / 2021 永久版里写下去会直接返回 #NAME?。先看清自己手里是哪个版本，再决定用哪一套写法。

第三个前提：溢出区域必须是空的。公式要往右往下铺开的那几格不能有任何内容，否则整条公式报 #SPILL!。这个报错会框出被挡住的位置，属于第一天上手最容易撞上、也最容易解决的一类问题。

XLOOKUP 的四个参数依次是：找什么、在哪一列找、返回哪一列的结果、找不到时返回什么。

三个实质性改进：可以向左查，不用再靠辅助列把查找列挪到最左边；默认就是精确匹配，不用每次在末尾补一个 0；第四个参数是「找不到时返回什么」，写个「未找到」远比一片 #N/A 友好。第二个例子里返回的是两列构成的一片区域，结果会一起溢出，一次取回整行。

FILTER 的第二个参数是条件、第三个参数是「一条都没筛到时显示什么」，不写第三个参数，空结果会返回 #CALC!，很多人以为公式写错了。三个函数可以逐层嵌套：先筛、再去重、最后按第四列降序排，整条流水线仍然只是一个公式，源数据一改结果立刻跟着变。

这是近几年最值得练的一组新函数，现已正式可用：

GROUPBY 做单轴汇总，PIVOTBY 再加上第二个轴，等于用公式搭出一个轻量透视表。相比传统透视表，它永远不过期、不需要手动刷新、可以直接喂给图表。代价是结果是公式而不是对象，没法拖字段，所以它适合那种「每周都要重出一遍」的固定报表；需要临时探索数据时，透视表依然更顺手。

第三个参数除了 SUM，还能传自定义的 LAMBDA，比如取中位数、算加权平均——这一点透视表做不到。第三个例子顺手用 VSTACK 给结果加了一行表头。

TEXTSPLIT 按分隔符把一个单元格拆成多格，替代了老一套「分列 + 辅助列 + 手工拼回」。正则三件套则把「取出手机号」「删掉所有非数字字符」这类清洗动作压成一条公式——以前这些要么靠一长串 MID 与 FIND 嵌套，要么得开 Power Query。它们同样只在 Microsoft 365 与网页版可用，永久版用户请直接用 Power Query 替代。

LET 让你给中间结果起名字，公式从「一坨嵌套」变成「几行有名字的步骤」，读和改都轻松很多，而且同一段计算被引用两次时只算一次。LAMBDA 更进一步，能把公式存成自定义函数，在整个工作簿里像内置函数一样反复调用。

另有一条 2026 年的新情况值得留意：2026 年 4 月起，Excel 的 Copilot 增加了「聊天 / 编辑」模式切换，编辑模式下它会直接改写你的工作簿，改动会高亮显示、被改动的工作表标签会变绿，而符合条件的用户默认就是开启编辑权限的。所以真实报表上建议先用聊天模式让它解释、给建议，确认无误再切到编辑模式动手。

这套写法真正改变的不是公式的长短，而是「数据一变就要重做一遍」这件事——过去每来一批新数据就得重做辅助列、重刷透视表，现在源数据一改，结果自己就跟上了。建议先从 XLOOKUP 和 FILTER 这两个换起，用顺了再上 GROUPBY 与 LET，不必一次全换。

既然看到这里了，如果觉得有用，随手点个赞、在看、转发三连吧。

THANKS FOR READING