数组溢出：一个公式填满一片格子

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

在动态数组出现之前，Excel 有一条铁律：一条公式待在一个单元格里，返回一个值。想让一列数据都按同一规则计算，就得先把公式写在第一个格子，再往下拖或者双击填充柄。想让结果自动增减，就得写复杂的动态区域，或者靠 VBA 帮忙。更麻烦的是老式数组公式，必须先选中一整片区域，输入公式，再按 Ctrl+Shift+Enter 三键结束，公式外面套上大括号，之后任何一次编辑都要重新按三键，稍不留神就只改了一个格子。

动态数组把这条铁律废掉了。现在一条公式可以返回一组值，Excel 会自动把它们铺到相邻的单元格里，这个铺开的过程就叫溢出。你在 B2 输入 =UNIQUE(A2:A500)，如果源数据里有 87 个不重复值，B2 到 B88 会一次性被填满，不需要任何拖动，也不需要预先选中区域。源数据一改，结果的行数跟着变，多了就往下延伸，少了就自动收回。

这个变化的深度远超“少拖一次鼠标”。它意味着公式的输出第一次有了“规模”这个概念：一个公式可以生产一张表，而这张表还能被另一个公式当作整体输入。于是做报表的方式从“先建好框架再往里填”变成“让公式自己长出框架”，很多过去必须靠透视表或者辅助表才能完成的任务，现在一条公式就能生成最终结果。

要判断自己的版本支不支持，最简单的办法是在任意单元格输入 =SEQUENCE(5)，回车后如果竖着出现 1 到 5，就说明支持；如果只出现一个 1，说明是老版本，它会按隐式交集的规则只取第一个值；如果报错，那就更明确了。绝大多数 Microsoft 365 订阅版和 Excel 2021 及以上都支持，Excel 2019 及更早版本无缘。

理解溢出的另一个关键是区分“公式所在格”和“结果所在格”。公式永远只存在于左上角那个格子里，其余格子显示的是结果，公式栏里是空的，选中它们时公式栏会显示灰色提示。这个设计保证了结果的唯一来源，也让整片区域的维护成本降到最低：想改逻辑，永远只需要改一个格子。

溢出区的规则：谁能占位，谁会挡路

溢出区域是一个整体，它有几个必须知道的性质。第一，只有左上角的那个单元格可以编辑，其余格子显示为浅色，双击会跳回左上角，想改结果只能改源头公式。第二，溢出区域不能被部分删除，选中整片按 Delete 无效，必须选中左上角单元格再删。第三，溢出区域会随源数据自动伸缩，你不用管它现在有多大。

最容易遇到的问题是 #SPILL! 错误，它的意思是“我想铺开的这片地方被人占了”。常见原因有五种。一是溢出路径上有非空单元格，哪怕只是一个空格也会挡路，把它删掉即可；二是溢出区超出了工作表边缘，比如在第 1048576 行附近写了一条会溢出上千行的公式；三是公式写在正式表格对象（Ctrl+T 创建的那种）内部，这类表格不支持溢出，必须换成普通区域；四是溢出结果大小不确定，比如 =SEQUENCE(RANDBETWEEN(1,100)) 这类每次重算行数都变的写法；五是溢出区与合并单元格相交，合并单元格是溢出的天敌，能拆就拆。

排查时可以照着下面这张表逐项检查，通常三十秒内就能定位问题，比盯着虚线框发呆有效得多。

溢出方向也有讲究。竖着排列的数组向下溢出，横着排列的向右溢出，二维的则同时向下和向右。构造数组常量时用逗号分隔表示横向、分号分隔表示纵向，={"甲","乙","丙"} 是一行三列，={"甲";"乙";"丙"} 是三行一列。这个规则在手工构造溢出结果时经常用到。

还有一条隐藏规则：溢出区域不能被单独格式化成不同的样式，条件格式可以覆盖，但手动改某一格的字体后，一旦结果行数变化，格式不会跟着结果走。想在溢出结果上做视觉区分，优先用条件格式而不是手动设置。

五个典型场景

场景一，去重加排序一步到位。过去要做“按销售额从高到低列出不重复的客户名单”，要么用透视表手动刷新，要么加两列辅助列。现在写 =SORT(UNIQUE(客户列),2,-1)，如果还要只保留华东区，就再套一层 =SORT(UNIQUE(FILTER(客户列,区域列="华东")),2,-1)。三层函数嵌套，一条公式，结果自动伸缩，源数据新增客户后名单立刻更新。

场景二，生成序列与日历。=SEQUENCE(12) 造 1 到 12 的月份，=SEQUENCE(1,7,起始日期,1) 造一周的日期，=DATE(2024,SEQUENCE(12),1) 造一年十二个月的第一天。这类需求过去靠填充柄，现在靠公式，而且可以被其他公式引用，不会因为插入行而错位。

场景三，多条件筛选后直接输出整张表。写 =FILTER(A2:F5000,(C2:C5000="华东")*(F2:F5000>10000),"无匹配")，一整块六列的结果直接铺开，包含表头在内的所有字段一起出来，不用逐列写公式。筛选条件一变，整张表跟着变。

场景四，一次返回多个统计量。利用数组常量驱动溢出，=COUNTIFS(区域,{"甲","乙","丙"}) 会横向溢出出三个计数结果；=SUMPRODUCT((MONTH(日期列)={1;2;3})*金额列) 会纵向溢出出前三个月的合计。这类写法把过去要写多条公式的需求压缩成一条，而且天然对齐，不会错位。

场景五，把溢出结果当参数继续计算。假设 B2 里是 =UNIQUE(客户列) 的溢出结果，那么 =COUNTA(B2#) 统计个数，=SORT(B2#) 再排序，=SUMIFS(金额列,客户列,B2#) 能为每个客户各算一个合计并整体溢出。溢出引用符在这里起了关键作用，下一节详细说。

溢出引用符 # 与隐式交集 @

溢出区域是一个整体，怎么引用它？答案是井号，也叫溢出引用符。如果 B2 里的公式溢出到了 B2:B88，那么在任何地方写 =B2# 就等价于引用 B2:B88 这个动态范围。它的价值在于“不用知道结果有多大”：结果变成 120 行，所有引用 B2# 的公式自动跟随，不需要改任何区域地址。

有了它，很多过去必须靠动态命名区域才能实现的写法变得极其简单。=COUNTA(B2#) 得到结果行数，=MAX(B2#) 取最大值，=XLOOKUP(关键字,A2#,B2#) 在两个溢出区之间查找，=SUM(B2#*C2#) 做逐元素相乘再求和。甚至可以把溢出区直接设成数据验证的下拉来源，做一个能自动增长的选项列表。

井号只能接在溢出区左上角的地址后面。写 =B5# 而 B5 不是溢出区的起点，Excel 会报错。另外井号引用不能用于需要固定区域的参数，比如图表数据源的某些设置，遇到限制时改用名称管理器定义动态名称再引用。

再来说 @。这个符号叫隐式交集运算符，它的作用是“只取对应的那一个值”。在老版本 Excel 里，数组公式遇到需要单个值的场合会自动取当前行对应的元素，这个行为叫隐式交集；新版 Excel 为了兼容旧文件，会把老公式改写并加上 @，于是你打开一个从 Excel 2010 传下来的文件时，可能会看到 =@SUM(...) 这种奇怪的写法。

多数情况下你不需要主动写 @，Excel 会在必要时自动添加。需要手动使用的场合很有限：当你希望某段返回数组的计算在当前行位置只取一个值时，或者想消除某个公式的溢出行为。看到公式里凭空多出一个 @ 也不必惊慌，它不是错误，只是兼容标记，删掉它之后公式可能反而会变成溢出行为。

老版本怎么办，以及性能提醒

如果你的 Excel 是 2019 或更早，动态数组一律不支持，替代方案有三条。第一，用老式数组公式：先选中要放结果的整片区域，输入公式，按 Ctrl+Shift+Enter 结束，公式会显示在大括号里，缺点是结果行数固定，数据变了要手动重选区域重新输入。第二，用辅助列加填充，最土但最稳，也最容易交接。第三，升级版本或者改用支持动态数组的在线表格，这是最省事的建议，只是不一定由你决定。

性能方面要提醒两点。第一，溢出公式会随着源数据增长而变大，如果源数据上万行，溢出结果也可能上万行，其内部的排序和去重操作会明显消耗资源。第二，避免在溢出公式里嵌套易失函数，比如把 TODAY() 或 RAND() 放进 FILTER 的条件里，那样每次工作表有任何改动都会触发整片重算。关于易失函数的完整讨论见易失函数与性能陷阱。

还有一条协作建议：把溢出公式放在单独的工作表或者独立区域，别和手工录入的数据混在一起。溢出的特性决定了它周围必须留白，如果同事在旁边随手敲了一个字，#SPILL! 就来了，而他会以为是你的公式坏了。留出足够空白，并在旁边标注“此区域由公式自动生成，请勿录入”，能省掉大量解释工作。更彻底的做法是给这片区域加一个显眼的底色，并在工作表保护里把这片区域设为只读，从物理上杜绝误操作。团队共用模板时，这一步几乎必做。

还有一个细节值得提前想清楚：溢出结果要不要转成静态值。定稿交付的报表建议转，用复制加选择性粘贴为数值，断开源数据依赖，文件也更好传；日常分析的过程表则不要转，保留自动更新能力，否则下次数据更新时你会忘记这里还留着一份手工粘贴的死数据。判断标准就看这份表以后还改不改。

版本兼容同样是绕不开的话题。动态数组是 Microsoft 365 与 Excel 2021 才有的能力，WPS 的部分版本支持但表现不一致，如果表格要在多种环境里打开，务必准备一份降级版本。完整的函数与版本对照表见版本对照：新函数在老 Excel 里怎么办。

最后说说和 LET、LAMBDA 的配合。溢出公式往往比较长，用 LET 把中间步骤命名之后可读性会好很多，比如先把 FILTER 的结果命名为“数据”，再做排序和汇总。更进一步，可以把整套逻辑封进 LAMBDA，注册成一个有名字的函数，之后一张表只需要写一条调用语句。相关写法见LET：给长公式里的中间值起名字。

常见问题

为什么我的公式只返回一个值

两种可能。第一种是版本不支持动态数组，Excel 2019 及更早版本会把溢出行为退化成隐式交集，只取对应位置的那一个值，而且不报错，最容易误导人。判断办法是输入 =SEQUENCE(5) 试试，如果只出现 1 就确认是版本问题。第二种是公式本身确实只返回单个值，比如外面套了 SUM、COUNTA 这类聚合函数，把数组压成了一个数字，这时要检查是不是多套了一层聚合。

#SPILL! 报出来了，怎么快速定位

Excel 会在出错时用一个虚线框标出它想占用的区域，顺着虚线框找，里面那个挡路的格子就是元凶。常见情况是里面有一个看起来空白、实际含有空格字符串的单元格，选中它按 Delete 就能解决。如果虚线框超出了工作表边界，就得把公式换个位置或者缩小结果规模。如果公式在 Ctrl+T 创建的正式表格里，把表格转成普通区域即可，表格对象不支持溢出。

溢出区和普通数组公式能混用吗

能，但要小心行为差异。老式数组公式（用 Ctrl+Shift+Enter 结束的那种）占用的是固定区域，它不会溢出，也不会给井号引用提供动态范围；溢出公式则完全相反。如果一张表里两种写法并存，最可能出现的问题是老式数组公式所在的行挡住了溢出路径，导致 #SPILL!。建议在新版本里一律改用溢出写法，把老式数组公式逐步替换掉，替换时记得先扩大老区域再删除，避免提示“不能更改数组的某一部分”。

溢出结果能不能单独改其中一格

不能，这是设计上的限制，也是优点。溢出区是一个整体，任何单格的修改都会破坏“结果完全由公式决定”这个前提，Excel 直接禁止了这种操作，编辑时会自动跳回左上角。想改某一格的显示，只能在源数据上改，或者在公式里加判断逻辑。如果确实需要允许手工覆盖，那就别用溢出，改成公式加手工覆盖两列并列，或者干脆用普通公式逐格填充。

井号引用能不能用在 SUMIFS 里

可以，而且非常好用。假设 A2 里是 =UNIQUE(产品列) 的溢出结果，写 =SUMIFS(金额列,产品列,A2#) 会为每个产品各算一个合计，并整体溢出出同样长度的一列。这比复制公式高效得多，也避免了行数变化时公式不够用的问题。要注意两侧的维度要匹配，如果条件区是竖着的一列，求和结果也会是竖着的一列；想让它横着排，就在外面套 TRANSPOSE 函数。

溢出结果能不能直接做图表数据源

可以，但要绕一步。图表的数据源不接受井号引用这种动态写法，直接填 =Sheet1!A2# 通常会被拒绝。解决办法是先定义一个名称，比如新建名称“图表数据”，引用位置填 =Sheet1!A2#，然后在图表的数据源里填写这个名称。这样图表会跟随溢出区自动伸缩，源数据增加时图形跟着变。要注意名称必须用工作簿级别，且引用的溢出区左上角地址要写全工作表前缀，否则切换工作表时容易失效。

溢出公式会让文件变大吗

一般不会明显变大。溢出区不存储公式，只在左上角存一份，其余格子是计算结果，文件体积的增加主要来自数据本身而不是公式。真正会让文件膨胀的是大量重复的普通公式，以及把溢出结果反复复制成静态值。需要归档时，可以把溢出结果复制后选择性粘贴为数值，这样能断开源数据的依赖，但也失去了自动更新能力，适合定稿版本。