excel多sheet创建索引目录方法.pdf

为为 Excel2007Excel2007 多个工作表快速创建目录多个工作表快速创建目录我们经常把同类相关 Excel 工作表集中保存在同一文档中，以便于在各表格间进行引用、查看。当一个文档中的工作表达到一定数量时，要想找到需要的工作表就变得很麻烦了。此时若能建立一张“目录”工作表显示所有工作表的名称和链接，事情将会简单很多。下面介绍一种可以快速为快速为 ExcelExcel 工作簿创建工作表目录工作簿创建工作表目录的方法。第一步第一步定义名称定义名称打开 Excel 2007，右击第一张工作表标签选择“重命名”（或者直接双击 sheet1），把它重命名为“目录”工作表。1.将 sheet1的名称改为“目录”，图解步骤如下：单击右键如图 2，或者直接双击 sheet1 当 sheet1 如图 3 显示时，直接输入“目录”图 11单击“重命名”如图 3此时直接输入文字即可图 3图 2第二步第二步 建立目录公式引用索引建立目录公式引用索引选中 B1 单元格，切换到“公式”选项卡，单击“定义名称”，在弹出的“新建名称”窗口中输入名称“工作表名”，在引用位置中则输入公式=INDEX(GET.WORKBOOK(1)，$A1)&T(NOW()，单击确定即可定义出一个名为“工作表名”的名称。操作过程如下图系：Step1Step1：切换至公式选项卡点选名称管理，如图切换至公式选项卡点选名称管理，如图 4 4此时单击“名称管理器”会出现图 5 所示界面图 42Step2Step2单击“新建（N）”图 5Step 3Step 3此处输入“工作表名”将 此 处 内 容 删 除 之 后，将=INDEX(GET.WORKBOOK(1)，$A1)&T(NOW()复制到此处，出现图 7界面所示图 63Step 4Step 4图 7公式中GET.WORKBOOK(1)用于提取当前工作簿中所有工作表名称，INDEX 函数则按 A1 中的数字决定要显示第几张工作表的名称。此外，由于宏表函数 GET.WORKBOOK(1)在数据变动时不会自动重算，而 NOW()是易失性函数任何变动都会强制计算，因此我们需要在公式中加上NOW()函数才能让公式自动重算。函数 T()则是将 NOW()产生的数值转为空文本以免影响原公式结果。注：宏表函数 GET.WORKBOOK，不能直接在单元格公式中使用，必须通过定义名称才能起作用。Step5Step5注意：大家可能完成上述操作之后会去寻找保存按钮，在此处该注意：大家可能完成上述操作之后会去寻找保存按钮，在此处该项操作是没有必要的，因为当按以上操作完成之后，单击项操作是没有必要的，因为当按以上操作完成之后，单击 X X退退 出出时，时，系统默认已经对你的设置进行了保存，系统默认已经对你的设置进行了保存，所以此处直接单击所以此处直接单击 X X 退出退出即可。即可。4第三步第三步 目录设置目录设置Step1Step1在“目录”工作表的 A1 单元格输入 1，在 B1 单元格输入公式=IFERROR(HYPERLINK(工作表名&!A1，RIGHT(工作表名，LEN(工作表名)-FIND(，工作表名)，)。公式表示当名称“工作表名”的值为错误值时显示为空“”，否则创建指向“工作表名!A1”的超链接并显示该“工作表名”。如图 8 所示图 8Step2Step2然后选中 A1：B1 单元格，把鼠标指向选中区右下角的“填充柄”按住鼠标左键向下拖动到所有 sheet 表都显示完成，在 A、B 列就会马上自动列出所有工作表目录(图 9)。单击相应工作表名称即可快速切换到该工作表中。图 9将鼠标放置此处，鼠标指针会变成加粗的“”“”号，此时按下鼠标左键不放向下拖拽直至所有的 sheet 名称显示完毕。最终就会变成图 10 所示。5Step3Step3图 11注：公式中 RIGHT(工作表名，LEN(工作表名)-FIND(，工作表名)这段函数的作用是除去“工作表名”中“”以前的内容。若你不介意工作表名称前显示“BOOK1.xlsx”一类内容的话，可以把B1中的公式简化成=IFERROR(HYPERLINK(工作表名&!A1，工作表名)，)。切换到“开始”选项卡适当设置一下目录中的字体、字号和颜色等等，建议把字号放大并设置加粗以便查看，还要调整一下 A：B 列6的列宽以便完全显示工作表名称。然后右击其他工作表标签选择“删除”，把所有其他工作表全部删除只保留一张“目录”工作表。最后单击“Office”按钮，选择“另存为”，在弹出的另存为窗口中选择保存类型为“Excel 启用宏的模板(*.xltm)”格式、文件名为“目录.xltm”，保存到 C:Program FilesMicrosoftOfficeOffice12XLSTART 文件夹下，关闭 Excel 2007 退出。若你的 Office 不是按默认路径安装，请按实际安装路径修改。第四步第四步 快速返回目录表快捷键设置快速返回目录表快捷键设置生成目录之后，如果 sheet 表较多，返回目录所在的 sheet 就比较繁琐，此时我们可以创建宏设置快捷键实现快速返回目录 sheet 的操作，具体步骤如下：生成目录之后不需关闭 excel，如果你关闭了就需要先执行“第第五步五步 保存设置保存设置”，然后再打开刚才的 excel，如果没有进行保存设置而直接关闭再次打开前面所有的设置就会失效。所以第五步在关闭带有宏的 excel 之前是很重要的。如果你在执行了第五步并且关闭了你建立的 excel 后，再次打开时你就会遇到如下面启用宏插图系列的情况，需要先执行图示操作才可以进行第四步的编辑。7单击“选项”出现启用宏插图 2启用宏 插图 1鼠标单击“启用此内容”前的圆形选择，当出现蓝色实心的时候表示设置成功然后点下面的确定即可选择完成后点确定启用宏插图 2注意：以上操作每次打开之后都会需要设置注意：以上操作每次打开之后都会需要设置完成插图所示的操作之后或者没关闭 excel 时，可以直接按照如下操作进行快捷键返回目录 sheet 的设置。步骤如下：Step1Step1 打开打开 excelexcel 菜单栏的视图板块，单击“宏”菜单栏的视图板块，单击“宏”8图 12Step2Step2图 13Step3Step3单击查看宏，出现图 13 界面单击“宏”出现图13对话框在此处输入创建的宏名，可以命名为“返回目录”，之后单击对话框中的创建，结果如图 15图 14Step 49在 两 个 语 句 段 之 间 输 入“Sheet1.SelectSheet1.Range(a1).Select”然后，关闭即可。图 15注意：此步骤大家要引起注意，大家在插入一定要注意目录注意：此步骤大家要引起注意，大家在插入一定要注意目录sheetsheet 表的编号，常见的是表的编号，常见的是 sheet1sheet1，也有会不是，也有会不是 sheet1sheet1 的？如果目的？如果目录的不是建立在了录的不是建立在了 sheet1sheet1，就需要将语句中的就需要将语句中的 sheet1sheet1 改成目录所在改成目录所在的的sheetsheet，如果目录在如果目录在 sheet3sheet3，那么就把那么就把1 1改成改成3 3就可以回到目录了。就可以回到目录了。此时最关键的是找到目录所在的此时最关键的是找到目录所在的 sheetsheet 编号是多少，编号是多少，方法如插图图系方法如插图图系所示步骤：所示步骤：鼠标放置这里，单击鼠标右键出现插图 2 对话框插图 110单击“查看代码（v）”出现插图 3 对话框插图 211此时查看以 sheet1（sheet1）结构显的内容就可知道你要找的重命名过后的sheet编号，“（sheet1）”就是重命名之后的表的名字，括号外的 sheet1 就是你想要找的 sheet 编号，如果你有命名目录在这里你就能看到目录的 sheet 是 sheet 几。插图 3当把语句输入之后就可以直接关闭图 15 的对话框，不需要进行保存操作，因为系统已经将语句自动保存。当关闭上述对话框之后，进行快捷键的设置。注意：注意：大家可能完成上述操作之后会去寻找保存按钮，大家可能完成上述操作之后会去寻找保存按钮，在此处该项操在此处该项操作是没有必要的，因为当按以上操作完成之后，单击作是没有必要的，因为当按以上操作完成之后，单击 X X退出时，退出时，系统系统默认已经对你的设置进行了保存，所以此处直接单击默认已经对你的设置进行了保存，所以此处直接单击 X X 退出即可。退出即可。12语句Sheet1.SelectSheet1.Range(a1).Select中 sheet1 是指目录所在的 sheet，一般情况下，创建目录的 sheet都是 sheet1，如果你不是在sheet1 创建的目录那么就把相应的数字改掉即可，比如说你想返回 sheet3，因为 sheet3 是你想创建目录的地方，那么就把 1 改为 3，也就是说你想让它返回 sheet x那么就把x 写成几。其中（”a1”）是指返回制定 sheet 之后，定位的单元格，你可以任意制定。一般定位在表的顶端 a1 处是比较常用的，也可以根据需要，改为 a2,e3,f5 等等，视情况而定。Step 5Step 5 创建返回快捷键创建返回快捷键创建快捷键的前两步入 step1 和 step2，因为你已经创建了返回目录的宏，所以选中该宏，如图 16 所示，选中该项，当该项呈现蓝色背景时，单击右侧菜单栏的“选项”，则出现图 17 所示图 1613单击“选项（o）”就会出现图 17 对话框当输入 A 之后就可以直接诶单击x 退出，此功能已经保存并生效，此时将退回到图 16 界面，然后在单击 x 直接退出注意“X”退出在这里，输入完之后直接单击“X”图 17当你输入之母之后，要记住快捷键是什么组合键，组合键使用的时候需要同时按下。如果是“ctrl+a”就需要同时按下“ctrl+a”，如果当你输入之母之后快捷键变为“ctrl+shift+a”的话，使用快捷键时，就需要“ctrl+shift+a”，之所以在 excel 里会出现输入字母a 后快捷键变为 ctrl+shift+a 原因大致如下：由于 excel 中的自带的快捷功能一般都是 ctrl+字母的形式，而且 26 个字母几乎在 excel 里都有默认的快捷功能，所以当在此处输入字母时，系统会自动变为ctrl+shift+字母的形式以便区分于excel 自带的快捷功能。当在上图空白处输入 A 时，将会出现图 18的结果。当输入 A 之后，单击 X 退出当前对话框，便出现图 16 界面，此时再单击 X退出，此时快捷键就设置成功。图 1814注意：注意：大家可能完成上述操作之后会去寻找保存按钮，大家可能完成上述操作之后会去寻找保存按钮，在此处该项操在此处该项操作是没有必要的，因为当按以上操作完成之后，单击作是没有必要的，因为当按以上操作完成之后，单击 X X退出时，退出时，系统系统默认已经对你的设置进行了保存，所以此处直接单击默认已经对你的设置进行了保存，所以此处直接单击 X X 退出即可。退出即可。完成上述步骤之后，你就可以尝试你建立的快捷键，同时按下ctrl+shift+A，就会回到目录表（sheet1）的 a1 单元格。第五步第五步保存设置保存设置当执行完上述操作之后，保存的时候就需要保存为“Excel 启用宏的工作簿(*.xlsm)”格式另存，这样下次打开时才能正常显示工作表目录。此外，打开工作簿时，Excel 2007 默认会禁用宏，得单击警告栏中的“选项”按钮，选中“启用此内容”单选项，确定后才能显示工作表目录。如果你没有将 excel 保存为上述格式，下次打开 excel 所有的功能就不能使用。15