Excel选择案例？ [英] Excel Select Case?
问题描述
我想为excel创建案例公式来模拟选择案例行为（带有多个参数，否则可选）。
如果A1和A2是excel单元格，这是目标：
A1案例：A2公式：A2结果
5个（{A1> 5，比5更伟大}}，{A1 <5，小于5}，{else，等于5}）等于5
Hi case {A1 =，没有什么}，{else，A1}）Hi
1024例（{5
60例（{A1 = 1〜49 | 51〜99，不50}）不50
如果可以的话，它必须接受excel公式或vba代码，才能在案件之前对单元进行操作，ig
个案（{len（A1）< 7，太短}，{else，good length}）
如果可以，它必须接受或更多的单元格来评估，ig如果A2 = A3 = A4 = A5 = 1，A1 = 2，则A6 =一，A7 =两
$
< b $ b
案例（A1！= A2 | A3 | A4 | A5，A6}，{else，A7}）将产生两
顺便说一句意味着或者！=表示不同
任何帮助？
我很感激。
我可以写什么这是：
公共函数arr（ParamArray args（））'你的函数，谢谢
arr = args
结束函数
公共函数案例（arg，arg2）'我不知道如何做得更好
With Application.WorksheetFunction
cases = .Choose（.Match （True，arg，0），arg2）
结束
结束函数
我以这种方式调用函数
= cases（arr（A1> 5，A1< 5，A1 = 5） arr（gt 5，lt 5，eq 5））
没有达到目标，它只适用于第一个条件，A1> 5。
我使用一个for ...修复它，但我认为它不像你的建议那样优雅：
函数selectCases（case，actions）
对于i = 1到UBound（个案）
如果cases（i）= True然后
selectCases = actions（i）
退出函数
结束如果
下一个
结束函数
当我调用函数：
= selectCases（arr（A1> A1 <5，A1 = 5），arr（gt 5，5，eq 5））
它可以工作。
感谢所有。
/ p>
功能案例（ParamArray casesList（））
'检查列表中的所有参数（case，action），
'case是2n个元素
'action是2n + 1元素
'if 2n元素不是测试或案例，那就像oth如果caseList（i）= True然后
'然后采取行动
如果caseList（i）= True然后
'然后执行
对于i = 0到UBound（casesList）步骤2
' case = casesList（i + 1）
退出函数
ElseIf casesList（i）<> False然后
'当元素不是case（一个布尔值），
'然后取该元素。
'它像其他句子一样
cases = casesList（i）
退出函数
结束如果
下一个
结束函数
当A1 = 5，我打电话：
= case（A1> 5，gt 5，A1 <5，lt 5，eq 5）
它与eq 5匹配
谢谢，这是令人兴奋的，真正的教育！
好的，根本没办法做你想要的。您不能在公式中使用除Excel语法之外的任何东西，因此像A1 = 1到9这样的东西是不可能的。
您可以<写一个非常精细的VBA例程，它采取字符串或东西并解析它们，但这真的相当于设计和实现一个完整的小语言。而你的代码不能很好的使用Excel。例如，如果你调用了一些类似
= cases（{A1 =，没有什么 }，{else，A1}）
（注意转义的引号），Excel不会更新您的A1参考移动或公式被复制。所以我们放弃整个语法选项。
然而，事实证明你可以很容易地得到我想要的很多行为常规Excel公式加上一个小VBA UDF。首先UDF：
公共函数arr（ParamArray args（））
arr = args
结束函数
这让我们从一组参数创建一个数组。由于参数可以是表达式而不是常量，我们可以从这样的公式中调用它：
= arr（A1 = 42，A1 = 99）
并返回一个布尔值数组。
使用该小型UDF，您现在可以使用常规公式来选择案例。他们会像这样：
= CHOOSE（MATCH（TRUE，arr（A1> 5，A1< 5，A1 = 5 ），0），gt 5，5，eq 5）
继续是'arr'返回一个布尔数组，'MATCH'找到第一个TRUE的位置，'CHOOSE'返回相应的case。
你可以通过将整个东西包装在IFERROR中来模拟else子句：
= IFERROR（CHOOSE（MATCH arr（A1> 5，A1 <5），0），gt 5，5），eq 5）
如果这对你来说太冗长了，你可以随时写另外一个VBA UDF，这样会使MATCH，CHOOSE等在里面被调用，如下所示：
=案例（arr（A1> 5，A1 <5，A1 = 5），gt 5，lt 5，eq 5）
与您提出的语法相距不远，而且简单得多。
编辑：
我看到你已经提出了一个（好的）解决方案更接近你真正想要的，但是我以为我会补充一下，因为我上面关于把MATCH，CHOOSE等等内容写在UDF里面的说法让它看起来更容易，真的是这样。
所以，这里是一个'case'UDF：
公共函数案例（caseCondResults，ParamArray caseValues（ ））
错误GoTo EH
Dim resOfMatch
resOfMatch = Application.Match（True，caseCondResults，0）
如果IsError（resOfMatch）然后
cases = resOfMatch
Else
调用assign（cases，caseValues（LBound（caseValues）+ resOfMatch - 1））
如果
退出函数
EH：
cases = CVErr（xlValue）
结束函数
它使用一个小帮助程序'assign'：
Public Sub assign（ByRef lhs，rhs）
如果IsObject（rhs）然后
设置lhs = rhs
Else
lhs = rhs
End If
End Sub
分配例程使得更容易处理用户可以使用值或范围引用来调用UDF的事实。由于我们希望我们的UDF可以像Excel的CHOOSE一样工作，我们希望在必要时返回参考。
基本上，在新的 UDF，我们通过索引到case值的param数组来做选择部分。我打了一个错误处理程序，所以基本的东西，如案例条件结果和案例值之间的不匹配将导致返回值为#VALUE！您可能会在实际功能中添加更多检查，例如确保条件结果为布尔等。
我很高兴您为自己达成更好的解决方案，虽然！这是有趣的。
更多关于'assign'：
为了回应您的评论，关于为什么这是我的答案的一部分。 VBA使用不同的语法将对象分配给变量，而不是分配一个普通值。看看VBA的帮助或看到这个stackoverflow问题和其他人喜欢它：关键字Set实际上在VBA中做了什么？
这很重要，因为当您从Excel公式调用VBA函数时，参数可以是对象的范围，除了数字，字符串，布尔值，错误和数组。 （参见可以从工作表调用的Excel VBA UDF是否可以传递除Range之外的任何Excel VBA对象模型类的实例？）
范围引用是使用Excel语法描述的，如A1：Q42。当您将一个传递给Excel UDF作为参数时，它将显示为Range对象。如果要从UDF返回Range对象，则必须使用VBASet关键字显式地执行该操作。如果您不使用'Set'，Excel将会替换包含在Range范围内的值并返回。大多数情况下，这并不重要，但是有时您需要实际的范围，就像当您有一个命名公式必须评估一个范围，因为它被用作验证列表的来源。
i want to create the "cases" formula for excel to simulate Select case behavior (with multiple arguments and else optional). If A1 and A2 are excel cells, this is the goal:
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
If it could, It must accept excel formulas or vba code, to make an operation over the cell before take a case, i.g.
cases({len(A1)<7, "too short"},{else,"good length"})
If it could, it must accept to or more cells to evaluate, i.g.
if A2=A3=A4=A5=1 and A1=2, A6="one", A7="two"
cases(A1!=A2|A3|A4|A5, A6}, {else,A7}) will produce "two"
By the way, | means or, != means different
Any help?
I'm grateful.
What I could write was this:
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
I call the function in this way
=cases(arr(A1>5, A1<5, A1=5),arr( "gt 5", "lt 5", "eq 5"))
And i can't get the goal, it just works for the first condition, A1>5.
I fixed it using a for, but i think it's not elegant like your suggestion:
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
When i call the function:
=selectCases(arr(A1>5, A1<5, A1=5),arr( "gt 5", "lt 5", "eq 5"))
It works.
Thanks for all.
After work a little, finally i get a excel select case, closer what i want at first.
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
When A1=5 and I call:
=cases(A1>5, "gt 5",A1<5, "lt 5","eq 5")
It match with "eq 5"
Thank you, it was exciting and truly educative!
O.K., there's no way at all to do exactly what you want. You can't use anything other than Excel syntax within a formula, so stuff like 'A1 = 1 to 9' is just impossible.
You could write a pretty elaborate VBA routine that took strings or something and parsed them, but that really amounts to designing and implementing a complete little language. And your "code" wouldn't play well with Excel. For example, if you called something like
=cases("{A1="""",""there is nothing""},{else,A1}")
(note the escaped quotes), Excel wouldn't update your A1 reference when it moved or the formula got copied. So let's discard the whole "syntax" option.
However, it turns out you can get much of the behavior I think you actually want with regular Excel formulas plus one tiny VBA UDF. First the UDF:
Public Function arr(ParamArray args())
arr = args
End Function
This lets us create an array from a set of arguments. Since the arguments can be expressions instead of just constants, we can call it from a formula like this:
=arr(A1=42, A1=99)
and get back an array of boolean values.
With that small UDF, you can now use regular formulas to "select cases". They would look like this:
=CHOOSE(MATCH(TRUE, arr(A1>5, A1<5, A1=5), 0), "gt 5", "lt 5", "eq 5")
What's going on is that 'arr' returns a boolean array, 'MATCH' finds the position of the first TRUE, and 'CHOOSE' returns the corresponding "case".
You can emulate an "else" clause by wrapping the whole thing in 'IFERROR':
=IFERROR(CHOOSE(MATCH(TRUE, arr(A1>5, A1<5), 0), "gt 5", "lt 5"), "eq 5")
If that is too verbose for you, you can always write another VBA UDF that would bring the MATCH, CHOOSE, etc. inside, and call it like this:
=cases(arr(A1>5, A1<5, A1=5), "gt 5", "lt 5", "eq 5")
That's not far off from your proposed syntax, and much, much simpler.
EDIT:
I see you've already come up with a (good) solution that is closer to what you really want, but I thought I'd add this anyway, since my statement above about bringing MATCH, CHOOSE, etc. inside the UDF made it look easier thatn it really is.
So, here is a 'cases' 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
It uses a little helper routine, 'assign':
Public Sub assign(ByRef lhs, rhs)
If IsObject(rhs) Then
Set lhs = rhs
Else
lhs = rhs
End If
End Sub
The 'assign' routine just makes it easier to deal with the fact that users can call UDFs with either values or range references. Since we want our 'cases' UDF to work like Excel's 'CHOOSE', we'd like to return back references when necessary.
Basically, within the new 'cases' UDF, we do the "choose" part ourselves by indexing into the param array of case values. I slapped an error handler on there so basic stuff like a mismatch between case condition results and case values will result in a return value of #VALUE!. You would probably add more checks in a real function, like making sure the condition results were booleans, etc.
I'm glad you reached an even better solution for yourself, though! This has been interesting.
MORE ABOUT 'assign':
In response to your comment, here is more about why that is part of my answer. VBA uses a different syntax for assigning an object to a variable than it does for assigning a plain value. Look at the VBA help or see this stackoverflow question and others like it: What does the keyword Set actually do in VBA?
This matters because, when you call a VBA function from an Excel formula, the parameters can be objects of type Range, in addition to numbers, strings, booleans, errors, and arrays. (See Can an Excel VBA UDF called from the worksheet ever be passed an instance of any Excel VBA object model class other than 'Range'?)
Range references are what you describe using Excel syntax like A1:Q42. When you pass one to an Excel UDF as a parameter, it shows up as a Range object. If you want to return a Range object from the UDF, you have to do it explicitly with the VBA 'Set' keyword. If you don't use 'Set', Excel will instead take the value contained within the Range and return that. Most of the time this doesn't matter, but sometimes you want the actual range, like when you've got a named formula that must evaluate to a range because it's used as the source for a validation list.
这篇关于Excel选择案例？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
