单元格中的格式取决于Excel中的行

Lets say I have a name in cell A1. I'd like to make that cell green if there is "o" or "x" in any of the cells spanning B1:Z1.

假设在单元格A1中有一个名字。如果在生成B1:Z1的任何细胞中有“o”或“x”，我想把这个细胞变成绿色。

Also, could you do this thing on a larger scale if you have 20 names under column A from A1:A20; so you don't need to format each cell individually?

同样，如果你在A1:A20的a列下有20个名字，你能在更大的范围内做这件事吗?所以你不需要分别格式化每个单元格?

Here is an example of my naughty and nice list:

下面是我列出的一个淘气又漂亮的例子:

    A          B       C       D
    Name      Mon     Tue     Wed
    Mark       x               x
    Donna      o       x       o
    Amy                x

I tried to do it with conditional formatting on A2

我试着用A2的条件格式来做。

=OR($B$2:$H$2="x";$B$2:$H$2="o")

=或(B美元2:H 2美元= " x ";B美元2:H 2美元=“o”)

but in this instance it only works on the first two examples, since the B column has a value in it. Is it possible to do that on a list of 40 people on a mass scale. to not have to do formatting for each row seperately?

但是在本例中，它只适用于前两个示例，因为B列中有一个值。是否有可能在一份40人的大规模名单上做到这一点?不需要对每一行分别进行格式化吗?

I'm using Excel 2013 if that helps in any way.

我正在使用Excel 2013，如果这对我有任何帮助的话。

3 个解决方案

#1

I would do what you have (the OR expression,
=OR(NOT(ISBLANK(Table2[@[B]:[D]]))) (as an array, shift-+ctrl+enter)
) and put it in it's own column(E). Then just look at that with a formula in the conditional formatting that looks like:
=E2

我将按照您所拥有的(OR表达式，=或(NOT) (ISBLANK(表2[@[B]:[D]]))(作为数组，shift-+ctrl+enter))进行操作，并将其放在自己的列(E)中。然后，只需在条件格式中使用公式(如:=E2)查看它

#2

Ahh, now that I understand the question, the conditional format option can be applied.

啊，既然我理解了这个问题，就可以使用条件格式选项了。

Select cells A2:A20 Go to Conditional Formatting on the Home tab Click on New Rule... and you will see something like this

选择单元格A2:A20在Home选项卡上选择条件格式，点击新规则…你会看到这样的东西

Click the "Use a formula" option and enter this in the "Format values where..."

单击“使用公式”选项，并在“格式值…”中输入该选项。

=OR(COUNTIF(A2:H2,"x")>0,COUNTIF(A2:H2,"o")>0)

and then click the Format... button to apply a green fill to the cells.

然后点击格式…按钮应用绿色填充到单元格。

I'm using COUNTIFs because comparing or testing a range for a specific value needs an array formula which I've never really considered for a conditional format. It may work but I prefer the KISS principle.

我使用COUNTIFs，因为比较或测试特定值的范围需要一个数组公式，我从来没有考虑过条件格式。这可能行得通，但我更喜欢KISS原则。

#3

for me this formula: =OR(B2:X2="o",B2:X2="x") produces the desired result, and I don't experience the strage problem "when B is empty" as you describe.

对于我来说，这个公式:=或(B2:X2="o"，B2:X2="x")产生了想要的结果，我没有遇到你描述的“B为空”时的strage问题。

Not sure about the difference between ; and , separators - it has something to do with regional settings (some info here)

不确定两者之间的区别;还有分隔符——它与区域设置有关(这里有一些信息)