返回目录:excel表格制作
有群友在群里问如何在多个工作表中查询数据,当时我太忙,没仔细考虑,只是建议用VLOOKUP+INDIRECT应该能解决。等忙完后自己仔细考虑了一下,这两个函数结合其它函数是可以解决这个问题的,现在将我解决的思路写出来供大家思考。
=VLOOKUP(A2,LOOKUP(1,0/COUNTIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2),INDIRECT({"河北区";"北京区";"天津区"}&"!A:B")),2,0)
模拟了一些数据,查询表中的姓名在三个工作表中的任意一个,需要得到每位员工对应的销售额。
VLOOKUP函数的用法不再解释,它由四个参数组成:
VLOOKUP(要查找的数据,在哪个区域查找,返回区域第几列,精确匹配或模糊匹配)
这四个参数中,我们可以解决三个,公式是在查询表的B2单元格中输入的。
VLOOKUP(A2,在哪个区域查找,2,0)
在哪个区域查找,这个我们是不确定的,有可能是在“=河北区!A:B”,也有可能是在“=北京区!A:B”,还有可能是在“=天津区!A:B”。因为是在三个工作表中,我们不确定这名员工到底在哪个工作表。所以我们需要来确定他在哪个工作表中。
因为姓名都在各工作表中第一列,所以要查找的姓名在三个区域中,分别为“=河北区!A:A”、 “=北京区!A:A”、 “=天津区!A:A”。
如果查找员工有没有在某一个工作表中,我们可以用COUNTIF来查找,如果结果等于1,代表这个工作表中有这名员工,如果等于0则代表这个工作表中没有这名员工。但现在我们需要在三个工作表中查找,可以用INDIRECT函数来引用。
所以可以编辑公式:COUNTIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2),它返回由1和0组成的数组,如{1;0;0},这个结果代表员工在河北区。
我们知道1在什么位置就是哪个工作表,但是EXCEL不知道,所以我们得让它知道1所以位置对应的工作表。
此时我们用到LOOKUP(1,0/数组或公式结果为数组,返回结果的区域或数组)这种经典的用法,公式如下:
LOOKUP(1,0/COUNTIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2),INDIRECT({"河北区";"北京区";"天津区"}&"!A:B"))
这样就能得到姓名所在的区域,例如 “河北区!A:B”。
这样的话,就是VLOOKUP的基础用法了:VLOOKUP(A2, 河北区!A:B,2,0)
我们要注意的是公式是数组公式,要用CTRL+SHIFT+ENTER键结束输入。
本例中公式可以作为多表查询的一个通用公式,其实我们通过姓名来返回数值型数据,还可以用SUMIF+INDIRECT,本例还可以用数组公式,公式不再解释。
=SUM(SUMIF(INDIRECT({"河北区";"北京区";"天津区"}&"!A:A"),A2,INDIRECT({"河北区";"北京区";"天津区"}&"!B:B")))