一、案例 如下图所示,为D盘文件夹“这是一个文件夹”下的文件。要求在Excel工作表中为该文件夹下的所有文件建立带跳转链接的目录。 最终实现的效果如下图所示。点击单元格内某个链接,即可打开相应的文件。 二、操作步骤 1、复制粘贴文件保存路径 新建一个工作表,重命名为“目录”。复制需要建立目录的文件在电脑中的存放路径(本例存放路径为“D:\这是一个文件夹”),粘贴到B2单元格。在复制的路径后面输入“\*”。如下图B2单元格所示: 2、新建名称 单击【公式】-【定义名称】,打开【新建名称】对话框。【名称】处输入“文件目录”,【引用位置】处输入公式 =FILES(目录!$B$2)&T(NOW()) 点击确定。 Files()是宏表函数,可以获取指定目录所有文件名,返回的结果为一个水平数组。T(NOW())返回空值。当文件夹下新增、删除文件,或修改文件名时,利用T(NOW())的易失性,按F9键一键更新文件目录。 3、在单元格D2输入公式 =IFERROR(HYPERLINK(LEFT($B$2,LEN($B$2)-1)&INDEX(文件目录,ROW(A1)),INDEX(文件目录,ROW(A1))),"") 拖动填充柄向下复制公式,直到返回空值。结果如下图所示: 这样就成功地为B2单元格的路径下的文件建立带跳转链接的目录了。如果需要为其他文件夹下的文件建立目录,只需要修改B2单元格的路径,然后按F9键刷新即可。 公式解析: (1)“文件目录”指步骤2新建的名称“文件目录”。该名称指FILES()函数返回的指定文件夹下的文件名的一个水平数组。 (2)INDEX(文件目录,ROW(A1))指返回该水平数组中的第1个数据。当公式向下复制时,依次返回第2个、第3个……数据。结果如下图所示: 错误值” #REF!”表示文件名已全部列示,没有文件名可以返回了,因此返回错误值。因此D2单元格中会使用IFERROR函数屏蔽错误值。 (3)Hyperlink函数用于建立跳转链接,其语法为HYPERLINK (link_location,[friendly_name])。 HYPERLINK(LEFT($B$2,LEN($B$2)-1)&INDEX(文件目录,ROW(A1)),INDEX(文件目录,ROW(A1))),其中参数(link_location为LEFT($B$2,LEN($B$2)-1)&INDEX(文件目录,ROW(A1)),用于确定链接的文件的存放路径。 参数[friendly_name]为INDEX(文件目录,ROW(A1)),用于确定链接显示的文本,此处链接显示的文本就是文本名。 参数link_location的值如下图所示: (4)IFERROR函数用于屏蔽错误值。当所有文件均已建立链接时,返回空值。 拓展(为指定类型的文件建立目录): 如果不仅需要为指定文件夹下的文件建立目录,还需要在选择文件类型后,只显示指定文件类型的文件目录,应该如何做呢? 如下图所示,当在B2单元格选择文件类型“.doc”时,E列仅显示word文件;当在B2单元格选择文件类型“.xls”时,E列仅显示excel文件。 要实现这个效果,只需要在上文介绍的操作步骤基础上做到以下两点:(1)在B2单元格使用【数据验证】功能,制作下拉菜单。在“来源”中输入“*.文件类型”,其中”*.*”代表所有文件类型。 2、在B2单元格的路径修改为: END 如果你想为工作表建立目录,可以阅读以下文章哦 |
|