Excel Power Pivot 简明教程

Excel Power Pivot - Aesthetic Reports

您可以利用資料模型中的 Power Pivot 資料建立美觀的資料分析報告。

You can create aesthetic reports of your data analysis with Power Pivot Data that is in Data Model.

其重要特性有 −

The important features are −

  1. You can use PivotCharts to produce visual reports of your data. You can use Report Layouts to structure your PivotTables to make them easily readable.

  2. You can insert Slicers for filtering data in the report.

  3. You can use a common Slicer for both the PivotChart and the PivotTable that are in the same report.

  4. Once your final report is ready, you can choose to hide the Slicers form the display.

在本章節中，您將會了解如何取得 Power Pivot 中可用的選項所產生的報告。

You will learn how to get reports with the options that are available in Power Pivot in this chapter.

考虑此章节中插图的以下数据模型。

Consider the following Data Model for illustrations in this chapter.

following data model

Reports based on Power PivotChart

建立 Power Pivot 圖表，如下所示−

Create a Power PivotChart as follows −

  1. Click the Home tab on the Ribbon in PowerPivot window.

  2. Click PivotTable.

  3. Select PivotChart from the dropdown list.

  4. Click New Worksheet in the Create PivotChart dialog box.

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

An empty PivotChart is created in a new worksheet in Excel window.

  1. Drag Sport from Medals table to Axis area.

  2. Drag Medal from Medals Table to ∑ VALUES area.

drag medal

  1. Click the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.

  2. Click Insert Slicer in the Filter Group. The Inset Slicers dialog box appears.

  3. Click the field NOC_CountryRegion in the Medals table.

  4. Click OK.

analyze

切片器 NOC_CountryRegion 出现。

The Slicer NOC_CountryRegion appears.

  1. Select USA.

  2. Drag Gender from Medals table to GENDER area.

gender area

  1. Right click on the PivotChart.

  2. Select Change Chart Type from the dropdown list.

chart type

将出现“更改图表类型”对话框。

The Change Chart Type dialog box appears.

单击“堆积柱形图”。

Click on Stacked Column.

stacked column

  1. Insert Slicer for Sport field.

  2. Drag Discipline from Disciplines table to AXIS area.

  3. Remove the field Sport from AXIS area.

  4. Select Aquatics in the Slicer – Sport.

select aquatics

Report Layout

创建数据透视表如下 −

Create PivotTable as follows −

  1. Click on Home tab on the Ribbon in PowerPivot window.

  2. Click on PivotTable.

  3. Click on PivotTable in the dropdown list. The Create PivotTable dialog box appears.

  4. Click on New Worksheet and click Ok. An empty PivotTable gets created in a new worksheet.

  5. Drag NOC_CountryRegion from Medals table to AXIS area.

  6. Drag Sport from Medals table to COLUMNS area.

  7. Drag Discipline from Disciplines table to COLUMNS area.

  8. Drag Medal to ∑ VALUES area.

report layout

单击“列标签”旁边的箭头按钮，然后选择“水生运动”。

Click on the arrow button next to Column Labels and select Aquatics.

labels column

  1. Click on the arrow button next to Row Labels.

  2. Select Value Filters from the dropdown list.

  3. Select Greater Than Or Equal To from the second dropdown list.

select greater

在“值筛选器”对话框中“奖牌计数大于或等于”旁边的框中键入 80。

Type 80 in the box next to Count of Medal is greater than or equal to in the Value Filter dialog box.

type 80

  1. Click the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.

  2. Click on Subtotals.

  3. Select Do Not Show Subtotals fromn the dropdown list.

not show subtotals

小计列（水生运动合计）移除。

The Subtotals column – Aquatics Total gets removed.

subtotals column

单击“报表布局”，然后从下拉列表中选择“ Show in Outline Form ”。

Click Report Layout and select Show in Outline Form from the dropdown list.

outline form

选中“条带行”框。

Check the box Banded Rows.

box banded

字段名称显示的行标签和列标签位置，并且报表清晰明了。

The field names appear in place of Row Labels and Column Labels and the report looks self-explanatory.

field names

Using a Common Slicer

创建数据透视图和数据透视表，彼此相邻。

Create a PivotChart and PivotTable next to each other.

  1. Click the Home tab on the Ribbon in PowerPivot tab.

  2. Click PivotTable.

  3. Select Chart and Table (Horizontal) from the dropdown list.

home tab

创建数据透视表和数据透视图 (水平) 对话框将显示。

The Create PivotChart and PivotTable (Horizontal) dialog box appears.

选择新建工作表，然后单击确定。在新建工作表中，空数据透视图和空数据透视表将并排显示。

Select New Worksheet and click OK. An Empty PivotChart and an empty PivotTable appear next to each other in a new worksheet.

ok

  1. Click PivotChart.

  2. Drag Discipline from Disciplines table to AXIS area.

  3. Drag Medal from Medals table to ∑ VALUES area.

  4. Click PivotTable.

  5. Drag Discipline from Disciplines table to ROWS area.

  6. Drag Medal from Medals table to ∑ VALUES area.

values area

  1. Click the ANALYZE tab in PIVOTTABLE TOOLS on the Ribbon.

  2. Click Insert Slicer. The Insert Slicers dialog box appears.

  3. Click on NOC_CountryRegion and Sport in Medals table.

  4. Click OK.

medals table

将显示两个筛选器 - NOC_CountryRegion 和 Sport。排列并调整其大小使其与数据透视表正确对齐。

Two Slicers – NOC_CountryRegion and Sport appear. Arrange and size them to align properly next to the PivotTable.

two slicers

  1. Select USA in the NOC_CountryRegion Slicer.

  2. Select Aquatics in the Sport Slicer. The PivotTable is filtered to the selected values.

selected values

如您所见，数据透视图未进行筛选。要使用相同筛选器筛选数据透视图，您不需要为数据透视图重新插入筛选器。您可以使用为数据透视表使用的相同筛选器。

As you can observe, the PivotChart is not filtered. To filter PivotChart with the same filters, you need not insert Slicers again for PivotChart. You can use the same Slicers that you have used for the PivotTable.

  1. Click on NOC_CountryRegion Slicer.

  2. Click the OPTIONS tab in SLICER TOOLS on the Ribbon.

  3. Click Report Connections in the Slicer group. The Report Connections dialog box appears for the NOC_CountryRegion Slicer.

options tab

您会看到对话框中列出了工作簿中的所有数据透视表和数据透视图。

You can see that all the PivotTables and PivotCharts in the workbook are listed in the dialog box.

  1. Click on the PivotChart that is in the same worksheet as the selected PivotTable and click OK.

  2. Repeat for Sport Slicer.

workbook

数据透视图也将根据在两个筛选器中所选的值进行筛选。

The PivotChart is also filtered to the values selected in the two Slicers.

filtered

接下来，您可以在数据透视图和数据透视表中添加详细信息。

Next, you can add details to the PivotChart and PivotTable.

  1. Click the PivotChart.

  2. Drag Gender to LEGEND area.

  3. Right click on the PivotChart.

  4. Select Change Chart Type.

  5. Select Stacked Column in the Change Chart Type dialog box.

  6. Click on the PivotTable.

  7. Drag Event to ROWS area.

  8. Click the DESIGN tab in PIVOTTABLE TOOLS on the Ribbon.

  9. Click Report Layout.

  10. Select Outline Form from the dropdown list.

legend area

Selecting Objects for Display in the Report

可以选择在最终报表中不显示筛选器。

You can choose not to display the Slicers on the final Report.

  1. Click the OPTIONS tab in SLICER TOOLS on the Ribbon.

  2. Click Selection Pane in Arrange group. The Selection Pane appears on the right side of the window.

selection pane

如您所见，符号 将显示在选择窗格中的对象旁边。这意味着这些对象是可见的。

As you can observe, the symbol appears next to the objects in the Selection Pane. This means those objects are visible.

  1. Click on symbol next to NOC_CountryRegion.

  2. Click on symbol next to Sport. The symbol is changed to for both. This means the visibility for the two Slicers is off.

symbol

关闭选择窗格。

Close the Selection Pane.

close selection pane

你可以看到报表中看不到两个切片器。

You can see that the two Slicers are not visible in the Report.