Excel Power Pivot 简明教程
Excel Power Pivot Charts - Creation
基于数据模型并从 Power 透视窗口创建的透视图表是一个 Power 透视图表。虽然它拥有与 Excel 透视图表类似的一些功能，但还有其他一些功能使其更加强大。
A PivotChart based on Data Model and created from the Power Pivot window is a Power PivotChart. Though it has some features similar to Excel PivotChart, there are other features that make it more powerful.
在本章中，你将学习有关 Power 透视图表的内容。从今以后，我们将称其为透视图表以示简单。
In this chapter, you will learn about Power PivotCharts. Henceforth we refer to them as PivotCharts, for simplicity.
Creating a PivotChart
假设你希望基于以下数据模型创建透视图表。
Suppose you want to create a PivotChart based on the following Data Model.
-
Click the Home tab on the Ribbon in Power Pivot window.
-
Click PivotTable.
-
Select PivotChart from the dropdown list.
出现 Create PivotChart 对话框。选择新建工作表，然后单击确定。
The Create PivotChart dialog box appears. Select New Worksheet and click OK.
在 Excel 窗口的新工作表上创建了一个空透视图表。
An empty PivotChart is created on a new worksheet in the Excel window.
如你所见，数据模型中的所有表格会显示在“透视表字段”列表中。
As you can observe, all the tables in the data model are displayed in the PivotChart Fields list.
-
Click on the Salesperson table in the PivotChart Fields list.
-
Drag the fields − Salesperson and Region to AXIS area.
透视表中将出现两个轴字段按钮，分别对应于两个选定字段。这些是轴字段按钮。字段按钮用于筛选透视表中显示的数据。
Two field buttons for the two selected fields appear on the PivotChart. These are the Axis field buttons. The use of field buttons is to filter data that is displayed on the PivotChart.
将 TotalSalesAmount 从四张表（East_Sales、North_Sales、South_Sales 和 West_Sales）中的每张表拖动到 ∑ VALUES 区域。
Drag TotalSalesAmount from each of the four tables– East_Sales, North_Sales, South_Sales and West_Sales to ∑ VALUES area.
工作表上会出现以下内容 −
The following appear on the worksheet −
-
In the PivotChart, column chart is displayed by default.
-
In the LEGEND area, ∑ VALUES are added.
-
The Values appear in the Legend in the PivotChart, with title Values.
-
The Value Field Buttons appear on the PivotChart. You can remove the legend and the value field buttons for a tidier look of the PivotChart.
-
Click on the button at the top right corner of the PivotChart. The Chart Elements dropdown list appears.
取消选中图表元素列表中的图例复选框。图例将从透视图表中删除。
Uncheck the box Legend in the Chart Elements list. The Legend is removed from the PivotChart.
-
Right click on the value field buttons.
-
Select Hide Value Field Buttons on Chart from the dropdown list.
图表上的值字段按钮被删除。
The value field buttons on the chart are removed.
Note − 字段按钮和/或图例的显示取决于透视图表的上下文。你需要决定需要显示哪些内容。
Note − The display of field buttons and/or legend depends on the context of the PivotChart. You need to decide what is required to be displayed.
PivotChart Fields List
与 Power 透视表一样，Power 透视图表字段列表也包含两个选项卡 - ACTIVE 和 ALL。在 ALL 选项卡下，显示数据模型中的所有数据表。在 ACTIVE 选项卡下，显示已将字段添加到透视图表的那些表。
As in the case of Power PivotTable, Power PivotChart Fields list also contains two tabs – ACTIVE and ALL. Under the ALL tab, all the data tables in the Power Pivot Data Model are displayed. Under the ACTIVE tab, the tables from which the fields are added to PivotChart are displayed.
同样，这些区域与 Excel 透视图表中的区域相同。有四个区域：
Likewise, the areas are as in the case of Excel PivotChart. There four areas are −
-
AXIS (Categories)
-
LEGEND (Series)
-
∑ VALUES
-
FILTERS
正如你在上一个章节中看到的，图例中填充了∑值。此外，已将字段按钮添加到数据透视表中，以便于过滤所显示的数据。
As you have seen in the previous section, Legend is populated with ∑ Values. Further, field buttons are added to the PivotChart for the ease of filtering the data that is being displayed.
Filters in PivotChart
你可以在图表上使用轴字段按钮来过滤所显示的数据。单击轴字段按钮的箭头 - 区域。
You can use the Axis field buttons on the chart to filter the data being displayed. Click on the arrow on the Axis field button – Region.
出现的下拉列表如下所示 −
The dropdown list that appears looks as follows −
你可以选择你想要显示的值。或者，你可以将该字段放在过滤器区域中以过滤值。
You can select the values that you want to display. Alternatively, you can place the field in FILTERS area for filtering the values.
将区域字段拖放到过滤器区域。透视报表筛选器按钮 - 区域出现在数据透视表上。
Drag the field Region to FILTERS area. The Report Filter button - Region appears on the PivotChart.
单击报表筛选器按钮的箭头 - 区域。出现的下拉列表如下所示 −
Click on the arrow on the Report Filter button − Region. The dropdown list that appears looks as follows −
你可以选择你想要显示的值。
You can select the values that you want to display.
Slicers in PivotChart
使用切片器是另一个在 Power PivotChart 中过滤数据的方法。
Using Slicers is another option to filter data in the Power PivotChart.
-
Click the ANALYZE tab under PIVOTCHART tools on the Ribbon.
-
Click Insert Slicer in the Filter group. The Insert Slicer dialog box appears.
所有表格和相应的字段都出现在插入切片器对话框中。
All the tables and the corresponding fields appear in the Insert Slicer dialog box.
在插入切片器对话框中单击业务员表格中的区域字段。
Click the field Region in Salesperson table in the Insert Slicer dialog box.
区域字段的切片器出现在工作表上。
Slicer for the field Region appears on the worksheet.
如你所见，区域字段仍然作为轴字段存在。你可以通过单击切片器按钮来选择想要显示的值。
As you can observe, the Region field still exists as an Axis field. You can select the values that you want to display by clicking on the Slicer buttons.
记住你可以在几分钟内完成所有这些操作，并且由于 Power Pivot 数据模型和定义的关系，这些操作都是动态的。
Remember that you are able to do all these in a few minutes and also dynamically because of the Power Pivot Data Model and defined relationships.
PivotChart Tools
在 Power PivotChart 中，PIVOTCHART 工具在功能区上有三个选项卡，而不是 Excel 数据透视表中的两个选项卡 −
In Power PivotChart, the PIVOTCHART TOOLS has three tabs on the Ribbon as against two tabs in Excel PivotChart −
-
ANALYZE
-
DESIGN
-
FORMAT
第三个选项卡 − 格式化是 Power PivotChart 中的附加选项卡。
The third tab − FORMAT is the additional tab in Power PivotChart.
单击功能区上的格式化选项卡。
Click the FORMAT tab on the Ribbon.
格式化选项卡下的功能区选项都用以增强你的数据透视表的精彩程度。你可以明智地使用这些选项，避免过分枯燥。
The options on the Ribbon under FORMAT tab are all for adding splendor to your PivotChart. You can use these options judiciously, without getting over bored.