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

问题描述

我正在纸上尝试条件格式。我需要根据以下条件语句填充单元格：

  = AND（（INDIRECT（ADDRESS（4 ; COLUMN（）））> = INDIRECT（ADDRESS（ROW（）; 4）））;（INDIRECT（ADDRESS（4; COLUMN（）））≤= INDIRECT（ADDRESS（ROW（）; 5）））） 
  

当我单独尝试AND（）函数中的语句时，它们似乎工作，但是当我将它们放在函数中，我看不到任何格式发生。

这里有一些背景：
当前列的第4行有一个日期（DATE1）。 当前行（DATE2和DATE3）的D和E列也有日期。所以，如果DATE1在DATE2到DATE3之间，我想填充单元格。

我不明白为什么公式不起作用。

更新（2011年12月13日）

注意：ActiveCell似乎不适合我。它使用从运行功能时选择的单元格中的数据。这不是我要找的。我当然可以通过电池本身（如A4，B7等），但我不知道在性能方面是否真的很重要。

感谢所有的人都回答了我的问题。

解决方案

我和AND（）打破条件格式化有同样的问题。我刚刚碰巧尝试将AND作为乘法来处理，它可以工作！删除AND（）函数，只是将你的参数乘以。 Excel将把booleans视为1为true，0为false。我刚刚测试了这个公式，似乎有效。

  =（INDIRECT（ADDRESS（4，COLUMN（）））> = INDIRECT（ADDRESS（ROW（），4）））*（INDIRECT（ADDRESS（4，COLUMN（）））< = INDIRECT（ADDRESS（ROW（），5）））
  

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))))

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.

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.

Update (Dec 13, 2011):

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.

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.

解决方案

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屋！