EXCEL技巧-工作表名称目录管理应用详解

如下为动态图演示效果：

①单击列表中【工作表名称】跳转至对应的工作表界面；

②在工作簿中【新增】/【删除】或【修改工作名称】后，按F9键后工作表列表名称会自动更新；

③在任意工作表中，从地址栏单击自定义名称【返回】，即可跳转至【目录表】工作表界面；

按照分步解题思考，拆解逐步实现？

第一步：获取动态工作表名称

①自定义名称

使用GET.WORKBOOK宏表函数自定义【目录】区域名称，首先选择功能区【公式】选项卡，接着在【定义的名称】分组中单击【名称管理器】，弹出【名称管理器】工作界面，单击【新建】按钮。

弹出【新建名称】工作界面窗口，在名称编辑编辑栏输入目录自定义的名称，可以根据需要随意取名；引用位置编辑栏输入公式 =GET.WORKBOOK(1)&T(NOW()) 单击*确认*按钮。

公式解析：

GET.WORKBOOK(1) 代公式部分，获取当前活动工作簿所有工作表名称；

GET.WORKBOOK应用详见

————

连接T(NOW() 公式部分，可以实现自动更新功能（T函数的作用是检测数据是否为文本，返回原样或空，而now函数结果为一个数值，所以T函数返回空值，不影响GET.WORKBOOK获取的工作表名称）。

②提取工作表名称

选中C3单元格输入公式 =MID(INDEX(目录,ROW(1:1)),FIND("]",INDEX(目录,ROW(1:1)))+1,99) ，下拉填充公式，获取当前活动工作簿中的所有工作表名称，此时文件夹中新增/删除或更改文件名称后，按F9键列表中的文件名称会自动更新。注意：公式的【目录】为自定义的区域名称。

公式解析：

INDEX(目录,ROW(1:1)) 公式部分，获取工作簿中第一工作表名称【目录表】，当公式下拉填充时，依次获取工作簿中第2、3、4...工作表名称。

FIND("]",INDEX(目录,ROW(1:1)))+1 公式部分，查询工作表第一个字符的位置【31】。

最后使用MID函数MID(INDEX(目录,ROW(1:1)),FIND("]",INDEX(目录,ROW(1:1)))+1,99) ，提取名称字符串中第31个字段到第99个字符的内容【目录表】。

第二步：超链接目录文件

在C3单元格原公式中嵌套HYPERLINK函数公式 =HYPERLINK("#"&MID(INDEX(目录,ROW(1:1)),FIND("]",INDEX(目录,ROW(1:1)))+1,99)&"!A1",MID(INDEX(目录,ROW(1:1)),FIND("]",INDEX(目录,ROW(1:1)))+1,99)) ，完成所有工作表名称的超链接设置。

公式解析：

HYPERLINK函数表达式，HYPERLINK(Link_location,[Friendly_name])，Link_location 为链接位置；Friendly_name显示内容。HYPERLINK链接同一工作簿不同工作表是在工作表名称前加“#”。

HYPERLINK应用详见——HYPERLINK函数的应用技巧——

第三步：屏蔽错误值

由于要实现工作簿中新增工作表后，按F9键后能自动获取到新增工作表名称，需要将HYPERLINK函数公式下拉填充至大于现有工作簿中工作表数量，此时会导致产生错误值。

解决的办法就是在原HYPERLINK函数公式外嵌套一个IFERROR函数 =IFERROR(HYPERLINK("#"&MID(INDEX(目录,ROW(1:1)),FIND("]",INDEX(目录,ROW(1:1)))+1,99)&"!A1",MID(INDEX(目录,ROW(1:1)),FIND("]",INDEX(目录,ROW(1:1)))+1,99)),"") ，当结果Wie错误值，返回空。