Hello,大家好,最近有粉丝问到:有没有什么简单的方法,能在不添加辅助列的情况下快速地搞定多条件查询,他觉得利用辅助列比较low。今天跟大家分享一种我觉得非常适合新手使用的方法,就是利用lookup函数,使用这个函数即使我们不理解公式的含义,只需要记得固定的格式,直接套用即可,下面就让我们来学习一下吧
大多数人非常熟悉的vlookup函数,就是由lookup函数改进得来的,所以lookup函数也是一个查找函数。
Lookup函数:从单行或单列中查找数据
语法:=LOOKUP(lookup_value, lookup_vector, result_vector)
第一参数:需要查找值
第二参数:查找的数据区域,一行或者一列
第三参数:返回的结果列,需要注意的是第三参数的数据个数必须与第二参数的数据格式相等
在使用lookup之前,我们还需要对第二参数查找的数据区域进行升序排序,否则的话可能会返回错误的结果。还有一点需要注意的是lookup的查找原理与vlookup的近似匹配是一模一样的,如果找不到精确的结果,lookup就会返回小于或等于查找值的最大值。下面我们来通过一个实际的例子来了解下lookup函数
比如在这里,我们想要求一下张飞的考核得分,如果我们不对数据区域进行升序排序,直接使用lookup函数来查找结果,公式为:=LOOKUP(L4,B2:B9,C2:C9),他的结果是208,这个结果并不是张飞对应的考核得分,当我们对查找区域进行升序排序后,才能找到张飞对应的195分,所以当我们使用lookup函数的时候,需要首先对查找数据区域进行升序排序。还有就是第三与第二参数的数据个数必须相等,否则的话函数就会返回错误值
1.为什么要使用多条件查询
在Excel中如果我们的查找值在查找区域中存在重复,那么函数仅仅会返回第一个找到的结果,这样的话就可能得到一个错误的结果
如下图,李白是存在重复值的,在这里我们想要查找3班李白的总分,如果仅仅将李白作为查找值,我们使用vlookup函数来查找数据他的结果是186,这个是1班李白对应的分数,并不是3班李白对应的分数,这样的话结果就是错误的,之所以会产生这样的结果的原因是因为186是第一个李白对应的数据
2.lookup多条件查询
既然一个条件我们不能查找到精确的结果,那么我们就要增加条件来获得精确的结果,这个就是多条件查询存在的意义
在这里我们只需要将公式设置为:=LOOKUP(1,0/((B2:B9=J4)*(C2:C9=K4)),D2:D9)然后点击回车即可查找到正确的结果。这个函数是一个数组公式,理解起来可能比较困难,下面我们来剖析下这个函数的结构
公式:=LOOKUP(1,0/((B2:B9=J4)*(C2:C9=K4)),D2:D9)
第一参数:查找值,1
第二参数:查找的数据区域,0/((B2:B9=J4)*(C2:C9=K4))
第三参数:返回的结果列,D2:D9
主要跟大家讲讲解下他的第二参数,0/((B2:B9=J4)*(C2:C9=K4))这是一个数组公式,B2:B9=J4与C2:C9=K4会与数据区域的中的每一个元素都发生计算,如下图所示
B2:B9=J4他的意思是:班级等于3班的,它的结果如下图橙色区域所示,是一列逻辑值。C2:C9=K4的结果如下图绿色区域所示,它的意思是姓名等于李白。随后将这两列结果相乘,可以将fales看作是0,true看作是1,相乘之后会得到一列0和1的数字,只有两个条件都满足它的结果才是1,最后我们再用0除以相乘结果,分母为0的话就会返回错误值,lookup会自动的将错误值忽略掉,这样的话就仅仅只剩0这个结果了,这样的话也就不用进行升序排序了。这个就是第二参数的计算过程
如果你觉得这个函数比较难懂,只需要记得这个公式的设置格式即可。格式为:=lookup(1,0/((条件1)*(条件2)*(条件3)),结果列)有几个条件就设置几个条件即可
以上就是今天分享的全部内容,怎么样?你学会了吗?