excel函数法+非函数法建工作目录

2020年8月6日 分类:Excel 作者:企业网盘

一个工作薄里会有很多工作表,翻看很麻烦。为了方便查找,需要给若干个工作表建立目录。

第一种方法:函数法

如下图所示工作表的目录:

目录

【方法实现】

第一步:定义名称

鼠标放在A1单元格,选择【公式】菜单中【定义名称】,在新建名称对话框中输入名称“目录”,引用位置输入公式:

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

如下图所示:

自定义名称

第二步:输入公式,建立目录

在A1单元格输入公式:

=IFERROR(HYPERLINK(目录&”!A1″,MID(目录,FIND(“]”,目录)+1,99)),””)

公式向下填充,即得所有工作表的目录。

如下图所示:

目录公式

第三步:返回目录公式

如果想随时返回目录工作表,可以在其他工作表添加“返回目录”按钮。

在目录工作表以外的表中合适位置,输入公式:

=HYPERLINK(“#目录!A1″,”返回目录”),即可得到返回“目录”的链接,如下图所示:

返回目录公式

【公式解析】

1、名称公式

=INDEX(GET.WORKBOOK(1),ROW(目录!A1))&T(NOW()):

GET.WORKBOOK(1)用于提取当前工作簿中所有工作表名称。

INDEX函数则按ROW(A1)返回的数字决定要显示第几张工作表的名称。

宏表函数GET.WORKBOOK(1)在数据变动时不会自动重算,而NOW()是易失性函数,因此在公式中加上NOW()函数让公式自动重算。

函数T()则是将NOW()产生的数值转为空文本,也就是相当于在工作表名称后加上&””。

2、目录公式

=IFERROR(HYPERLINK(目录&”!A1″,MID(目录,FIND(“]”,目录)+1,99)),””):

FIND(“]”,目录):用于查找符号”]”在自定义名称“目录”计算结果中的位置。

MID(目录,FIND(“]”,目录)+1,99):从“目录”中的”]”符号后一个字符处取值,取值长度为比较大的字符,这里设置99,也可自行设置其他长度。

HYPERLINK函数:是EXCEL超级链接的函数实现方法。当单击函数HYPERLINK所在的单元格时,Excel将打开链接的文件或跳转到指定的工作表的单元格。

IFERROR函数:用于屏蔽错误。

3、返回目录

=HYPERLINK(“#目录!A1″,”返回目录”):

HYPERLINK(link_location,[friendly_name])

参数:

Link_location必需。可以作为文本打开的文档的路径和文件名。

Friendly_name可选。单元格中显示的跳转文本或数字值。Friendly_name显示为蓝色并带有下划线。如果省略Friendly_name,单元格会将link_location显示为跳转文本。Friendly_name可以为数值、文本字符串、名称或包含跳转文本或数值的单元格。

*特别注意:

因为引用了宏表函数,所以文件保存时要保存成“启用宏的工作簿.xlsm”。

第二种方法:非函数法

第一步:选定所有工作表

在工作表标签上面,点击右键,选择“选定全部工作表”,如下图所示:

选定所有工作表

第二步:输入公式

此时,所有工作表处于选定状态,在A1单元格输入:=XFD1,确定,此函数返回值为0,如下图 1‑168所示:

输入简单的公式

Excel2003及之前的版本文件有256(2的8次方)列,即到IV列;Excel2007及以后的版本有16384(2的14次方)列,即到XFD列。这里引用XFD1单元格的数值,为0。

第三步:自动生成“兼容性报表”

点击【文件】菜单,【信息】——【检查问题】——【检查兼容性】,如下图所示:

打开兼容性检查器

在【兼容性检查器】中选择【复制到新表】,如下图 所示:

复制到新表

自动添加一个工作表,名为“兼容性报表”,报表中自动生成目录,如下图 所示:

生成兼容性报表

第四步:复制目录到“目录”工作表

复制“兼容性报表”中的目录到“目录”工作表,如下图所示:

复制兼容性报表中的目录

即成目录。

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
继续阅读