在一张EXCEL工作簿中有若干张工作表,乃至几十张,如果我们想给工作簿中所有的工作表生成一个目录,该如何操作呢?是将每一个工作表的sheet名称进行复制,然后再粘贴到一张表上吗?NO !NO !NO! 今天教你另外一种方法 首先在工作簿中插入一个sheet,命名为“目录” 在生成目录之前,还需要进行一次命名来获取工作表的名称 “公式”---“定义名称”---弹出“新建名称”对话框,输入“名称”,在引用位置中输入公式 =INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW),单击“确定”按钮 完成之后,在“公式”---“名称管理器”中即可看到命名的名称及引用的位置 制作目录 在B1单元格中输入公式 =IFERROR(HYPERLINK(目录&'!A1',MID(目录,FIND(']',目录)+1,100)),'')并向下拖动填充即可自动生成目录,并且为超链接 在对工作表的sheet进行增减或删除时,目录也会随之自动进行更新 制作“返回目录”按钮 按shift键,选中除“目录”外的其他sheet页,在E1单元格中录入 =HYPERLINK('#目录!A1','返回目录'),然后回车。所有选中sheet页中便成功添加“返回目录”超链接。 这样目录就做好了,最后就是保存 保存时,会弹出警告对话框,请点击“是”,将文件保存为启用宏的工作簿,否则,下次打开文件时,目录会失效。 公式解析 GET.WORKBOOK(1)是宏表函数,以[Book1.xls]Sheet1的形式返回工作簿中所有工作表名的水平数组。ROW(A1)在向下填充时会变成ROW(A2)、ROW(A3),从而得到行号1,2,3…… INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW),获取工作簿中所有工作表的名称。 &T(NOW)是因为T(NOW)='',不影响公式结果,可以使工作表改名或增加、删除工作表时,公式能自动重新计算。 =IFERROR(HYPERLINK(目录&'!A1',MID(目录,FIND(']',目录)+1,100)),'') 用“MID(目录,FIND(']',目录)+1,100)”提取工作表名称,表示从“]”之后开始提取100个字符,一般的工作表名称没有这么长,这里写100足够用了。 HYPERLINK是链接函数,第一个参数表示链接的位置,第二个表示要“显示的文字”。 FERROR去掉空值 |
|