两种用 Excel 创建目录的妙招
- 秋叶Excel
2022-09-28 14:48
原文标题:《Excel 还能创建目录?这招超简单,快到没朋友!》
大家好,我是最近努力学习的小爽~
最近在梳理 Excel 知识点的时候,不经意发出了一个疑问:
Word 中有目录导航,我们通过点击标题,就可以跳转到对应的文档位置。
PPT 中我们可以通过新增节,点击对应的节,就可以跳转到相对应的 PPT 页面。
为什么在 Excel 中,对于工作表,没有一个类似于导航目录的玩意???
Excel 虽然也有导航栏,但是当工作表数量很多,我们想找到指定工作表就太麻烦了!
紧接着,我就在想,既然 Excel 中没有,那我们可不可以自己创建一个目录页?
所以根据这个思路,我做了一个这样的目录页:
本文就介绍一下常用的 2 种创建超链接目录的方法:
利用 hyperlink 函数创建目录页(适用于所有版本)
利用兼容性检查创建目录页(适用于 Office 2003 以上的版本)
大家就跟着我的思路,继续往后面看吧~
现在工作簿中有如下的工作表,我们需要在目录页中创建工作表目录。
01、利用 hyperlink 函数创建目录页
创建目录页前,我们需要先获取工作表名称。
一个个输入工作表名称也是可以的,不过有点麻烦,下面我先介绍 2 种获取工作表名称的方法。
▋第一步:获取工作表名称,这里我们介绍两种方法。
❶ 方法一:利用方方格子函数库
之前我们介绍过方方格子函数库,这里我们就用到函数库里的 GetSheetName 函数
公众号后台回复:函数库,即可获得下载链接~
GetSheetName 函数的语法规则为:
=GetSheetName (序号,[是否忽略隐藏表]))
比如说:
=GetSheetName (1),就是获得第一张表的名称
=GetSheetName (2),就是获得第二张表的名称
……
我们可以利用 ROW 函数获得连续序号。
我们在目录页 A2 单元格中输入公式:
=IFERROR(GetSheetName(ROW(A2)),)
下拉填充,此时所有的工作表名称都出来了。
❷ 方法二:自定义函数公式。
除了用函数库,我们也可以自己写一个自定义函数。
① 按住快捷键【Alt+F11】打开 VBA 编辑器,右键插入一个模块。
② 单击模块,将 VBA 代码复制到编辑器里面。
Function getName(ByVal sheet_no As Integer)getName = Worksheets(sheet_no).Name' 这个的含义就是:Worksheets (1).Name,获取第一张工作表的名称;Worksheets (1).Name,获取第一张工作表的名称,以此类推……End Function
现在在工作表中,就存在 GetName 函数了,我们直接使用就可以啦~
注意:
使用自定义函数的话,文档需要另存为 xlsm / xls 格式。
不想改的话,可以直接获取工作表名称后,将名称直接复制粘贴为值,
到这里,我们就得到工作表的名称了,接下来我们就直接利用 hyperlink 函数创建目录~
▋第二步:使用 hyperlink 函数创建目录
hyperlink 函数基本语法:
=HYPERLINK (地址,[友好名称])
我猜,肯定会有小伙伴直接这样写公式:
=HYPERLINK(A2&"!A1",A2)
不过点开,结果会出错,原因是 HYPERLINK 函数在引用单元格的时候,第一参数前面需要加个#号。
整合起来我们就可以直接编写公式:
=HYPERLINK("#"&A2&"!A1",A2)
此时,目录页就大致做成了。
对于目录页的美化,我们可以将 A 列中的工作表名称设置为白色,C 列取消下划线,修改颜色。
02、利用兼容性检查创建目录页
PS:关于兼容性检查:Office 可以检查文档与其他版本的 Office for Mac 和 Windows 版本的 Office 的兼容性,并创建兼容性报告。
你可以打开兼容性报告以了解有关任何兼容性问题的详细信息,并尝试修复。
我们先来看一下具体操作!
▋第一步
❶ 选中除目录页以外的所有的工作表。
点击第 2 个工作表,按住【Shift】键不松开,点击最后一个工作表。
❷ 在 A1 单元格中输入:
=XDF1
❸ 按住快捷键【Ctrl+Enter】批量填充。
▋第二步:打开兼容性检查,将生成的目录链接复制到目录表上的 C2 单元格上。
❶ 选择【文件】选项卡。
❷ 选择「信息」-「检查问题」-「检查兼容性」。
❸ 此时会弹出兼容性检查器对话框,选择「复制到新表」,此时会出现一个工作表名称为「Sheet2」的工作表。
▋第三步:将生成连接区域复制到目录页工作表 B2 单元格上,通过替换和字体格式设置,美化目录页。
❶ 将链接区域进行复制。
❷ 将区域粘贴到目录页 C2 单元格。
❸选中区域,按住快捷键【Ctrl+H】调出替换窗口,将 '!A1 全部替换为(空)。
❹去掉下划线,更改字体颜色,修改单元格边框。
此时,目录页就完成了,鼠标悬停在文字上面会出现小指头,单击之后可以跳转到对应的工作表。最后将 Sheet2 工作表直接删除即可。
▋第四步:为除目录页之外的工作添加跳转链接。
选中除目录页之外的工作表。
在 A1 单元格中输入公式:
=HYPERLINK ("#目录页!A1","回到目录页")
【Ctrl+Enter】批量填充,将字体设置为加粗绿色字体。
最后将目录进行简单美化,就能做出开头所示的效果啦~
看到这里,你肯定有一些小小的问号?
XDF1 是什么意思?
我们点击 A1 单元格,按住快捷键【Ctrl+→】,这个时候可以跳转到最后一列,也就是 XFD1。
XDF1 跟 XFD1 一样,其实就是一个单元格,列标为 XDF,行标为 1。
输入公式:
=column(XDF1)
结果为 16334。
也就是 XDF1 的列数为 16334,工作表的最大列就是 XFD,也就是 16384 列。
为什么要输入 = XDF1 呢?
兼容格式的最大列数为 256 (IV) 列,当前格式的最大列数为 16384 (XFD) 列。
当我们在工作表中使用公式 = XDF1 后,再检查兼容性问题。
由于兼容性格式最大列数为 256,引用不到列数为 16334 的单元格,所以会出现窗口提示。
我们将检查到的内容复制到新表中,就可以看到对应提示问题中的超链接位置。
所以,除了引用 XDF1 这个单元格,我们还可以引用在 256(IV)到 16384 (XFD) 列之间的单元格。
03
好啦,最后总结一下本文介绍的 2 种方法:
利用 hyperlink 函数创建目录。
涉及知识点:
❶ 获取工作表名称。
自定义函数的编写,主要是利用 Worksheets (1).Name,表示第一个工作表的名称。你会发现其实 VBA 也不是特别难;
❷ 利用 hyperlink 函数创建超链接目录,其中第一参数,在跳转单元格的时候前面需要加一个#号。
利用兼容性检查创建目录。
涉及知识点:
❶ 兼容格式(03 版本)的最大列数为 256 (IV) 列,除兼容版本以外的格式最大列数为 16384(XFD)。
所以我们可以利用引用单元格构造兼容性问题,再检查兼容性问题时,复制新表,得到我们创建目录时所需要的超链接。
学会了今天的小技巧,当别人还在一个个翻找工作表时,你通过目录,一秒就能找到指定工作表啦!
如果你工作中经常用到 Excel,熟练掌握这些基础操作,能帮你大大提升效率!
本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小爽 编辑:竺兰
广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。