其实前面也讲到过这个函数的组合,因为后台总有人问到这个问,说还是不会用,这里呢再来做一个讲解,希望对大家有用。
首先这个函数确实非常有用,已经是一个程式化的函数组合。主要作用就是一对多的查找,也就是给出1个条件,然后在指定区域内取出多个值,想想你就是会知道它的用处有多大了。
下面先以一个简单的事例来讲一下这个函数怎么来用。要求,在A2:D13区域中查询出小强的全部单号。
那怎么来做呢?
先写IF函数
IF(条件,满足条件的行号,较大的值的行号)
在F2单元格输入公式=IF($B$2:$B$13=$F$2,ROW($B$2:$B$13),4^8)
函数解释:
如果B2:B13单元格满足条件(等于F2单元格),那么显示满足条件的行号,否则显示较大值的行号。
4^8表示4的8次方(4*4*4*4*4*4*4*4=65536,“^”符号是乘方符号,在大写环境下按shift+键盘上方的数字6写出乘方符号“^”。
Excel2003版最大行数是65536行,2007及以上的是1048576行,在这里用65536也可以表示最大的数值,一般的表没这么多行数据用65536表示最大也够了)。
在编辑栏中抹黑公式=IF($B$2:$B$13=$F$2,ROW($B$2:$B$13),4^8),然后按F9键,出现对此公式的解读,
{65536;3;65536;65536;65536;65536;65536;65536;65536;65536;12;13}我们发现单元格B3/B12/B13都是符合条件的显示其行号3/12/13,其余的不符合条件的都显示的是最大值65536。
small函数加入其中
small函数在此用途:对if函数得出的结果进行排序=Small(IF,ROW(1:1))
ROW(1:1)返回{1},ROW(2:2)返回{2},在这里用small函数加row函数可以把返回的行号数组从小到大一次排序
F2单元格输入公式=SMALL(IF($B$2:$B$13=$F$2,ROW($B$2:$B$13),4^8),ROW(1:1))然后按ctrl+shift+回车键结束。
在编辑栏抹黑公式=SMALL(IF($B$2:$B$13=$F$2,ROW($B$2:$B$13),4^8),ROW(1:1)),然后按F9键,得到解析=SMALL({65536;3;65536;65536;65536;65536;65536;65536;65536;65536;12;13},ROW(1:1))
ROW(1:1))得出最小的是3,倒是第二小的是12,….最大的是65536.
index函数加入其中
=Index(结果列,small函数运行的到的行号)
=返回行号所对应的结果列中的数据
在F2单元格输入公式
=INDEX(A:A,SMALL(IF($B$2:$B$13=$F$2,ROW($B$2:$B$13),4^8),ROW(1:1)))按ctrl+shift+回车键结束。
把公式往右再往下填充,发现符合姓名:小强的所有结果都显示出来了。超出结果部分,我们发现显示的是0,如何使得为空呢?
index( )&''超出部分显示为空
添加&'',使得超出结果部分显示为空。F2单元格修改公式为
=INDEX(A:A,SMALL(IF($B$2:$B$13=$F$2,ROW($B$2:$B$13),4^8),ROW(1:2)))&''然后按ctrl+shift+回车键结束。
至此index+small+if+row函数就介绍完毕,有兴趣的可以去试着做一做。为了屏蔽错误可以在函数前面加IFERROR(),函数式子是这样:
=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$13=$F$2,ROW($B$2:$B$13),4^8),ROW(1:2)))&'','')
其实这种组合函数,其应用方式可以说是千变万化,可扩展性非常强,后面我们也可以做进一步的论述,比如跨表的1对多查询,用起来是很有意思的。
联系客服