娃
在word中,我们可以通过创建目录来快速了解文本大纲,那么在excel中,我们是否也可以通过创建目录,来快速查找excel文件呢?
1、同一excel文件创建目录
在同一个excel中含有多个工作表,我们就可以通过创建目录,关联其他的工作表。
操作:
①选中B1单元格,点击“公式—名称管理器”,在“引用位置”中输入:=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())。
②接着,回到B1单元格,输入函数:=IFERROR(HYPERLINK(目录&"!A1\
奇趣软件技巧达人
最简单的Excel创建目录方法, 利用Excel兼容性检查,适用于2003以上版本
1)首选我们做出一个目录的格式,如下所示
2)选择所有的工作表,在A列前插入一行
3)在A1单元格输入公式=WW1
4) 点击文件-信息-检查问题-检查兼容性,然后复制到新表
5)复制如下表格内容粘贴到目录中,链接创建成功,将!A1替换成空白,再将目录适当美化
原理:利用Excel兼容性检查
由于Excel版本比较多,在保存后excel会检查是否有早期版本不支持的功能,比如说Excel 2003最大支持256列,即第IV列,如果现在版本数据在第IV列之后,excel会在兼容性检查里提示,如下图所示
这时候点击复制到新表,你会发现Excel把所有的出现问题的数据都列出来了,并且以超链接的方式展现出来
得到的启示就是:我要创建一个Excel低版本不兼容的公式,比如说在A1单元格输入公式=WW1(WW1单元格所在WW列超出了Excel2003最大支持列数256列,即第IV列),把所有工作表的A1单元格都输入这个公式,然后利用Excel兼容性检查,把其中的超链接复制出来,粘贴到目录就可以了
要创建返回目录表的链接才完美
操作要点:
1)选择目录之外的第一个工作表标签,点击右键--选择所有的工作表--再按住ctrl,点击目录工作表,这时候除了目录之外的工作表标签就都被选择上
2)点击A1单元格,输入公式=HYPERLINK("#目录!A1\
Excel奇妙之旅
用Excel建立目录,通常有以下2种情况:
对一个Excel工作簿内的多个表建立目录
对某个文件夹下的文件(比如其中的word或Excel文件)建立目录
以下对这两种情况分别用示例进行说明。
一、对一个Excel工作簿内的多个表建立目录
对一个Excel工作簿内的多个表建立目录,可以用手动添加超链接、或通过兼容性检查生成相关链接、又或者通过写VBA代码等方法来实现,但是,这些方法其实都比较麻烦。
因此,我推荐采用Excel2016的超级强大新功能Power Query(2010或2013可到微软官方下载插件)完成,操作非常简单,而且也可以在工作簿中的表格出现变化时直接刷新得到最新目录。实现步骤如下:
1、新建查询,将工作簿数据加载到Power Query
3、删除不需要的信息列
4、将数据返回Excel,用函数HyperLink创建链接
通过以上4步就可完成一个Excel工作簿所有工作表的目录创建,不需要写任何代码,只要鼠标点点点即可,非常简单,而且随着工作表的增减,只需要在链接目录表里右键刷新就得到最新的目录信息!
二、对某个文件夹下的文件(比如其中的word或Excel文件)建立目录
这实际上就是利用Excel管理文件夹及相关文档,可以很方便地利用Excel的筛选、查找等功能。
以前,要用Excel实现这样的管理比较麻烦,比如通过dos命令行获得文件相关信息,然后复制粘贴进来,或者通过VBA批量获取,但这些方法都太专业,对很多用户来说操作非常麻烦。
因此,推荐使用Excel2016的新功能Power Query来完成,而且,借助Excel中的HyperLink函数实现相关文档链接,达到直接通过Excel打开文档的效果。
具体实现步骤如下:
1、从文件夹获取数据
3、筛选想通过Excel进行管理的文件(如“doc”、“docx”、“xls”、“xlsx”等等)
4、删除不需要的信息
5、将获取的文档路径和名称信息返回Excel
6、通过hyperlink函数建立文档链接,如下图所示:
以上通过简单的6个步骤,就实现了对文件夹相关文件建立文档链接的过程,而且,在文件夹相关文档更新后,可以在Excel中直接刷新得到最新结果,如下图所示:
以上是用excel建立文件目录或工作表目录的2个示例,从中可以看出,通过Excel的新功能Power Query实现目录的创建,不仅简单易用,新手也能轻松掌握!而且还达到了VBA一键刷新的效果,非常值得推荐使用。
更多精彩内容,敬请关注【Excel到PowerBI】
私信我即可下载60+Excel函数、数据透视10篇及Power系列功能95篇汇总训练材料
我是大海,微软认证Excel专家,企业签约Power BI顾问
让我们一起学习,共同进步!
Excel到PowerBI
EXCEL工作表一多容易混乱,结构也不清晰,手动用超链接制作目录又太麻烦,其实,用公式就能完美实现目录功能!先看下效果
怎么实现呢?轻松三步轻松实现!
首先,定义一个名称:
SheetsName=MID(GET.WORKBOOK(1),FIND("]\