【原】提取多个工作表中相同位置单元格中数据，要想职场混得好，这个问题跑不了！

Excel情报局

Excel职场联盟

生产挖掘分享Excel基础技能

Excel爱好者大本营

用1%的Excel基础搞定99%的职场问题

做一个超级实用的Excel公众号

Excel是门手艺玩转需要勇气

数万Excel爱好者聚集地

SUPER EXCEL MAN

    

前言｜职场实例    

昨天晚上有些疲惫，刚想睡觉。。。突然收到公众号的粉丝留言。

他语气很急：表哥，能帮我处理个数据吗？这份表我搞了8个小时，眼睛都花了，实在弄不完了。

他发给我一看，原来是表格间提取数据的问题：提取1500个工作表中相同位置单元格中数据，放置到另外一个表格的一列中。

他的做法是一个一个复制粘贴，重复1500次，搞了8个小时。

我花了一分钟，写了一个定义函数和INDIRECT函数公式，很快的解决了这个问题。

做完后数据回传给他，他惊奇的连忙表示感谢。

这就是Excel的强大的力量，同样一项工作，有技巧与没有技巧，工作时间相差了几个甚至十几个小时。所以Excel作为日常使用频率这么高的工具，掌握一些技能真的很有现实意义。

如何提取多个工作表中相同位置单元格中的数据？

这个问题的模型是Excel中一个出现频率高，知识点价值高，并且基础实用性超强的技巧。今天，小编从头到尾捋顺了一遍关于这个问题的知识点，然后做成这篇实用文章，分享给大家。

如下图所示：有3张工作表，名称分别为“销售部”、“财务部”、“生产部”。

①表1：“销售部”：

②表2：“财务部”：

③表3：“生产部”：

现在我们想要批量提取“销售部”、“财务部”、“生产部”这3个工作表中表格位置B3单元格中的数据，放置到工作表名称为“提取”表中的A列单元格中。

如下图所示：

情景1｜工作表名称为顺序序号    

如果工作表名称为顺序序号，比如为：“1,2,3....”，我们可以利用INDIRECT函数进行操作实现。

INDIRECT函数含义：

此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，请使用此函数，INDIRECT为间接引用。

INDIRECT函数公式及参数：

=INDIRECT(ref_text,[a1])

①Ref_text 为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。

②如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值#REF!或#NAME?。

③如果 ref_text 是对另一个工作簿的引用(外部引用)，则工作簿必须被打开。如果源工作簿没有打开，函数 INDIRECT 返回错误值#REF!。

④a1 为一逻辑值，指明包含在单元格ref_text 中的引用的类型。

如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。

如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。

在“提取”表格A2单元格输入函数公式：

=INDIRECT("'1'!B3")

下拉填充后，我们发现只有A2单元格显示的值是正确的，其余的是错误的。原因是：公式中的工作表名称没有对应改变。始终显示的是“1”。

所以我们必须使用ROW函数来智能提取行号来实现工作表名称的改变。

ROW函数含义：

ROW函数作用是返回一个引用的行号。

ROW函数公式及参数：

ROW(reference)

①Reference 为需要得到其行号的单元格或单元格区域。

②如果省略 reference，则假定是对函数 ROW 所在单元格的引用。

③如果 reference 为一个单元格区域，并且函数 ROW 作为垂直数组输入，则函数 ROW 将 reference 的行号以垂直数组的形式返回。

④Reference 不能引用多个区域。

在“提取”表格A2单元格输入函数公式，即用ROW函数嵌套进INDIRECT中：

=INDIRECT(ROW(A1)&"!B3")

下拉填充公式，即可提取成功。

情景2｜工作表名称为普通文本   

如下图所示这种情况：

我们的工作表名称不是顺序序号“1,2,3....”这种格式了，而是普通文本“销售部”、“财务部”、“生产部”这种汉字形式，所以就不能像上面那样利用ROW函数提取行号作为工作表名称了。

我们需要先提取工作表的名称：

点击公式中的定义名称：

然后在定义名称输入：提取

在引用位置输入=GET.WORKBOOK(1)

然后B2单元格输入公式：

=INDEX(提取,ROW(A1))

回车结束公式，最后往下拉就完成工作表名称的提取了。

将公式部分单元格利用“选择性粘贴为数值”的方法将公式去掉，只留下文本值。

然后利用“查找和替换”的方法，将[Excel情报局.xls]这部分文本替换为空值删除。目的是：留下单纯的工作表名称。

最后A2单元格输入函数公式：

=INDIRECT(B2&"!B3")

下拉填充公式，即可完成最后的提取工作。

阅读完文章之后，希望小伙伴们在文章底部帮助小编[点赞]点亮[在看]并分享转发到[朋友圈]，坚持持续分享的路上很辛苦，需要有你们的鼓励与支持！您也可以通过在文章底部[留言]的方式来反馈实际办公中遇到的Excel问题场景。