与 30万 读者一起学Excel 有三个地区,分别是无锡、响水和苏州,各自的信息记录在三张表格,每个表格的格式都是一样的。如何根据地区跟店名在三个表格中查找造价? 格式相同的明细表 查询表 在查询表的C2单元格输入公式,并向下复制。 =VLOOKUP(B2,INDIRECT(A2&"!B:C"),2,0) 正常跨表查询都是这样写公式的: =VLOOKUP(B2,无锡!B:C,2,0) =VLOOKUP(B2,响水!B:C,2,0) =VLOOKUP(B2,苏州!B:C,2,0) 参数2是工作表名称!区域这样的形式,通过不断的改变工作表名称可以查询任意表格。 因为查询表已经提供了工作表名称,这时我们重新再用&连接区域即可。 =A2&"!B:C" 可是当我们直接这样引用并输入公式的时候,却提示了错误。 其实直接用&得到的只是一个文本,得通过INDIRECT函数间接引用才能转换成一个真正的区域。 =INDIRECT(A2&"!B:C") 在查询表有了地区的名字会比较好找,假如没有地区的名字,根据店名又该如何在这三个表格中查询工程造价? 在查询表的C2单元格输入公式,并向下复制。 =SUM(SUMIF(INDIRECT({"无锡","响水","苏州"}&"!B:B"),B2,INDIRECT({"无锡","响水","苏州"}&"!C:C"))) 因为查询的是数值,前面有提到,查询数值用SUM家族来处理最好。 因为是三个表格,所以先构成这三个表的区域文本。 {"无锡","响水","苏州"}&"!B:B" 接着嵌套INDIRECT函数进行间接引用,变成真正的区域。 INDIRECT({"无锡","响水","苏州"}&"!B:B") 根据SUMIF函数的语法,设置公式: =SUMIF(INDIRECT({"无锡","响水","苏州"}&"!B:B"),B2,INDIRECT({"无锡","响水","苏州"}&"!C:C")) 这时奇怪的现象发生了,除了无锡这个表的对应值是对的,其他都是错的。 这究竟是怎么一回事呢? 其实用SUMIF函数产生的一共有三个数据,而不止一个,因为无锡这第一个表,正常的话一个单元格只能显示第一个数据,也就是无锡是对的。而响水跟苏州是第2,第3个数据,显示不出来,所以错误。 有疑惑最好的办法就是在编辑栏用鼠标选中公式抹黑,然后用F9键解读。 因为是三个数据,需要在最外面嵌套SUM函数进行求和才能变成一个值,这样才能得到准确的结果。 推荐:VLOOKUP函数之魅 作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban) |
|