excel - 如何在评估函数中实现“sumifs”
问题描述
我找到了一个评估字符串的宏。它适用于大多数公式。但它不会评估我的 sumifs 公式。
WM_Eval() 的 VBA 如下所示:
Function wm_Eval(myFormula As String, ParamArray variablesAndValues() As
Variant) As Variant
Dim i As Long
'
' replace strings by values
'
For i = LBound(variablesAndValues) To UBound(variablesAndValues) Step 2
myFormula = RegExpReplaceWord(myFormula, variablesAndValues(i),
variablesAndValues(i + 1))
Next
'
' internationalisation
'
myFormula = Replace(myFormula, Application.ThousandsSeparator, "")
myFormula = Replace(myFormula, Application.DecimalSeparator, ".")
myFormula = Replace(myFormula,
Application.International(xlListSeparator), ",")
'
' return value
'
wm_Eval = Application.Evaluate(myFormula)
End Function
如果我输入 wm_Eval("1+1") 它就像一个魅力。但如果我这样做:
="sumifs(b2:b10,a2:a10,"&D2&">=2"&D2&")" where d2=" it returns #Value.
So the formula it should evaluate would be: sumifs(b2:b10,a2:a10,">=2")
我习惯于使用丹麦语 excel - 所以它可能只是我想念的非常非常简单的东西。
解决方案
我觉得你应该提供更多关于你为什么需要这个函数的信息,因为我相信有比上面的代码更好的方法来完成任务。
函数返回错误的原因有很多。主要是您的公式语法不正确。该wm_Eval()
函数需要一个公式参数,然后是成对的变量和值参数。您尚未包含RegExpReplaceWord()
函数的代码,但我想它会运行某种替换伪公式变量的值。如果没有看到代码的那部分,很难猜测语法,但原理的模型类似于:
=wm_Eval("SUMIFS(A1:A4,B1:B4,{a})","{a}",">=1")
其中 {a} 替换为 ">=1"
数据类型也是一个问题,您可以在以下语法中看到:
=wm_Eval("SUMIFS(A1:A4,B1:B4,{a})","{a}",1)
我不想太粗暴,但是这段代码的结构很差,它无法解析所有带有各种传入参数的不同公式。是的,它适用,SUM
但那是因为你没有传入任何变量值对。我会避开这个解决方案,想出一些更强大的东西。例如,如果您想收集公式的字符串版本,为什么不直接传入包含公式的范围并从那里处理呢?
我可以告诉你,我已经编写了一个例程来解析公式中的值。它既长又复杂,需要很多很多时间来开发,包括一些 WINAPI 调用和挂钩,所以如果你想继续使用当前的方法,你还有很多工作要做。
但是,我想我应该向您展示您当前的函数是如何工作的,所以我模拟了一个RegExpReplaceWord()
函数并注释掉了国际化代码:
Public Function wm_Eval(myFormula As String, ParamArray variablesAndValues() As Variant) As Variant
Dim i As Long
'
' replace strings by values
'
For i = LBound(variablesAndValues) To UBound(variablesAndValues) Step 2
myFormula = RegExpReplaceWord(myFormula, variablesAndValues(i), variablesAndValues(i + 1))
Next
'
' internationalisation
'
' myFormula = Replace(myFormula, Application.ThousandsSeparator, "")
' myFormula = Replace(myFormula, Application.DecimalSeparator, ".")
' myFormula = Replace(myFormula, Application.International(xlListSeparator), ",")
'
' return value
'
wm_Eval = Application.Evaluate(myFormula)
End Function
Private Function RegExpReplaceWord(myFormula As String, variable As Variant, value As Variant) As String
If VarType(value) = vbString Then
value = """" & value & """"
End If
RegExpReplaceWord = Replace(myFormula, variable, value)
End Function
因此,在您的工作表单元格中,您可以添加上面引用块中所示的功能。
总而言之,我不会继续使用您当前的方法,否则您将被如此多的错误所困扰,以至于您失去的3 天似乎什么都没有。