点击上方蓝字,关注公众号
大家好,今天小V给大家带来VLOOKUP的又一种用法!
vlookup函数是Excel中极其重要的一个函数。
温故知新
vlookup函数语法结构:
VLOOKUP
(lookup_value,table_array,col_index_num,range_lookup)
先来回顾下前面我们学习的VLOOKUP函数用法:
精确匹配,vlookup精确匹配可以代替IF函数多层嵌套,查找更简单;
模糊匹配,vlookup模糊匹配可以代替IF函数多条件判断,可以用来等级判断,提成比例计算;
反向查询,对于返回值在查找值左边列的情况,使用IF函数构造新的查询区域,来完成vlookup查询;
多条件查询,当查询的匹配条件有多个时,使用IF函数构造新的查询区域和查询条件,完成匹配。
今天我们来学习VLOOKUP应用系列5:一对多查询
如何在Excel中实现一表链接多表?
应用场景
我们来看这样一个案例,
情景:左表记录的是小可和小花今年的存款详情,现在小花想查询下自己今年的存款明细。如何快速查找。
需求:查询返回小花的所有存款额
左表中有多个可匹配的值小花,但是VLOOKUP函数的特性是只要找到匹配值就不会再往下寻找,也就是只能返回第一个匹配的值10000。
该怎么解决呢?
解决思路
VLOOKUP函数自身有其局限性,那就是VLOOKUP函数只能返回查找到的符合条件的第一个值。也就是说,只要查找到了第一个符合条件的值,VLOOKUP函数就不再查找了。因此VLOOKUP函数无法返回符合条件的多个值,也就是自身无法完成一对多的查询。
既然有多个相同的值时,只能返回第一个,那我们就给这多个相同的值加上序号,让他们变为不同的值,把他们有效区分开来,这样查找多个“小花”,就转换为查找“小花1”,“小花2”,“小花3”的问题。
解决方法
针对上面例子中的解决方法就是,把“小花”变为“小花1”,“小花2”,“小花3”,再使用VLOOKUP函数查找。
第一步:添加辅助列
对小花进行计数,第一次出现记为1,第二次出现记为2,第三次出现记为3,
实现计数我么可以使用COUNTIF,条件计数,
表达式:=COUNTIF($B$2:B2,B2)
计算出小花出现的次数,再将“小花”和次数拼接到一起;
表达式:=B2&COUNTIF($B$2:$B2,B2)
第二步:VLOOKUP查找
查找值是“小花1”,“小花2”,“小花3”,右表中的查找值是“小花”,在这里需要把小花也加上出现的次数,这里要怎么加呢?
这里可以使用ROW()函数,返回行号,行号表示次数,再把“小花”和ROW()返回值拼接起来
所以查找值就是:“小花”&ROW(1:1)
查找区域是包含辅助列的区域:$A$1:$C$7
返回值在第3列,选择精确匹配FALSE,所以,
表达式:=VLOOKUP($F$3&ROW(1:1),$A$1:$C$7,3,FALSE)
下拉填充公式就可以分别查询出小花的存款额,但是发现小花存了3次款,下拉到第四个单元格是就会出现#N/A错误
第三步:修改错误值
这里我们可以通过IFERROR函数设置当值为错误时,返回“”,
修改表达式:=IFERROR(VLOOKUP($F$3&ROW(1:1),$A$1:$C$7,3,FALSE),"")
向下填充。
加上IFERROR之后,查询不到的情况就以一个空单元格显示。
下期预告
下一期,我们讲讲VLOOKUP函数结合通配符使用。
扫
码
关
注
获取更多精彩内容