条件格式：在and（）和indirect（）内部使用row（）无效。我究竟做错了什么？ [英] Conditional formatting: using row() inside indirect() inside and() doesn't work. What am I doing wrong?

问题描述

说我已经在单元格B2上将值设置为55。

Say I have set a value of 55 on cell B2.

如果该值为55，则该单元格的背景色应为绿色。因此，我通过选择用户公式来确定要格式化的单元格来使用公式创建新规则，并将公式设置为以下内容：

If it's value is 55, the cell's background color should be green. So I created a new rule using a formula by selecting "User a formula to determine which cells to format" and set the formula to the following:

=B2 = 55

很明显它起作用了。但是，由于我们不希望对行进行硬编码，因为我们在将应用程序简单地复制到另一行即可生成更多行的应用程序中将此电子表格用作模板，因此我将公式设置为此：

It worked, obviously. But because we don't want to hard-code the row because we are using this spreadsheet as a template in an application that generates more rows by simply copying that cell to another row, I set the formula to this one:

=INDIRECT( "B" & row() ) = 55

由于我们实际上想要两个条件，而我们的要求并不那么简单，因此我尝试了以下操作：

And because we actually want two conditions and our requirement is not as simple as that, I tried the following:

=AND( INDIRECT( "B" & row() ) >= 50, INDIRECT( "B" & row() ) <= 60 )

如果值介于50和60（含）之间，则表示将背景设置为绿色。那没有用。但是可以使用以下方法：

Which means set the background to green if the value is between 50 and 60 (inclusive). And that did not work. But the following works:

=AND( INDIRECT( "B" & "2" ) >= 50, INDIRECT( "B" & "2" ) <= 60 )

有人可以告诉我为什么后者可以工作，但是前者调用row（）不能？

Could anyone tell me why does the latter work but the former that calls row() doesn't?

似乎row（）仅在与AND（）和INDIRECT结合使用时才起作用（），如下所示：

It seems row() only doesn't work if it's used with combination of AND() and INDIRECT() as demonstrated below:

=AND( ROW() = 2 ) -> this one works
=INDIRECT( "B" & row() ) = 55 -> this one works too
=AND( INDIRECT( "B" & row() ) ) = 55 -> doesn't work :((

有人可以解释为什么吗？这是Excel 2010中的错误吗？以及我该如何解决这个问题？

Could someone explain this why? Is this a bug in Excel 2010? And how do I solve this problem?

推荐答案

您面对的行为似乎是一个错误，这里有一些论坛链接，描述完全相同的问题： link1 ， link2 。

The behavior you faced looks like a bug, here are some forum links, where people describe exactly the same problem: link1,link2.

在这些主题之一中，您可以阅读到，如果将CF公式括以 ISERROR ，它将返回（原因不明） True 。在单元格中输入相同的公式非常好。

In one of those topics you can read that if you enclose your CF formula with ISERROR, it returns (for unknown reason) True. Same formula entered in a cell works perfectly fine.

在这种情况下，最简单的解决方案是替换：

The easiest solution in your case is to replace:

=AND(condition1,condition2)

等价于：

=(condition1)*(condition2)

其中一个链接线程中显示的其他解决方法是替换：

The other workaround shown in one of the linked threads is to replace:

INDIRECT("B" & row())

其等效项：

INDEX($B:$B,ROW())

两个解决方案都可以在条件格式规则中正常工作。

Both solutions work fine in conditional formatting rule.

这篇关于条件格式：在and（）和indirect（）内部使用row（）无效。我究竟做错了什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！