公式为条件格式化文本，其中包含另一个单元格的值 [英] Formula to conditional formatting text that contains value from another cell
问题描述
我还有4个单元格包含1个单词值：
B1 C1 D1和E1
我需要设置条件格式化就是这样的：
1）如果列A中的单元格中的文本包含与上述单元格中的值相匹配的单词，请以红色突出显示单元格（从A列）。
2）如果列A中的单元格中的文本包含与上述单元格的TWO匹配的单词，请以蓝色突出显示单元格（从A列）。
3）如果列A中单元格中的文本包含与上述单元格的三个值匹配的单词，请以黄色突出显示该单元格（从A列）。
4）如果列A中的单元格中的文本包含与上述单元格的所有FOUR中的值匹配的单词，请以绿色突出显示该单元格（从A列）。
查看附件图示：
当我更改B1 C1 D1或E1单元格中的任何一个值时，我想要将其反映在列A中，如果不是立即通过运行某种宏的方式。
我怀疑它应该是通过公式的条件格式化或通过运行某种宏...
PS：我使用Excel 2010
将此公式用于条件格式：
= SUM（COUNTIF（A1，*& $ B $ 1：$ E $ 1& *））= 1
你显然需要添加一个2，3和4，并选择适当的颜色，但这样做。
如果要测试单元格中的公式，则需要使用Ctrl作为数组公式输入-Shift输入。但是，条件格式化可以识别数组公式，而不需要任何花哨的步法。
公式表示将B1中包含的值的计数和由任何文本包围的E1相加，因此通配符。如果您只抓住公式的COUNTIF部分并按F9，您会看到它的评估结果如下：
= SUM（{1,0,0,0}）= 1
要将条件格式应用于所有列A，只需在每个公式的适用于框中输入$ A：$ A：
I have a column A that contains a 1-4 word phrases in each cell.
I also have 4 cells that contain 1 word values:
B1 C1 D1 and E1
I need to setup conditional formatting is such a way, that:
1) If text in a cell from column A contains a word that matches value from ONE of the cells mentioned above, highlight that cell (from column A) in red.
2) If text in a cell from column A contains words that matches value from TWO of the cells mentioned above, highlight that cell (from column A) in blue.
3) If text in a cell from column A contains words that matches value from THREE of the cells mentioned above, highlight that cell (from column A) in yellow.
4) If text in a cell from column A contains words that matches value from all FOUR of the cells mentioned above, highlight that cell (from column A) in green.
View an attached image for illustration:
When I change a value in any of B1 C1 D1 or E1 cells, I want it to be reflected in the column A, if not immediately then by the means of running some sort of macro.
I suspect it should be either conditional formatting by formula or by running some sort of macro...
P.S: I use Excel 2010
Use this formula for your conditional formatting:
=SUM(COUNTIF(A1,"*" & $B$1:$E$1 & "*")) = 1
You'll obviously need to add a formula for 2, 3 and 4, and select the appropriate colors, but that will do it.
If you want to test the formula in a cell it needs to be entered as an array formula with Ctrl-Shift-Enter. But conditional formatting recognizes array formulas without any fancy footwork.
The formula says sum the counts of the occurrences of the values contained in B1 to E1 surrounded by any text, hence the wildcards. If you grab just the COUNTIF part of the formula and press F9, you'll see that it evaluates to something like:
=SUM({1,0,0,0}) = 1
To apply the Conditional formatting to all of Column A, just enter $A:$A in the Applies To box for each formula:
这篇关于公式为条件格式化文本，其中包含另一个单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
