Excel Pivot Tables 简明教程

Excel Pivot Tables - Overview

数据透视表是一个强大的工具，你可以使用它来切片和处理数据。你可以使用动态更改的紧凑型表格来追踪和分析数以千计的数据点，以便帮助你发现数据的不同视角。它是一个简单易用，却又强大的工具。

A PivotTable is an extremely powerful tool that you can use to slice and dice data. You can track and analyze hundreds of thousands of data points with a compact table that can be changed dynamically to enable you to find the different perspectives of the data. It is a simple tool to use, yet powerful.

数据透视表的主要功能如下 −

The major features of a PivotTable are as follows −

  1. Creating a PivotTable is extremely simple and fast

  2. Enabling churning of data instantly by simple dragging of fields, sorting and filtering and different calculations on the data.

  3. Arriving at the suitable representation for your data as you gain insights into it.

  4. Ability to create reports on the fly.

  5. Producing multiple reports from the same PivotTable in a matter of seconds.

  6. Providing interactive reports to synchronize with the audience.

在本教程中，你将详细了解这些数据透视表功能以及示例。当你完成本教程时，你将对数据透视表功能有足够的了解，可以开始探索、分析和根据要求报告数据。

In this tutorial, you will understand these PivotTable features in detail along with examples. By the time you complete this tutorial, you will have sufficient knowledge on PivotTable features that can get you started with exploring, analyzing, and reporting data based on the requirements.

Creating a PivotTable

你可以从数据范围或 Excel 表格中创建数据透视表。如果你知道自己要找什么，则可以从一个空白的数据透视表开始填写详细信息。你还可以利用 Excel 推荐的数据透视表，它可以为你提供最适合汇总数据的透视表布局。

You can create a PivotTable from a range of data or an Excel table. You can start with an empty PivotTable to fill in the details, if you are aware of what you are looking for. You can also make use of Excel Recommended PivotTables that can give you heads up on the PivotTable layouts that are best suited for summarizing your data.

你将在章节 - 从表格或范围创建数据透视表中学习如何从数据范围或 Excel 表中创建数据透视表。

You will learn how to create a PivotTable from a data range or Excel table in the Chapter - Creating a PivotTable from a Table or Range.

Excel 为你提供了一种更强大的方式，可以从多个表格、不同的数据源和外部数据源创建数据透视表。它被命名为 PowerPivot，它在其被称为数据模型的数据库上工作。你将在本教程库中的其他教程中学习这些 Excel 强大工具。

Excel gives you a more powerful way of creating a PivotTable from multiple tables, different data sources, and external data sources. It is named as PowerPivot that works on its database known as Data Model. You will learn these Excel power tools in other tutorials in this Tutorials Library.

在你冒险使用强大工具之前，你需要先了解本教程中解释的普通数据透视表。

You need to first know about the normal PivotTable as explained in this tutorial, before you venture into the power tools.

PivotTable Layout - Fields and Areas

数据透视表布局取决于为报表选择的字段以及如何将它们排列在区域中。只需拖动字段即可进行选择和排列。拖动字段时，数据透视表布局将保持更改，并且在几秒钟内发生。

The PivotTable layout simply depends on what fields you have selected for the report and how you have arranged them in Areas. The selection and arrangement can be done by just dragging the fields. As you drag the fields, the PivotTable layout keeps the changing and it happens in a matter of seconds.

您将在章节数据透视表字段和数据透视表区域中了解数据透视表字段和区域。

You will learn about PivotTable Fields and Areas in the Chapters – PivotTable Fields and PivotTable Areas.

Exploring Data with PivotTable

使用数据透视表的首要目标通常是浏览数据以提取重要且必需的信息。您可以执行一些操作，包括排序、筛选、嵌套、折叠和展开、分组和取消分组等。

The primary goal of using a PivotTable normally is to explore the data to extract significant and required information. You have several options to do this that include Sorting, Filtering, Nesting, Collapsing and Expanding, Grouping and Ungrouping, etc.

您将在“使用数据透视表浏览数据”一章中概述这些选项。

You will have an overview of these options in the Chapter - Exploring Data with PivotTable.

Summarizing Values

一旦通过不同的浏览技术整理了所需数据，您要采取的下一步就是汇总数据。Excel 为您提供了各种计算类型，您可以根据适用性和要求应用这些类型。您还可以切换不同的计算类型并在几秒钟内查看结果。

Once you collate the data required by you by the different exploration techniques, the next step that you would like to take is to summarize the data. Excel provides you with a variety of calculation types that you can apply based on suitability and requirement. You can also switch across different calculation types and view the results in a matter of seconds.

您将在“按不同计算类型汇总值”一章中学习如何在数据透视表上应用计算类型。

You will learn how to apply the calculation types on a PivotTable in the Chapter - Summarizing Values by Different Calculation Types.

Updating a PivotTable

浏览并汇总数据后，如果源数据得到更新，您无需重复此练习。您可以刷新数据透视表，使其反映源数据中的更改。

Once you have explored the data and summarized it, you need not repeat the exercise if and when the source data gets updated. You can refresh the PivotTable so that it reflects the changes in the source data.

您将在“更新数据透视表”一章中学习刷新数据的各种方式。

You will learn the various ways of refreshing data in the Chapter – Updating a PivotTable.

PivotTable Reports

使用数据透视表探索并汇总数据之后，您会将其作为报表显示。数据透视表报表本质上是交互式的，即使是不熟悉 Excel 的人也能够直观地使用它。由于其固有的动态特性，它们将使您能够快速更改报表透视图，以显示所需の詳細级别或关注听众感兴趣的具体项目。

After exploring and summarizing the data with a PivotTable, you would be presenting it as a report. PivotTable reports are interactive in nature, with the specialty that even a person not familiar with Excel can use them intuitively. Because of their inherent dynamic nature, they will enable you to change the perspective quickly of the report to show the required level of detail or to focus on the specific items in which the audience expresses interest.

此外，您可以根据具体情况将数据透视表报表构建为独立演示文稿或作为广泛报表的一个组成部分。您将在“数据透视表报表”一章中学习使用数据透视表进行报表的多种方法。

Further, you can structure a PivotTable report for standalone presentation or as an integral part of a broad report as the case may be. You will learn the several of reporting with PivotTables in the Chapter – PivotTable Reports.