Excel下拉公式，引用的路径和工作簿名称怎样根据单元格内容的变化而变化
某天有个朋友问到这样的问题，一个文件夹下有500多个文件，部分文件截图如图1，每个文件的格式一样，部分内容截图如图2，需要根据把这些文件的内容汇总在一张工作表。
图1
图2
需要汇总的工作表部分字段内容如图3：
图3
需要汇总的工作簿有500多个，字段有近100个，5万多个单元格内容来自500多个工作簿，用公式怎样引用那么多工作簿单元格内容，一个个工作簿引用，一个字段要写的公式就有500多个。当需要更改公式中单元格的引用，而不更改公式本身，可以用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-样式的引用。
要引用500多个工作簿，必须这些工作簿全部打开，太麻烦了，因此，我们先把这500多个工作簿合并到一个工作簿的500多个工作表。操作步骤如下：
Step1、将需要合并的Excel工作簿放在一个文件夹中；
Step2、在该文件夹中新建一个工作簿；
Step3、打开新建立的Excel工作簿，按下【Alt+F11】组合键，在Visual Basic编辑器中选择“插入→模块”，在代码窗口输入以下代码，点击菜单栏运行，运行子过程/用户窗体，弹出需要合并的文件，关闭代码输入窗口。打开Excel工作簿，可以看到将选中的工作簿中的工作表都复制到了新建工作簿中。
Sub 合并汇总()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
FileToOpen_N = Application.GetOpenFilename("xls文件,*.xls", _
p.s.如果是xlsx文件就修改文件类型为xlsx
Title:="请选择要合并工作簿", MultiSelect:=True)
Newbz = 0
On Error Resume Next
For Each FileToOpen In FileToOpen_N
If FileToOpen <> False Then
If Newbz = 0 Then
Booknum = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = Booknum
NewBookName = ActiveWorkbook.Name
Sheets(1).Name = "sheet_tmp"
Newbz = 1
End If
Set OpenBook = Workbooks.Open(FileToOpen)
For Each Xlsheet In OpenBook.Sheets
Xlsheet.CopyBefore:=Workbooks(NewBookName).Sheets("sheet_tmp")
Next
OpenBook.Close SaveChanges:=False
End If
Next
Workbooks(NewBookName).Sheets("sheet_tmp").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Dim sht As Worksheet, lstRowZb As Integer, lstRow As Integer
'lstRowZb:总表的lastrow
Worksheets("1").Select
Worksheets("1").Range("a1:h1").CopyDestination:=Range("a1")
'复制表头
For Each sht In Worksheets
lstRowZb = Range("a1048576").End(xlUp).Row '每次COPY前取总表的最后一行
With sht
If .Name <> "1" Then
lstRow = .Range("a1048576").End(xlUp).Row
.Range("a2:h" & lstRow).CopyDestination:=Cells(lstRowZb + 1, "a")
End If
End With
Next sht
End Sub
此过程大概执行10分钟，执行完毕，500多个工作簿汇总到一个工作簿500多个工作表了，部分工作表截图如图4。
图4
接下来就是引用500多个工作表内容，先在汇总表里插入辅助列，辅助列为工作表名称，这里因为工作表名称左边的内容都相同，只是右边的数学不同，因此可以先输入第一个工作表名称，再拖动单元格右下角黑色+下拉。检测编号为500多个工作簿名称的前面编号，如图5，如果手工一个个输入要输入500多次，而且不能保证不输错。
图5
要遍历文件夹中的多个工作簿名称，可能很多人用VBA，这里介绍一个很简单很爽的方法，操作步骤如下：
Step1、鼠标左键点击，开始→运行→在对话框中输入字母“cmd”，进入dos模式 ，如图6:
图6
Step2、在dos模式输入命令行“cd c:\xueyuan”然后回车，再输入命令行“dir /b>rename.xls"，可将文件夹中的文件名在rename.xls文件中列出，用Excel打开c:\xueyuan中的rename.xls，你会发现所有文件名称全部罗列在A列中。
Step3、在rename文件中的B1单元格输入A1单元格左边的检测编号，按快捷键【Ctrl+E】快速填充B列其他单元格内容。关于快速填充功能请点击文章查看，神奇的快速填充功能
图7
至此工作表名和检测编号全部录入完毕，接下来性别、年龄、采血编号等在如图2的表格上方的信息用indirect函数引用。C2单元格公式=INDIRECT("'"&$A2&"'!D3")，双击单元格右下角黑色+填充整列公式，往右拖动公式填充D列和E列公式。
图8
解释C2公式：如果不用indirect函数，直接引用工作表，C2公式为='P-检测报告单(1)'!D3,所以用indirect函数，要先用单引号和A列连接起来，再连接工作表名称后面的'!D3。
F列到最后一列字段多，要引用的内容都在每张表的表格部分，因此用vlookup函数匹配，F2公式=VLOOKUP(F$1,INDIRECT("'"&$A2&"'!$A$7:$D$55"),4,0)，往右往下拖动公式填充全部单元格公式。
图9
总结：解决这个问题用到VBA多表合并、Dos命令提取文件夹下所有工作簿名称、快速填充、indriect函数引用不同工作表内容，以及vlookup函数查找。