Excel动态数组:一个公式,搞定一片区域

传统Excel公式的工作方式，是“一个格子一个公式”。

你写好一个公式，然后向下拖、向右拖，拖出一整片结果。

动态数组彻底改变了这件事——你只需要在一个单元格里写一个公式，结果自动“溢出”到周围的单元格。

不用拖拽，不用预选区域，不用理解什么花括号。

这篇文章，我从一张乘法表讲起，带你完整走一遍：传统公式 → CSE数组公式 → 动态数组，三个时代的演进。

看完之后你会明白，动态数组不是某个函数的小改进，而是Excel公式范式的一次根本转变。

！！！注意，请使用2021以上版本Excel，或WPS

一、从一张乘法表说起：传统公式的天花板

先看一个经典场景：用Excel生成大九九乘法表。

做法很常规：

A列填上1到9，第一行也填上1到9，然后在交叉位置写一个公式：=$A2*B$1

这里用到了混合引用——$A2锁列不锁行，B$1锁行不锁列。

写好之后，向右拖、向下拖，81个格子就填满了。

效果如下：

看起来挺酷。但问题不少。

第一，混合引用本身就是门槛。

$A2 和 B$1，美元符号放前面还是放后面，什么时候锁行什么时候锁列——这件事对新手极不友好。很多人用了好几年Excel，遇到混合引用还是要愣一下。

第二，拖拽本身就是“手动操作”。

现在是9×9，万一需求变成12×12呢？你得重新拖。行列数一变，就要返工。

第三，81个格子里躺着81个公式。

虽然长得一样，但每个格子都是独立的公式。改了一个，不影响其他的。维护起来，心里没底。

这不是乘法表独有的问题。

工作中的交叉汇总表、矩阵计算、双维度对照表，全部面临同样的困境——公式散落在一大片区域里，靠拖拽生成，靠手动维护。

二、曾经的“高手绝活”：CSE数组公式

Excel其实很早就提供了一种“批量计算”的能力，叫数组公式。

传统数组公式的使用方式是这样的：

1. 先用鼠标选中你想输出结果的整片区域
2. 在编辑栏输入公式
3. 按下 Ctrl+Shift+Enter（简称CSE）确认

Excel会在公式两端自动加上花括号 {}，表示这是一个数组公式。

用这种方式也能生成乘法表（请注意公式中没有使用绝对引用）：

看起来也能实现。但痛点同样明显。

第一，必须提前选中输出区域。

你要先知道结果是9行9列，然后精确地选中这81个格子，再输入公式。选大了不行，选小了也不行。大小估错，只能删掉重来。

第二，花括号不能手动输入。

很多人看到公式带花括号，就自己手敲一对 {} 上去——不管用。必须通过 Ctrl+Shift+Enter 让Excel自动加。这个细节，不知道绊倒过多少人。

第三，结果区域是固定的。

如果数据从9行变成了12行，CSE数组公式不会自动扩展。你必须删除原来的公式，重新选区、重新输入。

所以，CSE数组公式功能确实强大，但门槛太高。

它始终是少数高手的工具，从来没有真正普及过。

三、动态数组：一个公式，自动“溢出”整片区域

好的，现在进入正题。

什么是动态数组

从Microsoft 365（以及Excel 2021）开始，Excel引入了一个全新的机制：溢出（Spill）。

意思很直白：你在一个单元格里输入公式，如果这个公式的计算结果不止一个值，Excel会自动把结果向下、向右填充到相邻的空白单元格中。

不需要拖拽。

不需要 Ctrl+Shift+Enter。

不需要提前选中区域。

你就写一个公式，按回车，结果自己“溢”出来。

这就是动态数组。

用SEQUENCE重写乘法表

现在我们用动态数组的方式，重新做那张9×9乘法表。

只需要一个公式：

=SEQUENCE(9,1,1,1) * SEQUENCE(1,9,1,1)

解释一下。

SEQUENCE(9,1,1,1) 生成一个9行1列的序列：1、2、3……9，竖着排。

SEQUENCE(1,9,1,1) 生成一个1行9列的序列：1、2、3……9，横着排。

两者相乘，Excel自动进行矩阵运算，得到一个9行9列的乘法表。

在任意一个空白单元格输入这个公式，按回车：

一个公式，81个结果，自动溢出。

现在回头对比一下：

如果需求变成12×12？把两个SEQUENCE里的9改成12就行。一秒钟的事。

当然，也可以使用这种写法，更符合直觉（请注意数据区域的细边框，这表示是溢出的结果）：

不止SEQUENCE：动态数组家族

动态数组带来了一批新函数，每一个都遵循“一个公式，溢出一片结果”的逻辑。

这里先快速过一遍，让你有个印象：

UNIQUE —— 一键去重

=UNIQUE(A2:A100)

从A列提取所有不重复的值，结果自动向下溢出。以前做这件事，要么用辅助列+COUNTIF判断，要么用高级筛选。现在一个公式搞定。

SORT / SORTBY —— 动态排序

=SORT(A2:A100, 2, -1)

按水果名字降序排列，结果直接溢出。

数据源变了，排序结果自动更新（榛子是Z开头，比樱桃的Y要靠后）。

FILTER —— 条件筛选

=FILTER(A2:B100, B2:B100="北京")

筛选出B列等于“北京”的所有行。和自动筛选的区别是：结果在另一个位置以公式形式呈现，原始数据不受影响。

RANDARRAY —— 批量随机数

=RANDARRAY(5,3,1,100,TRUE)

生成5行3列、范围1到100的随机整数。做模拟数据、测试场景特别方便（按一下“开始计算”或快捷键F9，就会重新生成一次）

这些函数后面可以逐个深入讲。现在你只需要记住一件事：

它们都是“写一个公式，自动输出一片结果”。这是动态数组的核心范式。

四、溢出引用符 # ：让动态数组“串联”起来

动态数组还引入了一个新符号：井号 #。

什么是 # 引用

假设你在A1输入了一个公式，结果溢出到了A1:A5（5个值）。

如果你在另一个公式里写 A1#，它代表的就是A1溢出的整个结果区域——也就是A1:A5。

注意，这个引用是动态的。

如果A1的溢出结果变成了7个值（A1:A7），那 A1# 自动变成A1:A7。不需要你手动去改引用范围。

核心价值：链式更新

# 引用的真正威力在于：你可以把多个动态数组公式“串”起来，上游变了，下游自动跟着变。

来看一个实战场景。

假设你有一张销售数据表，其中有一列是“城市”（请注意，为了演示效果，这是一个“表”，可以被结构化引用）

第一步，在A1提取所有不重复的城市：

=UNIQUE(销售数据[城市])

结果自动溢出，得到“北京”“上海”“广州”三个值。

第二步，在B1统计每个城市出现的次数：

=COUNTIF(销售数据[城市], A1#)

注意这里的 A1#。它引用的是A1溢出的整个区域，也就是“北京”“上海”“广州”这三个值。

COUNTIF会对每个值分别计数，结果也溢出为三个数字。

现在，关键的来了。

如果数据源新增了一条“深圳”的记录，会发生什么？

A1的UNIQUE公式自动多溢出一行“深圳”。

B1的COUNTIF公式感知到 A1# 的范围变了，也自动多算一行。

两个公式，同时自动扩展。你什么都不用动。

再加一列统计销售额，也是一样的

想想以前怎么做这件事。

以前你得预留足够多的行——“万一以后城市变多呢？先留个100行吧”。COUNTIF写到100行，大部分是空的，但你不敢删，怕不够用。

现在完全不需要了。数据有多少，公式就覆盖多少。多一条自动多一行，少一条自动少一行。

这才是动态数组真正改变工作方式的地方。

五、总结：三个时代的对比

最后，把三种方式放在一起看：

从”一个格子一个公式”，到”一个公式一片结果”。

这不是某个函数的升级，是Excel公式范式的一次根本转变。