穿越时间•Excel升级之路连载5：数组的理想照进现实

跟我一起，穿越时间！

回顾一下之前走过的Excel升级之路，前面4期的内容主要是Excel中数据和运算，这是一切应用的基础，很多人总觉得这些东西没有什么用，所以他们直接拿出公式解决实际场景中的实际问题，然后告诉我们这个公式要这么写，却不讲为什么要这么写，也不讲如何根据我们的实际情况改写，不过走一遍穿越时间的Excel升级之路，也许会解决这些问题。

没有看过前面连载的可以点击头像或链接进行阅读：

穿越时间•Excel升级之路连载2：打牢Excel 2010中的数据类型基础

穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧

穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较

“理想是石，敲出星星之火；理想是火，点燃熄灭的灯；理想是灯，照亮夜行的路；理想是路，引你走到黎明。”在当代诗人流沙河创作的现代诗《理想》的优美语境下，我们开始接触Excel中的高级用法——数组，走到Excel升级之路的连载5：数组的理想照进现实。

一、何谓数组？

之前，我们提到的都是Excel中的数据，或者数字，而“数组”可谓是一个新的名词，不过它并不难理解。

拥有大学数学基础或其他程序设计语言基础的人应该比较清楚“数组”的概念，如果不清楚也没关系，我们从数据的量或者维的角度来说起。

首先我们可以发现，无论是电影、电视剧还是文学作品，无论是数学、物理学还是天文学，其中都可以找到类似的概念。

你看过黑客帝国三部曲（The Matrix Trilogy），或者是最新的《黑客帝国·矩阵重启（The Matrix Resurrections）》吗？

你阅读过刘慈欣的科幻小说《三体》？探索过多重宇宙中二向箔等降维打击武器吗？

这些内容中Matrix、二向等就是量或者维的概念，虽然在不同领域中存在差别，但整体兼容，可以用下表类比总结：

因此，我们可以认为数组就是一组数的集合：

像点一样的单个数可以视为只含有一个元素的特殊的数组；

像线一样的一行数或者一列数就是一维数组；

像平面一样的由行和列构成的多个数就是二维数组。

二、数组的分类和表示

在Excel中，我们会涉及到三大类数组：常量数组、区域数组、内存数组。

首先需要记住的是Excel执行数组运算的快捷键 Ctrl+Shift+Enter，带着这把尚方宝剑，来了解一下各类数组的基本规则。

1、常量数组

（1）表示

常量数组通过大括号{}来表示，大括号之内的内容就是数组的元素。

例如： {1,2,3,4,5}和{1;2;3;4;5} 表示的分别是一维横向常量数组和一维纵向数组。

注意逗号表示横向或行，分号表示纵向或列。

如果既有逗号又有分号，那么这个常量数组就是由行和列构成的二维数组。

常量数组中可以包括各种类型的数据，如文本字符型数据、数值型数据、逻辑型数据、错误值等，而且各个数据是可以重复的。

（2）一维数组的生成

通过={a,b,c}或={a;b;c}的方式可以生成一维横向数组或一维纵向数组。

在Excel中，我们需要先选中存放数组的单元格区域，比如A1：E1，然后在编辑栏里输入={1,2,3,4,5}，按Ctrl+Shift+Enter键即可快速在A1到E1单元格中输入一维数组常量。

此时，A1:E1整体即为一个区域数组。

可以看到，当按下Ctrl+Shift+Enter键之后，A1到E1单元格中会出现常量数组中的各个元素。此时，编辑栏中的公式={1,2,3,4,5}外面会多出一层大括号：{={1,2,3,4,5}}，这就是Ctrl+Shift+Enter执行数组运算的意思。

这里我们就要问了，为什么要提前选好区域？

如果选错或不选会发生什么？

如果选择的区域小，容纳不下数组里的各元素，那么Excel单元格会显示不全；

如果选择的区域大，超过数组里的元素，超出的单元格中会显示#N/A

以上是最简单的结论，实际需要考虑所选区域的行、列同常量数组的关系，涉及数组的运算，这一部分这里不展开。

（3）二维数组的生成

当然，通过逗号和分号组合，可以在Excel单元格中快速输入二维常量数组，如

={"穿越时间",2,2,4;TRUE,5,6,7}按Ctrl+Shift+Enter执行数组运算。

当然我们需要提前选择好一致的单元格：两行四列。

这个例子可以说明数组中可以包括各种类型的数据，而且支持重复值。

2、区域数组

Excel中的单元格区域也可以视为数组，这就是区域数组的由来。

需要注意的是，对于区域数组，如果要更改数据，需要通过编辑栏修改公式，然后按Ctrl+Shift+Enter执行数组运算；

我们不能直接点击其中某一个单元格来更改内容，如果非要这样做，Excel会提示错误：“不能更改数组的某一部分”。

3、内存数组

内存数组是什么？我们可以把Excel公式在数组计算过程中产生的数组视为内存数组，它只是储存在电脑的内存里，并不显示在单元格中。

内存数组可以作为函数的参数，让函数依据内存数组中的多个元素执行多次运算返回多个结果，与程序设计语言中的循环结构有异曲同工之妙。

在本文的最后我将对它进行解释。

三、数组的运算

同单个数据可以进行计算一样，数组也是可以执行计算的，这种计算包括算术运算、逻辑运算等Excel中支持的运算。（可以详见之前的连载4）

我们先以常量数组为例，通过常量数组的算术运算切入，体会一下数组的运算方法。

然后再结合Excel中的函数进行数组运算。

1、常量数组的运算

（1）单值与一维数组或二维数组运算

运算方法：无论数组是一维横向数组、一维纵向数组还是二维数组，都遵循单值与数组中每一个元素分别运算并返回结果。

例如公式=5*{1,2,3,4,5}

计算5*1,5*2,5*3,5*4,5*5

={5,10,15,20,25}

例如公式="穿越"&{"AA","BB","CC";"DD","EE","FF"}

计算"穿越"&"AA","穿越"&"BB","穿越"&"CC";"穿越"&"DD","穿越"&"EE","穿越"&"FF"

={"穿越AA","穿越BB","穿越CC";"穿越DD","穿越EE","穿越FF"}

（2）同向 一维数组与一维数组运算

同向同规格时，数组对应位置上的元素分别运算并得出结果

例如公式={1;2;3}*{4;5;6}

计算1*4;2*5;3*6

={4;10;18}

这里需要注意一下，同向一维数组运算的时候会出现两个数组元素个数不同的情况（即同向不同规格），数组元素个数不一样也不要紧，这时，可以把短的那个数组以#N/A补齐，然后继续遵循数组对应位置上的元素分别运算并得出结果。

例如：

公式={1;2;3}*{4;5;6;7}，第一个数组元素个数比第二个数组少一个，我们可以将第一个数组视为{1;2;3;#N/A}，然后和{4;5;6;7}运算。

公式={1;2;3}*{4;5;6;7}

计算1*4;2*5;3*6;#N/A*7

={4;10;18;#N/A}

（3）异向 一维数组与一维数组 运算

异向一维数组运算，数组1中的每个元素分别和数组2中的每个元素运算，得到新的二维数组，其元素个数为两个一维数组的行数*列数。

={1,2,3,4}*{5;6;7}

计算1*5,2*5,3*5,4*5; 1*6,2*6,3*6,4*6; 1*7,2*7,3*7,4*7

={5,10,15,20;6,12,18,24;7,14,21,28}

={"穿越","时间","丽云","流金"}&{"CY";"SJ";"LY";"LJ"}的结果如下：

如果想知道的更多，

以下是一维数组和二维数组的运算，二维数组和二维数组的运算，在Excel后面的函数应用中比较少涉及。我们了解一下：

（4）一维数组和二维数组运算

取决于一维数组的方向！

如果是一维横向数组，则一维横向数组中的每个元素和二维数组中每一行的元素运算；尺寸不同时用#N/A补齐。

例如：

={1,2,3}*{4,5,6;7,8,9}的结果如下：

例如：

={1,2,3,4}*{4,5,6;7,8,9}的结果如下：

例如：

={1,2,3}*{4,5,6,6;7,8,9,9}的结果如下：

如果是一维纵向数组，则一维纵向数组中的每个元素和二维数组中每一列的元素运算；尺寸不同时用#N/A补齐。

={1;2}*{4,5,6;7,8,9}的结果如下：

={1;2;3}*{4,5,6;7,8,9}的结果如下：

={1;2}*{4,5,6;7,8,9;10,11,12}的结果如下：

（5）二维数组和二维数组运算

遵循对应位置对应运算即可，尺寸不足的地方用#N/A补齐。

例如：

={1,2;3,4}*{4,5,6;7,8,9;10,11,12}的结果如下：

2、区域数组的运算

通过上一部分的内容，我们对数组如何运算应该有了清楚的了解。

在Excel中，区域数组的运算要比常量数组更加简洁，把上面例子中的常量数组替换为区域数组即可，而区域数组就是单元格引用，当然，需要按Ctrl+Shift+Enter键执行数组运算。

（1）最简单的例子：

={1;2;3}*{4;5;6}的结果和=A1:A3*C1:C3的结果是一样的。

我们在Excel中选好存放结果的单元格，使用公式=A1:A3*C1:C3

按Ctrl+Shift+Enter键即可执行数组运算。

在这一过程中，Excel会把A1:A3和C1:C3都当作区域数组。而计算的规则就是上面所讲过的。

（2）函数中的区域数组

数组可以作为函数的参数。

下面，我们以最简单的SUM函数为例，看看SUM函数与区域数组的简单结合。

SUM函数可以用来计算单元格区域中所有数值的和，单元格中的逻辑值和文本将被忽略，但是当作为参数键入时，逻辑值和文本有效。

以上是Excel中对SUM函数的解释，但实际的情况是这样：

SUM函数可以用来计算给定参数（参数以逗号分隔）中数字的和，给定参数可以是区域、单元格引用、数组、常量或其他公式和函数的结果；

如果参数不是数组或引用，那么参数中的逻辑值或文本型数字有效；

如果参数是一个数组或引用，则只计算其中的数值型数字。数组或引用中的空白单元格、逻辑值、文本型数字将被忽略。

如果任意参数为错误值或为不能转换为数字的文本，Excel 将会显示错误。

看几个单参数的sum函数公式：

=SUM(A1:E1)

这是最简单最容易理解的求和公式，按下Enter键后，sum函数会计算A1:E1中5个数的和。

采用数组=SUM({1,2,3,4,5})或者=SUM(A1:E1) 按下Ctrl+Shift+Enter键后，结果同样是15

这里没有什么难度，可以得出一个结论，sum函数遇到一个参数，且这个参数是数组的时候，会把数组中所有的元素求和。

继续：

=SUM(A1:G1)

按下Enter键后，sum函数会计算A1:G1区域的和，注意数组或引用中的空白单元格、逻辑值、文本型数字将被忽略，结果为15

=SUM({1,2,3,4,5,TRUE,"6"}) 或=SUM(A1:G1)

按下Ctrl+Shift+Enter键后，注意数组或引用中的空白单元格、逻辑值、文本型数字将被忽略，结果同样是15

继续：

看几个多参数的sum函数公式：

=SUM(1,2,3,4,5,TRUE,"6")

按下Enter键后，结果为22，此时符合如果参数不是数组或引用，那么参数中的逻辑值或文本型数字有效，逻辑型TRUE被当作1，文本型"6"被当作6，实际相当于是1+2+3+4+5+1+6=22

下面，如果sum函数公式中有多个数组参数会发生什么？

=SUM({1,0},{5,6,7}) 或=SUM(A1:B1,A3:C3) 按下Ctrl+Shift+Enter键后的结果为19

={1,0}+{5,6,7} 或=A1:B1+A3:C3按下Ctrl+Shift+Enter键后的结果为数组{6,6,#N/A}

虽然看起来都像是求两个数组的和，但实际是：

={1,0}+{5,6,7}是求两个数组的和，结果是数组；

=SUM({1,0},{5,6,7})计算的并不是数组{1,0}、 {5,6,7}求和得到新数组，然后再求新数组各元素的和，Sum函数计算的就是两个数组各个元素的和，1+0+5+6+7=19

有人就会好奇了，=SUM({1,0},{5,6,7}) 或=SUM(A1:B1,A3:C3)是两个参数的写法，不能实现数组{1,0}、{5,6,7}求和得到新数组，然后再求新数组各元素的和，那如果改成采用一个参数的写法行不行呢?

然而=sum({1,0}+{5,6,7}) 或者=sum(A1:B1+A3:C3)的写法会出错#N/A

为什么？

这次sum函数确实是会实现数组{1,0}、{5,6,7}求和得到新数组{6,6,#N/A}，然后再运算求sum({6,6,#N/A})时便会出错。

相当于是两个数组执行求和运算之后生成了一个新数组（内存数组），然后sum函数对新数组中的各个元素求和，而#N/A又不能够参与求和运算，所以出错。（符合如果任意参数为错误值或为不能转换为数字的文本，Excel 将会显示错误。）

这就是尺寸的问题，因为两个参数中的数组{1,0}、 {5,6,7}尺寸不同，所以会出错。

假如两个数组的尺寸相同，这种写法便是可以的。

例如=SUM({1,0,0}+{5,6,7})，这时按下Ctrl+Shift+Enter键后，计算过程=sum({6,6,7})=6+6+7=19

这下我们应该明白函数与数组结合，单参数、多参数是怎么回事了，也应该明白sum函数在什么情况下会忽略逻辑值和文本型数字了。

最后做个练习：

=SUM(A1:E1*{1,2,3,4,5}=25)它表示什么意思？执行数组运算后的结果是什么？

解析：

单参数写法；

先计算区域数组A1:E1和常量数组{1,2,3,4,5}的乘积，产生新数组，然后计算新数组和25的逻辑判断运算，再产生新数组，最后再求和。

=SUM(A1:E1*{1,2,3,4,5}=25) 按下Ctrl+Shift+Enter键计算后结果为0

你算对了吗？

计算过程：原公式

3、内存数组的运算

上面的例子已经可以很好地说明内存数组的存在了。

看到这里，你的大脑内存还够用吗？是否需要清一下内存再继续？

最后通过两个函数：IF函数和CHOOSE函数，讲一下内存数组的构建。

（1）IF函数方法

IF(logical_test, [value_if_true], [value_if_false])

IF(条件，条件为真时返回的值，条件为假时返回的值)

通过IF函数结合数组{1,0}或{0,1}，我们可以让IF函数返回数组，实现Excel引用区域的调整。

例如：

=IF({1,0},A1:A11,C1:C11)

原本燕京十景和西湖十景直接还有一个B列，通过IF执行数组运算之后，即可把两列靠在一起，注意{1,0}，运算时，相当于计算了=IF(1,A1:A11,C1:C11)，返回了A1:A11，又计算了=IF(0,A1:A11,C1:C11)，返回了C1:C11

非0的数值型数字作if函数条件，公式会返回条件为真时的值，

0作if函数条件，公式会返回条件为假时的值

所以也不一定非要用{1,0}，使用{2,0}、{3,0}等都是一个效果。

而=IF({0,1},A1:A11,C1:C11)，则可以调整列的前后顺序，返回的结果中西湖十景已经到了燕京十景的前面。

因此通过这个方法便可以实现VLOOKUP所谓的逆向查询。这里不展开。

（2）CHOOSE函数方法

CHOOSE(index_num, value1, [value2], ...)

CHOOSE(索引号数字,索引号为1时返回的值，索引号为2时返回的值……)

上面的if函数可以任意调整Excel中的两列，如果要构建组合多列，则可以使用CHOOSE函数。

举个例子：=CHOOSE(2,A1:A11,C1:C11,E1:E11)

索引号为2，表示返回索引号为2的值，执行数组运算后，返回C1:C11

=CHOOSE({3,1,2},A1:A11,C1:C11,E1:E11)

先返回E1:E11，再返回A1:A11，再返回C1:C11

即可实现青岛十景、燕京十景、西湖十景三列重新排序！

好了，以上就是连载5的全部内容，难度已经越来越高，你的数组的理想实现了吗，如果有不理解的可以先看前面的连载打牢基础。

点击头像或链接跳转：

穿越时间•Excel升级之路连载1：Office2010安装体验及个性设置

穿越时间•Excel升级之路连载2：打牢Excel 2010中的数据类型基础

穿越时间•Excel升级之路连载3：数据类型检测与相互转换技巧

穿越时间•Excel升级之路连载4：精通公式运算规则和字符比较

更多精彩，敬请关注。

（原创连载，个人观点保留，禁止任何未经授权的非本人账号复制文章到其他平台发布）