技巧1_色彩缤纷的Data Bars

Conditional Formatting Trick 1 – Multi-Coloured Data Bars条件格式技巧1——色彩缤纷的Data BarsA 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.