Excel一对多查询

千彩一工作室 2019-04-18

展开全文

在我们使用Excel进行数据处理过程中，可以说查询是必须要学会的一个技能。如果对Excel稍微熟悉一些的朋友应该都会知道，Excel中最常用的查询函数莫非是VLOOKUP了，但是大家有没有发现一个问题，使用VLOOKUP函数是无法完成一对多的查询的，但是一对多的查询却是非常常见的。今天，我们就来给大家讲解一下这个知识点哟。

按照我们一贯的做法，还是先来看一个例子吧。下面是某个年级的学生兴趣爱好的统计，我们如何根据爱好一对多查询出所对应的的学生呢？

我们需要达到的效果如下：

我们使用到的函数为IF、SMALL、ROW、INDEX四个，为了照顾到所有小伙伴，我们还是先对各个函数的基本使用方法做个简单的介绍。

一、函数基本使用方法

1.IF函数

功能：判断是否满足某个条件，满足返回一个值，不满足返回另外一个值。

语法：IF(logical_test,value_if_true,value_if_false)

参数解释：

logical_test：计算结果为 TRUE 或 FALSE 的任意值或表达式。

value_if_true：logical_test参数为true时的返回值。

value_if_false：logical_test参数为false时的返回值。

2.small函数

功能：返回数据组中的第n个最小值。

语法：=small(array,n)

参数解释：

array：为需要找到第 k 个最小值的数组或数字型数据区域。

n：返回的数据在数组或数据区域里的位置(从小到大)。

3.row函数

功能：返回一个引用的行号

语法：=row(reference)

参数解释：

reference：为需要得到其行号的单元格或者单元格区域。

4.index函数

功能：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。

语法：=index(array,row_num,column_num)

参数解释：

array:单元格区域或者数组常量。

row_num:数组或引用中要返回的行序号。如果忽略，则column_num参数必须有。

column_num:数组或引用中要返回的列序号。如果忽略，则row_num参数必须有。

注意：根据以上的参数介绍可知，row_num和column_num至少必须有一个存在。同时，该函数还有另外一种语法形式INDEX(reference,row_num,column_num,area_num)，此处不再介绍。

二、例子的实现

根据上面对四个函数的介绍，大家有思路了吗？我们一起来看一下吧，F2单元格的公式如下：

{=IFERROR(INDEX($B$3:$B$30,SMALL(IF($D$3:$D$30=F$1,ROW($1:$28),10000),ROW(F1)),),"")}

下面对以上的公式做个解释：

1、IF($D$3:$D$30=F$1,ROW($1:$28),1000)，判断D列的爱好是否等于F1的值，如果等于返回该单元格的行号（提醒：特别注意此处需要将D3：D30当作一个区域，在之后index的引用中D3的行号是1），否则返回10000（这个值根据实际情况，返回一个相对较大的值就可以）。返回的结果为{1;2;3;10000;10000;6;10000; 10000; 10000; 10000; 10000; 10000; 10000; 10000;15; 10000; 10000; 10000; 10000; 10000; 21;22; 10000; 10000; 10000; 26;27;10000}。

2、SMALL({1;2;3;10000;10000;6;10000; 10000; 10000; 10000; 10000; 10000; 10000; 10000;15; 10000; 10000; 10000; 10000; 10000; 21;22; 10000; 10000; 10000; 26;27;10000},ROW(F1))，返回第n大的行的行号，此处ROW(F1)在填充过程中依次返回1，2，3……。

3、INDEX($B$3:$B$30,1 ,)使用index函数返回区域B3:B30中的指定行（第1行）的值，此处省略column_num的值。

4.最外面一层IFERROR函数是为了使没有查询到的行显示为空值。

提示：对于复杂的公式，可以使用【公式】选项卡下的【公式审核】下的【公式求值】按钮逐步查看公式的运算结果。