词条信息

admin
admin
超级管理员
词条创建者 发短消息   

相关词条

热门词条

更多>>
如何防止网站过度优化?
对于一名“新手站长”而言,刚刚做出一个新网站,此刻,心情可以说是非常激动的,所以就想赶快把网站提交到搜索引擎,...
科创板和注册制真的要来了!
科创板和注册制越来越近。中共中央总书记、国家主席、中央军委主席、中央全面深化改革委员会主任习近平23日下午主持...
微信大更新!小程序变身效率神器
微信 7.0.0 大版本更新的余韵未了,许多人还在熟悉新界面和「时刻视频」带来的变化之时,猝不及防,微信又...
百度“偏爱”百家号,为了体验还是为了钱?
一直以来,百度搜索长期占据着国内搜索引擎市场的“头把交椅”,大量网民通过百度搜索,在纷繁的互联网上寻找自己想要...
百度回应:百家号在搜索结果中占比过高
北京时间1月22日晚,一篇名为《搜索引擎百度已死》的自媒体文章刷屏,质疑其搜索结果的公平性。文章中举例指责...
解决网 >>所属分类 >> 软件技术    office    Excel   

excel多条件查找三种方法

标签: exce lookup vlookup indexmatch

顶[0] 发表评论(0) 编辑词条

本文分享三种excel多条件查找函数方法,分别是:lookup多条件查询、vlookup多条件查找、indexmatch多条件查找

问题是如何返回单据编号和物料长代码对应的含税数额。如下表:

 
其实这位学员的问题就是excel的多条件查找问题。

excel的多条件查找
 
下面通过一个实例跟大家分享一下常用的3种excel多条件查找函数。
下表是某电商公司的客户投诉表,现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号,返回到A表的E列中。

下面是三种excel双条件查找返回的方法,依次来看:
 
目录

第一 excellookup多条件查询编辑本段回目录


excel多条件查找函数

函数公式:=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)
公式解析:首先通过A3单元格与B表I列数据做对比,同时用B3单元格与B表J列信息做对比。
在excel中如果两个单元格对比,相等则返回TRUE,在四则运算中用1表示。如果不相等则返回FALSE,使用0表示。
 
那么(A3=$I$3:$I$19)*(B3=$J$3:$J$19)这部分运算的结果就只有0或者1两种情况,因为只有0*1、1*1、1*0这三种情况。
 
按照二分法原理,lookup函数会在二分位处查找符合条件的数据。大家都知道lookup函数想要精准查找那么这组数值必须要升序,但实际上这组数据运算结果0和1的顺序是混乱的。
 
所以就想到了用0来除以0和1的方式来区分。由于分母不能为0,所以0/0返回的是错误,0/1返回的结果为0。Lookup函数在查找的时候是忽略错误的,所以只有数据运算结果为1的公式满足条件。
 
那么我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是将正确结果用0表示,其他的变成错误值,利用函数查找忽略错误这个特点完成查找。
 
总结:本函数由于使用了二分法原理查找,所以如果数据量较大时运算会很慢。
 

第二 vlookup多条件查找编辑本段回目录

 
vlookup函数是我们最常用的函数,vlookup函数主要用于垂直方向上向右查找。如下图:

excel多条件查找函数

使用G2单元格在A列中查找,如果查找到对应单元格则返回A列向右第二列的数据。简而言之:=VLOOKUP(查找什么,在哪查找,从条件所在列算起找到后返回对应的第几列数据,精确或模糊查找)。
 
那vlookup如何才能完成多条件查询呢?。
还以客户投诉表为例,按照姓名&地区来匹配产品型号返回到E里中。


其实我们是可以将A、B两表中插入辅助列,将姓名和地区都合并到一个单元格中然后使用vlookup来完成。


但是插入2个辅助列后整个表列数发生变动,在工作中往往单元格中有很多公式,如果列数发生变化将直接导致表格中函数公式运算结果错误。所以添加辅助列的方式虽然简单,但不是最好的方式。
 
那么不用辅助列如何才能完成多条件查询呢?
 
首先我们查找值合并很简单,输入函数vlookup时第一个参数可以写成A3&B3,即可将A3、B3两个单元格内容合并,作为查找值。
现在问题查找区域也需要做合并。
 
如果把两列内容合并在一起,可输入公式=H2:H19&I2:I19,按ctrl+shift+回车生成结果,然后下拉公式,这样两个条件就变成了一个。


接下来通过IF函数提取对应的J列数据,可输入公式=IF({0,1},H2:H19&I2:I19,J2:J19),按ctrl+shift+回车生成结果,然后下拉公式,{0,1}表示逻辑值{FALSE,TRUE}。
下面我们详细来解析一下:
首先在excel中0表示错误,1以及其他所有数值表示正确。如下表示例:

通过上面的例子我看到如果IF判断0则返回错误,判断1则返回正确。
现在我们可以将公式拆分为以下两种情况:
IF(0, H2:H19&I2:I19,J2:J19),0表示FALSE,所以只能返回J列数据。
IF(1, H2:H19&I2:I19,J2:J19),1表示TRUE,所以只能返回H列和I列合并结果。
那么IF({0,1},H2:H19&I2:I19,J2:J19)怎么理解呢?
既然是数组公式,那么可以将它理解为同时返回两组数据,0对应的是J2:J19,1对应的H2:H19&I2:I19,构建了两列数据。


最后我们使用vlookup函数完成嵌套,=VLOOKUP(A3&B3,IF({1,0},H3:H20&I3:I20,J3:J20),2,0),这里我们就可以理解为用A3&B3在H3:H20&I3:I20中查找对应J3:J20中的数据。因为公式中IF({1,0},H3:H20&I3:I20,J3:J20)返回的顺序是先返回H3:H20&I3:I20再返回J3:J20。

注意:很多人不明白为什么嵌套的时候IF第一参数又变成了{1,0},因为这里我们需要返回的是H和I合并结果作为查找区域。PS:所有数组公式完成输入后要使用数组三键ctrl+shift+ener来返回运算结果!
 
这样我们不用辅助列也能通过vlookup函数完成多条件查询。
 

第三 OFFSET+MATCH函数公式编辑本段回目录


很多excel高手都知道offset可以当vlookup函数使用,但职场新人大多都不了解。
下面举例跟大家分享一下通过offset函数完成多条件查询。


函数公式:{=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)}
公式解析:
完成多条件查询第一步先要确定A表中姓名&地区合并后对应在B表中姓名&地区的顺序。这里我们通过MATCH来完成,我们用个简单的例子说明。


=MATCH(A2,E:E,0)表示使用A2单元格在E列中查找,0表示精确查找、1小于、-1大于,通常情况下都是精确查找。
MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0)表示将A3与B3合并作为查找内容,H列和I列合并作为查找区域,0表示精确查找。

 

确定顺序后我们通过OFFSET函数以顺序数据作为偏移行数返回对应数值。
OFFSET函数的功能是以指定的单元格引用为参照系,通过给定偏移量得到新的引用。
返回的引用可以为一个单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值#VALUE!。


=OFFSET(J2,1,0,1,1)表示以J2单元格作为参照物向下偏移1行,向右偏移0列,返回1行1列数据区域。
=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)表示以$J$2为参照单元格,通过MATCH查找出来顺序作为向下偏移的行数,偏移列数量省略表示不偏移,第三个、第四个参数省略表示只返回一个单元格区域。
 
下面我们来总结一下本篇excel双条件查找返回的三种方式的利弊。LOOKUP函数使用过程中运算较慢;VLOOKUP函数使用IF({0,1})数组公式,理解上存在一定难度;OFFSET+MATCH函数公式简单,可以作为首选方案。


 

 

附件列表


按字母顺序浏览:A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

→我们致力于为广大网民解决所遇到的各种电脑技术问题
 如果您认为本词条还有待完善,请 编辑词条

上一篇电脑本地连接不见了
下一篇Mac版Office2016如何卸载

0
1. 本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
2. 本站内容仅供参考,如果您需要解决具体问题,建议您咨询相关领域专业人士。
3. 如果您没有找到需要的百科词条,您可以到百科问答提问或创建词条,等待高手解答。

关于本词条的提问

查看全部/我要提问>>