Excel Data Analysis 简明教程

Excel Data Analysis - PivotTables

对大型数据集合进行数据分析通常是必要且重要的。它涉及汇总数据、获得所需值和呈现结果。

Excel 提供了数据透视表，以便您轻松快速地汇总数千个数据值，从而获得所需的结果。

考虑以下销售数据表。从这些数据中，您可能需要按区域、按月或按销售人员汇总总销售额。处理这些任务的简单方法是创建一个数据透视表，您可以对其进行动态修改，以按您想要的方式汇总结果。

sales data table

Creating PivotTable

要创建数据透视表，请确保第一行包含标题。

  1. Click the table.

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

  3. 单击“表格”组中的“数据透视表”。将会出现“数据透视表”对话框。

creating pivottable

如您在对话框中所见，您可以使用当前工作簿中的“表格”或“区域”，也可以使用外部数据源。

  1. 在“表格/区域”框中，键入表格名称。

  2. 单击“新建工作表”以告诉 Excel 将数据透视表放在何处。

  3. Click OK.

table range box

将显示一个空白数据透视表和一个数据透视表字段列表。

blank pivottable fields

Recommended PivotTables

如果您是数据透视表的新手，或者您不知道从数据中选择哪些字段，则可以使用 Excel 提供的 Recommended PivotTables 。

  1. Click the data table.

  2. Click the INSERT tab.

  3. 单击“表格”组中的“推荐数据透视表”。推荐数据透视表”对话框。

recommended pivottables

在推荐数据透视表对话框中，显示了适合您数据的可能的自定义数据透视表。

  1. 单击每个数据透视表选项以在右侧查看预览。

  2. 单击按销售人员和月份汇总订单金额的数据透视表。

recommended pivottables dialog box

单击“确定”。选定的数据透视表将显示在一个新工作表中。您可以在数据透视表字段列表中观察到选定的数据透视表字段。

pivottable fields list

PivotTable Fields

数据表中的标题将显示为数据透视表中的字段。

header pivottable fields

您可以选择/取消选择它们以立即更改数据透视表，以仅显示您想要的信息，并且按照您想要的方式显示。例如，如果您希望显示帐户信息而不是订单金额信息，请取消选择“订单金额”并选择“帐户”。

select deselect pivottable fields

PivotTable Areas

您甚至可以立即更改数据透视表的布局。您可以使用“数据透视表区域”来完成此操作。

pivottable areas

在数据透视表区域中，您可以选择 −

  1. 显示为行的字段

  2. 显示为列的字段

  3. 如何对数据进行汇总

  4. 任一字段的筛选器

  5. 何时更新数据透视表布局可以在将字段拖到各区域时立即更新数据透视表布局，或可以推迟更新，仅在单击“更新”时才更新数据透视表布局

即时更新有助于使用不同布局，并选择最适合报告要求的布局。

只需将字段拖到这些区域，然后即可查看数据透视表布局。

observe pivottable layout

Nesting in the PivotTable

如果您在任何区域中有多个字段，则嵌套将按照您在该区域中放置字段的顺序进行。可以通过拖动字段并观察嵌套如何改变来改变顺序。在上述布局选项中，您可以观察到

  1. Months are in columns.

  2. 按该顺序的行中包含区域和销售人员。即，销售人员值嵌套在区域值下。

  3. 通过订单金额的总和来进行汇总。

  4. No filters are chosen.

结果数据透视表如下 −

pivottable result

在数据透视表区域中，在行中，单击区域并将其拖动到销售人员下方，使其如下所示 −

salesperson

嵌套顺序改变，得到的数据透视表如下 −

nesting order changes

Note − 您清楚地观察到，嵌套顺序为区域，然后是销售人员的布局比嵌套顺序为销售人员，然后是区域的布局产生了更好、更简洁的报告。如果销售人员代表多个区域，并且您需要按销售人员汇总销售，那么第二个布局将是一个更好的选择。

Filters

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

将区域从行拖动到数据透视表区域中的筛选器。

filters

带有区域标签的筛选器出现在数据透视表上方（如果您在数据透视表上方没有空行，则数据透视表将被向下推以腾出空间用于筛选器）。

filter with label

您可以看到 −

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

  2. 月值显示在列中。

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

  4. 汇总值为按销售人员划分的订单金额总和订单金额总和出现在列总计中按月份划分的订单金额总和出现在行总计中

单击筛选区域右侧框中的箭头。将出现一个下拉列表，其中包含区域字段的值。

field values

  1. 选中选项 Select Multiple Items 。所有值都显示为选中框。

  2. 选中“南方”和“西方”，取消选中其他值，然后单击“确定”。

select multiple items

仅筛选出“南方”和“西方”区域相关的数据，如图例所示 -

data pertaining

您会在“筛选区域”旁边看到已显示 Multiple Items ，表示您选择了一项以上内容。但是，从报告中无法得知选择了多少项和/或选择了哪些项。这时，使用切片器是筛选的较佳选项。

Slicers

您可以使用切片器更清晰地了解哪些项目经过数据筛选。

  1. 单击功能区上“透视表工具”下的“分析”。

  2. 单击“筛选”组中的“插入切片器”。“插入切片器”框随即出现。其中包含您数据中的所有字段。

  3. 选择字段“地区”和“月份”。单击“确定”。

slicers

所有选定字段的切片器都默认选中所有值后显示。切片器工具随即显示在功能区上，用于设定切片器设置、外观和风格。

selected fields

  1. 在“地区”切片器中选中“南方”和“西方”。

  2. 在“月份”切片器中选中“二月”和“三月”。

  3. 在切片器中选择多个值时，按住 Ctrl 键。

切片器中选定的项目将突出显示。将显示选定项目的汇总值透视表。

selected items highlighted

Summarizing Values by other Calculations

在前面的示例中，您已了解如何通过求和来汇总值。但如有必要，您还可以使用其他计算。

在透视表字段列表中

  1. Select the Field Account.

  2. 取消选中字段顺序“金额”。

summarizing values

  1. 将字段“账户”拖动到“汇总值”区域。默认显示“账户”的求和。

  2. 单击方框右侧的箭头。

  3. 在出现的下拉列表中，单击“值字段设置”。

value field settings

“值字段设置”框将出现。列表下会显示几种类型的计算，以按值字段汇总 -

  1. 在列表中选择计数。

  2. 自定义名称自动更改为帐户计数。单击确定。

select count

数据透视表按计数汇总帐户值。

summarizes account values

PivotTable Tools

按照以下步骤操作，了解如何使用数据透视表工具。

  1. Select the PivotTable.

以下数据透视表工具显示在功能区上 -

  1. ANALYZE

  2. DESIGN

pivottable tools

ANALYZE

某些 ANALYZE 功能区命令如下 -

  1. Set PivotTable Options

  2. 所选字段的值字段设置

  3. Expand Field

  4. Collapse Field

  5. Insert Slicer

  6. Insert Timeline

  7. Refresh Data

  8. Change Data Source

  9. Move PivotTable

  10. 解决顺序（如果有更多计算）

  11. PivotChart

DESIGN

某些 DESIGN 功能区命令如下 -

  1. 数据透视表布局选项，用于子总计、用于总计的选项、报告布局窗体和用于空行的选项

  2. PivotTable Style Options

  3. PivotTable Styles

Expanding and Collapsing Field

您可以通过两种方式展开或折叠所选字段的所有项 -

  1. 通过选择所选字段左侧的符号或。

  2. 通过单击“分析”功能区中的“展开字段”或“折叠字段”。

By selecting the Expand symbol or Collapse symbol to the left of the selected field

  1. 选择数据透视表中包含 East 的单元格。

  2. 单击 East 左侧的折叠符号。

expanding and collapsing field

East 下的所有项都将折叠。East 左侧的折叠符号更改为展开符号。

collapse and expand

您可以观察到，只有 East 下的项被折叠。数据透视表的其余项保持原样。

单击 East 左侧的展开符号。将显示 East 下的所有项。

Using ANALYZE on the Ribbon

您可以在功能区上使用“展开字段”和“折叠字段”命令，一次性折叠或展开数据透视表中的所有项目。

  1. 单击数据透视表中包含 East 的单元格。

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

  3. 单击“活动字段”组中的“折叠字段”。

using analyze

数据透视表中字段 East 的所有项目将折叠。

east field items

单击“活动字段”组中的“展开字段”。

expand field

所有项目将显示。

Report Presentation Styles

您可以选择数据透视表的显示样式，就像要将其包含在报告中一样。选择一种适合您演示或报告中其他内容的样式。但是，不要对样式过于沉迷，因为显示结果时产生影响的报告总是比色彩鲜艳但没有突出显示重要数据点的报告要好。

  1. 单击数据透视表中的“East”。

  2. Click ANALYZE.

  3. 单击活动字段组中的“字段设置”。将出现“字段设置”对话框。

  4. 单击“布局和打印”选项卡。

  5. 选中“在每个项目标签后插入空行”。

report presentation styles

将在区域字段的每个值后面显示空行。

您还可以从“@ {s0}”选项卡中插入空行。

insert blank rows

  1. Click the DESIGN tab.

  2. 单击布局组中的“报告布局”。

  3. 在下拉列表中选择“以提纲形式显示”。

outline form

  1. 将鼠标悬停在“数据透视表样式”上。将显示鼠标放置在其上的样式预览。

  2. 选择适合您报告的样式。

将显示具有所选样式的轮廓形式的数据透视表。

displays selected style

Timeline in PivotTables

为了理解如何使用时间线，请考虑下面的示例，其中销售数据按销售人员和地区分组。数据共有 1891 行。

timeline

用此范围创建一个数据透视表，其中 −

  1. 按此顺序将地区和销售人员放入行中

  2. Product in Columns

  3. 将金额总和放入汇总值中

range

  1. Click the PivotTable.

  2. Click INSERT tab.

  3. 单击筛选器组中的时间线。将显示插入时间线。

insert timelines

单击日期，然后单击确定。将显示时间线对话框，并且时间线工具显示在功能区上。

ribbon

  1. 在时间线对话框中，选择月。

  2. 从下拉列表中选择季度。

  3. Click 2014 Q2.

  4. 按住 Shift 键，然后拖动到 2014 第 4 季度。

时间线已选择 2014 第 2 季度至第 4 季度。

数据透视表已筛选到此时间线。

pivottable filtered