☰

Excel 条件格式碎片

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 conditional-formatting
Excel 条件格式碎片

通常，我会创建一个具有条件格式的工作表，并设置单元格范围，以便条件格式规则仅对单元格范围应用一次，例如

Make $A$1:$A$30 red and
Make $B$1:$B$30 blue.

插入/删除多个行和/或列后，条件格式规则集变得非常分散，相同的规则在不同范围重复。例如

Make $A$1:$A$2 red 
Make $A$3:$A$4 red 
Make $A$5:$A$9 red
Make $A$10:$A$20 red 
Make $A$21:$A$30 red
...
Make $B$1:$B$2 blue 
Make $B$3:$B$4 blue
Make $B$5:$B$9 blue
Make $B$10:$B$20 blue 
Make $B$21:$B$30 blue
....

是否有一个好的方法可以防止这种情况发生，或者当规则集变得太混乱时我是否注定要手动清理规则集？

答案1

插入和删除行不会导致条件格式变得碎片化。

原因是使用标准复制/粘贴在单元格或行之间进行复制/粘贴。解决方法是始终使用粘贴值或粘贴公式。在目标上单击鼠标右键，“粘贴选项”部分将提供 123（值）和 f（公式）。不要复制/粘贴格式，因为这会导致条件被复制/粘贴，有时它们会变得支离破碎。

执行标准复制/粘贴时，它也会复制单元格的条件公式。假设您有两条规则：
1) 将 $A$1:$A$30 变为红色
2) 将 $B$1:$B$30 变为蓝色
现在选择 A10:B10 并将其复制/粘贴到 A20:B20。Excel 将从应用于这些单元格的规则中删除 A20:B20 的条件格式，并添加具有 A20:B20 格式的新规则。最终您会得到四条规则。1
) 将 =$A$20 变为红色
2) 将 =$B$20 变为蓝色
3) 将 =$A$1:$A$19,$A$21:$A$30 变为红色
4) 将 =$B$1:$B$19,$B$21:$B$30 变为蓝色
如果您只将 A10 复制/粘贴到 A20，Excel 会注意到相同的规则应用于源和目标，并且不会分割规则。当您的复制/粘贴影响两个或更多条件格式时，Excel 还不够智能，无法弄清楚如何避免碎片化。

插入和删除行不会导致碎片，因为 Excel 只是扩展或收缩覆盖插入或删除行的区域的条件规则。

有人建议使用 $Q:$Q 而不是 $Q$1:$Q$30。这没有帮助，当您复制/粘贴单元格格式时，您仍然会得到碎片，如上所述。

答案2

将条件格式应用于表格的一列时遇到了同样的问题。添加行时，我发现最好使用$A:$A或任何一列将规则应用于整个列。

在此处输入图片描述

答案3

（这是一个解决方法，所以我本来想将其作为评论，但我的声誉不够。）

不幸的是，当规则变得混乱时，你似乎注定要清理规则集。

一个简单的方法是创建一个包含所需格式但不包含数据的工作表。这可以与原始工作表位于同一工作簿中，也可以位于您保留为模板的另一个工作簿中。

当你需要清理时，请转到此工作表，右键单击全选按钮，选择格式刷，然后点击全选按钮。格式将被未受污染的版本覆盖。

答案4

手动复制/粘贴/剪切/插入单元格会导致问题，而且很难避免。

通过 VBA 宏解决的问题。

我不需要手动复制/粘贴/剪切/插入单元格，而是通过 Excel 宏来完成，它可以保留单元格范围（通过按钮激活）。

Sub addAndBtnClick()
    Set Button = ActiveSheet.Buttons(Application.Caller)
    With Button.TopLeftCell
        ColumnIndex = .Column
        RowIndex = Button.TopLeftCell.Row
    End With
    currentRowIndex = RowIndex
    Set Table = ActiveSheet.ListObjects("Table name")
    Table.ListRows.Add (currentRowIndex)
    Set currentCell = Table.DataBodyRange.Cells(currentRowIndex, Table.ListColumns("Column name").Index)
    currentCell.Value = "Cell value"
    Call setCreateButtons
End Sub

Sub removeAndBtnClick()
    Set Button = ActiveSheet.Buttons(Application.Caller)
    With Button.TopLeftCell
        ColumnIndex = .Column
        RowIndex = Button.TopLeftCell.Row
    End With
    currentRowIndex = RowIndex
    Set Table = ActiveSheet.ListObjects("Table name")
    Table.ListRows(currentRowIndex - 1).Delete
End Sub

Sub setCreateButtons()
    Set Table = ActiveSheet.ListObjects("Table name")
    ActiveSheet.Buttons.Delete
    For x = 1 To Table.Range.Rows.Count
        For y = 1 To Table.Range.Columns.Count

            If y = Table.ListColumns("Column name").Index Then
                Set cell = Table.Range.Cells(x, y)
                If cell.Text = "Some condition" Then
                    Set btn = ActiveSheet.Buttons.Add(cell.Left + cell.Width - 2 * cell.Height, cell.Top, cell.Height, cell.Height)
                    btn.Text = "-"
                    btn.OnAction = "removeAndBtnClick"
                    Set btn = ActiveSheet.Buttons.Add(cell.Left + cell.Width - cell.Height, cell.Top, cell.Height, cell.Height)
                    btn.Text = "+"
                    btn.OnAction = "addAndBtnClick"
                End If
            End If
        Next
    Next
End Sub

重置格式（实际上不需要）：

Sub setCondFormat()
    Set Table = ActiveSheet.ListObjects("Table name")
    Table.Range.FormatConditions.Delete
    With Table.ListColumns("Column name").DataBodyRange.FormatConditions _
        .Add(xlExpression, xlEqual, "=ISTLEER(A2)") 'Rule goes here
        With .Interior
            .ColorIndex = 3 'Formatting goes here
        End With
    End With
    ...
End Sub

相关内容