大家好,我是讯享网,大家多多关注。
制作可自动更新的工作表目录的最简单方法是使用PowerQuery获取工作表名称。但是很多粉丝反映自己的Excel版本不支持,无法使用。今天,我将与你分享另一种解决方案。不限制Excel版本,但是操作起来有点麻烦,也可以自动更新。让我们一起做吧。
想从零开始学Excel,这里↑↑ⅳ
一、所需函数
1.获取.工作簿
Get.workbook:从工作簿中提取信息。
语法:=GET。工作簿(信息类型,名称)
第二个参数是可选参数。一般省略就是获取当前工作簿的信息。
Get.workbook是一个宏表函数,需要和index+定义名配合使用。这里只需要记住将名称定义为:=Get.workbook(1)&T(RAND())就可以得到工作簿名称和工作表名称。
2.超链接
超链接:创建超链接
语法:=超链接(连接地址,显示名称)
这是我们需要使用的两个函数。制作过程是使用Get.workbook动态获取工作表名称,然后使用超链接函数创建超链接。
二、获取工作表名称
1.定义名称
我们需要点击【公式】功能组找到【定义名称】,会弹出定义名称的窗口。这里我们将名称设置为【XX】,然后在底部设置公式:=Get.workbook(1)&T(RAND()),最后点击确定。
2.得到名字
单击下方的A1单元格,然后将公式设置为:= if error (index (xx,row (a1)),& # 34;”),然后向下填充得到工作簿名称和工作表名称。如果你想添加一个新的目录来自动更新,我们需要拉更多的这个公式。如果工作表的数量大于公式的数量,则不能自动显示新添加的工作表。
3.获取工作表名称
现在我们得到的名称是工作簿名称+工作表名称。在这里,我们需要的只是工作表名称,提取方法有很多种。这里我们用len+right th函数来提取。
只需将公式设置为:= if error (right (a1,len (a1)-9),& # 34;”),然后再往下填。公式中的9其实就是这部分【三月】的字符数。xlsm】。可以根据自己的实际情况设置,可以使用len函数快速计算字符数。
三、制作超链接
得到工作表名称后,就可以开始制作超链接了。要制作超链接,我们需要使用hyperlink函数,只需将函数设置为:= HYPERLINK(& # 34;#”& B1 & & # 34!a1 & # 34B1)然后往下填。
第一个参数:& # 34;#”& B1 & & # 34!a1 & # 34它代表跳转位置,是每个工作表中的A1单元格。#号不能小于,它表示当前工作簿。
第二个参数:B1,是工作表的名称和函数显示的结果。
四、制作返回目录
我们可以使用地址栏制作返回目录。它的本质其实就是定义名字。您需要单击A1单元格,然后在地址栏中输入单词[return],然后单击确定。在这种情况下,点击工作表名称会跳转,然后可以点击地址栏中的Return返回目录。最后,我们将隐藏额外的两列,设置显示格式,并添加网格线。至此,制作完成。
五、注意事项
1.格式问题
因为这里我们用的是宏表函数,它的本质其实是vba。如果我们想保存这些函数,我们需要将文件设置为可以保存VBA代码的格式。常见的格式是xlsm格式,我们只需要保存Excel,然后在【保存类型】中选择xlsm格式即可
2.设置问题
如果关闭目录后突然不显示了,而且宏功能也启用了,可能是没有启用宏表功能造成的。我们需要在Excel中设置它。
您需要打开Excel选项,然后单击信用中心,选择信任中心设置,查找宏设置,并在启用VBA时选中Excel4.0宏,这样就不会显示为错误值。
这就是我们今天分享的全部内容。怎么样?你学会了吗?
我从零到一都很优秀。关注我,持续分享更多Excel技巧。
本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://51itzy.com/42548.html