Excel基础学习园地
公众号“
Excel基础学习园地
”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“
Excel基础学习园地
”添加关注，方便我们每天向您推送精彩资讯。
前文回顾：菜鸟的Excel函数修炼手册 第3课 从发现规律开始——公式三板斧第三式
4.1 CSE公式是个什么鬼
在之前的学习中，我们知道了什么是公式：公式是Excel中以等号开头的可以得到一个结果的等式，公式以等号“=”开头，公式中可以包括函数、运算符、引用和常量。
相对于普通公式而言，在Excel中还有另一种形式的公式：数组公式。
数组公式通常也被称作 CSE (Ctrl-Shift-Enter) 公式，因为输入公式后不是只按 Enter 键，而是要按 Ctrl-Shift-Enter 完成公式的输入。
数组公式可以对两组或多组数据执行运算，执行多项计算后返回一个或多个结果。每一组数据就是一个数组，数组可以是同一行或者同一列中的多个数据，也可以是一个多行多列的区域中的数据。
与普通公式相同，数组公式同样需要以等号“=”开头，在创建数组公式时，有两种不同的方式，针对一个单元格创建的数组公式和针对单元格区域创建的数组公式。
4.2 数组公式的两种形态
4.2.1 单元格数组公式
下面通过一个常见的示例了解单元格数组公式，如图4-1所示，C列是商品单价，D列是销售数量，使用数组公式=SUM(C2:C11*D2:D11)可以直接计算出总销售额。
图4-1
使用数组公式时，不需要单独计算出每个商品的销售额，数组公式 =SUM(C2:C11*D2:D11) 与 公式=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11) 的意义相同。
数组公式输入完成后，同时按下 Ctrl-Shift-Enter三个键之后，会自动在公式两端添加大括号，并计算出结果。如果手动键入大括号，公式将转换为文本字符串，并不会得到结果，如图4-2所示。
图4-2
4.2.2 区域数组公式
与单元格数组公式不同，使用区域数组公式时，需要先选中要创建公式的单元格区域，例如要计算每个商品的销售额，需要先选中E2到E17这个区域，然后输入公式=C2:C11*D2:D11，如图4-3所示。
图4-3
同时按下Ctrl-Shift-Enter三个键，所选单元格会同时创建公式，结果如图4-4所示。
图4-4
这个例子中，使用普通公式=C2*D2下拉，与使用数组公式=C2:C11*D2:D11表面看上去没什么区别，实际上使用数组公式有以下几个优点：
1. 一致性：显示公式后，您将看到相同的公式，如图4-5所示，这种一致性可以使公式具有更高的准确性。
图4-5
2. 安全性：不能单独修改或者删除其中某个单元格的数组公式。例如单击单元格 E3，然后按 Delete删除公式时，就会出现提示，如图4-6所示。
图4-6
如果要修改或者删除区域数组公式，必须选择整个单元格区域(E2到E11)，然后更改整个数组的公式，修改后依然需要同时按Ctrl-Shift-Enter三个按键确认对公式的更改。
3.高效：使用普通公式(例如=C2*D2, =C3*D3, =C4*D4…)，要使用11个不同的公式，使用数组公式只需要一个相同的公式，而计算得出的结果并无差别。
4.3 公式有数组，常量也有数组
数组常量是数组公式的组成部分。可以通过输入一组常量后手动用大括号将这些常量括起来创建数组常量，类似于：={1,2,3,4,5}。
数组常量经常用来简化公式，例如要计算销售额排在前三名的金额合计，可以使用公式=SUM(LARGE(E2:E11,{1,2,3}))，结果如图4-7所示。
图4-7
LARGE函数的功能是返回一组数字中的第几个最大值，例如LARGE(E2:E11,4)就可以得到E2:E11这个区域中第四大的值。在LARGE(E2:E11,{1,2,3})中，第二个参数使用了常量数组，相当于三个LARGE的效果，分别是LARGE(E2:E11,1)、LARGE(E2:E11,2)和LARGE(E2:E11,3)，可以利用F9键看到结果，如图4-8所示。
图4-8
使用了一个常量数组，就把三个LARGE组合成一个，达到了简化公式的目的。
需要注意的是，在图4-7所示的公式中，并没有按三键输入公式，结果依然正确。
如果把常量数组中的1、2、3放在单元格中，公式修改为=SUM(LARGE(E2:E11,G1:G3))，则必须按三键输入公式，否则就会得到错误值，如图4-9所示。
图4-9
这是因为常量数组是明确的数组，Excel直接会进行数组运算，而引用单元格区域作为数组时，必须通过三键的形式告诉Excel要按数组去运算。
如果分不清什么时候需要三键的话，那就统统都按三键完成，一定是没问题的。
4.4 让人头晕目眩的维度
4.4.1 数组的逗号和分号
要理解数组之间的运算原理，必须了解数组的维度和方向。
如果使用逗号分隔各个项，将创建水平数组(一行)。如果使用分号分隔项，将创建垂直数组(一列)。若要创建二维数组，应在每行中使用逗号分隔项，并使用分号分隔每行。
示例1. {1,2,3,4}是一个单行数组，数组中的值以逗号分隔。选择同一行的六个单元格，输入公式={1,2,3,4}，同时按下Ctrl-Shift-Enter结果如图4-10所示。
图4-10
因为数组中只有四个数据，所以前四个单元格依次显示数组中对应的数据，后面两个单元格是错误值。
选中同一列的六个单元格，输入同样的公式，结果如图4-11所示。
图4-11
因为数组的方向与单元格的方向不一致，所以单元格中得到的都是数组中第一个数据。
示例2. {1;2;3;4}是一个单列数组，数组中的值以分号分隔。选择同一行的六个单元格，输入公式={1;2;3;4}，同时按下Ctrl-Shift-Enter结果如图4-12所示。
图4-12
因为数组的方向与单元格的方向不一致，所以单元格中得到的都是数组中第一个数据。
选中同一列的六个单元格，输入同样的公式，结果如图4-13所示。
图4-13
因为数组中只有四个数据，所以前四个单元格依次显示数组中对应的数据，后面两个单元格是错误值。
示例3. {1,2,3,4;5,6,7,8}是一个两行四列的数组，逗号代表同一行，分号代表同一列。在一个单元格区域中，同时按下Ctrl-Shift-Enter三键输入公式={1,2,3,4;5,6,7,8}，结果如图4-14所示。
图4-14
在区域左上角的两行四列单元格中，显示数组中对应的值，超过这个范围的其他单元格都是错误值。
示例1和示例2中只有一个方向(单行或单列)的数组是一维数组，示例3中同时存在两个方向的数组是二维数组。
4.4.2 数组的运算规则
1.单值与数组的运算规则：单值与数组中的每个值进行计算，得到与数组同方向同纬度的数据。
示例1.数字3与一维数组{1,2,3,4}相乘，公式为=3*{1,2,3,4}，结果如图4-15所示。
图4-15
示例2.数字3与二维数组{1,2,3,4;5,6,7,8}相乘，公式为=3*{1,2,3,4;5,6,7,8}，结果如图4-16所示。
图4-16
2.同向一维数组的运算规则：两个数组中对应的数据分别计算，进行计算的两个数组中的数据个数必须相同，否则多余部分返回错误值。
示例3.数组{1;2;3}+{4;5;6}返回{5;7;9}，结果如图4-17所示。
图4-17
示例4.数组{1,2,3,4}+{4,5,6}返回{5,7,9,#N/A}，结果如图4-18所示。
图4-18
3. 异向一维数组的运算规则：数组1的每一个数据与数组2的每一个数据分别运算并返回结果，得到两个数组的行数*列数个元素，也就是M行数组与N列数组运算结果为M*N的矩阵数组。
示例5. 数组{1;2;3}+{4,5,6,7,8}，执行1+4、1+5……1+8、2+4、2+5……3+8，结果如图4-19所示。
图4-19
4. 一维数组与二维数组的运算规则：二维数组其中一个方向与一维数组同向同尺寸，比如M*N的二维数组与M行或者N列的数组运算，在同向的运算类似同向一维数组之间的位置对应，在异向的运算则类似单值与数组之间的一一运算。超出尺寸范围都返回错误值。
示例6. 数组{1,2,3,4}+{1,2,3,4,5;6,7,8,9,10}即四列一维数组与两行五列二维数组相加，列方向上两个数组运算按位置一一对应的，不足部分用#N/A补齐，行方向上则是一对多的运算，结果如图4-20所示。
图4-20
提示：结果中的2、4、6、8为第一个数组中的数据与第二个数组中的1、2、3、4相加所得；结果中的7、9、11、13为第一个数组中的数据与第二个数组中的6、7、8、9相加所得；其他位置无法对应均返回错误值。
5. 二维数组之间的运算规则：要求参加运算的二维数组具有相同的尺寸，相同位置的数据一一对应进行计算，无法对应的位置则会返回错误值。
示例7. 数组{1,2;3,4}+{1,2,3,4,5;6,7,8,9,10}即两行两列的数组与两行五列二维数组相加，数组中相同位置一一对应相加，无法对应的部分用#N/A补齐，结果如图4-21所示。
图4-21
数组的维度和运算规则属于公式应用中比较有难度的部分，初学阶段只需要了解逗号和分号与数组方向之间的关系，掌握一维数组之间的计算规则即可，随着使用经验的增加，再慢慢去理解其他计算规则。
下节预告
上课方式：QQ群视频直播，课后可以下载视频反复看。
报名费用：9.9元体验一周实战课程，5月1日起包月价格调整为50元，包年价300元终身价666元维持不变。
扫码加Excel交流群
交流心得 解决问题
验证信息：Excel
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
