Excel常⽤函数之VLOOKUP函数查多个⼯作表的数据
从近期与⼤家的交流看,还有很多朋友对VLOOKUP函数不是很熟悉。因此,准备详细的介绍⼀下这个函数的使⽤。VLOOKUP函数功能⾮常强⼤,使⽤场景也变化很⼤,因此准备分为⼏篇来介绍:
1. VLOOKUP函数精解(已发,详情阅读这⾥)
2. VLOOKUP排错(已发,详情阅读这⾥)
3. VLOOKUP函数动态返回不同的列(已发,详情阅读这⾥)
4. VLOOKUP函数使⽤多个条件(已发,详情阅读这⾥)
5. VLOOKUP函数返回多个值
6. VLOOKUP函数反向查
7. VLOOKUP函数查多个⼯作表的数据
今天是第7篇,VLOOKUP查多个⼯作表中的数据。
01
场景介绍
VLOOKUP从⼀个区域中查相应的数值,已经是⾮常熟悉的应⽤了。但是,有的时候,我们需要查的区域可能在多个不同的⼯作表上:
但是这个产品与⼤类的对应表存在多个表上:
上⾯是A⼯⼚产品的资料表,还有⼀些在B⼯⼚的表格中:excel vlookup函数怎么用
还有⼀些产品在其他⼯⼚的资料表中。
如果我们写VLOOKUP公式,只能返回⼀个表格区域的结果。
02
使⽤VLOOKUP查多个表
我们需要使⽤⼀个复杂的公式。
⾸先,在表格中创建⼀个区域,输⼊所有的⼯作表名称:
然后在C2单元格中输⼊公式:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
注意这是⼀个数组公式,所以需要按Ctrl+Shift+Enter来输⼊。然后填充到整列,得到结果:
下⾯简单解释⼀下这个公式:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)公式中紫⾊背景的部分就是我们添加的辅助区域,⽤于记录存放产品资料的⼯作表名称。所以,下⾯公式中紫⾊背景部分:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)就是去分别计算每个⼯作表中是否包含B3这个单元格的值,如果包含单元格的值的话,计数就⼤于0,否则返回0
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)这样,这个逻辑表达式就会返回每个⼯作表是否含有要查的值,⽐如,这⾥的结果就是:{1;0;0},
然后Match函数就会到那个包含查值的序号:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)这⾥的结果就是1,也就是第⼀个⼯作表包含要查的值。
于是,下⾯的阴影部分就到了这个⼯作表名称:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)然后,INDIRECT函数引⽤相应区域:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)剩下的就是⼀个⾮常简单的VLOOKUP了。
这是⼀个复杂的IF公式,即使对于很多Excel⽼⼿来说,也不是⼀眼就能明⽩这个公式,更不⽤说遇到问题时⾃⼰写出这样的公式了。所以,我⼀般不推荐使⽤这种公式解决问题。这⾥的公式主要是写在这⾥以备紧急情况下你可以参考,解决燃眉之急。
更好的做法是学习使⽤其他的⽅法,这样你就可以⾃⼰解决这个问题。
你能想到不使⽤公式就解决这个问题的办法吗?提⽰⼀下,使⽤Power Query。如果你想到了怎么做,可以留⾔告诉我。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论