excel批量更改超链接_excel超链接应用：快速生成目录的几个方法-上

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

＊＊＊＊＊＊＊＊＊

如下图所示，工作簿里有4张工作表，为了方便快速跳转到指定工作表中，我们先创建一个名为“目录”的工作表。

创建好后，就开始我们目录的制作吧！

一、 基础方法

1.手动生成目录

单击鼠标右键-点击“链接”（也可以直接按快捷键CTRL+K）-链接到“在本文档中的位置”-选择需要链接的工作表名称，修改要显示的链接名称，最后“确定”，就能生成一个超链接。重复以上步骤，就可以生成一个目录。操作步骤如下图所示：

2.跳转函数生成单个链接

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

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

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

关于HYPERLINK函数的详细用法，小伙伴们可以参考之前的教程《给我去你心里的地址，我就能用excel跳转函数住进去》，在此就不细说了。

优点：

​操作简单、易上手。

缺点：

① 比较繁琐，如果需要创建的目录数过多，会非常耗时间。

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

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

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

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

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

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

关于更多宏表函数的内容，小伙伴们可以参考之前的教程《为什么你的加班在老板眼里一文不值？因为Excel宏表函数3秒就可以解决》

接着使用公式“=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启用宏的工作簿”即完成整体操作。

详细的批量生成超链接目录的方法，小伙伴们可以参考之前的教程《用GET.WORKBOOK函数实现excel批量生成带超链接目录且自动更新》，就不再赘述了。

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

操作步骤：

① 打开“数据”选项卡-点击“新建查询”下拉菜单中的“从文件”-“从工作簿”。

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

③ 在“导航器”窗口中，选中工作簿“目录”，并点击“转换数据”。

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

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

优点：

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

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

缺点：

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

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

三、CTRL+F生成目录

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

操作步骤：

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

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

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

优点：

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

② 可以实现双向跳转。

缺点：

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

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

＊＊＊＊＊＊＊＊＊

说了这么多，小伙伴们估计有疑问了？如何既能在不占用多余页面空间的基础上，实现双向跳转，又能让目录实现自动更新呢？别急，下篇教程就给大家揭晓答案！

＊＊＊＊部落窝教育－excel目录制作技巧＊＊＊＊

原创：壹仟伍佰万/部落窝教育（未经同意，请勿转载）

更多教程：部落窝教育（http://www.itblw.com）

微信公众号：exceljiaocheng