使用通用公式Excel条件格式

I want to conditionally format cells in Excel. Is it possible to create a generic formula that does the following:

我想在Excel中有条件地格式化单元格。是否有可能创建一个通用公式来执行以下操作:

    IF the value in column A of the given row = "A" 
    AND the value in the given cell is greater than .1 
    AND the value in the given cell is less than 1
    THEN format the cell with a light grey background color

In the example below, I want the formula to apply to cells B1:K2, but I only want cells E1, I1 and K1 to be highlighted in grey.

在下面的例子中，我希望这个公式适用于B1:K2细胞，但我只希望细胞E1、I1和K1用灰色突出显示。

3 个解决方案

#1

• Select B1:K[n]. B1 is the active cell.
• 选择B1:K[n]。B1是活性细胞。
• Then, click Home > Conditional Formatting > New Rule.
• 然后，单击Home >条件格式>新规则。
• In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
• 在“新格式规则”对话框中，单击“使用公式”确定要格式化哪些单元格。

• Under Format values where this formula is true, type the formula:

  在该公式为真的格式值下，输入公式:

  =AND($A1="A",B1>0.1,B1<1)

  =和(A1美元=“A”,B1 > 0.1,B1 < 1)

• Click Format.

  单击格式。

• In the Fill box, select your light gray color.
• 在填充框中，选择浅灰色。
• Click OK until the dialog boxes are closed.
• 单击OK，直到关闭对话框。

Greetings

问候

Axel

阿克塞尔

#2

Tap F5 and when the GoTo dialog opens, type A:K (◄ just like that) into the Reference: box and click OK. You should have columns A through K selected with A1 as the Active Cell.

利用F5 GoTo对话框打开时,A型:K(这样◄)为参考:框并点击OK。应该有A到K列，A1作为活动单元格。

Create a new Conditional Formatting Rule using the Use a formula to determine which cells to format option and supply the following for Format values where this formula is true:

创建一个新的条件格式规则，使用一个公式来确定哪些单元格格式化选项，并提供以下格式值的格式值:

=AND($A1="A", A1>0.1, A1<1, ISODD(COLUMN(A:A)))

Click Format and select a light=grey Fill then click OK to accept the format and then OK again to create the new CF rule. Your results should resemble the following.

单击Format并选择light=grey Fill，然后单击OK接受该格式，然后再次单击OK创建新的CF规则。您的结果应该类似于以下内容。

    

    

Edit: Revised from literal interpretation to subjective interpretation

编辑:从字面解释修改为主观解释

#3

Select cells E1:F2,I1:I2,K1:K2 (hold down Ctrl while selecting discontinuous range) so the conditional formatting will apply to them.

选择单元格E1:F2,I1:I2,K1:K2(在选择不连续范围时按下Ctrl)，这样条件格式将适用于它们。

The cell K1 should have the focus, i.e. be highlighted with white background, while other selected cells are highlighted with dark background. Insert conditional formatting with following formula:

单元K1应该有焦点，即以白色背景突出显示，而其他选中的单元则以暗背景高亮显示。使用以下公式插入条件格式:

=($A1="A")*(K1>0.1)*(K1<1)