Excel如何使用不同的数据集进行分组

在这个简单的 Microsoft Excel 教程中，我将向您展示如何使用各种方法针对不同的数据集对 Excel 中的行进行分组。

您可能希望对任意大小和结构的数据集中的行进行分组，以便更好地组织、更高效地进行数据分析、更轻松地进行数据汇总、更好地进行报告并提高可读性。

但是，如果您打算手动选择多个传染性或非传染性行并将它们分组到不同或相同的工作表中，那么这不是一种有效的方式。此外，它不会达到 Excel 分组行功能的目的。

请阅读本文直至结尾并按照这些方法操作。在这里，我介绍了半自动和全自动的方法，以尽可能少的步骤和时间对行进行分组。

使用自动轮廓命令

Excel 中的“自动大纲”命令会自动为您的数据创建大纲（前提是格式正确）。如果您的数据以结构化格式组织良好，则可以成功使用它。通常，它应该是分层数据，其中摘要行与详细数据不同。

例如，您可以在上面的数据集中应用该命令。它在几行数值数据后包含一个摘要行。摘要行还包含SUM函数来计算前几行的总数。

按照上述格式组织您的工作表数据并选择整个数据集。

现在，转到“数据”选项卡并单击“大纲命令块”的“组”下拉命令。

单击上下文菜单中的“自动轮廓”选项。

Excel 将自动对选定工作表中的摘要和详细数据行进行分组，如上图所示。

如果您单击1大纲导航面板，您将只会看到摘要行。

相反，如果您单击2同一个面板，您将看到包括摘要行在内的所有数据行。

这项技术的缺点是，输入数据集必须高度组织成层次结构。您无法将此方法应用于任何数据集。

如果您尝试在无组织的数据集上使用它，您将收到如上所示的错误。

使用群组工具

您是否需要在不重组数据集的情况下快速对 Excel 中的行进行分组？在这种情况下，您可以使用“分组”工具。

使用此 Excel 命令，您可以选择数据集中的任意数量的行，将它们分组到一个包中。然后，您可以在第一个组中选择更多行来创建另一组行。最后，您可以创建一个逐步的行层次结构，以便一次只显示一组您想要查看的行。

下面让我向您展示如何将上述数据集的行分组到以下组中：

一月份可见的行
一月和二月的可见行
所有行均可见

首先，选择列标题行下方的整个数据集，然后单击数据>大纲命令块中的“分组”命令。

在“组”对话框中，选择“行”，然后单击“确定”。

它将为整个数据集创建一个大纲。

现在选择二月和三月的行并再次对这些行应用 Group 命令。第三个大纲将显示在数据集中。

在这里，如果您单击第二个大纲，则只有一月份的行会可见。

最后，选择三月行并再次应用组命令。这将创建第四个轮廓。

在这里，如果你点击第三个大纲，你会看到一月和二月的行。

第4 个大纲使3 月份的行与第 2 个和第 3 个大纲一起可见，从而使所有数据集可见。

Excel 组行热键

如果需要加快Excel分组行的过程，可以选择目标行，然后按键盘上的Alt++键。ShiftRight Arrow

当您必须在 Excel中删除行组时，可以按Alt++快捷键。ShiftLeft Arrow

使用小计命令

假设您有一个组织的购买清单，如上图所示。

您想要根据部门对所有购买商品的行进行分组，显示按部门划分的购买的小计，最后显示所有购买的总计。

在这种情况下，您可以使用大纲命令块的Subtotal工具对 Excel 中的行进行分组。

选择整个输入数据集以及列标题。Subtotal工具将使用列标题创建 Excel 组行并显示计算选项。

单击“数据”选项卡中的“小计”命令。

您应该会看到“小计”对话框。在这里，请按照以下概述进行操作：

每次更改时：这是您要根据其创建小计行的列标题。在此示例中，它是部门列。
使用函数：使用此下拉菜单选择要在小计行中使用的计算。我将使用SUM函数。
添加小计到：勾选要应用小计计算的列标题。基本上，Excel 将在每个小计间隔对所选列中的值执行 SUM。我在本练习中选择了“成本”列。
勾选数据下方的摘要复选框。
其余项目保持默认设置状态。

单击“小计”对话框中的“确定”以应用配置。

Excel 现在将转换输入数据集以显示两个轮廓。

第一个大纲折叠或展开数据集的所有行。如果折叠，则仅显示总计行。

第二个大纲折叠了数据行并仅显示小计行。

第二个大纲中的加号图标允许您折叠或展开每个小计摘要行的项目。

使用合并工具

假设您有三个业务站点的设施、IT和运营费用工作表。

您希望在不同的工作表中对所有这些费用进行汇总，并对分组行进行概述。在这种情况下，您可以使用“数据”选项卡的“数据工具”命令块中的“合并”工具。

如果您严格遵循下面概述的说明，使用合并工具非常简单：

转到目标工作表并单击A1。现在，转到数据>数据工具命令块 > 单击合并。

合并对话框将会弹出。

单击“函数”下拉菜单选择一个函数。我在本练习中选择了SUM函数。

点击“引用”字段，转到另一张工作表中的第一个费用数据集。选择整个数据集并点击“添加”按钮。

现在，对站点其余费用数据重复相同的步骤。

合并对话框将如上面的屏幕截图所示。

勾选顶行和左列的复选框。另外，勾选创建链接…复选框。

单击“确定”即可创建 Excel 组行。

单击大纲扩展图标或加号图标可查看底层分组行。

使用名称管理器工具

假设您的数据集很小到中等大小，并且在当前工作表显示范围内显示良好。此时，如果您想专注于特定的一组行而不隐藏其他行，则可以使用名称管理器工具。

假设您正在处理的数据集与上述示例类似。您想将NY、CA和FL的销售数据分组。

选择NY 的数据行和小计行，然后单击“公式”选项卡。

单击“定义名称”命令块内的名称管理器命令。

在名称管理器对话框中，单击新建按钮。

自定义新名称字段的值，如名称、范围、注释等。如果需要更改选定的单元格范围，请单击引用字段并从活动工作表中突出显示新的单元格范围。

单击“确定”以创建NY_Sales命名范围。

现在，对CA Sales和FL Sales 的行重复相同的步骤。

名称管理器对话框中将有三个新的命名范围。单击关闭按钮。

现在，单击名称框下拉菜单并选择一个命名范围，如CA_Sales。

此操作将选择并突出显示与反映CA 商店A7:D11销售数据的行相对应的单元区域。

因此，您快速突出显示了重要的行，而无需隐藏所有其他行。您可能不会立即需要它们，但您也不想隐藏它们。

此方法的优点是您可以对所选数据集中不连续的行进行分组和突出显示。您只需要学习在 Microsoft Excel 中突出显示每隔一行的各种方法。

使用数据透视表

假设您正在处理按部门划分的费用数据集。其中，行不是按类似部门组织的。如果您需要按不同列标题的值对行进行分组，则可以使用数据透视表工具。

例如，上面是一个示例数据集。其中部门列内的项目是混合的。您希望通过以不同角度对行进行分组来查看数据集，例如按单价、单位或成本。

要使用数据透视表从与部门名称匹配的分组行中获取数据洞察，而无需手动对行进行分组，请突出显示整个数据集。

单击“插入”选项卡，然后从“表格”命令块中选择“数据透视表”。

在数据透视表对话框中选择现有工作表选项，然后选择活动工作表上的单元格作为目标。单击确定以应用配置。

您应该在右侧看到数据透视表字段导航面板。

将部门项目拖放到行字段。

假设您想要根据按“成本”标题分组的行获取数据洞察。将“成本”项目拖放到“值”字段中。

您应该看到通过在“部门”列下对相似的行进行分组来以表格形式表示“成本总和”。

类似地，您可以通过将“单位”项目拖放到“值”字段来计算按部门购买的物品的总单位数。

使用 Power Query

Power Query还允许您通过对从 Excel 导入的数据集中的行进行分组来创建数据洞察。但是，由于 Excel 的行处理限制，从外部数据库导入大型数据库更为有用，因为 Excel 工作表无法容纳这些数据库。

📒阅读更多：Excel 可以处理多少行？

如果要将外部数据集导入Power Query，请单击“数据”选项卡的“获取和转换数据”内的“获取数据” 下拉菜单。

将光标悬停在“来自数据库”菜单上，然后从溢出菜单中选择一个选项，例如“来自 Oracle 数据库”。

在当前练习中，我将数据集从活动的 Excel 工作表导出到Power Query。为此，我选择了目标数据集并单击了“数据”选项卡中的“从表/范围”命令。

以上介绍了Power Query如何组织您导入的数据集。

现在，转到“转换”选项卡并单击“分组依据”命令。

您现在应该可以看到Group By向导。

使用 Excel VBA

如果您想自动化 Excel 分组行过程，可以使用 VBA 编程。这真的很简单！您只需按照几个步骤即可创建宏。我将分享这些宏中要使用的编程脚本。

首先，阅读以下 Excel 教程，了解如何从头开始创建宏：

📒阅读更多：如何使用在线找到的 VBA 代码

如果您已准备就绪，请使用以下脚本打开用于在 Excel 中对行进行分组的“Subtotal”对话框：

Sub ShowSubtotalDialogBox()
    ' Declare a Range object
    Dim TargetRange As Range

    ' Show an input box and set the TargetRange to the selected range
    On Error Resume Next ' In case the user cancels the input box
    Set TargetRange = Application.InputBox("Please select the target dataset using your mouse:", Type:=8)
    On Error GoTo 0 ' Reset error handling

    ' Check if a range was selected
    If Not TargetRange Is Nothing Then
        ' Select the range
        TargetRange.Select

        ' Show the Subtotal dialog box
        Application.Dialogs(xlDialogSubtotalCreate).Show
    Else
        MsgBox "No range selected. Please try again.", vbInformation
    End If
End Sub

使用上述代码创建 VBA 宏后，按Alt+F11调出宏对话框。

在那里，单击ShowSubtotalDialogBox宏并点击运行。

您将看到一个输入框。使用鼠标在活动工作表上选择一个数据集。

单击“确定”，Excel 将显示“小计”对话框。

现在，按照本 Excel 教程前面的“小计”部分中提到的步骤对行进行分组。

结论

到目前为止，您已经学习了在 Excel 中对行进行分组的八种最佳经过尝试和测试的方法。

如果您处理的是中小型数据集，则可以使用 Excel 用户界面命令，例如自动大纲命令、分组工具和小计命令。

如果您不想隐藏某一组行而只想突出显示特定行，则可以使用名称管理器工具。

要从不同工作表中类似组织的数据集创建大纲和数据洞察，您可以使用合并工具。

如果您希望对行进行分组并创建数据可视化或从类似分组的行中提取数据见解，则可以使用基于Power Query和数据透视表的方法。

最后，如果您希望自动化 Excel 分组行过程，则可以使用基于Excel VBA的技术。

如果本文帮助您了解如何在 Excel 中对行进行分组，请在下方评论。如果您知道我可能遗漏的更好更简单的方法，请在评论中提及。