Excel Power Pivot 简明教程

Excel Power Pivot - Exploring Data

在上一章中,您学习了如何从一组普通数据表中创建 Power Pivot 表。在本章中,您将学习当数据表包含数千行时,如何使用 Power Pivot 表来探索数据。

为了更好地理解,我们将从一个访问数据库导入数据,该数据库是一个关系数据库。

Loading Data from Access Database

要从 Access 数据库加载数据,请按照给定的步骤操作 -

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

  2. 在“数据模型”组中单击“管理”。

  3. 单击功能区上的 POWERPIVOT 选项卡。

ribbon

“透视表”窗口会出现。

  1. 单击 Power Pivot 窗口中的“主页”选项卡。

  2. 单击获取外部数据组中的 From Database

  3. 从下拉列表中选择 From Access

power pivot

表导入向导出现。

  1. Provide Friendly connection name.

  2. 浏览到 Access 数据库文件 Events.accdb,即 Events 数据库文件。

  3. 单击“下一步 >”按钮。

friendly connection

Table Import 向导会显示用于选择如何导入数据的选项。

单击 Select from a list of tables and views to choose the data to import ,然后单击 Next

import table

Table Import 向导会显示您已选取的 Access 数据库中的所有表格。选中所有的方框以选中所有的表格,然后单击“完成”。

table import wizard

Table Import 向导会显示 - Importing ,并呈现导入的状态。这可能需要几分钟的时间,您也可以单击 Stop Import 按钮来停止导入。

一旦数据导入完成,“导入表格向导”就会显示 - Success ,并显示导入的结果。单击 Close

stop import

透视表在数据视图的不同标签中显示所有的导入表格。

tabs in data view

单击图表视图。

click diagram view

您可以观察到表格之间存在关系 - Disciplines and Medals 。这是因为当您从关系数据库(例如 Access)导入数据时,数据库中存在的相关性也导入到了透视表中的数据模型中。

Creating a PivotTable from the Data Model

使用先前导入的表格创建一个数据透视表,如下:

  1. 单击功能区上的“PivotTable”。

  2. 从下拉列表中选择数据透视表。

  3. 在随后出现的“创建数据透视表”对话框中选择“新建工作表”,然后单击“确定”。

select new worksheet

在 Excel 窗口的新工作表中创建了一个空数据透视表。

empty pivottable

在 Power 透视数据模型中列入的所导入表格将显示在数据透视表字段列表中。

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

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

  3. 筛选学科以只显示五项体育:射箭、潜水、击剑、花样滑冰和速度滑冰。这可通过数据透视表字段区域或数据透视表本身中的行标签筛选器完成。

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

  5. 再次从 Medals 表选择奖牌并将其拖动到筛选器区域。

数据透视表将填充选定区域的添加字段和布局。

noc countryregion

Exploring Data with PivotTable

你可能只想显示奖牌数 > 80 的值。要执行此操作,请按照以下步骤操作:

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

  2. 从下拉列表中选择 Value Filters

  3. 从第二个下拉列表中选择 Greater Than…

  4. Click OK.

value filters

将会出现 Value Filter 对话框。在最靠右的框中输入 80,然后单击“确定”。

value filters dialog box

数据透视表只会显示奖牌总数高于 80 的地区。

region

你只需按照几个步骤,就可以用不同的表格生成你想要的特定报表。由于 Access 数据库中的表之间存在预先的关系,因此才能够实现这一点。由于你同时从数据库中导入了所有表格,因此 Power 透视会在其数据模型中重新创建关系。

Summarizing Data from Different Sources in Power Pivot

如果你从不同来源获取数据表,或者没有同时从数据库中导入表格,或者如果你在工作簿中创建了新的 Excel 表格并将它们添加到数据模型中,则必须创建希望用于在数据透视表中进行分析和汇总的表格之间的关系。

  1. 在工作簿中创建新的工作表。

  2. 创建 Excel 表格 - Sports(体育)。

summarizing data

将“体育”表格添加到数据模型中。

add sports

使用字段 SportID 在表格 Disciplines and Sports 之间创建关系。

disciplines and sports

将字段 Sport 添加到数据透视表中。

sport

整理字段 - Discipline and Sport 在行区域。

rows area

Extending Data Exploration

您还可以进一步探索 Events 表中的数据。

创建 EventsMedals 表格之间的关系,字段为 DisciplineEvent

extending data exploration

将表格 Hosts 添加到工作簿和数据模型中。

hosts

Extending the Data Model using Calculated Columns

为了将主机表格连接到任何其他表格,它应该具有一个字段,该字段的值唯一标识主机表格中的每一行。由于主机表格中不存在这样的字段,因此您可以在主机表格中创建计算列,以便其中包含唯一值。

  1. 转到 PowerPivot 窗口的数据视图中的主机表格。

  2. 在功能区上点击“设计”选项卡。

  3. Click Add.

最右边的名为添加列的列被高亮显示。

highlighted
  1. 在公式栏中键入以下 DAX 公式 = CONCATENATE ([Edition], [Season])

  2. Press Enter.

使用上述 DAX 公式生成的值创建了一个新列,标题为 CalculatedColumn1 ,并且该列已填满。

dax formula

右键单击新列,然后从下拉列表中选择重命名列。

rename column

在新列的标题中键入 EditionID

editionid

如您所见,列 EditionID 在 Hosts 表格中有唯一值。

Creating a Relationship Using Calculated Columns

如果您必须在 Hosts 表格和 Medals 表格之间创建关系,则列 EditionID 还应存在于 Medals 表格中。按照以下步骤在 Medals 表格中创建计算列-

  1. 单击 Power Pivot 中的数据视图中的 Medals 表格。

  2. 在功能区上点击“设计”选项卡。

  3. Click Add.

在公式栏中键入 DAX 公式 = YEAR ([EDITION]) ,然后按 Enter。

将创建的新列重命名为 Year,然后单击 Add

created as year
  1. 在公式栏中键入以下 DAX 公式 = CONCATENATE ([Year], [Season])

  2. 将创建的新列重命名为 EditionID

concatenate

正如您所观察到的,Medals 表格中的 EditionID 列与 Hosts 表格中的 EditionID 列具有相同的值。因此,您可以使用 EditionID 字段在表格之间(Medals 和 Sports)创建关系。

  1. 切换到 PowerPivot 窗口中的图表视图。

  2. 使用 Gemini 将表(奖牌和主办方)与从计算列获取的字段建立关系,即 EditionID

calculated column

现在可以将主办方表中的字段添加到 Power 透视表中。