Excel中的Case函数等价物

Question

我有一个有趣的挑战 - 我需要在Excel中对以下数据进行检查：

|   A  -   B  -   C  -  D   |
|------|------|------|------|
|  36  |   0  |   0  |   x  |
|   0  |  600 |  700 |   x  |
|___________________________|

请原谅我糟糕得惊人的 ASCII 艺术。所以我需要让 D 列（x）对相邻单元格进行检查，然后根据需要转换值。以下是标准：

如果 B 列大于 0，则一切正常并且我可以喝咖啡。如果不符合该要求，则需要根据表格将 A1 进行转换 - 例如，32 = 1420，并放置到 D 中。不幸的是，A 和它需要转换成的东西之间没有关系，因此创建计算公式是不可行的。

在这种情况下，一个 case 或 switch 语句会很完美，但我认为它不是 Excel 的本机函数。我也认为将一堆 =IF() 语句链接在一起有点疯狂，我在尝试了四次之后决定这是个坏主意（我的生活故事）。

- Nic

---

似乎需要使用VLOOKUP函数。 - Blorgbeard

我要求您将其作为答案，先生。这可能非常适合这个任务。 - Nic

如果您的需求比 VLOOKUP 更复杂，您可以看一下这个链接：https://dev59.com/R1PTa4cB1Zd3GeqPjXjp#4719706 - jtolle

Answer 1

听起来需要使用VLOOKUP函数！

您可以将32到1420类型的映射放在某些列中，然后使用VLOOKUP函数进行查找。

- Blorgbeard

---

我只想更新一下并让你知道，我现在非常坚定地使用它。再次感谢，这是一个很棒的解决方案。 - Nic

Answer 2

不涉及原问题（我猜已经解决了），我最近发现了一个很棒的技巧，可以使Choose函数像select case语句一样工作，而无需修改数据。只有一个注意点：在任何时候只能有一个Choose条件为真。

语法如下：

CHOOSE( 
    (1 * (CONDITION_1)) + (2 * (CONDITION_2)) + ... + (N * (CONDITION_N)),
    RESULT_1, RESULT_2, ... , RESULT_N
)

假设条件1到N中只有一个成立，其余都是0，这意味着数字值将对应适当的结果。

如果您不能100%确定所有条件是互斥的，您可能更喜欢类似以下内容：

CHOOSE(
    (1 * TEST1) + (2 * TEST2) + (4 * TEST3) + (8 * TEST4) ... (2^N * TESTN)
    OUT1, OUT2, , OUT3, , , , OUT4 , , <LOTS OF COMMAS> , OUT5
)

话虽如此，如果Excel在函数中的参数数量上有上限，你很快就会达到这个上限。

说实话，我不敢相信我花了这么多年才发现它，但我以前没有见过它，所以想把它留在这里帮助其他人。

编辑：根据@aTrusty下面的评论： 通过使用以下形式的公式，可以消除愚蠢的逗号数量（因此，choose语句将适用于最多254个情况）：

CHOOSE(
    1 + LOG(1 + (2*TEST1) + (4*TEST2) + (8*TEST3) + (16*TEST4),2), 
    OTHERWISE, RESULT1, RESULT2, RESULT3, RESULT4
)

注意 LOG 子句的第二个参数，它将其放置在底数为2的对数中，并使整个过程正常工作。

编辑：根据 David 的答案，如果你足够幸运地在 Office 2016 上工作，现在有一个真正的 switch 语句。除了阅读上的困难之外，这还意味着您不仅可以获得行为，还可以获得 switch 的效率！

- tobriand

---

我能解释一下这里出现了一个CASE OTHERWISE的功能，我们处理当没有测试为真的情况：choose(1+log(2Test1+4Test2,2),OtherwiseResult,Result1,Result2)。 - Peter Smallwood

好发现 - 没想到！ - tobriand

编辑后的公式不起作用。我不得不将其更改为CHOOSE(LOG((2TEST1)+(4TEST2)+(8TEST3),2)，RESULT1，RESULT2，RESULT3) 这样正确吗？LOG(8TEST3,2) = LOG(8,2) = 3 对于第三个位置是正确的吗？另外，我认为“否则”样式选项中的括号可能有误 - Peter 在评论中的括号不同。 - Daniel Tallentire

抱歉，丹尼尔，我的“否则”逻辑是错误的。应该是：choose(1+log(1+2Test1+4Test2+8Test3+16Test4+32*Test5,2),"否则","结果1","结果2","结果3","结果4","结果5")。我认为这次括号是正确的。 - Peter Smallwood

回答已更新，以上已经反映了 - 棒极了。应该还要澄清如果通过多个测试会发生什么情况。假设测试3和测试4是有效的。那么你会得到 LOG(8 + 16,2) = 4.5...，而 CHOOSE 处理为 4 即使你可能认为它可能会四舍五入。这可以通过 CHOOSE(1+LOG(8+16,2),1,2,3,4,5,6) 轻松验证。只有当总数超过下一个索引时，才会选择下一个索引。 - tobriand

谢谢您提供的详细信息。它有效！还有一个问题 - 如果有两个结果符合条件怎么办？目前会抛出错误。我们能否包含检查，以便取第一个匹配条件的结果？ - Bonson

Answer 3

在Excel 2016 / Office 365中，现在可用Switch函数。

_{SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])}

example:
=SWITCH(A1,0,"FALSE",-1,"TRUE","Maybe")

Microsoft -Office Support

注意：微软已经更新该页面，只记录Excel 2019的行为。最终，他们可能也会删除对2019的引用...要查看2016年页面的内容，请使用wayback机器: https://web.archive.org/web/20161010180642/https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e

- david

---

按照最受欢迎的建议创建映射似乎并不是回答问题最合适的方式，但这个答案是正确的。 - Dan

终于！还解决了我上面回答中的不足之处，即条件不会短路——这通常不是 Excel 的问题，但它应该比现在更重视！ - tobriand

@david - 看起来微软在 Excel 2016 中删除了这个功能。现在它只能在 Excel 2019 和 Office 365 中使用。 - Altered-Ego

@Altered-Ego :) 我不想对微软太苛刻，但是请不要相信微软说的关于当前版本的任何内容都适用于以前的版本。 - david

Answer 4

尝试这个；

=IF(B1>=0, B1, OFFSET($X$1, MATCH(B1, $X:$X, Z) - 1, Y)

WHERE
X = 要创建索引的列
Y = 左侧（-Y）或右侧（Y）的列数，以获取您要查找的值
Z = 0 如果是确切匹配（如果您想处理错误）

- BKimmel

---

我最终使用了VLOOKUP，但仍然点赞是因为这也是一个很棒的答案。 - Nic

Answer 5

我使用了这个解决方案来将单字符颜色代码转换为它们的描述：

=CHOOSE(FIND(H5,"GYR"),"Good","OK","Bad")

您基本上需要在数组中查找要解码的元素，然后使用 CHOOSE() 选择相关项目。它比构建 VLOOKUP() 表格更加紧凑。

- Robert

Answer 6

我了解这是对一个旧帖子的回复-

我喜欢使用If()函数结合Index()/Match():

=IF(B2>0,"x",INDEX($H$2:$I$9,MATCH(A2,$H$2:$H$9,0),2))

如果函数比较B列中的内容，如果大于0，则返回x，否则使用被Index()函数确定并由Match()选择的数组（信息表）返回对应a的值。

Index数组的绝对位置设置为$H$2:$I$9（美元符号），以便它指向的位置在复制公式时不会更改。要返回的值所在的行由Match()函数确定。Match()的附加价值是不需要一个Vlookup()所需的已排序列表来查找值。Match()可以通过值查找值：小于1、等于0、大于-1。我在绝对Match()数组$H$2:$H$9之后放了一个零来查找精确匹配。对于希望返回的Index()数组的列，输入相应的值。我输入了2，因为在我的数组中返回值在第二列。下面是我的索引数组的样子：

32   1420

36   1650

40   1790

44   1860

55   2010

在列表中搜索'a'列中的值，在我的示例中，要搜索的值位于第一列，要返回的相应值位于其右侧。查找/引用表可以在工作簿的任何选项卡中，甚至可以在另一个文件中。-Book2是文件名，Sheet2是“其他选项卡”名称。

=IF(B2>0,"x",INDEX([Book2]Sheet2!$A$1:$B$8,MATCH(A2,[Book2]Sheet2!$A$1:$A$8,0),2))

如果当b的值大于零时你不想返回x，请删除x并用“空白”/null等效或者可能放置一个0 - 不确定你想要什么。

以下是已删除x的函数开头。

=IF(B2>0,"",INDEX...

- Dan

Answer 7

如果您的Excel版本（早于Excel-2016）中没有SWITCH语句，这里提供了一个VBA实现：

Public Function SWITCH(ParamArray args() As Variant) As Variant
    Dim i As Integer
    Dim val As Variant
    Dim tmp As Variant

    If ((UBound(args) - LBound(args)) = 0) Or (((UBound(args) - LBound(args)) Mod 2 = 0)) Then
        Error 450       'Invalid arguments
    Else
        val = args(LBound(args))
        i = LBound(args) + 1
        tmp = args(UBound(args))

        While (i < UBound(args))
            If val = args(i) Then
                tmp = args(i + 1)
            End If
            i = i + 2
        Wend
    End If

    SWITCH = tmp
End Function

它的工作方式与预期完全相同，例如可以用作Google电子表格中SWITCH函数的替代品。

语法：

=SWITCH(selector; [keyN; valueN;] ...  defaultvalue)

where

• 选择器是与键进行比较的任何表达式
• key1、key2等是与选择器进行比较的表达式
• value1、value2等是在选择器等于对应键时选择的值(仅限于此)
• 如果没有键与选择器匹配，则使用默认值

例子：

=SWITCH("a";"?")                       returns "?"
=SWITCH("a";"a";"1";"?")               returns "1"
=SWITCH("x";"a";"1";"?")               returns "?"
=SWITCH("b";"a";"1";"b";TRUE;"?")      returns TRUE
=SWITCH(7;7;1;7;2;0)                   returns 2
=SWITCH("a";"a";"1")                   returns #VALUE!

要使用它，请打开Excel，转到开发工具选项卡，单击Visual Basic，在ThisWorkbook上右键单击，选择插入，然后选择模块，最后将代码复制到编辑器中。您必须另存为宏友好的Excel工作簿（xlsm）。

- gaborsch

Answer 8

我知道回答有点晚，但我认为这个短视频会对你很有帮助。

http://www.xlninja.com/2012/07/25/excel-choose-function-explained/

基本上它使用了choose函数。他在视频中讲得非常清楚，所以我会让他来做而不是打20页字。另一个他的视频解释了如何使用数据验证来填充下拉菜单，您可以从有限范围中选择。

http://www.xlninja.com/2012/08/13/excel-data-validation-using-dependent-lists/

你可以将两者结合起来，使用下拉列表中的值作为您要选择的函数的索引。尽管他没有展示如何结合它们，但我相信你可以通过他的视频找出解决方法。如果有困难，请告诉我，我会更新我的答案来向您展示。

- Francis Rodgers

Answer 9

如果您使用的是Office 2016或更高版本，或者Office 365，则有一个新功能类似于CASE函数，称为IFS。以下是Microsoft文档中该函数的描述：

IFS函数检查是否满足一个或多个条件，并返回与第一个TRUE条件对应的值。IFS可以代替多个嵌套的IF语句，并且在具有多个条件时更容易阅读。

下面是一个用法示例：

=IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")

你甚至可以指定一个默认结果：

为了指定一个默认结果，在最后的 logical_test 参数中输入 TRUE。如果没有符合条件的情况，将返回相应的值。

默认结果功能已包含在上述示例中。

您可以在 Microsoft支持文档中了解更多信息。

- David Silverthorn

Answer 10

即使这个问题看起来有些老旧，但似乎仍然很受欢迎，所以我将发布另一种解决方案，我认为非常优雅：

http://fiveminutelessons.com/learn-microsoft-excel/using-multiple-if-statements-excel

它之所以优雅，是因为它只使用了IF函数。基本上，它可以归结为：

if(条件, 从表格中选择/使用一个值，if(条件, 从表格中选择/使用另一个值...

依此类推

它的效果非常好，甚至比HLOOKUP或VLOOKUP更好。

但是...请注意 - Excel可以处理嵌套IF语句的数量是有限制的。

- Chiwda