最新

Excel 怎么用动态数组函数一次算出多行结果

一个公式只出一个结果的时代过去了。讲清 FILTER、UNIQUE、SORT 怎么铺开一片结果,溢出报错和旧版本打开后失效怎么处理。

一个公式只出一个结果,是很多人对表格的固有印象。动态数组把这层限制拆掉了:写好一个筛选或者去重公式,结果会自动往下往右铺开一片区域,公式只写在左上角那一格。省事的同时也带来新问题,结果被挡住会报错,溢出区域不能随便塞内容。

入口在哪

没有单独的开关,动态数组是函数自身的行为。只要版本支持,直接在单元格里输入公式就会溢出。

  • 支持版本:Microsoft 365、Excel 2021,以及网页版;更早的 2019、2016 只能用传统数组公式。
  • 写公式的位置:选一个四周留空的单元格,右边和下边至少空出结果可能占的范围。
  • 判断是否支持:在空格里输入一个会返回多个值的公式,能自动铺开就是支持。

具体步骤

  1. 先想清楚要几列。结果按公式返回的结构铺开,列的顺序由函数决定,不是拖出来的。
  2. 筛选用 FILTER:给出数据区域、条件、以及找不到结果时显示什么。条件写成一列一列的判断,不要写成一整段文字。
  3. 去重用 UNIQUE:把一列丢进去,重复项自动合并,顺序按首次出现。需要按字母排,就在外面再套一层排序。
  4. 排序用 SORT:指定区域、按第几列排、升序还是降序,比手动点排序更适合会变的数据源。
  5. 公式只写在左上角。写完按回车,看结果有没有自己铺开,不要手动往下拖填充。
  6. 要给结果做后续计算,引用时在区域后面加一个井号,表示整个溢出区域,数据增减时会自动跟着变。

几个坑

  • 溢出区域被占。右边或者下边有内容,公式会报溢出错误,把挡路的格子清空就行,不是公式写错了。
  • 手动往下拖。拖动会把一个公式复制成很多份,结果互相覆盖,也失去了随数据更新的意义。
  • 整列引用。拿整列做筛选条件,计算量暴涨,文件明显变卡,尽量框实际数据范围。
  • 旧版本打开。用新版做好的表发给用旧版的人,结果会退化成第一个值,看起来像数据丢了。
  • 嵌套套错层。外面套排序时引用的是内层的结果区域,顺序写反会得到意料之外的列。

怎么验证

先看溢出:结果区域四周有没有一圈细线框,有就说明整片来自同一个公式。

再看联动:在源数据末尾加一行符合条件的记录,结果应该自动多出一行;删掉一行,结果跟着少一行。不联动,多半是公式被拖成了多份。

最后抽查两三条:手工核对某一行是否符合条件、是否重复、排序是否正确,比整体扫一眼可靠。

常见问题

为什么公式只出了一个结果

三种可能:版本不支持、公式外面套了只返回单值的函数、或者结果区域被挡住。先换个空白区域试同一个公式,能铺开就说明是位置问题。

结果能不能固定成普通数据

可以。复制整片结果,原地粘贴为值。适合发给别人或者做存档,代价是不再跟随源数据变化。

多个公式能不能互相引用

能,把上游结果当作下游的输入区域即可。注意别让两个公式的溢出区域重叠,否则会互相报错。

一句话总结:动态数组把一个公式一个结果变成一片结果,记住只写在左上角、四周留空、引用加井号,基本就不会踩坑。