Excel Dashboards 简明教程

Excel Dashboards - PivotTables

如果您将数据放入单个 Excel 表中，则可以使用 Excel 数据透视表以所需的方式汇总数据。数据透视表是一个非常强大的工具，您可以使用它来切分和整理数据。您可以使用一个紧凑的表格跟踪和分析数十万个数据点，该表格可以动态更改，以便您从数据的不同角度发现它们。这是一个简单、但功能强大的工具。

Excel 为您提供了一种更强大的方法，可以从多个表格、不同数据源和外部数据源创建数据透视表。它被命名为“数据透视表”，它使用其自己的数据库（称为数据模型）。您将在其他章节中了解数据透视表和其他 Excel 强大的工具，例如数据透视图表和 Power View 报表。

数据透视表、动力数据透视表、动力数据透视图表和 Power View 报表非常方便，可以在仪表板中显示来自大型数据集的汇总结果。在尝试使用这些强大工具之前，您可以在普通数据透视表上获得熟练度。

Creating a PivotTable

您可以创建数据透视表，方法是从数据范围或 Excel 表中创建。在这两种情况下，数据的首行应包含列标题。

您可以从一个空数据透视表开始，并从头开始构建它，或使用 Excel 推荐的数据透视表命令预览适合您数据的可能自定义数据透视表，并选择一个适合您目的的数据透视表。在任何一种情况下，您都可以立即修改数据透视表，以深入了解手头数据的不同方面。

考虑以下包含每个销售人员在每个地区和 1 月、2 月和 3 月的销售数据的数据范围 −

sales data

要从这个数据范围创建数据透视表，请执行以下操作 −

  1. 确保首行包含标题。您需要标题，因为它们将成为数据透视表中的字段名。

  2. 将数据范围命名为 SalesData_Range。

  3. 单击数据范围 − SalesData_Range。

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

  5. 单击表格组中的数据透视表。

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

pivottable

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

  1. 单击“选择表格或区域”。

  2. 在“表格/区域”框中，键入区域名称 − SalesData_范围。

  3. 在“选择您要放置数据透视表报告的位置”下单击“新工作表”。

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

create pivottable

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

  2. 命名工作表 − 范围-数据透视表。

new worksheet

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

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

  1. 在“数据透视表字段”列表中单击“销售人员”字段，并将其拖动到“行”区域。

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

  3. 单击“订单金额”，并将其拖动到“∑ 值”区域。

pivottable fields

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

Filtering Data in PivotTable

如果您需要关注数据透视表数据的一个子集，则可以基于一个或多个字段值的子集对数据透视表中的数据进行筛选。例如，在上述示例中，您可以基于范围字段筛选数据，以便仅显示所选区域的数据。

有几种方法可以在数据透视表中筛选数据 −

  1. Filtering using Report Filters.

  2. Filtering using Slicers.

  3. Filtering data manually.

  4. Filtering using Label Filters.

  5. Filtering using Value Filters.

  6. Filtering using Date Filters.

  7. 使用前 10 名筛选器进行筛选。

  8. Filtering using Timeline.

您将在本部分了解报表筛选器的使用，并在下一部分了解切片器的使用。有关其他筛选选项，请参阅 Excel 数据透视表教程。

您可以为某个字段分配筛选器，以便您可以根据该字段的值动态更改数据透视表。

  1. 将字段区域拖到“筛选器”区域。

  2. 将字段销售人员拖动到“行”区域。

  3. 将“月”字段拖动到“列”区域。

  4. 将“订单金额”字段拖动到“∑ 值”区域。

filtering data

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

filter region

您可以观察到，

  1. 销售人员值显示在行中。

  2. 月值显示在列中。

  3. 区域筛选器显示在顶部，默认选择为全部。

  4. 汇总值为订单金额之和。以销售人员为基础的订单金额之和显示在“总计”列中。以月份为基础的订单金额之和显示在“总计”行中。

  5. 单击区域筛选器中的箭头。

将显示带有区域字段值的列表。

region values

  1. 勾选“选择多个单位”复选框。所有值都会显示复选框。默认情况下，所有复选框都会被勾选。

  2. 取消勾选（全部）复选框。所有复选框都会被取消勾选。

  3. 勾选南部和西部复选框。

multiple items

  1. 单击“确定”按钮。将只汇总南部和西部区域的数据。

selected items

您可以观察到，在区域筛选器旁边单元格中显示了（多个单位），表示您选择了多个值。但是，从显示的报告中，并不知道有多少个值和/或哪些值。在这种情况下，使用切片器会是一个更好的筛选选项。

Using Slicers in PivotTable

使用切片器筛选有许多优势 −

  1. 您可以通过选择切片器的字段制作多个筛选器。

  2. 您可以可视化应用筛选器的字段（每个字段一个切片器）。

  3. 切片器会有表示它所代表的字段值的按钮。您可以单击切片器的按钮以选择/取消选择字段中的值。

  4. 你可以在筛选器中看到一个字段的哪些值被使用了（在切片器中，选中的按钮被高亮显示）。

  5. 你可以为多个数据透视表和/或数据透视图使用一个常见的切片器。

  6. 你可以隐藏/取消隐藏一个切片器。

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

usage of slicers

假设你想要根据区域和月份字段过滤数据透视表。

  1. 点击功能区中 PIVOTTABLE TOOLS 下的 ANALYZE 选项卡。

  2. 点击 Filter 组中的 Insert Slicer。

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

  1. 勾选区域和月份。

analyse pivottable

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

slicer tools

正如你所看到的，每个切片器都具有其所代表的字段的所有值，这些值以按钮的形式显示。默认情况下，字段的所有值都被选中，因此所有按钮都被高亮显示。

假设你只想为南方和西方区域以及二月和三月显示数据透视表。

  1. 在区域切片器中点击南方。只有南方会在区域切片器中被高亮显示。

  2. 按下 Ctrl 键并单击区域切片器中的西方。

  3. 在月份切片器中点击二月。

  4. 按住 Ctrl 键并单击月份切片器中的三月。在切片器中选定的值被高亮显示。数据透视表将根据选定的值进行汇总。

selected values

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