Читать книгу Excel Data Analysis For Dummies - Paul McFedries - Страница 25

You can apply conditional formatting based on the results of a formula. That is, you set up a logical formula as the conditional-formatting criteria. For each cell in which that formula returns TRUE, Excel applies the formatting you specify; for all the other cells, Excel doesn't apply the formatting.

In most cases, you use a comparison formula, or you use an IF function, often combined with another logical function such as AND or OR. In each case, your formula's comparison value must reference only the first value in the range. For example, if the range you are working with is a set of dates in A2:A100, the comparison formula =WEEKDAY(A2)=6 would apply conditional formatting to every cell in the range that occurs on a Friday.

The following steps show you how to apply conditional formatting based on the results of a formula:

1 Select the range you want to work with.

2 Choose Home ⇒ Conditional Formatting ⇒ New Rule.The New Formatting Rule dialog box appears.

3 Select Use a Formula to Determine Which Cells to Format.

4 In the Format Values Where this Formula Is True text box, type the logical formula.The figure shows an example of using a formula to apply conditional formatting.

5 Choose Format, use the Format Cells dialog box to define the rule’s style and formatting, and then click OK.

6 Click OK.Excel applies the conditional formatting to each cell in the range in which the logical formula returns TRUE.

When you're messing around with formula-based rules, one useful technique is to apply a conditional format based on a formula that compares all the cells in a range to one value in that range. The simplest case is a formula that applies conditional formatting to those range cells that are equal to a cell value in the range. Here’s the logical formula to use for such a comparison:

=range=cell

Here, range is an absolute reference to the range of cells you want to work with, and cell is a relative reference to the comparison cell. For example, to apply a conditional format to those cells in the range A1:A50 that are equal to the value in cell A1, you would use the following logical formula:

=$A$1:$A$50=A1