Excel Pivot Tables 简明教程

Excel Pivot Tables - Reports

数据透视表的主要作用是报告。在创建数据透视表,通过整理其行和列中的字段来探索数据后,即可向广泛受众展示数据。通过筛选器、不同的汇总和重点关注特定数据,可以根据单个数据透视表生成多个所需报告。

由于数据透视表报告具有交互性,因此可快速进行必要的更改,以在报告时突出显示具体结果,例如数据趋势、数据汇总等。还可以向收件人提供报表筛选器、切片器、时间轴、数据透视图表等可视提示,以便他们可视化所需详情。

在此章节中,将学习不同方法,以通过可视提示美化数据透视表报告,以便快速探索数据。

Hierarchies

已了解如何嵌套字段以形成层次结构,在本教程的“在数据透视表中嵌套”章节中。还学习了如何在“使用数据透视表工具”章节中对数据透视表中的数据进行分组/取消分组。将示范几个示例,展示如何利用层次结构生成交互式数据透视表报告。

如果数据中的字段具有内置结构,例如:年-季度-月,则嵌套字段以形成层次结构将使你能够快速展开/折叠字段,以查看所需级别的汇总值。

例如,假设有 2015-16 财年的销售数据,地区包括东部、北部、南部和西部,如下所示。

hierarchies

创建如下所示的数据透视表。

create

正如所见,这是一种使用嵌套字段作为层次结构报告数据的全面方法。如果仅想在季度级别显示结果,则可以快速折叠“季度”字段。

quater field

假设数据中存在“日期”字段,如下所示。

your data

在这种情况下,可通过以下步骤按“日期”字段对数据分组:

创建数据透视表。

group

如你所见,此数据透视表不利于突出重要数据。

  1. 按日期字段对数据透视表分组。(在本指南的“使用数据透视表工具探索数据”一章中,你已经学习了分组功能。)

  2. 将销售人员字段放入筛选器区域。

  3. 将列标签筛选为东区。

east region

Report Filter

假设你要分别针对每个销售人员生成报告。可按以下步骤操作:

  1. 确保筛选器区域中有销售人员字段。

  2. Click on the PivotTable.

  3. 单击功能区中的“分析”选项卡。

  4. 单击数据透视表组中选项旁边的箭头。

  5. 从下拉列表中选择显示报告筛选器页面。

report filter

Show Report Filter Pages 对话框随即显示。选择字段销售人员,然后单击确定。

filter pages

将为销售人员字段的每个值创建一个独立的工作表,其中数据透视表筛选至该值。

separate worksheet

工作表将以字段值命名,值显示在工作表的选项卡上。

Slicers

数据透视表中的另一复杂功能是切片器,可用作可视化筛选字段。

  1. Click on the PivotTable.

  2. Click the ANALYZE tab.

  3. 单击筛选组中的插入切片器。

  4. 在插入切片器对话框中单击 Order Date, Quarters and Years 。将创建三个切片器:订单日期、季度和年份。

  5. 调整切片器大小,为切片器上的按钮添加更多列。

  6. 还要为销售人员和地区字段创建切片器。

  7. 选择切片器样式,以便将日期字段组合为一种颜色,另外两个字段获取不同的颜色。

  8. Deselect Gridlines.

slicer

如你所见,你不仅有一个交互式报告,还拥有一个吸引人的报告,可以轻松理解。

Timeline in PivotTable

如果透视表中包含“日期”字段,插入“时间轴”也是生成美观报表的一种选择。

  1. 在“行”区域创建包含业务员的透视表,在“列”区域创建包含区域的透视表。

  2. 插入“订单日期”字段的时间轴。

  3. 筛选时间轴以显示 2015 年 11 月至 2016 年 3 月的 5 个月数据。

timeline

DESIGN Commands

功能区上的 PIVOTTABLE TOOLS - DESIGN 命令提供格式化透视表的选项,包括以下内容:

  1. Layout

  2. PivotTable Style Options

  3. PivotTable Styles

Layout

根据以下内容,透视表布局可以使用您的首选项:

  1. Subtotals

  2. Grand Totals

  3. Report Layout

  4. Blank Rows

layout

PivotTable Layout – Subtotals

可以选择是否显示 Subtotals 。默认情况下,子总数显示在组的顶部。

subtotals

当您观察到高亮显示的组——“东部”时,子总数位于组的顶部。您可以按照以下步骤更改子总数的位置:

  1. Click on the PivotTable.

  2. 单击功能区上的设计选项卡。

  3. 单击“布局选项”组中的“子总数”。

  4. 单击“在组底部显示所有子总数”。

click subtotals

子总数现在将显示在每个组的底部。

bottom

如果您不必报告子总数,则可以选择 - 不显示子总数。

show subtotals

Grand Totals

可以选择显示或不显示“合计”。有四种可能的组合:

  1. 关闭行和列

  2. 打开行和列

  3. On for Rows Only

  4. On for Columns Only

默认情况下,是第二个组合——打开行和列。

Report Layout

您可以从若干报表布局中选择最适合您数据的布局。

  1. Compact Form.

  2. Outline Form.

  3. Tabular Form.

如果发生多次出现,还可以选择是否重复所有项目标签。

report layout

默认的报表布局是你熟悉的紧凑格式。

Compact Form

compact form

紧凑格式优化了数据透视表的可读性。其他两个格式也会显示字段标题。

单击大纲格式中的 Show

click show

单击表格格式中的 Show

tabular form

考虑以下数据透视表布局,其中字段“月份”嵌套在字段“区域”下 −

nested

正如你所观察到的,“月份”标签是重复的,这是默认设置。

单击“不重复项目标签”。“月份”标签将仅显示一次,数据透视表看起来很清晰。

not repeat

Blank Rows

为了使数据透视表报表更清晰,可以在每个项目后插入一个空白行。你稍后可以随时删除这些空白行。

blank rows

单击 Insert Blank Line after Each Item

insert blank line

PivotTable Style Options

你有以下数据透视表样式选项 −

  1. Row Headers

  2. Column Headers

  3. Banded Rows

  4. Banded Columns

style option

默认情况下,行标题和列标题的框被选中。这些选项用于分别显示第一行和第一列的特殊格式。勾选框 Banded Rows

banded rows

勾选带状列框。

banded columns

PivotTable Styles

你可以选择几种数据透视表样式。选择适合你报告的样式。例如,如果你选择“数据透视表样式深色5”,你将获得以下数据透视表样式。

pivottable styles

Conditional Formatting in PivotTable

你可以通过值设置数据透视表单元格的条件格式。

condition formatting

PivotCharts

数据透视图表在你的数据透视表报表中加入了视觉重点。你可以通过以下方式插入与数据透视表数据关联的数据透视图表 −

  1. Click on the PivotTable.

  2. 单击功能区中的“分析”选项卡。

  3. Click PivotChart.

pivotcharts

“插入图表”对话框会出现。

单击左侧窗格中的“柱形图”并选择“堆积柱形图”。单击“确定”。

click column

堆积柱形图显示出来。

stacked column
  1. 单击数据透视表的“月份”。

  2. 筛选到“2 月”,然后单击“确定”。

february

您会观察到,数据透视表也会按照数据透视表进行筛选。