如何在一个EXCEL表格里自动获取其他很多个EXCEL表格特定行列的数据？

Question

我手里有上百个同格式的EXCEL表格文件，但是我只需要每个表格里特定行列的一个数据，我需要在一个总表格里把上百个子表格特定行列处的数据进行引用，以前是手动复制粘贴过来，特别繁琐。请问有什么公式可以实现这个需求么？
比如在总表格的A1栏显示子表格甲C7处的数据，在总表格的A2栏显示子表格乙C7处的数据，在总表格的A3栏显示子表格丙C7处的数据……
求公式或解决方案，谢谢！

Answer 1

可将“表格甲”，“表格乙”等没有任何规律的工作表名在总表的某一列录入，比如录入到D2:D200

A1输入   =INDIRECT(D2&"!C7")    公式下拉即可

Answer 2

粘贴到代码视图里面，直接点击运行即可，百来张表的话数据会比较多，运行的时间有点久，等待即可。

Sub 提取当前工作目录下所有表格c7单元格()

Dim Wb As Workbook

Application.ScreenUpdating = False

MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & "\" & "*.xlsx")

AWbName = ActiveWorkbook.Name

i = 1

Do While MyName <> ""

If MyName <> AWbName Then

Set Wb = Workbooks.Open(MyPath & "\" & MyName)

ActiveSheet.Range("C7").Copy ThisWorkbook.Sheets("sheet1").Cells(1, i)

Wb.Close False

i = i + 1

End If

MyName = Dir

 Loop

 Application.ScreenUpdating = True

 MsgBox "已完成"

End Sub

追问

有两个疑问，1.自动提取当前工作目录下所有EXCEL文档C7单元格的数据自动填入汇总表，怎么给所有子表格的数据一一对应？每个子表格的名字都是有规律的，例如XXX001XX，XXX002XX……
我在汇总表里怎么知道那一栏的数据是哪个子表格的呢？如何一一对应着提取数据？

2粘贴到代码视图里，这句话没看懂，是直接粘贴到EXCEL表格内容里么？还是哪里进去的专用代码输入界面，谢谢！

追答

针对你的两个问题，我将代码做了修改：

第一行放置每个表格的C7单元格内容；第二行放置每个表格的名字，这样就解决了第一个问题；

粘贴到哪里，题主之前应该没接触到VBA，我这里做个简单的动态，你看下，有问题，在沟通。

在你表格的文件夹内随便新建一张表，点击打开，依次点击【开发工具】—【visual basic】，如果你没有开发工具这个选择，你就直接按快捷键【Alt+F11】，出来界面之后，右键【thisworkbook】—【插入】—【模块】，粘贴下面的代码，点击运行即可，你表格比较多，等待的时长会比较久，别急。

修改后的代码如下：

Sub 提取当前工作目录下所有表格c7单元格()

Dim Wb As Workbook

Application.ScreenUpdating = False

MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & "\" & "*.xlsx")

AWbName = ActiveWorkbook.Name

i = 1

Do While MyName <> ""

If MyName <> AWbName Then

Set Wb = Workbooks.Open(MyPath & "\" & MyName)

ActiveSheet.Range("C7").Copy ThisWorkbook.Sheets("sheet1").Cells(1, i)

ThisWorkbook.Sheets("sheet1").Cells(2, i) = MyName

Wb.Close False

i = i + 1

End If

MyName = Dir

 Loop

 Application.ScreenUpdating = True

 MsgBox "已完成"

End Sub

需要注意的是：所有表格的插页名字都叫“Sheet1”，如果是其它名字的话，代码得做相应的改动！

本回答被提问者和网友采纳

Answer 3

使用函数 indirect 来解决吧，建立一个数表，里面有各表格的名字，然后使用 其他函数来构成
indirect函数的参数。追问

能详细点么？

追答

参考图示，公式：

=INDIRECT("'["&F1&".xlsx]sheet1'!$c$7")

下拉就会依次引用 工作簿 表格甲、乙、丙  的工作表sheet1 的c7单元格

补充下，注意里面的 .xlsx 是表格的扩展名，早期的是xls

追问

&F1& 就是子表的文件名？可以随意更改？那子表要放在什么地址呢？

追答

自己研究下了，因为indirect函数的参数就是 类似
'[工作簿名称.文件扩展名]工作表名称'!单元格
遮掩的格式（注意文字全部用实际数据代替，符号全部保留）
引用公式中 "'["&F1&".xlsx]sheet1'!$c$7"
""中就是保留的各种数据 & 是连接各部分数据，最终形成
'[工作簿名称.文件扩展名]工作表名称'!单元格
格式的数据的。自己检查下
= "'["&F1&".xlsx]sheet1'!$c$7"
的运行结果，再放入 indirect函数即可。
公式中的f1就是对应f1单元格的数据，当然可以随便改，比如 sheet1！c1
就是 sheet1工作表的 c1单元格。

Answer 4

vba是一种办法。对于我这样不熟悉vba但熟悉powerquery的家伙来说，我用powerquery，第一次建立好数据模型之后，以后要做的就只是更改数据源然后刷新。

Answer 5

这个需要写代码来解决