组成

  • 数组外部是用 {} 包围
  • 横向数组内部是使用逗号, 分隔
  • 竖向数组内部使用分号；分隔(都是英文输入法下的符号)

在单元格显示的是数组的第一个元素，但数组常用于用于公式中

数组公式

  • 在单元格输入普通公式，按Enter键或编辑栏左侧的“输入”按钮完成输入
  • 而在单元格输入数组公式需要按Ctrl+Shift+Enter三键完成输入，所以数组公式也叫做CSE公式。

按Ctrl+Shift+Enter三键输入数组公式后，在编辑栏显示该公式用大括号“{}”括住。不要直接输入这对大括号来代替按三键，那样输入的只是文本，不是公式。

按**Ctrl+Shift+Enter后，公式中的数据区域都看做数组 {} 来参与公式运算**

数组分类

一维数组

简单的一维数组，分为横向数组和竖向数组，
横向一维数组：将4个连续的横向单元格E3:G3转成一个数组，先选中区域，按Ctrl+Shift+Enter，公式变为 {=E3:G3}，要查看数组的内容，在公式中按F9，见下图；
{=E3:G3}等效于={1,2,3,4}
Excel数组 - 图1
竖向一维数组也是同样的道理，将4个连续的竖向单元格D3:D6转成一个数组，先选中区域，按Ctrl+Shift+Enter，公式变为{=D3:D6}，要查看数组的内容，在公式中按F9，见下图；
fb738d9c2cf7dfb260b0099fd01b1edef5dc133d.png

二维数组

二维数组，建一个3行3列的二维数组，第一行的是1,4,7，第二行的是2,5,8，第三行的是3,6,9，数组表示形式为{1,4,7;2,5,8;3,6,9}
分号可以理解为换行啦
def3c219ce2c5b1ba177336f2b39131fcfec0e3d.png

数组运算之原理

Excel数组 - 图4数组运算分别取各数组中相同维度上相同位置的数值进行重复计算
所以两数组进行运算的结果还是一个数组，且两个数组的行数取最大值，列数取最大值，即为结果的行列数。
**
数组a和数组b的行、列数都相同，则维度相同，否则不同。

  • 维度相同，如={1,2;4,5;7,8}+{10,20;40,50;70,80} 返回{11,22;44,55;77,88}，都是两列三行的二维数组
  • 维度不同，数组会自动扩展至与结果的维度相同，再进行一一对应的运算

扩展的方式有两种：

  1. 原值扩展：用原数组的值填充扩展的位置

  2. N/A值扩展：用#N/A值填充扩展的位置

记住：想要进行数组运算，需要Ctrl+Shift+Enter

维度相同：
={1,2;4,5;7,8}+{10,20;40,50;70,80} 返回{11,22;44,55;77,88}
image.png
={1;2;3;4}*{5;6;7;8}返回{5;12;21;32}
image.png

维度不同：#N/A扩展
image.png

数组运算之扩展规则

会扩展至与结果相同的维度后再参与运算
分号可以理解为换行啦

先看运算的结果是几行几列，然后在把数组扩展至对应的行列数
新维度上的拓展采用原值扩展，旧维度上的拓展采用**#N/A值扩展**

单元素数组或单值与任意数组的运算

单值或单元素数组，可以看成是零维度，它们的扩展属于新维度上的扩展，都是原值扩展。
例如：
={1,2;4,5;7,8}+1 等效于
={1,2;4,5;7,8}+{1} 等效于（扩展后的数组）
={1,2;4,5;7,8}+{1,1;1,1;1,1}
均返回{2,3;5,6;8,9}。
再例如
={10} + {10,20,30}等效于
={10,10,10} + {10,20,30}
均返回{20,30,40}

154330357_2_20190216090158974.gif

  1. 已知：结果区域为1行3列。
  2. 数组1自动扩充区域，并填充数据，然后执行运算。

同方向、不同大小的一维数组的运算

同方向的一维数组扩展，就是在原有维度上的扩展，使用#N/A值扩展。
例如：
={“A”,”B”,”C”,”D”}&{“E”,”F”} 扩展成相同大小的数组后变成
={“A”,”B”,”C”,”D”}&{“E”,”F”,#N/A,#N/A}
返回{“AE”,”BF”,#N/A,#N/A}。

不同方向的一维数组运算

不同方向的一维数组扩展，相当于在新维度上的扩展，使用原值扩展，并生成一个二维数组。
例如：
={4,5}{6;7;8}
={4,5;4,5;4,5}{6,6;7,7;8,8}
均返回{24,30;28,35;32,40}
image.png
再例如
={10,20,30}+{40;50;60}
={10,20,30;10,20,30;10,20,30}*{40,40,40;50,50,50;60,60,60}
均返回{50,60,70;60,70,80;70,80,90}
154330357_3_20190216090159896.gif

  1. 已知：结果区域为3行3列。
  2. 数组2，数组3都会自动扩充区域，并填充数据，然后执行运算

一维数组和二位数组运算

可能同时存在两种扩展方式：一维数组扩展为二维数组，在新维度上使用原值扩展；另外，在原有维度上，如果大小不同，还可能进行#N/A值扩展。
例如
={4,5}+{10,20,30;40,50,60}
={4,5,#N/A; 4,5,#N/A}+{10,20,30; 40,50,60}
均返回{14,25,#N/A; 44,55,#N/A}
又如：
={40;50;60} + {1,2; 3,4; 5,6; 7,8}
={40,40; 50,50; 60,60; #N/A,#N/A} + {1,2; 3,4; 5,6; 7,8}
={41,42; 53,54; 65,66; #N/A,#N/A}**
154330357_4_20190216090200692.gif

  1. 已知：结果区域为4行2列。
  2. 数组3先横向扩充区域并填充列数据，然后再纵向扩充区域。但是尝试纵向填充数据的时候，Excel无法判断应该填充什么数据，所以会填充“#N/A”错误值。
  3. 数组4为4行2列的二维数组，所以无须再扩充区域。
  4. 执行运算的时候，对#N/A进行运算的结果为#N/A。

不同大小的二维数组运算

属与原维度上的扩展，使用#N/A值扩展。
例如：
= {1,2; 3,4; 5,6; 7,8} + {10,20,30; 40,50,60}
={1,2,#N/A; 3,4,#N/A; 5,6,#N/A; 7,8,#N/A} + {10,20,30; 40,50,60; #N/A,#N/A,#N/A; #N/A,#N/A,#N/A}
={11,22,#N/A; 43,54,#N/A; #N/A,#N/A,#N/A; #N/A,#N/A,#N/A}
154330357_5_20190216090201692.gif

数组和sum

计算一维数组中大于0的数之和，可以使用公式 =sum(数组*(数组>0))

  • （B1:B7>0）在Ctrl+Shift+Enter会转为数组 {TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}
  • 数组相乘*时，TRUE和FALSE会转为1和0，相乘的结果 {1;2;3;4;0;0;0} 还是一个数组
  • 最后sum函数对数组中的元素相加求和

image.png

数组和IF

在vlookup中经常套用IF( {1,0}, …)来实现数组位置变换，例如
0067PPKnty6ZX16Xhk2c6&690.jpg
（图中的H列是为了展示运算结果，实际中向右填充公式并不能达到图中效果）

选择区域G1:H4，输入以下数组公式，可实现A、B列位置互换：
{=IF({0,1}, A1:A4, B1:B4)}或
{=IF({1,0}, B1:B4, A1:A4)}

公式返回{“B1”,”A1”;”B2”,”A2”;”B3”,”A3”;”B4”,”A4”}。

对{=IF({1,0}, B1:B4, A1:A4)}的解析
再阅读Excel数组后来进行解析
第一个参数 {1,0} 扩展后变成{1,0;1,0;1,0;1,0}
第二个参数 {B1:B4} 扩展后变成{“B1”,”B1”;”B2”,”B2”;”B3”,”B3”;”B4”,”B4”}
第三个参数 {A1:A4} 扩展后变成{“A1”,”A1”;”A2”,”A2”;”A3”,”A3”;”A4”,”A4”}
于是我们可以确定：这个数组公式需要重复计算8次，并返回一个四行两列的数组。

  • 第一次计算分别取三个参数的第一个元素，组成普通公式=IF(1,"B1","A1")，根据数值类型自动转换规律，1被转换为逻辑值TRUE，所以计算结果为”B1”，该结果为返回的数组中第一行第一列的值；
  • 第二次计算分别取三个参数的第二个元素，组成普通公式=IF(0,"B1","A1")，根据数值类型自动转换规律，0被转换为逻辑值FALSE，所以计算结果为”A1”，该结果为返回的数组中第一行第二列的值；
  • 第三次计算分别取三个参数的第三个元素，组成普通公式=IF(1,"B2","A2")，计算结果为”B2”，该结果为返回的数组中第二行第一列的值；
  • 如此类推。数组公式的结果如图中G1:H4所示

参考