1、目录分类

当前excel可添加的目录分为两种：单向跳转和双向跳转：

- 单向跳转：单击目录可跳转到相应工作表，但无法跳转回目录，工作表之间切换不方便

- 双向跳转：单击目录，跳转到工作表，双击工作表单元格，跳转到目录

2、表边目录制作-宏表函数（可自动更新）

**注意1：使用该方法时，需将表格形式改为适用宏的xlsm保存**

**注意2：get.workbook 是宏表函数，只能在定义名称中使用。**

- 1-公式 - 定义名称 - 输入自定义的名称：shname，在引用位置中输入公式：

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())

- 2-在第1个工作表的A列一单元格中输入公式并向下复制

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

- 3-复制已设置公式的工作表A列，选取后面所有工作表粘贴到A列，就可以把公式粘贴到后面所有工作表的A列

- 4-全选所有工作表，调整列宽、设置背景色字体颜色

表边目录制作-宏表函数

3、目录表制作-宏表函数（可自动更新）

**注意1：使用该方法时，需将表格形式改为适用宏的xlsm保存**

**注意2：定义的公式名称是唯一的，如需更换内容，可使用公式-定义的名称-名称管理器更改**

- 1-单击B1单元格，切换到【公式】选项卡，单击【定义名称】，弹出【新建名称】对话框，在“名称”文本框中输入“目录”，在“引用位置”文本框输入以下公式：

=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())

- 2-单元格输入公式，并向下复制：

=IFERROR(HYPERLINK("#'"&MID(目录,FIND("]",目录)+1,99)&"'!A1",MID(目录,FIND("]",目录)+1,99)),"")

- 3-制作“返回目录”超链接：

在任意工作表的空白单元格中输入以下公式，然后复制该单元格，粘贴到其他工作表中。

=HYPERLINK("#目录!A1","返回目录")

目录表制作-宏表函数

4、制作公式详解

4-1 GET.WORKBOOK-提取工作表信息

**该函数为宏表函数，现在宏表函数已经被VBA替代，但我们将宏表函数在 “公式-定义名称” 中自定义后仍可以使用。**

**函数语法为GET.WORKBOOK(type_num,name_text)**

- type_num：提取信息的编号类型

  - 常用编号“1”：获取名称

  - 常用编号“4”：获取工作表数量

- name_txt：打开的工作表名称，省略则为当前活动工作表

如果使用=get.workbook(1)来获取工作表名称，名称前会加上xls的文件名（例如 [测试用例-模板.xlsm]目录），这对目录制作没有益处，所以需要MID函数进行裁剪。

4-2 MID-获取单元格部分数据

**该函数语法：MID(text,start_num,Num_chars)**

- text：需要提取的文本

- start_num：提取起始位置

- Num_chars：提取的字符长度

**实例解析**

在MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)这个公式中

1）要截取的文本

GET.WORKBOOK(1)，即工作表的名称

2）截取起始位置

该处套用了函数FIND，下面为该部分详解：

- FIND函数的语法为FIND(find_text,within_text,[start_num])

  - find_text：要查找的文本

  - within_text：要查找文本所在的文本

  - start_num：查找开始位置（可不填，前不需加逗号

- Find函数将全角字符和半角字符都计为一个字符，在FIND("]",GET.WORKBOOK(1))中，我们将“]”作为要查找的文本，GET.WORKBOOK(1)取出的工作表名称作为查找文本所在的文本，这样的函数会返回获得的工作表名称中“]”所在的位置。

- 截取起始位置的完整公式为FIND("]",GET.WORKBOOK(1))+1，我们已知FIND公式部分返回了“]”所在位置，而之后的“+1”，则是让起始位置指向了“]”之后的工作表名称，即去除了xls文件名的工作表名称

3）提取长度

这里为了提取名称不遗漏，选择了99作为提取长度。

4-3 T(NOW())-自动刷新

now()本身没有参数，直接输入会返回当前日期和时间（例如：2020/4/10 13:40

）

T()主要的作用试讲文本型数据保留文本，其他数据返回为空.(另，N()函数是将数值型数据保留文本，文本数据返回0)

因为NOW()是易失性函数，其值会随时刷新。我们用T函数去处理他返回的日期型数值，结果是空，不会产生文本资料，但这样使用可以起到自动刷新的效果。

4-4 HYPERLINK-超链接

**HYPERLINK函数的语法为：=HYPERLINK(link_location,[friendly_name])**

- link_location：目标位置-可采用绝对路径或相对路径（以当前工作簿所在文件夹为起始位置）

  - excel单元格：前面需加“#”

例：=HYPERLINK("Sheet!A1","Sheet1")

  - 文档路径

例： =HYPERLINK("C:\dacheng\work\测试文档\1.txt","test")

  - 互联网网址

例： =HYPERLINK("http://cn.bing.com/","bing!")

- friendly_name：显示文本，可不填

**实例解析**

HYPERLINK("#"&INDEX(shname,ROW(A1))&"!A1",INDEX(shname,ROW(A1)))

**在上述公式中，HYPERLINK的文件位置是多个字符使用"&"拼接而成的：**

- "#"：主要作用是将目标位置限定为单元格

- INDEX(shname,ROW(A1))

之前我们设定过自定义公式，shname设定为=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())，即自动更新的简化版工作表名称。**根据INDEX公式的语法，该部分是在所有工作表名称中，选取第一行信息展示，展示结果是第一张表的名称，如需将表的全部展示，可以向下拖拽公式进行填充。**（*如果需要将目录横向展示，可以将ROW(A1)改为COLUMN(A1)，之后向右拖拽公式进行填充*）

- "!A1"

这里有疑问可能是纠结“！”的用法，我们可大体了解下，感叹号在公式中主要用在工作表和单元格之间，这里出现在工作表名称之后，A1之前，总体表示为当前工作表名称对应表的A1单元格。

**这样-"#"&INDEX(shname,ROW(A1))&"!A1"，最终给出的就是工作表名称对应表的A1单元格的位置**

**逗号之后是超链接所在单元格的自定义名称，一般我们使用INDEX(shname,ROW(A1))，即链接对应的工作表名称，当然也可以自定义其他名称。**

4-5 IFERROR-捕获错误

**IFERROR函数的语法为：IFERROR(value,value_if_error)**

- value：取值，从中检查是否存在错误

- value_if_error：公式计算错误时返回的值，计算错误类型：#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？或 #NULL！

**实例解析**

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

之前已经解析了HYPERLINK的部分，得到的是工作表名称超链接到对应工作表的A1单元格，IFERROR的作用则是在该公式计算出错时，返回一个空字符串""

**上述两种目录制作方式的公式，仅在函数嵌套前后顺序上有所不同，其他地方无较大差别，可统一考虑，因此不作赘余**

5、常见问题

5-1表名中的特殊字符导致引用失效

当工作表名称中出现特殊字符（如+、-等）的时候，公式=IFERROR(HYPERLINK("#"&INDEX(shname,ROW(A1))&"!A1",INDEX(shname,ROW(A1))),"")使用时可能会提示引用失效，这时我们可以将INDEX获取的工作表名称构成为字符串进行处理，也就是在原本&左右的“旁再加上一个单引号，变为=IFERROR(HYPERLINK("#'"&INDEX(shname,ROW(A1))&"'!A1",INDEX(shname,ROW(A1))),"")

当然这只是针对方法一中的公式，方法二中已经将所有表名当做字符串处理，便可不用考虑该问题出现。

文档中目录制作公式来源网络，图片均为本人截取，如有侵权请告知