wps怎么自动生成目录(wps如何快速建立超链接目录)

昨天在只需要两步就可以在WPS表格里插入超链接目录的评论区,有个网友提问,如果有几百个几千个文件,怎么快速建立超链接的目录。

现在把详细的教程整理了下,发在这里。

这里我创建了一个excel表格,表格名字叫a.xlsx,位置在:

C:\Users\Administrator\Desktop。

然后在这个工作簿里创建了7个sheet,6个普通表格,还有一个就是目录。我们要在目录这个sheet里创建带超链接的目录。下面就来分享步骤:

一、用GET.WORKBOOK和index获取sheet名称

1、定义“名称管理器”

点击“公式”,“名称管理器”。在弹出的对话框中,点击“新建”,这样会弹出新的对话框。在“名称”这里输入TQ(备注:可以输入任意字母或汉字),在“引用位置”输入公式:

=GET.WORKBOOK(1)

注释:

a、Excel 4 使用宏表而不是模块。而Get.Workbook( )是一个 Excel 4 中的宏表函数。所以在使用这个GET.WORKBOOK函数的时候,不需要启用宏。

b、GET.WORKBOOK可以提取当前工作簿中的所有sheet名称

使用这个函数后,会得到一个数组:

{“[工作薄名字.xlsx]Sheet名字1”,“[工作薄名字.xlsx]Sheet名字2”,“[工作薄名字.xlsx]Sheet名字3”…………}

2、用index()读取数组的元素

通过Get.Workbook( )我们已经获取了sheet的元素数组,这个时候我们要读取每一个元素。而读取元素就要使用函数index()。

根据百度百科的定义:INDEX函数是返回表或区域中的值或值的引用。

Index()有两个参数,第一个就是数组,第二个就是获取数组中的第几元素。

这个时候如果要获取刚才的GET.WORKBOOK(1)中第一个元素,我们就要先把GET.WORKBOOK(1)定义为TQ。然后用index(TQ,1)就能获取第一个元素了。

为了在表格中批量获取,我们把第二个参数,用行号获取函数ROW()来实现。

index(TQ,ROW(A1))这样下拉后,每一行就能获取对应行号对应数字的对应的元素。

那么,在这个案例中,我们在目录这个sheet表中的A1单元格,输入:

=INDEX(TQ,ROW(A1))

然后下拉填充

3、提取sheet名称,去掉工作簿名字以及其他无关的符号

通过刚才的案例,我们已经把sheet名字获取来了,但是里面有工作簿名字,我们只需要sheet名字,其他的我们都要删掉。那么怎么操作?这个时候,我们就需要用MID()函数了。

a 、MID()提取函数

MID()函数,有三个主要的参数,第一个参数就是要提取的字符串,第二个参数就是从第几个字符开始提取,第三个参数就是提取多少个字符。

而刚才我们用index(TQ,ROW(A1))已经获取了第一个sheet的名字了,这个名字就是[工作薄名字.xlsx]Sheet名字1。我们可以看到我们需要的sheet名字前面有个]符号,我们只要能获取这个符号的位置,就能知道sheet名字的第一个字符的位置。这个时候我们使用find函数就可以了。

b、通过find()函数获取位置

Find()函数有两个主要的参数,第一个参数就是要查找的字符,第二个参数就是在哪个字符串中查找。这个时候我们用find(“]”,index(TQ,ROW(A1))),这样就能得到一个数字。然后在这个数字的基础上加1,就是sheet名字开始的位置。然后MIND()第三个参数,我们用比较大一点的长度,因为每个sheet不定有规律,字符串长度也不一,有的长,有的短。我们呢就输入999,这样能满足大部分的需求了。

通过刚才的一顿操作,我们就获取了sheet的名字了。

那么对于这个案例,我们尝试在B1单元格输入下面的公式:

=MID(A1,FIND("]",A1)+1,999)

4、通过HYPERLINK()函数建立超链接

下面就是用超链接函数HYPERLINK()来建立超链接并给它显示sheet名字。这个函数有两个主要参数,第一个就是添加链接,第二个就是显示名字。

a 、超链接到某个excel文件

超链接到某个工作簿的话需要输入完整的地址,比如,桌面有个excel文件:bb.xls,这个文件的路径为:

C:\Users\Administrator\Desktop\

则完整的链接地址为:C:\Users\Administrator\Desktop\bb.xls

则超链接后,点击这个超链接后直接打开这个工作薄的第一个sheet,或者当前打开的sheet。

b 、超链接到某个sheet

如果要指向某个sheet的话,就需要加入sheet的名字,比如要打开bb.xlsx的sheet3,则应该写成如下公式:

首先在当前输入公司的sheet表中的A6单元格中输入刚才的完整路径地址

则A6=C:\Users\Administrator\Desktop\bb.xls

然后指向sheet3的链接地址为:"["&A6&"]"&"Sheet3!A1"

这个格式很像我们在做VLOOKUP时,在引用另一个表格中数据区域时的路径格式,我们这个时候可以去熟悉下vlookup引用数据区域:

=vlookup(B1,[bb.xlsx]Sheet3!$A$1:$B$24,2,0)

"["&A6&"]"&"Sheet3!A1"中的&是连接符,通过连接符把A6单元格的变动地址和sheet表连起来。并且指向超链接sheet的A1单元格。当然我们也可以指向其他任意单元格。

通过刚才的分析,我们就能得到一个完整的超链接公式:

=HYPERLINK("["&A6&"]"&"Sheet3!A1")通过这个公式我们就可以超链接到bb.xls工作薄的sheet3并指向A1单元格。

那么回到我们这个案例,我们在C1单元格输入以下公式:

=HYPERLINK(A1&"!A1")

因为我们这里超链接的sheet都是本excel工作薄里的,所以不需要把目录加入到公式里。

5、最终的公式——建立超链接,解决本篇的问题

现在我们回到本次的主题,如何超链接到本工作簿的各个sheet并指向每一个sheet中的A1单元格。

我们利用刚才的思路,写出如下公式:

=HYPERLINK(INDEX(TQ,ROW(A1))&"!A1",MID(INDEX(TQ,ROW(A1)),FIND("]",INDEX(TQ,ROW(A1)))+1,999))

也可以做个简化,因为有个公式在其他单元格已经输入过了:

=HYPERLINK(A1&"!A1",B1)

我们再全选sheet 在B2单元格输入:

=HYPERLINK(INDEX(TQ,ROW(A7))&"!A1",MID(INDEX(TQ,ROW(A7)),FIND("]",INDEX(TQ,ROW(A7)))+1,999))

这样每一个sheet页面都会有一个链接到目录的超链接。

这样我们的超链接目录就生成了。

发表回复

后才能评论