![Excel 条件格式和偏移](https://linux22.com/image/1590137/Excel%20%E6%9D%A1%E4%BB%B6%E6%A0%BC%E5%BC%8F%E5%92%8C%E5%81%8F%E7%A7%BB.png)
我无法将条件格式复制到右侧(使用画笔),因为 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)