☰

如何创建根据单元格先前的值更改单元格的规则

tag-icon • tag-icon microsoft-excel
如何创建根据单元格先前的值更改单元格的规则

我正在尝试创建一条规则，这样如果单元格值发生变化，它将根据其先前的值更改颜色。例如，如果它的先前值小于其新值，那么我希望它变为蓝色，否则如果它的值更大，我希望将其更改为绿色。如果相同，则将其更改为橙色。我想我试图将单元格与其自身进行比较，而不是与另一个单元格进行比较。这在 excel 中可行吗？

我曾尝试使用大于公式，但没有效果！

答案1

您可以使用一个老技巧来实现这一点，无需 VBA：

首先进入文件 -> 选项 -> 公式 -> 启用迭代计算。这允许“循环引用”，引用其自身单元格的公式：

迭代是重复重新计算工作表，直到满足特定的数字条件。Excel 无法自动计算引用包含公式的单元格（直接或间接）的公式。这称为循环引用。如果公式引用其自己的单元格之一，则必须确定公式应重新计算多少次。循环引用可以无限迭代。但是，您可以控制最大迭代次数和可接受的变化量。

我假设 A1 是您想要执行的操作的单元格。B1 和 C1 是辅助单元格，按以下顺序计算：

B1: =IF(A1<>C1,C1,B1)
C1: =IF(B1=C1,A1,C1)

现在，无论何时更改单元格 A1 的值，B1 中都会显示最后一个值：

----> iterations when A1 is edited

A1:     1 |     2      2 |     3      3       3
B1: =B1=1 | =C1=1  =B1=1 | =C1=1  =B1=2   =B1=2
C1: =C1=1 | =A1=1  =C1=2 | =A1=2  =C1=2   =C1=3

设置条件格式以比较 A1 和 B1 来获得你想要的结果（除了“如果相同就改成橙色”)。辅助单元格可以隐藏，也可以位于不同的工作表……没关系，只要顺序不变即可。

要在一个单元格中执行此操作，您可以使用：

B1: =IFERROR(IF(RIGHT(B1,LEN(B1)-FIND(" ",B1))<>TEXT(A1,"@"),RIGHT(B1,LEN(B1)-FIND(" ",B1))&" "&A1,B1),"0 "&A1)

为了进行比较，您需要使用：

    =(LEFT(B1,FIND(" ",B1))*1)>A1 (for new value is smaller)
    =(LEFT(B1,FIND(" ",B1))*1)<A1 (for new value is bigger)

要从长远角度实现这一点，您可以使用：

=IFERROR(IF(MID(B1,FIND(" ",B1)+1,LEN(B1)-FIND(" ",B1)-11)<>TEXT(A1,"@"),MID(B1,FIND(" ",B1)+1,LEN(B1)-FIND(" ",B1)-10)&A1&" "&TEXT(NOW()*72000,"0"),IF(AND(((NOW()*72000)-2)>(RIGHT(B1,10)*1),CELL("address")=CELL("address",A1)),A1&" "&A1&" "&TEXT(NOW()*72000,"0"),B1)),"0 "&A1&" "&TEXT(NOW()*72000,"0"))

每当 A1 发生更改时，它都会被跟踪。如果在选择 A1 时触发重新计算并且上次记录的更改超过 2 秒，它也会被跟踪。
条件格式保持不变（但现在也适用于更改为旧值）：

    =(LEFT(B1,FIND(" ",B1))*1)>A1 (for new value is smaller)
    =(LEFT(B1,FIND(" ",B1))*1)<A1 (for new value is bigger)
    =(LEFT(B1,FIND(" ",B1))*1)=A1 (for new value is same)

另一种方法可能是“共享工作簿”选项。同时在单独的工作表中跟踪更改。通过简单的查找，您可以找到特定单元格中所做的最后更改（事件将告知旧值），您可以将其用作条件格式中的参考。

答案2

Excel 不会在任何地方保存旧值，因此这不可能成为一项规则。

也许您可以编写一个宏，在更改时固定颜色，但之后，由于旧值消失了，就无法再计算颜色了，因此它必然是颜色的固定设置 - 否则，在保存/加载后，Excel 如何知道如何为单元格着色？您可能能够在宏中将旧值复制到其他地方以保留它，但不确定。

相关内容