在Excel中设置目录页

当Excel文件有很多工作表时，我们可以设置一个目录页方便查找和跳转。下面我们来看看怎样设置目录页。

1、添加自定义名称

在“公式”菜单中找到“名称管理器”，点“新建”按钮，按照下图所示，名称输入“ws”，引用位置框中输入公式：

=get.workbook(1)&t(now())

注意：定义了这个名称，excel需要保存为启用宏的工作簿，即工作表名称的后缀是.xlsm。

2、在目录工作表中的单元格中输入以下公式，并下拉填充。

=IFERROR(HYPERLINK("#'"&MID(INDEX(ws,ROW(A1)),FIND("]",INDEX(ws,ROW(A1)))+1,100)&"'!A1",MID(INDEX(ws,ROW(A1)),FIND("]",INDEX(ws,ROW(A1)))+1,100)),"")

这样目录页就设置好了。当增加新的工作表时，只要复制公式继续向下填充到单元格中，就会增加上相应的链接。

3、给每个页面增加返回目录页的链接

在表中插入一个矩形，点右键，超链接，如下图所示，在左边选择“在本文档中的位置”，在右面选择目录页的名称，输入要链接到的单元格地址，默认是A1单元格，点确定就可以设置链接。然后把这个形状复制到所有工作表中，就可以设置返回到目录页的链接。

 

下面解释一下在第二步中的公式

=IFERROR(HYPERLINK("#'"&MID(INDEX(ws,ROW(A1)),FIND("]",INDEX(ws,ROW(A1)))+1,100)&"'!A1",MID(INDEX(ws,ROW(A1)),FIND("]",INDEX(ws,ROW(A1)))+1,100)),"")

1、最外层的IFERROR就是当已列出所有工作表时就返回空值。

2、MID(INDEX(ws,ROW(A1)),FIND("]",INDEX(ws,ROW(A1)))+1,100) 中的

Ws是获取所有工作表名称的一个集合；

INDEX(ws,ROW(A1))是取出单个的完整工作表名称，比如：[20160903 设置目录页.xlsm]Sheet1

MID(INDEX(ws,ROW(A1)),FIND("]",INDEX(ws,ROW(A1)))+1,100)是单独分离出工作表的名称，比如：Sheet1。原理是在完整工作表名称中，从“]”符号之后开始截取字符串，因为工作表名称限制，所以为了简便起见，最后一个参数就写100。

3、用hyperlink函数来建立链接。 &"'!A1" 中的A1表示链接到目标工作表的A1单元格，也可以改成自己想要定位到的单元格地址。

如果大家不想费脑筋去理解，就直接套用好了。

--End--