要进入某个工作表,需要找到这个工作表并单击工作表名称。当工作簿中有太多的工作表时,查找工作表相当费时,因为Excel的工作表名称不支持搜索功能。

是否可以新建一个名为“目录”的工作表,然后在此表中创建所有工作表的目录呢?

解题步骤

Excel的一切工作表函数都无法生成活动工作簿中的所有工作表名称,而比较古老的宏表函数GET.WORKBOOK却可以,因此本例借用GET.WORKBOOK实现需求,具体操作步骤如下。

1.打开需要创建目录的工作簿,然后新建一个工作表,且将该表移到最前面,并命名为“目录”,此时工作表分布状态如图7.120所示。

Excel 如何设计工作表目录?-Excel22

图7.120 新建“目录”工作表

2.选择A1单元格,按下组合键<Ctrl+Shift+Enter>,打开“名称管理器”,然后单击“新建”按钮,弹出“新建名称”对话框。

3.将名称设置为“第N个工作表”,将引用位置设置为以下公式:

Excel 如何设计工作表目录?-Excel22

设置界面如图7.121所示。

Excel 如何设计工作表目录?-Excel22

图7.121 创建名称

4.单击“确定”按钮保存设置,然后返回工作表界面。

5.在A1单元格输入以下公式:

Excel 如何设计工作表目录?-Excel22

6.将公式向下填充,有多少个工作表就填充多少个单元格,填充后会产生活动工作簿中的所有工作表目录,效果如图7.122所示。

Excel 如何设计工作表目录?-Excel22

图7.122 工作表目录

7.单击A6单元格,Excel会马上激活“财务表”,且选中“财务表”中的A1单元格。

8.返回“目录”工作表,单击A7单元格,Excel会马上激活“出货表”,且选中“出货表”中的A1单元格。

知识扩展

1.宏表函数GET.WORKBOOK的功能是获取与工作簿相关的约一百项信息,当参数的值为1时表示获取所有工作表的名称。

由于表达式“GET.WORKBOOK(1)”可以生成一组名称,包含所有工作表,因此要取得单个名称就需要借用INDEX函数。

本例中的公式“=INDEX(GET.WORKBOOK(1),ROW(A1))”在向下填充时,ROW的参数A1会变成A2、A3……ROW(A1)也因此会生成1、2、3这种序号,所以整个公式的结果就是第一个工作表名称、第二个工作表名称、第三个工作表名称……当填充到超过工作表数量时会产生错误值。

2.宏表函数不能直接写在单元格中,只能在名称中调用宏表函数,然后在单元格中调用名称。

3.当工作簿中使用了宏表函数后,此工作簿不能保存为xlsx格式。

4.HYPERLINK 是链接函数,用于打开指定的文件、网站或激活指定的单元格,本例中用它激活每个工作表的A1单元格,从而实现目录功能。HYPERLINK函数的语法如下:

HYPERLINK(link_location,friendly_name)

第一参数代表链接对象,必须遵循严格的命名方式;第二参数代表要显示的字符,可以随意指定。本例中名称“第N个工作表”产生的结果是“[疑难170.xlsm]生产表”这种形式的,不符合单元格引用规则,因此需要在后方添加叹号和单元格地址。

5.目录中显示了工作簿名称,如果只需要工作表名称,那么可以使用FIND函数查找“]”的位置,然后用MID函数提取该位置右边的所有字符,具体的公式如下:

=HYPERLINK(第N个工作表&"!a1",MID(第N个工作表,FIND("]",第N个工作表)+1,99))此公式生成的目录效果如图7.123所示。

Excel 如何设计工作表目录?-Excel22

图7.123 不包含工作簿名称的目录