☰

工作表1

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
工作表1

我有一张包含 2 个工作表的 Excel 电子表格。第一个工作表只有一个标题行和一列项目名称。第二个工作表是项目组列表，最左侧列中有一个标题行和一个标题，后面的每一行都是来自另一张工作表的一个项目或另一个项目：

Sheet1:            Sheet2:
+-------+--+--+    +-------+-------+-------+-------+-------+
| Item  |  |  |    | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+    +-------+-------+-------+-------+-------+
| Shirt |  |  |    | A     | Shirt | Hat   | Tie   |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Hat   |  |  |    | B     | Socks | Shirt | SHOES |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Socks |  |  |    | C     | Hat   | Socks |       |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Tie   |  |  |    | D     | Tie   | Tie   | Socks |
+-------+--+--+    +-------+-------+-------+-------+-------+
| ...   |  |  |
+-------+--+--+

我想有条件地格式化“Sheet2”中的所有单元格，这样任何与“Sheet1”第一列中的值不匹配的值都标记为红色背景；与“Sheet1”第一列中的值匹配的值标记为绿色背景。因此，本例中从 B2 开始的所有单元格都将为绿色，除了值“SHOES”。下面的值没有输入任何内容，因此根本不会格式化。

我尝试过的绿色的格式规则是：

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

对于红色，大致相同：

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

这两条规则都“适用于”某种程度上的任意范围（我希望它适用于整个工作表，而不是最上面和最左边的行/列）：

=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35

这个方法虽然有效，但结果却难以预测。有些值如我所料高亮显示，但只有几行，而其他值则没有。可能我的范围不知何故不正常，但我使用 Excel 的频率已经不像以前那么高了。有人能帮忙吗？

谢谢！

答案1

正如 Doktoro Reichard 所说，你想使用条件格式执行此操作。在此特定情况下，您需要制定三条规则：

  1. 如果单元格为空白，则不要更改背景
  2. 如果单元格匹配，则将背景设为绿色
  3. 如果单元格没有匹配，则将背景设为红色

抱歉，我的 Excel 是日文的。今天是多语言日。

为此，我们需要 3 个公式来返回每个条件的TRUE或FALSE。我假设您的数据如下所示：

工作表1

在此处输入图片描述

工作表2

在此处输入图片描述

规则1

以下公式将返回单元格是否为空白。我选择了

在此处输入图片描述

=ISBLANK(B2)

请注意，我选择了B2:D5具有相对引用的单元格。这将应用相同的公式，更改所选范围内每个单元格的单元格引用。当此条件为真时，将背景颜色设置为白色（或您喜欢的任何颜色）。

规则2

以下公式将返回工作表 1 中的列表中是否存在完美匹配：

在此处输入图片描述

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

规则3

以下公式将返回工作表 1 中的列表中是否没有完美匹配：

在此处输入图片描述

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

命令

最上面的规则将首先执行。因此，由于所有空白单元格都将不匹配，因此您需要将空白规则放在首位。#2 和 #3 的顺序无关紧要（它们永远不会重叠）。

答案2

看来您需要的是单元格条件格式。

这里有一个关联描述这是什么。

我的图片来自葡萄牙语版本的 Excel 2003，但 Office 2010 中也应该具有该功能。事实上，Office 2010 允许工作表之间存在条件格式，而 2003 则不能，因此，我在一个工作表上完成所有操作。

首先，表格：

表格

您想要做的是将第二个表中的元素与第一个表中的所有元素进行比较。因此，您需要编写如下函数：

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

所做EXACT()的是比较两个文本字符串。 所做的是如果其中有任何逻辑条件满足，则OR()变为。TrueTrue

话虽如此，然后您从第二个表中选择所有单元格，然后按条件格式，如下所示。

如何访问条件格式

了解 Office 2010 及其新的功能区界面后，您应该在“格式”窗格中查找它。如果我没记错的话，它以图标的形式出现。

单击该图标将出现类似如下的窗口：

条件格式窗口的一部分

在那里，您首先需要选择您想要的公式，然后粘贴我之前提到的公式。要使所有符合条件的单元格都变成绿色，只需更改格式即可。要进行红色格式化，只需使用NOT(OR(...))；这将返回您设置的条件的逆。

为了确保它不会格式化其中没有任何内容的单元格，请创建第三个条件，其中公式为 ISBLANK(E3)（E3 为左上角）。

我在写完初稿后检查了优先级。至少在我的版本中，条件 1 在条件 2 之前得到验证，依此类推。因此，您应该以不会干扰自身的方式对条件进行排序。因此：

1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))

因此，你应该有一个这样的窗口：

最后的窗口

尝试根据你的情况进行调整。如果我没记错的话，这与我展示的并没有什么不同。结果应该是这样的：

最终结果

相关内容