快捷搜索:

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

 

如下为动态图演示:

在单击任意列表中的文件名称,此时就可以打开此的文件内容;同时如在文件中新增/删除或更改文件名称,按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键会自动更新。

[注:本文部分图片来自互联网!未经授权,不得转载!每天跟着我们读更多的书]


互推传媒文章转载自第三方或本站原创生产,如需转载,请联系版权方授权,如有内容如侵犯了你的权益,请联系我们进行删除!

如若转载,请注明出处:http://www.hfwlcm.com/info/117187.html