excel条件格式的多种功能 [英] excel conditional formatting multiple functions
问题描述
我有一个动态表，例如5列:
I have a dynamic table with for example 5 columns:
- 第一列->仅包含"AA"或"BB"字符串.
- 第2/5列->只有一个字母值.
- 1st column -> only has "AA" or "BB" strings.
- 2nd/5th column -> only has one letter values.
我已经有以下规则:
- 如果一行的第一列具有 AA ，则该行会用黄色填充.
- 如果一行的第一列中有 BB ，则该行会用蓝色填充.
- If a line has AA in the 1st column, it fills the line with color yellow.
- If a line has BB in the 1st column, it fills the line with color blue.
我使用此公式，并且有效:
I use this formula, and it works:
=Search("AA"; $A1)>0
现在我想要一条规则:
- 如果我在其他列中找到字母 M ，但它在 AA 行中，则带有 M 的单元格将被其他颜色，例如绿色.
- 如果在其他列中找到字母 M ，但它在 BB 行上，则带有 M 的单元格将被另一个单元格填充颜色，例如红色.
- If I find the letter M in the other columns, but it's on the AA line, the cell with M it's filled with other color, let's say green.
- If I find the letter M in the other columns, but it's on the BB line, the cell with M it's filled with another color, let's say red.
这可能吗?
下图代表了我想要的: 表
The figure below represents what I want: table
推荐答案
搜索不会在单元格之间轻松找到内容.
Search wont find things easily across cells.
最好使用另一个功能.在这种情况下，我通常使用COUNTIF.
It is better to use another function. I typically use COUNTIF in this case.
仅突出显示表中的一个单元格，并将其用作条件格式中的公式. 此选项仅在带有"M"的单元格上为您突出显示:
Highlight only one cell in your table and use this as the formula in the conditional formatting. This one gives your the highlight on the cell with "M" only:
=AND(SEARCH("AA", $A1)>0, A1="M")
=AND(SEARCH("BB", $A1)>0, A1="M")
对于该行，请使用:
=COUNTIF($A1,"AA")>0
=COUNTIF($A1,"BB")>0
将条件格式仅放在一个单元格中之后，更改适用于"字段以反映希望将其应用到的整个范围.这依赖于条件格式中的相对引用.这会很快变得非常混乱，因此我建议手动编辑适用于"字段，而不是将格式复制/粘贴到指定区域.
After putting the conditional formatting in only one cell, change the "Applies to" field to reflect the whole range you wish apply it to. This relies on relative references in conditional formatting. This can get very messy very quickly, so I recommend manually editing the "Applies to" field instead of copy/paste formats to the specified areas.
更新了图片和公式，以反映问题的清晰度.
Updated the picture and formulas to reflect the update to the question's clarity.
这篇关于excel条件格式的多种功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
