excel宏 拆分工作表
If you're setting up a workbook for other people to use, they'll appreciate it if you make it easy to move around in the file. You can create a table of contents on the first sheet, and that will get them off to a good start. But then what?
如果您要设置一个供其他人使用的工作簿,并且您可以轻松地在文件中四处移动,他们将不胜感激。 您可以在第一张纸上创建目录 ,这将使它们有一个良好的开端。 但是那又怎样呢?
Once they're on one of the other sheets, how can they get back to the table of contents, or go directly to a different sheet? We'll add a drop down list of sheets, to make it easy to go to the one that you need.
一旦放在另一张纸上,他们如何返回目录或直接转到另一张纸? 我们将添加一个工作表下拉列表,以使您轻松找到所需的工作表。
浏览捷径 (Navigate With Shortcuts)
To navigate through a large workbook, you can use keyboard or mouse shortcuts:
要浏览大型工作簿,可以使用键盘或鼠标快捷键:
- Ctrl+Page Up to go to the next sheet Ctrl + Page Up转到下一页
- Ctrl+Page Down to go the previous sheet Ctrl + Page Down转到上一页
- Right-click on the sheet navigation arrows, to see a list of sheets 右键单击工作表导航箭头,以查看工作表列表
But how do you really find a sheet most of the time? I usually click the sheet navigation arrows, and look for the sheet name as the sheets fly past. That's not too efficient!
但是,您大部分时间如何真正找到工作表? 我通常单击工作表导航箭头,并在工作表飞过时查找工作表名称。 不太有效!
成品纸选择器 (The Finished Sheet Selector)
To make it easy to go to a specific worksheet, we'll build a drop down list that shows the sheet names. In the cell below that, a hyperlink that will take you to the selected sheet.
为了方便进入特定的工作表,我们将构建一个下拉列表来显示工作表名称。 在该单元格下面的单元格中,将有一个超链接,将您带到所选工作表。
![hyperlinknavigation05 hyperlinknavigation05](https://i-blog.csdnimg.cn/blog_migrate/fc8715225cd87a742104994b9965fdce.png)
创建图纸列表 (Create a List of Sheets)
The first step is to create a list of sheets that people need to go to. You probably don't need to list all the sheets in the file – don't include any Admin sheets where you keep the lookup lists, etc.
第一步是创建人们需要浏览的工作表列表。 您可能不需要列出文件中的所有工作表-保留查找列表的地方也不要包括任何管理工作表,等等。
Tip: Since this list will be use for selecting a sheet, you could sort the sheet names alphabetically, to make them easier to find in a long list.
提示:由于此列表将用于选择工作表,因此您可以按字母顺序对工作表名称进行排序,以使在长列表中更容易找到它们。
In my sample file, I listed four worksheets, and formatted the list as a named Excel table. The table is named tblSheets.
在示例文件中,我列出了四个工作表,并将列表格式化为一个命名的Excel表 。 该表名为tblSheets。
![hyperlinknavigation03 hyperlinknavigation03](https://i-blog.csdnimg.cn/blog_migrate/3dfd6317d5cde2b24118e8e39466e1cb.png)
命名清单 (Name the List)
Next, I selected the list of sheet names (not the heading), and named the list – SheetList. I'll use that name when creating the drop down list.
接下来,我选择了工作表名称(而不是标题) 列表 ,并将其命名为 – SheetList。 创建下拉列表时,我将使用该名称。
![hyperlinknavigation02 hyperlinknavigation02](https://i-blog.csdnimg.cn/blog_migrate/a43bf9d1b124ee890488e627e48ec347.png)
添加工作表下拉列表 (Add a Drop Down List of Sheets)
To let people choose the sheet they want to go to, you can use a data validation drop down list.
为了让人们选择他们想要去的工作表,您可以使用数据验证下拉列表。
In this example, the drop down list will go in cell A1.
在此示例中,下拉列表将进入单元格A1中。
- Select cell A1, and on the Excel Ribbon, click the Data tab 选择单元格A1,然后在Excel功能区上,单击“数据”选项卡
- Click the top of the Data Validation command 单击数据验证命令的顶部
- On the Settings tab, from the Allow drop down, choose List 在设置选项卡上,从允许下拉列表中,选择列表
- Click in the Source box, and on the keyboard, press F3, to open the Paste Name window. 在“源”框中单击,然后在键盘上按F3,打开“粘贴名称”窗口。
- Select the SheetList name, and click OK 选择SheetList名称,然后单击确定
- Click OK to create the Data Validation list. 单击确定以创建数据验证列表。
![hyperlinknavigation08 hyperlinknavigation08](https://i-blog.csdnimg.cn/blog_migrate/d39b3fef611fa05c1fedf2dce802840e.png)
为所选工作表添加超链接 (Add a Hyperlink for Selected Sheet)
Then, in cell A2, you'll add a HYPERLINK formula to create a link to the selected sheet.
然后,在单元格A2中, 您将添加一个HYPERLINK公式以创建指向所选工作表的链接。
The first argument for the HYPERLINK function is the link location – the place that the link will take you
HYPERLINK函数的第一个参数是链接位置 –链接将带您到的位置
The second argument is the "Friendly Name" -- the text that will show in the cell.
第二个参数是“友好名称” -将在单元格中显示的文本。
=HYPERLINK("#'" & A1 & "'!A1","Go to sheet")
= HYPERLINK( “#'”&A1&“'!A1” , “转到工作表” )
- The pound sign (#) at the start of the address indicates that the location is within the current file. 地址开头的井号(#)表示该位置在当前文件中。
- This link will take you to cell A1 on the sheet that was selected from the drop down list. 该链接将您带到从下拉列表中选择的工作表上的单元格A1。
- The text in the link will say "Go to sheet". 链接中的文本将显示“转到工作表”。
![hyperlinknavigation07 hyperlinknavigation07](https://i-blog.csdnimg.cn/blog_migrate/d0567a2827c228b0f631fd1b141184e5.png)
测试链接 (Test the Link)
To test the hyperlink, select a sheet name from the drop down list in cell A1. Then, click the link in cell A2, and you should go to the sheet whose name you selected.
要测试超链接,请从单元格A1的下拉列表中选择一个工作表名称。 然后,单击单元格A2中的链接,然后应转到所选名称的工作表。
复制链接 (Copy the Link)
Once the drop down list and hyperlink are set up and working correctly, you can copy them to all the other sheets. Then, freeze the top 2 rows of each worksheet, so that the navigation cells are always visible.
下拉列表和超链接建立并正常工作后,您可以将它们复制到所有其他工作表中。 然后,冻结每个工作表的前2行,以使导航单元始终可见。
下载样本文件 (Download the Sample File)
To get the sample file, go to the Excel Sample files page on my website. In the Functions section, look for FN0044 - Hyperlink Formulas for Worksheet Navigation.
要获取示例文件,请转到我的网站上的Excel示例文件页面 。 在“功能”部分中,查找“ FN0044-工作表导航的超链接公式”。
The sample file also has formulas to show the sheet name, and hyperlink formulas that will take you to the next or previous sheet.
该示例文件还具有显示工作表名称的公式,以及将您带到下一个或上一个工作表的超链接公式。
![hyperlinknavigation01 hyperlinknavigation01](https://i-blog.csdnimg.cn/blog_migrate/3d835623aa8934345e54870e3dd95fa0.png)
翻译自: https://contexturesblog.com/archives/2016/03/17/excel-sheet-selector-with-no-macros/
excel宏 拆分工作表