![Excel 条件格式碎片](https://linux22.com/image/1364205/Excel%20%E6%9D%A1%E4%BB%B6%E6%A0%BC%E5%BC%8F%E7%A2%8E%E7%89%87.png)
通常,我会创建一个具有条件格式的工作表,并设置单元格范围,以便条件格式规则仅对单元格范围应用一次,例如
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