根据另一个工作表和单元格中的值设置单元格颜色格式

Question

我有一个带两个工作表的工作簿。我想根据第二张工作表中的值来格式化第一张工作表中第一列的单元格背景色。

例如，如果sheet2中第6行第2列的值为4，则我希望sheet1中第4行第1列的背景颜色为绿色。如果在sheet2的第二列中没有任何值与sheet1中的某一行对应，则我希望将其保留为无颜色状态。在sheet2的第二列中出现相同的值并不会受到禁止。如果您告诉我如何取消指向sheet1的最后一个值，则会获得额外的赞誉。

我相信对于Excel高手来说，这可能很简单，但是我很少使用Excel，当然也没有时间成为黑带。可以有人给我建议、指导或快速公式来完成这个问题吗？如果需要编写复杂的VB代码来实现此操作，则不值得。

谢谢！

- DCookie

---

这是一个获取答案的地方，还是一个辩论语义的地方？我不明白为什么在Excel中应用公式不算一种编程形式。 - DCookie

Answer 1

您也可以使用命名区域来完成此操作，这样您就不必将Sheet1的单元格复制到Sheet2了：

1. 为具有要基于其条件的值的列定义一个命名区域，例如Sheet1Vals。您可以通过使用“插入\名称\定义...”菜单项来定义新的命名区域。键入名称，然后在“引用”框中使用单元格浏览器选择要包含在范围内的单元格。如果该范围随时间变化（添加或删除行），则可以使用此公式而不是显式选择单元格：

   =OFFSET('SheetName'!$COL$ROW,0,0,COUNTA('SheetName'!$COL:$COL))。

   如果列具有标题行，请在最后一个)前面添加-1。

2. 为具有要以条件格式设置的值的列定义一个命名区域，例如Sheet2Vals。
3. 使用条件格式设置对话框创建条件。在下拉菜单中指定Formula Is，然后将以下公式放入公式中：

   =INDEX(Sheet1Vals,MATCH([FirstCellInRange],Sheet2Vals))=[Condition]

   其中[FirstCellInRange]是您要设置格式的单元格的地址，[Condition]是您要检查的值。

例如，如果在Sheet1中我的条件具有1、2和3的值，并且我正在格式化Sheet2中的B列，则我的条件格式将如下所示：

=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=1
=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=2
=INDEX(Sheet1Vals, MATCH(B1,Sheet2Vals))=3

您可以使用格式刷将这些格式复制到其他单元格中。

- Patrick Cuff

---

这是正确的答案。在Google Sheets中，您可以使用=indirect("NamedRange")进行引用。 - SavageCore

Answer 2

以下是我在Excel 2003中使用条件格式的方法，使用Sheet2中的值来应用到Sheet1中，您需要将这些值反映到Sheet1中。

创建一个镜像Sheet2中B列的Sheet1

1. 进入Sheet1。 2. 通过右键单击A列标题并选择“插入”来插入新列。 3. 在A1中输入以下公式： =IF(ISBLANK(Sheet2!B1),"",Sheet2!B1) 4. 通过右键单击A1并选择“复制”来复制A1。 5. 通过右键单击列A的标题并选择“粘贴”将该公式粘贴到列A中。

现在，Sheet1中的A列应该与Sheet2中的B列完全相同。（注意：如果您不喜欢它在A列中，那么将其放在Z列或任何其他地方也可以。）

应用条件格式

1. 保持在Sheet1上。 2. 单击B列标题以选择该列。 3. 选择菜单项“格式>条件格式…” 4. 将“条件1”更改为“公式是”，并输入以下公式： =MATCH(B1,$A:$A,0) 5. 单击“格式…”按钮并选择绿色背景。

现在，您应该在Sheet1中看到匹配单元格应用了绿色背景。

隐藏镜像列

1. 保持在Sheet1上。 2. 右键单击A列的标题并选择“隐藏”。

这样，无论在Sheet2中何时更改任何内容，都应自动更新Sheet1。

- mskfisher

---

+1，就是这样，只是它没有按照我想要的方式工作，我认为它假定B列的值是行号。如果B的值出现在A中的任何单元格中，我需要将B设置为绿色。所以这个更改对我有效：=MATCH(B1,$A:$A,0)。在解决方案中进行此更改，我会接受它。 - DCookie

Answer 3

我正在使用Excel 2003 -

在这里使用条件格式的问题是，您无法在条件中引用另一个工作表或工作簿。您可以在第1个工作表上将某些列设置为与第2个工作表上的相应列相等（例如，在您的示例中=Sheet2!B6）。我在下面的示例中使用了列F。然后，您可以使用条件格式。选择Sheet 1、行和列1处的单元格，然后转到条件格式菜单。从下拉菜单中选择“公式是”，并将条件设置为“=$F$6=4”。单击格式按钮，然后选择“图案”选项卡。选择所需的颜色即可。

您可以使用格式刷工具将条件格式应用于其他单元格，但请注意，默认情况下，Excel在条件中使用绝对引用。如果要使它们成为相对引用，则需要从条件中删除美元符号。

一个单元格可以应用最多3个条件（在条件格式对话框底部使用添加 >> 按钮），因此如果最后一行是固定的（例如，您知道它将始终是第10行），则可以将其用作条件来将背景颜色设置为无。假设您关心的最后一个值在第10行，则（仍然假设您已将Sheet1上的F列设置为Sheet2上相应的单元格），则将第一个条件设置为公式为=$F$10=""，模式为None。将其设置为第一个条件，它将覆盖任何后续冲突的语句。

- Lee Mandell

---

如果您在条件中使用了命名范围，那么可以引用另一个工作表。 - Patrick Cuff

Answer 4

这里是我自己的解决方案，用于在将某些高度格式化的工作表或模板复制到新电子表格时恢复原始颜色。它直接复制所有数据，因此仅适用于需要复制工作表的情况，而不仅仅是将颜色应用于具有不同数据的不同工作表：

通过ctrl + g并选择适当的范围复制原始格式工作簿

将其粘贴到新的工作表中，颜色将全部更改

仍然突出显示目标，右键单击并转到“特殊粘贴”，选择“使用源主题全部”，然后重复特殊粘贴，只有这一次使用“值”，它应该与您复制的原始工作表完全相同

- David Griffin

Answer 5

我以前用过条件格式化来完成这个任务。这是一种很好的方式，可以在工作簿中视觉检查单元格并发现数据中的异常值。

- jcollum

---

虽然它没有解决我需要引用第二个表格的问题，但是这是一个很棒的链接，点赞！ - DCookie