EXCEL表格制作智能文件管理系统应用详解

互推小编 2023-07-22

如下为动态图演示：

在单击任意列表中的文件名称，此时就可以打开此的文件内容；同时如在文件中新增/删除或更改文件名称，按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函数的公式及应用案例详解——