实战之路《Excel 数据处理与分析实战宝典_第2版》_8.7.2 编制工作表目录

【摘要】 本书摘自《Excel 数据处理与分析实战宝典_第2版》一书中第8章，第7节，耿勇著。

8.7.2 编制工作表目录

有时我们遇到同一工作簿中有多张表格，在这种

情况下标签栏无法显示所有的工作表，例如：财务预

算体系、建筑投标中某项目的土建或安装体系。这时

我们可以考虑利用HYPERLINK 函数的跳转功能实现

表格的快速查看，形成一个报表目录，单击报表目录

的单元格中的超链接可到达指定的工作表。

首先，在Excel表格中定义名称， 一般选择定义的名称为B1单元格中所表述的内容(即首行)。 这里选择“报表名称”,有时需要选择“最左列”。在引用位置处输入如下公式：

=REPLACE(GET.WORKBOOK(1),1,FIND(")",GET.WORKBOOK(1)),)&T(NOW())

在单元格 B2 中输入如下公式：=IF(ROWO>COUNTA (报表名称),"",HYPERLINK

("#"&INDEX(报表名称，ROWO),INDEX(报表名称，ROWO))),然后拖动公式到不出现报表名称为止。

在A2 单元格中定义公式：=IF(B2="","",ROW()-1),然后拖动复制公式。

注 意 ★该方法使用了宏表函数 GET.WORKBOO K(), 需要启用宏。如果你的 Excel 宏安全性设置为

高或禁止宏，则无法使用该函数。

★其中的工作表名称不能出现“]”“O”之类的字符，否则会出现“引用无效”或“无法找到

指定文件”之类的错误，这时可对工作表名称进行修改。

提示 通过这种方式不仅可将文件名从各分表中取出来，而且通过将分表名称作为超链接的方式可实 现自动跳转到所希望查看的分表。更为重要的是，通过取出文件名的方式，还可与前述 INDIRECT 函 数结合实现汇总查询分表数据的功能。

8.7.3 取得硬盘指定目录下的文件名

在日常工作中有时候某个文件夹下的文件过于凌乱时不易查找，这时我们可以通过 HYPERLINK 函数的跳转功能实现表格的快速查找和打开编辑文件。利用 HYPERLINK 函数、名称、数据验证、宏表函数相结合所生成的一个文件，单击相应的链接即可 打开文件。

首先，新建一个空白的 Excel2003文件，并将Sheet1改名为“目录”,在A2 单元格中设置数 据验证，“数据验证”对话框中选择“序列”,在“来源”处输入“*,*,*xls, *.doc”。其中，“**”表示需要显示所有类型的文件，“*xls” 表示只显示 Excel 文件(包括后缀 名为.xls 、.xlsx 、.xls m等的文件),“*.doc” 表示只显示各种Word 文档。

其次，在名称管理器中定义名为“路径”的名称，在引用位置处后的文本框中输入的公式如下： =MID(CELL("filename"),1,FIND("[",CELL("filename"),1)-1)

在“路径”名称的公式中的 CELL("filename")为取出单元格的路径和文件名，利用FIND("[",CELL("filename"),1)-1, 找出第一个 “[”在路径字符串中的位置，然后减去1即取 出完整的路径。

定义名为“分类”的名称：=FILES(路径 & 目录!SAS2)&T(NOWO)。其中 FILES 为宏表 函数，可返回指定目录下指定条件的文件名， 并且以一维数组的形式返回结果。FILES(路径 &目录!$AS2)返回“路径”中的文件名。而 NOWO 是易失性函数，在工作表改动或打开时 会被强迫重算。T(NOWO) 将数值转换成空文 本，用&连接起来。整个用法的目的就是在工 作表改动时重算整个公式。

再次，在 B2 单元格中定义如下数组公式：

=IF(ROW()-1>COUNTA(分类),"",HYPERLINK(路径&INDEX(分类，ROW(1:1)),INDEX (分 类，ROW(1:1)))

公式解析：如果当前行大于找到的文件个数，就返回空值；否则返回 HYPERLINK 超链接，

返回目录下所有“分类”的超链接。

提示 此处用 Excel 2003文件来创建一个目录文件，可以直接保存这种带有宏表函数的文件；而 在，XLSX 文件中使用宏表函数，则必须另存为带宏的文件名(.XLSM)。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。