Excel批量制作目录技巧

第一步：设置名称框

首先在需要建目录的工作薄中，新建一个工作表，之后点：公式—定义的名称—名称管理器—新建，在编辑名称对话框：名称命名为“提取”，在引用位置输入公式=GET.WORKBOOK(1)，如下图所示：

说明：GET.WORKBOOK(1)是宏函数，表示获取工作表名称。

第二步：提取工作表名称

在A1单元格中输入公式=INDEX(提取，ROW())，然后鼠标移动到右下角进行拖拽。

语法=INDEX (数据源，行，列）

先将公式生成的工作表名称复制，选择性粘贴选为数值。

利用【数据】-【分列】-【固定宽度】，将鼠标移动标尺移动到]后面，点下一步【常规】，这样就分离开了。或者利用函数也可以利用MID和FIND函数。输入公式=MID(A1,FIND("]",A1)+1,99)&T(NOW())，或者Ctrl+E（office 2013以后版本才有）。

第三步：设置链接

输入公式

=HYPERLINK("#'"&A1&"'!A1",A1)

HYPERLINK语法= HYPERLINK（链接，显示文本）

链接当前工作薄中其他工作表的指定区域

在单元格中，输入公式:

=HYPERLINK("#’Sheet2’!A1","sheet2的A1单元格")

同理也可以链接其他工作表中的指定区域

注意：利用单引号’引用工作表全名，由于我的工作表中存在一些括号等符号，所以在工作表前添加了单引号，防止出现“引用无效”的状况。工作表名称前后加半角单引号即可解决这是由于一些特殊符号在跨表引用时自动在引用地址里面会自动加上 '，造成引用的字符串不一致造成的。

整合公式：

=HYPERLINK("#'"&MID(INDEX(提取，ROW()),FIND("]",INDEX(提取，ROW()))+1,99)&T(NOW())&"'!A1",MID(INDEX(提取，ROW()),FIND("]",INDEX(提取，ROW()))+1,99)&T(NOW()))

如果为整合公式的，最后保存的文件类型是：启用宏的工作簿（*.xlsm)，Microsoft Excel 启用宏的工作表 (.xlsm)，因为前面定义了一个名称【提取】。