嗨,大家好,我是努力研究函数的小爽~
在工作中,我们经常会遇到这样的问题——
领导:小王,给我找出某某产品的销售额,哦,对了,再给我找出 2 月份某某产品的销售额……
小王:好的好的,领导~
(小王心想:这难不倒我!!)
不过这次小王收到表格后,彻底蒙圈了。
他之前收到的表格都是单一方向的一维表,而这次居然是一张二维表(交叉表)!
小王现在需要找出两个方向上、要同时满足条件的数据。
如果表格数据量比较少,那肉眼一瞧就能找到了,但是现在是一张几千行的表格啊!
小王内心崩溃了,不过活还是得要干!
于是,小王同学就找到了我,请求帮忙。
上面小王遇到的问题是:需要根据列方向的值,及行方向的值,得到交叉点的值。
这个问题,我把它归为一个交叉匹配的问题。
今天,我们就来聊一聊交叉匹配那些事~
00
下面我们通过一张图,具体来看看交叉匹配:
好啦,现在我们已经知道了交叉匹配,接下来,就来看看最常见的三种做法~
❶ VLOOKUP 和 MATCH 函数结合;
❷ INDEX 和 MATCH 函数结合;
❸ 利用名称管理器。
我们还是用这个案例,这里有一份成绩单,现在我需要找出小爽的图表成绩。
关于查询,我们肯定第一个想到的就是 VLOOKUP 函数,毕竟太熟悉了。
可是 VLOOKUP 函数只能纵向查找,没办法实现横向查找,而对于交叉匹配是不是就无能为力了呢?
01
VLOOKUP 和 MATCH 函数结合
虽然 VLOOKUP 函数不能实现横向查找,但是它可以找它的兄弟帮忙啊!
所谓在家靠父母,出门靠兄弟!
纵向部分的查询,我们可以利用 VLOOKUP 函数;
但是返回指定的列,可以利用另外一个函数 MATCH 来返回对应的索引数。
我们先来看看 VLOOKUP 和 MATCH 函数的做法~
结果图:
公式如下:
=VLOOKUP('小爽',A1:D5,MATCH('图表',A1:D1,0),FALSE)
在这个公式中,我们用到了 vlookup 函数和 match 函数。
MATCH 函数说明:
=MATCH(找啥,在哪里找,匹配方式)
其中匹配方式中 0 为精确匹配。
比如:
=MATCH('小爽',{'小叮';'小爽';'秋叶 Excel'},0)
结果为 2,表明小爽在这个区域中的第二位。
下面通过一个简单的图示图来看看~
对于 VLOOKUP 函数,之前有许多文章介绍过,后台回复关键词【up】,可以查收文章合集~
VLOOKUP 函数说明:
=VLOOKUP(找啥,在哪里找,找的相对位置,匹配方式)
其中匹配方式中 FALSE 为精确匹配。
比如:
=VLOOKUP('小爽',{'姓名','图表';'小爽','45'},2,False)
结果为 45,表明小爽的图表成绩为 45。
假设我们不知道图表的相对位置,也就是不知道第三参数中的 2。
我们可以借助前面介绍的 MATCH 函数来查找图表在表头的相对位置,公式就应该为:
=VLOOKUP('小爽',{'姓名','图表';'小爽','45'},MATCH('图表',{'姓名','图表'},0),False)
▲左右滑动查看完整公式
结果也为 45。
下面,我们就通过一个图示来更深入地理解吧~
简单总结一下:
由于 VLOOKUP 函数只能纵向查找,所以我们需要借助 MATCH 函数来查找查询值在标题区域中的相对位置。
这个位置的结果可以作为 VLOOOKUP 函数的第三参数,来查找出交叉匹配中的值,该做法是最常见的交叉匹配的做法。
下面我们介绍 index 和 match 函数的做法。
02
INDEX 和 MATCH 函数结合
我们知道 INDEX 和 MATCH 函数是一对万金油查找函数公式,两者配合,能够发挥出巨大的作用!
MATCH 函数负责找位置,然后告诉 INDEX 位置,INDEX 就去把东西抓过来,好比侦探和警察的关系。
下面我们就来介绍一下它两配合的做法吧~
=INDEX(A1:D5,MATCH('小爽',A1:A5,0),MATCH('图表',A1:D1,0))
03
利用名称管理器
=图表 小爽
图表=B2:B5 小爽=B3:D3 也就是=图表 小爽 相当于=(B2:B5 B3:D3)
❶ VLOOKUP 和 MATCH 函数结合——借助 match 函数获取横方向的相对位置; ❷ INDEX 和 MATCH 函数——一个找位置,一个抓东西; ❸ 利用名称管理器——借助名称管理器和 Excel 引用运算。
秋叶 Excel 数据处理学习班年前最后一期
👇👇👇
秋叶 Excel 数据处理学习班12 月 8 号就开营,
小 E 特别和大家申请了 200 元 的优惠券!
助你用 21 天系统训练,为升职加薪提速!
联系客服