officeba > 单独文章

---

在Excel 2010中的条件格式化功能

更多的条件格式化功能在Excel 2010年
In this blog post I'll walk through some of the new Conditional Formatting features in Excel 2010 such as:在这个博客帖子我会走过一些新的条件格式功能在Excel 2010年，如：

Cross-Sheet References交叉参考表
Robust Error Handling强大的错误处理
Performance Enhancements性能增强
Cross-Sheet References交叉参考表
In Excel 2010, you can create conditional formatting with references to a different sheet on the workbook.在Excel 2010年，您可以创建条件格式提及到不同的资产负债表的工作簿。 This means that you no longer need to copy or link your data to the same sheet when using conditional formatting.这意味着你不再需要复制或链接您的数据，以同样的资产负债表时，使用条件格式。

For Example, let us look at the monthly sales data for a bookstore chain.例如，让我们来看看每月销售数据书店链。 The bookstore organizes their sales data as follows:书店举办的销售数据如下：

Each location gets its own worksheet每个地点得到自己的工作
The last worksheet contains the cumulative company wide sales figures with the averages for each department.最后一个工作表包含的累积公司广泛的销售数字与平均每个部门。
We will apply conditional formatting so that we can easily determine if the department at the local branch is doing better or worse as compared to the other branches in the company.我们将适用条件格式，以便使我们可以很容易地确定该部在地方分行正在做或好或坏相比其他部门在该公司。 Let us use the San Francisco branch as our example.让我们利用旧金山分行作为我们的榜样。 We will apply a green background fill for the departments if they are performing better than average.我们将采用绿色背景填补政府部门来说，如果他们表现好于平均水平。

1.  Start on the sheet for San Francisco location and select the cells in the sales column to which you want to apply conditional formatting. 1 。启动的表旧金山位置，并选择细胞的销售栏要应用条件格式。 Go to the Home Tab-> Conditional formatting -> Highlight Cell Rules -> Greater Than.返回首页制表符“条件格式- ”突出显示单元规则- “大于。



2.  Click on the 2 。点击  (RefEdit icon) and simply navigate to the sheet whose value you want to compare against. （ RefEdit图标） ，并只需转到资产负债表，其价值要比较。 In our case, we will navigate to the company wide worksheet and click on the average for the first row in the department column.就我们而言，我们将转到该公司广泛的工作，并点击的平均水平的第一行部栏。



3.  Now here is a neat little tip – references in Conditional Formatting follow all the rules of absolute and relative references. 3 。现在，这里是一个整洁小提示-中提到的所有条件格式遵循的规则绝对和相对引用。 The Cell References are relative to the active cell in the “Applies To” range (in this case the top-left cell in the range).该单元格引用是相对活跃细胞在“适用于”范围（在这种情况下，左上角单元格范围内） 。

Let us modify the reference to be relative to the rows by deleting “$” symbol before the row number.让我们修改的提法是相对于该行删去“ $ ”符号前的行号。



The result is that each department on the “San Francisco” sheet being compared to the same type of department on the “Company” sheet.其结果是，每个部门的“旧金山”相比，资产负债表是同一类型的新闻部的“本公司”资产负债表。

4.  Click OK and you have a Conditional Formatting Rule that is dependent on other sheets in the workbook. 4 。单击确定，你有一个条件格式规则，依赖于其他表中的工作簿。



Error Handling错误处理
Have you ever used ranged based conditional formatting (such as icon sets, color scales and data bars) only to find that one of your formulas have evaluated to an error?  In Excel 2007, errors in the range would lead to conditional formatting not being applied to all the cells in the range.  Users told us that this was frustrating.您是否曾经使用范围的条件格式（如图示套，颜色表和数据酒吧）才发现您的一个公式评价一个错误？在Excel 2007中，错误将导致一系列条件格式不适用所有细胞的范围内。用户告诉我们，这是令人沮丧。

In Excel 2010, conditional formatting silently ignores the errors and continues applying conditional formatting to the rest of the cells in the range.在Excel 2010年，条件格式悄悄地忽略了错误，并继续申请条件格式到其他细胞的范围内。



Performance Enhancements性能增强
For this release we've also improved the performance of formatting in Excel 2010.这一版，我们还提高了性能的格式在Excel 2010年。 For conditional formatting we store dependencies of the formulas used so we don't need to reevaluate the entire conditional format as often.我们有条件格式化存储相依使用的公式，以便我们并不需要重新评估整个有条件格式往往。 We've also made banding in the table and pivot table styles faster, so this will impact things like refreshing a pivot table or scrolling with a large table on your sheet.我们同时做了带的表和数据透视表样式更快，因此这会影响像刷新数据透视表或滚动大表上的资产负债表。 For all the above cases, we also cache the format of the cell so if you scroll off the screen and come back, or switch sheets and come back, we'll remember the cell formatting and can display faster.上述所有情况下，我们还缓存格式的单元格，这样如果你的屏幕上滚动了回来，或切换床单回来，我们会记得单元格格式，可以显示速度更快。

In our next Excel 2010 post, we'll look at a new object model feature for conditional formatting.在我们下次的Excel 2010年后，我们将看到一个新的对象模型功能的条件格式。

声明：欢迎各大网站转载本站文章，还请保留一条能直接指向本站的超级链接，谢谢！

时间:2009-08-14 10:10:10,点击:65824