【原创】条件格式：当相邻单元格符合条件时如何高亮显示当前单元格

z391634362

新手发帖，先拜过各位大神。

以下为我对条件格式应用的一些心得，望多指教。

关于条件格式，我个人之前使用的最多的是对当前单元格满足相应条件时高亮显示，比如当前单元格包含、大于条件等时高亮显示，这应该是最简单的做法，可以直接一键操作。那么，当相邻单元格符合条件时，如何高亮显示当前单元格呢？

废话不多说，先上图。


如图所示，共5组产品检测数据，每组产品有Ⅰ、Ⅱ号两种产品。

M5单元格，就是两个立体单元格左边那个，为需要高亮显示的产品组，O5单元格，即右边那个，为需要查询/筛选的分数。这两个单元格是可以输入的，输入后数据区会根据输入的内容变化高亮显示的单元格。

要求：

我们需要做3个条件格式规则，能满足以下条件：
以D组产品，60分为合格线为例

1. Ⅰ、Ⅱ号产品检测分数均高于查询分数的样品组，绿色显示（Ⅰ、Ⅱ>=60分）

2. Ⅰ、Ⅱ号产品有任意一种产品检测分数低于查询分数的样品组，黄色显示（Ⅰ/Ⅱ<60分）

3. Ⅰ、Ⅱ号产品检测分数均低于查询分数的样品组，红色显示（Ⅰ、Ⅱ<60分）

当然，前提是不能用VBA（咳，我会承认我其实是不会用么）

先说说我对条件格式的理解，在我看来条件格式的本质很简单，其实就是公式+方程式，可以这么理解，当你在条件格式中输入自定义公式后，只要把某单元格代入公式能得出TRUE的结果，该单元格就会高亮显示。

所以，当你用条件格式—突出显示单元格规则—等于3，该方法等同于自定义公式

=INDIRECT(ADDRESS(ROW(),COLUMN()))=3

意思是，不管你公式怎么编，一是公式本身要对，二是你需要高亮显示的单元格代入其中能得出TRUE的结果，就OK。

如果你理解了这一层含义，那么相邻单元格的定位就简单了。

相邻单元格=3时当前单元格高亮显示的公式应该为：=INDIRECT(ADDRESS(ROW(),COLUMN()±1))=3，也可以用OFFSET公式OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),,±1)=3

如果你懂了，本题也就easy了。

上公式。

列取本表格中第3种情况，Ⅰ、Ⅱ<60分的条件格式公式如下：

=(INDIRECT(ADDRESS(ROW(),COLUMN()))=$M$5)*(INDIRECT(ADDRESS(ROW(),COLUMN()+1))<$O$5)*(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<$O$5)=1

公式分三个部分：

1.       绿色部分为判断当前单元格是否等于D，如果符合条件，返回TURE（1），不符合则返回FALSE（0）；

2.       黄色部分判断右边的单元格COLUMN()+1是否<60，如果符合，返回TURE（1），不符合则返回FALSE（0）；

3.       蓝色部分判断左边单元格COLUMN()-1是否<60，如果符合，返回TURE（1），不符合则返回FALSE（0）。

当以上条件都满足的时候，3项TURE相乘返回数值1 =1，返回TRUE，高亮显示。如果有任何一项不满足返回FALSE则相乘的结果为0<>1，返回FALSE，非高亮显示。如此就达到了当前单元格=D，且左右单元格<60，三项同时达标才能高亮显示的要求。

同理，Ⅰ、Ⅱ>=60，Ⅰ/Ⅱ<60的情况大家可以自己研究一下。

纯属原创，本人才疏学浅，班门弄斧，如有前辈已发表过类似的帖子，大家就一笑而过，当没看见~ ^ ^

条件格式.zip (12.8 KB, 下载次数: 27)

2014-11-24 16:38 上传

点击文件名下载附件

蝶·舞

公式有点过于复杂了

qh8600

支持一下

tgydslr

不错，分享了就好，大家都学习到了

z391634362

蝶·舞 发表于 2014-11-24 17:13
公式有点过于复杂了

可否指点一下公式如何简化？