下载安卓APP箭头
箭头给我发消息

客服QQ:3315713922

你不能不知的Excel检索功能:vlookup

作者:课课家教育     来源: http://www.kokojia.com点击数:1575发布时间: 2016-11-30 08:00:28

标签: Excel办公软件Excel试题Excel二级考试

软考,您想通过吗?一次通过才是硬道理

  在Excel众多的实用检索功能中,vlookup一直都很受大家的欢迎,在vlookup函数的使用中,利用vlookup查找返回多个符合条件结果却难倒了不少的小伙伴,所以小编今天就特地为大家整理一下查找返回多个结果的方法,让大家能够熟悉掌握这一部分的内容。

  首先我们第一步讲解的是辅助列法

  我们先看第一个例子,如下图所示:

  B列是产品,C列是供应商。本例要求我们在下表中,把每个产品的供应商显示在相对的后面列中,如何利用vlookup函数查找返回多个符合条件的功能来完成这个效果?

你不能不知的Excel检索功能:vlookup_Excel办公软件_Excel试题_Excel二级考试_课课家

  首先我们对本例进行简单的分析:利用vlookup的数组公式,我们可以直接设置公式,但公式很复杂也容易设置错误,所以今天我们只需要添加一个辅助列,就可以很轻松地解决这个问题,第一步我们先在表格前面插入辅助列和公式:

  A2=B2&COUNTIF(B$1:B2,B2)

 简单的分析

  完成辅助列的插入后,我们就可以在下表中直接设置公式,具体公式如下:

  =IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")

  以上的公式相对比较复杂,小编为小伙伴们简单说明一下:

  我们看到的Column,这可以让公式向右复制时生成1,2,3..序号,并且在后面和产品连接后就会自动生成产品1,产品2,产品3等等,可以帮我们的数据自动进行序号。

  公式中的IFERROR表示的是,当vlookup查找不到时,在excel中就会显示为空白,具体效果图如下图所示:

公式向右复制

  在本例中我们可以用总结出:其实很多复杂的公式,都可以通过添加辅助列加以简化,简化之后就想本例一样,如果小伙伴们无法设置出复杂的公式,那就试着添加辅助列,复杂的公式设置就会变得很简单。

  下面我们讲解第二个主要内容:vlookup数组公式法

  我们先看一下例子:本例要求把如图表中所有张一的消费金额全列出来,具体要求如下图所示:

vlookup数组公式法

  面对操作要求,我们第一时间是先分析操作的步骤,本例想考察的是实现多项查找,我们可以通过对查找的内容进行编号,第一个出现的是后面连接1,第二个出现的连接2,这样我们就可以轻松实现对内容的同意规划。

  首先我们先将公式列出来:

  {=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}

  面对这一大串的公式,相信很多小伙伴已经开始头疼了,不慌,让小编给大家细细讲解:

  其是公式中B$9&ROW(A1)是连接序号,也就是说当公式向下复制时会变成B$9连接1,2,3,以此类推。

  我们也会给所有的张一进行编号,但假如我们需要想生成编号,就需要生成一个不断扩充的区域(INDIRECT("b2:b"&ROW($2:$6))。

  接下来我们就需要在这个逐行扩充的区域内统计“张一”的个数,然后我们再连接上$B$2:$B$6后就可以对所有的张一进行编号。

  IF({1,0}把编号后的B列和C组重构成一个两列数组,这样我们就可以得出一个独立的列。

  最后我们要讲解的是Index+Small函数法,本函数是两个方法的集合,会相对复杂,但是也不难哦。

  我们在excel的日常使用中经常需要用到一对多查找的功能,目的是为了实现筛选。通过用vlookup函数我们也同样可以实现,但是公式将会很难理解。所以这个时候我们可以使用最常用的index+Small公式组合,具体怎么操作呢,让我们一起通过一个例子来讲解一下:

  先看一下例子:如下图所示的客户消费明细表中,要求我们在E列根据E1的姓名,查找出所有该客户的消费金额,具体如下图所示:

查找出所有该客户的消费金额

  在本例中我们需要有用到E3公式,公式套路如下:

  (数组公式,输入后按ctrl+shift+enter三键结束),下面我们就按着要求输入公式,最后得出:

  =IFERROR(INDEX(B:B,SMALL(IF(A$2:A$11=$E$1,ROW($2:$11)),ROW(A1))),"")

  这个看似又复杂又难理解的公式其实原理很简单:IF(A$2:A$11=$E$1,ROW($2:$11)意思就是把所有客户名称和E1进行对比,如果相同则返回行号,否则值为FALSE,是一个检索的步骤。

  公式中的Small(..,row(a1))表示的是我们将会随着公式的向下复制,逐个提取该客户所在的每一个行号数字,也就是将E1的消费金额进行提取。

  最后我们通过Index()根据行号将B列的消费金额填入新的行列中,通过IFERROR()把公式产生错误值时修改为"",也就是空白,这样错误值就不会影响到最后的结果,以上就是本例的全部内容啦,小伙伴们学会了怎么用vlookup函数查找返回多个符合条件的方法了吗?正所谓条条大路通罗马,希望大家在本例中能够找到适合自己的方法。

赞(0)
踩(0)
分享到:
推荐查看
华为认证网络工程师 HCIE直播课视频教程