Excel 选择案例？

Question

我想为 excel 创建“cases”公式来模拟 Select case 行为（具有多个参数和其他可选参数）。 如果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代码，在采取案例之前对单元格进行操作，ig

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)

并返回一个布尔值数组。

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

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

“arr”返回一个布尔数组，“MATCH”找到第一个 TRUE 的位置，“CHOOSE”返回相应的“case”。

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

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

如果这对您来说太冗长，您可以随时编写另一个将 MATCH、CHOOSE 等引入其中的 VBA UDF，并像这样调用它：

=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

“分配”例程只是使用户可以使用值或范围引用调用 UDF 的事实变得更容易。由于我们希望“案例”UDF 像 Excel 的“CHOOSE”一样工作，因此我们希望在必要时返回引用。

基本上，在新的“cases”UDF 中，我们通过索引到 case 值的 param 数组来自己完成“选择”部分。我在那里设置了一个错误处理程序，因此基本的东西（例如 case 条件结果和 case 值之间的不匹配）将导致返回值 #VALUE!。您可能会在实际函数中添加更多检查，例如确保条件结果是布尔值等。

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

关于“分配”的更多信息：

为了回应您的评论，这里更多地说明了为什么这是我的答案的一部分。 VBA 使用与分配普通值不同的语法来将对象分配给变量。查看 VBA 帮助或查看此 stackoverflow 问题和其他类似问题： 关键字 Set 在 VBA 中实际上有什么作用？

这很重要，因为当您从 Excel 公式调用 VBA 函数时，参数除了数字、字符串、布尔值、错误之外，还可以是 Range 类型的对象，和数组。 （请参阅从工作表调用的 Excel VBA UDF 是否可以传递除“范围”之外的任何 Excel VBA 对象模型类的实例？）

范围引用是您使用 Excel 语法（如 A1:Q42）描述的内容。当您将一个作为参数传递给 Excel UDF 时，它会显示为 Range 对象。如果要从 UDF 返回 Range 对象，则必须使用 VBA 'Set' 关键字显式执行此操作。如果您不使用“设置”，Excel 将采用范围内包含的值并返回该值。大多数情况下，这并不重要，但有时您需要实际范围，例如当您有一个必须计算为范围的命名公式时，因为它用作验证列表的源。