【公式注释】
提示:输入数组公式,需在公式编辑栏写好公式化,同时按键盘 Ctrl Shirt Enter三键,方能得到正确结果。
=MIN(FIND(ROW($1:$10)-1,A1&1/17))
1、ROW($1:$10)生成数组
{1;2;3;4;5;6;7;8;9;10}
2、ROW($1:$10)-1生成数组
{0;1;2;3;4;5;6;7;8;9}
3、1/17返回一个包含0-9这10个数字的字符串
1/17=0.0588235294117647
4、A1&1/17 生成一个字符串,也就是在A1的字符串后链接1/17生成的0-9所有数字的字符串,形成一个新的字符串。
'wd123fdf0.0588235294117647'
5、FIND(ROW($1:$10)-1,A1&1/17)的意思是在
'wd123fdf0.0588235294117647'这个字符串中寻找
{0;1;2;3;4;5;6;7;8;9}这10个数字,并标记其在字符串中的位置,将公式用【F9】抹黑,生成如下数组
{9;3;4;5;20;12;24;23;13;19},其对应{0;1;2;3;4;5;6;7;8;9}这10个数字在字符串'wd123fdf0.0588235294117647'中的位置,0的位置为9,1的位置为3,2得位置是4......
6、MIN({9;3;4;5;20;12;24;23;13;19}) 最后用min函数取
{9;3;4;5;20;12;24;23;13;19} 的最小值,自然就是第一个数字在字符串所在的位置。
根据姓名来查询房屋面积,A列是包含姓名的地址。
【公式注释】
=LOOKUP(9^9,FIND(D1,$A$2:$A$10),$B$2:$B$10)
1、9^9,9的9次方,代表一个很大的数字,在单元格输入公式运算
2、FIND(D1,$A$2:$A$10),返回数组
{#VALUE!;#VALUE!;16;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
如果在A列中找到改字符,那么就显示其在字符串中的位置,见16。百里守约 在A4的字符串中的位置是16
3、=LOOKUP(9^9,FIND(D1,$A$2:$A$10),$B$2:$B$10)
Lookup函数是二分法的查找原理,不管懂不懂这个原理。
只要知道在FIND(D1,$A$2:$A$10)生成的数组当中,查找9^9这么大的数字,lookup会返回小于9^9的最后一个数字。在{#VALUE!;#VALUE!;16;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} 中就是16,也就是A4单元格所在的位置。
我们需要的结果自然就是A4单元格对应的B4单元格。
如果要反过来,根据姓名地址,查询房屋面积。如下图。
【结果】
可以看到Find函数的参数调换了。
FIND($D$1:$D$6,A2)返回的数组是:
{#VALUE!;#VALUE!;#VALUE!;12;#VALUE!;#VALUE!}
假如使用 FIND(A2,$D$1:$D$6)则发生错误,搜索不到结果。
只是调换了一下参数,为啥结果不相同了?
这是因为,Find函数的第一参数是支持数组的用法的。
使用FIND($D$1:$D$6,A2)的时候,我们用鼠标抹黑$D$1:$D$6,按F9返回的是数组{'百里守约';'李白';'夏侯敦';'小李飞刀';'庄周';'曹操'};
然后在A2单元格里面,依次查找每个姓名,找不到就返回#VALUE!,找到就显示姓名在A2字符串中的位置;
最后生成下面的数组;
{#VALUE!;#VALUE!;#VALUE!;12;#VALUE!;#VALUE!}
A2包含【小李飞刀】这个姓名,当Find搜索【小李飞刀】的时候自然就返回其在A2字符串中的位置12。然后Lookup大法和上面同样的道理。
【公式注释】
1、FIND(D2,$A$2:$A$8)返回数组
{1;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}
2、IFERROR(FIND(D2,$A$2:$A$8),0)返回数组
{1;0;0;1;0;0;0},IFERROR是对
{1;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}
中的错误进行处理,使错误变成0
3、IFERROR(FIND(D2,$A$2:$A$8),0)*$B$2:$B$8
返回数组
{88;0;0;87;0;0;0},因为姓氏都是在字符串的第一位的,所以和$B$2:$B$8【分数】相乘后就可以用max取其最大值了。
IFERROR(FIND(D2,$A$2:$A$8),0)生成的数组
乘以
$B$2:$B$8生成的数组
{1;0;0;1;0;0;0}
X
{88;45;26;87;56;68;98}
‖
{88;0;0;87;0;0;0}
最后Max函数取其最大值。
当然,我们也可以用Left函数生成一个数组,但姓氏不总是只有一个字符的,姓氏是“欧阳”就出错了。当然拉,用Left函数也是可以的,len函数读取一下姓氏的字符数,代入Left函数第二参数即可。
另外,如果版本支持,Excel2016新增的Maxifs函数可以使用,其支持通配符用法。
下面2010新增函数也可以完成,不需要数组三键。
公式来自:流浪铁匠
=AGGREGATE(14,6,B$2:B$8/FIND(D2,A$2:A$8)^0,1)
统计姓名地址A列单元格区域单号“西关街”的单元格个数。
公式:=COUNT(FIND(C1,A2:A10))
有上面几个例子的铺垫,应该会明白此公式的思路了。
此例也可以通过用Countif辅助列的方法来实现。
辅助列公式:
=COUNTIF(A$2:A2,'*'&$C$1&'*')
求最大值公式:
=MAX(B2:B10)
联系客服