logo标签列表

Excel - 条件格式化 - 插入行

excelconditional-formatting
26

在“应用于”中使用偏移或间接似乎不起作用。是否有其他方法可以防止插入行后条件格式破裂？

我有一个条件格式适用于范围例如 $O$19:$O$105。条件是“如果单元格值> 10”，则以红色格式化。

问题是 - 当我在Excel中插入行时，此格式化范围会分裂，我会得到2个格式化规则。例如，如果我在第20行插入新行，则范围为 $O$19，$O$21:$O$105 和 $O$20 的2个规则。

通常对于上述条件，如果规则分成多个范围可能并不重要。但是对于“突出显示前10个”的条件，它会导致不希望的结果。

我尝试了以下方法，但并没有取得太大的成功：

  1. 尝试使用间接函数-但Excel似乎会解析公式并保存格式化规则，因此无法按预期进行插入。
  2. 尝试使用偏移函数-在这里，Excel再次解析了与上述相同的范围。

有人知道如何编写条件格式，以防止行插入后破裂吗？

[编辑]我意识到插入行并不会导致条件格式规则分裂。我还将一行复制并粘贴到插入的行中，这才是问题所在。如果我选择特殊粘贴并仅选择公式，则一切正常。

如果内置的条件格式不符合您的特定情况，您可以始终通过依附于工作表更改方法并查找与要执行格式化的区域相交的方式，在VBA中自定义编码动态格式化规则。 - danielpiestrak
@Gimp 你有一个我可以开始的示例/片段吗？我已经使用过VBA，但还不是很擅长。 - ssp
在使用VBA之前，请将条件格式公式中的单元格引用更改为相对引用，而不是绝对引用。因此，将“$O$19:$O$105”更改为“O19:O105”。然后插入您的行并查看它是否按照您的预期工作。顺便说一句，欢迎来到SO！ - Scott Holtzman
@scott-holtzman 谢谢！我尝试了你的建议，但不起作用。Excel会自动将其转换回$O19:$O$105，并且再次插入行会拆分范围。 - ssp
2
我现在没有时间研究这个，但我不会选择VBA。肯定有一个非 VBA 的解决方案。这也太“简单”了吧。看一下这个链接... 也许你需要更改你的格式范围...只是一个想法: http://www.excelforum.com/excel-programming-vba-macros/778585-inserting-new-rows-splits-conditional-format.html - Scott Holtzman
感谢大家抽出时间查看我的问题。 特别感谢@scott-holtzman的评论，促使我更仔细地检查并意识到了我的错误。 - ssp
17个回答
1

  1. 将条件格式应用于整列，例如"$A:$A"。删除不同的单元格引用，如"$A2: $A10"。

  2. 不要使用“插入行”，因为它会破坏条件格式。而是将新数据条目作为Excel表的最后一行之后的新行附加。如果正在使用vba，请利用它来识别最后一行。

  3. 使用“全部合并条件格式”选项，将当前列的公式和格式从最后一行复制到新行中的“粘贴特殊”中。

    last_row = Cells(Rows.Count, 1).End(xlUp).Row

    Rows(last_row).Copy

    Rows(last_row + 1).PasteSpecial xlPasteAllMergingConditionalFormats

  4. 根据需要使用vba重新排序表。

    例如：Range("A:AU").AutoFilter Field:=46, Criteria1:="TRUE"

1

我发现插入行不会导致条件格式规则的拆分。我还复制了一行并粘贴到插入的行中，这样做就可以了。如果我选择特殊粘贴并仅选择公式，则可以正常工作。

然而，我想知道是否有必要在条件格式的“适用范围”字段中使用“INDIRECT”或“OFFSET”。如果是这样，那将会是一个问题。

我不同意。一旦我插入一行，它就会破坏我的条件格式规则。复制/粘贴也会搞砸它，有时会让它变得更加复杂。 - Trashman
0
在2013年，一旦您发现您的格式规则已被拆分/复制，请为每个格式定义一个新的命名范围。然后将适用于设置为=[命名范围]。Excel将使用实际范围替换命名范围。然后删除重复的格式。
0

我找到了一个简单的过程，似乎可以始终有效地插入新行或列，并保持条件格式规则的连续性（至少在 Office 2010 中），具体步骤如下：

  1. 在要保留条件格式的行或列上方、下方、左侧或右侧进行简单的“插入”，插入所需数量的新行或列。

注意： a）你无需再进行任何操作，即可自动将条件格式应用于插入的行或列。该格式应已从相邻行或列继承下来。 b）任何边框格式也应已复制到新插入的单元格中。

  1. 选择一个与新插入行或列相邻的行、列或范围（通过单击），其中包含要复制的条件格式（以及公式和数据，如果适用）。

  2. 将鼠标悬停在所选范围的左下角或右下角，直到出现加号“+”符号（不要将其与行重新调整大小工具混淆，因为它们看起来很相似）。

  3. 单击并按住加号“+”，并拖动到要格式化的所需行、列或范围，然后释放。

注意：我创建了一个只引用一个单元格的条件格式规则： 例如）在标题为“仅当此公式为真时格式化值”的字段中，创建一个规则，如... =AND($B8="",$C8="",$D8="",$K8<>"")，其中此规则适用于范围... =$B$8:$D$121,$J$8:$M$121。

0
我正在建立一个解决方案，其他人的Excel技能水平参差不齐，所以我需要比让他们记住某种特定的复制和粘贴方式更容易、更一致的东西。
在Excel 2016中，您可以从选择的范围插入表格，然后获得使用结构化引用的好处（例如：tblTOP [Type]，用于引用名为tblTOP的表格中Type列中的数据）。
然后我在Microsoft的网站上找到了这个答案，它展示了在CF公式部分引用表格的有效方法： conditional formatting structured references。

所以，有了这个基础...

这是我正在处理的内容：

tblTop Columns

我设置了条件格式，当我在A到E之间的任何地方更改Type值时，它将把该行更改为相应的颜色。（示例：B将该行变为绿色）

这是通过使用公式=INDIRECT("tblTOP[Type]")="B"实现的。

然而，当我要添加一行时，第二行也会应用相同的格式 :(。

Broke CF between two rows

有效的CF公式

长话短说，以下公式是我想出来的，可以将我的CF规则应用于特定的行，而不影响任何被添加或删除的行：

=INDIRECT("tblTOP[@Type]")="B"

在结构引用前面添加“@”符号，可以使事情仅发生在给定的行中。很好。

现在我可以通过制表键或使用上下文菜单添加新行，等待类型选择以确定仅对该行设置的颜色。

新行已添加并清除

新行按预期工作

我还没有测试过粘贴单元格，因为此表的目的是供最终用户输入数据并根据需要添加/删除行，所以我不能确定这是否适用于粘贴行。

希望这能帮助到需要在表格中进行条件格式设置的人。

0
我正在尝试让条件格式化起作用，你提供的链接中的第一个答案对于“适用范围”字段无效。不过它可能适用于条件。 当然，如果没有其他办法，我将不得不使用VBA方法。 - ssp
VBA是我的猜测，但我很想知道是否有其他人有不需要它的方法。学习VBA确实需要一定的时间，但一旦掌握了它，你就可以按照自己的想法操纵工作表！：] - danielpiestrak
-1

我通过以下步骤让它在 Excel Mac 2011 上工作：

  • 插入新行
  • 复制上面的行（已应用条件格式）
  • 突出显示新行并选择 PASTE SPECIAL -> MERGE CONDITIONAL FORMATTING。

条件格式规则未分割并更新以包括附加行。

1
只有在省略第一步的情况下才对我有效。 - Trashman
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接