你会做 Excel目录吗？它简直是一部Excel函数百科全书

2022-04-13 Excel目录

在Excel表格插入工作表目录，是一个老生常谈的技巧。但大数多用户只会套用，并不懂其中的原理，毕竟制作过程太过复杂。所以，离开了教程也做不出来了。

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
为了让大家可以随时随地做自已做目录，今天兰色就剖析一下制作Excel目录的过程。
制作过程：
1、公式 - 定义名称：shname
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW)

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
乖乖，好多陌生的函数....新手看了这个公式估计要晕掉。别急，兰色一步步帮大家分析。
在Excel中有一类函数叫宏表函数，功能非常强大，可以提取Excel或电脑的信息，比如提取单元格颜色，提取文件目录。今天要用到的是一个可以提取所有工作表名称的函数： Get.Workbook
由于宏表函数只能在定义名称中使用，所以必须先定义名称：
公式 - 定义名称 - 输入自定义的名称 - 在引用位置输入公式:
=get.workbook(1)
注: get.workbook的参数是 1时，可以提取所有工作表名称

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
定义的名称可以在单元格公式中直接使用，比如输入=Shname即可返回所有工作表名称。（选中公式按F9可以查看所有值）

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
由于返回的工作表名称前含工作簿名称“ [抖音Excel技巧集.xlsm]”，所以下一步把用函数它删除：
用 Find函数查找"]'的位置，然后用 MID函数截取。
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
怎么把工作表名称显示到一列中？
可以用 Index函数+ row函数提取：index可以根据位置提取数据，Row函数可以在向下复制时生成1，2，3，4...序数
=INDEX(shname,ROW(A1))

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
名称有了，下一步是给工作表名称添加链接，这一步要用
=Hyperlink（#工作表名称!单元格地址,工作表名称）
即：
=HYPERLINK("#"&INDEX(shname,ROW(A1))&"!a1",INDEX(shname,ROW(A1)))

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
当公式超出工作表个数时，再复制公式会返回错误值，所以需要再外套 IFerror函数。
=IFERROR(HYPERLINK("#"&INDEX(shname,ROW(A1))&"!a1",INDEX(shname,ROW(A1))),"")

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
好像很完美了? No...当你修改、删除工作表时，目录并不会自动更新：

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
问题出在哪？原来我们在定义名称时少了两个函数： T和 Now

Now 函数可以生成自动更新的时间，可以让公式强制刷新
T 函数则可以把数字（时间也是数字）转换为空白

所以 T(Now)即可以让公式强制刷新（双击或其他单元格内容更新），又不影响单元格的值。
接下来修改定义的名称：

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
至此，目录的公式设置完成。无论添加、删除工作表或修改工作表名称，双击任一个单元格或任一单元格内容发生修改，目录都会自动更新。

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
完成了吗？No! 我们还少最后一步，把工作簿另存为启用宏的工作簿。

你会做 Excel目录吗？它简直是一部Excel函数百科全书

文章插图
兰色说：盘点了一下，制作目录共用了两大类9个函数，对新手真的有点难度，所以要想随时随地制作目录，你还真的需要理解这些函数的用法。

【你会做 Excel目录吗？它简直是一部Excel函数百科全书】

推荐阅读

上一篇：古树茶花的功效与作用,墨旱莲的功效与作用

下一篇：红枣玫瑰洛神花喝多了,要注意什么喝花茶