Excel Data Analysis 简明教程

Excel Data Analysis - PivotTables

对大型数据集合进行数据分析通常是必要且重要的。它涉及汇总数据、获得所需值和呈现结果。

Data analysis on a large set of data is quite often necessary and important. It involves summarizing the data, obtaining the needed values and presenting the results.

Excel 提供了数据透视表，以便您轻松快速地汇总数千个数据值，从而获得所需的结果。

Excel provides PivotTable to enable you summarize thousands of data values easily and quickly so as to obtain the required results.

考虑以下销售数据表。从这些数据中，您可能需要按区域、按月或按销售人员汇总总销售额。处理这些任务的简单方法是创建一个数据透视表，您可以对其进行动态修改，以按您想要的方式汇总结果。

Consider the following table of sales data. From this data, you might have to summarize total sales region wise, month wise, or salesperson wise. The easy way to handle these tasks is to create a PivotTable that you can dynamically modify to summarize the results the way you want.

sales data table

Creating PivotTable

要创建数据透视表，请确保第一行包含标题。

To create PivotTables, ensure the first row has headers.

  1. Click the table.

  2. Click the INSERT tab on the Ribbon.

  3. Click PivotTable in the Tables group. The PivotTable dialog box appears.

creating pivottable

如您在对话框中所见，您可以使用当前工作簿中的“表格”或“区域”，也可以使用外部数据源。

As you can see in the dialog box, you can use either a Table or Range from the current workbook or use an external data source.

  1. In the Table / Range Box, type the table name.

  2. Click New Worksheet to tell Excel where to keep the PivotTable.

  3. Click OK.

table range box

将显示一个空白数据透视表和一个数据透视表字段列表。

A Blank PivotTable and a PivotTable fields list appear.

blank pivottable fields

Recommended PivotTables

如果您是数据透视表的新手，或者您不知道从数据中选择哪些字段，则可以使用 Excel 提供的 Recommended PivotTables 。

In case you are new to PivotTables or you do not know which fields to select from the data, you can use the Recommended PivotTables that Excel provides.

  1. Click the data table.

  2. Click the INSERT tab.

  3. Click on Recommended PivotTables in the Tables group. The Recommended PivotTables dialog box appears.

recommended pivottables

在推荐数据透视表对话框中，显示了适合您数据的可能的自定义数据透视表。

In the recommended PivotTables dialog box, the possible customized PivotTables that suit your data are displayed.

  1. Click each of the PivotTable options to see the preview on the right side.

  2. Click the PivotTable Sum of Order Amount by Salesperson and month.

recommended pivottables dialog box

单击“确定”。选定的数据透视表将显示在一个新工作表中。您可以在数据透视表字段列表中观察到选定的数据透视表字段。

Click OK. The selected PivotTable appears on a new worksheet. You can observe the PivotTable fields that was selected in the PivotTable fields list.

pivottable fields list

PivotTable Fields

数据表中的标题将显示为数据透视表中的字段。

The headers in your data table will appear as the fields in the PivotTable.

header pivottable fields

您可以选择/取消选择它们以立即更改数据透视表，以仅显示您想要的信息，并且按照您想要的方式显示。例如，如果您希望显示帐户信息而不是订单金额信息，请取消选择“订单金额”并选择“帐户”。

You can select / deselect them to instantly change your PivotTable to display only the information you want and in a way that you want. For example, if you want to display the account information instead of order amount information, deselect Order Amount and select Account.

select deselect pivottable fields

PivotTable Areas

您甚至可以立即更改数据透视表的布局。您可以使用“数据透视表区域”来完成此操作。

You can even change the Layout of your PivotTable instantly. You can use the PivotTable Areas to accomplish this.

pivottable areas

在数据透视表区域中，您可以选择 −

In PivotTable areas, you can choose −

  1. What fields to display as rows

  2. What fields to display as columns

  3. How to summarize your data

  4. Filters for any of the fields

  5. When to update your PivotTable Layout You can update it instantly as you drag the fields across areas, orYou can defer the update and get it updated only when you click on UPDATE

即时更新有助于使用不同布局，并选择最适合报告要求的布局。

An instant update helps you to play around with the different Layouts and pick the one that suits your report requirement.

只需将字段拖到这些区域，然后即可查看数据透视表布局。

You can just drag the fields across these areas and observe the PivotTable layout as you do it.

observe pivottable layout

Nesting in the PivotTable

如果您在任何区域中有多个字段，则嵌套将按照您在该区域中放置字段的顺序进行。可以通过拖动字段并观察嵌套如何改变来改变顺序。在上述布局选项中，您可以观察到

If you have more than one field in any of the areas, then nesting happens in the order you place the fields in that area. You can change the order by dragging the fields and observe how nesting changes. In the above layout options, you can observe that

  1. Months are in columns.

  2. Region and salesperson in rows in that order. i.e. salesperson values are nested under region values.

  3. Summarizing is by Sum of Order Amount.

  4. No filters are chosen.

结果数据透视表如下 −

The resulting PivotTable is as follows −

pivottable result

在数据透视表区域中，在行中，单击区域并将其拖动到销售人员下方，使其如下所示 −

In the PivotTable Areas, in rows, click region and drag it below salesperson such that it looks as follows −

salesperson

嵌套顺序改变，得到的数据透视表如下 −

The nesting order changes and the resulting PivotTable is as follows −

nesting order changes

Note − 您清楚地观察到，嵌套顺序为区域，然后是销售人员的布局比嵌套顺序为销售人员，然后是区域的布局产生了更好、更简洁的报告。如果销售人员代表多个区域，并且您需要按销售人员汇总销售，那么第二个布局将是一个更好的选择。

Note − You can clearly observe that the layout with the nesting order – Region and then Salesperson yields a better and compact report than the one with the nesting order – Salesperson and then Region. In case Salesperson represents more than one area and you need to summarize the sales by Salesperson, then the second layout would have been a better option.

Filters

您可以为某个字段分配筛选器，以便您可以根据该字段的值动态更改数据透视表。

You can assign a Filter to one of the fields so that you can dynamically change the PivotTable based on the values of that field.

将区域从行拖动到数据透视表区域中的筛选器。

Drag Region from Rows to Filters in the PivotTable Areas.

filters

带有区域标签的筛选器出现在数据透视表上方（如果您在数据透视表上方没有空行，则数据透视表将被向下推以腾出空间用于筛选器）。

The filter with the label as Region appears above the PivotTable (in case you do not have empty rows above your PivotTable, PivotTable gets pushed down to make space for the Filter.

filter with label

您可以看到 −

You can see that −

  1. Salesperson values appear in rows.

  2. Month values appear in columns.

  3. Region Filter appears on the top with default selected as ALL.

  4. Summarizing value is Sum of Order Amount Sum of Order Amount Salesperson-wise appears in the column Grand TotalSum of Order Amount Month-wise appears in the row Grand Total

单击筛选区域右侧框中的箭头。将出现一个下拉列表，其中包含区域字段的值。

Click the arrow in the box to the right of the filter region. A drop-down list with the values of the field region appears.

field values

  1. Check the option Select Multiple Items. Check boxes appear for all the values.

  2. Select South and West and deselect the other values and click OK.

select multiple items

仅筛选出“南方”和“西方”区域相关的数据，如图例所示 -

The data pertaining to South and West Regions only will be summarized as shown in the screen shot given below −

data pertaining

您会在“筛选区域”旁边看到已显示 Multiple Items ，表示您选择了一项以上内容。但是，从报告中无法得知选择了多少项和/或选择了哪些项。这时，使用切片器是筛选的较佳选项。

You can see that next to the Filter Region, Multiple Items is displayed, indicating that you have selected more than one item. However, how many items and / or which items are selected is not known from the report that is displayed. In such a case, using Slicers is a better option for filtering.

Slicers

您可以使用切片器更清晰地了解哪些项目经过数据筛选。

You can use Slicers to have a better clarity on which items the data was filtered.

  1. Click ANALYZE under PIVOTTABLE TOOLS on the Ribbon.

  2. Click Insert Slicer in the Filter group. The Insert Slicers box appears. It contains all the fields from your data.

  3. Select the fields Region and month. Click OK.

slicers

所有选定字段的切片器都默认选中所有值后显示。切片器工具随即显示在功能区上，用于设定切片器设置、外观和风格。

Slicers for each of the selected fields appear with all the values selected by default. Slicer Tools appear on the Ribbon to work on the Slicer settings, look and feel.

selected fields

  1. Select South and West in the Slicer for Region.

  2. Select February and March in the Slicer for month.

  3. Keep Ctrl key pressed while selecting multiple values in a Slicer.

切片器中选定的项目将突出显示。将显示选定项目的汇总值透视表。

Selected items in the Slicers are highlighted. PivotTable with summarized values for the selected items will be displayed.

selected items highlighted

Summarizing Values by other Calculations

在前面的示例中，您已了解如何通过求和来汇总值。但如有必要，您还可以使用其他计算。

In the examples so far, you have seen summarizing values by Sum. However, you can use other calculations also if necessary.

在透视表字段列表中

In the PivotTable Fields List

  1. Select the Field Account.

  2. Unselect the Field Order Amount.

summarizing values

  1. Drag the field Account to Summarizing Values area. By default, Sum of Account will be displayed.

  2. Click the arrow on the right side of the box.

  3. In the drop-down that appears, click Value Field Settings.

value field settings

“值字段设置”框将出现。列表下会显示几种类型的计算，以按值字段汇总 -

The Value Field Settings box appears. Several types of calculations appear as a list under Summarize value field by −

  1. Select Count in the list.

  2. The Custom Name automatically changes to Count of Account. Click OK.

select count

数据透视表按计数汇总帐户值。

The PivotTable summarizes the Account values by Count.

summarizes account values

PivotTable Tools

按照以下步骤操作，了解如何使用数据透视表工具。

Follow the steps given below to learn to use the PivotTable Tools.

  1. Select the PivotTable.

以下数据透视表工具显示在功能区上 -

The following PivotTable Tools appear on the Ribbon −

  1. ANALYZE

  2. DESIGN

pivottable tools

ANALYZE

某些 ANALYZE 功能区命令如下 -

Some of the ANALYZE Ribbon commands are −

  1. Set PivotTable Options

  2. Value Field Settings for the selected Field

  3. Expand Field

  4. Collapse Field

  5. Insert Slicer

  6. Insert Timeline

  7. Refresh Data

  8. Change Data Source

  9. Move PivotTable

  10. Solve Order (If there are more calculations)

  11. PivotChart

DESIGN

某些 DESIGN 功能区命令如下 -

Some of the DESIGN Ribbon commands are −

  1. PivotTable Layout Options for Sub TotalsOptions for Grand TotalsReport Layout FormsOptions for Blank Rows

  2. PivotTable Style Options

  3. PivotTable Styles

Expanding and Collapsing Field

您可以通过两种方式展开或折叠所选字段的所有项 -

You can either expand or collapse all items of a selected field in two ways −

  1. By selecting the symbol or to the left of the selected field.

  2. By clicking the Expand Field or Collapse Field on the ANALYZE Ribbon.

By selecting the Expand symbol or Collapse symbol to the left of the selected field

  1. Select the cell containing East in the PivotTable.

  2. Click on the Collapse symbol to the left of East.

expanding and collapsing field

East 下的所有项都将折叠。East 左侧的折叠符号更改为展开符号。

All the items under East will be collapsed. The Collapse symbol to the left of East changes to the Expand symbol .

collapse and expand

您可以观察到，只有 East 下的项被折叠。数据透视表的其余项保持原样。

You can observe that only the items below East are collapsed. The rest of the PivotTable items are as they are.

单击 East 左侧的展开符号。将显示 East 下的所有项。

Click the Expand symbol to the left of East. All the items below East will be displayed.

Using ANALYZE on the Ribbon

您可以在功能区上使用“展开字段”和“折叠字段”命令，一次性折叠或展开数据透视表中的所有项目。

You can collapse or expand all items in the PivotTable at once with the Expand Field and Collapse Field commands on the Ribbon.

  1. Click the cell containing East in the PivotTable.

  2. Click the ANALYZE tab on the Ribbon.

  3. Click Collapse Field in the Active Field group.

using analyze

数据透视表中字段 East 的所有项目将折叠。

All the items of the field East in the PivotTable will collapse.

east field items

单击“活动字段”组中的“展开字段”。

Click Expand Field in the Active Field group.

expand field

所有项目将显示。

All the items will be displayed.

Report Presentation Styles

您可以选择数据透视表的显示样式，就像要将其包含在报告中一样。选择一种适合您演示或报告中其他内容的样式。但是，不要对样式过于沉迷，因为显示结果时产生影响的报告总是比色彩鲜艳但没有突出显示重要数据点的报告要好。

You can choose the presentation style for your PivotTable as you would be including it as a report. Select a style that fits into the rest of your presentation or report. However, do not get over bored with the styles because a report that gives an impact in showing the results is always better than a colorful one, which does not highlight the important data points.

  1. Click East in the PivotTable.

  2. Click ANALYZE.

  3. Click Field Settings in Active Field group. The Field Settings dialog box appears.

  4. Click the Layout & Print tab.

  5. Check Insert blank line after each item label.

report presentation styles

将在区域字段的每个值后面显示空行。

Blank rows will be displayed after each value of the Region field.

您还可以从“@ {s0}”选项卡中插入空行。

You can insert blank rows from the DESIGN tab also.

insert blank rows

  1. Click the DESIGN tab.

  2. Click Report Layout in Layout group.

  3. Select Show in Outline Form in the drop-down list.

outline form

  1. Hover the mouse over the PivotTable Styles. A preview of the style on which the mouse is placed will appear.

  2. Select the Style that suits your report.

将显示具有所选样式的轮廓形式的数据透视表。

PivotTable in Outline Form with the selected Style will be displayed.

displays selected style

Timeline in PivotTables

为了理解如何使用时间线，请考虑下面的示例，其中销售数据按销售人员和地区分组。数据共有 1891 行。

To understand how to use Timeline, consider the following example wherein the sales data of various items is given salesperson wise and location wise. There are total 1891 rows of data.

timeline

用此范围创建一个数据透视表，其中 −

Create a PivotTable from this Range with −

  1. Location and Salesperson in Rows in that order

  2. Product in Columns

  3. Sum of Amount in Summarizing values

range

  1. Click the PivotTable.

  2. Click INSERT tab.

  3. Click Timeline in Filters group. The Insert Timelines appears.

insert timelines

单击日期，然后单击确定。将显示时间线对话框，并且时间线工具显示在功能区上。

Click Date and click OK. The Timeline dialog box appears and the Timeline Tools appear on the Ribbon.

ribbon

  1. In Timeline dialog box, select MONTHS.

  2. From the drop-down list select QUARTERS.

  3. Click 2014 Q2.

  4. Keep the Shift key pressed and drag to 2014 Q4.

时间线已选择 2014 第 2 季度至第 4 季度。

Timeline is selected to Q2 – Q4 2014.

数据透视表已筛选到此时间线。

PivotTable is filtered to this Timeline.

pivottable filtered