Excel 仪表板 - 数据透视表

如果您的数据位于单个 Excel 表中，则可以使用 Excel 数据透视表按照所需的方式汇总数据。数据透视表是一个非常强大的工具，可用于对数据进行切片和切块。您可以使用一个紧凑的表格来跟踪、分析数十万个数据点，该表格可以动态更改，使您能够找到数据的不同视角。它是一个使用简单但功能强大的工具。

Excel 为您提供了一种从多个表、不同数据源和外部数据源创建数据透视表的更强大方法。它被命名为 Power PivotTable，在称为数据模型的数据库上工作。您将在其他章节中了解 Power PivotTable 和其他 Excel 电动工具，例如 Power PivotChart 和 Power View Reports。

数据透视表、Power PivotTables、Power PivotCharts 和 Power View 报表可以方便地在仪表板上显示大数据集的汇总结果。在尝试使用电动工具之前，您可以先掌握普通的数据透视表。

创建数据透视表

您可以从一系列数据或 Excel 表创建数据透视表。在这两种情况下，数据的第一行应包含列的标题。

您可以从一个空的数据透视表开始，然后从头开始构建它，或者使用 Excel 推荐的数据透视表命令来预览可能为您的数据定制的数据透视表，并选择一个适合您的目的的数据透视表。无论哪种情况，您都可以即时修改数据透视表，以深入了解手头数据的不同方面。

考虑以下数据范围，其中包含每个销售人员、每个区域以及一月、二月和三月的销售数据 -

销售数据

要从此数据范围创建数据透视表，请执行以下操作 -

  • 确保第一行有标题。您需要标题，因为它们将是数据透视表中的字段名称。

  • 将数据范围命名为 SalesData_Range。

  • 单击数据范围 - SalesData_Range。

  • 单击功能区上的插入选项卡。

  • 单击“表”组中的“数据透视表”。

将出现“创建数据透视表”对话框。

数据透视表

正如您所观察到的，在“创建数据透视表”对话框中的“选择要分析的数据”下，您可以从当前工作簿中选择表或范围，也可以使用外部数据源。因此，您可以使用相同的步骤从范围或表中创建数据透视表。

  • 单击选择表或范围。

  • 在表/范围框中，输入范围名称 - SalesData_Range。

  • 单击“选择要放置数据透视表的位置”下的“新建工作表”。

您还可以观察到，通过将此数据范围添加到数据模型，您可以选择分析多个表。数据模型是Excel Power Pivot 数据库。

创建数据透视表

  • 单击“确定”按钮。新的工作表将插入到您的工作簿中。新工作表包含一个空数据透视表。

  • 将工作表命名为 - Range-PivotTable。

新工作表

正如您所观察到的，数据透视表字段列表显示在工作表的右侧，其中包含数据范围中列的标题名称。此外，在功能区上，会出现数据透视表工具 - 分析和设计。

您需要根据要显示的数据选择数据透视表字段。通过将字段放置在适当的区域，您可以获得所需的数据布局。例如，要汇总销售人员一月、二月和三月的订单金额，您可以执行以下操作 -

  • 单击数据透视表字段列表中的“销售人员”字段并将其拖至“行”区域。

  • 单击数据透视表字段列表中的“月份”字段，并将其也拖动到“行”区域。

  • 单击“订单金额”并将其拖至“Σ VALUES”区域。

数据透视表字段

您的数据透视表已准备就绪。您只需在区域上拖动字段即可更改数据透视表的布局。您可以选择/取消选择数据透视表字段列表中的字段来选择要显示的数据。

过滤数据透视表中的数据

如果您需要关注数据透视表数据的子集，则可以根据一个或多个字段的值的子集来筛选数据透视表中的数据。例如，在上面的示例中，您可以根据“范围”字段过滤数据，以便仅显示所选区域的数据。

有多种方法可以过滤数据透视表中的数据 -

  • 使用报告过滤器进行过滤。
  • 使用切片器进行过滤。
  • 手动过滤数据。
  • 使用标签过滤器进行过滤。
  • 使用值过滤器进行过滤。
  • 使用日期过滤器进行过滤。
  • 使用 Top 10 过滤器进行过滤。
  • 使用时间轴进行过滤。

您将在本节中了解报表过滤器的用法，在下一节中了解切片器的用法。有关其他筛选选项，请参阅 Excel 数据透视表教程。

您可以将筛选器分配给其中一个字段，以便您可以根据该字段的值动态更改数据透视表。

  • 将区域字段拖至过滤器区域。
  • 将字段“销售人员”拖至“ROWS”区域。
  • 将“月份”字段拖至“列”区域。
  • 将订单金额字段拖至 Σ VALUES 区域。
过滤数据

标签为“区域”的筛选器显示在数据透视表上方（如果数据透视表上方没有空行，数据透视表将被下推以为筛选器腾出空间）。

过滤区域

正如你所观察到的，

  • 销售人员值显示在行中。

  • 月份值显示在列中。

  • 区域过滤器显示在顶部，默认选择为“全部”。

  • 汇总值是订单金额之和。

    • 销售人员的订单金额总和显示在“总计”列中。

    • 每月订单金额总和显示在“总计”行中。

  • 单击区域过滤器中的箭头。

将出现包含区域字段值的下拉列表。

区域值

  • 选中“选择多个项目”框。所有值都会出现复选框。默认情况下，所有复选框均已选中。

  • 取消选中该框（全部）。所有的框都将被取消选中。

  • 选中复选框 - 南和西。

多个项目

  • 单击“确定”按钮。仅汇总有关南部和西部地区的数据。

所选项目

正如您所观察到的，在区域过滤器旁边的单元格中显示 - （多个项目），表明您选择了多个值。但从显示的报告中无法得知有多少值和/或哪些值。在这种情况下，使用切片器是过滤的更好选择。

在数据透视表中使用切片器

使用切片器进行过滤有很多优点 -

  • 您可以通过选择切片器的字段来拥有多个过滤器。

  • 您可以可视化应用过滤器的字段（每个字段一个切片器）。

  • 切片器将具有表示它所代表的字段的值的按钮。您可以单击切片器的按钮来选择/取消选择字段中的值。

  • 您可以直观地看到过滤器中使用的字段值（所选按钮在切片器中突出显示）。

  • 您可以对多个数据透视表和/或数据透视图使用通用切片器。

  • 您可以隐藏/取消隐藏切片器。

要了解切片器的用法，请考虑以下数据透视表。

切片机的使用

假设您想根据字段过滤此数据透视表 - 区域和月份。

  • 单击功能区上的“数据透视表工具”下的“分析”选项卡。
  • 单击“过滤器”组中的“插入切片器”。

将出现“插入切片器”对话框。它包含您数据中的所有字段。

  • 选中“区域”和“月份”框。
分析数据透视表

  • 单击“确定”按钮。默认情况下，每个选定字段的切片器都会显示选定的所有值。切片器工具出现在功能区上，用于处理切片器设置、外观和感觉。

切片工具

正如您所观察到的，每个切片器都有它所代表的字段的所有值，并且这些值显示为按钮。默认情况下，会选择字段的所有值，因此所有按钮都会突出显示。

假设您只想显示南部和西部地区以及二月和三月的数据透视表。

  • 单击“区域切片器”中的“南”。只有南部才会在切片器 - 区域中突出显示。

  • 按住 Ctrl 键并单击区域切片器中的 West。

  • 单击月份切片器中的二月。

  • 按住 Ctrl 键并单击“月份切片器”中的“三月”。切片器中选定的值会突出显示。数据透视表将针对所选值进行汇总。

选定的值

要从过滤器中添加/删除字段的值，请按住 Ctrl 键并单击相应切片器中的这些按钮。