Excel Power Pivot 简明教程

Excel Power Pivot - Installing

Excel 中的 Power Pivot 提供了一个数据模型，可以连接各种不同的数据源，基于这些数据源可以分析、可视化和探索数据。Power Pivot 提供的易于使用的界面使具有 Excel 实践经验的人员能够毫不费力地加载数据、管理数据（作为数据表）、创建数据表之间的关系并执行必要的计算以形成报告。

Power Pivot in Excel provides a Data Model connecting various different data sources based on which the data can be analyzed, visualized, and explored. The easy-to-use interface provided by Power Pivot enables a person with hands-on experience in Excel to effortlessly load data, manage the data as data tables, create relationships among the data tables, and perform the required calculations to arrive at a report.

在本章中，你将了解是什么使 Power Pivot 成为分析师和决策者不可或缺的强大工具。

In this chapter, you will learn, what makes Power Pivot a strong and sought after tool for analysts and decision makers.

Power Pivot on the Ribbon

开始使用 Power Pivot 的第一步是确保功能区上显示 POWERPIVOT 选项卡。如果您使用的是 Excel 2013 或更高版本，则功能区上会显示 POWERPIVOT 选项卡。

The first step to proceed with Power Pivot is to ensure that the POWERPIVOT tab is available on the Ribbon. If you have Excel 2013 or later versions, the POWERPIVOT tab appears on the Ribbon.

power pivot on ribbon

如果您使用的是 Excel 2010，则 POWERPIVOT 选项卡可能不会显示在功能区上，只要您尚未启用 Power Pivot 加载项。

If you have Excel 2010, POWERPIVOT tab might not appear on the Ribbon if you have not already enabled the Power Pivot add-in.

Power Pivot Add-in

Power Pivot 加载项是一个 COM 加载项，需要启用它才能在 Excel 中获取 Power Pivot 的完整功能。即使功能区上显示了 POWERPIVOT 选项卡，您还需要确保启用该加载项才能访问 Power Pivot 的所有功能。

Power Pivot Add-in is a COM Add-in that needs to be enabled to get the complete features of Power Pivot in Excel. Even when POWERPIVOT tab appears on the ribbon, you need to ensure that the add-in is enabled to access all the features of Power Pivot.

Step 1 - 单击功能区上的文件选项卡。

Step 1 − Click the FILE tab on the Ribbon.

Step 2 - 单击下拉列表中的选项。此时会显示 Excel 选项对话框。

Step 2 − Click Options in the dropdown list. The Excel Options dialog box appears.

addins

Step 3 - 按照以下说明进行操作。

Step 3 − Follow the instructions as follows.

  1. Click Add-Ins.

  2. In the Manage box, select COM Add-ins from the dropdown list.

  3. Click the Go button. The COM Add-Ins dialog box appears.

  4. Check Power Pivot and click OK.

What is Power Pivot?

Excel Power Pivot 是用于集成和处理大量数据的一个工具。使用 Power Pivot，您可以轻松地在包含数百万行的各种数据中加载、排序和筛选数据，以及执行所需的计算。您可以利用 Power Pivot 作为一种即席报告和分析解决方案。

Excel Power Pivot is a tool for integrating and manipulating large volumes of data. With Power Pivot, you can easily load, sort and filter data sets that contain millions of rows and perform the required calculations. You can utilize Power Pivot as an ad hoc reporting and analytics solution.

如下所示的 Power Pivot 功能区包含各种命令，从管理数据模型到创建报表。

The Power Pivot Ribbon as shown below has various commands, ranging from managing Data Model to creating reports.

powerpivot

Power Pivot 窗口的特色功能区如下所示 -

The Power Pivot window will have the Ribbon as shown below −

powerpivot window

Why is Power Pivot a Strong Tool?

调用 Power Pivot 时，Power Pivot 会创建数据定义和连接，并将其以压缩形式与您的 Excel 文件一起存储。当源中的数据更新时，它会在您的 Excel 文件中自动刷新。这就使得可以方便地使用在别处维护的数据，而只需不时地进行研究和决策制定即可。源数据可以采用任何形式 - 从文本文件或网页到不同的关系数据库。

When you invoke Power Pivot, Power Pivot creates data definitions and connections that get stored with your Excel file in a compressed form. When the data at the source is updated, it is refreshed automatically in your Excel file. This facilitates the usage of the data maintained elsewhere but is required for study time-to-time study and arriving at decisions. The source data can be in any form − ranging from a text file or a web page to the different relational databases.

PowerPivot 窗口中 Power Pivot 的用户友好界面使您无需了解任何数据库查询语言即可执行数据操作。然后，您可以在几秒钟内创建分析报告。报告通用、动态且交互性强，可让您进一步探查数据，以获得见解并得出结论/决策。

The user-friendly interface of Power Pivot in the PowerPivot window enables you to perform data operations without the knowledge of any database query language. You can then create a report of your analysis within few seconds. The reports are versatile, dynamic and interactive and enable you to further probe into the data to get the insights and arrive at the conclusions / decisions.

您在 Excel 中及 Power Pivot 窗口中处理的数据存储在 Excel 工作簿中一个分析数据库中，一个强大的本地引擎会加载、查询和更新该数据库中的数据。因为数据位于 Excel 中，所以会立即提供给数据透视表、数据透视图、Power View 以及 Excel 中您用来聚合数据和与数据交互的其他功能。由 Excel 提供数据演示和交互性，且数据和 Excel 演示对象包含在同一个工作簿文件中。Power Pivot 支持大小高达 2 GB 的文件，并且能让您在内存中处理高达 4 GB 的数据。

The data that you work on in Excel and in the Power Pivot window is stored in an analytical database inside the Excel workbook, and a powerful local engine loads, queries, and updates the data in that database. Since the data is in Excel, it is immediately available to PivotTables, PivotCharts, Power View, and other features in Excel that you use to aggregate and interact with the data. The data presentation and interactivity is provided by Excel and the data and Excel presentation objects are contained within the same workbook file. Power Pivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.

Power Features to Excel with Power Pivot

Power Pivot 功能随 Excel 免费提供。Power Pivot 通过强大的功能增强了 Excel 性能，这些功能包括：

Power Pivot features are free with Excel. Power Pivot has enhanced the Excel performance with power features that include the following −

  1. Ability to handle large data volumes, compressed into small files, with amazing speed.

  2. Filter data and rename columns and tables while importing.

  3. Organize tables into individual tabbed pages in the Power Pivot window as against the Excel tables distributed all over the workbook or multiple tables in the same worksheet.

  4. Create relationships among the tables, so as to analyze the data in the tables collectively. Before Power Pivot, one had to rely on heavy usage of VLOOKUP function to combine the data into a single table before such analysis. This used to be laborious and error-prone.

  5. Add power to the simple PivotTable with many added features.

  6. Provide Data Analysis Expressions (DAX) language to write advanced formulas.

  7. Add calculated fields and calculated columns to the data tables.

  8. Create KPIs to use in PivotTables and Power View reports.

您將在下一章詳細了解 Power Pivot 功能。

You will understand the Power Pivot features in detail in the next chapter.

Uses of Power Pivot

您可以将 Power Pivot 用于以下用途−

You can use Power Pivot for the following −

  1. To perform powerful data analysis and create sophisticated Data Models.

  2. To mash-up large volumes of data from several different sources quickly.

  3. To perform information analysis and share the insights interactively.

  4. To write advanced formulas with the Data Analysis Expressions (DAX) language.

  5. To create Key Performance Indicators (KPIs).

Data Modelling with Power Pivot

Power Pivot 提供 Excel 中的進階資料建模功能。Power Pivot 中的資料是由資料模型管理，資料模型也稱為 Power Pivot 資料庫。您可以使用 Power Pivot 協助您深入了解您的資料。

Power Pivot provides advanced data modeling features in Excel. The data in the Power Pivot is managed in the Data Model that is also referenced as Power Pivot database. You can use Power Pivot to help you gain new insights into your data.

您可以建立資料表之間的關聯，以便對資料表進行資料分析。透過 DAX，您可以撰寫進階公式。您可以在資料模型中資料表中建立計算欄位和計算欄。

You can create relationships between data tables so that you can perform data analysis on the tables collectively. With DAX, you can write advanced formulas. You can create calculated fields and calculated columns in the data tables in the Data Model.

您可以定義資料中要在工作簿各處使用的階層，包括 Power View。您可以建立 KPI，以便在樞紐分析表和 Power View 報告中使用，用以一目了然地顯示是否超過或低於一個或多個指標的效能目標。

You can define Hierarchies in the data to use everywhere in the workbook, including Power View. You can create KPIs to use in PivotTables and Power View reports to show at a glance whether performance is on or off target for one or more metrics.

Business Intelligence with Power Pivot

商業智慧 (BI) 基本上是一套工具和流程，人們用來收集資料、將資料轉換為有意義的資訊，然後做出更明智的決策。Excel 中 Power Pivot 的 BI 功能讓您能夠收集資料、視覺化資料，並與組織中的人員分享資訊，同時涵蓋多部裝置。

Business intelligence (BI) is essentially the set of tools and processes that people use to gather data, turn it into meaningful information, and then make better decisions. The BI capabilities of Power Pivot in Excel enable you to gather data, visualize data, and share information with people in your organization across multiple devices.

您可以將工作簿分享到已啟用 Excel 服務的 SharePoint 環境。在 SharePoint 伺服器上，Excel 服務會處理資料並將其呈現於瀏覽器視窗中，其他人在視窗中可以分析資料。

You can share your workbook to a SharePoint environment that has Excel Services enabled. On the SharePoint server, Excel Services processes and renders the data in a browser window where others can analyze the data.