＊＊＊＊部落窝教育－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次复制粘贴

优点：

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

② 目录能自动更新。

缺点：

比较难懂