Excel 条件格式和偏移

Excel 条件格式和偏移

我无法将条件格式复制到右侧(使用画笔),因为 Excel 在复制时引用了错误的单元格。这是我需要实现的:

如果 Sheet2!E4=empty,则 A1 需要被格式化。我将条件格式公式设置为“=isblank(Sheet2!E4)=false”,并且它可以起作用。

现在我需要针对以下场景实现完全相同的格式:

如果 Sheet2!E4=空,则需要格式化 A1

如果 Sheet2!E5=空,则 B1 需要格式化

如果 Sheet2!E6=空,则 C1 需要格式化,等等。

请注意,我试图让 Excel 从左到右复制条件格式公式(AC 列),但参考单元格从上到下(第 4-6 行)。

有没有办法实现这一点,而不必单独输入条件格式公式?也许包括偏移量?这是一个非常大的表格,我永远也做不完。任何意见都值得赞赏!

答案1

实现此目的的一种方法是在任意一张工作表中设置一个辅助行。

在以下示例中,我在新工作表 Sheet3 中添加了一个辅助行,如下所示。使用此功能,您实际上是在转置以获取对 Sheet2 中单元格的引用,您希望在 Sheet1 的条件格式中使用这些单元格。 在此处输入图片描述

接下来,在 Sheet1 中单元格 A1 设置以下条件格式公式。 =INDIRECT(CONCATENATE("Sheet2!"&Sheet3!A1))=""

在上面的公式中,INDIRECT 函数将有助于将 Sheet3!A1 转换为 A1 的内容,然后转换为 Sheet2!E4 并根据空白值对其进行评估。

现在,您可以将格式从 A1 复制到单元格范围。复制时,正常的 Excel 行为将启动,并在后续单元格中将 A1 替换为 A2、A3、A4 等。HTH

答案2

选项1

另一种与 Bharat 的答案类似的方法是使引用绝对但偏移量为每列增加 +1 的索引行。

为了直观起见,假设 Sheet1 中有以下内容:

E3  Data Header [not actual data, just the header]
E4  Apple
E5  Banana
E6  Orange
E7+ [Blank]

您可以对 Sheet2 执行以下操作,您可以添加一行(如果需要,可以使其不可见),从 1 开始,每列增加 1。从最左边的列开始,将偏移公式放入 IF 语句中并引用索引列。这是您想要停止从 Sheet2 的右侧拖动/复制的位置,当您向右拖动时,偏移量将随着引用的增加而增加 +1。

也许直观地看会更容易。下面是 Sheet2 的视觉效果,我还展示了返回的数据,以便您知道偏移量正在起作用,以及IF(ISBLANK,TRUE,FALSE)Excel 公式中嵌入偏移量函数的示例。如您所见,系统将增加偏移量列,但不增加引用列,这样,使用您的索引,它会在 Sheet 1 上向下增加,而在 Sheet 2 上横向移动。

[表2]https://i.stack.imgur.com/OZKE3.jpg


选项 2

基于列的偏移,您甚至不需要创建索引行来引用,如果您不是从 A1 开始,则可能需要相应地调整列偏移。这也可能不适用于所有情况。

从 Sheet1

=IF(ISBLANK(OFFSET(Sheet2!$E$3,COLUMN(),0)),TRUE,FALSE)

相关内容