VII －條件格式與數據透視表（一）

VII －條件格式與數據透視表（一）

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

PivotTable VII: – Conditional formatting gets even better, or visualizing your data in PivotTables 數據透視表VII －條件格式使數據透視表變得更好，數據更形象化

In a series of previous articles I introduced the new conditional formatting capabilities in Excel 12 (see here for the whole series of posts). Today I want to add one more article to the series, which is the work we have done to make conditional formatting work really well in Excel PivotTables. This is an area that I love to show people, because the work we have done reduces a formerly-tricky task down to a few clicks. 在我之前一系列的文章中，我介紹了Excel 12里面的條件格式。今天，我想在此系列里再增加一篇文章，那就是我們在Excel數據透視表里使用條件格式所作的努力。這是一個我樂意給人們展示的領域，因為我們所作的工作將以前難以完成的任務簡化為非常簡單的操作。

The first point I want to make is that all the features we added around new types of conditional formatting (data bars, colour scales, icon sets, etc.), new rules (top 10, below average, etc.), and new UI are available for use on PivotTables. However, we did not stop there. To provide a great experience with conditional formatting inside PivotTables, we now associate the conditional formatting rules to the structure of the PivotTable instead of to the cells. What this means is that as users work with PivotTables (adding and removing fields, refreshing the data, expanding and collapsing levels, Pivoting fields between rows and columns, grouping fields, etc.), the formatting tracks the cells in the PivotTable appropriately, so the users don’t need to worry about what is happening to the formatting – things just work, and they can focus on analyzing their data instead. Let’s walk through an example. 首先一點我需要說明的是，關于條件格式新格式（數據條，顏色比例，圖標，等等），新規則（前10，均值之下，等等），以及新用戶界面等方面增加的所有特點，在數據透視表里面都是可用的。然而，我們并沒有就此滿足。為了在數據透視表里面提供一個強大的條件格式體驗，我們現在將條件格式的規則與數據透視表聯系起來，而不是與單元格了。這意味著當用戶操作數據透視表時（添加和刪除字段，刷新數據，展開和折疊層次，在行與列之間透視字段，組合字段，等等），格式將恰當地追蹤數據透視表里的單元格，因此用戶不必擔心格式會發生什么變化——照常運轉，他們可以側重于分析他們的數據。我們來看一個例子吧。

In the PivotTable below I’m looking at sales for different bike models, and I have arranged my report so that I have a column with sales data for each year. To make it easier to visually compare the sales of various bike models, I want to apply conditional formatting to the sales values. In the same way as I would when conditionally formatting “ordinary” (i.e. non-PivotTable cells), I select some cells containing sales values … 在下面的數據透視表里，我在看不同型號自行車的銷售情況，我已經做好了我的報告，其中一列是每年的銷售數據。我想要將條件格式應用到這些銷售數據上，將不同型號自行車銷售情況的比較變得形象化。就象設置“普通”（例如，非數據透視表單元格）的條件格式一樣，我選擇一些包含銷售數據的單元格……

… and then I use the ribbon to add a Data Bar format which helps users quickly scan their data to compare values and find outliers. ……然后，我使用Ribbon來添加Data Bar格式，使用戶快速瀏覽他們的數據時就可以比較數據并且找到突出者。

This is helpful, since I can now easily get a much better sense as to the relative sizes of the numbers I have selected, but oftentimes what I actually want is to apply the conditional formatting to *all* cells displaying bike sales. In this example, I might want to compare mountain bikes with road bikes using the same conditional formatting rule, but I want to avoid formatting subtotals (such as the total for the entire Mountain Bike category), since they would skew the results. You might be thinking “that’s going to take a lot of multiple-selection, especially on big PivotTables with a lot of levels.” To make it very easy to choose the “scope” of a conditional formatting rule in a PivotTable, Excel 12 provides some “on-object-UI” (OOUI) which gives you the choice of which scope you meant for the rule to be applied to (by scope I mean which set of cells get the conditional formatting). 這一招非常實用，因為我現在可以輕易地獲得關于我所選擇數字的相對大小概念。但是，我時常需要將該條件格式應用到顯示自行車銷售的所有單元格上。在本例中，我可能想用相同的條件格式規則來比較山地車和道路車，但是我想避開小計（例如整個山地車品類的總數），因為它們可能會弄亂結果。你可能會想“需要做很多個多區域選擇，特別是有很多層次的大數據表”。為了使在數據透視表里選擇條件格式“范圍”變得非常簡單，Excel 12提供一些“on-object-UI”（OOUI，譯者，對象上的用戶界面），讓你選擇你想要應用的范圍（我想設置條件格式的單元格區域）。

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

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