☰

如何防止条件格式公式和范围自动改变？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 conditional-formatting
如何防止条件格式公式和范围自动改变？

我发现，当您在电子表格中复制、删除或移动数据时，条件格式公式和范围会自动调整。虽然这是一个好主意，但它往往会以一些相当奇怪的方式破坏我的东西。

为了避免这种情况，我尝试编写适用于全部的电子表格并键入列标题来突出显示我想要检查的数据。

示例：=AND(A$1="Check This Column For Blanks),ISBLANK(A1))应用于=$1:$1048576

然而，即使有规则明确地应用于整个工作表，当我在工作表中工作时，它仍然会自动调整（并且以奇怪的方式中断）。

我该如何避免这种情况？

答案1

当我需要一个在以下情况下不会改变的范围时任何包括移动、插入和删除单元格等情况，我使用了命名范围和INDIRECT功能。

例如，如果我想要一个范围总是应用于单元格A1:A50，我通过名称管理器定义了一个命名范围：

添加命名范围

在名称管理器中，添加一个新范围（单击新建），然后在引用至：字段中，使用 INDIRECT 函数指定所需的单元格范围，例如=INDIRECT("A1:A50")或=INDIRECT("Sheet!A1:A50")。因为范围在技术上只是一个文本参数，所以无论重新排列多少单元格都不会导致 Excel 更新它。

此外，这至少在 Excel 2010 和 Excel 2013 中有效。虽然我的屏幕截图来自 2013 年，但我过去曾在 2010 年使用过这种精确的技术。

注意事项

  1. 请记住，这种不变性也可能给您带来麻烦。例如，如果您更改工作表的名称，命名范围将会中断。

  2. 我注意到，在大量单元格上使用此策略时，性能会略有下降。我在工作中使用的模型将此技术用于跨越数千个不同单元格范围的命名范围，当我更新这些范围内的单元格时，Excel 感觉有点迟缓。这可能是我的想象，也可能是 Excel 正在对 INDIRECT 进行额外的函数调用。

答案2

我发现规则很容易被打破，但你可以尝试一些似乎没有违反任何规则的事情。

您可以更改单元格内的文本。如果您需要添加一行，请在表格末尾添加数据并重新排序。如果您需要删除一行，只需删除文本/数字，然后重新排序表格。

当我将条件格式应用于列时，这种方法对我来说很有效，我通常会为整个列设置格式，例如 $F:$F。如果您要格式化部分范围，这种方法仍然有效，只需确保在添加/删除和重新排序完成后，您想要格式化的所有数据仍在原始范围参数范围内。

这对我来说也是一个巨大的挫败。

我希望这有帮助。

答案3

我发现使用INDIRECT函数和ROW条件格式规则中的函数可以消除 Excel 创建新规则和更改范围的问题。

例如，我想在支票簿登记电子表格中当月份从一行变为下一行时在行之间添加一条线。因此，我在 CF 规则中的公式是：

=MONTH(INDIRECT("C"&ROW()))<>MONTH(INDIRECT("C"&ROW()-1))

我的电子表格中的 C 列包含日期。我不需要对范围做任何特殊操作（不需要定义范围名称等）。

因此，在原始海报的示例中，CF 规则中的“A1”或“A$1”应该使用：

INDIRECT("A"&ROW())

答案4

我遇到过非常类似的问题。我制作了一些宏来添加行，复制公式，然后调整列和行的大小以设置工作表的格式。我发现这个问题在两种情况下都会发生。

1) 当“适用于”范围内的内容被剪切/粘贴到该范围之外时。

2）当“应用于”内有合并单元格时，任意行或列都会进行调整。

在合并单元格问题期间，Excel 似乎必须取消合并所有内容，重新计算其条件应用，调整所有单元格（添加或删除行或其他操作），然后重新合并它们。我们看不到它，但这似乎是它的应用方式。

我认为这可能有助于我们找到解决这个问题的办法。

-T

相关内容