条件匹配后停止 Excel 更新公式 [英] Stop Excel from updating formula after a criteria is matched

问题描述

有没有办法在匹配某个条件后阻止公式更新?

Is there a way to stop a formula from updating after a certain criteria is matched?

例如:

A1 = 1
B1 = '=A1*2'

假设这是当前的.明天数据会变

Lets say this is the current one. Tomorrow data will change

A1 = 2
B1 = '=A1*2'

我需要能够将单元格 B1 的值固定为 2 (1*2)，而不是将其更新为 4 (2*2).触发器应该是日期.

I need to be able to fix the value of cell B1 at 2 (1*2) and not have it update to 4 (2*2). The trigger should be the date.

A1中的值会动态切换；我无法阻止，我只需要能够在日期匹配后阻止其他单元格更新.

The values in A1 will switch dynamically; I can't stop that, I just need the ability to stop other cells from updating after a date is matched.

推荐答案

VBA 答案.即使禁用循环引用，这个也能工作(尽管它不太灵活).它定义了两个工作表函数，它们可以根据条件有选择地计算单元格中的公式或冻结它们:

A VBA answer. This one works even with circular references disabled (though it is less flexible). It defines two worksheet functions which can selectively either evaluate formulas in a cell or freeze them, depending on the condition:

Function EvaluateIf(expression As String, condition As Boolean) As Variant
    Application.Volatile
    Dim myText As String
    Dim myVal As Variant
    If condition Then
        myVal = Application.Evaluate(expression)
    Else
        myText = Application.Caller.Text
        If IsNumeric(myText) Then
            myVal = Val(myText)
        Else
            myVal = myText
        End If
    End If
    EvaluateIf = myVal
End Function


Function FreezeAfter(expression As String, deadline As Date) As Variant
    Application.Volatile
    Dim myText As String
    Dim myVal As Variant
    If Now > deadline Then
        myText = Application.Caller.Text
        If IsNumeric(myText) Then
            myVal = Val(myText)
        Else
            myVal = myText
        End If
    Else
        myVal = Application.Evaluate(expression)
    End If
    FreezeAfter = myVal
End Function

来说明它们的用途.如果在 B1 中输入 =EvaluateIf("2*A1",C1) 然后当 C1 包含 =True() B1 更新为 A1 但如果 C1 有 =False() 那么 B1 保持冻结.对于第二个函数，如果在 B2 中输入 =FreezeAfter("A1*2",C2) 并且如果在 C2 中输入类似 6/25/2015 1:00:00 PM 的内容，则 B2 中的公式将更新为 A1 之前到下午 1:00，但之后将保持冻结状态.

To illustrate their use. If in B1 you enter =EvaluateIf("2*A1",C1) then when C1 contains =True() B1 updates with A1 but if C1 has =False() then B1 stays frozen. For the second function, if in B2 you enter =FreezeAfter("A1*2",C2) and if in C2 you have something like 6/25/2015 1:00:00 PM then the formula in B2 will update with A1 prior to 1:00 PM but will remain frozen afterwards.

在这两种方法(循环与 VBA)中，我怀疑非 VBA 可能更有效，也可能更可靠(我还没有测试过具有多种功能的 VBA 方法).另一方面——启用循环引用可能会导致问题(默认情况下不会无故关闭).

Of the two approaches (the circular vs. VBA) I suspect that the non-VBA is probably more efficient and possibly more reliable (I haven't tested the VBA approach with a wide variety of functions). On the other hand -- enabling circular references could potentially cause problems (it isn't turned off by default for no reason).

这篇关于条件匹配后停止 Excel 更新公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！