匹配条件后停止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屋！