工作簿的目录一直是一个比较有用的东西,方便在工作簿中对于得个工作表进行切换,那么这个目录到底是怎么制作的,今天老师给大家整理了两个方法以便大家学习,由简单到一般地去对比着学习。
01
操作法
在一张工作簿中,当有很多个工作表的时间查看是十分地不方便地,那么如何快速地创建目录呢?如下图所示:
Step-1:在按Shift键选中第一工作表与最后一个工作表,即选中所有的工作表:
Step-2:然后在A1单元格中输入公式:=XFD1,按Enter键确定。如下图所示:
Step-3:然后单击【文件】-【信息】-【检查问题】-【检查兼容性】。如下图所示:
Step-4:然后弹出的对话框中单击【复制到新表】。如下图所示:
此时会生成一个工作表【兼容性报表】,内容如下图所示:
Step-5:删除没有用的项目,将工作表名称修改为“目录表”,留下带有连接的部分,如下图所示:
Step-6:为每个工作表设置“返回”链接,首先按Shift键选中第一个与最后一个工作表,达到全选工作表的目的。最后在A1单元格中输入公式:
=HYPERLINK("#目录表!A1","返回"),按Enter键完成即可。然后取消工作表组合如下图所示:
效果如下动图所示:
原理解释:在Excel2003里,最大的列是256(即IV,2的8次方)列,而2007及以后的版本中最大的列是16384(即XFD,2的14次方)列。所以在前面的Step-2里输入的“=XFD1”即最后一列的第一个单元格,利用这个兼容性的原理可以在当前工作簿里的工作表的A1单元格建立成链接,即具有等同于目录的效果。后期的文章中将推出建立目录的其他的方法。
02
公式法
如下图所示,是一个工作簿的若干个工作表:
Step-1:在这些工作表的前面新建一个工作表,命名为“目录”,然后单击【公式】-【名称管理器】-【新建】,然后输入名称的名称如“提取工作表名称”,在【引用位置】输入公式:=GET.WORKBOOK(1),然后单击【确定】。如下图所示:
注意:GET.WORKBOOK(1)是宏表类函数。所以在保存工作表的时候一定要保存成“xlsm”格式的工作簿,否则就不能自动更新了。
Step-2:再次定义一个名称,在【公式】-【名称管理器】-【新建】,输入名称为wn,在引用位置输入公式为:=MID(提取工作表名称,FIND("]",提取工作表名称)+1,99)&T(now()),然后单击【确定】,如下图所示:
注意:T(now())的作用类似于F9,就是起到一个刷新的作用。
Step-3:在B2单元格中输入公式,逐个提取工作表名称,公式为:=INDEX(wn,ROW(A2)),然后向下填充至空白为至。
Step-4:使用HYPERLINK函数自动插入超连接,在B2单元格中将公式修改为:
=HYPERLINK("#"&INDEX(wn,ROW(A2))&"!A1",INDEX(wn,ROW(A2))),如下图所示:
Step-5:然后使用IFERROR函数将错误值进行屏蔽,如下图所示:
Step-6:为每个工作表制作返回菜单。先在第一个工作表中的A1单元格中输入返回。然后右键【链接】,在打开的对话框中选择【文档中的位置】下面的“目录”,然后点击【确定】,接着复制A1单元格,,然后选中第二个工作表,按住Shift键后点选最后一个单元格,然后将刚才的复制的内容粘在A1单元格中,然后取消工作表组合。右下图所示:
最后完成工作表的目录的制作,如果新增一个工作表的话只需要双击任意一个单元格即可。如下图所示:
作者:我是世杰,财务excel深度玩家,坚持每天分享财务excel干货,微信公众号:24财务excel
推荐阅读