嵌套If语句的条件格式[excel]

I have a PivotTable that, for this example's sake, has three columns:

我有一个数据透视表,为了这个例子,它有三列:

Column B contains non-negative number values.

B列包含非负数值。

Column D contains non-negative number values.

D列包含非负数值。

Column C shows the percent of change from Column D to Column B (ie, D1=1 and B1=2, C1=100%).

C列显示从D列到B列的变化百分比(即D1 = 1且B1 = 2,C1 = 100%)。

I have a Conditional Formatting rule set up so that if any value in Column C is greater than 10%, the cell is highlighted in red. However, this does not work when a cell in Column D has a value of zero, and the corresponding cell in Column B has a value higher than zero, because the result would be infinity.

我设置了条件格式规则,以便如果C列中的任何值大于10%,则单元格将以红色突出显示。但是,当列D中的单元格值为零时,这不起作用,而列B中的相应单元格的值大于零,因为结果将是无穷大。

My goal is to set up a rule so that if any cell in Column D has a value of zero, and any cell (on the same row) in Column B has a value greater than zero, the corresponding row in Column C will still be highlighted in red. Since I have non-negative numbers in my data, technically as long as Column D is zero and Column B is not zero, this would satisfy the formula.

我的目标是设置一个规则,以便如果列D中的任何单元格的值为零,并且列B中的任何单元格(在同一行上)的值大于零,则列C中的相应行仍将是以红色突出显示。由于我的数据中有非负数,从技术上讲,只要D列为零且B列不为零,这就满足了公式。

I've tried using the following formatting rule to apply to Column C but have not been successful. I'm sure this could be also be accomplished using nested IF statements:

我已尝试使用以下格式规则应用于C列但尚未成功。我确信这也可以使用嵌套的IF语句来完成:

IF(D$=0 AND(B$<>0), TRUE, FALSE)

2 个解决方案

#1

AND in Excel is used differently... it works like this: and(condition1,condition2). So your code should be formatted like this:

AND在Excel中的使用方式不同......它的工作原理如下:和(condition1,condition2)。所以你的代码应格式如下:

IF(AND(D$=0,(B$<>0)), TRUE, FALSE)

IF(AND(D $ = 0,(B $ <> 0)),TRUE,FALSE)

But it works differently in conditional formatting... so what you do is select the range, but make sure to note what cell is highlighted:

但它在条件格式中的工作方式不同......所以你要做的是选择范围,但要确保注意突出显示的是哪个单元格:

As you can see, you don't have to worry about the TRUE FALSE conditions... just the logical test is enough. So if you follow my screenshot, your formula should be:

如您所见,您不必担心TRUE FALSE条件......只需逻辑测试即可。因此,如果您按照我的屏幕截图,您的公式应为:

=AND(D4=0,B4<>0)

(I assume your data starts in row 4... this should be the row that is HIGHLIGHTED in your selected range. In my case it's row 4.

(我假设你的数据从第4行开始......这应该是你所选范围内突出显示的行。在我的例子中,它是第4行。

#2

Conditional formatting of column C, rows 1 to 50:

列C,第1行到第50行的条件格式:

Applies to: =$C$1:$C$50

适用于:= $ C $ 1:$ C $ 50

Format values when this formula is true: =AND($D1=0, $B1>0)

此公式为true时的格式值:= AND($ D1 = 0,$ B1> 0)

If your line separator is semicolon, not comma use this instead: =AND($D1=0; $B1>0)

如果你的行分隔符是分号,而不是逗号使用它:= AND($ D1 = 0; $ B1> 0)

#1