Excel Data Analysis 简明教程

Exploring Data with PivotTables

你可以使用数据透视表进行广泛的数据分析并生成所需的报告。数据模型与数据透视表的集成增强了对数据进行整理、连接、汇总和报告的方式。你可以从外部数据源导入表格，并使用导入的表格创建数据透视表。这在连接的数据源中的数据更新时，便于数据透视表中值自动更新。

You can do extensive data analysis using PivotTables and produce desired reports. The integration of Data Model with PivotTable enhances the way the data is collated, connected, summarized and reported. You can import tables from external data sources and create a PivotTable with the imported tables. This facilitates automatic updations of the values in the PivotTable whenever the data in the connected data sources is updated.

Creating a PivotTable to analyze External Data

要创建数据透视表来分析外部数据−

To create a PivotTable to analyze external data −

  1. Open a new blank workbook in Excel.

  2. Click the DATA tab on the Ribbon.

  3. Click From Access in the Get External Data group. The Select Data Source dialog box appears.

  4. Select the Access database file.

create pivottable

  1. Click the Open button. The Select Table dialog box appears, displaying the tables in the database. Access database is a relational database and the tables will be similar to Excel tables, with the exception that relationships exist among those tables.

  2. Check the box Enable selection of multiple tables.

  3. Select all the tables. Click OK.

select tables

Import Data 对话框出现。选择动态数据透视表。该选项将表导入您的 Excel 工作簿并创建一个动态数据透视表来分析导入的表。

The Import Data dialog box appears. Select PivotTable Report. This option imports the tables into your Excel workbook and creates a PivotTable for analyzing the imported tables.

analyze imported tables

正如您所观察到的，已选中并禁用了“将此数据添加到数据模型”复选框，表明表将被自动添加到数据模型。

As you observe, the checkbox Add this data to the Data Model is selected and disabled, indicating that the tables will be added to the Data Model automatically.

数据将导入，并且会创建一个空的动态数据透视表。导入的表会显示在动态数据透视表字段列表中。

The data will be imported and an empty PivotTable will be created. The imported tables appear in the PivotTable Fields list.

pivottable fields

Exploring Data in Multiple Tables

您可以在短短几步内使用动态数据透视表分析来自导入的多个表的，并获得您想要的特定报表。这是因为源数据库中的表之间存在预先的关联。当您同时从数据库中导入所有表时，Excel 会在其数据模型中重新创建关联关系。

You can analyze the data from the imported multiple tables with PivotTable and arrive at the specific report you want in just few steps. This is possible because of the pre-existing relationships among the tables in the source database. As you imported all the tables from the database together at the same time, Excel recreates the relationships in its Data Model.

在动态数据透视表字段列表中，您将找到导入的所有表以及其中每一张表中的字段。如果任何表中看不到字段，

In the PivotTable Fields list, you will find all the tables that you imported and the fields in each of them. If the fields are not visible for any table,

  1. Click on the arrow next to that table in the PivotTable Fields list.

  2. The fields in that table will be displayed.

Exploring Data using PivotTable

您知道如何将字段添加到动态数据透视表，并在各个区域之间拖放字段。即使您不确定想要的最终报表，您也可以利用数据并选择合适的报表。

You know how to add fields to PivotTable and drag fields across areas. Even if you are not sure of the final report that you want, you can play with the data and choose the appropriate report.

假设您想要显示以下内容的报表 -

Suppose you want to have a report displaying the following −

  1. Data for five disciplines - Archery, Diving, Fencing, Figure Skating and Speed Skating.

  2. Regions that scored more than 80 medals in these 5 disciplines.

  3. The count of medals in each of the five disciplines in each of these regions.

  4. Total count of medals for the five disciplines in each of these regions.

您可以看到，只需几个步骤即可轻松创建此报表。

You can see how easily you can create this report in few steps.

首先，创建一个动态数据透视表，显示如下所示所选五项运动的所有地区的奖牌数 -

To start with, create a PivotTable displaying the count of medals in all the regions for the selected five disciplines as follows −

  1. Drag the NOC_CountryRegion field from the Medals table to the COLUMNS area.

  2. Drag Discipline from the Disciplines table to the ROWS area.

  3. Filter Discipline to display only the five disciplines for which you wanted the report. This can be done either in the PivotTable Fields area, or from the Row Labels filter in the PivotTable itself.

  4. Drag Medal from the Medals table to the VALUES area.

  5. Drag Medal from the Medals table to the FILTERS area.

您将获得以下透视表 −

You will get the following PivotTable −

drag medal

正如您所看到的，将显示所有区域中及您选择的五项学科的奖牌数。接下来，您必须微调此报表，以便仅显示奖牌总数超过 80 的那些区域。

As you observe, Count of Medals is displayed for all the regions and for the five disciplines that you selected. Next, you have to fine-tune this report so that only those regions with total count of medals greater than 80 will be displayed.

  1. Click the arrow button to the right of Column Labels.

  2. Click Value Filters in the drop-down list that appears.

  3. Select Greater Than… from the drop-down list that appears.

greater than

将出现“值筛选器”对话框。

Value Filters dialog box appears.

value filters

正如您所看到的，奖牌数和大于将显示在 Show items for which 下方的框中。在包含大于的框旁边的框中输入 80，然后单击“确定”。

As you observe, Count of Medals and is greater than are displayed in the boxes below Show items for which. Type 80 in the box next to the box containing is greater than and click OK.

show items

现在，透视表将仅显示在选定的五个科目中奖牌总数大于 80 的那些区域。

Now, the PivotTable displays only those regions with total count of medals in the selected five disciplines greater than 80.

pivottable displays

Creating a Relationship between Tables with PivotTable Fields

如果您没有同时导入表，如果数据来自不同的来源，或如果您向工作簿中添加新表，则您必须自己创建表之间的关系。

If you do not import the tables at the same time, if the data is from different sources, or if you add new tables to your workbook, you have to create the relationships among the tables by yourself.

向工作簿中添加包含 Sport 和 SportID 字段的新工作表。

Add a new worksheet with a table that contains Sport and SportID fields to your workbook.

sport and sportid

  1. Name the table - Sports.

  2. Click ALL in the PivotTable Fields list in the PivotTable worksheet.

sports table

您会看到新添加的表 - “运动”也在“透视表字段”列表中可见。

You can see that the newly added table- Sports is also visible in the PivotTable Fields list.

接下来，还将字段 Sport 添加到透视表，如下所示 −

Next, add the field Sport also to the PivotTable as follows −

  1. Drag the field Sport from the table Sports to ROWS area. The Sport values appear as Row labels in the PivotTable.

  2. A message will appear in the PivotTable Fields list that Relationships between tables may be needed. A CREATE button appears next to the message.

message to create relations

单击“创建”按钮。将出现“创建关系”对话框。

Click the CREATE button. The Create Relationship dialog box appears.

create relationship dialog box

  1. Select Medals under Table.

  2. Select Sport under Column.

  3. Select Sports under Related Table. Sport appears under related column.

  4. Click OK.

select sports

将学科拖到 Sport 中的 ROWS 中。这是为了定义数据透视表中的层次结构。数据透视表显示运动及其相应学科组。

Drag Discipline under Sport in ROWS. This is to define the hierarchy in the PivotTable. The PivotTable displays the Sport and the corresponding group of disciplines for that sport.

sport in rows