需要注意的：
- 虽然
+*有时可以和or、and互换，但是他们是有区别的，例如：
=sum(if(and(A1:A2>0,A1:A2<8),B1:B2))
VS
=sum(if((A1:A2>0)*(A1:A2<8),B1:B2))
前者只有当
A1:A2都介于0和8之间时才返回B1:B2的和，否则得到的结果是0；
后者则分开了，比如A1介于0和8之间，A2不介于0和8之间，那么求得的结果是B1的和，而不是0。
AND(logical1,logical2, ...) --详见 and 函数帮助：当所有的logical们都为True时返回True --这是1个单值；
OR(logical1,logical2, ...) --详见 or 函数帮助：当所有的logical们只要有一个为True时返回True --这是1个单值；
(A1:A2>0)*(A1:A2<8) -- 返回的是{True;False}*{True;True}类型的数组相乘的结果{1;0}——这是一个数组；
(A1:A2>0)+(A1:A2<8) -- 返回的是{True;False}+{True;True}类型的数组相加的结果{2;1}——这是一个数组；
就是说And、OR计算出来的总是单值，不是数组。
-
--用于把文本转换成数字，也可以用*1代替，（或者N("xx"+0)?） -
应用: 将文本中的数字取出，例如："收取32.0元"
=LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&1234567890) ),ROW(INDIRECT("1:"&LEN(A1)))))
还可以再加上对 MIN(FIND...) 的值与 LEN(A1) 的比较，以免出现 #N/A
解释：构造了一个搜索数组，从最先出现数字的位置开始取子字符串，然后将其转换为数字(不能转换为数字的为#N/A)，然后用Lookup指定一个9E+307这样的大数，最后返回小于等于他的（实际上也就是最接近它的）数，也就是最长的可以转换为数字的子字符串。
- 应用：取出某列中非空不重复的单元格的内容：
Excel数组公式
这里加一个要求:返回B1:B20内不重复非空单元格。
步骤分解：
（1）首先取出非空不重复单元格所在的行号:
IF($B$1:$B$20<>"",IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),ROW($B$1:$B$20)))
因为MATCH函数不能使用空值作为第一个参数，所以先使用IF语句把空值排除出去。
然后由于MATCH函数返回搜索值中在整个区域第一次出现的位置，将其和自身所在行号进行比对，就可以判断是否重复。
为了方便将它定义为一个名称x，这是一个数组。
（2）然后使用 IF(ROW(1:1)>COUNT(x),"",INDEX(A:A,SMALL(x,ROW(1:1)))) 得到想要的结果，这是使用数组得到一个单值。
- 应用：返回B列内等于“A”的对应A列的记录：
Excel数组公式
步骤分解：
（1）首先使用 IF($B$1:$B$20="A",ROW($B$1:$B$20)) 实现判断B列等于"A"的记录的行号。
注意这个结果是一个数组，使其有了不同的用途，为了方便可以将其定义为名称 x 。
（2）使用 SMALL(x,ROW(1:1)) 把符合条件的行号按照从小到大的顺序列出来，这是使用数组得到一个单值。
（3）使用 INDEX($A$1:$A$20,SMALL(x,ROW(1:1))) ，把A列对应的记录取出来。
（4）为了不出现错误值，使用 SUM(($B$1:$B$20="A")*1)<ROW(1:1) 作为判断条件，一般使用SUM数组公式，可以组合多个条件，且的关系使用 *，或的关系使用 + ，只有一个条件时要加个常用条件，例如本例 *1 。
如果在增加一个条件，则在此主公式中加入 IF(($B$1:$B$20="A")*($C$1:$C$20="B"),ROW($B$1:$B$20)) , 然后相应地在 (SUM(($B$1:$B$20="A")*1)<ROW(1:1) 中加入相应的判断条件
（5）最终公式为：
=IF(SUM(($B$1:$B$20="A")*1)<ROW(1:1),"",INDEX($A$1:$A$20,SMALL(x,ROW(1:1))))
-
返回重复次数最多的值
Excel数组公式
解题步骤：
（1）首先使用IF(COUNTIF($B$1:$B$20,$B$1:$B$20)=MAX(COUNTIF($B$1:$B$20,$B$1:$B$20)),ROW($B$1:$B$20))
得到一个数组,用于符合条件的行的行号，也就是G列所示的值，为了方便可以将其定义为名称 x 。
（2）使用 INDEX($C$1:$C$20,SMALL(x,ROW(1:1)))，把C列中的内容取出来。
（3）为了不出现错误值，可以使用 IF(COUNTIF($B$1:$B$20,$B$1:$B$20)=MAX(COUNTIF($B$1:$B$20,$B$1:$B$20)),1) 得到另外一个数组，用于符合条件的行的计数，将其定义为名称y。然后使用 SUM(y)<ROW(1:1) 作为判断条件。
（4）最终公式为
=IF(SUM(y)<ROW(1:1),"",INDEX($C$1:$C$20,SMALL(x,ROW(1:1))))
- 按照重复次数的大小列示内容
Excel数组公式 - supershll - 记忆里
解题步骤：
（1）首先得到不重复单元格所在的行号数组：
IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),ROW($B$1:$B$20))
如E列所示。
（2）然后得到不重复单元格的重复次数数组:
IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),COUNTIF($B$1:$B$20,$B$1:$B$20))
如F列所示
（3）为了进行排序的同时又得到行号，所以综合E列和F列的数据，使用如下：
IF(MATCH($B$1:$B$20,$B$1:$B$20,0)=ROW($B$1:$B$20),COUNTIF($B$1:$B$20,$B$1:$B$20)+ROW($B$1:$B$20)/1000)
这样整数部分是重复次数，小数部分是行号。
如G列所示，这个数组符合我们的使用要求了，将其定义为名称 x 。
（4）使用LARGE函数进行排序。LARGE(x,ROW(1:1)) ，这是使用数组得到一个单值。
（5）然后使用MOD函数取出行号。MOD(LARGE(x,ROW(1:1))*1000,1000)
（6）然后在使用INDEX函数取出对应行的记录。INDEX($B$1:$B$20,MOD(LARGE(x,ROW(1:1))*1000,1000))
（7）最后屏蔽错误值，IF(ROW(1:1)>COUNT(x),"",INDEX($B$1:$B$20,MOD(LARGE(x,ROW(1:1))*1000,1000))), 也可以使用ROW(1:1)>SUM(1/COUNTIF($B$1:$B$20,$B$1:$B$20)) 作为判断条件, 这个SUM的意思就是说区域内去重后的单元格的个数(假设重复的单元格a的个数n,n个(n/1)相加最后变成1)。
- 返回区域内符合条件的值
Excel数组公式 - supershll - 记忆里
解题步骤：
（1）首先使用 IF($A$2:$H$12=1,ROW($A$2:$H$12)*10000+COLUMN($A$2:$H$12)) 找出符合条件的单元格行号和列号数组，这是一个2维数组。将其定义为名称 x
（2）如果想使用INDEX函数就需要找出相关的参数来，即INDEX(数据区，行数，列数) 。使用INT(SMALL(x,ROW(1:1))/10000) 获得行号, MOD(SMALL(x,ROW(1:1)),10000) 获得列号。
（3）最后使用INDEX获得结果，此公式是姓名列的公式(其中Column()-17是根据公式所在列设置的) INDEX($A$1:$H$12,INT(SMALL(x,ROW(1:1))/10000),MOD(SMALL(x,ROW(1:1)),10000)+COLUMN()-17)
（4）最后就是屏蔽错误值的问题。使用IF(SUM(IF($A$2:$H$12=1,1))<ROW(1:1),"","原公式")
-
按顺序返回不重复值
Excel数组公式 - supershll - 记忆里
解题步骤：
（1）使用MATCH函数获取不重复行的行号和值的数组，将其定义为名称x。
IF(MATCH($B$1:$B$6,$B$1:$B$6,0)=ROW($B$1:$B$6),ROW($B$1:$B$6)*100+$B$1:$B$6)
（2）使用SMALL函数：
SMALL(MOD(SMALL(x),{1;2;3}),100),ROW(1:1))
- 返回数字中不重复的前3位数。
Excel数组公式 - supershll - 记忆里
解题步骤：
（1）使用 ROW(INDIRECT("1:"&LEN(A1*B1))) 求出乘积结果的位数序号数组，如F列所示，定义为名称 x
（2）使用 MID(A1*B1,x,1) 把乘积的结果按照原有顺序组成内存数组,如G列所示，定义为名称 y
（3）然后使用MATCH函数获取不重复行的行号和值的数组，定义为名称 m ，如J列所示 IF(MATCH(y,y,0)=x,x*100+y)
（4）使用SMALL函数，SMALL(MOD(SMALL(m),{1;2;3}),ROW(1:1)) ，如M列所示,这是由数组得到的单值
（5）将结果串起来 TEXT(SUM(SMALL(MOD(SMALL(m),{1;2;3}),{1;2;3})*10^{2,1,0}),"000") 再次使用数组得到一个单值。
- 总结：使用数组公式，一定要时刻明白，何时结果是数组，何时结果是单值。然后再根据结果参与计算，例如送数组中取出单值或把单值组合成数组
网友评论