用hyperlink制作会跳转的工作表目录
你好,我是刘卓。欢迎来到我的公号,excel函数解析。昨天咱们学了hyperlink的用法,今天就用它来制作会跳转的工作表目录。
想要制作这个目录,首先要把工作簿下的所有工作表名提取出来,然后用hyperlink链接到它们各自的表中。
提取所有工作表名用的是get.workbook这个宏表函数。你还记得它的用法吗?点击《表名再多也不怕!一条公式提取所有工作表名》可以回顾。
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())
这个名称bm返回的就是当前工作簿下的所有工作表名,是一个数组。
下面再对上面的公式简单说明一下:
get.workbook(1)返回了当前工作簿下的所有工作表名,但是表名前面有工作簿名。所以用mid截取出工作表名,截取的标志就是右中括号"]"。从右中括号的后面一位开始截取,截取的长度是99。
find就是找右中括号的位置,加1就是从它后面的一位开始截取。
t(now())返回的结果是空文本"",连接它主要是为了公式的更新,而又不影响最后的结果。now()返回系统的当前日期时间,是个易失性函数,因为它的结果一直是变化的。
第二步,将上一步的工作表名一一提取出来,公式为:
=INDEX(bm,ROW(A1))
=HYPERLINK("#'"&INDEX(bm,ROW(A1))&"'!a1",INDEX(bm,ROW(A1)))
还记得昨天的文章吗,hyperlink第1参数的格式为"#工作表名!单元格",上面的公式中工作表名用一对单引号包裹,可以适用于所有的表名。
第2参数的跳转文本还是它的工作表名,只需把index那一段公式复制一下就可以了。最外层套个iferror来处理下错误值就ok了。
最后还要从其他工作表中返回目录表,这个公式就可以批量创建。选中第2个工作表,按住shift,选中最后一个工作表,选择一个要跳转的单元格,输入公式=HYPERLINK("#目录!a1","返回目录")。
不要忘了将这个工作簿保存为启用宏的工作簿,后缀为.xlsm。
https://pan.baidu.com/s/1geGZO9WZlf26ba320KTeqg