excel怎么自动生成目录

讲真，每次看到有人打开一个塞满了二三十个，甚至上百个工作表的Excel文件，然后用鼠标在那小小的标签栏上疯狂左右拖拽，试图找到那个叫“第三季度华南区销售复盘”的Sheet，我就觉得一阵心累。那场面，简直就像在没有搜索引擎的年代，试图从一座图书馆里徒手找一本具体的书。太原始了，真的。

你是不是也这么干过？或者更“高级”一点，专门新建一个名叫“目录”或者“索引”的工作表，然后一个一个地把其他工作表的名称手动敲进去，再右键，点击“链接”，在弹出的那个该死的对话框里，选择“本文档中的位置”，再找到对应的工作表，点击确定。

恭喜你，你完成了一个链接。然后重复这个动作三十遍。

当你为自己的耐心和细致感到骄傲时，你的老板过来说：“那个‘华南区’的表述不准确，改成‘大湾区’吧。”

于是，你回到那个工作表，把标签名改了。然后呢？你的“目录”上那个链接，那个你亲手敲上去的名字，它还叫“华南区”。它不会动，它就像个纪念碑，纪念着你刚刚浪费掉的生命。更糟的是，那个链接现在点过去，Excel会给你一个冰冷的提示：“引用无效”。

崩溃不？

所以，别再用那种石器时代的方法了。今天我们来聊聊，怎么让Excel自己、自动地生成一个“活”的目录。你增加、删除、重命名任何工作表，这个目录都能像有生命一样，自己更新。这感觉，才叫掌控。

核心武器：一个被遗忘的远古神兽——GET.WORKBOOK

要实现这个魔法，我们需要请出一个很多人闻所未闻的函数：GET.WORKBOOK。

这玩意儿你别想在单元格里直接输入=然后敲GET...找到它。找不到的。它是一个宏表函数，属于Excel的上古神兽，诞生于VBA还没那么普及的年代。它威力巨大，但被微软雪藏了起来，只能在一个特殊的地方召唤它——名称管理器。

这个名字听起来就很酷，对吧？它就像一个存放着各种咒语和秘籍的地方。

来，我们开始召唤。

第一步：定义“咒语”

1. 点击Excel顶部菜单栏的 “公式” 选项卡。
2. 找到中间那个像名牌一样的图标，叫 “名称管理器”，点它。
3. 在弹出的窗口里，点击左上角的 “新建”。

现在，就是关键时刻了。会弹出一个“新建名称”的对话框，这里需要我们填入两个东西：

• 名称： 给你的这套咒语起个名字。简单点，就叫 SheetNames 吧，好记。
• 引用位置： 这是最核心的部分。把里面默认的内容删干净，然后，像念咒一样，一字不差地输入以下这串代码： =GET.WORKBOOK(1)&T(NOW())

我来解释一下这串咒语的意思：

GET.WORKBOOK(1) 就是核心，它的作用是提取当前工作簿里所有工作表的名称，并把它们以一个数组的形式打包起来。

&T(NOW()) 是个小小的黑科技。NOW()会返回当前的日期和时间，它是个易失性函数，意味着任何操作都可能让它重新计算。T()函数的作用是，如果NOW()的结果是文本，就返回文本，如果是数字（时间在Excel里就是数字），就返回空。所以T(NOW())总是返回空。那么把它连在后面干嘛？就是为了“骗”Excel。我们利用NOW()的易变特性，等于给GET.WORKBOOK这个老古董装上了一个雷达，只要工作簿里有任何风吹草动（比如工作表名称变了），NOW()就会刷新，从而强制让GET.WORKBOOK也跟着重新获取一遍最新的工作表列表。

妙不妙？

写完之后，点击“确定”，关闭名称管理器。好了，咒语已经埋下了。虽然表面上什么都没发生，但你的Excel文件已经不一样了。

第二步：释放力量，让目录显形

现在，回到你那个准备当目录的工作表。假设你希望从A2单元格开始，往下依次列出所有的工作表名称。

在A2单元格里，输入这个公式：

=INDEX(SheetNames, ROW(A1))

是不是有点懵？别急，我们拆开看。

ROW(A1) 的结果是1。当你把公式往下拖动时，A1会变成A2、A3... ROW(A2)就是2，ROW(A3)就是3，以此类推。所以，ROW(A1)在这里的作用，就是一个自动增长的序号。

SheetNames 是什么？就是我们刚才在名称管理器里定义的那个咒语！它现在代表着一个包含了所有工作表名称的列表。

INDEX 函数的作用就是从一个列表（数组）里，根据你给定的序号，取出对应位置的那个值。

所以，INDEX(SheetNames, ROW(A1)) 的意思就是：从SheetNames这个列表里，取出第1个工作表的名字。 把它放到A2，往下拖拽，A3的公式就变成了INDEX(SheetNames, ROW(A2))，意思就是：从SheetNames这个列表里，取出第2个工作表的名字。

看到了吗？你往下轻轻一拉，所有工作表的名称，就都乖乖地、按顺序地排列出来了。

现在你去试试，随便找个工作表，改个名字。再回到目录页，看，A列的那个名字是不是瞬间就自己变了？你再新建一个工作表，回到目录页，往下多拉一个单元格的公式，新表的名字是不是就出来了？

这才是自动化！

终极进化：加上灵魂——HYPERLINK

光有名字还不够，我们要的是能点的目录，对吧？所以，最后一步，就是把这些名字变成可以一键跳转的超链接。

这就需要另一个函数登场：HYPERLINK。

它的语法是 HYPERLINK(链接地址, 显示的文本)。

我们已经有了“显示的文本”，就是刚才用INDEX取出来的表名。现在的问题是，“链接地址”该怎么写？

在Excel内部，指向某个工作表某个单元格（比如A1）的链接地址，格式是这样的：#'工作表名'!A1。注意，那个单引号是当你的工作表名字里有空格或者特殊字符时必须加的，为了保险起见，我们都加上。

所以，我们需要把刚才用INDEX取出来的名字，拼接到这个固定的格式里去。

选中A2单元格，把原来的公式大改造，变成下面这个样子：

=HYPERLINK("#'"&INDEX(SheetNames,ROW(A1))&"'!A1", INDEX(SheetNames,ROW(A1)))

这个公式看起来吓人，但其实就是个“三明治”。

• 最外层是 HYPERLINK(..., ...)。
• 第一个参数，也就是链接地址，是"#'"（这是文本，要加双引号） & INDEX(SheetNames,ROW(A1))（我们取到的动态表名） & "'!A1"（又是固定格式的文本）。用&符号把这三段粘合在一起，就构成了一个完整的、动态的内部链接。
• 第二个参数，也就是显示出来的文本，还是我们熟悉的老朋友 INDEX(SheetNames,ROW(A1))。

回车。然后把这个公式往下拖拽。

奇迹发生了。

A列现在不仅显示了所有工作表的名称，它们还都变成了蓝色的、带下划线的超链接。随便点一个试试，是不是“嗖”地一下就飞到了对应的工作表？

现在，再去改个工作表的名字，比如把“Sheet5”改成“年中总结报告”。回到目录页，你会发现，链接的显示文本自动变成了“年中总结报告”，而且你点一下，它依然能准确无误地跳过去。

这，才叫真正的“一劳永逸”。

一点小优化

你可能会发现，这个目录把自己（“目录”这个工作表）也给包含进去了。如果你不想要它，也很简单。用一个IF函数来过滤掉就行。比如，你的目录表就叫“目录”，那你可以把公式改成：

=IF(INDEX(SheetNames,ROW(A1))="目录", "", HYPERLINK("#'"&INDEX(SheetNames,ROW(A1))&"'!A1", INDEX(SheetNames,ROW(A1))))

当然，如果你的工作表数量是不固定的，往下拖公式可能会出现#REF!错误，因为后面的工作表不存在了。最简单的处理方法，套一个IFERROR函数就好了：

=IFERROR(HYPERLINK("#'"&INDEX(SheetNames,ROW(A1))&"'!A1", INDEX(SheetNames,ROW(A1))), "")

这样，当公式找不到对应的工作表时，就不会显示烦人的错误值，而是显示一个干净的空白。

从今往后，别再手动做目录了。花五分钟设置好这个“自动化目录生成器”，你节省下来的，可能是未来几年里无数个点击、修改、核对的十分钟。这不仅仅是一个技巧，这是一种工作思维的升级——从被动地做重复劳动，到主动地构建一个能自我维护的系统。

去试试吧，感受一下把那个曾经让你抓狂的庞然大物，驯服得服服帖帖的快感。