excel - 如果列表的所有值都存在于不同的列中，请更改excel中单元格的颜色？

Question

如果列表的所有值都存在于不同的列中，我正在尝试更改 excel 中单元格的颜色。

我，还有另一列有一些值。

如果MyList的所有值都不存在，我希望myColumn单元格的颜色应该是红色。

如果MyList中的所有值都存在于myColumn中，它应该是绿色的

我尝试使用conditional formatting，但它会根据单个单元格值更改单元格的颜色。

=INDEX(B2:H2,MATCH(D2,B13:B15,0))

我怎样才能根据列表的值来做到这一点？

标签： excelexcel-formulaconditional-formatting

Answer 1

假设您有以下命名范围：

• MyListB2:H2在我的例子中是 Cell ；
• B5:B7在我的示例中，myColumn是 Cell 。请注意，我以表格的形式命名了这个范围，所以当我向这个列添加值时，新值会自动添加到命名范围中。将标题的背景颜色设置myColumn为 red。

然后您可以将以下公式设置为单元格的条件格式规则，B4即标题myColumn：

=SUMPRODUCT(ISNUMBER(MATCH(MyList,myColumn,0))*1)=COLUMNS(MyList)

MATCH将比较MyListwith中的值myColumn并返回一个匹配的数字，#N/A如果不匹配，ISNUMBER会将结果转换为TRUEor FALSE。SUMPRODUCT将对结果求和，如果所有值都匹配，则应7在上面的示例中返回。然后将求和结果与预期结果进行COLUMNS(MyList)比较7。如果匹配，公式将返回TRUE，然后触发条件格式将单元格背景颜色变为绿色。

如果我添加更多值myColumn以匹配MyList：

请注意，该公式仅检查是否所有MyList值都存在于myColumn而不是相反，这意味着如果您myColumn还包含其他数字，例如8, 9，10它们不在 中MyList，只要它包含来自的所有值，标题仍将变为绿色MyList不管它是否有额外的价值。

如果您有任何问题，请告诉我。干杯:)