“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

关注回复[目录]学习113篇excel教程 [原创文章:www.pp00.com]

[本文来自:www.pp00.com]

之前给大家推送了一篇用PQ完成跨表数据核对的教程:《我折腾到半夜,同事用这个Excel技巧,30秒跨表核对数据交给领导!》,但由于版本限制,好多伙伴都无法使用,今天给大家介绍3个公式,同样可以完成数据核对。

最近在微信学习交流群中收到某位学员的问题咨询,问题是如何根据单据编号和物料长代码返回对应的含税数额。如下表:

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

下载课件请加入QQ群:537870165

其实这位学员的问题就是如何实现多条件查询

下面通过一个实例跟大家分享一下常用的几种多条件查询方法。

下表是某电商公司的客户投诉表,现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号,返回到A表的E列中。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”


1.lookup函数

函数公式:=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

公式解析:首先通过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函数

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

使用G2单元格在A列中查找,如果查找到对应单元格则返回A列向右第二列的数据。简而言之:=VLOOKUP(查找什么,在哪查找,从条件所在列算起找到后返回对应的第几列数据,精确或模糊查找)。

那vlookup如何才能完成多条件查询呢?。

还以客户投诉表为例,按照姓名&地区来匹配产品型号返回到E里中。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

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

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

但是插入2个辅助列后整个表列数发生变动,在工作中往往单元格中有很多公式,如果列数发生变化将直接导致表格中函数公式运算结果错误。所以添加辅助列的方式虽然简单,但不是最好的方式。

那么不用辅助列如何才能完成多条件查询呢?

首先我们查找值合并很简单,输入函数vlookup时第一个参数可以写成A3&B3,即可将A3、B3两个单元格内容合并,作为查找值。

现在问题查找区域也需要做合并。

如果把两列内容合并在一起,可输入公式=H2:H19&I2:I19,按ctrl+shift+回车生成结果,然后下拉公式,这样两个条件就变成了一个。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

接下来通过IF函数提取对应的J列数据,可输入公式

=IF({0,1},H2:H19&I2:I19,J2:J19),按ctrl+shift+回车生成结果,然后下拉公式,{0,1}表示逻辑值{FALSE,TRUE}。

下面我们详细来解析一下:

首先在excel中0表示错误,1以及其他所有数值表示正确。如下表示例:

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

通过上面的例子我看到如果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,构建了两列数据。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

最后我们使用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。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

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

这样我们不用辅助列也能通过vlookup函数完成多条件查询。


3.OFFSET+MATCH函数

下面举例跟大家分享一下通过offset函数完成多条件查询。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

函数公式:

{=OFFSET($J$2,MATCH(A3&B3,$H$3:$H$19&$I$3:$I$19,0),)}

公式解析:

完成多条件查询第一步先要确定A表中姓名&地区合并后对应在B表中姓名&地区的顺序。这里我们通过MATCH来完成,我们用个简单的例子说明。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

=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表示精确查找。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

确定顺序后我们通过OFFSET函数以顺序数据作为偏移行数返回对应数值。

OFFSET函数的功能是以指定的单元格引用为参照系,通过给定偏移量得到新的引用。

返回的引用可以为一个单元格或区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用;否则,函数 OFFSET 返回错误值#VALUE!。

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

=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函数公式简单,可以作为首选方案。

热文推荐

(点击下方图片开始学习)

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

想要跟随滴答老师全面系统学习Excel,不妨关注《一周Excel直通车》视频课或者《Excel极速贯通班》直播课。


《一周Excel直通车》视频课

包含Excel技巧、函数公式、

数据透视表、图表。

一次购买,永久学习。


最实用接地气的Excel视频课

《一周Excel直通车》

风趣易懂,快速高效,带您7天学会Excel

38 节视频大课

(已更新完毕,可永久学习)

理论+实操一应俱全


主讲老师: 滴答

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!” 

Excel技术大神,资深培训师;

课程粉丝100万+;

开发有《Excel小白脱白系列课》

        《Excel极速贯通班》。

原价299元

限时特价 99 元,随时涨价

少喝两杯咖啡,少吃两袋零食

就能习得受用一生的Excel职场技能!


  长按下面二维码立即购买学习

“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

购课后,加客服微信:603830039领取练习课件 


“我面试了几十个大学生都不达标,发现他们连这3个Excel公式都看不懂!”

自媒体微信号:pp00扫描二维码关注公众号
爱八卦,爱爆料。
小编推荐
  1. NO.1 中国男女比例2019(最新中国男女比例揭晓)

    人口地理学主要研究一定历史条件下的人口分布、人口变动、人口构成(年龄构成、性别构成等)、人口增长的空间变化,以及与自然和人文环境相

  2. NO.2 一线女星陪睡价格曝光!睡过男人最多的10大女星,章子怡只能排第5?

    原来外表光鲜的当红女星,背后竟是这些不为人知的辛酸!

  3. NO.3 黄色漫画:旅店屌丝男被女主播扒去内裤,拉到洗手间...

    色漫画:在旅馆打工的屌丝男主,无意间撞上了“血红色的月亮”,从此他的人生发生了巨大的变化...第1话 你是谁

  4. NO.4 蒙牛纯牛奶怎么样,蒙牛和伊利哪个质量好?

    看哪种奶好,主要看一下三点: 1、看配料多少: 配料表中的配料越少越好,从下图可以看出,纯牛奶配料最少,只有生牛乳,其次为有机奶,配料

  5. NO.5 黄色漫画:女生自己动舒服吗?

    污漫画:继母所生隐居不出的妹妹,迷途知返的哥哥,当父母双亲身亡后,二者又如何将这复杂的关系进行下去?第1话

  6. NO.6 “别喊,我慢慢 . 进就不难受了...”

    正能量第1章 孤独的爱第2章 被威胁了由于微信篇幅限制,只能发到这里啦! 点击下方阅读原文,后续剧情高潮不

  7. NO.7 【独家】“先锋系”水落石将出 旗下金融机构或面临风险处置

    这家类“明天系”、“德隆系”的民营金融控股集团,在复杂隐蔽的大量关联交易之下,诸多投资失败,核心上市公司股票被沽空,财务危机爆发,

  8. NO.8 实录:100位性瘾者的自述

    来人间走一趟,要晒晒太阳,和心爱的人,走在大街上。

Copyright2018.皮皮自媒体资讯站,让大家及时掌握各行各业第一手资讯新闻!