數據透視表II―輕松創建綜合報告

數據透視表II―輕松創建綜合報告

譯者：rongjun? 來源：http://blogs.msdn.com/excel 發表于：2006年7月7日

PivotTables part 2: Creating summary reports made easy 數據透視表2：輕松創建綜合報告

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): 一旦用戶選擇這兩個命令的其中之一時，會出現一個創建數據透視表的新對話框。這個簡單的對話框取代了現有的多級向導，只顯示用戶必需的選項……我們的可用性調查報告表明：由于繁瑣的選擇條件，許多用戶從來不按照向導的步驟創建數據透視表。（注：可能會有人懷疑這樣的數據透視表是否仍然可以隨著多重合并區域的變動而變。回答是肯定的。現有的向導仍然適用于想利用這個功能的高級用戶，但它已經不處在主界面上了。）

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應用軟件共有的操作方式）。

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. 最后，我們的用戶添加銷售額字段就可以完成他們的綜合報告了。

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、 值。這個區域保留被匯總的字段（比如銷售額）。在這個區域的字段通常代表數值，但是也可以是非數值的（在這種情況下只能進行計數）。

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

非常感謝Kevin的幫助和指導!

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。