Excel Data Analysis 简明教程

Exploring Data with PivotTables

你可以使用数据透视表进行广泛的数据分析并生成所需的报告。数据模型与数据透视表的集成增强了对数据进行整理、连接、汇总和报告的方式。你可以从外部数据源导入表格,并使用导入的表格创建数据透视表。这在连接的数据源中的数据更新时,便于数据透视表中值自动更新。

Creating a PivotTable to analyze External Data

要创建数据透视表来分析外部数据−

  1. 在 Excel 中打开一个新的空白工作簿。

  2. 单击功能区上的“数据”选项卡。

  3. 单击“获取外部数据”组中的“来自 Access”。将出现“选择数据源”对话框。

  4. 选择 Access 数据库文件。

create pivottable
  1. 单击“打开”按钮。将出现“选择表格”对话框,显示数据库中的表格。Access 数据库是关系数据库,表格类似于 Excel 表格,不同之处在于这些表格之间存在关系。

  2. 选中“启用选择多个表格”框。

  3. 选择所有表。点击确定。

select tables

Import Data 对话框出现。选择动态数据透视表。该选项将表导入您的 Excel 工作簿并创建一个动态数据透视表来分析导入的表。

analyze imported tables

正如您所观察到的,已选中并禁用了“将此数据添加到数据模型”复选框,表明表将被自动添加到数据模型。

数据将导入,并且会创建一个空的动态数据透视表。导入的表会显示在动态数据透视表字段列表中。

pivottable fields

Exploring Data in Multiple Tables

您可以在短短几步内使用动态数据透视表分析来自导入的多个表的,并获得您想要的特定报表。这是因为源数据库中的表之间存在预先的关联。当您同时从数据库中导入所有表时,Excel 会在其数据模型中重新创建关联关系。

在动态数据透视表字段列表中,您将找到导入的所有表以及其中每一张表中的字段。如果任何表中看不到字段,

  1. 点击动态数据透视表字段列表中该表旁边的箭头。

  2. 将显示该表中的字段。

Exploring Data using PivotTable

您知道如何将字段添加到动态数据透视表,并在各个区域之间拖放字段。即使您不确定想要的最终报表,您也可以利用数据并选择合适的报表。

假设您想要显示以下内容的报表 -

  1. 五项运动的数据 - 射箭、跳水、击剑、花样滑冰和速度滑冰。

  2. 在这些 5 项运动中获得超过 80 枚奖牌的地区。

  3. 每个地区五项运动中每项运动的奖牌数。

  4. 每个地区五项运动的奖牌总数。

您可以看到,只需几个步骤即可轻松创建此报表。

首先,创建一个动态数据透视表,显示如下所示所选五项运动的所有地区的奖牌数 -

  1. 将 Medals 表中的 NOC_CountryRegion 字段拖动到 COLUMNS 区域。

  2. 将 Disciplines 表中的 Discipline 拖动到 ROWS 区域。

  3. 筛选 Discipline,仅显示您要为此报表显示的五项运动。这可以在动态数据透视表字段区域或动态数据透视表本身的行标签筛选器中完成。

  4. 将表 Medals 中的 Medal 拖至 VALUES 区域。

  5. 将表 Medals 中的 Medal 拖至 FILTERS 区域。

您将获得以下透视表 −

drag medal

正如您所看到的,将显示所有区域中及您选择的五项学科的奖牌数。接下来,您必须微调此报表,以便仅显示奖牌总数超过 80 的那些区域。

  1. 单击“列标签”右边的箭头按钮。

  2. 单击出现的下拉列表中的“值筛选器”。

  3. 从出现的下拉列表中选择 Greater Than…

greater than

将出现“值筛选器”对话框。

value filters

正如您所看到的,奖牌数和大于将显示在 Show items for which 下方的框中。在包含大于的框旁边的框中输入 80,然后单击“确定”。

show items

现在,透视表将仅显示在选定的五个科目中奖牌总数大于 80 的那些区域。

pivottable displays

Creating a Relationship between Tables with PivotTable Fields

如果您没有同时导入表,如果数据来自不同的来源,或如果您向工作簿中添加新表,则您必须自己创建表之间的关系。

向工作簿中添加包含 Sport 和 SportID 字段的新工作表。

sport and sportid
  1. 命名表 - Sports

  2. 在透视表工作表中的“透视表字段”列表中单击“全部”。

sports table

您会看到新添加的表 - “运动”也在“透视表字段”列表中可见。

接下来,还将字段 Sport 添加到透视表,如下所示 −

  1. 将表 Sports 中的字段 Sport 拖至 ROWS 区域。Sport 值在透视表中显示为行标签。

  2. “透视表字段”列表中会显示一条消息,说明可能需要表之间的关系。消息旁边将显示一个“创建”按钮。

message to create relations

单击“创建”按钮。将出现“创建关系”对话框。

create relationship dialog box
  1. Select Medals under Table.

  2. Select Sport under Column.

  3. 在相关表格中选择运动。运动显示在相关列中。

  4. Click OK.

select sports

将学科拖到 Sport 中的 ROWS 中。这是为了定义数据透视表中的层次结构。数据透视表显示运动及其相应学科组。

sport in rows