excel:超链接应用,快速生成目录的几个方法

工作杂谈 专栏收录该内容
31 篇文章 0 订阅

****部落窝教育-excel目录制作技巧****
原创:壹仟伍佰万/部落窝教育(未经同意,请勿转载)
更多教程:部落窝教育(http://www.itblw.com)
微信公众号:exceljiaocheng

原文地址:https://zhuanlan.zhihu.com/p/89613237(上)
原文地址:https://zhuanlan.zhihu.com/p/89868082(下)

编按:哈喽,大家好!在平时工作中,为 了能快速打开需要的工作表,我们通过会设置目录。目录的设置方法有很多种,但大多数人使用的方法还基本是手动插入超链接的操作。虽然这个方法也行,但是将大大降低你的工作效率。那么今天,我们将给大家分享在excel中生成目录的 6 种方法,保证招招精彩,赶紧来看看吧!

如下图所示,工作薄理由4张工作表,为了方便快捷跳转到指定的工作表中,我们先创建一个名为「目录」的工作表。
创建好后,就开始我们目录的制作吧!

基础方法

1.手动生成目录

单击鼠标右键 ——> “链接”(也可以直接按快捷键CTRL+K) ——> “在本文当中的位置” ——> 选择需要链接的工作表名称,修改要显示的链接名称 ——> “确定”,就能生成一个超链接。重复以上步骤,就可以生成一个目录。操作步骤如下图所示:

2.跳转函数生成耽搁链接

在A2单元格输入公式 =HYPERLINK("#方便面!a1","方便面")。并以此填写其他链接。

HYPERLINK 函数有两个参数:第 1 参数是链接的位置,第 2 参数是单元格里要显示的文字内容。

注意:当 HYPERLINK 第 1 参数的链接位置是某个工作表时,需在地址前面加 “#”,这个是固定的用法,小伙伴们记住就行。

优点: 操作简单、易上手。

缺点: ①比较法索,如果需要创建的目录过多,会非常耗时间。

②如果要实现双向跳转的目录,会占用多余页面的空间。

③不能实现自动更新,如果删除或增加工作薄中的工作表,需要重新修改目录。


二、跳转函数批量生成链接

前面说的方法虽然操作起来比较简单,但是都太麻烦了,而且不够灵活。有没有那种既能批量创建链接,还能伴随着工作表的变化,实现自动更新的目录呢?当然有啦!

其实上面的方法之所以繁琐,是因为我们无法直接的到各个工作表的名称,需要在公式中一个一个去输入。知道了问题就好解决了,说到提取工作表的名称,小伙伴们有没有觉得很耳熟呢?没错,前不久说道的宏表函数刚好可以达到这个目的。

由于宏函数无法在单元格中直接使用,需要定义名称才可以使用。所以我们可以先定义一个名为目录的名称,并在引用位置处使用宏表函数 =GET.WORKBOOK(1) ,提取出工作表的信息。


接着使用公式 =INDEX(目录,ROW(A2)),提取出名称为目录中的所有工作表的名称。

当然我们也可以使用转置函数 TRANSPOSE 提取出工作表的名称,不过需要先选中A2:A6区域,在输入公式 =TRANSPOSE(目录) ,完成后使用数组公式快捷键 CTRL+SHIFT+ENTER 返回结果。

由于现在提取出来的工作表名称是带用工作薄名称的额,所以需要我们使用公式进一步优化,达到只保留工作表名称的效果。在A2单元格输入公式 =REPLACE(INDEX(目录,ROW(A2)),1,FIND("]",INDEX(目录,ROW(A2))),""),下拉后即可完成这一步骤。

接着用 HYPERLINK 函批量生成超链接,在B2单元格输入公式 =HYPERLINK("#"&A2&"!A1",A2),即完成目录的制作。

后续如果我们的工作表有变动,只需要重新刷新并下拉拖拽公式,目录就能自动更新了。

最后将工作薄另存为“Excel启用宏的工作薄”即可完成整体操作。

其实除了可以使用宏表函数提取工作表名外,Power Query也同样能达到这一效果。

操作步骤: ①打开“数据”选项卡 ——> 点击“新建查询”下拉菜单 ——> “从文件” ——> “从工作薄”。

②在弹出的窗口中,找到名为“目录”的工作薄,选中并点击“导入”。

③在“导航器”窗口中,选中工作薄“目录”,并点击“转载数据”。

④下面我们就进入了Power Query编辑器界面,单机鼠标右键 ——> “删除其他列” ——> “关闭并上载”,此时在新增的一个工作表中,就会出现我们需要的工作表名称。

然后按照上述的步骤,用 HYPERLINK 函数批量生成超链接,在B2单元格输入公式: =HYPERLINK("#"&A2&"!A1",A2),即可完成目录的制作。

优点:

①批量生成链接,节约时间。

②可以实现目录的自动更新。

缺点: ①涉及到宏表函数和引用函数INDEX,对新手不友好。

②如果要实现双向跳转目录,会占用多余页面的空间。


三、CTRL+F生成目录

相信看完上面方法的小伙伴,已经开始头晕眼花了,没关系,下面小编就给大家介绍一个超便捷的方法,仅用快捷键CTRLF就可以制作目录!简直就是小白的福音呀!

操作步骤:

①找到在所有工作表中都为空的单元格,输入一个表中没有的任意字符,比如“囡”,这里叫大家一个批量录入的技巧。

在第一个工作表中,单机空白单元格A4,然后按住 SHIFT键点击左后一个工作表,输入“囡”,再按 ENTER键。此时可以发现每个工作表中A4单元格都有“囡”。

②使用CTRL+F打开查找页面,在“查找内容”中输入“囡”,点开“选项”,在“范围”处选择“工作薄”,最后点击“查找全部”,就可以实现一个动态目录啦!操作动图如下:

优点:

①操作简单、节约时间,小白也可以轻松上手。

②可以实现双向跳转

缺点:

①会改变原本工作表中的数据。

②目录不能随着工作表变动而自动更新。
说了这么多,小伙伴们估计有疑问了?如何既能在不占用多余页面空间的基础上,实现双向跳转,又能让目录实现自动更新呢?

四、用excel自带的“检查功能”制作目录

“检查功能”是啥?是不是在此之前从来没听说过。其实在“检查功能”中,有两个地方可以用来制作目录,一是“检查兼容性”,二是“检查辅助功能”。我们分别来说说他们都是如何制作目录。首先,“检查兼容性”它的主要作用就是检查当前工作表中所使用的功能是否有2003版本无法支持的功能。是不是还不太明白?没关系,看完操作,就懂!

操作步骤: ①输入公式

按照上面提到的批量录入方法,在每个工作表的一处空白单元格中,输入公式 =xfd1

②自动生成“超链接列表”

点击“文件” ——>“信息” ——>,在“检查工作薄”的左侧,“检查问题”的下拉选项中,点击“检查兼容性”。

在弹出的“兼容性检查器”窗口中 ——> “复制到新表”,生成“兼容性报表”。

此时,大家可以发现,在刚刚生成的“兼容性报表”中,E列已经自动生成了带连接的工作表名称。

原理: =xfd1 表示等于excel中第16384列单元格的数据,由于2003版本的excel仅支持256列 x 65536行的工作表格式,因此无法兼容改公式。用检查功能就可以列出03版本的excel不能兼容的数据所在的工作表名。

③ 制作目录表

将E列中生成的工作表链接复制到名为“目录”的工作表中,使用替换快捷键CTRL+H,把“'!A2”替换为空。

最后再制作一个返回“目录”表的链接。

④ 制作返回“目录”表的链接

删除“兼容性报表”,并按照之前批量录入数据的方法,选中除“目录”外的工作表,在其空白单元格中,输入公式: =HYPERLINK("#目录!A1","返回目录"),完成操作!



优点:

方便快速添加,不需要记忆公式。

缺点:

① 只适用于2003版本以上,并且目录不能随着工作表的变动而自动更新。

② 如果要实现双向跳转的目录,会占用多余页面的空间。

下面我们再来说说“检查辅助功能”,它的主要作用是检查出工作表中可能会为残疾人士带来困扰的数据,并提出解决建议。是不是同样一头雾水?没关系,一起来操作一下吧!

操作步骤:

使用批量录入数据的方法,在每个工作表的一处空白单元格中,输入任字符,字体颜色设置为白色,这里输入字符“囡”。然后再点击“文件” ——> “信息”,在“检查工作簿”的左侧,“检查问题”的下拉选项中,点击“检查辅助功能”,目录就制作完成了。

原理:我们这里把字体颜色设置为白色,使字体颜色与背景色相近。对于视力不太好的人来说,阅读与背景对比度较低的文本通常会觉得比较吃力。所以辅助功能检查器就认为这是会给残疾人带来困扰的数据,于是将它们都查找了出来。

优点:

① 方便快速添加,不需要记忆公式。

② 可以实现双向跳转。

缺点:

① 会改变原本工作表中的数据。

② 目录不能随着工作表的变动而自动更新。

五、监视窗口

嗯?监视窗口又是什么鬼?是不是发现自己漏掉了好多excel功能?顾名思义,监视窗口主要是用来监视工作表中的单元格及其公式。听起来似乎和目录没什么关系,别着急,下面我们就一起来看看如何使用监视窗口制作目录。

操作步骤:
① 在“公式”选项卡下,点击“监视窗口”。在“监视窗口”中点击“添加监视”,依次加入各个工作表。

② 添加完成后,双击“监视窗口”中的工作表名称,即可实现快速跳转。

此外,监视窗口还可以像其他工具栏一样进行移动或停靠,可以将监视窗口置于工作表的上下左右侧。

优点:

① 方便快速添加,不需要记忆公式

② 可以实现双向跳转,放置目录的位置比较灵活

缺点:

目录不能随着工作表的变动而自动更新。

六、VBA生成目录

在上面教程中,给大家留下了一个问题:如何既能在不占用多余页面空间的基础上,实现双向跳转,又能让目录实现自动更新呢?答案就是VBA。

单击目录中的工作表名称,即可跳转到相应的工作表中,双击其他的工作表单元格,即可返回目录页面。

如果工作簿中的工作表发生变动,被删除,或者增加,目录也可以实现自动更新,只需要在删除或者增加工作表后,重新运行VBA中的第一段代码即可。

比如,我们增加一个工作表,再删除“炫富三件套”工作表。接着在EXCEL对象里面的“Sheet1(目录)”中,点击第一段代码,并点击“运行”按钮。再返回工作表中,就会发现,目录已经自动更新了。

操作步骤:

详见:最快捷的Excel目录制作:不用公式,只需3次复制粘贴

优点:

① 能在不占用多余页面的基础上,实现双向跳转。

② 目录能自动更新。

缺点:

比较难懂
  • 1
    点赞
  • 0
    评论
  • 3
    收藏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值