excel 溢出 修复_修复Excel条件格式重复规则

excel 溢出 修复

Conditional formatting is a great way to highlight specific data, but did you know that it can automatically create new rules on its own? I'll show you how that happens, and an easy way to fix those conditional formatting duplicated rules.

条件格式是突出显示特定数据的一种好方法，但是您知道它可以自动自动创建新规则吗？ 我将向您展示这是如何发生的，以及解决这些条件格式重复规则的简便方法。

视频：条件格式重复规则 (Video: Conditional Formatting Duplicated Rules)

Watch this video to see how the duplicated rules are created, and an easy way to fix the problem. There are written instructions below the video.

观看此视频，了解如何创建重复的规则以及解决问题的简便方法。 视频下方有书面说明。

演示地址

If you need to do this frequently, go to my website to get a macro that removes the duplicate rules. It's designed for data in in an Excel named table.

如果您需要经常执行此操作，请访问我的网站以获取删除重复规则的宏 。 它是为Excel中名为table的数据而设计的。

简单条件格式规则 (Simple Conditional Formatting Rules)

In this example, there is a small table, with 2 simple conditional formatting rules:

在此示例中，有一个小表，其中包含2条简单的条件格式设置规则：

• In column E, highlight prices that are greater than 500
  在E列中，突出显示大于500的价格
• In columns A:E, add a blue top border if the date in column A is different from the date above
  如果A列中的日期与上述日期不同，请在A：E列中添加蓝色顶部边框

自动创建新规则 (New Rule Automatically Created)

To see how a new rule is automatically created, delete row 10 in this table. Then, go to Conditional Formatting > Manage Rules, to see the results.

要查看如何自动创建新规则，请删除此表中的第10行。 然后，转到条件格式>管理规则，以查看结果。

• The original rule has changed, and excludes the deleted row.
  原始规则已更改，并排除了已删除的行。
• There is a new rule that applies to row 10 only, and it has a #REF! error
  有一个新规则仅适用于第10行，并且有一个#REF !！ 错误

为什么会发生 (Why It Happened)

The new rule was created because the Top Border rule has a formula that refers to another row. It compares the date in the current row, to the date in the row above:

之所以创建新规则，是因为“上边界”规则具有引用另一行的公式。 它将当前行中的日期与上一行中的日期进行比较：

=$A2<>$A3

= $ A2 <> $ A3

When you delete a row, the row below that loses its reference to that row. If we had the same formula on the worksheet, you'd see a #REF! error.

当您删除一行时，下面的行将失去对该行的引用。 如果工作表上有相同的公式，您将看到一个#REF！ 错误。

The same #REF! error occurs in the conditional formatting formula, so Excel creates a new rule for that formula.

一样的#REF！ 条件格式公式中发生错误，因此Excel为该公式创建新规则。

修复条件格式重复规则 (Fix Conditional Formatting Duplicate Rules)

If you frequently delete and insert rows, you could end up with many duplicated rules. In a big workbook, that could potentially slow down your workbook's calculation speed.

如果您经常删除和插入行，则可能会产生许多重复的规则。 在大型工作簿中，这可能会减慢工作簿的计算速度。

And, you might not even know about those extra rules, unless you go into the Manage Rules dialog box for some reason.

并且，除非出于某种原因进入“管理规则”对话框，否则您甚至可能都不知道这些额外的规则。

I've written about this problem before, and there are a few ways to prevent the problem from occurring. Now that I've found an easy way to fix the problem, I don't worry about prevention – I just clean things up when necessary.

我之前已经写过有关此问题的文章 ，并且有几种方法可以防止出现此问题。 现在，我已经找到了解决问题的简便方法，我不必担心预防工作–我会在必要时进行清理。

轻松解决问题的简单步骤 (Easy Steps to Fix the Problem)

Here are the easy steps to remove the conditional formatting duplicate rules:

以下是删除条件格式重复规则的简单步骤：

• Except for the first row, select all the rows with the same conditional formatting rules
  除第一行外，选择所有具有相同条件格式规则的行
• On the Excel Ribbon's Home tab, click Conditional Formatting
  在“ Excel功能区”的“主页”选项卡上，单击“条件格式”
• Click Clear Rules, then click Clear Rules from Selected Cells
  单击清除规则，然后单击“从选定单元格清除规则”
• Select the first row, and on the Excel Ribbon's Home tab, click the Format Painter
  选择第一行，然后在Excel功能区的“主页”选项卡上，单击“格式刷”
• Drag the Format Painter over all the cells where the conditional formatting rules should be applied, including the first row
  将“格式刷”拖到应应用条件格式设置规则的所有单元格上，包括第一行

To confirm that the duplicate rules were removed, go back to the Manage Rules window. Only the two original rules should be listed.

要确认删除了重复的规则，请返回“管理规则”窗口。 仅应列出两个原始规则。

获取示例文件和宏 (Get the Sample File and Macro)

To get the sample file that I used in this example, go to the Conditional Formatting page on my Contextures website. The details are there, as well as the macro code. Scroll down a bit to see the link to download the file – it is in xlsx format, so the macro isn't in that file.

若要获取本示例中使用的示例文件，请转到Contextures网站上的“条件格式”页面 。 那里有详细信息以及宏代码。 向下滚动以查看下载文件的链接–该文件为xlsx格式，因此该宏不在该文件中。

翻译自: https://contexturesblog.com/archives/2017/03/16/fix-excel-conditional-formatting-duplicate-rules/

excel 溢出 修复