3个Excel公式都看不懂!”
发布时间:2022-09-14 00:23:30
最近收到某位学员的问题咨询,问题是如何根据单据编号和物料长代码返回对应的含税数额。如下表:
其实这位学员的问题就是如何实现多条件查询。
下面通过一个实例跟大家分享一下常用的几种多条件查询方法。下表是某电商公司的客户投诉表,现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号,返回到A表的E列中。
1、LOOKUP函数
函数公式:=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这三种情况。用0来除以0和1,由于分母不能为0,所以0/0返回的是错误,0/1返回的结果为0。Lookup函数在查找的时候是忽略错误的,所以只有数据运算结果为1的公式满足条件。
那么我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是将正确结果用0表示,其他的变成错误值,利用函数查找忽略错误这个特点完成查找。总结:本函数由于使用了二分法原理查找,所以如果数据量较大时运算会很慢。
2、VLOOKUP函数
使用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函数完成多条件查询。
3、OFFSET+MATCH函数
下面举例跟大家分享一下通过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查找出来顺序作为向下偏移的行数,偏移列数量省略表示不偏移,第三个、第四个参数省略表示只返回一个单元格区域。下面我们来总结一下三种方式的利弊。LOOKUP函数使用过程中运算较慢;VLOOKUP函数使用IF({0,1})数组公式,理解上存在一定难度;OFFSET+MATCH函数公式简单,可以作为首选方案。
猜你喜欢
- 1、打开WPS后,默认的是“开始”标签,那么我们需要点击“插入”标签, 2、打开“插入”标签后,在功能区点击“书签
- 今天为大家分享excel数据单元格的11种选取方法,教程比较实用,对于办公人员很值得大家学习,推荐到脚本之家,喜欢的朋友可以参考本文,来看看
- 上期给大家分享过5个常用的函数例子,今天给大家继续分享5个常用函数,在工作中帮助我们快速解决问题。6、vlookup数据查找很熟,根据内容查
- Excel表格中怎么设计出差报销单?经常出差,想要谁一个出差费用的报销单,该怎么设计呢?下面我们就来看看详细的教程,需要的朋友可以参考下ex
- 在excel中怎么插入柱形图?如何给柱形图改标题、填充颜色?很多朋友并不是很清楚,所以下面小编就为大家详细介绍一下,不会的朋友可以参考本文,
- 有用户反映在使用win10系统电脑过程中遇到了无法开机情况,而且提示0xc000000f的错误代码,这是怎么情况呢?一般是由于映像出现问题所
- 在办公中,经常会碰到excel表格中存在大量列,但有一些列,可能是近期不在使用,就是暂时不在使用的,为了更好的突出重点列,就可以暂时把这些不
- 在 iOS 14 以及更新系统中,用户可以在苹果自带的“相机”应用中即时更改视频分辨率和帧频,不用进入“设置”应用程序。打开 iPhone
- excel表格自带有格式刷功能,提高了工作效率,这篇文章为大家分享了excel表格中多次使用格式刷的方法,感兴趣的小伙伴们可以参考一下在生活
- 在Word文档中有一个匹配左右括号功能,开启该功能后,我们输入左(,{,【后,后续输入右括号中的任意一种,系统会自动匹配为正确的符号。也就是
- 大家在使用win10系统的使用,会发现桌面右下角总是会弹出一些window安全警报,打开某些文件也会出现提示,非常烦人,哪有什么方法可以关闭
- 软件安装好了以后在桌面都会显示图标的,但是最近有Win10专业版的用户遇到了Win10专业版软件安装好桌面没有图标,不知道去那里找这个软件,
- 你可能经常需要在 Excel 表格中找出重复数据并删除它们,在一大堆数据中人工找不太现实。下面教两条小方法,帮你处理重复数据。方法 1:突出
- Word文档中怎么使用格式刷?许多用户在使用Word的时候,都会为自己的文章增加不同的格式效果。但是有些内容基本都是需要统一格式的,这个时候
- 在Excel中,组合图表指的是在一个图表中包含两种或两种以上的图表类型——例如,可以让一个图表同时具有折线系列和柱形系列。当我们在图表向导的
- Excel中使用“符号”对话框可以插入键盘上没有的符号(如 ¼ 和 ©)或特殊字符(如长划线 (—) 或省略号 (…)),还可以插入 Uni
- word软件之所以受到用户的欢迎和喜爱,是因为软件中的功能帮助用户解决了很多的问题,让用户简单操作几个步骤就可以将文档编辑完成,总之这款办公
- Word是是一款办公软件,平时我们在使用Word的时候可以用来做文档,也可以用来做表格,是一款操作简单,功能强大的软件,但是对于很多新手小白
- 我们在编辑文档的时候往往都会碰到这种情况,段尾最后一个字后面老是有一个讨厌的标记,那么该怎么去除它们呢?下面小编就为大家介绍一下,来看看吧W
- 写文章和写论文的过程中我们经常想让每一章节的页眉来显示本章节的标题,而每一章节的封面却又不想要页眉,这种效果如何达到呢,今天,小编就教大家在