excel工作表添加目录_长Excel工作表的目录

最新推荐文章于 2023-07-14 10:28:52 发布

culiao2169

最新推荐文章于 2023-07-14 10:28:52 发布

阅读量948

点赞数

文章标签： excel linux 软件测试 python vba

原文链接：https://contexturesblog.com/archives/2011/08/15/table-of-contents-for-long-excel-sheet/

版权

excel工作表添加目录

In the comments for my post on creating a table of contents in Excel, Eden asked: "Can I create a content page within one worksheet? I have one worksheet and it is very long." Good idea! A short table of contents at the top of a worksheet would make it easy to find specific sections on a long worksheet. Here are the steps for setting that up.

在关于我在Excel中创建目录的文章的评论中，伊甸园问：“我可以在一个工作表中创建内容页面吗？我有一个工作表，而且它很长。” 好主意！工作表顶部的简短目录使在长工作表上查找特定部分变得容易。以下是设置步骤。

创建标题列表 (Create the Headings List)

The first step is to copy all the headings to the top of the worksheet, where they'll be used for the table of contents.

第一步是将所有标题复制到工作表的顶部，这些标题将用于目录。

Insert blank rows at the top of your worksheet, to make room for the table of contents.
在工作表的顶部插入空白行，以便为目录留出空间。
Click on the first heading, to select it.
单击第一个标题以将其选中。
Press the Ctrl key, and click on each of the remaining headings, to select all of them.
按Ctrl键，然后单击其余的每个标题，以选择所有它们。
Click the Copy command on the Excel Ribbon
单击Excel功能区上的“复制”命令
Right-click in an empty cell at the top of the worksheet, and under Paste Options, click Paste as Values
右键单击工作表顶部的空白单元格，然后在“粘贴选项”下，单击“粘贴为值”

That creates a list of headings at the top of the sheet.

这将在工作表的顶部创建标题列表。

命名标题单元格 (Name the Heading Cells)

Next, follow these steps, to create a named range for each heading cell.

接下来，按照以下步骤，为每个标题单元格创建一个命名范围 。

In the body of the worksheet, select the cell with the first heading.
在工作表的主体中，选择具有第一个标题的单元格。
To name that cell, click in the name box, to the left of the Formula Bar.
要命名该单元格，请在公式栏左侧单击名称框。
Type a short one-word name for that range, and press Enter.
为该范围键入一个简短的单字名称，然后按Enter。
Repeat these steps, to name each of the remaining headings.
重复这些步骤，以命名其余的每个标题。

In this example, the first heading cell is named Income.

在此的示例第一个标题单元格称为收入。

在目录中添加链接 (Add Links in the Table of Contents)

The final step is to link the headings to the table of contents. Follow these steps, to add the hyperlinks.

最后一步是将标题链接到目录。请按照下列步骤，添加超链接。

At the top of the worksheet, select the cell with the first heading that you're going to link.
在工作表的顶部，选择要链接的第一个标题的单元格。
On the Excel Ribbon's Insert tab, click Hyperlink
在Excel功能区的“插入”选项卡上，单击“超链接”
Under Link To, click on Place in This Document
在“链接到”下，单击“在此文档中放置”。
Under Defined Names, click on that heading name – Income in this example.
在“已定义的名称”下，单击该标题名称-在本示例中为“收入”。
(Optional) Click Screen Tip, and type the heading name, then click OK
(可选)单击“屏幕提示”，然后键入标题名称，然后单击“确定”。
Click OK, to close the Insert Hyperlink dialog box.
单击“确定”，关闭“插入超链接”对话框。

Now you have a list at the top of the worksheet that's linked to each of the sections below.

现在，工作表顶部有一个列表，该列表链接到下面的每个部分。

链接到链接 (Link to the Links)

To make navigation even easier, you could put a link near each of the subheadings, to take you back to the table of contents.

为了使导航更加容易，您可以在每个子标题附近放置一个链接，以带您回到目录。

First, name a cell at the top of the worksheet. In the screen shot below, the main heading cell is named TopSummary
首先，在工作表的顶部命名一个单元格。在下面的屏幕快照中，主标题单元格名为TopSummary

Next, add a shape to the worksheet, to the left of the first heading cell. I added a small arrow, and set its properties to not Print, and Move, but don't Size with cell.
接下来，向第一个标题单元格左侧的工作表中添加一个形状。我添加了一个小箭头，并将其属性设置为“不打印并移动”，但不设置“单元格大小”。

With the shape selected, insert a Hyperlink to the named range at the top of the sheet.
选择形状后，将超链接插入工作表顶部的命名范围。

Copy the shape, and paste to the left of the remaining headings.

复制形状，然后粘贴到其余标题的左侧。

测试导航 (Test the Navigation)

Now your navigation system is ready to test.

现在，您的导航系统已准备好进行测试。

Click on a link in the table of contents to quickly go to a section in the report.
单击目录中的链接以快速转到报告中的某个部分。
Click on an arrow, to return to the top of the worksheet.
单击箭头，返回到工作表的顶部。