SPILL! 通常是什么原因？

溢出区域被其他内容、合并单元格、表格或另一张溢出结果挡住。

FILTER 的条件里，和 + 分别代表什么？

表示“且”，+ 表示“或”；条件要加括号。

为什么动态数组公式不能放在 Excel 表格的 calculated column 里？

表格的 calculated column 要求每行返回单值，动态数组会返回一片区域，容易 SPILL!。

FILTER、UNIQUE、SORT、SEQUENCE：Excel 365/2021 动态数组

适用场景

想从明细表里筛出某个地区的数据，结果自动更新；想生成去重名单、按金额排序、取前 10 名；想把多张结构相同的表纵向合并。动态数组让一个公式返回一片区域，结果会自动“溢出”到相邻单元格，不用再按 Ctrl+Shift+Enter。

动态数组在 Microsoft 365 和 Excel 2021 中可用；Excel 2019/2016 没有这些函数，打开文件会看到 _xlfn. 前缀或 #NAME?。

溢出与引用

公式写在左上角单元格，结果自动填满右侧和下方的空白区域，这叫“溢出”。
溢出区域被其他内容挡住时返回 #SPILL!；清空挡住的单元格即可。
引用整个溢出结果用 A2#，例如 =COUNTA(A2#)。
在动态数组公式里，@ 表示只取当前行的值（隐式交集）；旧公式兼容时会自动加 @。
动态数组公式不能放在 Excel 表格（Ctrl+T）的 calculated column 里，需要放在表格外的普通区域。

常用函数

函数	作用	示例
`FILTER`	按条件筛选	`=FILTER(A2:D100,C2:C100="华东","无结果")`
`UNIQUE`	去重	`=UNIQUE(A2:A100)`
`SORT`	排序	`=SORT(A2:D100,4,-1)`
`SORTBY`	按另一列排序	`=SORTBY(A2:A100,D2:D100,-1)`
`SEQUENCE`	生成序列	`=SEQUENCE(10,1,1,1)`
`RANDARRAY`	生成随机数组	`=RANDARRAY(5,2,1,100,TRUE)`
`CHOOSECOLS`	选列	`=CHOOSECOLS(A2:D100,1,4)`
`CHOOSEROWS`	选行	`=CHOOSEROWS(A2:D100,1,3,5)`
`TAKE`	取前/后 N 行	`=TAKE(A2:D100,5)`
`DROP`	去掉前/后 N 行	`=DROP(A2:D100,1)`
`VSTACK`	纵向堆叠	`=VSTACK(表1,表2)`
`HSTACK`	横向拼接	`=HSTACK(A2:B10,D2:E10)`
`TOCOL`	多列转一列	`=TOCOL(A2:C10,1)`
`TOROW`	多行转一行	`=TOROW(A2:C10,1)`

FILTER 示例

假设 A 列地区、B 列产品、C 列金额、D 列日期：

筛选华东的全部列
=FILTER(A2:D100,A2:A100="华东","无结果")

筛选华东且金额大于 10000
=FILTER(A2:D100,(A2:A100="华东")*(C2:C100>10000),"无结果")

筛选华东或华南
=FILTER(A2:D100,(A2:A100="华东")+(A2:A100="华南"),"无结果")

只返回产品列
=FILTER(B2:B100,A2:A100="华东","无结果")

条件之间用 * 表示“且”，用 + 表示“或”。乘除运算优先级高于比较运算，条件要加括号。

UNIQUE 与 SORT

去重名单
=UNIQUE(A2:A100)

只保留出现一次的值
=UNIQUE(A2:A100,FALSE,TRUE)

按金额降序排序
=SORT(A2:D100,4,-1)

先筛选再排序
=SORT(FILTER(A2:D100,A2:A100="华东"),4,-1)

取销售额前 5 名
=TAKE(SORT(FILTER(A2:D100,A2:A100="华东"),4,-1),5)

按另一列的顺序排序
=SORTBY(A2:A100,C2:C100,-1)

SORT 的第二个参数是排序依据的列号（相对数组），第三个参数 1 升序、-1 降序。UNIQUE 默认把空白当成一个值，需要排除空白时先 FILTER(A2:A100,A2:A100<>"")。

合并多表

纵向合并两张结构相同的表
=VSTACK(Sheet1!A2:D100,Sheet2!A2:D100)

横向拼接
=HSTACK(A2:B10,D2:E10)

多列转一列，忽略空白
=TOCOL(A2:C10,1)

VSTACK 要求各表列数一致；列数不同时，缺少的单元格会补 #N/A。先统一列顺序和列数，再合并。

易错点

#SPILL!：溢出区域被内容、合并单元格、表格或另一张溢出结果挡住；清空右侧/下方区域。
#CALC!：FILTER 没有匹配结果且没写 if_empty；加上第三个参数。
条件数组大小不一致：FILTER 的 include 参数要和数组的行数或列数对应，否则 #VALUE!。
UNIQUE 把空白算进去：先 FILTER 排除空白，或接受一个 0 值并手动处理。
表格里不能用溢出：Excel 表格的 calculated column 要求单值；把动态数组放到表格外。
RANDARRAY 是易失函数：每次重算都会变，不适合做需要固定的抽样结果；复制后粘贴为值。
整列引用：FILTER(A:A,...) 会处理上百万行，数据量大时明显变慢；限定实际范围。
版本兼容：Excel 2019/2016 打开会显示 _xlfn.FILTER 或 #NAME?；共享前确认对方版本，或改用透视表/辅助列。

参考链接

Microsoft Support: Excel

读完自测

合上页面，先回答，再展开对照。

#SPILL! 通常是什么原因？
FILTER 的条件里，* 和 + 分别代表什么？
为什么动态数组公式不能放在 Excel 表格的 calculated column 里？

答案

溢出区域被其他内容、合并单元格、表格或另一张溢出结果挡住。
* 表示“且”，+ 表示“或”；条件要加括号。
表格的 calculated column 要求每行返回单值，动态数组会返回一片区域，容易 #SPILL!。