技巧1_色彩繽紛的Data Bars

技巧1_色彩繽紛的Data Bars

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

Conditional Formatting Trick 1 – Multi-Coloured Data Bars 條件格式技巧1——色彩繽紛的Data Bars

A few months ago, I described the new features we have added to Excel 2007 in the area of conditional formatting. One of the new formats we added is called a “data bar” … check out this earlier post for a refresher, but the basic idea is that Excel draws a bar in each cell representing the value of that cell relative to the other cells in the selected range. Here is a shot from that post. 幾個月以前，我講述了在Excel 2007中新增加的有關條件格式的各種特性，其中之一就是我們稱之為“data bar”的……可參閱以前的文章。但是我們只講到最基礎的概念，即在一個選定的區域里面，由Excel根據不同單元格的數值對比情況，在每個單元格中繪制一個色帶。下面是以前文章中的截圖：

The Excel 2007 UI allows you to choose whatever colour you want for your data bars, but, by default, all the data bars you apply to a range have to be the same colour. Someone on our team recently showed me how to use a tiny bit of VBA to simulate having multiple colours of data bars on a range conditionally applied, so I thought I would pass along the trick. Excel 2007允許你為你的data bars選擇任何你想要的顏色，但是，默認情況下，同一區域中的data bars，只能有同一種顏色。最近，開發團隊中的某位成員向我展示了如何在同一區域中創建多種顏色的data bars，只需要利用非常簡單的VBA代碼即可。所以，我想我應該好好發揮這個技巧的威力。

Say, for example, you are looking at student grades, and you want all the data bars for students with a passing mark (60%+ and above) to be green, and those with a failing grade (59% or less) to be red. The first thing you would do is to add some red data bars to your data, and then some green data bars. By default, Excel shows you the last set applied, so the data bars would be green. If you then launch the VB Editor (Alt + F11) and in the immediate window (Ctrl+G), type: selection.FormatConditions(1).formula = “=if(c3>59, true, false)” You would see that your data now looks like this, which makes it easy to spot the failing grades. 舉個例子，你正在考慮為學生評分，你希望當學生成績及格（60%或更高）時，data bars是綠色的，而不合格的成績（59%或更低）對應的data bars是紅色的。第一步，你肯定會為你的數據加上一些紅色的data bars，然后是綠色的。在默認情況下，Excel只接受你最后的設置，所以所有的data bars都將是綠色的。如果你現在打開VB編輯器(Alt + F11)，在立即窗口(Ctrl+G)中輸入：selection.FormatConditions(1).formula = “=if(c3>59, true, false)” 你將會看到你的數據就會像下面這樣，非常容易的辨識出不合格的成績：

So how does this work? Every conditional format has a Formula property, which allows you to specify a formula which determines whether the conditional format is visible. In this case, we are simply saying that the green data bars (the most recent ones) should only be visible if a value is greater than 59. This property is available on all conditional formats, so I expect that users will find all sorts of creative uses beyond just this case. 那么這項工作是如何完成的？原來，每一個條件格式都有一個公式的屬性，此屬性允許你指定一個公式來判斷其本身是否可見。在這個例子中，我們簡單的指定為，只有單元格數值大于59時，綠色的data bars才會被顯示。 所有的條件格式都可以利用這個屬性，因此，我希望用戶能夠發揮創造力，舉一反三。

Published Friday, February 24, 2006 1:32 PM by David Gainer

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

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