Excel Data Analysis 简明教程

Exploring Data with Power View

Power View 支持互动式数据探索、可视化和演示，鼓励直观的即席报告。可以使用通用可视化工具即时分析大型数据集。数据可视化也可以进行动态处理，从而便于使用单个 Power View 报告演示数据。

Power View enables interactive data exploration, visualization and presentation that encourages intuitive ad-hoc reporting. Large data sets can be analyzed on the fly using the versatile visualizations. The data visualizations can also be made dynamic facilitating ease of presentation of the data with a single Power View report.

Microsoft Excel 2013 中引入了 Power View。在使用 Power View 开始数据分析之前，请确保已启用 Power View 加载项并且该加载项在功能区中可用。

Power View is introduced in Microsoft Excel 2013. Before you start your data analysis with Power View, make sure that the Power View add-in enabled and available on the Ribbon.

单击功能区上的“插入”选项卡。Power View 应在“报表”组中可见。

Click the INSERT tab on the Ribbon. Power View should be visible in the Reports group.

power view

Creating a Power View Report

您可以从数据模型中的表创建 Power View 报表。

You can create a Power View report from the tables in the Data Model.

  1. Click the INSERT tab on the Ribbon.

  2. Click Power View in the Reports group.

Opening Power View 消息对话框会显示一个水平滚动的绿色状态栏。这可能需要一些时间。

Opening Power View message box appears with a horizontal scrolling green status bar. This might take a little while.

opening power view

Power View 表单在您的 Excel 工作簿中创建为工作表。它包含一个空 Power View 报表、过滤器占位符以及显示数据模型中的表的 Power View 字段列表。Power View 作为功能区上的一个选项卡显示在 Power View 表单中。

Power View sheet is created as a worksheet in your Excel workbook. It contains an empty Power View report, Filters space holder and the Power View Fields list displaying the tables in the Data Model. Power View appears as a tab on the Ribbon in the Power View sheet.

power view sheet

Power View with Calculated Fields

在工作簿的数据模型中，您有以下数据表 −

In the Data Model of your workbook, you have the following data tables −

  1. Disciplines

  2. Events

  3. Medals

假设您想显示每个国家赢得的奖牌数。

Suppose you want to display the number of medals that each country has won.

  1. Select the fields NOC_CountryRegion and Medal in the table Medals.

这两个字段将显示在“区域”中的“字段”下。Power View 将显示为一张表，其中两个选定的字段作为列。

These two fields appear under FIELDS in the Areas. Power View will be displayed as a table with the two selected fields as columns.

fields

Power View 显示了每个国家赢得的奖牌。要显示每个国家赢得的奖牌数，需要对奖牌进行计数。要获得奖牌计数字段，您需要在数据模型中进行计算。

The Power View is displaying what medals each country has won. To display the number of medals won by each country, the medals need to be counted. To get the medal count field, you need to do a calculation in the Data Model.

  1. Click PowerPivot tab on the Ribbon.

  2. Click Manage in the Data Model group. The tables in the Data Model will be displayed.

  3. Click the Medals tab.

  4. In the Medals table, in the calculation area, in the cell below the Medal column, type the following DAX formula Medal Count:=COUNTA([Medal])

medals table

您可以看到奖牌计数公式出现在公式栏中，并且在公式栏的左侧，显示列名“勋章”。

You can observe that the medal count formula appears in the formula bar and to the left of the formula bar, the column name Medal is displayed.

您会收到一条 Power View 消息，指出数据模型已更改，并且如果您单击“确定”，该更改将反映在您的 Power View 中。单击“确定”。

You will get a Power View message that the Data Model is changed and if you click OK, the changes will be reflected in your Power View. Click OK.

power view message

在 Power View 表单中的 Power View 字段列表中，您可以看到以下内容 −

In the Power View Sheet, in the Power View Fields list, you can observe the following −

  1. A new field Medal Count is added in the Medals table.

  2. A calculator icon appears adjacent to the field Medal Count, indicating that it is a calculated field.

  3. Deselect the Medal field and select the Medal Count field.

Power View 表按每个国家/地区显示奖牌数量。

Your Power View table displays the medal count country wise.

newly added calculated field

Filtering Power View

可以通过定义筛选器条件筛选在 Power View 中显示的值。

You can filter the values displayed in Power View by defining the filter criteria.

  1. Click the TABLE tab in the Filters.

  2. Click Medal Count.

  3. Click the icon Range file mode that is to the right of Medal Count.

  4. Select is greater than or equal to from the drop-down list in the box below Show items for which the value.

  5. Type 1000 in the box below that.

  6. Click apply filter.

在字段名称“奖牌数量”下方会出现“大于或等于 1000”。Power View 将仅显示那些奖牌数量大于或等于 1000 的记录。

Below the field name – Medal Count, is greater than or equal to 1000 appears. Power View will display only those records with Medal Count >= 1000.

medal count greater than thousand

Power View Visualizations

在 Power View 工作表中，功能区上会显示两个选项卡：“POWER VIEW”和“设计”。

In the Power View sheet, two tabs – POWER VIEW and DESIGN appear on the Ribbon.

单击“ DESIGN ”选项卡。您将在功能区上的“切换可视化效果”组中找到多个可视化命令。

Click the DESIGN tab.You will find several visualization commands in the Switch Visualization group on the Ribbon.

power view visualizations

利用 Power View，您可以快速创建许多适合您的数据的不同数据可视化效果。可能的可视化效果包括表、矩阵、卡片、地图、图表类型（例如，条形图、柱形图、散点图、折线图、饼图和气泡图），以及成套的多张图表（具有相同轴的图表）。

You can quickly create a number of different data visualizations that suit your data using Power View. The visualizations possible are Table, Matrix, Card, Map, Chart types such as Bar, Column, Scatter, Line, Pie and Bubble Charts, and sets of multiple charts (charts with same axis).

若要使用这些可视化效果来探索数据，您可以在 Power View 工作表上开始创建一个表（默认可视化效果），然后轻松地将其转换为其他可视化效果，以找到最能说明您的数据的另一种可视化效果。您可以通过从功能区上的“切换可视化效果”组中选择一种可视化效果来将一种 Power View 可视化效果转换为另一种可视化效果。

To explore the data using these visualizations, you can start on the Power View sheet by creating a table, which is the default visualization and then easily convert it to other visualizations, to find the one that best illustrates your Data. You can convert one Power View visualization to another, by selecting a visualization from the Switch Visualization group on the Ribbon.

在同一张 Power View 工作表上还可以有多种可视化效果，以便您可以重点突出重要字段。

It is also possible to have multiple visualizations on the same Power View sheet, so that you can highlight the significant fields.

在以下部分中，您将了解如何通过两种可视化效果（矩阵和卡片）来探索数据。您将在后续章节中详细了解如何通过其他 Power View 可视化效果来探索数据。

In the sections below, you will understand how you can explore data in two visualizations – Matrix and Card. You will get to know about exploring data with other Power View visualizations in later chapters.

Exploring Data with Matrix Visualization

矩阵可视化效果类似于表可视化效果，因为它也包含数据行和列。但是，矩阵具有附加功能：

Matrix Visualization is similar to a Table Visualization in that it also contains rows and columns of data. However, a matrix has additional features −

  1. It can be collapsed and expanded by rows and/or columns.

  2. If it contains a hierarchy, you can drill down/drill up.

  3. It can display totals and subtotals by columns and/or rows.

  4. It can display the data without repeating values.

在 Power View 中同时进行表可视化和矩阵可视化并排查看同一数据侧时，您可以看到这些视图中的差异。

You can see these the differences in the views by having a Table Visualization and a Matrix Visualization of the same data side by side in the Power View.

  1. Choose the fields – Sport, Discipline and Event. A Table representing these fields appears in Power View.

exploring data with matrix visualization

正如您所观察到的，每一项运动都有多个学科和每一项学科都有多个赛事。现在，按照以下步骤在该表格可视化的右侧创建另一个 Power View 可视化元素：

As you observe, there are multiple disciplines for every sport and multiple events for every discipline. Now, create another Power View visualization on the right side of this Table visualization as follows −

  1. Click the Power View sheet in the space to the right of the Table.

  2. Choose the fields – Sport, Discipline and Event.

表示这些字段的另一个表显示在 Power View 中，位于之前的表右侧。

Another Table representing these fields appears in Power View, to the right of the earlier Table.

choose fields

  1. Click the right Table.

  2. Click the DESIGN tab on the Ribbon.

  3. Click Table in the Switch Visualization group.

  4. Select Matrix from the drop-down list.

table represents fields

Power View 中右侧的表格被转换为矩阵。

The Table on the right in Power View gets converted to Matrix.

power view converted to matrix

左侧的表格列出每项赛事对应的运动和学科，而右侧矩阵只列出每项运动和学科一次。因此，在这种情况下，矩阵可视化将为您提供全面、紧凑且可读的数据格式。

The table on the left lists the sport and discipline for each and every event, whereas the matrix on the right lists each sport and discipline only once. So, in this case, Matrix visualization gives you a comprehensive, compact and readable format for your data.

现在，您可以浏览数据以找出获得 300 枚以上奖牌的国家/地区。您还可以找出相应的运动并进行小计。

Now, you can explore the data to find the countries that scored more than 300 medals. You can also find the corresponding sports and have subtotals.

  1. Select the fields NOC_CountryRegion, Sport and Medal Count in both the Table and Matrix Visualizations.

  2. In the Filters, select the filter for the Table and set the filtering criteria as is greater than or equal to 300.

  3. Click apply filter.

  4. Set the same filter to Matrix also. Click apply filter.

select fields

再一次，您会观察到，在矩阵视图中，结果是可读的。

Once again, you can observe that in the Matrix view, the results are legible.

Exploring Data with Card Visualization

在卡片可视化中，您将拥有一系列快照，显示表格中每行的数据，布局类似于索引卡。

In a card visualization, you will have a series of snapshots that display the data from each row in the table, laid out like an index card.

  1. Click the Matrix Visualization that is on the right side in the Power view.

  2. Click Table in the Switch Visualization group.

  3. Select Card from the drop-down list.

select card

矩阵可视化将会转换为卡片可视化。

The Matrix Visualization gets converted to Card Visualization.

matrix to card visualization

您可以使用卡片视图以全面方式显示突出数据。

You can use the Card view for presenting the highlighted data in a comprehensive way.

Data Model and Power View

工作簿可以包含以下数据模型和 Power View 组合。

A workbook can contain the following combinations of Data Model and Power View.

  1. An internal Data Model in your workbook that you can modify in Excel, in PowerPivot, and even in a Power View sheet.

  2. Only one internal Data Model in your workbook, on which you can base a Power View sheet.

  3. Multiple Power View sheets in your workbook, with each sheet based on a different Data Model.

如果您在工作簿中有多个 Power View 工作表，则只有在工作表基于相同数据模型的情况下，才能从一个工作表复制可视化项到另一个工作表。

If you have multiple Power View sheets in your workbook, you can copy visualizations from one to another only if both the sheets are based on the same Data Model.

Creating Data Model from Power View Sheet

可以按照以下步骤在 Power View 工作表中创建和/或修改工作簿中的数据模型 -

You can create and/or modify the Data Model in your workbook from the Power View sheet as follows −

从包含两个工作表中销售人员数据和销售数据的新工作簿开始。

Start with a new workbook that contains Salesperson data and Sales data in two worksheets.

salesperson and sales data

  1. Create a table from the range of data in the Salesperson worksheet and name it Salesperson.

  2. Create a table from the range of data in the Sales worksheet and name it Sales.

您的工作簿中有两个表格 - Salesperson 和 Sales。

You have two tables – Salesperson and Sales in your workbook.

  1. Click the Sales table in the Sales worksheet.

  2. Click the INSERT tab on the Ribbon.

  3. Click Power View in the Reports group.

click power view

将创建 Power View 工作表放入您的工作簿中。

Power View Sheet will be created in your workbook.

您可以在 Power View 字段列表中看到，显示工作簿中的两个表格。但是，Power View 中只会显示活动表格（销售）字段，因为只有活动数据表格字段在字段列表中被选中。

You can observe that in the Power View Fields list, both the tables that are in the workbook are displayed. However, in the Power View, only the active table (Sales) fields are displayed since only the active data table fields are selected in the Fields list.

power view fields list

您可以在 Power View 中看到显示销售人员 ID。假设您希望改显示销售人员姓名。

You can observe that in the Power View, Salesperson ID is displayed. Suppose you want to display the Salesperson name instead.

在 Power View 字段列表中，进行以下更改。

In the Power View Fields list, make the following changes.

  1. Deselect the field Salesperson ID in the Salesperson table.

  2. Select the field Salesperson in the Salesperson table.

由于工作簿中没有数据模型，因此这两个表格之间不存在关系。Power View 中不会显示任何数据。Excel 会显示指导您执行相关操作的消息。

As you do not have a Data Model in the workbook, no relationship exists between the two tables. No data is displayed in Power View. Excel displays messages directing you what to do.

excel displays messages

还会显示一个“创建”按钮。点击“创建”按钮。

A CREATE button also will be displayed. Click the CREATE button.

Create Relationship 对话框在 Power View 表格中自身打开。

The Create Relationship dialog box opens in the Power View Sheet itself.

create relationship

  1. Create a relationship between the two tables using the Salesperson ID field.

不用离开 Power View 表格，您已经成功创建以下内容：

Without leaving the Power View sheet, you have successfully created the following −

  1. The internal Data Model with the two tables, and

  2. The relationship between the two tables.

“销售人员”字段随销售数据一起显示在 Power View 中。

The field Salesperson appears in Power View along with the Sales data.

sales data

  1. Retain the fields Region, Salesperson and ∑ Order Amount in that order in the area FIELDS.

  2. Convert the Power View to Matrix Visualization.

  3. Drag the field Month to the area TILE BY. Matrix Visualization appears as follows −

appears matrix visualization

正如您所观察到的，对于每个区域，该区域的销售人员和订单金额的总值都会显示出来。每个区域的子总数都会显示。显示按月份进行，如同在视图上方的图块中所选的那样。当您在图块中选择月份时，该月份的数据将显示出来。

As you observe, for each of the regions, the Salespersons of that region and sum of Order Amount are displayed. Subtotals are displayed for each region. The display is month wise as selected in the tile above the view. As you select the month in the tile, the data of that month will be displayed.