给大量的工作表批量创建目录和返回目录,大大提高工作效率

当一个工作薄中有大量的工作表,比如有10,30,50个或者更多。这时候弄一个目录页,然后在其他内容页在创建个返回目录页是不是非常方便管理呢。

如何快速批量创建工作表并将工作表重命名我们之前已经讲过,具体看参见这篇文章:利用Excel批量创建多个工作表并命名

下面的文章我们以这个创建好的批量工作表来练习。

今天我们就来做如何批量创建目录和返回目录。在正式工作开始之前,我们需要将已经创建好的用各个国家名字命名的多个工作表中的宏删除掉,因为工作表已经创建好了,宏已经不需要了,并且把“中国”这个工作表中的内容清空,“中国”这个表里的内容是用来批量创建工作表的,已经创建好了,没用了。如果你的是一个全新的表可以直接看下面的教程。

一、创建返回目录

我们在这个工作薄内新建一个工作表,并重命名为“目录”,注意要将这个工作表放在最前面,对此例来说也就是放在“中国”表的前面,作为目录页。

1、自定义名称

单击B1单元格,切换到”公式”选项卡,单击”定义名称”,如下图所示:

给大量的工作表批量创建目录和返回目录,大大提高工作效率

弹出“新建名称”对话框,在“名称”文本框中输入“目录”,在“引用位置”文本框输入以下公式:

=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW()),如下图所示:

给大量的工作表批量创建目录和返回目录,大大提高工作效率

2、创建目录

然后,我们在单元格“B1”中粘贴如下公式“=IFERROR(HYPERLINK(“#'”&MID(目录,FIND(“]”,目录)+1,99)&”‘!A1″,MID(目录,FIND(“]”,目录)+1,99)),””)”,然后鼠标右键选中该单元格,将鼠标放在该单元格右下角处,待鼠标变成“+”后,往下拖动,直到出现空白单元格就将目录页创建完毕了。如下图所示:

给大量的工作表批量创建目录和返回目录,大大提高工作效率

最终效果如下图


给大量的工作表批量创建目录和返回目录,大大提高工作效率


这时候我们将鼠标放到任意单元格上,鼠标就会变成一个小手,点击后就进入该工作表中了。目录页就制作完成了。如下图所示。

给大量的工作表批量创建目录和返回目录,大大提高工作效率


目录已经创建好了,可以顺利地进入具体的工作表中,那么我们想反回目录可怎么办呢。下面我们就来制作返回目录。

二、制作返回目录。

制作返回目录主要用到这个公式“=HYPERLINK(“#目录!A1″,”返回目录”)”,制作返回目录有多种方法,今天我们主要讲两种方法。

1、笨方法

这个方法为什么叫笨方法呢,因为这个需要你一个一个去操作,费工夫,耗费体力。但是这个方法简单易学,一说就懂。

就是你随便到其他工作表中,比如到“中国”这个工作表中,找个空白的单元格,将上面的公式粘贴进去就行了。

如下图所示,粘贴后这个单元格就带超链接了,鼠标变成小手后点击一下就返回到目录了,非常简单。

给大量的工作表批量创建目录和返回目录,大大提高工作效率

二、聪明的方法

这个方法为啥叫聪明的方法呢,因为标题已经说了要批量建立返回目录,这个方法可以快速在每个工作表中生成返回目录。当然要用到VBA了。代码如下:

Sub cjfhml()
Dim i
For i = 2 To ActiveWorkbook.Worksheets.Count
Sheets(i).Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(""#目录!A1"",""返回目录"")"
Next i
End Sub

代码写好后我们点击下图中的绿色小三角,也就是运行按钮,就可以了。所有的工作表中都有这个返回目录功能了。哈哈,是不是超级快速,超级批量啊。

给大量的工作表批量创建目录和返回目录,大大提高工作效率


效果如下图:所有的内容页的工作表中第一个单元格都有一个返回目录了,将鼠标放到该单元格上,鼠标变成小手的时候,点击就返回目录了。

给大量的工作表批量创建目录和返回目录,大大提高工作效率

批量创建目录和返回目录就讲完了。隐藏部分是关于公示的讲解,excel附件,以及带注释的vba和vba运行要注意的事项,和工作表中的核心注意事项,不注意的话程序会出错。有兴趣学习的同学可以详细学习下。

此处内容需要购买后查看,月费会员可免费查看

支付1.0元查看

今天讲的目录和返回目录主要利用的是vba和公式,有一定的弊端,比如工作表的标签不能隐藏,否则的话就无效了。随后我们会将更高级的纯VBA实现目录和返回目录的制作,这样的目录和返回目录就没有任何局限了。敬请期待。

未经允许不得转载:飞鹰图表 » 给大量的工作表批量创建目录和返回目录,大大提高工作效率
分享到:

评论抢沙发

评论前必须登录!