数组公式入门：一次算一片

数组公式到底在算什么

普通公式的输入是一个单元格，输出也是一个单元格，一对一。数组公式改变的是这个对应关系：输入可以是一片区域，输出也可以是一片区域，一次算一群。这个“一对多”的能力，是数组公式与普通公式最本质的区别。

理解数组公式的关键是接受“批量运算”这个心智模型。以前算一百行的结果要写一百条公式，用数组公式写一条就够了，它自己按位置把一百个格子都算完。Excel 内部做的事情是：把两个同样大小的区域按对应位置逐对运算，产生一个同样大小的结果区域，再把结果放到该放的地方。

这个能力在动态数组出现之前需要按 Ctrl 加 Shift 加 Enter 特别输入，公式会用花括号包起来（花括号是 Excel 自动加的，手敲无效）。有了动态数组之后，普通输入即可，结果会自动溢出到相邻格子。两种方式表达的是同一件事，区别只在版本和输入方式。本章讲的是这个能力的原理和用法，具体的动态数组函数在另一本书里有专门章节。

两种数组：区域数组和常量数组

数组有两种来源，理解这一点能帮你读懂别人写的公式。

第一种是区域数组，来自表格里的实际数据。写 A2:A100，Excel 把它当成一个有一百个元素的纵向数组。区域数组的维度由区域本身决定，一列是纵向数组，一行是横向数组，多行多列是二维数组。公式运算时，两个区域数组参与运算，Excel 会自动按对应位置配对。

第二种是常量数组，直接写在公式里的固定值列表，用花括号包起来。纵向用分号分隔，横向用逗号分隔。比如 {1;2;3} 是一个纵向三元素数组，{1,2,3} 是横向三元素数组。{"一月","二月","三月"} 是横向的文本数组。常量数组不依赖表格数据，适合做固定参数，比如月份名称列表、等级名称列表。

常量数组里分隔符的地区差异是个坑。中文版 Excel 用分号分隔行、逗号分隔列，而某些地区版本正好相反。从网上复制公式时如果报语法错误，先把分隔符换一遍试试。这个坑不常遇到，遇到时很费解。

数组运算的配对规则

数组公式的运算规则说穿了很简单：对应位置相加。但规则背后的细节决定了公式的成败。

两个同样大小的数组相运算，结果的大小和它们一致，每个位置独立计算。A2:A5+B2:B5 得到的是四个位置各自相加的结果，这是最基本的情况。

数组和一个单值相运算时，单值会自动扩展到整个数组。A2:A5*2 里，2 被复制到每一个位置参与运算，等价于每个元素都乘 2。这个特性叫广播，它让常量参与批量运算变得很方便。

两个大小不同的数组相运算时，Excel 会尝试扩展较小的那个。单行数组和单列数组相运算，会扩展成一个矩阵。A2:A5+B1:E1 得到的是四行五列的矩阵，每个格子是行元素加列元素。这个能力是很多高级技巧的基础，比如乘法表、交叉统计。

但大小不匹配且无法扩展的时候，公式会返回 #VALUE!。把四行的数组和五行的数组相加，Excel 不知道怎么配对，只能报错。这类错误常见于区域范围写错，一行之差就报错。

从 IF 到数组：条件统计的进阶

数组公式最实用的场景是条件统计。常规的 IF 只能处理单个值，=IF(A2>60,"合格","不合格") 一次只判一行。要在统计里用条件，就得靠数组。

经典写法是 =SUM(IF(A2:A100>60,B2:B100,0))，含义是逐行判断 A 列是否大于 60，是就取 B 列的值，不是就取零，最后全部加起来。IF 在这里接收的是一个数组条件，返回的也是一个数组结果，SUM 把这个数组求和。这一条公式实现了条件求和，等价于 SUMIF 或者 SUMPRODUCT 的写法。

IF 的老版本数组写法需要按数组键输入，否则只算第一个元素。新版有了动态数组之后，普通输入即可。如果要用在老版本环境，稳妥的做法是换成 SUMPRODUCT，写法和数组公式等价但不需要特殊输入。

数组 IF 的真正价值在于条件复杂到函数覆盖不了的时候。比如要统计满足某个计算条件的行数，条件是两列相减或者相乘的结果，SUMIF 的写法就束手无策，数组 IF 依然可以用。举个具体例子：要数出成本低于售价七成的产品数量，写成 =SUM(--(成本区<售价区*0.7))，这个条件涉及两列之间的比较，SUMIF 做不到。这是数组公式不可替代的地方。

多维数组和矩阵运算

数组公式能处理二维甚至更高维度的数据，这让一些矩阵运算变得可行。

用 MMULT 做矩阵乘法，=MMULT(区域1,区域2)，两个区域的列数和行数需要满足乘法规则。这个函数在需要做多因素加权、财务建模、线性代数类计算时用得上，日常办公场景用得不多，但遇到时能救急。

用 TRANSPOSE 转置数组，把一行变成一列。这个需求在把横向的数据转成纵向的排版时很常见，以前得靠复制加选择性粘贴转置，现在可以写公式实现。转置之后的区域如果源数据变了会自动更新，比手工粘贴的静态结果灵活。

用 SUM 配合二维数组做整表统计。=SUM((A2:A10>60)*(B2:F10)) 里，一列的条件数组和一片多列数据相乘，条件会广播到每一列，实现按行筛选后整片求和。这种写法在需要按行条件统计多列数据时很省事，比写五条 SUMIF 干净得多。

矩阵运算的典型短板是可读性差。=MMULT(TRANSPOSE(A2:B10),C2:D10) 这种嵌套，不看注释没人知道在算什么。用这类公式时务必在旁边的说明页写清数学含义，否则三个月后自己都不认识。这是可维护性和表达能力的取舍，需要时用，但要用得克制。

数组公式的性能边界

数组运算强，代价是计算量。普通公式算一千个格子要写一千条公式，每条算一次；数组公式一条算一千次。总量差不多，但数组公式在内存使用和优化上不如多条独立公式友好。

真正需要注意的是范围。数组公式里写 A:A 这样的整列引用，意味着 Excel 要为一百多万个位置做运算，即便有数据的地方只有几百行。这个代价是灾难性的。数组公式里的区域范围一定要精确到实际数据行。

另一个需要注意的点是数组公式的复制。一条数组公式占一片区域，复制到另一片区域时行为可能和你预期不同。老版本里数组公式的区域大小是固定的，复制到大小不匹配的位置会报错。新版动态数组灵活得多，但也因此容易出现溢出冲突。

做大数据量的统计时，如果数组公式开始拖慢表格，优先考虑换成数据透视表或者 Power Query。公式适合中等规模的数据，上万行之后就未必是最优解了。这个判断分寸在公式性能：到底是什么拖慢了表里有更细的讨论。

数组公式的调试方法

数组公式出错时比普通公式难查，因为你看不到中间结果。有几个办法能把它拆开。

第一个办法是用 F9 临时求值。在编辑状态下选中公式里的某个片段，按 F9，Excel 会把这个片段替换成它的计算结果，让你看到数组里到底是什么。看完按 Esc 撤销，恢复原公式。这个技巧对理解广播和配对的实际情况非常有用，尤其是当你不确定某个条件数组产生了什么的时候。

第二个办法是把数组公式降到单元格里逐格验证。先在一格里写不带数组的版本，把区域引用换成单个格子，看单行结果对不对；单行对了再恢复成数组。这个思路和调试其他公式一样，从简单情况开始。

第三个办法是借助辅助列把中间数组落成实际数据。在辅助列里写 =A2>60 这样的条件，填充整列，得到的是一列 TRUE 和 FALSE，这正是数组公式内部产生的条件数组的样子。看到这一列，你就能理解数组公式在做的事。

这三种方法配合起来，数组公式的调试难度就降到了普通公式的水平。关键是要有耐心把它拆开，别指望盯着一条长公式就能看出问题。

数组公式的实际应用场景清单

不是所有场景都值得用数组公式。下面这份清单列出真正有价值的应用，遇到类似需求时可以优先考虑。

需要“先筛选再计算”的场景，尤其是筛选条件本身是一个计算式。比如统计毛利率低于某个阈值的订单金额合计，条件涉及两列相减再比较，SUMIFS 覆盖不了，此时用数组写法一条搞定。

需要“整片数据按行条件处理”的场景。比如给一张多列表加一列标记，标记依据是这行的多列是否满足某个组合条件。用一条数组公式加上 IF 即可生成整列结果，不用逐行写。

需要矩阵运算的场景，比如加权评分、多因素综合排序、财务模型的现金流折现。这类场景的公式一旦写对就极其稳定，值得投入时间。

需要返回动态区域给其他函数吃的场景。新版动态数组的函数返回的是一片区域，可以直接作为其他函数的输入参数，实现函数嵌套式的数据处理流水线。这个能力在处理复杂数据转换时很有价值。

反过来，纯粹的行级简单计算、单条件的求和计数、只需返回单个结果的查找，都不必用数组公式。有现成的普通函数就用普通函数，简单永远优于复杂。判断标准就是：这条需求用普通函数能不能干净地做完，能就不用数组。

还有一种情况要特别提醒：不要把数组公式当作解决性能问题的手段。有些人听说数组公式一次算一片，以为它比多条公式快，于是在大表上大量使用。实际效果往往相反，数组公式的中间数组会占用大量内存，范围一大反而比多条简单公式更慢。它的价值在表达能力，不在速度。想要速度应该优化区域范围、减少易失函数，而不是换写法。

掌握数组公式的门槛比普通公式高一截，但收益也相应更大。它是从“会写公式”到“会用公式做数据加工”的分水岭。跨过这道坎之后，很多以前需要手工整理或者用透视表才能完成的事，都能用一条公式自动化掉。花几个小时把它弄明白，往后每年都能省下这些时间。真正值得投入的学习，大多是这个样子：前期有点陡，学会了就一直在用。

常见问题

为什么我的数组公式只算出了第一个结果

老版本里数组公式必须按 Ctrl 加 Shift 加 Enter 输入才会按数组处理，直接回车只算第一个元素。判断方法很简单：看公式栏里公式是否被花括号包着，有就是数组公式，没有就是普通公式。新版动态数组不需要这个操作，普通输入即可。

花括号能手动敲吗

不能。手敲花括号会被 Excel 当成普通文本，公式无法正常工作。花括号是 Excel 在识别出数组公式后自动添加的标记，只能靠输入方式触发。动态数组时代它也不再显示了，因为普通输入就能达到同样效果。

数组公式报值错误是怎么回事

多半是区域大小不匹配。两个数组参与运算时大小必须一致，或者能通过广播扩展。检查公式里所有区域的起止行是否对齐，这种错误往往就差一行。另一种可能是区域里有无法参与运算的内容，比如文本撞上算术运算。

动态数组和传统数组公式有什么区别

功能上等价，使用上差两代体验。传统数组公式要特殊输入、范围要预先选好、复制容易出错；动态数组普通输入、结果自动溢出、形状自动调整。2021 及以后的版本都支持动态数组，往前的版本只能走传统路线。

数组公式能替代数据透视表吗

在特定场景下能，比如需要结果随源数据自动更新的统计表。但透视表在交互探索、多维度切换、大数量聚合上依然更强。两者的关系是互补：固定的、格式要求明确的报表用数组公式；需要随时换角度看数据的用透视表。选哪个取决于你是要做一份成品还是要探索数据。

老版本怎么实现动态数组的效果

靠 INDEX 配合数组公式返回指定位置，或者用辅助列加普通公式。也可以用多条件聚合的四种写法对比里介绍的 SUMPRODUCT 系列写法，它们完全不依赖新版本，只是写起来麻烦一些。选哪种取决于你的版本环境和可接受的复杂度。