☰

Excel 多条件格式问题

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting array
Excel 多条件格式问题

我需要一个公式来查看一个单元格中的文本并将其与其他三列进行比较，并以指定的颜色突出显示。

以下是示例：

数据

  • 单元格 A2 包含“这是单元格中的示例文本”
  • 单元格 B2 包含“样本”
  • 单元格 C2 包含“文本”
  • 单元格 D2 包含“单元格”

所需配方

  1. 如果 A2 包含 B2 或 C2 或 D2，则突出显示黄色
  2. 如果 A2 包含 B2 和 C2，或者包含 B2 和 D2，或者包含 C2 和 D2，则突出显示蓝色
  3. 如果 A2 包含 B2 和 C2 和 D2，则突出显示绿色

我尝试过的方法

  • 我尝试过使用=SUM(COUNTIF(A2,"*"&B2&"*"))。这对于 #1 来说可以正常工作，但需要我创建单独的格式规则，分别为 B2、C2 和 D2 创建规则。因此它不太优雅，我无法找出使其适用于 #2 的正确语法。

  • 对于＃3，我尝试使用，=SUM(COUNTIF(A2,"*"&B2:D2&"*"))但这会返回误报并显示只有一个单元格符合条件。

  • 我也尝试过=SUMPRODUCT(--(A2=things))>0（things=命名数组），如下所述：https://exceljet.net/formula/cell-equals-one-of-many-things 但这不起作用，因为它要求我为每一行创建唯一的列表名称。

  • 而且我也尝试过=IF(OR(A2="sample",A2="text",A2="cell"),"True","False")并应用 True 输出的条件格式，但这不起作用，因为它依赖于硬编码值。

公式必须能够使用动态值（单元格编号 A2、B2、C2 等），不能依赖于任何硬值。我需要对数百行使用此公式，并将不同的值插入每个单元格。因此，公式必须仍然以相同的方式适用于单元格 A3、B3、C3 和 D3，而无需我进行任何更改。它需要直接复制并粘贴到正在评估的列中，在此示例中，它将是 A 列中的所有内容。

我是新来的，所以如果这个问题在其他地方已经得到解答，我深表歉意，只是我找不到。如果是这样，请告诉我答案！

答案1

这应该可以解决问题：

=SUMPRODUCT(-- ISNUMBER(SEARCH(B2:D2,$A$2)))

这与您的第三次尝试类似，但是 -

  • 您不需要使用命名范围。您只需使用B2:D2，当您将其复制到下一行时，它将按您的期望工作。
  • 您需要检查字符串是否包含测试值，而不是是否等于它们。

SEARCH返回一个字符串在另一个字符串内的起点，ISNUMBER根据是否找到该字符串将其转换为 true 或 false，并将--布尔值转换为 0 或 1。传递一系列值意味着SEARCH输出是一个由 0 和 1 组成的数组，而不是单个值。

使用SUMPRODUCT而不是SUM可以让你将这些值相加，而无需使用 Excel 的数组公式符号。最终结果是一个从 0 到 3 的数字，具体取决于成功执行的比较次数，你可以测试该数字以驱动条件格式。

请注意，如果任何比较单元格为空白，则比较将成功（因为所有字符串都包含空字符串）。

（根据https://exceljet.net/formula/cell-contains-one-of-many-things）

相关内容