Excel 选择案例？

Question

我想为 excel 创建“案例”公式来模拟选择案例行为(带有多个参数，否则是可选的)。
如果 A1 和 A2 是 excel 单元格，那么目标是:

A1 Case:     A2 Formula:                                                                   A2 Result
5            cases({A1>5,"greather than 5"}, {A1<5, "less than 5"},{else,"equal to 5"})    equal to 5   
Hi           cases({A1="","there is nothing"},{else,A1})                                   Hi
1024         cases({5<A1<=10,10},{11<=A1<100,100},{A1>100,1000})                           1000
12           cases({A1=1 to 9, "digit"}, {A1=11|22|33|44|55|66|77|88|99, "11 multiple"})   (empty) 
60           cases({A1=1 to 49|51 to 99,"not 50"})                                         not 50

如果可以，它必须接受 excel 公式或 vba 代码，以便在处理案例之前对单元格进行操作，例如

cases({len(A1)<7, "too short"},{else,"good length"})

如果可以，它必须接受一个或多个单元格来评估，例如

如果 A2=A3=A4=A5=1 和 A1=2，A6="一个"，A7="两个"

cases(A1!=A2|A3|A4|A5, A6}, {else,A7}) will produce "two"

顺便说一句，|表示或，!= 表示不同

有什么帮助吗？

我很感激。

我能写的是这样的:

Public Function arr(ParamArray args())  'Your function, thanks
    arr = args
End Function

Public Function cases(arg, arg2)  'I don't know how to do it better
    With Application.WorksheetFunction
        cases = .Choose(.Match(True, arg, 0), arg2)
    End With
End Function

我以这种方式调用函数

=cases(arr(A1>5, A1<5, A1=5),arr( "gt 5", "lt 5", "eq 5"))

而且我无法达到目标，它只适用于第一个条件，A1>5。

我使用 for 修复了它，但我认为它不像您的建议那样优雅:

Function selectCases(cases, actions)
    For i = 1 To UBound(cases)
        If cases(i) = True Then
            selectCases = actions(i)
            Exit Function
        End If
    Next
End Function

当我调用函数时:

=selectCases(arr(A1>5, A1<5, A1=5),arr( "gt 5", "lt 5", "eq 5"))

有用。

谢谢大家。

经过一番工作，最后我得到了一个 excel 选择案例，更接近我最初想要的。

Function cases(ParamArray casesList())
    'Check all arguments in list by pairs (case, action),
    'case is 2n element
    'action is 2n+1 element
    'if 2n element is not a test or case, then it's like the "otherwise action"
    For i = 0 To UBound(casesList) Step 2
        'if case checks
        If casesList(i) = True Then
            'then take action
            cases = casesList(i + 1)
            Exit Function
        ElseIf casesList(i) <> False Then
            'when the element is not a case (a boolean value),
            'then take the element.
            'It works like else sentence
            cases = casesList(i)
            Exit Function
        End If
    Next
End Function

当 A1=5 我调用:

=cases(A1>5, "gt 5",A1<5, "lt 5","eq 5")

可以这样读:当A1大于5时，则选择“gt 5”，而当A1小于5时，则选择“lt 5”，否则选择“eq 5”。运行后，它与“eq 5”匹配

谢谢，这很令人兴奋，而且很有教育意义!

Answer 1

好吧，根本没有办法完全按照您的意愿行事。您不能在公式中使用 Excel 语法以外的任何内容，因此像“A1 = 1 到 9”这样的东西是不可能的。

你可以编写一个非常精细的 VBA 例程，它接受字符串或其他东西并解析它们，但这实际上相当于设计和实现一种完整的小语言。而且您的“代码”不能很好地与 Excel 配合使用。例如，如果您调用类似

=cases("{A1="""",""there is nothing""},{else,A1}")

(注意转义引号)，Excel 在移动或复制公式时不会更新您的 A1 引用。所以让我们放弃整个“语法”选项。

但是，事实证明，您可以通过常规 Excel 公式和一个微小的 VBA UDF 获得我认为您真正想要的大部分行为。首先是UDF:

Public Function arr(ParamArray args())
    arr = args
End Function

这让我们可以从一组参数创建一个数组。由于参数可以是表达式而不仅仅是常量，我们可以从这样的公式中调用它:

=arr(A1=42, A1=99)

并取回一个 bool 值数组。

使用那个小的 UDF，您现在可以使用常规公式来“选择案例”。它们看起来像这样:

=CHOOSE(MATCH(TRUE, arr(A1>5, A1<5, A1=5), 0), "gt 5", "lt 5", "eq 5")

发生的事情是'arr'返回一个 bool 数组，'MATCH'找到第一个TRUE的位置，'CHOOSE'返回对应的“case”。

您可以通过将整个内容包装在“IFERROR”中来模拟“else”子句:

=IFERROR(CHOOSE(MATCH(TRUE, arr(A1>5, A1<5), 0), "gt 5", "lt 5"), "eq 5")

如果这对您来说太冗长，您总是可以编写另一个 VBA UDF 将 MATCH、CHOOSE 等带入内部，并像这样调用它:

=cases(arr(A1>5, A1<5, A1=5), "gt 5", "lt 5", "eq 5")

这与您提出的语法相差不远，而且要简单得多。

编辑:

我看到你已经想出了一个更接近你真正想要的(好的)解决方案，但我想我还是会添加这个，因为我上面关于在 UDF 中引入 MATCH、CHOOSE 等的声明做到了看起来更容易，那真的是。

因此，这是一个“案例”UDF:

Public Function cases(caseCondResults, ParamArray caseValues())
    On Error GoTo EH

    Dim resOfMatch
    resOfMatch = Application.Match(True, caseCondResults, 0)

    If IsError(resOfMatch) Then
        cases = resOfMatch
    Else
        Call assign(cases, caseValues(LBound(caseValues) + resOfMatch - 1))
    End If

    Exit Function

EH:
    cases = CVErr(xlValue)
End Function

它使用一个小帮助程序，“分配”:

Public Sub assign(ByRef lhs, rhs)
    If IsObject(rhs) Then
        Set lhs = rhs
    Else
        lhs = rhs
    End If
End Sub

'assign' 例程只是更容易处理用户可以使用值或范围引用调用 UDF 的事实。因为我们希望我们的“案例”UDF 像 Excel 的“选择”一样工作，所以我们希望在必要时返回引用。

基本上，在新的“案例”UDF 中，我们通过索引案例值的参数数组来自己执行“选择”部分。我在那里打了一个错误处理程序，所以基本的东西，如案例条件结果和案例值之间的不匹配，将导致返回值#VALUE!。您可能会在实际函数中添加更多检查，例如确保条件结果是 bool 值等。

不过，我很高兴您为自己找到了更好的解决方案!这很有趣。

有关“分配”的更多信息:

针对您的评论，这里有更多关于为什么这是我的回答的一部分。 VBA 使用与分配普通值不同的语法将对象分配给变量。查看 VBA 帮助或查看此 stackoverflow 问题和其他类似问题: What does the keyword Set actually do in VBA?

这很重要，因为当您从 Excel 公式调用 VBA 函数时，除了数字、字符串、 bool 值、错误和数组之外，参数还可以是 Range 类型的对象。 (见 Can an Excel VBA UDF called from the worksheet ever be passed an instance of any Excel VBA object model class other than 'Range'?)

范围引用是您使用 Excel 语法(如 A1:Q42)描述的内容。当您将 1 作为参数传递给 Excel UDF 时，它显示为 Range 对象。如果要从 UDF 返回 Range 对象，则必须使用 VBA 'Set' 关键字显式执行此操作。如果您不使用“设置”，Excel 将取而代之的是包含在范围内的值并返回该值。大多数时候这无关紧要，但有时您需要实际范围，例如当您有一个必须评估为范围的命名公式时，因为它被用作验证列表的来源。