EXCEL数组公式(4) : 用例子比较数组公式和非数组公式，发现数组公式 {} 写法的本质在于：每个步骤都会按照 数组去计算

总结

• 数组公式 {} 写法的本质在于：每个步骤都会按照 数组去计算
• 加上{}  就改变了 {}里的函数的计算方式，从值计算变成了数组计算！

 

1 公式和数组公式分类

先看一个例子

 

1.1 不可处理（完全无法处理）数组的函数/公式

比如字符串类函数
比如 find()
错误写法：FIND(1,B5:B9) 会直接返回 #N/A
正确写法：FIND("天生",F13)
正确写法：FIND("天生","")

1.2  多数函数可以处理数组，但是无法 直接 返回数组，但是可以和{}数组公式配合使用的

这下面又分3类

1.2.1 部分能处理数组的普通函数，但只会返回数组的第1个数

• 还要注意一些细节
• rows()    返回行数，是一定不能用于数组运算的
• row()     返回行号，可以处理数组，但不能直接返回数组，返回的一定是1个数（不是数组）
• 也就是说，不配合数组函数，或其他可处理数组内部数的函数，这个基本上无用。
• {row()}   返回一组行号，可以 结合数组公式返回 数组，把返回的数组存在一个单元格

 

1.2.2 部分函数可处理数组的整体，可以显示正确结果

• 比如聚合类的sum等，
• 比如 ：sum(),  large()
• sum()
• {sum()}  和sum() 还是有差别，差别在于 中间计算过程是否遵循数组计算

1.2.3 部分函数可直接处理数组内部的序列

• 有少部分工作表函数天生能处理数组的函数
• 部分查找和引用函数，计算函数，是可以的，比如index()  match()
• 一般字符串函数肯定不行
• 比如index() 函数非数组函数，但是天生可以处理数组的内部（取到数列里某个）    
• 还有
• sum()
• lookup()
• vlookup()
• match()
• choose()
• index()
• offset()
• 等等

1.3 在多个单元格输入数组公式（相当于直接返回了整个数组的元素）

（行列式的计算需要考虑这个）

数组公式{}有两种返回情况

• 返回1个数，在多个单元格写意义不大，因为会返回一样的值
• 返回1个数组，这种情况，如果有需要显示 返回的结果数组，最好在多个单元格里输入数组公式，以期显示返回的数组中的所有元素。

这么做的好处和坏处

• 好处在于：输出为数组的情况下，可以显示所有的值
• 难度在于：如果想知道所有的数组里的值，需要提前计算出数组结果的行列矩阵个数！(行数和列数)

1.4 在1个单元格输入数组公式

数组公式{}有两种返回情况

• 返回1个数，在多个单元格写意义不大，因为会返回一样的值
• 返回1个数组，这种情况，如果有需要显示 返回的结果数组，最好在多个单元格里输入数组公式，以期显示返回的数组中的所有元素。
• 如果是返回1个数组的情况，数组公式写在一个单元格，虽然只会显示数组中的第1个数，但实际这个单元格存的是一个数组。

 

1.5 这种是假数组公式，这么写是没意义的！

 

1.6 总结： 用数组公式{}  不用数组公式的比较，差别到底是什么？

• 我总结的差别就是：
• 如果用了数组公式 {} 写法，每个步骤都会按照 数组去计算
• 如果没用{}数组公式，则中间过程，函数会直接返回默认的结果（第1个，聚合值，或其他）

 

2  例子2：比较 数组公式和非数组公式 结果的差别

• 这种写法其实不是数组公式，我觉得是错的，无意义的
• =B3:B7+D3:D7
• =$B$3:$B$7+$D$3:$D$7
• 还需要往下拉公式
• 错误原因：因为不是数组，所以large()无法正确取到数组中得某值！              

 

• 数组公式是对的
• 
• 
• 数组公式即使只输入到1个单元格，large()取到得还是1个数组，所以能查到值！            

 

两者差别比较

• 普通公式，1个单元格里只能存1格数值，就是1格数值                
• 数组公式，输出有可能是1个数/1个数组，如果输出在1个单元格，那么只显示第1个数，但实际上还是1个数组！                
• 那么，如何把数组公式如果输出为数组时，把数组元素全部显示呢？ 就是选中多个单元格，输入数组公式！计算结果出来前有难度                
• 也就是说，数组公式可以把1个数组输出到1个单元格，存储在其中，但只显示1个数，有时候第1个甚至是false                
• 可见，数组公式保证的是，从输入数据如果是数组，那么中间的每步计算都按数组计算                
• 当然输出结果是否数组得看 每个函数f(x) 是输出数组还是单个聚合数                
   

 

3 例子3 比较if(含数组)  和 {if(含数组)} 的差别

3.1 if(含数组)  和 {if(含数组)} 的差别

• if(含数组)  就是两者中间每步是把数组转为单个值
• {if(含数组)} ，每步都用数组计算

 

3.2 if(含数组)  

• IF(B$4:B$12=E$4,ROW(B$4:B$12),FALSE)
• 中间那步，ROW(B$4:B$12) 变成了4 而不是 {4,5,6,7,8,9,10,11,12} (有数据行的行数序列)

 

3.3 {if(含数组)} 数组公式

• 
• 
• 每个计算步骤里，应该是行列数数组的，都是返回的行列式，数组
• 比如中间那步，ROW(B$4:B$12)  返回是 {4,5,6,7,8,9,10,11,12} 而不是1个数4！！！