Excel是Office套件中的应用程序之一,在工作场所以及家庭或家庭办公室中同样有用。Excel能够存储大量信息;有时,这些信息变得过于笨拙,以至于随着文件的增长,使用存储在Excel工作表中的数据变得越来越麻烦。
在商业环境中更常使用,家庭用户通常不知道您可以对Excel工作表中的行和列进行分组和折叠,以在需要时隐藏信息。当您在电子表格中使用公式来汇总信息并且您大部分时间只对这些汇总感兴趣时,这尤其有用。
在Excel 工作表中对(Excel Worksheet)行(Rows)和列(Columns)进行分组
假设(Suppose)您有一个如下图所示的Excel工作表。(Excel)请注意(Notice),有许多包含数据的单元格,并且每组数据都汇总在一个单独的单元格(B6、B13和B20)中。
您遇到的麻烦是单元格中的数据( B1 到 B5、 B8 到B12和B15到B19)对您没有用处;您只关心每组数据的总值、平均值和最大值。
使用 Excel 的Group功能,您可以将这些数据分别组合在一起并将它们折叠起来。当您需要查看或编辑数据时,您可以展开组并再次使用它们。
例如,让我们将第 8 行到第 12 行组合在一起,将它们折叠起来,并只保留第 13 行中的平均值(Average)可见。首先用鼠标选择第 8 到 12 行。单击(Click)功能区(Ribbon)上的数据(Data)选项卡并找到功能区(Ribbon)中标记为大纲(Outline)的部分。单击标有组(Group)的按钮,然后从菜单中选择组。(Group)
您会立即注意到您可能从未见过的Excel工作表的更改。在第 8 行到第 12 行旁边,有一条线将这些行连接到左侧,第 13 行旁边有一个减号。这表示单元格 8 到 12 是当前扩展组的一部分。
如果单击第 13 行旁边的减号,第 8 行到第 12 行将折叠,并且减号变成加号。这也表示第 8 行到第 12 行是组的一部分,并且该组当前已折叠。
单击加号将再次展开该组。另外,请注意,折叠时,工作表中的行从第 7 行向右移动到第 13 行,这肯定表明工作表中的某些行已被分组并且当前已折叠。
如果我们对第 1 到 5 行和第 15 到 19 行执行相同的操作,我们会看到当这些行被分组和折叠时,原始数据被隐藏在视图中,从而更容易找到重要的单元格。请注意(Notice),工作表左侧装订线中的三个加号表示当前存在三个折叠的行组。
Excel中的分组和折叠单元格不限于行;您也可以对列进行分组和折叠。甚至可以在组内创建组,以更好地组织在臃肿的Excel(Excel)工作表中难以处理的数据。
Group Rows and Columns in an Excel Worksheet
Excel is one of the applications іn the Office suite that iѕ еqually useful in the workplace and in a home or home office. Excel is cаpable of storing great amounts of information; sometimeѕ that infоrmation becomes too unwieldy sυch that using the data stored in an Excel worksheet becomes more and more of а hasѕle over time as the file grоws.
Used more often in commercial settings, home users are often unaware that you can group and collapse both rows and columns in an Excel worksheet to hide information until it is needed. This is especially useful when you use formulas in a spreadsheet to summarize information and you are only interested in those summaries most of the time.
Grouping Rows and Columns in an Excel Worksheet
Suppose you have an Excel worksheet that looks like the one pictured below. Notice that there are a number of cells that contain data and that each set of data is summarized in a separate cell (B6, B13, and B20).
The trouble you are having is that the data in the cells (B1 to B5, B8 to B12, and B15 to B19) are not useful to you on a regular basis; you only care about the total, average, and maximum values for each set of data respectively.
Using Excel’s Group function, you can group these data together separately and collapse them out of view. When you need to view or edit the data, you can expand the groups and work with them again.
As an example, let’s group rows 8 through 12 together, collapse them, and leave only the Average in row 13 visible. Begin by selecting rows 8 through 12 with your mouse. Click on the Data tab on the Ribbon and locate a section of the Ribbon labeled Outline. Click on the button labeled Group and select Group from the menu.
You’ll immediately notice a change to the Excel worksheet that you may never have seen before. Next to rows 8 through 12, there is a line connecting these rows to the left and there is a minus sign next to row 13. This signifies that cells 8 through 12 are part of a group that is currently expanded.
If you click the minus sign next to row 13, rows 8 through 12 will collapse and the minus sign turns into a plus sign. This also signifies that rows 8 through 12 are part of a group and that the group is currently collapsed.
Clicking on the plus sign will expand the group again. Also, notice that when collapsed, the rows in the worksheet go right from row 7 to row 13, a sure sign that some of the rows in the worksheet have been grouped and are currently collapsed.
If we do the same for rows 1 through 5 and rows 15 through 19, we see that when these rows are grouped and collapsed, the original data is hidden from view making the important cells more easily found. Notice that the three plus signs in the left hand gutter of the worksheet indicate that there are currently three collapsed groups of rows.
Grouping and collapsing cells in Excel is not limited to rows; you can group and collapse columns as well. It is even possible to create groups within groups to better organize data that has become difficult with which to work in a bloated Excel worksheet.