这是一个使用 vlookup 函数嵌套 INDIRECT 函数来实现跨工作表数据引用的教程。

某小学决定要抽查本校三年级三个班的期末考情况,抽查方法为在每个班中抽查5名学生,将这15名学生的期末考情况汇总到一个表中考察。

工具/原料

 
  • 电子表格软件
  • vlookup 、 INDIRECT

方法/步骤

 
  1. 1

    该小学现已在一个工作簿中建立《三年1班》、《三年2班》、《三年3班》3张工作表,如图:

    使用vlookup嵌套INDIRECT函数实现跨表数据引用
  2. 2

    现在每班5名同学也已抽出,如图:

    使用vlookup嵌套INDIRECT函数实现跨表数据引用
  3.  

    现在要做的,就是要在《成绩抽查》工作表里面,引用《三年1班》、《三年2班》、《三年3班》3张工作表中被抽查的学生的成绩。应该怎么做呢?

  4.  

    使用 VLOOKUP 函数引用数据

    根据常规的做法,我们可以使用 VLOOKUP 函数实现数据的引用。

    我们可以在《成绩抽查》工作表 D3 单元格中填写公式:

    =VLOOKUP(A3,三年1班!A3:E17,3,FALSE)

     

     

  5.  

    公式讲解:

    使用vlookup嵌套INDIRECT函数实现跨表数据引用
  6.  

    ① VLOOKUP函数,表示搜索一个或多个数据列。

    ② 表示将单元格 A3 的值作为搜索内容。

    ③ 表示在工作表《三年1班》单元格 A3:E17 的范围内搜索是否有与单元格 A3 的值相匹配的内容。

    ④ 如果找到了相匹配的值,则返回工作表《三年1班》单元格 A3:E17 第3列的对应内容。

    ⑤ "FALSE",表示查找时要求完全匹配,而不是模糊匹配。

  7.  

    我们把此公式填充到 D4:D17,执行结果如下:

    使用vlookup嵌套INDIRECT函数实现跨表数据引用
  8.  

    为什么 D8:D17 单元格会出现 "#N/A" 这样的结果呢?这是因为所要查找的数据分散在三个工作表,而原来的公式只是在《三年1班》这一个工作表中查找。因为在工作表《三年1班》中查找不到2班、3班的学生,所以公式结果出错。那有没有一个函数能和 VLOOKUP 函数结合在一起,帮助我们实现跨工作表的数据引用?

    有的,那就是 INDIRECT 函数。

  9.  

    使用 INDIRECT 函数,使数据引用实现跨工作表

     

      公式修改  我们现在考虑如何修改我们最先列出的那个公式。其中一个有效的方法,是把上一个公式中 "三年1班!A3:E17" 这一部分替换为 "INDIRECT($C3&"!A3:E17")" 。

      公式讲解:

    使用vlookup嵌套INDIRECT函数实现跨表数据引用
  10.  

    我们先来看分式①。分式①是一个文本函数。表示将C3单元格和  "!A3:E17"  这个字符串联合起来,组成一个新字符串。C3单元格中的数值为 "三年1班" ,和  "!A3:E17"  这个字符串组合后就成为 "三年1班!A3:E17" 。所以这个公式此时就相当于:

     

     

    =INDIRECT("三年1班!A3:E17")

     

     

    而它等价于:

     

     

    =三年1班!A3:E1

     

     

  11.  

    所以我们就得到了我们所需要的单元格的引用。因此,我们将我们给出的第一个公式

     

     

    =VLOOKUP(A3,三年1班!A3:E17,3,FALSE)

     

     

    中 "三年1班!A3:E17" 这一部分替换为 "INDIRECT("三年1班!A3:E17")" ,就修改成为如下的新公式:

     

     

    =VLOOKUP(A3,INDIRECT($C3&"!A3:E17"),3,FALSE)

     

     

     

    我们将这个公式填充,公式就能根据C列中所列出的不同班级,在相应工作表中查找和引用相关数据,这样显示结果就正常了。

    使用vlookup嵌套INDIRECT函数实现跨表数据引用
  12.  

    完成工作表我们再在E3填写公式:

     

     

    =VLOOKUP(A3,INDIRECT($C3&"!A3:E17"),4,FALSE)

     

     

     

    在F3填写公式:

     

     

    =VLOOKUP(A3,INDIRECT($C3&"!A3:E17"),5,FALSE)

     

     

     

    并填充,最终完成这一个数据表:

    使用vlookup嵌套INDIRECT函数实现跨表数据引用 
     
    电子表格中函数嵌套使用会大大提高办公效率,大家应该多多使用练习。