说说关于Excel目录的二三事
问题分析
应用场景：
一个比较大的项目，存在很多项目文件，大多为Excel文件，而Excel文件中又包含较多的工作表，记录着不同部门的数据，工作中经常需要查找文件中的某些资料和数据。
应用问题：
由于文件繁多，每次想找到需要的资料都很费劲，特别是找某个Excel工作表，效率很低，很是耽误时间。
应用需求：
希望创建有效的项目目录索引，能够快速定位到想要的资料。
解决方案：
1、创建专门的文件目录表，以包含所有的项目文件，让查找项目文件不再困难。
2、针对有很多工作表的Excel工作簿，为其创建目录表，可链接定位到其中的某个工作表。
具体实现方法
第一步：创建文件目录表
1、在D盘的“项目文件”目录下存在很多项目文件，我们新建一个Excel文件，命名为“目录”；
2、打开新建的目录文件，切换到【公式】功能页签，点击名称管理器，新建两个工作簿范围的名称：
2.1、第1个名称“路径”，引用位置：=GET.DOCUMENT(2)&T(NOW())；
名称公式简单解释：
GET.DOCUMENT(2)是宏表函数，获取当前文件所在目录的路径；NOW()函数用于得到系统当前日期时间，为易失性函数，T函数将其转换为空文本；在之后增加“&T(NOW())”，目的是使宏表函数可以自动重算，在定义名称时，加入一个易失性函数，只要有单元格触发了重算，宏表函数即可重算；
2.2、第2个名称“文件名列表”，引用位置：=FILES(GET.DOCUMENT(2)&"*.*")&T(NOW())；
名称公式简单解释：
FILES函数返回指定路径下的所有文件名；GET.DOCUMENT(2)之后再增加“&"*.*"”，是指返回任意扩展名的文件名；
3、在B3单元格输入公式：
=IFERROR(HYPERLINK(路径&""&INDEX(文件名列表,ROW(1:1)),INDEX(文件名列表,ROW(1:1))),"")，下拉填充，直到显示出目录下的所有文件；
公式简单解释：
INDEX(文件名列表,ROW(1:1)，返回定义的名称【文件名列表】数组中的第1个文件名，随着公式向下填充，将依次显示出各个文件名；路径&""&INDEX(文件名列表,ROW(1:1)，构造文件名相应的完整路径；HYPERLINK函数使用公式创建超链接，第1参数指定要打开的文件的路径和文档名，第2参数指定单元格中显示的跳转文本，即文件名称；IFERROR函数屏蔽错误值，当下拉超过总文件数量时，HYPERLINK返回错误值“#REF!”，IFERROR将其转换为空；
4、调整表格格式，设置字体、边框、标题等：
5、保存文件，注意，由于使用了宏表函数，所以该文件必须保存为“启用宏的工作簿”，后缀为“xlsm”。
至此，文件目录表创建完成，在文件夹下新增文件，打开目录文件时（注意要选择“启用宏”），目录将自动更新。
第二步：创建工作簿目录
1、在工作簿中新增“目录”工作表，调整位置到第1个，设置工作表标签颜色为红色。
2、切换到【公式】功能页签，点击名称管理器，新建名称“工作表名”，引用位置：
=MID(GET.WORKBOOK(1)&T(NOW()),FIND("]",GET.WORKBOOK(1)&T(NOW()))+1,30)
名称公式简单解释：
GET.WORKBOOK(1)返回工作簿中所有工作表的名称，为宏表函数，返回的数据形式为：[工作簿名称]工作表名称；在之后增加“&T(NOW())”，目的是使宏表函数可以自动重算；FIND函数查找“]”在GET.WORKBOOK(1)返回的数据中的位置；MID函数从“]”出现的位置之后1位开始，获取其后30位的字符，意在获得纯粹的工作表名称；
3、在B3单元格输入公式：
=IFERROR(HYPERLINK("#"&INDEX(工作表名,ROW()-1)&"!a1",INDEX(工作表名,ROW()-1)),"")
公式简单解释：
ROW()，对于B3单元格，获取当前单元格的行号，为3，随着公式向下复制，将获得3、4、5……的自然数序列；ROW()-1，将自然数-1，获得2、3、4……的自然数序列，目的是使得INDEX函数可以从数组中的第2个开始逐一获得工作表名，要排除掉“目录”工作表自身；HYPERLINK函数使用公式创建超链接，在工作表名前面加“"#"&”，后面加“&"!a1"”，表示链接到当前工作簿中的指定名称工作表的A1单元格；IFERROR函数用于屏蔽错误值；
4、调整目录表的格式，设置字体、边框、标题等：
5、在“目录”工作表之外的其它工作表，添加“返回”链接，在工作表的合适位置输入“返回”，为其插入链接，设置链接到“本文档中的位置”，选择位置为“目录”工作表，输入单元格引用为“B2”：
6、保存文件，注意，此时也需要保存成“启用宏的工作簿”，后缀为“xlsm”。
至此，工作簿目录也创建完成，在工作簿中新增或修改工作表，按F9或重新打开文件时，这个目录将自动更新。
本文版权归Excellive所有，更多Excel好文和示例下载，请关注Excellive！喜欢的话就请点赞、收藏、分享吧！如果您有问题或建议，欢迎留言与大家一起分享！