一起聊聊Excel中的数组

有人说数组是Excel函数的精华，也有人说数组是Excel函数的糟粕，男说男有理女说女有爱，各有各的理由——所以王源说世间没有真正的感同身受。但不管怎么说吧，数组作为Excel函数进阶后的知识，是相当部分Excel使用者都绕不过去的一条……河。

我想数组多少还是要学一下的。
那么什么是数组？什么是数组运算？什么是数组公式？三者之间有何神秘关系？数组公式的概念为什么在坊间屡次引发了场面壮观的口水战？牵牵手，跟我来。

什么是数组

简单而言，数组是元素的有序集合。

元素这里可以理解为数据。不知道你是否还记得，在本系列函数教程前面章节，咱们讲了什么是Excel数据。元素可以是数据类型中的任意：数值、文本、逻辑值、错误值均可。
有序集合指的是数组内的元素排列是有顺序的。
我举个小栗子。
={1,2,"星光",TRUE}
这是一个常量数组。包含了数值(1,2)，文本("星光")，逻辑值(TRUE)，3种类型的元素。元素有序排列，第1个是1，第2个是2，第3个是星光，第4个是TRUE。
如果我们要取出该数组内第3个元素的值，可以使用INDEX函数。
=INDEX({1,2,"星光",TRUE},3)
有序是数组运算的基础，如果数组元素是无序的，我们很难高效控制数组间元素的运算和准确获取运算后的完整结果。
……

数组的分类

人按性别可以分为男人、女人；按年龄可以分为老人、中年、青年、少年等。数组公式按照不同的标准也可以分为不同类别。

按照维度，数组可以分为一维数组、二维数组和多维数组。数据只有一行或一列的数组被称为一维数组，数据有多行多列的数组被称为二维数组。由不同平面二维数组构成的数组被称为多维数组（或者说多维引用），Excel函数数组最高只有三维。
按照数据来源，数组又可以分为常量数组、内存数组和区域数组。
……量数组顾名思义是由常数构成的数组，典型标志是在首尾有一对大括号{}

例如咱们上面举的例子：
={1,2,"星光",TRUE}
常量数组只能由常数组成，不能存在单元格引用、嵌套函数等形式。
比如下面的常量数组中存放了sum函数，是规则绝对不允许的，钱再多权再大都没用——暂时。
={2,sum(a2),1}
在Excel中输入上面的公式，系统会显示以下错误信息。

内存数组是指在函数运算过程中数据保存在内存里的数组，它是某个公式的计算结果，然后又嵌套在另一个公式中继续参与运算。
这话听起来有点儿绕，我举个例子。

如上图所示，我们需要计算人头售出总金额。H1单元格公式如下：
=SUMPRODUCT(B2:B5*C2:C5)
B2:B5*C2:C5是指B2:B5的人头单价分别乘以C2:C5的数量，得到的是4个元素的数组{12;70;30;15}，但这4个元素保存到哪儿去了呢？显然没有保存到单元格中，而是保存到了电脑的内存里。它作为SUMPRODUCT函数的一个参数，继续参与公式运算，像这样的情况，我们称之为内存数组。区域数组就比较简单，是在公式中对单元格区域的引用，比如公式：
=A2:A10

什么是数组运算

既然有数组，也就有数组和数组之间的运算，这就好比既然有男人，必然就有……你猜？——数组运算又被称为多项运算，是指同时对一组或多组数组内的元素执行运算。既然是运算，必然有规则，这就好比既然有战争，必然就有……你再猜？——数组运算规则总结起来有以下几种情况。

……

第1种情况是一维数组和单值之间进行运算。

前面咱们讲过，一维数组是指单行或单列元素构成的数组。
单行数组又被称为水平数组，例如单元格区域A1:D1，常量数组{1,2,3,4}。
单列数组又被称为垂直数组，例如单元格区域A1:A4，常量数组{1;2;3;4}。
很明显，单行数组由多列数据构成，比如A1:D1，有A/B/C/D四列；单列数组由多行数据构成，比如A1:A4，有第1/2/3/4四行。在常量数组中分号代表行（分号有两层，只有行才分层，所以它代表行），逗号代表列。

数组和单值之间执行多项运算，必然返回同尺寸的数组。

举个例子，如下所示的数组公式，单行数组{1,2,3,4}乘以2。
={1,2,3,4}*2
运算过程是数组中的每个元素都乘以2。1*2、2*2、3*3、4*2，结果为内存数组{2,4,6,8}。
再举一个实战的小案例。

如上图所示，由于B列的工资为文本值，直接SUM函数求和结果会返回0。B6单元格改用公式如下。
=SUMPRODUCT(B2:B5*1)
B2:B5*1，B2:B5是一个垂直数组，运算过程是B2:B5中的每一个元素均乘以1，通过数学运算将文本型数值转换为纯数值。此时生成一个内存数组{900;100;9999;99999}，SUMPRODUCT再执行求和运算返回正确结果。
如果把数组和单值运算比作男女关系，单值就像皇帝，数组是它的后宫，数组内每一个人都要给皇帝生娃娃……所以说皇帝这个职业确实很——辛苦呐！

……
第2种情况是一维数组和一维数组之间的运算。
由于一维数组有两种形式，这种运算就又产生了两种情况。一种是同方向一维数组之间的运算。比如垂直数组和垂直数组或者水平数组和水平数值间的运算。
这种情况的数组运算是比较单纯的男女关系，两个数组内的每个元素按照先后顺序、一夫一妻制结婚。
举个例子。

以上图所示数据为例。计算商品售出总金额。公式如下：
=SUMPRODUCT(B2:B5*C2:C5)
B2:B5是垂直数组，C2:C5也是垂直数组，其运算过程中是B2*C2、B3*C3、B4*C4、B5*C5……你看，是不是按照先后顺序、一夫一妻制的规则进行运算的？它的计算结果是4个元素的垂直数组。
{12;70;30;15}
这规则似乎看起来让单身汪感到特别美好，但其实并不尽然。男多女少或者女多男少的情况了解一下？
比如公式：
=SUMPRODUCT(B2:B5*C2:C4)
B2:B5是4个元素构成的垂直数组，C2:C4是3个元素构成的垂直数组；如果前者是男人，后者是女人，那就属于男多女少了。

这个时候B2跟C2结婚了，B3跟C3结婚了，B4跟C4结婚了，那B5怎么办呢？女人们都嫁了，剩下一个男人怎么办？总不能送个充气娃娃强制脱贫吧？——没办法，返回错误值补位吧。
因此它的计算结果也是4个元素构成一个内存数组，只不过最后一个元素是错误值。
{12;70;30;#N/A}
同样的道理，公式：
=SUMPRODUCT(B2:B4*C2:C5)
B2和C2结婚，B3和C3结婚，B4和C4结婚。剩下一个女孩C5，但没有男孩了，怎么办呢？没办法，男女平等，也返回错误值补位吧。
{12;70;30;#N/A}
……
总结一下。同方向一维数组之间的运算，必须具有相同的元素数量，否则结果中会产生错误值进行补位，它的运算结果依然是同向的一维数组。

外一种情况是不同向的两个一维数值之间的运算，也就是垂直数组和水平数组之间的运算，这种情况男女关系比较复杂，身经百战的居委会大妈看了都得哭。
我举个例子。

如上图所示的数据。B6单元格输入公式：
=SUMPRODUCT(A2:A4*B1:C1)
A2:A4是3个元素构成的垂直数组，B1:C1是2个元素构成的水平数组，它俩之间做乘法运算，结果返回了一个3行2列的二维内存数组：

{4,5;8,10;12,15}

它的运算过程是这样的。垂直数组中的每个元素分别和水平数组中的每个元素作运算，如果把这比作男女关系——委实有点混乱，所以还是先别打这个比方了。
按照有序原则，首先运算的是A2。
A2先和B1运算，也就是A2*B1，然后再和C1做运算，也就是A2*C1。

然后轮到A3。
A3先和B1运算，也就是A3*B1，然后再和C1做运算，也就是A3*C1。

最后轮到A4。
A4先和B1运算，也就是A4*B1，然后再和C1做运算，也就是A4*C1。

……
有朋友说，这不是一夫多妻制吗？同志，我说你是不是对一夫多妻有啥误解？你的意思是A2娶了两个老婆，B1和C1；新婚第2天，A2出门遇见了A3，不聊不知道一聊才知道，原来A3昨天也新婚了，也娶了两个老婆，这俩老婆也是B1和C1，你说这尴尬不尴尬？

……
总结一下，两个不同方向的一维数组，也就是X行垂直数组和Y列水平数组进行运算，其运算方式是垂直数组中每一个元素分别与水平数组的每一个元素一一运算，返回X行Y列的二维数组。

……

闭上眼睛，休息一下。

……

第3种情况是一维数组和二维数组之间的运算。
再说一下什么是二维数组，由多行多列元素构成的数组是二维数组，比如单元格区域B2:D4是一个3行3列的二维数组。
一维数组和二维数组之间的运算是什么情况呢？
还是举个例子。

如上图所示数据为例，A1:B4是不同次数考试成绩的加权系数，D1:G4是该班成绩明细，如果需要计算所有人考试成绩加权系数后的总分，可以使用以下公式。
=SUMPRODUCT(B2:B4*E2:F4)
B2:B4是一维垂直数组，E2:F4是3行2列的二维数组，两者之间做乘法运算。
根据有序原则，首先运算的是B2。
B2先和E2运算，B2*E2，然后再和F2运算，B2*F2。

B3先和E3运算，B3*E3，然后再和F3运算，B3*F3。

B4先和E4运算，B4*E4，然后再和F4运算，B4*F4。

最后SUMPRODUCT函数执行汇总求和。

这就是典型的一夫多妻制了，当然，也可以说是一妻多夫制。按照先来后到的顺序，B2娶了两个老婆E2和F2、B3也娶了两个老婆E3和F3……
有些男同胞又蠢蠢欲动，以为这是盛世复兴的景象……那可就又未必了，什么是先来后到了解一下？
比如公式：
=SUMPRODUCT(B2:B5*E2:F4)
B2:B5是4行元素的垂直数组，E2:G4是三行两列元素的二维数组。前者比后者多了一行；当B2娶走了E2和F2，B3娶走了E3和F3，B4娶走了E4和F4……剩下一个B5，没有老婆可娶了，怎么办呢？——老办法，返回错误值补位吧。
{48,83;27.6,19.2;10.4,18.2;#N/A,#N/A}
错误值是无法统计求和的，因此这条SUMPRODUCT函数最后会返回了错误值。
总结一下，一维数组和二维数组做运算的过程是一维数组的每个元素和同方向二维数组的每个元素一一对应运算，最后结果返回一个二维数组。如果两个数组相同方向的元素数量不一致，会产生错误值补位。
……
第4种情况是二维数组和二维数组之间的运算。
二维数组相互运算，要求两者具有完全相同的尺寸，也就是行数和列数都要相同。运算的过程是将每个相同位置的元素两两对应，返回一个与它们尺寸一致的二维数组结果。

——如果两个数组的尺寸大小不一样呢？当然还是老办法，以错误值进行补位。
举个例子。
如上图所示，A1:D4是成绩表；需要汇总大于等于60分的成绩之和。

公式如下。
=SUMPRODUCT((B2:D4>=60)*B2:D4)
公式首先运算B2:D4>=60部分，B2:D4是3行3列的二维数组，60是单值，因此这是二维数组和单值做比较运算，数组的内每个元素分别和60分比大小，大于等于60返回TRUE，否则返回FALSE，结果返回一个3行3列的由逻辑值构成的二维内存数组。

{FALSE,FALSE,TRUE;FALSE,TRUE,TRUE;TRUE,TRUE,TRUE}
将该数组映射到单元格中，如下图所示。

公式继续运算，将这个二维数组和B2:D4做乘法运算，这就属于两个二维数组之间的运算了。
按照有序原则，系统会将两个数组相同位置的元素一一运算，是的，又回到一夫一妻制。A数组的第1个元素和B数组的第1个元素结婚，A数组的第2个元素和B数组的第2个元素结婚……直至两个数组的元素用完；如果两个数组元素不一样多，照例用错误值补位。
TRUE在数学运算中视为1，FALSE视为0，因此这一步返回内存数组如下。
{0,0,91;0,74,89;65,80,60}

最后SUMPRODUCT执行求和运算，返回结果459.

……
第5种情况是多维数组的运算。这个一般人一般时候也用不上，咱们还是放到引用和多维引用篇里再讲。

话筒，做个全面总结：

数组和单值做运算，是后宫和皇帝的关系，人人都得给皇帝生孩子；两个相同尺寸的数组做运算，会坚持一夫一妻制，比如水平数组和水平数组、二维数组和二维数组等；一维数组和二维数组作运算，那就是一夫多妻制（或者说一妻多夫制）；而水平数组和垂直数组作运算，彻底毁三观，它奉行极端的多夫多妻制……

什么是数组公式

什么是数组公式？这个问题很有意思，非常有意思，在ExcelHome论坛，它引发了一次又一次口水战。

话说先前咱们聊了什么是数组运算；有的函数天生就默认执行数组运算，比如SUMPRODUCT、LOOKUP等；但绝大部分函数并不默认执行数组运算，比如SUM，但有时候我们又需要它们执行数组运算，怎么呢？——可以强制它们执行。
举个例子。

如上图所示的数据，需要统计人头销售总金额，可以使用公式：
=SUMPRODUCT(B2:B5*C2:C5)
也可以使用数组公式：
{=SUM(B2:B5*C2:C5)}上面这个公式前后的大括号不是手工输入的，而是在公式编辑结束时，同时按下<Ctrl+Shift+Enter>组合键后系统自动产生的。

<Ctrl+Shift+Enter>也被称为数组三键。它是数组运算的启动键，等于告诉系统，老子是数组公式，不是普通函数，你丫的给我执行多项运算。
如果不按数组三键，而是直接输入普通公式：
=SUM(B2:B5*C2:C5)
结果会怎么样呢？
SUM函数只会按照正常模式运算，也就是只运算每个数组的首个元素，返回B2*C2的结果。
这就是数组三键的意义。

一切看起来都很正常……
然后口水战就来了。
……
什么是数组公式？按照正常的思维逻辑，执行了数组运算的就是数组公式，对不对？
但微软公司说，不不不，只执行数组运算还不能算数组公式，做人得有仪式感，做函数也是一样的，什么是数组公式？执行了数组运算，同时公式自身还得包括在大括号中的才算。

微软这么说，也有一点道理。就像前面所说，虽然有的函数天生就默认执行数组运算，但绝大部分函数确实没有这个特性，它需要数组三键才能打开数组运算的开关。
于是问题就来了。比如说，下面这个公式……
=SUMPRODUCT(B2:B5*C2:C5)
它默认执行了数组运算，它是不是数组公式？微软说不是，因为它没有包含在一对大括号中。
那好，我们使用数组三键给它加上大括号。
{=SUMPRODUCT(B2:B5*C2:C5)}
这样算数组公式了吗？微软说，是的，这就符合我们制定的数组公式的概念标准了。
但这个公式和上面的公式两者的运算过程有什么区别吗？没有，没有任何区别…
于是口水就飞起来了。
甲：我软，你说你这是不是有病？
乙：我没病，我顶多有点仪式感。
甲：你这是典型的形式主义。
乙：请不要将仪式感和形式主义混为一谈。
甲：你就是有病。
乙：卧槽，产品是我家的，我说了算，我的地盘我做主，懂不？

……

什么是区域数组公式

咱们前面讲过，数组公式返回的是一组元素；但是Excel一个单元格只能显示数组元素中的一个结果（默认为数组中的首个元素）。

比如，我们在D2单元格输入数组公式{=B2:B5*C2:C5}，尽管该数组公式返回了多个结果，但D2单元格只显示了B2*C2的值。

如果需要显示数组公式的全部元素呢？——可以使用区域数组公式。
那么什么是区域数组公式？在一个单元格中输入的公式被称为数组公式，在多个单元格中输入同一数组公式就被称为多单元格数组公式，也就是区域数组公式。区域数组公式可以有序返回结果数组中的每个元素。
举个简单的例子（以后见面请尊称我举栗子大力星光上士）

如上图所示的表格，选中D2:D5单元格区域，在编辑栏编写公式=B2:B5*C2:C5，然后按数组三键结束公式输入，也就在D2:D5区域内输入了同一条数组公式，这就是区域数组公式。

该公式返回一个内存数组{12;70;30;15}，系统会将数组的每个元素依次显示在D2:D5区域中。
需要说明的是，使用多单元格数组公式时，所选择的单元格个数必须与公式最终返回的数组元素个数相同，如果所选区域单元格的个数大于公式最终返回的数组元素个数，多出部分将显示为错误值。老规矩，人头不够，错误值来凑。

比如选中D2:D7输入数组公式=B2:B5*C2:C5，D2:D7有6个房间，返回的内存数组有4个元素，多出的房间显示为错误值。
但如果所选区域单元格的个数小于公式最终返回的数组元素个数，则结果会显示不完整，毕竟规则是先来后到，一人一个单元格。
除此之外，区域数组公式还有一个特点，它们作为一个整体，系统不允许单独更改其中一个单元格的公式。

如果需要修改或删除，必须整体处理。

我严重怀疑区域数组公式不但没听说过枪打出头鸟这句谚语，而且从小就是唱着团结就是力量一起长大的。

比如我们选中A1:D1区域，编写以下公式，并按数组三键结束。
={"ID","姓名","地址","电话"}
之后A:D列的数据就不能单独删除了，否则系统会弹出警告信息。

嘿！这是不是也是一种保护数据结构的特别手段呢？

那么有没有什么快捷方法选取当前全部数组公式呢？
可以选择任意一个存在区域数据公式的单元格，然后按<Ctrl+G>快捷键调出定位对话框，依次单击【定位条件】→【当前数组】功能，如下图所示。

鲁迅先生说，能够一口气看到这里的，都是敢于面对惨淡函数人生的真正勇士~今天，你，勇士了吗？

原载公众号：Excel星球

图文作者：看见星光