I have a column A that contains a 1-4 word phrases in each cell.
我有一个A列，每个单元格中包含1-4个单词短语。
I also have 4 cells that contain 1 word values:
我还有4个包含1个单词值的单元格：
B1 C1 D1 and E1
B1 C1 D1和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.
1）如果来自A列的单元格中的文本包含与上述一个单元格中的值匹配的单词，请将该单元格（来自A列）突出显示为红色。
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.
2）如果来自A列的单元格中的文本包含与上述两个单元格的值匹配的单词，则将该单元格（来自A列）突出显示为蓝色。
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.
3）如果来自A列的单元格中的文本包含与上述三个单元格中的值匹配的单词，则以黄色突出显示该单元格（来自A列）。
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.
4）如果来自A列的单元格中的文本包含与上述所有四个单元格中的值匹配的单词，请以绿色突出显示该单元格（来自A列）。
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.
当我更改B1 C1 D1或E1单元格中的任何一个值时，我希望它反映在A列中，如果不是立即通过运行某种宏的方式。
I suspect it should be either conditional formatting by formula or by running some sort of macro...
我怀疑它应该是公式的条件格式化或运行某种宏...
P.S: I use Excel 2010
P.S：我使用的是Excel 2010
1 个解决方案
#1
4
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.
你显然需要为2,3和4添加一个公式，并选择合适的颜色，但这样做。
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.
如果要在单元格中测试公式，则需要使用Ctrl-Shift-Enter将其作为数组公式输入。但条件格式识别数组公式没有任何花哨的步法。
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:
公式表示由任何文本包围的B1到E1中包含的值的出现次数的总和，因此是通配符。如果您只抓取公式的COUNTIF部分并按F9，您将看到它评估为：
=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:
要将条件格式应用于所有列A，只需在每个公式的“应用于”框中输入$ A：$ A.
#1
4
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.
你显然需要为2,3和4添加一个公式，并选择合适的颜色，但这样做。
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.
如果要在单元格中测试公式，则需要使用Ctrl-Shift-Enter将其作为数组公式输入。但条件格式识别数组公式没有任何花哨的步法。
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:
公式表示由任何文本包围的B1到E1中包含的值的出现次数的总和，因此是通配符。如果您只抓取公式的COUNTIF部分并按F9，您将看到它评估为：
=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:
要将条件格式应用于所有列A，只需在每个公式的“应用于”框中输入$ A：$ A.