Excel - 条件格式 - 插入行-Java 学习之路

在'Applies To'中使用偏移或间接似乎不起作用 . 插入行后是否还有其他方法可以阻止条件格式化中断

我有一个范围的条件格式，例如$Ø$ 19：$Ø$ 105条件是'如果单元格值> 10'，则使用红色格式化 .

问题是 - 当我在Excel中插入一行时，这个格式化范围分裂，我得到2个格式规则 . 对于例如如果我在第20行插入一个新行，那么2个规则的范围分别为$ O $ 19，$ O $ 21：$ O $ 105和$ O $ 20 .

通常对于如上所述的条件，如果规则被分成多个范围，则可能无关紧要 . 但对于像“突出显示前10名”这样的情况，它会导致不良后果 .

我没有太多运气尝试了以下内容：

尝试使用间接 - 但excel似乎解决了公式并保存了格式规则，因此无法按预期使用插入
尝试使用偏移 - 这里再次excel解析与上面相同的范围 .

任何人都知道如何编写一个不会因行插入而中断的条件格式？

[编辑]我意识到插入行不会导致条件格式规则的拆分 . 我还复制一行并粘贴插入的行，这样做 . 如果我选择特殊粘贴并仅选择配方，它的工作正常 .

16 回答

0
这是条件格式的一般问题 . 如果您插入行或移动东西，Excel会假定您希望条件格式随单元格移动，并且您希望插入具有原始格式的单元格（或根本不插入） .

因此，它会尽力根据更改分割格式化范围 . 不幸的是“它最好”并不是很好 . 在幸运的情况下，您的格式规则会在没有您注意的情况下重复;在不幸的情况下，他们会破坏部分或全部应用范围 .

如果使用ListObjects（a.k.a . "Excel tables"），这尤其是一个问题 . 插入一些行，重新排序，拖动一些值，下次查看条件格式列表时，您有几十到几百个重复规则 . （例如：http://blog.contextures.com/archives/2012/06/21/excel-2010-conditional-formatting-nightmare/）

根据我的经验，解决混乱的最快方法是删除所有规则并重新创建它们（或不重建） .

一些旁注：
- apply-to范围始终是绝对的 . 没有办法解决这个问题 .
- 更糟糕的是，条件格式被视为易失性公式，这意味着它们会在很多场合重新计算（打开另一个文件，滚动等） . 如果您没有注意到拆分，他们可能会在一段时间后显着减慢整个应用程序的速度 .
- 如果你选择VBA，你可能想要使用Worksheet_Calculate事件，至少如果你的公式引用其他工作表（注意名字！）
回复于 2024-04-20T12:58:36+08:00
0

我知道这是一个老线程，但这是另一个非常简单且效果很好的解决方案 .

只需根据需要插入新的行或列 . 然后选择并复制具有正确条件格式的行/列 . 过去特殊到您刚刚创建的新行/列，并选择“所有合并条件格式”选项 . 现在应该自动更新条件格式规则 .

快乐的Excel-ing =）

回复于 2024-04-20T12:58:36+08:00
1

我在尝试创建报告时遇到了这个问题 - 一旦完成它们就不需要改变了，但是当我制作它们时，我不断添加新行，每个新行都会破坏条件格式 .

这绝不是一个好的解决方案，但它是我能找到的最好的，而不是诉诸于VBA - 这是：

a）使条件格式规则一次应用于整列或更多列

例如，不是在C2和C17上设置条件格式，而是添加一个额外的列，并在第2行和第17行中写入“this one”，然后将整个列C的格式设置为“如果另一列说'this一个'然后应用这种格式“

b）将Applies To更改为$ C $ 1：$ C $ 2 .

c）进行更改并插入行和内容

d）然后返回并将Applies To更改为$ C：$ C.

这样，当您更改内容并添加内容时，条件格式不存在，但之后您将其全部放回原处 .

如果在以后需要添加更多行，首先将它从$ C：$ C更改为$ C $ 1：$ C $ 2，然后进行更改，然后将其重新置于$ C：$ C . 这样你就不必从头开始完全重写所有的格式规则，就像你做我以前做过的那样只删除所有格式规则，诅咒，然后重新开始;）

显然，如果您计划在第1行或第2行的顶部插入行，那将无效，但您可以始终将其设置为您知道不会更改的其他行 .

回复于 2024-04-20T12:58:36+08:00
1
虽然这是一个相当古老的主题，但我的Excel工作表在插入新行时也会遇到重复条件格式的问题 .

我能够解决它 . 让我与其他人分享，也可能有所帮助 .

在我的例子中，我的所有条件格式规则都应用于整个表 . 我意识到插入新行时只复制某些规则 . 这些规则是基于公式的比较不同行中的值 .

就我而言，我想在两个相邻行的值不同时渲染水平边框，例如：
```
=$A2 <> $A1
```
如果我使用OFFSET来引用前一行，则一切正确，没有重复的条件格式规则 .
```
=$A2<>OFFSET($A2; -1; 0)
```
我实际上将这些条件格式化公式放入隐藏列，但结果应该相同 .
回复于 2024-04-20T12:58:36+08:00
7

我意识到插入行不会导致条件格式规则的拆分 . 我还复制一行并粘贴插入的行，这样做 . 如果我选择特殊粘贴并仅选择配方，它的工作正常 .

但是，我想知道是否需要在“适用于”条件格式的“适用于”字段中使用“间接”或“偏移” . 如果是这样，那将是一个问题 .

回复于 2024-04-20T12:58:36+08:00
-1

我意识到这是一个旧帖子，但我遇到了同样的问题，并且已经弄清楚如何不获得拆分条件格式规则 .

在我的Excel 2010电子表格中，我在B列中输入日期 . 有时我会在日期条目中添加日期，这就是我想要有条件地格式化它们的原因 . 最初，我选择了一个范围（B2：B1960），因此我在条件格式规则中的公式将是“= B2：B1960> TODAY（）” .

好吧，直到我在现有行之间插入新行之前一切正常 . 规则将像OP描述的那样分裂 . 我碰巧看了几个不同的网站，发现了一个微软Office网站，它指出了我的答案 . 它提到要突出显示要格式化的范围，但将公式更改为“= B2> TODAY（）” .

自从更改公式后，我现在可以在现有行之间插入新行，而不像以前那样获取拆分规则 . 这是该网页的链接 . http://office.microsoft.com/en-us/excel-help/use-a-formula-to-apply-conditional-formatting-HA102809768.aspx

回复于 2024-04-20T12:58:36+08:00
1

对我有用的是，当你插入一行时，不要从另一行复制格式 . 只有copy-special粘贴公式 . 然后条件格式不会被拆分 .

回复于 2024-04-20T12:58:36+08:00
7
我对这个问题的解决方案是清除源副本的格式 . 脚步：
- 将源复制到剪贴板
- 打开新的Excel文档
- 选择性粘贴，选择公式（复制值和公式并省略格式）
- 将此复制到剪贴板
- 现在您可以在使用它之前粘贴回源，或者使用条件格式将空行插入到工作表中（注意只插入行不会更改条件格式规则的范围）并将剪贴板内容粘贴到新行中 .
这对我使用MS Excel 2016很有用
回复于 2024-04-20T12:58:36+08:00

我同意以前发布的内容;复制和粘贴值（或粘贴公式）将完全不分割条件格式 .

我有点懒惰 . 而且我不希望那些使用我的电子表格的人必须这样做 . 我也不相信他们会记得复制和粘贴值 . :(

我不知道这个解决方案是否适合您的需求，但我每次打开工作簿时都会删除所有条件格式并重新应用正确的条件格式 .

因为每次打开工作簿时都会运行此宏，所以用户无需更改复制和粘贴的方式 . 他们不需要知道宏就在那里 . 他们不需要手动运行宏;它是自动的 . 我觉得这可以创造更好的用户体验 .

请记住，此代码需要复制并粘贴到“此工作簿”模块中;不是常规模块 .

Private Sub Workbook_Open()
'This will delete all conditional formatting and reapply the conditional formatting properly.
'After copying and pasting the conditional formatting get split into two or more conditional formattings. After a few
'weeks there are so many conditional formattings that Excel crashes and has to recover.

Dim ws As Worksheet, starting_ws As Worksheet


Set starting_ws = ActiveSheet   'remember which worksheet is active in the beginning
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "InvErr" Then
        ws.Activate
        Cells.FormatConditions.Delete
        ''Every Other Row Tan
        Range("A4:M203").FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(ROW(A4))"
        Range("A4:M203").FormatConditions(Range("A4:M203").FormatConditions.Count).SetFirstPriority
        Range("A4:M203").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        Range("A4:M203").FormatConditions(1).Interior.ThemeColor = xlThemeColorDark2
        Range("A4:M203").FormatConditions(1).Interior.TintAndShade = 0
        Range("A4:M203").FormatConditions(1).StopIfTrue = False

        ''Highlight Duplicates Red
        Columns("B").FormatConditions.AddUniqueValues
        Columns("B").FormatConditions(Columns("B").FormatConditions.Count).SetFirstPriority
        Columns("B").FormatConditions(1).DupeUnique = xlDuplicate
        Columns("B").FormatConditions(1).Font.Color = -16383844
        Columns("B").FormatConditions(1).Font.TintAndShade = 0
        Columns("B").FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        Columns("B").FormatConditions(1).Interior.Color = 13551615
        Columns("B").FormatConditions(1).Interior.TintAndShade = 0
        Columns("B").FormatConditions(1).StopIfTrue = False
    End If
Next

starting_ws.Activate   'activate the worksheet that was originally active
Application.ScreenUpdating = True

End Sub

回复于 2024-04-20T12:58:36+08:00

3

这是一个类似的线程，可能会让你正确的轨道：

How to use the Worksheet_Change event for Conditional Formatting?

它概述了R1C1样式格式的变通方法，这些格式可能不受插入（未经测试）以及我在评论中提到的VBA方法的影响 .

回复于 2024-04-20T12:58:36+08:00
0
我发现了一个简单的过程，似乎一致地用于插入新的行或列，并保留条件格式的连续性规则（至少在Office 2010中），如下：
- 在包含要保留的条件格式的行或列的上方，下方或左侧或右侧执行所需数量的新行或列的简单"Insert" .
注意a）条件格式自动应用于插入的行或列，而无需进一步操作 . 格式应该是从相邻的行或列继承的 . b）任何边框格式也应该被复制到新插入的单元格中 .
- 选择与新插入的行相邻的行，列或范围（通过单击它），其中包含要复制的条件格式（以及公式和数据，如果适用） .
- 将鼠标悬停在所选范围的左下角或右下角，直到看到加号“”符号出现（不要将其与行重新调整大小的小工具混淆，因为它们看起来相似） .
- 左键单击并按住“”，然后拖动要格式化的所需行，列或范围，然后释放 .
注意：我创建条件格式规则，仅引用一个单元格：示例）在 Headers 为“格式化此公式为真的值：”的字段中，创建一个规则，如... = AND（$ B8 =“”，$ C8 =“ “，$ D8 =”“，$ K8 <>”“），此规则适用于说范围... = $ B $ 8：$ D $ 121，$ J $ 8：$ M $ 121 .
回复于 2024-04-20T12:58:36+08:00
1

你要做的是1）插入一个新行2）复制你想要克隆的行3）粘贴特殊的“合并条件格式”

如果您希望其他人共享您创建的工作簿，则不直观且需要大量的用户培训

回复于 2024-04-20T12:58:36+08:00
0

在2013年，一旦发现您的格式规则已被拆分/复制，请为每种格式定义一个新的命名范围 . 然后将apply设置为= [Named Range] . Excel将使用实际范围替换指定范围 . 然后删除重复的格式 .

回复于 2024-04-20T12:58:36+08:00
0

我正在构建一个解决方案，其他具有混合Excel技能水平的人将会发挥作用，因此我需要更容易和更一致的东西，而不是让他们记住以某种方式复制和粘贴 .

在Excel 2016中，您可以插入所选范围中的表，然后使用结构化引用（例如：tblTOP [Type]）来引用名为tblTOP的表的Type列中的数据 .

然后我在微软的网站上找到了这个答案，它显示了在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”

在结构化引用前面添加“@”可以使事情发生在给定的行中 . 尼斯 .

所以现在我可以通过选项卡或使用上下文菜单添加新行，它等待类型选择以仅确定该行的颜色 .

New Row added clean

New Row working as expected

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

希望这可以帮助某人在表格中使用条件格式 .

回复于 2024-04-20T12:58:36+08:00

这对我来说运作得很好......

Sub ConditionalFormattingRefresh()
'
' ConditionalFormattingRefresh Macro
'

'Generales
Dim sh As Worksheet
Dim tbl As ListObject
Dim selectedCell As Range
Set sh = ActiveSheet
Set tbl = Range("Plan").ListObject
Set selectedCell = ActiveCell

'Rango a copiar
Dim copyRow As Range
Set copyRow = tbl.ListRows(1).Range

'Rango a restaurar
Dim startCell As Range
Dim finalCell As Range
Dim refreshRange As Range
Set startCell = tbl.DataBodyRange.Cells(2, 1)
Set finalCell = tbl.DataBodyRange.Cells(tbl.ListRows.Count, tbl.ListColumns.Count)
Set refreshRange = Range(startCell.Address, finalCell)

'Ocultar procesamiento
Application.ScreenUpdating = False
Application.EnableEvents = False

'Borrar formato corrupto
refreshRange.FormatConditions.Delete

'Copiar
copyRow.Copy
'Pegar formato
tbl.DataBodyRange.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Retornar a la normalidad
selectedCell.Select
    Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

回复于 2024-04-20T12:58:36+08:00

0
我通过以下步骤在Excel Mac 2011上工作
- 插入新行
- 复制它上面的那个（已经应用了条件格式）
- 突出显示新行和PASTE SPECIAL - > MERGE CONDITIONAL FORMATTING .
CF规则保持未拆分并更新为包含额外的行 .
回复于 2024-04-20T12:58:36+08:00

Excel - 条件格式 - 插入行

16 回答

CF公式有效

相关问题