一个公式,顶过去一整天:EXCEL动态数组五剑客

如果你还在用“筛选→复制→粘贴到新表→删重复→排序”这套流程处理数据，那么这篇文章可能会让你感到一点“冒犯”——因为Excel里有一组函数，可以用一个公式完成上述全部动作，而且源数据一变，结果自动跟着变，不需要任何手动刷新。

这组函数叫动态数组函数。其中最有代表性的五个是：FILTER、SORT、UNIQUE、SEQUENCE、RANDARRAY。

它们共享一个底层机制：公式不再只返回一个值，而是将结果“溢出”到相邻的空白单元格中，形成一个动态数组区域。这意味着你只需要在一个单元格里写公式，剩下的交给Excel自己铺开。

前提只有一个：Excel 2021、Microsoft 365（含网页版），或最新版WPS。Excel 2019及更早版本不支持动态数组。如果你的版本符合，往下看。

01 FILTER：让数据自己“跳”出来

FILTER解决的是一个最朴素的需求：把符合条件的行，原样搬出来。

语法只有三个参数：

=FILTER(要返回的数据区域, 筛选条件, [无结果时显示什么])

最简单的用法，一对多查询。比如A列是商品名，B列是品牌，想把“松下”的所有商品拎出来：

=FILTER(A2:A13, B2:B13="松下")

条件 B2:B13="松下" 会生成一组TRUE/FALSE，FILTER只保留TRUE对应的行。如果一条都没匹配上，Excel会甩一个 #CALC! 给你；加上第三参数就能兜底：

=FILTER(A2:A13, B2:B13="松下", "无匹配记录")

多条件靠乘号 * 实现“同时满足”。筛选品牌为松下且库存大于20：

=FILTER(A2:A13, (B2:B13="松下")*(C2:C13>20))

两个逻辑值数组相乘，TRUE×TRUE=1（保留），其余为0（排除）。

还有一个不太起眼但极实用的变体：提取未售出商品。用COUNTIF判断A列每个商品在已售列表C列中出现的次数，次数为0的就是没卖掉的：

=FILTER(A2:A13, COUNTIF(C2:C5, A2:A13)=0)

02 SORT：排序，但不碰原数据

Excel自带的排序按钮会改变原数据的行序。SORT不同——它生成一份排序后的副本，原表纹丝不动。这在做报表、看板时非常关键：源数据是“账本”，不能随便动。

语法：

=SORT(数组, [按第几列排], [1升序/-1降序], [FALSE按行/TRUE按列])

只写 =SORT(B3:E51)，默认按第一列升序。想按第2列降序：

=SORT(B3:E51, 2, -1)

SORT的真正威力在于嵌套。先筛选，再排序是最经典的组合：

=SORT(FILTER(B3:E51, B3:B51="Laptop"), 2, 1)

先让FILTER把“Laptop”的行挑出来，SORT再把结果按第2列升序排列。一个公式，两层逻辑。

03 UNIQUE：去重，但不用“删除重复项”

“删除重复项”按钮是破坏性的——原数据没了。UNIQUE是生成性的：它返回一份去重后的列表，原表完好，源数据一变，去重结果自动更新。

=UNIQUE(数据区域, [按行/按列], [返回“仅出现一次”的值])

一列去重，最常见：

=UNIQUE(B2:B6)

第三参数设为TRUE，则只保留仅出现过一次的值（真正意义上的“唯一值”）：

=UNIQUE(B2:B6, , TRUE)

一个冷门但好用的组合：按条件去重。比如要从值班表中提取“A区”的不重复经理名单：

=UNIQUE(FILTER(C2:C14, A2:A14="A区"))

FILTER先圈出A区的人，UNIQUE再去重。顺序不能反——先去重再筛选，会把跨区的同名记录错误合并。

UNIQUE还有一个“数学用法”：计算实际参赛人数。名单里有人参加多个项目，=COUNTA(UNIQUE(A2:A9)) 一步得出不重复人数。

04 SEQUENCE：数字序列，不再手拖

生成 1、2、3、4……过去靠拖填充柄，数据量变了还得手动扩展。SEQUENCE让序列本身变成公式产物：

=SEQUENCE(行数, [列数], [起始值], [步长])

=SEQUENCE(10) 生成1到10的垂直数组；=SEQUENCE(4,5) 生成4行5列的矩阵。步长可以自定义：=SEQUENCE(3,4,5,2) 从5开始，每次加2，铺满3行4列。

SEQUENCE和日期结合，能做出自动更新的时间轴。比如生成从某天开始的连续14天：

=SEQUENCE(, 14, DATE(2026,10,1))

配合日期格式，就是一条滚动日程条。甘特图的时间轴横标也可以用 SEQUENCE(, MAX(结束日期)-MIN(开始日期)+1, MIN(开始日期)) 自动生成，项目起止日期一改，横轴自动伸缩。

在组合公式里，SEQUENCE常担任“行号发生器”的角色。SEQUENCE(ROWS(数据))^0 会生成一列全是1的数组，在FILTER的可选条件场景中用来“放行全部记录”。

05 RANDARRAY：随机，但可复现的随机

RANDARRAY返回一个随机数矩阵，是RAND函数的“批量版”：

=RANDARRAY([行数], [列数], [最小值], [最大值], [TRUE整数/FALSE小数])

=RANDARRAY(3,2) 生成3行2列、0到1之间的小数；=RANDARRAY(3,2,1,10,TRUE) 生成1到10的随机整数。

RANDARRAY单独用，最直接的是抽奖 / 随机排序。把它塞进SORTBY的排序依据里，就等于给每一行发了一个随机“号码牌”：

=SORTBY(SEQUENCE(9), RANDARRAY(9))

生成1到9的随机排列。取前N个，就是中奖序号。想随机抽取3个人名：

=TAKE(SORTBY(A2:A100, RANDARRAY(ROWS(A2:A100))), 3)

按 Ctrl+Alt+F5 可以重新随机。

一个使用提醒：RANDARRAY是易失性函数，工作表中任何一次计算都可能触发它重新生成随机数。如果想让某一组随机结果“固定”下来，复制后“粘贴为值”。

06 组合技：一个公式 = 筛选 + 去重 + 排序

五个函数单拿出来都不难，但动态数组真正改变工作方式的地方，是把数据处理的链条写进一个公式。

最经典的组合是 FILTER → UNIQUE → SORT。假设要提取某区域的客户名单，去重后按字母排序：

=SORT(UNIQUE(FILTER(客户列, 区域列="华东")))

FILTER先筛出华东的行，UNIQUE去掉重复客户，SORT把最终名单排好。源数据新增记录、修改区域、删除客户，结果自动更新，不需要任何“刷新”按钮。

这个链条还可以继续往前延伸。如果想把SEQUENCE和RANDARRAY也接进来，一个“随机抽奖 + 去重 + 排序”的完整公式可以是：

=SORT(UNIQUE(TAKE(SORTBY(名单区域, RANDARRAY(ROWS(名单区域))), 10)))

先随机打乱名单、取前10个，再去重，最后排序——一个单元格，完成过去需要辅助列和多次复制粘贴才能做到的事。

一个值得养成的习惯：在公式栏里按 Alt+Enter 给嵌套公式换行，逻辑分层会清晰很多，后续排查也方便。

07 遇到 #SPILL! 怎么办

动态数组的报错里，#SPILL! 几乎是最常见的一个。它不是公式写错了，而是公式想“铺开”的地方被挡住了。

排查三步：

第一步：看溢出区。 选中报错的单元格，Excel会用虚线框标出它“想要”占据的范围。检查这个范围内是否有文字、数字、合并单元格或图表。

第二步：清空阻挡物。 删掉溢出区里的内容，或者把公式挪到一个下方有足够空白的位置。

第三步：检查是否在“超级表”里。 Excel表格（Ctrl+T创建的Table）中的公式默认使用 @ 进行单值引用，可能会抑制数组溢出。如果在表格内使用动态数组，可以先用 @ 运算符确认行为，或把数据区域转换为普通区域后再试。

还有一个容易踩的坑：一个动态数组报错，引用它的下游公式会连锁报错。所以看到多个 #SPILL! 时，先找到最上游的那个修复，下游往往自动恢复。

最后

FILTER、SORT、UNIQUE、SEQUENCE、RANDARRAY这五个函数，本质上在做同一件事：把“手工步骤”翻译成“公式逻辑” 。

手工筛选是一次性的，公式筛选是持续的；手工排序会破坏源数据，SORT生成的是一个活的副本；手工去重删掉就没了，UNIQUE保留原始数据、只交出干净的结果。动态数组改变的与其说是函数写法，不如说是你和数据之间的关系：你不再需要“操作”数据来得到答案，只需要描述答案应该长什么样，Excel会持续把它算出来。

如果只记一个起点，从 =UNIQUE(FILTER(...)) 开始。把这一条用熟，后面的组合会自然长出来。