【Excel】做一个自动更新的文件管理目录

2021-01-13 本文已影响0人兔子先生_小灰灰

管理一大堆位于不同文件夹中的文档，需要建立用ecxel建立目录。
要求：

对应文件下的文件数目增减可以自动更新目录，并读取文件名

在目录中插入超链接可以直接打开该文件，且文件路径为相对引用，即该目录转移到其他电脑可以正常使用

power query是微软推出的自助大数据分析工具，主打数据导入和查询功能，这个工具以插件的形式安装在excel2016及后续版本中，我们的自动更新目录就用它来实现。

1、将文件夹目录导入到power query编辑器中

从文件夹获取数据，然后点击加载，加载成功可以在右侧看到查询的连接了。

powerquery加载数据步骤 *GifCam制作*

2、编辑Power Query

点击屏幕右侧对应的查询就可以进入Power Query 编辑器，然后删除多余的列

Power Query 编辑器

3、加载到excel表格中

点击左上角关闭并上载就可以把修改后的表格更新到excel表中

关闭上载png.png

4、添加文件打开的链接

整理excel表格，在E3插入公式=HYPERLINK(C3&B3,"打开")

添加打开链接
现在在文件中新增或删除文档后，文档目录就会自动更新喽！
BUT：
power query本身是不支持相对路径引用的，意味着如果这个目录到别人电脑上,如果放在了不同文件夹下就不能正常使用了。🤔

于是奇技淫巧来了：

5、自动修改绝对路径

a、创建路径参数表，手动填写绝对路径，然后通过CELL函数获取文件实际位置
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1))-1)&[rPath]
b、创建power query查询并修改M公式
power query每一步操作本质是都是一条M公式，直接修改公式相当于创建一个自定义函数

let
    源 = Excel.CurrentWorkbook(){[Name="Arg_Path"]}[Content],
    筛选 = Table.SelectRows(源, each [Name] = strPath),
    值 = if Table.IsEmpty(筛选)=true then null else (Record.Field(筛选{0},"fPath"))
in
    值

代码的意义：
引入【strPath】这个外部参数
打开现有工作簿中名为【Arg_Path】的表格
筛选【Name】的值等于【strPath】
筛选后如果表为空返回【null】；不为空则取【fPath】的第一个值

2021-01-13 15_28_55-TBU文档目录.xlsx - Excel.png

修改完成后点关闭并上载，可以看到图标变成了 fx，这时候getpath的函数就创建成功了。

c、引用自定义公式
然后打开之前保存目录的查询，将其中的源数据路径修改为getPath("测试流程")，点击完成就可以实现路径相对引用了。
2021-01-13 15_25_50-TBU文档目录.xlsx - Excel.png