excel函数怎样一对多查询
1、打开excel2010,打开如图所示表格,我们需要查询美国的大学。
2、我们嚼但匙噻首先使用IF函数查询美国大学是位于第几行。=IF(C2:C13=E1,row(C2:C13))如果C2:C13中哪一个单元格等于E1(E1也就是“美国”),就利用row(C2:C13),返回那一格所在的行数。这实际上是一个数组公式,返回的是一组数在编辑栏选中“IF(C2:C13=E1,row(C2:C13))”,按F9,就可以看到计算的结果{2;3;4;5;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE}意思是第2,3,4,5,10行是“美国”,其余行不是“美国”,显示为FALSE
3、注意:因为公式完成后我们需要向下复制,所以需要把单元格相对位置改成绝对位置。IF(C2:C13=E1,row(C2:C13))需要改成IF($C$2:$C$13=$E$1,ROW($C$2:$C$13))
4、接下来我们怎样把“美国”所在的行挑选出来,即把第2,3,4,5,10行挑选出来,忽略掉FALSE,我们可以使用SMALL函数SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1))SMALL函数可以求出一组数中的第K个最小值(可以是第一个最小值,可以是第2个最小值,也可以是第3个最小值,也可以是第K个最小值)但SMALL只处理数值,所以非数值的FAlSE就会被剔除,这样我们就能够把“美国”所在的行数挑选出来。
5、注意:ROW(A1)在SMALL函数中只是为了得到一个递增序数ROW(A1)=1,往下拖动复制的时候,ROW(A2)=2,ROW(A3)=3,ROW(A4)=4,所以ROW(A1)中的A1单元格并没有什么特殊的意义,不是非得用A1,其实使用B1,C1,D1,就算是Z1结果也是一样,因为使用ROW(Z1)函数得出的结果也是1,往下拖动也会递增序数。
6、通过SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1))把“美国”所在的行数挑选出来之后,我们就可以得用INDEX函数挹掷秦把把美国的大学挑选出来。INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)))查询B:B,也就是查询B列(大学名称列)美国大学所在行我们已经挑选出来,也就是SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1))
7、=INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)))这是一个数组公式,完成之后需要按“Ctrl+Shift+Enter”确认。结果变成{=INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)))}
8、向下拖动复制公式,美国大学查询出来后会出现“#NUM!”,这是因为在前面得出的数组{2;3;4;5;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FA讣嘬铮篌LSE}中FALSE是无法被SMALL函数处理的。所以最终结果会提示“#NUM!”。我们需要添加一个IFERROR函数来解决这个问题=IFERROR(INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1))),"")意思就是如果刚才的函数出现错误,则显示为“”,也就是显示为空白。添加IFERROR后,按Ctrl+Shift+Enter”确认,往下拖动复制,就会发现“#NUM!”变成了空白。
9、如果大学的数目有所增加,变成前100排名,或者前1000排名,我们只需要把函数稍微更改一下就可以了,把$C$2:$C$13改成$C$2:$C$10000,函数可以统计$C$3:$C$10000范围内的数据,右边的公式预先往下拖动,左边添加数据后,右边就会自动计算出结果。至此,我们就完成了excel函数一对多查询。