讲一讲excel数组公式是怎么计算的

Excel函数 2021-11-17 14:20:03

1、行列数相同数组的运算
讲一讲excel数组公式是怎么计算的
数组1+数组2，这是一个多单元格的数组公式，第一个数组的第一个元素与第二个数组的第一个元素相加，结果作为数组公式结果的第一个元素，然后第一个数组的第二个元素与第二个数组的第二个元素相加，结果作为数组公式结果的第二个元素，接着是第三个元素……直到第N个。
讲一讲excel数组公式是怎么计算的
这是横向的一维数组的计算，原理同上。

这是二维数组与二维数组进行计算，生成一个新的二维数组的多单元格数组公式。同样的计算过程，第一个数组的第一行的第一个元素与第二个数组的第一行的第一个元素相乘，结果为数组公式的结果的数组的第一行的第一个元素，接着是第二个，第三个……直到第N个。

规律很简单：两个同行同列的数组计算是对应元素间进行运算，并返回同样大小的数组。
正如穿鞋要穿合脚的才走得了路一样，在公式或函数中使用数组时，运算对象或参数的数组维数要匹配，否则计算会出错。教室里，第一排的有8个同学，第二排有9个同学，老师说：“第一排和第二排的同学交换作业，互相检查。”第二排的第9个同学和谁交换？这就是数组的不匹配。数组不匹配时，工作就不能完成了。
你可以试着改一改数组的参数试试。

2、数组与单一的数据的运算
讲一讲excel数组公式是怎么计算的
这相当于在E42单元格输入公式=A42*$C$42，然后下拉复制公式实现。

等同于在B56输入公式“=B52+$B$54”，然后右拉复制公式实现。

等同于在C67单元格输入公式“=A60+$E$60”然后右拉下拉复制公式实现。

不难看出：一个数组与一个单一的数据进行运算，是将数组的每一元素均与那个单一数据进行计算，并返回同样大小的数组。

3、单列数组与单行数组的计算
讲一讲excel数组公式是怎么计算的
两个数组相加，查看结果是几行几列：在任意单元格输入公式“=A80:A83+B87:E87”，抹黑公式，按F9键，可看到公式的计算结果为数组 “{110,210,310,410;120,220,320,420;130,230,330,430;140,240,340,440}”通看看分号与逗号，我们知道这是一个四行四列的数组，选择一个四行四列的单元格，输入公式“=A80:A83+B87:E87”，三键结束，可看到返回的结果为：
讲一讲excel数组公式是怎么计算的
相当于在E80输入公式“=$A80+B$87”右拉下拉复制公式的结果。
单列数组与单行数组的计算：
A、计算结果返回一个多行列的数组；
B、返回数组的行数同单列数组的行数相同、列数同单行数组的列数相同。
C、返回数组中第R行第C列的元素是单列数组的第R个元素和单行数组的第C个元素运算的结果。

4、行数（或列数）相同的单列（或单行）数组与多行多列数组的计算
（1）单列数组的行数与多行多列数组的行数相同时：
讲一讲excel数组公式是怎么计算的

（2）单行数组的列数与多行多列数组的列数相同时：

计算规律同单行单列的数组计算的规律大同小异：
A、计算结果返回一个多行列的数组；
B、返回数组的行、列数与多行多列数组的行列数相同；
C、单列数组与多行多列数组计算时，返回的数组的第R行第C列的数据等于单列数组的第R行的数据与多行多列数组的第R行第C列的数据的计算结果；
D、单行数组与多行多列数组计算时，返回的数组的第R行第C列的数据等于单行数组的第C列的数据与多行多列数组的第R行第C列的数据的计算结果。

＝＝＝＝＝＝＝留给你的思考题＝＝＝＝＝＝＝
讲到这里，我们可以暂停一下进度。课间休息，插播一段广告：
你可以喝杯水，听听音乐，然后我们来看几个例子：

图1：
讲一讲excel数组公式是怎么计算的
图2：

图3：

上面的三张图，第一个公式是我们前面讲的例子，第二个公式是在第一个公式的基础上对参与计算的数组区域进行了修改，但是，两个不同参数的公式，返回的结果却都是一样的。这里我只是举了三个例子，你可以把前面我们讲过的公式里的数组参数都修改修改，什么情况下，会返回相同的结果呢？它们又有什么共同的地方？知识总是光顾那些善于总结和发现的人。否则，踩着别人的脚印走，想要看到别人没看到的风景，你要等到猴年马月？
好了，我也仿小学老师的口气问问大家：“为什么两个不同的公式，返回的结果都是一样的呢？从上面的图，你发现了什么？把你的发现说给你的伙伴听一听。”
这就是你今天的作业，如果你是真心想想学数组公式的，记得跟贴回复！

5、行、列数不相等的数组计算
（1）行数不相等的单列数组与与多行列数组的计算
讲一讲excel数组公式是怎么计算的
（2）列数不相等的单行数组与多行多列数组的计算

（3）行、列数不相同的两个多行多列数组的计算

有了对前面例子的分析，再来看这三个例子就相对简单了。它们的计算规则和前面都是一样的，不难看出：
A、公式返回一个多行多列数组；
B、返回数组的行数与参与计算的两个数组中行数较大的数组的行数相同，列数与较大的列数的数组相同；
C、返回数组的大于较小行数数组行数、大于较大列数数组列数的区域的元素均为#N/A。有效元素为两个数组中对应数组的计算结果。
需要提醒一点的是，对会返回#N/A的数组，在进行再计算和处理时，考虑对#N/A值作相应的处理！
比如我们想对上面数组与数组2相加后的结果进行求和：
讲一讲excel数组公式是怎么计算的
正确的公式（数组）：=SUM(IF(ISNA(A213:B216+D213:F215),0,A213:B216+D213:F215))
通过ISNA函数对返回的数组里的各个元素进行了判断和处理，把把有的#N/A值替换成数值0，最后再用SUM函数对所有数值进行求和。
我们说，数组计算时，得注意行列数的匹配，其实如果了解了数组的计算原理后，能正确处理那些返回的#N/A值的话，很多时候，并不会出错的。