首页 excel笔记函数技巧正文

Excel表格制作目录

九天 函数技巧 2020-04-17 212 0 | 作者:Excel精英培训 midnowget.workbookindexrowhyperlinkiferrort

在Excel表格插入工作表目录,是一个老生常谈的技巧。但大数多用户只会套用,并不懂其中的原理,毕竟制作过程太过复杂。所以,离开了教程也做不出来了。

为了让大家可以随时随地做自已做目录,今天兰色就剖析一下制作Excel目录的过程。

制作过程:

1、公式 - 定义名称:shname

=mid(get.workbook(1),find("]",GET.WORKBOOK(1))+1,99)&T(now())

乖乖,好多陌生的函数....新手看了这个公式估计要晕掉。别急,一步步帮大家分析。

在Excel中有一类函数叫表函数,功能非常强大,可以提取Excel或电脑的信息,比如提取单元格颜色,提取文件目录。今天要用到的是一个可以提取所有工作表名称的函数:Get.Workbook

由于宏表函数只能在定义名称中使用,所以必须先定义名称:

公式 - 定义名称  - 输入自定义的名称 - 在引用位置输入公式:=get.workbook(1)

注: get.workbook的参数是1时,可以提取所有工作表名称

定义的名称可以在单元格公式中直接使用,比如输入 =Shname即可返回所有工作表名称。(选中公式按F9可以查看所有值

由于返回的工作表名称前含工作簿名称“[抖音Excel技巧集.xlsm]”,所以下一步把用函数它删除:

Find函数查找"]'的位置,然后用MID函数截取。

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)

怎么把工作表名称显示到一列中?

可以用index函数+row函数提取:index可以根据位置提取数据,Row函数可以在向下复制时生成1,2,3,4...序数

=INDEX(shname,ROW(A1))

名称有了,下一步是给工作表名称添加链接,这一步要用公式:=hyperlink#工作表名称!单元格地址,工作表名称)

即:=HYPERLINK("#"&INDEX(shname,ROW(A1))&"!a1",INDEX(shname,ROW(A1)))

当公式超出工作表个数时,再复制公式会返回错误值,所以需要再外套iferror函数。

=ifERROR(HYPERLINK("#"&INDEX(shname,ROW(A1))&"!a1",INDEX(shname,ROW(A1))),"")

好像很完美了? No...当你修改、删除工作表时,目录并不会自动更新:

问题出在哪?原来我们在定义名称时少了两个函数:TNow

  • Now函数可以生成自动更新的时间,可以让公式强制刷新

  • T函数则可以把数字(时间也是数字)转换为空白

所以T(Now())即可以让公式强制刷新(双击或其他单元格内容更新),又不影响单元格的值。

接下来修改定义的名称:

至此,目录的公式设置完成。无论添加、删除工作表或修改工作表名称,双击任一个单元格或任一单元格内容发生修改,目录都会自动更新。

完成了吗?No! 我们还少最后一步,把工作簿另存为启用宏的工作簿。

盘点了一下,制作目录共用了两大类9个函数,对新手真的有点难度,所以要想随时随地制作目录,你还真的需要理解这些函数的用法。

打赏
  • 文章发表:九天
  • 本文地址:https://rijifang.com/index.php/post/2802.html
  • 声       明:转载请注明出处和附带本文链接!文章部份资料来自于网络,版权归原作者,尊重原创,注重分享;如涉版权问题,请联系本站删除!