Excel Data Analysis 简明教程

Exploring Data with Power View Charts

在 Power View 中，您有许多图表选项：饼图、柱状图、条形图、折线图、散点图和气泡图。Power View 中的图表是交互式的。如果您单击一张图表中的一个值：

In Power View, you have a number of Chart options: Pie, Column, Bar, Line, Scatter, and Bubble. The Charts in Power View are interactive. If you click on a value in one chart −

  1. That value in that chart is highlighted.

  2. That value in all the other charts in Power View is also highlighted.

  3. All the tables, matrices and tiles in Power View are filtered to that value.

因此，Power View 图表可用作交互式的图片式数据分析工具。此外，这些图表在演示环境中也是交互式的，这使您可以突出显示分析结果。

Thus, Power View Charts serve as interactive, pictorial data analysis tools. Further, the charts are interactive in a presentation setting also, which would enable you to highlight the analysis results.

Exploring with Line Charts

您可以使用折线图来比较一个或多个数据系列中的数据点。折线图均匀地沿着水平（分类）轴分布分类数据，沿着垂直（值）轴分布所有数值数据。

You can use Line charts for comparing data points in one or more data series. Line charts distribute category data evenly along a horizontal (category) axis, and all numerical value data along a vertical (value) axis.

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

Suppose you want to display the Medal Count for each country.

  1. Create a Power View with the fields NOC_CountryRegion and Medal Count selected. By default, Table will be displayed.

  2. Click the Table.

  3. Click Other Chart in the Switch Visualization group.

  4. Select Line from the drop-down list. Line Chart will be displayed in the Power View.

exploring with line charts

  1. Click on the Line or the Category (x-axis) axis.

  2. Drag to left or right. The Categories to the left or right will be displayed and Line chart will be displayed accordingly.

  3. Place the cursor on any of the data points on the line.

与该数据点对应的值将显示在该点上。

The values corresponding to that data point will be displayed at that point.

data point

Exploring with Bar Charts

您可以使用条形图来比较一个或多个数据系列中的数据点。在条形图中，类别沿着垂直轴组织，而值沿着水平轴组织。在 Power View 中，有三种条形图子类型 −

You can use Bar charts for comparing data points in one or more data series. In a Bar chart, categories are organized along the vertical axis and values along the horizontal axis. In Power View, there are three Bar chart subtypes −

  1. Stacked Bar.

  2. 100% Stacked Bar.

  3. Clustered Bar.

您可以按以下方式将表可视化转换为条形图可视化：

You can convert a Table Visualization to Bar Chart Visualization as follows −

  1. Create two Table visualizations side-by-side.

  2. Click the right Table.

  3. Click Bar Chart in the Switch Visualization group.

  4. Click Stacked Bar.

exploring with bar charts

右边的表可视化会转换为条形图可视化。您会注意到，y 轴值按升序排列类别值。

The Table Visualization on the right gets converted to Bar Chart Visualization. As you observe, the y-axis values are sorted by the category values in ascending order.

bar chart visualization

  1. Take the cursor above the Bar chart. You will find – sort by NOC_CountryRegion asc.

  2. Click NOC_CountryRegion. It is changed to Medal Count.

  3. Click asc. It is changed to desc. You will find that the Bar Chart is sorted by descending Medal Count.

medal count

  1. Click the Bar with Category GER. Only that Bar will be highlighted.

  2. With Ctrl key pressed, click the Bars with Categories FRA and ITA. The Bars for GER, FRA and ITA will be highlighted.

  3. The Table on the left also shows values for these three Categories only.

ger category

在这两个可视化中，单击 Power View 字段列表中的性别字段。

In both the visualizations, click the Gender field also in the Power View Fields list.

power view fields

单击条形的左侧部分 - GER。它会高亮。在表中，只会显示 GER 和 Men 的信息。

Click the left portion of the Bar – GER. It is highlighted. In the Table, only the information for GER and Men will be displayed.

Note - 在这种情况下，您无法进行多重选择。

Note − You cannot make multiple selections in this case.

fra

Exploring with Column Charts

您可以使用柱状图来显示一段时间内的变化数据，或说明项目之间的比较。在柱状图中，类别沿水平轴，值沿垂直轴。

You can use Column charts for showing data changes over a period of time or for illustrating comparison among items. In Column charts, categories are along the horizontal axis and values along the vertical axis.

在 Power View 中，有三种柱状图子类型 -

In Power View, there are three Column chart subtypes −

  1. Stacked Column.

  2. 100% Stacked Column.

  3. Clustered Column.

您可以如下将表格可视化转换为柱状图可视化 -

You can convert a Table Visualization to Column Chart Visualization as follows −

  1. Create two Table visualizations side-by-side.

  2. Click the right Table.

  3. Click Column Chart in the Switch Visualization group.

  4. Click Stacked Column.

exploring with column charts

右侧的表格可视化转换为条形图可视化。正如您所观察到的，x 轴值按升序排列类别值。

The Table Visualization on the right is converted to Bar Chart Visualization. As you observe, the x-axis values are sorted by the category values in ascending order.

column chart visualization

  1. Take the cursor to above the Column chart. You will find – sort by NOC_CountryRegion asc.

  2. Click on NOC_CountryRegion. It gets changed to Medal Count.

  3. Click on asc. It gets changed to desc. You will find that the Column Chart is sorted by descending Medal Count.

click asc

单击类别 GER 的条形下部。它将被突出显示。

Click on the lower portion of the Bar with Category GER. It gets highlighted.

在表格中，将仅显示 GER 和男子部分的信息。

In the Table, only the information for GER and Men will be displayed.

ger information

Exploring with Simple Pie Charts

Power View 中的饼图是简单或复杂的。您将在本节中学习简单的饼图。您将在下一节学习复杂的饼图。

Pie charts in Power View are simple or sophisticated. You will learn simple Pie charts in this section. You will learn sophisticated Pie charts in the next section.

从如下创建饼图开始 -

Start with creating a Pie chart as follows −

  1. Resize the Stacked Column chart and move it upwards.

  2. Create another Table visualization below the Stacked Column chart.

  3. Click the new Table.

  4. Click Other Chart in the Switch Visualization group.

  5. Select Pie.

exploring with simple pie charts

堆叠柱形图下方的表格可视化转换为饼图可视化。正如您所观察到的，饼图中有太多切片，因为有许多类别（国家）。请注意，只有当类别数少于或等于 8 时，饼图才有用。

The Table Visualization below the Stacked Column chart is converted to Pie Chart Visualization. As you observe, there are too many slices in the Pie chart as there are many categories (countries). Note that Pie charts work well only when the number of categories is 8 or less.

pie chart visualization

您可以如下通过筛选值来减少类别的数目 -

You can reduce the number of categories by filtering the values as follows −

  1. Set the filtering as Medal Count is greater than or equal to 1300 in − Table VisualizationColumn Chart VisualizationPie Chart Visualization

Note - 您必须分别定义和应用筛选，以便可视化。

Note − You have to define and apply filtering to each of the visualizations separately.

visualizations

现在，您有一个简单的饼状图可视化，其中奖牌数量由饼大小显示，国家/地区由颜色显示。

Now, you have a Simple Pie Chart Visualization, wherein the count of Medals are shown by the Pie size, and countries by colors.

单击一个饼状图切片。该切片将高亮显示，而其他切片将变灰。柱状图中的相应列也将高亮显示。在表格中，只会显示与突出显示的饼状图切片对应值。

Click on a Pie slice. That slice is highlighted and others get grayed. The corresponding column in the Column chart also is highlighted. In the table, only the values corresponding to the highlighted Pie slice will be displayed.

click pie slice

Exploring with Sophisticated Pie Charts

您可以通过添加更多功能来使饼状图可视化变得复杂。您可以创建一个饼形图 -

You can make your Pie Chart Visualization sophisticated, by adding more features. You can make a pie char that −

  1. Drills down when you double-click a slice, or

  2. Shows sub-slices within the larger color slices.

A Pie chart that drills down when you double-click a slice

  1. In the Pie chart, in the Power View Fields list, drag the field Gender to COLOR area, to below the field NOC_CountryRegion. This means you have two categories.

  2. In the Table, include Gender also in the Fields list.

您的 Power View 如下所示 -

Your Power View looks as follows −

exploring with sophisticated pie charts

您观察到，对于每个类别都有一个单一切片和一种颜色 - 国家/地区。

As you observe, there is a single slice with one color for each category - country.

在饼状图上，双击美国切片。

On the Pie chart, double-click on the USA slice.

您的 Power View 中的饼状图将更改为显示按性别（即第二个类别）针对所选类别（美国）的值。饼状图的颜色现在显示第二个字段的百分比，即按您双击的饼状颜色进行筛选的性别。换句话说，饼状图已下钻。您观察到饼状图的右上角出现一个小箭头。如果您将鼠标悬停其上，箭头会高亮显示，并会显示上钻。

The Pie chart in your Power View will be changed to show values by Gender, which is the second category, for the selected category (USA). The colors of the pie chart now show the percentages of the second field, i.e., Gender, filtered for the pie color you doubleclicked. In other words, the Pie chart was drilled down. As you observe, a small arrow appears on the top right corner of the Pie chart. If you place the mouse over it, the arrow is highlighted and Drill up will be displayed.

click usa slice

单击上钻箭头。饼状图返回其前一个状态。

Click the drill up arrow. The Pie Chart returns to its previous state.

A Pie chart that shows sub-slices within the larger color slices

在饼状图中，在 Power View 字段列表中，将字段性别从颜色区域拖到切片区域。

In the Pie chart, in the Power View Fields list, drag the field Gender from COLOR area to SLICES area.

您的 Power View 如下所示 -

Your Power View looks as follows −

power view result

您会看到，在饼状图中，对于类别美国，有两个相同颜色的切片。

As you can see, in the Pie chart, there are two slices of same color for the category USA.

单击其中一个切片。

Click on one of these slices.

您将能够在 Power View 中看到以下更改 -

You will be able to see the following changes in Power View −

  1. The selected slice is highlighted and other slices are grayed or deactivated.

  2. The bar for the category USA displays the medal count for the selected slice.

  3. The Table shows the values for the selected slice.

highlighted selected slice

  1. Click the other slice. You can observe the changes as given above for this selected slice.

click slice

Exploring with Scatter Charts

您可以使用散点图在一张图表中显示许多相关数据。在散点图中，x 轴显示一个数值字段，y 轴显示另一个字段，从而便于查看图表中所有项目的两个值之间的关系。

You can use Scatter charts to display many related data in one chart. In Scatter charts, the x-axis displays one numeric field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart.

要创建散点图可视化效果，请执行以下操作：

To create a Scatter Chart Visualization, proceed as follows −

  1. Add the fields Sport, Medal Count and Event to Table.

  2. Click the arrow next to Event in the Power View Fields list. Click Count (Distinct). The field Event changes to the numeric field Count of Event. Therefore, you have one category field – Sport and two numeric fields – Medal Count and Count of Event.

  3. Click Other Chart in the Switch Visualization group.

  4. Click Scatter.

exploring with scatter charts

您将获得散点图可视化效果，数据点显示为相同大小的圆圈，显示每个运动的项目计数和奖牌数值之间的关系。

You will get the Scatter Chart Visualization, with the data points displayed as circles of same size, showing how the Count of Event and Medal Count values are related for each sport.

scatter chart visualization

  1. Click the LAYOUT tab on the Ribbon.

  2. Click Data Labels in the Labels group.

  3. Select Right from the drop-down list. The Data labels appear for the data points.

data labels

与在较少项目中获得更多奖牌的水上运动相比，摔跤运动在更多项目中获得的奖牌较少。

The sport Wrestling has less number of medals in more number of events as compared to the sport Aquatics that has more number of medals in less number of events.

Exploring with Bubble Charts

您可以使用气泡图在一张图表中显示许多相关数据。在气泡图中，x 轴显示一个数值字段，y 轴显示另一个字段，从而便于查看图表中所有项目的两个值之间的关系。第三个数值字段控制数据点的大小。

You can use Bubble charts to display many related data in one chart. In Bubble Charts, the x-axis displays one numeric field and the y-axis displays another, making it easy to see the relationship between the two values for all the items in the chart. A third numeric field controls the size of the data points.

要创建气泡图可视化效果，请执行以下操作：

To create a Bubble Chart Visualization, proceed as follows −

  1. Drag Medal Count to Size.

  2. Drag NOC_CountryRegion to ∑ X-VALUE. The Scatter chart is converted to Bubble chart.

exploring with bubble charts

如同您所观察到的，每个气泡的大小显示奖牌数。数据标签显示运动。

As you observe, the size of each bubble shows the medal count. The data labels show the Sport.

Exploring with Colors

您还可以按类别为气泡着色，如下所示：

You can also color the bubbles by a category as follows −

  1. Drag the field NOC_CountryRegion to COLOR area in the Power View Fields list.

  2. Drag the field DiscipleEvent to ∑ X-VALUES.

exploring with colors

正如你所观察到的，图例会显示处于彩色区域的分类值和各自的颜色。数据标签对应于详细信息区域的分类。数据点的尺寸由面积和尺寸进行衡量。

As you observe, Legend shows the values of the category that is in COLOR area and the respective colors. The data labels correspond to the category in DETAILS area. The size of the data points is by the area ∑ SIZE.

接下来，你可以看到图例中选择分类如何改变可视化程度——

Next, you can see how selecting a category in Legend changes the visualization −

点击图例中的一个值。此时，仅会突出显示该颜色的数据点（即，对应于该值的数据点）。所有其他数据点均会停用。

Click on a value in the Legend. Only the data points of that color (i.e., corresponding to that value) will be highlighted. All the other data points will be deactivated.

deactivate data points

正如你所观察到的，与所选国家相对应的所有运动都会显示出来，每个气泡的尺寸都代表奖牌数量。

As you observe, all the sports corresponding to the selected country are displayed and the size of each bubble represents the medal count.

如果你想了解单个数据点的详细信息——

If you want to know the details of a single data point −

  1. Highlight the data point by just clicking on that bubble.

  2. Place the cursor on that data point.

此时，只有该气泡会突出显示，而其余气泡都会变为灰色。关于该数据点的所有信息都会显示在数据点旁边的一个框内。

Only that bubble is highlighted and the rest of the bubbles get grayed. All the information about that data point will be displayed in a box next to the data point.

bubble highlighted

Exploring with Play Axis

你可以使用播放轴，按照以下步骤对一段时间的的数据更改进行可视化：

You can visualize the data changes over a period of time using Play Axis as follows −

  1. Drag the field Edition in the Power View Fields list to PLAY AXIS area.

一条播放按钮时间轴会插入到气泡图可视化中。你可以通过在筛选器中筛选 Edition 字段值来调整时间轴。如果你想关注某个特定的时间范围，或时间轴太宽，这会很有用。

A timeline with a Play button is inserted in your Bubble chart visualization. You can adjust the timeline by filtering the Edition field values in the Filters. This would be useful if you want to focus on a particular time range or if the timeline is too wide.

  1. Adjust the timeline by filtering Edition field in Filters and choosing a span of time values.

exploring with play axis

  1. Click the Play button. The bubbles travel, grows and shrink to show how the values change based on the play axis. A small vertical line appears on the timeline that moves across the timeline. The time at that point also is displayed.

click play button

你可以在任何点暂停以更详细地研究数据。

You can pause at any point to study the data in more detail.