Excel Home


译者:rongjun  来源:http://blogs.msdn.com/excel

PivotTables part 2: Creating summary reports made easy

One of the goals we had for our PivotTable work in Excel 12 was to make creating PivotTables a much more approachable task. In this post, I would like to walk through an example of creating a PivotTable in order to highlight the changes we have made in Excel 12. In general, we tried to make the process simpler and more intuitive.
让用户很方便、很直观的创建数据透视表是我们的一个目标,我们已经在Excel 12 中实现了这个目标。在这篇文章,为了重点说明我们在Excel 12 中对数据透视表作出的改变,我创建一个数据透视表作为例子。通常,我们试图使得操作过程简单而且直观。

As I said in the previous post, PivotTables are great for summarizing large amounts of data. For example, a user might have a table full of sales data (contained in a query table, that they copied from elsewhere, that they have been typing into the grid over time, etc.) that contains products, sales figures, product categories, etc., and might want to see a summary of sales grouped by product and product category. This is exactly the sort of thing that is easy to create with a PivotTable. To start, the user needs to tell Excel they want to create a PivotTable. There are two places they can do this. First, on the Insert tab, where the first button in the ribbon is an inset PivotTable button.
正如我在以前的文章中所说,数据透视表主要用于处理大量的数据。例如,某个用户可能有一个填满销售数据的列表,包含产品、销售指数、产品种类等等,并且该用户想通过产品及产品种类看到各个销售组的销售概要。通过创建数据透视表这是件很容易实现的事情。回到出发点,用户需要告诉Excel,他们想要创建数据透视表。创建数据透视表有两种方式:第一、在“插入”标签上,在 ribbon 里的第一个按钮就是“插入数据透视表”按钮。

Second, on the Table tab (the tab that shows up when I am working with a table of data), we have added a command to “Summarize With Pivot”. This is essentially the same command, except when you use this command, we know that you want the table you are working with to be the data source for the PivotTable.
第二、在“列表”标签上(当我激活数据表时这个标签才会显示出来),我们已经附加一个命令到“Summarize With Pivot”。从本质上讲,这是相同的命令,除非当你使用这个命令时,我们知道你想把你正在操作的列表作为数据透视表的源数据。

Once the user selects one of these commands, they are presented with a new dialog for creating PivotTables. This dialog replaces the existing multistep wizard with a simple dialog that only presents the user with the most necessary choices … our usability research showed that a lot of users never made it past the wizard due to the complexity of choices required. (Note, there are probably some out there wondering about whether they can still pivot against multiple consolidation ranges, etc. … the answer is yes. The existing wizard is still available for advanced users that want to take advantage of its functionality; it just isn’t the mainline UI):

In our example, because we had a table selected when we created the PivotTable, all our user has to do is to click OK and the PivotTable is created – that’s a total of two clicks. If the user wanted to work with data external to Excel, they would simply select “Use an external data source” and select the external data connection they would like to use from the drop down. (Note that in the Table/Range refedit control, you can now type “structured references” to tables or parts of tables in addition to cell references. This is true for most of the refedit controls in Excel, and is another small but powerul addition to the product. See this post for more on structured referencing.)
在我们的例子里,因为当我们创建数据透视表时已经选择了一个列表,所以用户所要做的就是点击“OK”,那么数据透视表就创建完成了——这总共只需要点击两次鼠标。如果用户想要引用外部数据到Excel,只需要简单的选择“使用外部数据源”并从下拉列表中选择他们想要用的外部数据连接。(注:在列表/范围refedit controns,除了单元格引用之外,你还可以键入“structured references(成形的引用)”。在 Excel中,多数的refedit controns都是可以实现的,这也是另一个小而高效的附加功能。这篇文章( this post)可以看到更多的关于成形引用的介绍。)

After clicking OK in the dialog, the user sees a new PivotTable in the Excel grid, and a field list (significantly changed from previous versions) with which to populate the PivotTable:

Now that the PivotTable has been created, the next step is to add the data the user wants summarized. This is another area where our customer research and usability studies demonstrated that many users had trouble, specifically in three areas.

• Figuring out that they needed to get their “fields” onto the various areas of the PivotTable
• Deciding which area, or “drop zone” of the PivotTable they needed to add their data to build their report.
• Figuring out how to get the field to the drop zone (drag-drop not being an action that is all that common in Office applications)

• 确定他们需要的字段该放到数据透视表的哪些区域。
• 判断数据透视表的哪个区域或“拖放区域”需要增加他们的数据才能建立他们的报表。
• 学会将字段添加到拖放区域中(拖拽并不是所有的Office应用软件共有的操作方式)。

In order to address these problems, we made a couple of changes. First, we added checkboxes to the field list, which, though a simple change, clearly advertises to the user what actions they need to take. Second, we changed the rules, so that users don’t need to move fields to drop zones to get going – when they check a checkbox for a field, that field is automatically added to the PivotTable, using a simple but generally effective algorithm. If the data type of the field is numeric, the field is added to the Values area, with a default aggregation of sum. If the data type is non-numeric, the field is added to the Row Labels area. As you click more non-numeric fields, Excel places them on the inside of fields already on the PivotTable, building a hierarchy. Un-checking a checkbox will remove the field from the PivotTable.
为了解决这些难题,我们做了两个改动。第一,我们在字段列表框里增加了复选框,尽管这是一个很简单的改变,但是能清楚的告诉用户,他们需要如何来操作。第二,我们改变原有的惯例,所以用户不需要移动字段到拖放区域——当他们勾选某个字段的复选框时,这个字段就会自动增加到数据透视表中,利用了一个简单但通用有效的运算法则。如果这个字段的数据类型为数值,那么这个字段就会增加到值区域,形成一个求和的默认集合。如果该数据类型不是数值型,那么这个字段就会增加到行标签区域。当你点击多个非数值型字段时,Excel 会自动把这些字段安排在数据透视表的字段区域当中,并建立层次结构。不勾选复选框的话,这些字段就会从数据透视表中移除。

So with the new field list, a user can get a quick summary by simply checking a couple of checkboxes, and that’s it. Let’s walk through what this looks like. As I said, our user wants to build a sales summary for products by their product category. The first field for the user to add is Product Category, so they click in the checkbox for that field …

… and the items of the Product Category field are immediately added to the PivotTable.

Next, to show the individual products of each product category, the user adds the Product Name field:

Now we have the products nicely listed under their product category in the PivotTable, complete with some UI that hints that you can expand and collapse levels … more on that in a later post.

And finally, our user adds the Sales Amount field to finish their summary report.

They probably want to format it as a currency too, which is one click on the ribbon. And, with that, our user now has their summary report – Excel has grouped all the original data by Category and Product, calculated the Sales Amount, and slapped on a Grand Total … all in a total of 5 clicks.

I can hear some of your saying “but I liked dragging things around”. I can also hear some other folks asking “how do I get things to the Filter area or the Column area to build a crosstab?” (And probably a lot of other things, but I will stop guessing, address those two, and hear the rest in your comments.) Well, you probably noticed in the screenshots above that as I added fields to the PivotTable, they appeared in the lower section of the field list. This section holds the drop zones, which were designed around two key points – making it easy to determine the current placement of fields in the PivotTable, as well as making it easy to rearrange the fields in a PivotTable. There are four areas in a PivotTable, each of which is represented by a drop zone in the field list.

1. Report Filter. This area holds the fields that the whole PivotTable is filtered by.
2. Row Labels. This area holds fields that act as labels for the values, and the labels appear to the left of the values.
3. Column Labels. This area is just like row area, but the labels appear above the values, breaking them out by column instead of row.
4. Values. This area holds the fields that are summarized (for example sales amount). Fields in this area are typically numeric, but can also be non-numeric (in which case they are counted).

1、 报告筛选。这个区域保留整个数据透视表未被使用的字段。
2、 行标签。这个区域保留担当值标签的字段,并且这个标签出现在值的左侧。
3、 列标签。这个区域跟行区域相像,但是这个标签出现在值的上方,用“列”把它们隔开而不是“行”。
4、 值。这个区域保留被汇总的字段(比如销售额)。在这个区域的字段通常代表数值,但是也可以是非数值的(在这种情况下只能进行计数)。

If you prefer to drag fields from the field list to the drop zones, you can … this is also the answer to how you add fields to the Filter or Column drop zones. To change the report layout, you can drag and drop fields between the different drop zones or you can click a field in a drop zone and select which area to move it to in the menu that pops up.

And finally, for those of you that really want the drop zones in the grid, we have put in a toggle to bring them back, but there are some additional considerations to that one, so I will cover it further in a later post.

So, to sum up, we have worked to make PivotTables easier and faster to create, and our usability testing with both beginning users as well as PivotTable experts (see post on usability studies for more on that subject) show that both user groups benefit from the new design.

Next week, much more on PivotTables.

Published Friday, December 09, 2005 12:25 PM by David Gainer

注:本文翻译自http://blogs.msdn.com/excel,原文作者为David Gainer(a Microsoft employee),Excel home授权转载。严禁任何人以任何形式转载,违者必究。


未经允许不得转载:ExcelHome » 数据透视表II―轻松创建综合报告

本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号