如下为动态图演示: 在单击任意列表中的文件名称,此时就可以打开此的文件内容;同时如在文件中新增/删除或更改文件名称,按F9键文件名称列表会自动更新。 思考如何在EXCEL表格中实现此效果 解题思路 第一步:动态获取文件名称 ①提取文件的动态路径 首先使用CELL函数公式=CELL('filename')获取文件的全路径('D:\EXCEL文件夹\[新建 XLSX 工作表.xlsx]Sheet1'),然后使用LEFT和FIND函数组合公式=LEFT(CELL('filename'),FIND('[',CELL('filename'))-1) 提取文件夹的动态路径('D:\EXCEL文件夹\');此时文件位置移动后公式提取的路径也会相对变更。 CELL函数知识详见——CELL函数公式及应用案例详解—— 如下为动态图演示: ②自定义名称 使用FILES函数自定义【名称】区域名称=FILES(LEFT(CELL('filename'),FIND('[',CELL('filename'))-1)&'*.*')&T(NOW()) 提取文件名,*.* 代表文件夹中的所有内容的文件,连接T(NOW() 可以实现自动更新功能(T函数的作用是检测数据是否为文本,返回原样或空,而now函数结果为一个数值,所以T函数返回空值,不影响FILES函数获取的文件名称)。 LEFT函数知识详见——LEFT函数应用技巧—— FIND函数知识详见——FIND查找定位函数应用技巧—— T函数知识详见——EXCEL中最短函数N和T函数的应用技巧—— NOW函数知识详见——易失性函数NOW应用技巧—— 动态演示图如下: ③提取文件名称 选中C3单元格输入公式 =INDEX(名称1,ROW(1:1)) ,下拉填充公式,获取文件夹中的所有文件名称,此时文件夹中新增/删除或更改文件名称后,按F9键列表中的文件名称会自动更新。注意:公式的【名称1】为自定义的区域名称。 INDEX函数知识详见——INDEX函数的公式及应用案例详解—— 第二步:超链接目录文件 在C3单元格中补充HYPERLINK函数公式 =HYPERLINK(LEFT(CELL('filename'),FIND('[',CELL('filename'))-1)&INDEX(名称1,ROW(1:1)),INDEX(名称1,ROW(1:1))) 。 HYPERLINK函数知识详见——HYPERLINK函数的应用技巧—— 公式解析: HYPERLINK函数表达式,HYPERLINK(Link_location,[Friendly_name]),Link_location 必需。 链接位置(要打开的文件名称或完整路径)。Friendly_name 可选。 超链接要显示内容。 CELL('filename'),FIND('[',CELL('filename'))-1) 公式部分为获取文件的路径,INDEX(名称1,ROW(1:1)) 部分获取的文件夹第一个文件名称,将它们使用&链接符链接在一起后,就是文件家中第一个文件的全路径,HYPERLINK函数第一个参数;第二参数 INDEX(名称1,ROW(1:1)) 是超链接要显示的内容(文件名称);当公式下拉填充时ROW(1:1) 返回1、2、3...等序号,HYPERLINK函数公式相对获取文件夹中第1、2、3...文件名。 第三步:屏蔽错误值 由于要实现文件夹中新增文件后,按F9键后能自动更新获取到新增文件名,需要将HYPERLINK函数公式下拉填充至大于现有文件夹文件数量,此时会导致产生错误值。 解决的办法就是在原HYPERLINK函数公式外嵌套一个IFERROR函数 =IFERROR(HYPERLINK(LEFT(CELL('filename'),FIND('[',CELL('filename'))-1)&INDEX(名称1,ROW(1:1)),INDEX(名称1,ROW(1:1))),'') ,当结果Wie错误值,返回空。 IFERROR函数知识详见——IFERROR函数的公式及应用案例详解—— 到这里公式就全部嵌套完成,单击文件名称可以跳转打开对应的文件,文件夹中新增/删除或更新名称后,按F9键会自动更新。 |
|