Excel摸鱼指南|第8期:5个365动态数组函数,旧函数可以淘汰了

你还在写VLOOKUP吗？还在手动删除重复值吗？还在复制筛选结果吗？

如果你用的是Excel 365或2021以上版本，这些操作早就有更简单的替代方案了。一个公式自动返回一堆结果，不用下拉填充，不用手动刷新，数据变了结果自动跟着变。

今天教你5个动态数组函数，学会以后，很多旧公式都可以扔了。

版本说明：以下函数需要Excel 365或Excel 2021及以上版本。如果是旧版本，可以先收藏，换了新版Excel直接用。WPS最新版也已支持大部分动态数组函数。

技巧1：XLOOKUP，VLOOKUP的终极替代品

能解决什么问题：VLOOKUP三大痛点——不能往左查、必须数列号、找不到就报错。XLOOKUP全部解决，而且更简单、更强大。

函数语法：

=XLOOKUP(查找值, 查找区域, 返回区域, [找不到时显示], [匹配模式], [搜索模式])

参数说明：

查找值：你要找什么，比如姓名、工号

查找区域：在哪一列找

返回区域：找到后返回哪一列的内容

找不到时显示（可选）：找不到就显示这个，不用再套IFERROR

匹配模式（可选）：0=精确匹配（默认），-1=精确匹配或下一个较小项，1=精确匹配或下一个较大项，2=通配符匹配

操作步骤（根据姓名查工资）：

假设A列是姓名，C列是工资，要在E2根据D2的姓名查工资

在E2输入：=XLOOKUP(D2, A:A, C:C, "查无此人")

按回车，直接出结果。不用下拉，因为XLOOKUP支持数组溢出（如果D列有多个姓名，公式会自动往下填充）

对比VLOOKUP的优势：

支持反向查找：要根据姓名查工号（工号在姓名左边），直接写就行，不用调整列顺序

不用数列号：直接选返回区域，不怕中间插入列导致公式出错

自带容错：第4个参数直接写找不到时显示什么，不用套IFERROR

默认精确匹配：不用写最后那个0，少记一个参数

实用场景：

反向查找：=XLOOKUP(D2, B:B, A:A, "无")（B列找，返回A列）

通配符查找：=XLOOKUP("张*", A:A, C:C, , 2)（找姓张的，最后一个参数2表示通配符）

查找最后一个匹配项：=XLOOKUP(D2, A:A, C:C, , 0, -1)（最后一个参数-1表示从后往前搜）

技巧2：FILTER，按条件动态筛选

能解决什么问题：以前筛选数据要手动点筛选按钮，复制结果，数据变了还要重新筛选。FILTER函数一个公式搞定，结果自动更新，还能直接用于后续计算。

函数语法：

=FILTER(返回区域, 条件, [无结果时显示])

操作步骤（筛选销售部所有员工）：

假设A列是姓名，B列是部门，C列是工资

在空白单元格（比如E2）输入：=FILTER(A:C, B:B="销售部", "无数据")

按回车，销售部的所有员工信息（姓名、部门、工资）自动全部显示出来，自动向下溢出

源数据新增或修改，筛选结果自动更新，不用任何手动操作

多条件筛选：

且条件（同时满足）：用乘号*连接。比如销售部且工资大于1万：=FILTER(A:C, (B:B="销售部")*(C:C>10000), "无数据")

或条件（满足其一）：用加号+连接。比如销售部或市场部：=FILTER(A:C, (B:B="销售部")+(B:B="市场部")>0, "无数据")

小技巧：FILTER的结果可以直接套其他函数，比如统计筛选后的工资总和：=SUM(FILTER(C:C, B:B="销售部"))，一个公式搞定，不用先筛选再求和。

技巧3：UNIQUE，一键提取不重复值

能解决什么问题：以前提取不重复值要"数据→删除重复值"，或者用复杂的数组公式。UNIQUE函数一个公式搞定，而且源数据变了自动更新。

函数语法：

=UNIQUE(数据区域, [按列去重], [只返回出现一次的])

参数说明：

数据区域：要去重的区域

按列去重（可选）：FALSE=按行去重（默认），TRUE=按列去重

只返回出现一次的（可选）：FALSE=返回所有不重复值（默认），TRUE=只返回只出现过一次的值

操作步骤（提取所有不重复的部门）：

假设B列是部门，有很多重复

在空白单元格输入：=UNIQUE(B:B)

按回车，所有不重复的部门自动列出来，自动向下溢出

源数据新增部门，自动出现在结果里，不用重新操作

进阶用法：

多列组合去重：选中多列区域，比如=UNIQUE(A:B)，姓名+部门都相同才算重复

只返回出现一次的值：=UNIQUE(A:A, FALSE, TRUE)，找出只出现过一次的异常数据

搭配SORT排序：=SORT(UNIQUE(B:B))，去重后自动按字母排序

技巧4：SORT，动态排序

能解决什么问题：以前排序要手动点排序按钮，数据变了还要重新排。SORT函数一个公式搞定，自动排序，源数据变了自动更新。

函数语法：

=SORT(数据区域, [排序依据列], [升序降序], [按行按列])

参数说明：

数据区域：要排序的区域

排序依据列（可选）：按第几列排序，默认第1列

升序降序（可选）：1=升序（默认），-1=降序

按行按列（可选）：FALSE=按行排序（默认），TRUE=按列排序

操作步骤（按工资从高到低排序）：

假设A列是姓名，C列是工资

在空白单元格输入：=SORT(A:C, 3, -1)

按回车，整个表格按第3列（工资）降序排列，自动溢出

参数解释：A:C是数据区域，3表示按第3列排，-1表示降序

多列排序：

先按部门升序，同部门再按工资降序：

=SORT(A:C, {2,3}, {1,-1})

用数组{2,3}表示先按第2列再按第3列，{1,-1}表示第2列升序、第3列降序。

实用组合：

筛选后排序：=SORT(FILTER(A:C, B:B="销售部"), 3, -1)，先筛选销售部，再按工资降序

去重后排序：=SORT(UNIQUE(B:B))，去重后的部门自动排序

取前N名：=SORT(A:C, 3, -1)配合INDEX取前10，做排行榜超方便

技巧5：TEXTSPLIT，按分隔符拆分文本

能解决什么问题：以前拆分文本要用"分列"功能，或者写复杂的MID+FIND公式。TEXTSPLIT一个公式搞定，自动溢出到多列。

函数语法：

=TEXTSPLIT(文本, 列分隔符, [行分隔符], [忽略空值], [匹配模式], [填充值])

操作步骤（按逗号拆分地址）：

假设A2是"广东省,深圳市,南山区"，要拆分成三列

在B2输入：=TEXTSPLIT(A2, ",")

按回车，自动拆分成三列：广东省 | 深圳市 | 南山区

下拉填充，所有行一次性拆分完成

进阶用法：

多个分隔符：用数组表示，比如同时按逗号和顿号拆分：=TEXTSPLIT(A2, {",","、"})

按行拆分：第3个参数写行分隔符，比如单元格内有换行，按换行拆分成多行：=TEXTSPLIT(A2, , CHAR(10))

忽略空值：连续分隔符导致空单元格时，第4个参数写TRUE：=TEXTSPLIT(A2, ",", , TRUE)

反向操作：TEXTJOIN合并文本

如果要把多列合并成一列，用TEXTJOIN：=TEXTJOIN(",", TRUE, A2:C2)，用逗号连接A2到C2，忽略空单元格。和TEXTSPLIT是一对，一个拆一个合。

写在最后

这5个动态数组函数，代表了Excel的未来方向——一个公式返回一片结果，自动溢出，自动更新，不用下拉填充，不用手动刷新。

如果你还在用旧版本Excel，建议尽快升级到365，这些函数能让你的效率提升不止一倍。

记住：工具在进化，你的方法也要跟着进化。别让旧公式拖慢你的下班速度。

觉得有用的话，收藏起来慢慢学，也转发给还在写VLOOKUP的同事吧——是时候升级了。

你最想学会哪个新函数？评论区聊聊，下期可以出详细教程。

关注我，每天5个Excel技巧，帮你早点下班。