在Microsoft Excel中创建目录的4种方法

在Microsoft Excel中创建目录的4种方法

作者

在Microsoft Excel中创建目录的4种方法

您是否需要整理 Excel 工作簿中的所有工作表？您是否想知道如何在 Excel 中创建目录？本 Excel 教程将介绍使用自动化创建 Excel 目录的最简单方法。

当文档太大而无法记住所有部分时，目录可以帮助您浏览文档。Excel 工作簿也可能出现同样的情况。当 Excel 文件包含数百个工作表时尤其如此。这些工作表可以包含数据透视表、图表、图形、仪表板、数据输入表等。

您无需记住工作簿或将重要的工作表名称写在便签上，而是始终可以在 Excel 中创建目录。遗憾的是，Excel 中没有内置函数或功能让您像 Microsoft Word 一样只需单击一下即可创建目录。

因此，请按照下面提到的方法在 Excel 中创建目录，而无需寻求帮助。

推荐：如何在Excel中删除前导零

创建 Excel 目录的原因

  1. 简化了浏览大量 Excel 工作簿的艰巨任务。此外，Excel 目录可轻松找到工作簿中的特定部分和数据点。
  2. 提供宝贵的省时功能，让您快速访问目标信息。从而免除您无休止地滚动浏览表格和标签的烦恼。
  3. Excel 中的目录通过呈现工作簿内容的美观且组织良好的概述来提升用户体验。
  4. 您可以通过对相关工作表或部分进行逻辑分组来有效地排列数据，有助于维护大型工作簿管理的结构化和一致的布局。
  5. 它还提供了快速引用重要数据、图表和表格的便捷方式，减少了耗时的搜索。因此，您可以专注于实际的数据分析和可视化。
  6. 当 Excel 文件中有目录时，最终用户、数据输入操作员、利益相关者或受众可以轻松理解工作簿。
  7. 它通过简化内容定位来支持协作和团队合作。您可以让团队成员轻松找到特定部分进行输入和审阅。
  8. 通过防止在浏览工作簿时意外修改不相关的数据来最大限度地减少发生错误的可能性，从而确保数据的完整性。

使用公式创建 Excel 目录

此方法将利用包含命名范围元素的公式。这是一种半自动化方法，因为您还必须执行一些手动任务才能首次创建目录。

定义命名范围

在Microsoft Excel中创建目录的4种方法
在Microsoft Excel中创建目录的4种方法
为工作表分配命名范围
  1. 转到工作簿的第一个工作表。
  2. 单击任意空白单元格并选择 公式 标签。
  3. 点击 定义名称 下拉菜单中的 定义的名称 堵塞。
  4. 在 編輯名稱 对话框中，输入 工作表 在里面 姓名 场地。
  5. 现在，在 引用字段 输入以下公式：
=GET.WORKBOOK(1)&REPT(NOW(),)
  1. 点击 好的 关闭对话框。

制作目录

  1. 在所有现有选项卡的开头添加新的工作表选项卡，方法是按 转移 + F11 键。
  2. 将其重命名为 目录。
  3. 前往牢房 A1 的 目录 选项卡并输入以下公式：
=IF(ROW(A1)>SHEETS(),REPT(NOW(),),SUBSTITUTE(HYPERLINK("#'"&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(Worksheets,ROW(A1))," ",CHAR(255)),"]",REPT(" ",32)),32))&"'!A1",TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(INDEX(Worksheets,ROW(A1))," ",CHAR(255)),"]",REPT(" ",32)),32))),CHAR(255)," "))

要完成目录，请按照下列步骤操作：

  1. 打 进入 按钮，您将在单元格中看到目录工作表名称 A1。
  2. 现在，将填充柄沿着列向下拖动，直到得到一个空白单元格。
  3. 现在，Excel 应该已经填充了工作簿中所有 Excel 工作表的超链接。
  4. 单击任意工作表名称即可导航至该 Excel 选项卡。

回到 目录 选项卡，只需单击 Excel 应用程序底部的工作表名称即可。或者，您可以添加指向 目录 在所有工作表中。为此，请复制单元格 A1 来自 目录 选项卡到工作簿中的所有工作表或使用以下 Excel VBA 脚本：

Sub CopyFormulaToAllWorksheetsWithNewRowAtTop()
    Dim SourceSheet As Worksheet
    Dim DestSheet As Worksheet
    Dim SourceCell As Range
    Dim DestCell As Range
    Dim FirstRow As Long
    Dim ws As Worksheet    
    Set SourceSheet = ThisWorkbook.Worksheets(1)
    Set SourceCell = SourceSheet.Range("A1")
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name  SourceSheet.Name Then
            Set DestSheet = ws
            Set DestCell = DestSheet.Range(SourceCell.Address)
            If DestCell.Value  "" Then
                FirstRow = DestSheet.Cells(DestSheet.Rows.Count, "A").End(xlUp).Row
                DestSheet.Rows(FirstRow).EntireRow.Insert
            End If            
            DestCell.Formula = SourceCell.Formula
        End If
    Next ws
End Sub

请参阅下面的 Excel VBA 部分，了解如何在 Excel 工作簿中使用上述 VBA 脚本。

将目录单元格复制到所有工作表
将目录单元格复制到所有工作表

使用上述方法的条件如下：

  • 您必须将工作簿保存为启用宏的工作簿或 XLSM 格式。
  • 该公式将显示并链接工作簿中的所有 Excel 工作表，包括隐藏的工作表。
  • Excel 目录将反映以下更改：
    • 工作表的添加和删除
    • 更改工作表名称
    • 更改工作表标签的顺序

使用 Power Query 在 Excel 中创建目录

您可以使用 Power Query 工具创建工作簿中所有工作表的列表。然后，应用 HYPERLINK 函数快速创建 Excel 目录。

因此，如果您使用 Power Query 访问大型工作簿并且它没有任何目录选项卡，则您也可以添加该选项卡来组织工作簿。

使用 Power Query 创建选项卡列表

在 Power Query 上选择 Excel 工作簿
在 Power Query 上选择 Excel 工作簿
  1. 打开任意 Excel 工作簿并单击 数据 选项卡上的 Excel 功能区菜单。
  2. 点击 获取数据 按钮里面 获取和转换数据 堵塞。
  3. 将鼠标光标悬停在 从文件 选项。
  4. 在溢出菜单上，单击 从 Excel 工作簿。
导入 Excel 文件
导入 Excel 文件
  1. 在 导入数据 对话框中，导航到需要创建目录的 Excel 文件。
  2. 选择文件并点击 进口 按钮。
转换数据 Power Query
转换数据 Power Query
  1. 您现在应该可以看到 Excel 文件的所有工作表 Power Query 导航器 对话框。
  2. 单击父文件夹，例如 目录.xlsx，然后选择 转换数据 按钮。
  3. 工作簿将在 Power Query 编辑器 界面。
删除 Power Query 中的其他列
删除 Power Query 中的其他列
  1. 右键点击 第一列或 姓名 列并选择 删除其他列 上下文菜单上的选项。
  2. 点击 文件 菜单并选择 关闭并加载至。
将数据导入工作表
将数据导入工作表
  1. 选择 新建工作表 选项 导入数据 窗口并单击 好的。
清除表格中的筛选条件
清除表格中的筛选条件
  1. 突出显示整个导入的数据并按 Ctrl + 转移 + 大号 移除过滤器。
特殊粘贴
特殊粘贴
  1. 再次突出显示表格，按 Ctrl + C 复制，选择目标单元格，然后按 Ctrl + Alt + 五 打开 特殊粘贴。
  2. 在 特殊粘贴 对话框中，选择 价值观，然后点击 好的。
目录列表
目录列表
  1. 从导入的表中删除所有不必要的条目。

推荐：Microsoft Excel中扩展表格的6种方法

制作目录标签

在工作表名称中应用公式
在工作表名称中应用公式
  1. 将工作表重命名为 目录。
  2. 写 目录 在第一个单元格中： A1。
  3. 在第一个工作表名称右侧的相邻单元格中输入以下公式：
=HYPERLINK("#Sheet1!A1","Data1")
  1. 现在，将相同的公式复制并粘贴到所有其他工作表的列中。
工作表公式
工作表公式
  1. 重命名公式元素，例如 Sheet1 下一个工作表的名称和 Data1 到 Data2， 等等。

使用 Excel VBA 在 Excel 中创建目录

您还可以使用以下 VBA 脚本将 Excel 目录添加到任何具有许多选项卡、表格、数据透视表等的工作簿。

Excel VBA 脚本

Sub TOC()
    Dim TOCSheet As Worksheet
    Dim ws As Worksheet
    Dim rowNum As Long    
    Set TOCSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    TOCSheet.Name = "Table of Contents"
    TOCSheet.Cells(1, 1).Value = "Contents"
    TOCSheet.Cells(1, 2).Value = "Details"    
    rowNum = 2
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name  TOCSheet.Name Then           
            TOCSheet.Cells(rowNum, 1).Value = ws.Name
            TOCSheet.Cells(rowNum, 2).Value = "Worksheet # " & ws.Index & " printable pages " & GetPrintablePageCount(ws)
            TOCSheet.Hyperlinks.Add _
                Anchor:=TOCSheet.Cells(rowNum, 1), _
                Address:="", _
                SubAddress:=ws.Name & "!A1", _
                TextToDisplay:=ws.Name
            rowNum = rowNum + 1
        End If
    Next ws
End Sub
Function GetPrintablePageCount(ws As Worksheet) As Long
    Dim pageCount As Long
    pageCount = ws.PageSetup.Pages.Count
    GetPrintablePageCount = pageCount
End Function

如何使用代码

在 Excel VBA 中创建 TOC VBA 脚本
在 Excel VBA 中创建 TOC VBA 脚本
  1. 点击 Alt + F11 调用 Excel VBA 编辑器。
  2. 点击 插入 菜单栏上的按钮。
  3. 选择 模块 在上下文菜单中。
  4. 将上面提到的 Excel VBA 脚本复制并粘贴到模块中。
  5. 点击 节省 按钮。
  6. 关闭 Excel VBA 编辑器。
执行宏
执行宏
  1. 打 Alt + F8 调用 宏 对话框。
  2. 选择 目录 宏。
  3. 打 跑步 按钮创建 Excel 目录。

您可能需要格式化新创建的目录工作表，以使其变得可读且易于呈现。

使用 Office 脚本创建 Excel 目录

在 Excel 网页版或适用于 Microsoft 365 桌面应用程序的 Excel 中，可以使用 Office Scripts 脚本工具创建自动化 Excel 函数，例如制作 Excel 目录工作表。 您必须遵循以下步骤：

在新选项卡中创建工作表列表

使用 Office 脚本在新工作表中列出所有工作表
使用 Office 脚本在新工作表中列出所有工作表
  1. 点击 自动化 选项卡并点击 新脚本 按钮。
  2. 进入 代码编辑器 出现后，复制并粘贴以下脚本：
function main(workbook: ExcelScript.Workbook) {
	// Create a new worksheet called "TOC".
	let sheetNamesSheet = workbook.addWorksheet("TOC");
	// Get the collection of worksheets in the workbook.
	let worksheets = workbook.getWorksheets();
	// Create a string that will contain the list of sheet names.
	let sheetNames = "";
	// Iterate over the worksheets and add their names to the string.
	for (let worksheet of worksheets) {
		sheetNames += worksheet.getName() + "\n";
	}
	// Set the value of each cell in the "SheetNames" sheet to a sheet name.
	for (let i = 1; i
  1. 点击 保存脚本 按钮。
  2. 打 跑步 按钮来执行脚本。

Office 脚本将自动创建一个名为 目录 并按出现的顺序列出工作簿中所有现有的工作表。

添加指向目录工作表的链接

添加目录表链接
添加目录表链接
  1. 转到新创建的工作表或 目录 床单。
  2. 您必须查看工作簿中工作表的完整列表。
  3. 点击 自动化 并选择 新脚本 选项。
  4. 在 – 的里面 代码编辑器，复制并粘贴此 办公室脚本 代码：
function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range A1 on selectedSheet
	selectedSheet.getRange("A1").setValue("TOC");
	// Reorder selectedSheet
	selectedSheet.setPosition(0);
	// Set range B2 on selectedSheet	selectedSheet.getRange("B2").setFormulaLocal("=HYPERLINK(\"#Sheet1!A1\",\"Data 1\")");
	// Paste to range B3:B6 on selectedSheet from range B2 on selectedSheet
	selectedSheet.getRange("B3:B6").copyFrom(selectedSheet.getRange("B2"), ExcelScript.RangeCopyType.all, false, false);
	// Set range B3:B6 on selectedSheet	selectedSheet.getRange("B3:B6").setFormulasLocal([["=HYPERLINK(\"#Sheet3!A1\",\"Data 3\")"],["=HYPERLINK(\"#Sheet4!A1\",\"Data 4\")"],["=HYPERLINK(\"#PivotTable!A1\",\"PivotTable\")"],["=HYPERLINK(\"#Table!A1\",\"Table\")"]]);
	// Auto fit the columns of range B:B on selectedSheet
	selectedSheet.getRange("B:B").getFormat().autofitColumns();
}
  1. 点击 保存脚本 选项。
  2. 打 跑步 按钮来执行代码。

如果 目录 您这边的选项卡与本教程中的示例完全相同，脚本将创建一个带有重定向链接的目录。

假设您的工作簿略有不同，则修改这些代码元素：

  • getRange("B2") 应该 getRange("C2")， getRange("D2")等，根据您想要应用第一个 超级链接 功能。
  • 在里面 超级链接 函数，修改工作表引用 Sheet1!A1 到您想要引用的确切工作表和单元格，例如 Sheet2!B1， Sheet3!C1， ETC。
  • 更改单元格范围 B3:B6 到您想要应用上述公式的列中的精确单元格范围。例如， E1:E100 在 E 列中。
  • 最后，您需要修改每个工作表的引用名称 超级链接 根据您自己的工作簿制定公式。

结论

到目前为止，您已经了解了创建 Excel 目录的四种不同方法。此外，这些方法具有不同的自动化级别。根据 Excel 工作簿中的组件数量（例如工作表、图表、数据透视表等的数量），您必须选择最合适的方法。

当工作簿相对较小，例如只有几个工作表、图表、表格等时，您可以依赖自定义函数。

如果工作簿很大，请尝试 Excel VBA 方法。如果您需要在 Excel 网页版应用中自动创建 Excel 目录，请尝试 办公室脚本 方法。您也可以尝试 Power Query 方法。

尝试上述方法后，请在下方评论告诉我们您最喜欢哪种方法。

推荐：GeneratePress Premium   + GenerateBlocks Pro 

Previous

在Microsoft Excel中从另一张工作表提取数据的5种方法

Next

如何在Windows 11中为活动和非活动标题栏设置颜色

相关文章

如何从SSD启动Raspberry Pi并将其用于永久存储

如何从SSD启动Raspberry Pi并将其用于永久存储

如何在Windows 11中禁用网络摄像头

如何在Windows 11中禁用网络摄像头

在Windows 11中隐藏特定文件和文件夹的5种方法

在Windows 11中隐藏特定文件和文件夹的5种方法

如何修复Windows 11中的内核数据页内错误

如何修复Windows 11中的内核数据页内错误

发表评论