万万没想到(vlookup函数一对多查找值)vlookup一对多查询,Excel函数:VLOOKUP函数进阶用法之一对多查找匹配详解(附案例解析),翁媳爱爱,基层组织条例,

楼盘 nanfang 2023-12-10 16:22 112 0

1.vlookup匹配出多个值后求和

各位小伙伴们大家好,我是Excel归故乡今天来给大家说一下如何用VLOOKUP函数进行一对匹配这个技巧在实际工作也是非常常用,通常问的朋友也比较多,今天就来给大家详细介绍一下首先来看一下一对多匹配是什么意思,顾名思义就是一个查找值对应多个结果,如下图所示,比如一个班级有多名同学,我们怎么能够一次性将二班的同学匹配出来?这就是我们今天要讲解的用法。

2.vlookup匹配出多个值后求和一个公式完成

首先我们来分析一下,如果要将二班全部匹配出来,直接用二班匹配肯定是不行的,根据VLOOKUP的特性,只能匹配到第一次二班出现时对应的同学那要想将二班对应的同学全部找出来,就要将这些同学所在行对应的二班做出区分,我们可以想办法给她做一个编号,比如改成,二班1,二班2,二班3……以此类推,而我们匹配的时候也使用二班1,二班2,二班3这样对应的内容去匹配,简单来说就是保证每个“二班”都不一样,无重复,是不是就可以匹配出来了?。

3.vlookup匹配出多个值后求和为啥全部为0

思路有了,就看如何实现首先是如何将二班进行编号,我们观察发现上面举例中二班后面的数字,实际上就是二班这个关键字从上到下第几次出现的次数,大家可以思考一下是不是这个逻辑那么我们就可以用COUNTIFS来判断出现次数,那我们就添加一个辅助列来实现出现次数(后面的COUNT类专题会详细讲解)。

4.vlookup匹配出多个值后求和为0怎么办

我们来对班级进行扩展选区的计数=COUNTIF(B$2:B2,B2),注意这里参数的写法,条件区域我们对前半部分绝对引用,后半部分相对引用,是为了让它在我们下拉公式的时候,这个区域自动扩展,从而得到计算出现次数的效果。

5.vlookup匹配多个结果在同一单元格

可以看到我们标黄的部分就是二班所在的位置,分别对二班出现次数进行了编号,如下图所示:

6.vlookup一对多匹配并求和

但是到这里,还没达到我们需要的二班1,二班2,二班3……这样的效果,其实有了数字就已经很接近了,只需要将数字所在的单元格与这个数字进行连接即可,公式=B2&COUNTIF(B$2:B2,B2),效果如下:

7.vlookup一个值查找多个对应数值

第二步,我们将相同的班级进行了区分之后,就只需要用对应的班级+编号去匹配,那如何生成随着单元格下拉而生成的二班1,二班2,二班3呢?我们可以用ROW函数,这个函数与我们之前讲过的COLUMN函数的功能恰好相反,它是用来获取参数单元格的行号,比如ROW(A1),就返回A1所在的行,以此类推,用法如下:

8.vlookup存在多个结果如何选择

而有了这个函数后,我们VLOOKUP函数的第一参数匹配值是不是就可以用要匹配的班级连接ROW函数,在下拉的时候是不是就可以自动生成二班1,二班2,二班3……请看下图:

9.vlookup和sum求和结合

以上两个问题解决之后,我们发现,此时的查找值和数据源中的数据此时是对应的且唯一的,那这时候就可以直接用VLOOKUP函数进行匹配,最终整理结果如下:

10.sum和vlookup一起使用

但是我们发现二班的同学虽然是全部找出来了,但是后面存在一些错误值,这是因为二班总共就只有4位同学,到第五个单元格时,查找值就是二班5,但是数据源中并没有二班5了,自然就会出现错误值为了让表格美观,我们可以在外层嵌套一层iferror函数,让它出现错误值的时候显示空白即可,就得到了我们最终的公式。

=IFERROR(VLOOKUP(I$1&ROW(A1),$A$1:$G$22,3,),"")我们在实际操中,因为可能不知道会有多少个匹配值,因此我们可以尽可能的多填充一些单元格,知道出现个空白为止,这样防止有漏掉的选项没被抓取到。

到这里我们就完成了VLOOKUP函数的一对多匹配,其实写函数并不困难,重要的是思路,每一篇文章都在给大家讲解思路,一步一步带着大家去完成操作,让大家知其然且知其所以然本期给大家讲解的是使用辅助列的方法,一对多查找也有不使用辅助列的方法,但是要用VLOOKUP函数的话会比较复杂,后面我们会讲解其他函数来完成。

原创不易,请大家多多支持,期待与大家一起进步!

标签列表