90%的人没用过!Excel里的#能让公式范围自动更新!

Hi，我是咸鱼。

打开同事发过来的Excel表格，发现公式写着=SUM(A2#)，这是手滑多打了一个#？

NONONO，这可不是错误。它叫“溢出范围运算符”。

今天这篇教程，我们一起把#学透。你会发现，数据加几行、删掉几行，公式都不用动，结果自动跟着变。

我们先从“动态数组”说起。

动态数组最早于Microsoft 365引入，Excel 2021及以后的版本永久支持。一个公式一次返回的多个结果，能自动“溢出”到相邻的单元格。不需要像以前，得先选中和返回结果数量一样的单元格，再同时按下Ctrl+Shift+Enter。

比如，在A1输入：

回车后，会自动在A1:A5返回1~5的序号。

SEQUENCE、UNIQUE、SORT、SORTBY、FILTER等等这些函数，天生就是为动态数组设计的函数——它们能返回多个结果，自动溢出到下方和右边的单元格。

此外，有一些老函数：像VLOOKUP、SUMIF、MATCH这些，平时一次只返回一个值。但在支持动态数组的Excel版本里，给它们的参数里塞个数组，它们也能"觉醒"，自动溢出多个数据。

如果对=SEQUENCE(5)返回的结果求和，不用写=SUM(A1:A5)，只要写：

A1#的意思就是：把A1单元格开始的整个动态数组，全部给我。不管数组变大变小，它都跟跟着变。

划个重点：这个写法只对动态数组溢出的区域有效。

如果A1是你手敲的内容，那么=A1#返回的是#REF!错误。

【要求】

把左边的表格统计成右边的部门+小计，并且，未来在左边下方新增数据，右边自动更新部门和小计信息。

【解析】

• UNIQUE的功能是返回所选区域的唯一值；

• C2:.C100里的点：是函数TRIMRANGE的简写形式（也叫“语法糖”），表示裁剪C2~C100区域下方的全部空白行。这样既能覆盖后续新增数据，又不会把下面空白单元格都算进去，多出一个碍眼的0。

↑如果冒号后面不加点，因为下方有空单元格，结果会有个0

第2步，在H2单元格输入：

【解析】

第1参数：C:.C，第3参数E:.E的原理同上。引用整列，同时自动裁剪下方空白区域。

就这么简单，搞定啦~

1.如果输入公式后，返回了#SPILL!错误，代表数据溢出区域有其他内容，溢出的数据放不下，就会报错。只要清除遮挡数据，就能正常返回结果！

2.以上提到的“语法糖”写法，需要Microsoft 365和新版的WPS才支持哦！