基于其他工作表和单元格中的值设置单元格颜色格式

下面是我如何在Excel2003中使用条件格式完成的。

将条件格式应用于 Sheet1 使用来自的值 Sheet2 ,需要将值镜像到 表一 .

创建第1页第2列的镜像

1. 去 表一 .
2. 通过右键单击A列的标题并选择“插入”来插入新列。

3. 在A1中输入以下公式:

   =IF(ISBLANK(Sheet2!B1),"",Sheet2!B1)

4. 拷贝 A1 右键单击并选择“ Copy “。
5. 将公式粘贴到列中 A 右键单击其标题并选择“ Paste “。

表一 ,专栏 一 现在应该精确镜像 板2 ,专栏 B .

(注:如果您不喜欢列中的内容 一 ,它的工作原理和将它放在列中一样好。 Z 或者其他地方。)

应用条件格式

1. 停留在 表一 .
2. 选择列 乙 通过左键单击其标题。
3. 选择菜单项 Format > Conditional Formatting...

4. 变化 Condition 1 “ Formula is “并输入以下公式:

   =MATCH(B1,$A:$A,0)

5. 单击 Format... 按钮并选择绿色背景。

现在应该可以看到应用于匹配单元格的绿色背景 表一 .

隐藏镜像列

1. 停留在 表一 .
2. 右键单击列上的标题 一 选择“ Hide “。

这将自动更新 表一 无论什么时候 板2 改变了。

你也可以用 命名范围 因此,您不必将单元格从Sheet1复制到Sheet2:

1. 定义一个命名范围,比如 Sheet1Vals 对于具有要基于其条件的值的列。您可以使用 Insert\Name\Define... 菜单项。输入您的姓名,然后在 Refers to 框选择要在该范围内的单元格。如果范围将随时间变化(添加或删除行),则可以使用此公式而不是显式选择单元格:

   =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 我正在格式化的列是列 B 在Sheet2中,我的条件格式如下:

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

然后,您可以使用格式刷将这些格式复制到其余单元格中。

我使用的是Excel2003-

这里使用条件格式的问题是,在条件中不能引用其他工作表或工作簿。您可以做的是将表1中的某一列设置为表2中的相应列(在您的示例中=表2!)B6)。我在下面的示例中使用了F列。然后可以使用条件格式。在第1页第1行第1列选择单元格,然后转到“条件格式”菜单。从下拉列表中选择“Formula is”,并将条件设置为“=$F$6=4”。单击“格式”按钮,然后选择“图案”选项卡。选择你想要的颜色,你就完成了。

可以使用格式绘制工具将条件格式应用于其他单元格,但请注意,默认情况下,Excel在条件中使用绝对引用。如果你希望他们是相对的,你需要从条件中去掉美元符号。

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

以下是我自己的解决方案,用于在将某些高度格式化的工作表或模板复制到新的电子表格时恢复原始颜色。它直接复制所有数据,因此仅在需要复制工作表时才起作用,而不只是对具有不同数据的不同工作表应用颜色:

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

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

在目标仍然突出显示的情况下,右键单击并转到“选择性粘贴”,选择“全部使用源主题”,然后重复选择性粘贴,这次只使用“值”,它应该与您复制的原始工作表相同。

我以前用过 conditional formatting . 这是一种很好的方法,可以直观地检查工作簿中的单元格,并找出数据中的异常值。