首页
- 全部课程
- 视频
- 图文教程
- 教学动态
- 设计资讯
- 学员天地
- 图文教程 >
- 电脑与办公教程 >
- EXCEL >
- Excel查找公式归纳整理,涉及5个函数,20+个公式,赶紧收藏!
Excel查找公式归纳整理,涉及5个函数,20+个公式,赶紧收藏!
作者:老菜鸟来源:部落窝教育发布时间:2022-08-23 17:33:10点击:11234
编按:
今天来给大家归纳整理查找类公式,涉及5个函数,20+个函数公式,一起来看看吧!
Excel里常用的查找函数有五个:VLOOKUP、INDEX、OFFSET、LOOKUP、INDIRECT。
能够用到查找函数的场景大致可以分成四类:单条件查找、多条件查找、一对多查找、多对多查找。
今天为大家整理一期查找公式大全,篇幅所限,不做具体解释了,遇到对应的情况直接挑合适的公式即可。
一、单条件查找(从左向右查找)
例如,要找到指定订单ID所对应的地址,首选公式为=VLOOKUP(D3,A:B,2,0)。
使用其他查找函数的公式分别为:
=INDEX(B:B,MATCH(D3,A:A,0))
=OFFSET($B$1,MATCH(D3,A:A,0)-1,)
=LOOKUP(1,0/(A:A=D3),B:B)
=INDIRECT("B"&MATCH(D3,A:A,))
说明:虽然五个公式可以得到同样的结果,但具体原理各有不同。解决这类问题大家可能更习惯用VLOOKUP,但是VLOOKUP有个最大的限制就是查找条件必须在查找区域的首列,也就是从左向右查找。如果是从右向左又该如何做呢?看下面这个例子。
二、单条件查找(从右向左查找)
例如按照指定的地址查找对应的订单ID,上述五个公式都需要做修改。
公式分别为:
=VLOOKUP(D3,IF({1,0},B:B,A:A),2,0)
=INDEX(A:A,MATCH(D3,B:B,0))
=OFFSET($A$1,MATCH(D3,B:B,0)-1,)
=LOOKUP(1,0/(B:B=D3),A:A)
=INDIRECT("a"&MATCH(D3,B:B,))
大家对比一下就会发现,只有VLOOKUP的变化是最大的,用IF函数构建了一个数组,而其他四个公式基本一样。
除了以上说的两种单条件查找,日常用得比较多的还有多条件查找。
三、多条件查找
例如通过客户ID和商品名称两个条件来查找运货商,还是用上述五个查找函数来对比看下公式。
公式分别为:
=VLOOKUP(E3&F3,IF({1,0},A:A&B:B,C:C),2,0)
=INDEX(C:C,MATCH(E3&F3,A:A&B:B,0))
=OFFSET($C$1,MATCH(E3&F3,A:A&B:B,0)-1,)
=LOOKUP(1,0/((A:A=E3)*(B:B=F3)),C:C)
=INDIRECT("c"&MATCH(E3&F3,A:A&B:B,))
说明:多条件查找时,除了LOOKUP的原理不同之外,其他四个函数都是利用了&将条件进行合并,其本质与单条件并无不同,但是由于合并过程中涉及到了数组计算,非365版本的用户在使用时需要按Ctrl、shift和回车键输入公式。另外强调一点,多条件查找时不建议使用整列,不然你的表格会很卡。
注意:以上的单条件查找和多条件查找,返回的结果都是唯一的,如果返回结果是多项的话,对应的问题就变成了一对多查找和多对多查找。
这两类问题使用公式解决都比较麻烦,当然如果你用的是最新版Excel的话,可以用新版特有的函数去处理,下面还是分情况来进行介绍。
四、一对多查找
例如要查找出指定运货商的所有订单ID,就需要用到一对多查找的公式,非365版本可以使用公式:
=IFERROR(INDEX($B$2:$B$19,SMALL(IF($A$2:$A$19=$D$2,ROW($1:$18),99),ROW(A1))),"")
结果如图所示。
如果你使用的是365版本的Excel,这个问题就比较容易了,直接使用公式=FILTER($B$2:$B$19,$A$2:$A$19=G2)即可,结果如图所示。
以上是一对多的两个公式,多对多的公式就更复杂了。
五、多对多查找
按照城市和运货商查找对应的订单ID,非365版本使用公式:
=IFERROR(INDEX($C$2:$C$19,SMALL(IF($A$2:$A$19&$B$2:$B$19=$E$2&$F$2,ROW($1:$18),99),ROW(B1))),"")
365版本使用公式=FILTER($C$2:$C$19,($A$2:$A$19=$E$2)*($B$2:$B$19=F2))
以上就是今天分享的内容,是不是感觉有些函数的用法还没有完全掌握呢?
关注我们,一步一步慢慢来吧!
希望最后大家都能够了解各种查找函数在不同场景的使用方法。
本文配套的练习课件请加入QQ群:902294808下载。
做Excel高手,快速提升工作效率,部落窝教育 《一周Excel直通车》视频和 《Excel极速贯通班》直播课全心为你!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
八大查找函数公式,轻松搞定数据中的多条件查找
10种职场人最常用的excel多条件查找方法!(建议收藏)
Excel教程:如何制作带有层次和透视感的图表?
八大查找函数公式,轻松搞定数据中的多条件查找
版权申明:
本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。
最热教程
- 像绿皮火车一样长像珠穆拉玛峰一样高的Excel表怎么操作才方便?
- Power Query实战:按指定次数递增数据
- 2019年全网最全—excel提取身份证信息合集!(建议收藏)-下篇
- 明明没有重复,Excel却判定数据重复,这是怎么回事?
- 文本格式的求和,及求和中最容易出现的问题解疑
- 致命缺陷:不懂一维表!
- 函数组合思维,你有吗?
- 学会这2个公式,整理考勤数据只要一分钟
- 就算被说是拍马屁也成,今天你应该这样发Excel报表……
- 如何计算Excel单元格中的算式,四种求和方法请收好!
最新教程
- 用函数合并多个工作表数据
- 用LAMBDA自定义颜色求和函数
- 用SUMIF错位求和多表求和与查找
- BYROW和BYCOL函数用法详解
- makearray函数用法6例
- PDF转Excel表格的两种免费方法
- Excel加PPT三步完成图片批量加水印
- SCAN函数基本用法和典型应用
- MAP函数如何使用?有何意义?
- 简单6步完成Excel气泡地图