在Microsoft Excel中使用数据透视表

PivotTables are one of the most powerful features of Microsoft Excel.  They allow large amounts of data to be analyzed and summarized in just a few mouse clicks. In this article, we explore PivotTables, understand what they are, and learn how to create and customize them.

数据透视表是Microsoft Excel的最强大功能之一。 它们使您只需单击几下鼠标即可分析和汇总大量数据。 在本文中，我们将探讨数据透视表，了解它们是什么，并学习如何创建和自定义它们。

Note:  This article is written using Excel 2010 (Beta).  The concept of a PivotTable has changed little over the years, but the method of creating one has changed in nearly every iteration of Excel.  If you are using a version of Excel that is not 2010, expect different screens from the ones you see in this article.

注意：本文是使用Excel 2010(Beta)编写的。 数据透视表的概念多年来变化不大，但是几乎在每次Excel迭代中，创建数据透视表的方法都已发生变化。 如果您使用的Excel版本不是2010，请与本文中看到的屏幕不同。

A Little History

一点历史

In the early days of spreadsheet programs, Lotus 1-2-3 ruled the roost.  Its dominance was so complete that people thought it was a waste of time for Microsoft to bother developing their own spreadsheet software (Excel) to compete with Lotus.  Flash-forward to 2010, and Excel’s dominance of the spreadsheet market is greater than Lotus’s ever was, while the number of users still running Lotus 1-2-3 is approaching zero.  How did this happen?  What caused such a dramatic reversal of fortunes?

在电子表格程序的早期，Lotus 1-2-3占据了主导地位。 它的统治地位是如此完整，以至于人们认为微软花时间开发自己的电子表格软件(Excel)与Lotus竞争是浪费时间。 快进到2010年，Excel在电子表格市场上的统治地位已超过Lotus，而仍在运行Lotus 1-2-3的用户数量接近零。 这怎么发生的？ 是什么原因造成了如此戏剧性的命运逆转？

Industry analysts put it down to two factors:  Firstly, Lotus decided that this fancy new GUI platform called “Windows” was a passing fad that would never take off.  They declined to create a Windows version of Lotus 1-2-3 (for a few years, anyway), predicting that their DOS version of the software was all anyone would ever need.  Microsoft, naturally, developed Excel exclusively for Windows.  Secondly, Microsoft developed a feature for Excel that Lotus didn’t provide in 1-2-3, namely PivotTables.  The PivotTables feature, exclusive to Excel, was deemed so staggeringly useful that people were willing to learn an entire new software package (Excel) rather than stick with a program (1-2-3) that didn’t have it.  This one feature, along with the misjudgment of the success of Windows, was the death-knell for Lotus 1-2-3, and the beginning of the success of Microsoft Excel.

业界分析家将其归结为两个因素：首先，Lotus认为这个新颖的GUI平台称为“ Windows”，是一种永不过时的风尚，永远不会起飞。 他们拒绝创建Windows版本的Lotus 1-2-3(无论如何，几年了)，并预测他们软件的DOS版本是任何人都需要的。 微软自然会专门为Windows开发Excel。 其次，Microsoft开发了Lotus在1-2-3中未提供的Excel功能，即PivotTables 。 Excel独有的数据透视表功能被认为是如此有用，以至于人们愿意学习一个全新的软件包(Excel)，而不是坚持使用没有它的程序(1-2-3)。 这一功能，加上对Windows成功的错误判断，是Lotus 1-2-3的丧钟，也是Microsoft Excel成功的开端。

Understanding PivotTables

了解数据透视表

So what is a PivotTable, exactly?

那么什么是数据透视表呢？

Put simply, a PivotTable is a summary of some data, created to allow easy analysis of said data.  But unlike a manually created summary, Excel PivotTables are interactive.  Once you have created one, you can easily change it if it doesn’t offer the exact insights into your data that you were hoping for.  In a couple of clicks the summary can be “pivoted” – rotated in such a way that the column headings become row headings, and vice versa.  There’s a lot more that can be done, too.  Rather than try to describe all the features of PivotTables, we’ll simply demonstrate them…

简而言之，数据透视表是一些数据的摘要，创建后可以轻松分析这些数据。 但是与手动创建的摘要不同，Excel PivotTables是交互式的。 创建数据后，如果它不能提供所需数据的准确见解，则可以轻松对其进行更改。 只需单击几下，即可“透视”摘要-以使列标题变为行标题的方式旋转，反之亦然。 还有很多事情可以做。 与其尝试描述数据透视表的所有功能，不如直接演示它们……

The data that you analyze using a PivotTable can’t be just any data – it has to be raw data, previously unprocessed (unsummarized) – typically a list of some sort.  An example of this might be the list of sales transactions in a company for the past six months.

使用数据透视表分析的数据不能只是任何数据，而必须是原始数据，以前未经处理(未汇总)，通常是某种列表。 例如，过去六个月中公司的销售交易清单。

Examine the data shown below:

检查以下数据：

Notice that this is not raw data.  In fact, it is already a summary of some sort.  In cell B3 we can see $30,000, which apparently is the total of James Cook’s sales for the month of January.  So where is the raw data?  How did we arrive at the figure of $30,000?  Where is the original list of sales transactions that this figure was generated from?  It’s clear that somewhere, someone must have gone to the trouble of collating all of the sales transactions for the past six months into the summary we see above.  How long do you suppose this took?  An hour?  Ten?

请注意，这不是原始数据。 实际上，它已经是某种形式的摘要。 在B3单元格中，我们可以看到$ 30,000，这显然是James Cook在1月份的总销售额。 那么原始数据在哪里呢？ 我们如何得出30,000美元的数字？ 此图是从哪个原始的销售交易清单生成的？ 显然，在某个地方，一定有人麻烦将过去六个月中的所有销售交易整理成我们上面所看到的摘要。 您认为这花了多长时间？ 一小时？ 十？

Most probably, yes.  You see, the spreadsheet above is actually not a PivotTable.  It was created manually from raw data stored elsewhere, and it did indeed take a couple of hours to compile.  However, it’s exactly the sort of summary that could be created using PivotTables, in which case it would have taken just a few seconds.  Let’s find out how…

很有可能，是的。 您会看到，上面的电子表格实际上不是数据透视表。 它是从存储在其他位置的原始数据手动创建的，确实需要花费几个小时才能进行编译。 但是，这正是可以使用数据透视表创建的摘要，在这种情况下，只需花费几秒钟。 让我们找出如何...

If we were to track down the original list of sales transactions, it might look something like this:

如果我们要追踪原始的销售交易清单，它可能看起来像这样：

You may be surprised to learn that, using the PivotTable feature of Excel, we can create a monthly sales summary similar to the one above in a few seconds, with only a few mouse clicks.  We can do this – and a lot more too!

您可能会惊讶地发现，使用Excel的数据透视表功能，我们只需单击几下鼠标，便可以在几秒钟内创建类似于上述内容的月度销售摘要。 我们可以做到–还有更多！

How to Create a PivotTable

如何创建数据透视表

First, ensure that you have some raw data in a worksheet in Excel.  A list of financial transactions is typical, but it can be a list of just about anything:  Employee contact details, your CD collection, or fuel consumption figures for your company’s fleet of cars.

首先，请确保您在Excel的工作表中有一些原始数据。 典型的是财务交易清单，但几乎可以是任何清单：员工联系方式，您的CD收款或公司车队的油耗数据。

So we start Excel… and we load such a list…

因此，我们启动Excel ...并加载这样的列表...

Once we have the list open in Excel, we’re ready to start creating the PivotTable.

在Excel中打开列表后，就可以开始创建数据透视表了。

Click on any one single cell within the list:

单击列表中的任何一个单元格：

Then, from the Insert tab, click the PivotTable icon:

然后，从“ 插入”选项卡中，单击“ 数据透视表”图标：

The Create PivotTable box appears, asking you two questions:  What data should your new PivotTable be based on, and where should it be created?  Because we already clicked on a cell within the list (in the step above), the entire list surrounding that cell is already selected for us ($A$1:$G$88 on the Payments sheet, in this example).  Note that we could select a list in any other region of any other worksheet, or even some external data source, such as an Access database table, or even a MS-SQL Server database table.  We also need to select whether we want our new PivotTable to be created on a new worksheet, or on an existing one.  In this example we will select a new one:

出现“ 创建数据透视表”框，向您询问两个问题：新的数据透视表应基于哪些数据，以及应在何处创建？ 因为我们已经单击了列表中的一个单元格(在上面的步骤中)，所以已经为我们选择了该单元格周围的整个列表(在此示例中，“ 付款”页面上的$ A $ 1：$ G $ 88 )。 请注意，我们可以选择任何其他工作表的任何其他区域中的列表，甚至可以选择某些外部数据源，例如Access数据库表，甚至MS-SQL Server数据库表。 我们还需要选择是要在新工作表上还是在现有工作表上创建新的数据透视表。 在此示例中，我们将选择一个新的 ：

The new worksheet is created for us, and a blank PivotTable is created on that worksheet:

为我们创建了新的工作表，并在该工作表上创建了一个空白的数据透视表：

Another box also appears:  The PivotTable Field List.  This field list will be shown whenever we click on any cell within the PivotTable (above):

还将出现另一个框： 数据透视表字段列表 。 每当我们单击数据透视表中的任何单元格时，都会显示此字段列表：

The list of fields in the top part of the box is actually the collection of column headings from the original raw data worksheet.  The four blank boxes in the lower part of the screen allow us to choose the way we would like our PivotTable to summarize the raw data.  So far, there is nothing in those boxes, so the PivotTable is blank.  All we need to do is drag fields down from the list above and drop them in the lower boxes.  A PivotTable is then automatically created to match our instructions.  If we get it wrong, we only need to drag the fields back to where they came from and/or drag new fields down to replace them.

框顶部的字段列表实际上是原始原始数据工作表中列标题的集合。 屏幕下部的四个空白框允许我们选择希望数据透视表汇总原始数据的方式。 到目前为止，这些框中没有任何内容，因此数据透视表为空白。 我们需要做的就是将字段从上面的列表中拖放到下面的框中。 然后将自动创建数据透视表以匹配我们的说明。 如果我们弄错了，我们只需要将字段拖回到它们来自的位置和/或向下拖动新字段来替换它们。

The Values box is arguably the most important of the four.  The field that is dragged into this box represents the data that needs to be summarized in some way (by summing, averaging, finding the maximum, minimum, etc).  It is almost always numerical data.  A perfect candidate for this box in our sample data is the “Amount” field/column.  Let’s drag that field into the Values box:

值框可以说是四个中最重要的。 拖到此框中的字段表示需要以某种方式汇总的数据(通过求和，求平均值，找到最大值，最小值等)。 它几乎总是数字数据。 在我们的样本数据中，此框的理想选择是“金额”字段/列。 让我们将该字段拖动到“ 值”框中：

Notice that (a) the “Amount” field in the list of fields is now ticked, and “Sum of Amount” has been added to the Values box, indicating that the amount column has been summed.

请注意，(a)现在在字段列表中的“金额”字段被打勾，并且“金额总计”已添加到“ 值”框中，指示已对金额列进行求和。

If we examine the PivotTable itself, we indeed find the sum of all the “Amount” values from the raw data worksheet:

如果检查数据透视表本身，则实际上可以从原始数据工作表中找到所有“金额”值的总和：

We’ve created our first PivotTable!  Handy, but not particularly impressive.  It’s likely that we need a little more insight into our data than that.

我们已经创建了第一个数据透视表！ 方便，但不是特别令人印象深刻。 可能我们需要的是对数据的更多了解。

Referring to our sample data, we need to identify one or more column headings that we could conceivably use to split this total.  For example, we may decide that we would like to see a summary of our data where we have a row heading for each of the different salespersons in our company, and a total for each.  To achieve this, all we need to do is to drag the “Salesperson” field into the Row Labels box:

参考我们的样本数据，我们需要确定一个或多个可以用来分割总数的列标题。 例如，我们可能决定，我们希望查看数据摘要，其中我们公司的每个不同销售人员都有一个行标题 ，而每个行都有一个标题 。 为此，我们需要做的就是将“销售人员”字段拖到“ 行标签”框中：

Now, finally, things start to get interesting!  Our PivotTable starts to take shape….

现在 ，终于，事情开始变得有趣起来了！ 我们的数据透视表开始成型…。

With a couple of clicks we have created a table that would have taken a long time to do manually.

通过几次单击，我们创建了一个表，该表需要很长时间才能手动完成。

So what else can we do?  Well, in one sense our PivotTable is complete.  We’ve created a useful summary of our source data.  The important stuff is already learned!  For the rest of the article, we will examine some ways that more complex PivotTables can be created, and ways that those PivotTables can be customized.

那我们还能做什么？ 好吧，从某种意义上说，我们的数据透视表是完整的。 我们为源数据创建了有用的摘要。 重要的东西已经学会了！ 在本文的其余部分，我们将研究可以创建更复杂的数据透视表的一些方法以及可以自定义这些数据透视表的方法。

First, we can create a two-dimensional table.  Let’s do that by using “Payment Method” as a column heading.  Simply drag the “Payment Method” heading to the Column Labels box:

首先，我们可以创建一个二维表。 让我们通过使用“付款方式”作为列标题来做到这一点。 只需将“付款方式”标题拖到“ 列标签”框中：

Which looks like this:

看起来像这样：

Starting to get very cool!

开始变得非常酷！

Let’s make it a three-dimensional table.  What could such a table possibly look like?  Well, let’s see…

让我们把它三个维表。 这样的桌子可能看起来像什么？ 走着瞧…

Drag the “Package” column/heading to the Report Filter box:

将“包”列/标题拖到“ 报表过滤器”框中：

Notice where it ends up….

请注意它的结尾位置…。

This allows us to filter our report based on which “holiday package” was being purchased.  For example, we can see the breakdown of salesperson vs payment method for all packages, or, with a couple of clicks, change it to show the same breakdown for the “Sunseekers” package:

这使我们可以根据购买的“假日套餐”来过滤报告。 例如，我们可以看到所有软件包的销售人员与付款方式明细，或者单击几下即可对其进行更改，以显示与“ Sunseekers”软件包相同的明细：

And so, if you think about it the right way, our PivotTable is now three-dimensional.  Let’s keep customizing…

因此，如果您以正确的方式考虑问题，则我们的数据透视表现在是三维的。 让我们继续自定义...

If it turns out, say, that we only want to see cheque and credit card transactions (i.e. no cash transactions), then we can deselect the “Cash” item from the column headings.  Click the drop-down arrow next to Column Labels, and untick “Cash”:

如果事实证明，例如，我们只想查看支票和信用卡交易(即没有现金交易)，那么我们可以从列标题中取消选择“现金”项。 单击“ 列标签 ”旁边的下拉箭头，然后取消选中“现金”：

Let’s see what that looks like…As you can see, “Cash” is gone.

让我们看看它是什么样子……正如您所看到的，“现金”已经消失了。

Formatting

格式化

This is obviously a very powerful system, but so far the results look very plain and boring.  For a start, the numbers that we’re summing do not look like dollar amounts – just plain old numbers.  Let’s rectify that.

这显然是一个非常强大的系统，但是到目前为止，结果看起来非常简单而乏味。 首先，我们求和的数字看起来不像美元，而是简单的旧数字。 让我们纠正一下。

A temptation might be to do what we’re used to doing in such circumstances and simply select the whole table (or the whole worksheet) and use the standard number formatting buttons on the toolbar to complete the formatting.  The problem with that approach is that if you ever change the structure of the PivotTable in the future (which is 99% likely), then those number formats will be lost.  We need a way that will make them (semi-)permanent.

一种诱惑可能是在这种情况下做我们惯用的事情，然后简单地选择整个表(或整个工作表)，然后使用工具栏上的标准数字格式按钮来完成格式化。 这种方法的问题在于，如果将来更改数据透视表的结构(可能性为99％)，则这些数字格式将丢失。 我们需要一种使它们(永久)永久化的方法。

First, we locate the “Sum of Amount” entry in the Values box, and click on it.  A menu appears.  We select Value Field Settings… from the menu:

首先，我们在“ 值”框中找到“金额总计”条目，然后单击它。 出现菜单。 我们从菜单中选择“ 值字段设置” ：

The Value Field Settings box appears.

出现“ 值字段设置”框。

Click the Number Format button, and the standard Format Cells box appears:

单击数字格式按钮，并出现标准格式单元格框 ：

From the Category list, select (say) Accounting, and drop the number of decimal places to 0.  Click OK a few times to get back to the PivotTable…

从“ 类别”列表中，选择(例如) Accounting ，然后将小数位数减少到0。单击OK几次，返回到数据透视表...

As you can see, the numbers have been correctly formatted as dollar amounts.

如您所见，数字已正确格式化为美元金额。

While we’re on the subject of formatting, let’s format the entire PivotTable.  There are a few ways to do this.  Let’s use a simple one…

当我们讨论格式化问题时，让我们格式化整个数据透视表。 有几种方法可以做到这一点。 让我们用一个简单的...

Click the PivotTable Tools/Design tab:

单击数据透视表工具/设计选项卡：

Then drop down the arrow in the bottom-right of the PivotTable Styles list to see a vast collection of built-in styles:

然后，在“ 数据透视表样式”列表的右下角下拉箭头，以查看大量内置样式：

Choose any one that appeals, and look at the result in your PivotTable:

选择任何具有吸引力的，然后在数据透视表中查看结果：

Other Options

其他选择

We can work with dates as well.  Now usually, there are many, many dates in a transaction list such as the one we started with.  But Excel provides the option to group data items together by day, week, month, year, etc.  Let’s see how this is done.

我们也可以处理日期。 现在通常情况下，交易清单中有很多日期，例如我们开始的日期。 但是Excel提供了按天，周，月，年等对数据项进行分组的选项。让我们看看如何完成此操作。

First, let’s remove the “Payment Method” column from the Column Labels box (simply drag it back up to the field list), and replace it with the “Date Booked” column:

首先，让我们从“ 列标签”框中删除“付款方式”列(只需将其拖回到字段列表中)，然后将其替换为“预定日期”列：

As you can see, this makes our PivotTable instantly useless, giving us one column for each date that a transaction occurred on – a very wide table!

如您所见，这使我们的数据透视表立即失效，在交易发生的每个日期为我们提供了一栏-一张非常宽的表！

To fix this, right-click on any date and select Group… from the context-menu:

要解决此问题，请在任意日期上单击鼠标右键，然后从上下文菜单中选择“ 组… ”：

The grouping box appears.  We select Months and click OK:

出现分组框。 我们选择月份 ，然后单击确定：

Voila!  A much more useful table:

瞧！ 更有用表：

(Incidentally, this table is virtually identical to the one shown at the beginning of this article – the original sales summary that was created manually.)

(顺便说一句，此表实际上与本文开头显示的表相同-原始销售摘要是手动创建的。)

Another cool thing to be aware of is that you can have more than one set of row headings (or column headings):

要注意的另一件很酷的事情是，您可以拥有多组行标题(或列标题)：

…which looks like this….

…看起来像这样…。

You can do a similar thing with column headings (or even report filters).

您可以对列标题(甚至报告过滤器)执行类似的操作。

Keeping things simple again, let’s see how to plot averaged values, rather than summed values.

让事情再简单一点，让我们看看如何绘制平均值而不是求和值。

First, click on “Sum of Amount”, and select Value Field Settings… from the context-menu that appears:

首先，单击“金额总和”，然后从出现的上下文菜单中选择“ 值字段设置 ”：

In the Summarize value field by list in the Value Field Settings box, select Average:

在“ 值字段设置”框中的“按值 汇总 字段”中，选择“ 平均值” ：

While we’re here, let’s change the Custom Name, from “Average of Amount” to something a little more concise.  Type in something like “Avg”:

在这里，让我们将“ 自定义名称 ”从“平均金额”更改为更简洁的名称。 输入类似“ Avg”的内容：

Click OK, and see what it looks like.  Notice that all the values change from summed totals to averages, and the table title (top-left cell) has changed to “Avg”:

单击确定 ，然后查看其外观。 请注意，所有值都从总和变为平均值，并且表标题(左上角的单元格)已更改为“ Avg”：

If we like, we can even have sums, averages and counts (counts = how many sales there were) all on the same PivotTable!

如果愿意，我们甚至可以在同一数据透视表上获得总和，平均值和计数(计数=有多少笔销售)！

Here are the steps to get something like that in place (starting from a blank PivotTable):

以下是完成类似操作的步骤(从空白的数据透视表开始)：

1. Drag “Salesperson” into the Column Labels

   将“推销员”拖到列标签中

2. Drag “Amount” field down into the Values box three times

   将“金额”字段向下拖动到“ 值”框中三次

3. For the first “Amount” field, change its custom name to “Total” and it’s number format to Accounting (0 decimal places)

   对于第一个“金额”字段，将其自定义名称更改为“总计”，并将其数字格式更改为“ 会计” (0个小数位)

4. For the second “Amount” field, change its custom name to “Average”, its function to Average and it’s number format to Accounting (0 decimal places)

   对于第二个“数量”字段中，其自定义名称更改为“平均”，其功能以平均 ，它的数字格式会计 (0位小数)

5. For the third “Amount” field, change its name to “Count” and its function to Count

   对于第三个“金额”字段，将其名称更改为“ Count”，并将其功能更改为Count

6. 

   Drag the automatically created field from Column Labels to Row Labels

   将自动创建的字段从“ 列标签”拖到“ 行标签”

Here’s what we end up with:

这就是我们的最终结果：

Total, average and count on the same PivotTable!

总数，平均值和计数都在同一个数据透视表上！

Conclusion

结论

There are many, many more features and options for PivotTables created by Microsoft Excel – far too many to list in an article like this.  To fully cover the potential of PivotTables, a small book (or a large website) would be required.  Brave and/or geeky readers can explore PivotTables further quite easily:  Simply right-click on just about everything, and see what options become available to you.  There are also the two ribbon-tabs: PivotTable Tools/Options and Design.  It doesn’t matter if you make a mistake – it’s easy to delete the PivotTable and start again – a possibility old DOS users of Lotus 1-2-3 never had.

由Microsoft Excel创建的数据透视表有很多很多功能和选项-太多了，无法在此类文章中列出。 为了充分利用数据透视表的潜力，将需要一本小书(或一个大型网站)。 勇敢和/或讨厌的读者可以更轻松地进一步探索数据透视表：只需右键单击几乎所有内容，然后查看可用的选项。 还有两个功能区选项卡： 数据透视表工具/选项和设计 。 没关系，没关系–删除数据透视表然后重新启动很容易– Lotus 1-2-3的旧DOS用户从未有过这种可能性。

If you’re working in Office 2007, you might want to check out our article on how to create a PivotTable in Excel 2007.

如果您使用的是Office 2007，则可能需要查看有关如何在Excel 2007中创建数据透视表的文章 。

We’ve included an Excel workbook that you can download to practice your PivotTable skills on.  It should work with all versions of Excel from 97 onwards.

我们提供了一个Excel工作簿，您可以下载该工作簿来练习PivotTable技能。 从97开始，它应可与所有版本的Excel一起使用。

Download Our Practice Excel Workbook

下载我们的实践Excel工作簿

翻译自: https://www.howtogeek.com/howto/13336/working-with-pivottables-in-excel/