Excel 中的等效 Case 函数

发布于 2024-10-27 11:53:57 字数 555 浏览 0 评论 0原文

我有一个有趣的挑战 - 我需要在 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() 语句链接在一起有点疯狂，我这样做了大约四次，然后才认为这是一个坏主意（我的生活故事）。

原文

I have an interesting challenge - I need to run a check on the following data in Excel:

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

You'll have to excuse my wonderfully bad ASCII art. So I need the D column (x) to run a check against the adjacent cells, then convert the values if necessary. Here's the criteria:

If column B is greater than 0, everything works great and I can get coffee. If it doesn't meet that requirement, then I need to convert A1 according to a table - for example, 32 = 1420 and place into D. Unfortunately, there is no relationship between A and what it needs to convert to, so creating a calculation is out of the question.

A case or switch statement would be perfect in this scenario, but I don't think it is a native function in Excel. I also think it would be kind of crazy to chain a bunch of =IF() statements together, which I did about four times before deciding it was a bad idea (story of my life).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谈下烟灰 2024-11-03 11:53:58

如果您的 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 Spreadsheet 的 SWITCH函数。

语法：

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

其中

选择器是与键进行比较的任何表达式
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)。

If you don't have a SWITCH statement in your Excel version (pre-Excel-2016), here's a VBA implementation for it:

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

It works exactly like expected, a drop-in replacement for example for Google Spreadsheet's SWITCH function.

Syntax:

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

where

selector is any expression that is compared to keys
key1, key2, ... are expressions that are compared to the selector
value1, value2, ... are values that are selected if the selector equals to the corresponding key (only)
defaultvalue is used if no key matches the selector

Examples:

=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!

To use it, open your Excel, go to Develpment tools tab, click Visual Basic, rightclick on ThisWorkbook, choose Insert, then Module, finally copy the code into the editor. You have to save as a macro-friendly Excel workbook (xlsm).

回复收藏 0 原文

乖乖公主 2024-11-03 11:53:58

即使很旧，这似乎也是一个流行的问题，所以我将发布另一个解决方案，我认为它非常优雅

： if-statements-excel" rel="nofollow noreferrer">http:// Fiveminutelessons.com/learn-microsoft-excel/using-multiple-if-statements-excel

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

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

等等

效果很好，甚至比 HLOOKUP 或 VLOOOKUP 更好

，但是...请注意 - excel 可以处理的嵌套 if 语句的数量是有限的。

回复收藏 0 原文

贪了杯 2024-11-03 11:53:58

Microsoft 将 SWITCH、IFS 和 IFVALUES 替换为仅 CHOOSE 函数。

=CHOOSE($L$1,"index_1","Index_2","Index_3")

Microsoft replace SWITCH, IFS and IFVALUES with CHOOSE only function.

=CHOOSE($L$1,"index_1","Index_2","Index_3")

回复收藏 0 原文

爱冒险 2024-11-03 11:53:58

最近不幸再次使用Excel 2010一段时间，我非常怀念SWITCH功能。我想出了以下方法来尽量减少我的痛苦：

=CHOOSE(SUM((A1={"a";"b";"c"})*ROW(INDIRECT(1&":"&3))),1,2,3)
CTRL+SHIFT+ENTER

其中 A1 是你的病情所在（它可以是一个公式，无论如何）。好处是我们只需要提供一次条件（就像 SWITCH 一样），并且情况（在本例中：a、b、c）和结果（在本例中：1、2、3）是有序的，这使得这很容易推理。

它的工作原理如下：

Cond={"c1";"c2";...;"cn"} 返回 TRUE 或 FALSE 的 N 向量（其行为类似于 1 和 0）
ROW(INDIRECT(1&": "&n)) 返回一个由有序数字组成的 N 向量：1;2;3;...;n
两个向量的乘法将返回大量零和条件匹配的数字（位置）
SUM 只是变换这个带有零和位置的向量只是一个数字，然后可以使用 CHOOSE
如果您想添加另一个条件，只需记住增加 INDIRECT 内的最后一个数字
如果您想要 ELSE 情况，只需将其包装在 IFERROR 公式
中如果您多次提供相同的条件，公式将无法正常运行，但我想无论如何都没有人愿意这样做

Recently I unfortunately had to work with Excel 2010 again for a while and I missed the SWITCH function a lot. I came up with the following to try to minimize my pain:

=CHOOSE(SUM((A1={"a";"b";"c"})*ROW(INDIRECT(1&":"&3))),1,2,3)
CTRL+SHIFT+ENTER

where A1 is where your condition lies (it could be a formula, whatever). The good thing is that we just have to provide the condition once (just like SWITCH) and the cases (in this example: a,b,c) and results (in this example: 1,2,3) are ordered, which makes it easy to reason about.

Here is how it works:

Cond={"c1";"c2";...;"cn"} returns a N-vector of TRUE or FALSE (with behaves like 1s and 0s)
ROW(INDIRECT(1&":"&n)) returns a N-vector of ordered numbers: 1;2;3;...;n
The multiplication of both vectors will return lots of zeros and a number (position) where the condition was matched
SUM just transforms this vector with zeros and a position into just a single number, which CHOOSE then can use
If you want to add another condition, just remember to increment the last number inside INDIRECT
If you want an ELSE case, just wrap it inside an IFERROR formula
The formula will not behave properly if you provide the same condition more than once, but I guess nobody would want to do that anyway

回复收藏 0 原文

可是我不能没有你 2024-11-03 11:53:58

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

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

使用示例如下：

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

您甚至可以指定默认结果：

要指定默认结果，请为最终的逻辑测试参数输入 TRUE。如果不满足其他条件，则返回相应的值。

默认结果功能包含在上面所示的示例中。

您可以在 Microsoft 的支持文档

If your using Office 2016 or later, or Office 365, there is a new function that acts similarly to a CASE function called IFS. Here's the description of the function from Microsoft's documentation:

The IFS function checks whether one or more conditions are met, and returns a value that corresponds to the first TRUE condition. IFS can take the place of multiple nested IF statements, and is much easier to read with multiple conditions.

An example of usage follows:

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

You can even specify a default result:

To specify a default result, enter TRUE for your final logical_test argument. If none of the other conditions are met, the corresponding value will be returned.

The default result feature is included in the example shown above.

You can read more about it on Microsoft's Support Documentation

回复收藏 0 原文

野稚 2024-11-03 11:53:57

听起来像是 VLOOKUP！

你可以把你的 32 ->第1420章不服输

回复收藏 0 原文

草莓酥 2024-11-03 11:53:57

在没有参考原始问题（我怀疑这个问题早已解决）的情况下，我最近发现了一个巧妙的技巧，可以使 Choose 函数完全像 select case 语句一样工作，而无需修改数据。只有一个问题：在任何时候，您选择的条件中只能有一个为真。

语法如下：

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 语句。从阅读难度来看，这也意味着你得到的是开关的效率，而不仅仅是行为！

Without reference to the original problem (which I suspect is long since solved), I very recently discovered a neat trick that makes the Choose function work exactly like a select case statement without any need to modify data. There's only one catch: only one of your choose conditions can be true at any one time.

The syntax is as follows:

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

On the assumption that only one of the conditions 1 to N will be true, everything else is 0, meaning the numeric value will correspond to the appropriate result.

If you are not 100% certain that all conditions are mutually exclusive, you might prefer something like:

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

That said, if Excel has an upper limit on the number of arguments a function can take, you'd hit it pretty quickly.

Honestly, can't believe it's taken me years to work it out, but I haven't seen it before, so figured I'd leave it here to help others.

EDIT: Per comment below from @aTrusty:
Silly numbers of commas can be eliminated (and as a result, the choose statement would work for up to 254 cases) by using a formula of the following form:

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

Note the second argument to the LOG clause, which puts it in base 2 and makes the whole thing work.

Edit: Per David's answer, there's now an actual switch statement if you're lucky enough to be working on office 2016. Aside from difficulty in reading, this also means you get the efficiency of switch, not just the behaviour!

回复收藏 0 原文

怕倦 2024-11-03 11:53:57

Switch 功能现已在 Excel 2016 / Office 365

_{中可用
SWITCH(表达式, 值1, 结果1, [默认值或值2, 结果2],…[默认值或值3, 结果3])}

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

Microsoft -Office 支持

注意：MS 已更新该页面，仅记录 Excel 2019 的行为。最终，他们可能还会删除对 2019 年的引用...要查看该页面在 2016 年的样子，请使用 wayback machine：
https://web.archive.org/web/20161010180642/https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e< /a>

The Switch function is now available, in Excel 2016 / Office 365

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

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

Microsoft -Office Support

Note: MS has updated that page to only document the behavior of Excel 2019. Eventually, they will probably remove references to 2019 as well... To see what the page looked like in 2016, use the wayback machine:
https://web.archive.org/web/20161010180642/https://support.office.com/en-us/article/SWITCH-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e

回复收藏 0 原文

唔猫 2024-11-03 11:53:57

试试这个；

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

哪里
X = 您要索引的列
Y = 索引列左侧 (-Y) 或右侧 (Y) 的列数，以获取您要查找的值

如果完全匹配则 Z = 0（如果要处理错误）

Try this;

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

WHERE
X = The columns you are indexing into
Y = The number of columns to the left (-Y) or right (Y) of the indexed column to get the value you are looking for

Z = 0 if exact-match (if you want to handle errors)

回复收藏 0 原文

拥醉 2024-11-03 11:53:57

我使用此解决方案将单字母颜色代码转换为它们的描述：

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

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

I used this solution to convert single letter color codes into their descriptions:

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

You basically look up the element you're trying to decode in the array, then use CHOOSE() to pick the associated item. It's a little more compact than building a table for VLOOKUP().

回复收藏 0 原文

倒带 2024-11-03 11:53:57

我知道现在回答有点晚了，但我认为这个短片会对您有很大帮助。

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/

您可以将两者结合起来，并使用下拉列表中的值作为选择函数的索引。虽然他没有展示如何将它们结合起来，但我相信你可以弄清楚，因为他的视频很棒。如果您遇到问题，请告诉我，我会更新我的答案以向您展示。

回复收藏 0 原文

狼亦尘 2024-11-03 11:53:57

我知道这是对旧帖子的回应 -

我喜欢 If() 函数与 Index()/Match() 相结合：

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

if 函数比较 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，因为在我的数组中，返回值位于第二列。我的索引数组如下所示：

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

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

如果您不希望 x 在 b 的值大于零时返回，请删除 x 作为“空白”/空等效值，或者可能放一个 0 - 不确定您想要什么。

下面是删除了 x 的函数的开头。

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

I understand that this is a response to an old post-

I like the If() function combined with Index()/Match():

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

The if function compare what is in column b and if it is greater than 0, it returns x, if not it uses the array (table of information) identified by the Index() function and selected by Match() to return the value that a corresponds to.

The Index array has the absolute location set $H$2:$I$9 (the dollar signs) so that the place it points to will not change as the formula is copied. The row with the value that you want returned is identified by the Match() function. Match() has the added value of not needing a sorted list to look through that Vlookup() requires. Match() can find the value with a value: 1 less than, 0 exact, -1 greater than. I put a zero in after the absolute Match() array $H$2:$H$9 to find the exact match. For the column that value of the Index() array that one would like returned is entered. I entered a 2 because in my array the return value was in the second column. Below my index array looked like this:

The value in your 'a' column to search for in the list is in the first column in my example and the corresponding value that is to be return is to the right. The look up/reference table can be on any tab in the work book - or even in another file. -Book2 is the file name, and Sheet2 is the 'other tab' name.

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

If you do not want x return when the value of b is greater than zero delete the x for a 'blank'/null equivalent or maybe put a 0 - not sure what you would want there.

Below is beginning of the function with the x deleted.