Excel动态数组

大家好，欢迎来到IT知识分享网。

今天，我们来探讨一下Excel中的“动态数组“功能，我们把这个词拆开去理解，数组大家应该很非常熟悉，可以简单的理解为一组值的集合，那动态简单的可以理解为可变的。那动态数组的意思就是可以返回可变化大小的数组。

了解了这个概念后，我们要区分下版本，在早期的版本例如Excel2003、2007至2019.这些我们经常统称为低版本。在这些旧版本中，Excel并没有动态数组的概念。因为旧版本的数组公式始终返回固定的大小，简单来说，不管你的公式返回了多少个值，是一维数组还是还是多维，他只会返回在一个单元格中，只显示数组的第一个值（数组左上角第一个）。你可能听过ctrl shift Enter 。随着版本的升级，在Excel2021 、365，甚至现在流行的国产WPS，都具备动态数组溢出。溢出的意思就是说你返回的结果是一个1列5行的一维数组，那它会自动扩展到其他单元格中，再也不用像以前一样去按F9看公式的计算过程。

一、溢出效果

让我们直观地体验一下Excel中的动态数组功能，特别是它的“溢出”效果,我们只需要在一个单元格中输入公式，使用UNIQUE去除重复值，他会溢出当前单元格，得到一个数组。这里我写完公式直接按回车，并不需要使用上文提到的三建，当鼠标选择溢出的单元格时，会自动有一个蓝色的边框。当点击其他位置的时候，外边框消失。如果你想删除这个溢出的数组，你只需要删除写公式的单元格内容即可，删除其他的单元格溢出的结果是无法删除的。

二、溢出报错

我们继续探讨使用UNIQUE函数去除重复值的动态数组案例，假如我在E4单元格随便输入一个内容。刚刚的公式=UNIQUE(C3:C8)，返回是是红色的溢出结果，如果这时候有一个内容吧我们的结果挡住，那这时候函数就无法得到最终的结果了。就会报错#溢出（图1），在WPS中报错#SPILL!（图2）。那我们如何解决这个错误，我们只需要把挡住我们结果的内容删掉即可，或者说，你把公式复制粘贴到没有挡着的地方。

图1

图2

三、@

@是绝对交集运算符。@是出现隐式交叉的位置。隐式交集就是将多个值减少为单个值。如果是单个值，那没什么可说的，就是返回本身，如果是一个区域，返回的是与公式相同的行或者列上的单元格值。如果是数组返回左上角第一个值。听着有点深奥，我们看下案例就知道了。图3我们输入@F3:F7 ，那当前F3所在的行是第三行，所以的区域是C列，那绝对交集的结果自然是交叉的悟空单元格。图4我们选择的是一行C7:D7，那就是第7行与当前公式单元格C列的交叉，结果就是貂蝉。以上两个是一个区域，那如果是一个数组直接返回左上角第一个，如图5。对区域降序，排序完成取数组的左上角。就是最高分。

图3

图4

图5

四、#

当我们在Excel中使用辅助列来处理数据时，有时会直接引用一个公式返回的内存数组。让我们通过一个简单的例子来理解这一概念,假如我们把成绩批量+5分，形成一个辅助列，这时候我们再用sum求和，我们选择成绩区域时，这时候引用的区域并不是F3:F7，而是F3#，这个#的意思就是当我们写内存数组的左上角单元格引用，在加个#他就会自动识别这个内存数组的区域大小。当我们初学函数的时候，我们可能会拆解复杂的公式，单独展示某部分公式的返回结果，以便更好地理解公式的工作原理。借用辅助列的形式 ，那就会使用到#，或者可以在公示中看到#。如果后续你函数嵌套非常熟练，无需辅助列，那自然也不会用到#。