使用INDIRECT函数的条件格式在布尔AND或OR或包含公式的单元格中失败 [英] Conditional formatting using the INDIRECT function fails with boolean AND or OR or with cells containing formulas

问题描述

我具有以下功能来检查列L是否包含单词"completed"，并且我使用INDIRECT能够使用条件格式为整个行着色:

I have the following function for checking whether column L contains the word "completed" and I use INDIRECT to be able to color the whole row with Conditional Formatting:

 =INDIRECT("l"&ROW())="completed"

此功能有效.但是，我需要扩展它，我也想基于一个额外的单元格使用条件格式，所以我尝试了这个:

This function works. However, I need to extend this, I want to use Conditional Formatting based on an extra cell as well, so I tried this:

=AND(INDIRECT("l"&ROW())="completed";INDIRECT("m"&ROW())="duplicate")

当我在Excel工作表中使用第二个函数时，它们会给出正确的TRUE或FALSE.

When I use this second function inside the Excel worksheet they give the proper TRUE or FALSE.

此外，我需要对单元格中的公式结果进行自定义格式设置.我尝试了以下方法:

Furthermore, I needed a Custom Formatting on the result of a formula in a cell. I tried the following:

=INDIRECT("n"&ROW())=123456

仅当我删除单元格中的公式且结果本身为数字时，此方法才有效.同样，该功能在粘贴到工作表单元格中后仍然有效.

This only worked if I removed the formula in the cell with the result itself as a number. Again, the function worked when pasted in an worksheet cell.

是否有办法使它在Excel中起作用，或者对条件格式设置功能可以做的工作有限制吗?

Is there a way to make this work inside Excel or is there a limit to what Conditional Formatting functions can do?

如果您要问:AND(1;1)可以使所有内容变为黄色，则AND(INDIRECT("n"&ROW())=123456;1)不起作用，也不能将AND替换为OR.

In case you ask: AND(1;1) works and makes everything yellow, AND(INDIRECT("n"&ROW())=123456;1) does not work, nor does replacing AND with OR.

分号是因为我在荷兰语言环境中.如果您使用的是英语语言环境，请用逗号替换.

The semicolon is because I am in the Dutch locale. Replace it with a comma if you are in an English locale.

推荐答案

您必须考虑xlR1C1公式才能理解CFR.基于公式的CFR将其视为=RC12="completed"或更完整地称为=AND(RC12="completed", RC13="duplicate").

You have to think in terms of xlR1C1 formulas to understand CFRs. A CFR based on a formula thinks of it as =RC12="completed" or more completely =AND(RC12="completed", RC13="duplicate").

无论将xlR1C1公式粘贴到哪个单元格，它都不会更改.通过这种方式，CFR可以应用于广泛的单元格，而无需花费计算周期来更新每个单个单元格的公式. RC12表示"您所在行的L列中的单元格".如果填入，填入正确或复制到任何其他位置，它都不会更改.

The xlR1C1 formula does not change no matter what cell you paste it to; it is in this way that CFRs can be applied to a wide range of cells without expending calculation cycles to update the formula for each individual cell. RC12 means 'the cell in column L on the row you are on'. It does not change if filled down, filled right or copied to any other location.

现在，除非您实际使用的是xlR1C1(文件，选项，公式，使用公式，R1C1参考样式)，否则必须将xlR1C1转换为xlA1样式.如果将CFR应用于从第一行开始的许多行，则R变为1，C12变为$ L.

Now unless you are actually working in xlR1C1 (File, Options, Formulas, Working with Formulas, R1C1 reference style) you have to convert the xlR1C1 to xlA1 style. If you are applying the CFR to a number of rows starting with the first row then the R becomes 1 and the C12 becomes $L.

'xlR1C1
=AND(RC12="completed", RC13="duplicate")
'xlA1
=AND($L1="completed", $M1="duplicate")

如果将CFR应用于从第2行开始的范围，请将$ L1更改为$ L2，将$ M1更改为$ M2.

If you were applying the CFR to a range starting in row 2 change the $L1 to $L2 and the $M1 to $M2.

_{在以xlA1样式工作时未将xlR1C1样式公式直接放入CFR创建对话框的其他原因是，实际上在xlA1中存在一个RC12单元.}

这篇关于使用INDIRECT函数的条件格式在布尔AND或OR或包含公式的单元格中失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！