使用 AND() 函数的条件格式 [英] Conditional formatting using AND() function

问题描述

我正在尝试在工作表上进行条件格式设置.我需要根据以下条件语句用某种颜色填充单元格:

I'm trying conditional formatting on a sheet. I need to fill the cells with a certain color according to the following conditional statement:

=AND((INDIRECT(ADDRESS(4;COLUMN()))>=INDIRECT(ADDRESS(ROW();4)));(INDIRECT(ADDRESS(4;COLUMN()))<=INDIRECT(ADDRESS(ROW();5))))

当我分别尝试 AND() 函数中的语句时，它们似乎有效，但是当我将它们放在函数中时，我没有看到任何格式发生.

When I try the statements in the AND() function separately, they seem to work, but when I put them together in the function I don't see any formatting happening.

这里是一些背景:当前列"的第 4 行中有一个日期 (DATE1).当前行"(DATE2 和 DATE3)的 D 和 E 列上也有日期.因此，如果 DATE1 在 DATE2 和 DATE3 之间，我想用颜色填充单元格.

Here is some background: Row 4 of the "current column" has a date (DATE1) in it. There are also dates on the D and E columns of the "current row" (DATE2 and DATE3). So, I would like to fill the cell with a color if DATE1 is between DATE2 and DATE3.

我不明白为什么公式不起作用.非常感谢任何帮助.

I cannot see why the formula is not working. Any help is much appreciated.

更新(2011 年 12 月 13 日):

我实现了一个从需要此功能的单元格调用的函数.该函数返回整数值.然后条件格式只使用单元格中的整数.这样，条件格式就不那么复杂了.我将 INDIRECT(ADDRESS(ROW();COLUMN())) 传递到我实现的函数中.因此，在处理相对和/或绝对单元格时，我拥有所需的所有信息.知道一种更简单的方法将当前单元格作为范围传递到函数中会很棒.

I implemented a function that I call from the cells I need this functionality. The function returns integer values. Then conditional formatting only uses the integers in the cells. This way, the conditional formatting is less complicated. I'm passing INDIRECT(ADDRESS(ROW();COLUMN())) into the function I implement. So, I have all the information I need when working on relative and/ or absolute cells. Would be great to know a simpler way to pass the current cell as range into the function.

注意:ActiveCell 似乎对我不起作用.它使用在函数运行时选择的单元格中的数据.那不是我要找的.我当然可以通过单元格本身(如在 A4、B7 等中)，但我不确定它在性能方面是否真的很重要.

Note: ActiveCell didn't seem to work for me. It uses the data from the cell which is selected by the time the function is run. That's not what I'm looking for. I could of course pass the cell itself (as in A4, B7, etc.) but I'm not sure if it really matters in terms of performance.

感谢所有回答我问题的人.

Thanks to all of you who responded to my question.

推荐答案

我遇到了与 AND() 破坏条件格式相同的问题.我只是碰巧尝试将 AND 视为乘法，它起作用了！删除 AND() 函数并乘以您的参数.Excel 会将布尔值视为 1 表示真，0 表示假.我刚刚测试了这个公式，它似乎有效.

I was having the same problem with the AND() breaking the conditional formatting. I just happened to try treating the AND as multiplication, and it works! Remove the AND() function and just multiply your arguments. Excel will treat the booleans as 1 for true and 0 for false. I just tested this formula and it seems to work.

=(INDIRECT(ADDRESS(4,COLUMN()))>=INDIRECT(ADDRESS(ROW(),4)))*(INDIRECT(ADDRESS(4,COLUMN()))<=INDIRECT(ADDRESS(ROW(),5)))

这篇关于使用 AND() 函数的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！