当您的Excel工作簿具有多个布局和结构相同的工作表时,您可以通过将相似工作表分组来简化工作。更具体地说,一旦您了解了如何在Excel中对工作表进行分组,您就可以通过仅更改其中一个分组工作表来将更改应用于所有分组工作表中的相应单元格。
例如,如果您更改一张工作表的行高和宽度(change the row height and width),那么分组工作表的行高和宽度也会改变。
为什么要在Excel中对(Excel)工作表进行分组(Group Worksheets)?
您可以在分组的工作表上执行多项任务,例如:
- 修改(Modify)或添加数据并插入公式。
- 同时打印它们。
- 同时移动(Move)、复制或删除信息。
由于Excel不允许添加多个工作表,您可以将 10 个工作表分组,右键单击它们,然后选择“插入(Insert)”选项以一次插入 10 个工作表。
如何在 Excel 中对工作表进行分组
我们将使用以下示例来了解如何在Excel中对工作表进行分组。
假设(Say)您已根据他们销售的产品将销售团队分成三组,并且您拥有这些单独团队的销售数据。销售数据位于三个单独的工作表中,每个工作表包含一个产品的数据。您希望计算每位员工的佣金,而无需在多张工作表上手动输入公式。
您可以将工作表分组,而不是单独计算每张工作表上的佣金。
- 按住Ctrl 按钮(Ctrl button)。
- 单击(Click)要分组的工作表。分组的工作表变为白色,而未分组的工作表继续显示为灰色。当您在其中一个分组工作表中时,您会看到标题栏中添加了“组”一词。(Group)
- 添加公式以计算一名员工的佣金(C 列,第 2 行),最好在第一行中,以便您可以将公式拖到后续单元格中。
请注意(Notice),我们已将所有三个工作表分组,因为我们希望将更改应用于所有工作表。
- 选择刚刚输入公式的单元格并拖动边框选择以将公式应用于整个单元格区域。这样(Doing)做会将相同的更改应用于所有分组的工作表。
- 验证更改是否也出现在其他分组的工作表中。如果你做的一切都正确,那么所有三张表的 D 列都应该填满应支付给员工的佣金金额。
如何在Excel中对(Excel)所有工作表进行分组(Group All Worksheets)?
如果您正在处理包含几十个工作表的工作簿,则可能需要一段时间(可能还有阿司匹林)来单独选择所有工作表。相反,Excel有一个“选择所有工作表”(Select All Sheets )选项,可让您一次对所有工作表进行分组。
您需要做的就是右键单击任何一个工作表选项卡,然后点击Select All Sheets。
注意:(Note:)使用此选项的问题在于,在工作表之间导航会自动取消选择(或取消组合)所有工作表,这与上一个选项(我们选择单个工作表)不同,您可以在工作表之间切换而无需取消组合。
如何移动(Move)、复制(Copy)、删除(Delete)、打印(Print)或隐藏分组工作表(Hide Grouped Worksheets)
您可以对分组工作表应用大量Excel操作,例如移动、复制甚至隐藏工作表(hide sheets)。通常,它的工作方式与普通工作表相同。
移动或复制分组工作表(Move or Copy Grouped Worksheets)
将工作表分组后:
- 右键单击(Right-click)其中一个并选择移动或复制(Move or Copy)。
- 从下拉列表中选择目标书籍,然后选择要移动或复制分组工作表的位置。如果要复制,请选中底部的创建副本复选框,然后选择(Create a copy)确定(OK)。
删除分组工作表(Delete Grouped Worksheets)
您也可以以相同的方式删除分组的工作表。将工作表分组后,右键单击其中一个分组的工作表并选择Delete。
打印分组工作表(Print Grouped Worksheets)
将工作表分组后,按Ctrl + P。您现在将看到打印(Print)选项。向下滚动(Scroll)到Settings并选择Print Active Sheets。
在您选择打印(Print)之前,请查看右侧的预览以确保您打印的是正确的纸张。确认后,选择Print。
隐藏分组工作表(Hide Grouped Worksheets)
您还可以一次隐藏分组的工作表。
- 首先对工作表进行分组。
- 从顶部功能区中,选择Home,然后从Cells组中选择Format 。
- 选择Format后,您将看到一个下拉菜单。选择隐藏和取消隐藏(Hide & Unhide )>隐藏工作表(Hide Sheet)。
这将隐藏所有分组的工作表。
如何在 Excel 中取消组合工作表
如果要取消组合所有分组的工作表,只需右键单击任何工作表并选择Ungroup Sheets。
如果您尚未对工作簿中的所有(all)工作表进行分组,单击任何未分组的工作表也会取消分组的工作表。
如果要取消组合一些工作表,请按住 Ctrl(press-hold Ctrl)并单击要取消组合的工作表。
通过在Excel(Excel)中对工作表进行分组来节省时间(Time)
对工作表进行分组通常可以节省您在工作表之间复制和粘贴公式或执行其他重复性任务的时间。不过, Excel(Excel)是一个功能丰富的程序,并且总有一些捷径可以让 Excel 中的事情变得更简单(make things easier in Excel)。例如,如果您正在处理具有大量工作表的工作簿,则有多种方法可以在工作表之间快速切换。(switch between worksheets)
How to Group Worksheets in Excel
When your Excel workbook has multiple sheets identical in laуoυt and struсtυre, you can streamline your wоrk bу grouрing similar sheets. Τo be more specifiс, oncе you learn how to group worksheets in Excel, you can apply a change tо correѕponding cells in all the groυped shеets by changіng just one of the grouped sheets.
For instance, if you change the row height and width for one sheet, it will change for the grouped sheets as well.
Why Would You Want to Group Worksheets in Excel?
You can perform several tasks on grouped sheets, such as:
- Modify or add data and insert formulas.
- Print them at the same time.
- Move, copy or delete information simultaneously.
Since Excel doesn’t allow adding multiple sheets, you could group 10 sheets, right-click on them, and select the Insert option to insert 10 sheets at once.
How to Group Worksheets in Excel
We’ll use the following example to see how to group worksheets in Excel.
Say you’ve divided your sales team into three groups based on the product they sell, and you have sales data for those individual teams. The sales data is in three separate worksheets, each worksheet containing data for one product. You want to calculate the commission for each employee without having to enter formulas on multiple sheets manually.
Instead of calculating commissions on each sheet separately, you could group the worksheets.
- Press-hold the Ctrl button.
- Click on the sheets you want to group. The grouped sheets turn white, while ungrouped sheets continue to appear grey. You’ll see the word Group added to the title bar when you’re in one of the grouped sheets.
- Add the formula to calculate the commission for one employee (column C, row 2), preferably in the first row, so that you can drag the formula into subsequent cells.
Notice that we’ve grouped all three sheets since we’d like to apply the changes to all sheets.
- Select the cell you just entered the formula in and drag the border selection to apply the formula to the entire cell range. Doing so will apply the same changes to all the grouped sheets.
- Verify that the changes also appear in other grouped worksheets. If you did everything correctly, all three sheets should have their D column populated with the commission amount due to the employees.
How to Group All Worksheets in Excel?
If you’re dealing with a workbook with several dozen worksheets, it could take you a while (and perhaps an aspirin) to select all worksheets individually. Instead, Excel has a Select All Sheets option that allows you to group all sheets at once.
All you need to do is right-click on any one sheet tab and tap Select All Sheets.
Note: The catch with using this option is that navigating among worksheets automatically deselects (or ungroups) all worksheets, unlike in the last option (where we selected individual worksheets), where you could switch between sheets without ungrouping them.
How to Move, Copy, Delete, Print, or Hide Grouped Worksheets
You can apply plenty of Excel operations to grouped sheets like move, copy, or even hide sheets. Usually, it works the same way as it would for normal worksheets.
Move or Copy Grouped Worksheets
Once you’ve grouped the worksheets:
- Right-click on one of them and select Move or Copy.
- Select the target book from the drop-down and select the position where you want to move or copy the grouped sheets. If you want to copy, select the Create a copy checkbox at the bottom and then select OK.
Delete Grouped Worksheets
You can also delete grouped sheets the same way. When the worksheets are grouped, right-click on one of the grouped sheets and select Delete.
Print Grouped Worksheets
When the worksheets are grouped, press Ctrl + P. You’ll now see the Print options. Scroll down to Settings and select Print Active Sheets.
Before you select Print, look at the preview on the right to make sure you’re printing the correct sheets. Once you’ve confirmed this, select Print.
Hide Grouped Worksheets
You can also hide grouped sheets at once.
- Start by grouping the sheets.
- From the top ribbon, select Home, and select Format from the Cells group.
- Once you select Format, you’ll see a drop-down menu. Select Hide & Unhide > Hide Sheet.
This will hide all the grouped sheets.
How to Ungroup Worksheets in Excel
If you want to ungroup all the grouped worksheets, just right-click on any worksheet and select Ungroup Sheets.
If you haven’t grouped all worksheets in the workbook, clicking on any of the ungrouped worksheets will also ungroup the grouped worksheets.
If you want to ungroup a few worksheets, press-hold Ctrl and click on the sheets you want to ungroup.
Save Time by Grouping Worksheets in Excel
Grouping worksheets often saves you the time you’d otherwise spend copying and pasting formulas across sheets or performing other repetitive tasks. Excel is a feature-rich program, though, and there are always shortcuts to make things easier in Excel. For instance, if you’re working on a workbook with a large number of sheets, there are several ways to switch between worksheets quickly.