excel一对多查询（使用index,small,if,row）

excel一对多查询（使用index,small,if,row）

现在我们来学习excel中一对多的查询，需要使用index,small,if,row,iferror等5个函数，是一个相对比较复杂的查询运用。

首先打开如下表格，现在我们需要把成绩为优的学生筛选出来，而且随着学生名单的变化自动生成。

现在我们来拆开讲解这个函数:

首先我们使用if函数，输入“=if(C3:C7="优",row(C3:C7))”，这个函数的意义是如果C3到C7单元格中的内容等于“优”，则返回它们的行号“row(C3:C7)”,否则返回错误“false”。

这是一个数组公式，按F9可以查看运算结果：{3;FALSE;FALSE;6;7}，这个函数运算得出的不是一个值，而是一组数。

现在我们给这个数组添加一个small函数，求这个数组的第K个最小值。

“=SMALL(IF(C3:C7="优",ROW(C3:C7)),ROW(A1))”

其中“IF(C3:C7="优",ROW(C3:C7))”也就是数组{3;FALSE;FALSE;6;7}，

ROW(A1)＝１，返回数组最小的值，也就是3

如果函数往下拖动会变成ROW(A2)＝２，返回数组第2小的值，也就是6。

再往下拖动会变成ROW(A3)＝3，返回数组第3小的值，也就是7。

这样我们就可以通过函数得到等于“优”的行数，从小到大依次是第3，第6，第7行。

通过“=SMALL(IF(C3:C7="优",ROW(C3:C7)),ROW(A1))”得出等于“优”的行数之后，我们可以再添加index函数，去查询等级是“优”的姓名。

"=index(A:A,SMALL(IF(C3:C7="优",ROW(C3:C7)),ROW(A1)))"

在姓名列A:A查询，返回等级是“优”的行数，

SMALL(IF(C3:C7="优",ROW(C3:C7)),ROW(A1))返回数组{3;FALSE;FALSE;6;7}的最小行数，也就是第3行。

因为查询区域A:A只有一列，只能返回它本身的列数，也就是1，所以可以忽略。

完成"=index(A:A,SMALL(IF(C3:C7="优",ROW(C3:C7)),ROW(A1)))"函数之后，由于需要拖动向下和向右复制，所以需要把C3：C7设置成绝对位置，设置之后就成"=index(A:A,SMALL(IF($C$3:$C$7="优",ROW($C$3:$C$7)),ROW(A1)))"。

由于这是一个数组公式，输入完成之后需要按“Ctrl+Shift+Enter”。结果变成

{=index(A:A,SMALL(IF($C$3:$C$7="优",ROW($C$3:$C$7)),ROW(A1)))}

向下拖动复制公式，第4行之后会出现“#NUM！”，excel提示出错。

这是因为数组{3;FALSE;FALSE;6;7}中，FALSE是无法参与到small函数的最小值排序的，所以excel提示出错，而且数组{3;FALSE;FALSE;6;7}只有5个数，当ROW(A1)的结果为6时，数组根本不存在最小的第6个数，excel也会提示出错。

怎样解决这个问题呢？我们在原有函数的基础上添加"iferror"函数，

{=IFERROR(INDEX(A:A,SMALL(IF($C$3:$C$7="优",ROW($C$3:$C$7)),ROW(A1))),"")}，如果函数出现错误，显示为“”，也就是显示为空白。

往下往右拖动复制公式。

如果左边的数据需要不断增加，那可以把$C$3:$C$7改成$C$3:$C$10000，意思就是函数可以统计$C$3:$C$10000范围内的数据，这样就可以不断增加左边的数据，右边的函数可以自动对增加的数据筛选出等级“优”的名单。