blocks|key|949190|text|试试这个；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|949191|=IF(B1>=0,+B1,+OFFSET($X$1,+MATCH(B1,+$X:$X,+Z)+-+1,+Y)|code-block|syntax|javascript|949192|哪里|949193|X=要索引到的列|949194|Y=索引列左侧(-Y)或右侧(Y)的列数，以获取要查找的值|949195|如果精确匹配，则z=0(如果要处理错误)|949196|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Try this; 

<pre><code>=IF(B1&gt;=0, B1, OFFSET($X$1, MATCH(B1, $X:$X, Z) - 1, Y)
</code></pre>

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)

blocks|key|949234|text|听起来像是VLOOKUP的工作！|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|949235|您可以将32个VLOOKUP+1420类型映射放在几个列中，然后使用->函数执行查找。|949236|entityMap|0|LINK|mutability|MUTABLE|url|https://support.office.com/en-ie/article/VLOOKUP-function-adceda66-30de-4f26-923b-7257939faa65^0|5|7|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Sounds like a job for <a href="https://support.office.com/en-ie/article/VLOOKUP-function-adceda66-30de-4f26-923b-7257939faa65" rel="noreferrer">VLOOKUP</a>!

You can put your 32 -> 1420 type mappings in a couple of columns somewhere, then use the VLOOKUP function to perform the lookup.

blocks|key|733297|text|我知道现在回答有点晚，但我认为这段简短的视频会对你有很大帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|733298|http://www.xlninja.com/2012/07/25/excel-choose-function-explained/|offset|length|733299|从本质上讲，它使用了choose函数。他在视频中很好地解释了这一点，所以我会让它来做，而不是键入20页。|733300|他的另一个视频解释了如何使用数据验证来填充您可以从有限范围内选择的下拉列表。|733301|http://www.xlninja.com/2012/08/13/excel-data-validation-using-dependent-lists/|733302|您可以将这两者结合起来，并使用下拉列表中的值作为choose函数的索引。虽然他没有展示如何将它们结合起来，但我相信你可以弄清楚，因为他的视频很好。如果你有问题，请告诉我，我会更新我的答案给你看。|733303|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|1U|0|0|0|0|0|26|1|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@$D|13|E|14|1|15]]|A|$]]|$1|L|3|M|5|6|7|16|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|C]]|U|$5|Q|R|S|A|$T|K]]]]

I know it a little late to answer but I think this short video will help you a lot.

<a href="http://www.xlninja.com/2012/07/25/excel-choose-function-explained/" rel="nofollow">http://www.xlninja.com/2012/07/25/excel-choose-function-explained/</a>

Essentially it is using the choose function. He explains it very well in the video so I'll let do it instead of typing 20 pages.

Another video of his explains how to use data validation to populate a drop down which you can select from a limited range.

<a href="http://www.xlninja.com/2012/08/13/excel-data-validation-using-dependent-lists/" rel="nofollow">http://www.xlninja.com/2012/08/13/excel-data-validation-using-dependent-lists/</a>

You could combine the two and use the value in the drop down as your index to the choose function. While he did not show how to combine them, I'm sure you could figure it out as his videos are good. If you have trouble, let me know and I'll update my answer to show you.

blocks|key|949413|text|我知道这是对一个老帖子的回应-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|949414|我喜欢将If()函数与Index()/Match()结合使用：|949415|=IF(B2>0,"x",INDEX($H$2:$I$9,MATCH(A2,$H$2:$H$9,0),2))|code-block|syntax|javascript|949416|if函数比较b列中的内容，如果大于0，则返回x；如果不大于0，则使用Index()函数标识的数组(信息表)和Match()选择的数组返回a对应的值。|949417|索引数组具有绝对位置集$H$2:$I$9+(美元符号)，因此它所指向的位置在复制公式时不会更改。具有您想要返回的值的行由Match()函数标识。Match()的附加值是不需要排序列表来查找Vlookup()所需的内容。Match()可以找到具有以下值的值:1小于，0精确，-1大于。我在绝对匹配()数组$H$2:$H$9后面加了一个零，以查找精确匹配。对于列，输入希望返回的Index()数组的值。我输入了一个2，因为在我的数组中返回值在第二列。下面的索引数组如下所示：|offset|length|style|CODE|949418|32+++1420

36+++1650

40+++1790

44+++1860

55+++2010|949419|在我的示例中，要在列表中搜索的'a‘列中的值在第一列，要返回的相应值在右边。查找/引用表可以在工作簿中的任何选项卡上，甚至可以在另一个文件中。-Book2是文件名，Sheet2是'other+tab‘名称。|949420|=IF(B2>0,"x",INDEX([Book2]Sheet2!$A$1:$B$8,MATCH(A2,[Book2]Sheet2!$A$1:$A$8,0),2))|949421|如果你不想在b的值大于零时返回x，请删除x中的空值/null等效项，或者可能添加一个0-不确定你想要的是什么。|949422|下面是函数的开头，删除了x。|949423|=IF(B2>0,"",INDEX...|949424|entityMap^0|0|0|0|0|B|9|47|9|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|17|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|18|8|@$M|19|N|1A|O|P]|$M|1B|N|1C|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|F|7|1D|8|@]|9|@]|A|$G|H]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|1F|8|@]|9|@]|A|$G|H]]|$1|W|3|X|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1H|8|@]|9|@]|A|$]]|$1|10|3|11|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|12|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|13|$]]

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

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

<pre><code>=IF(B2&gt;0,"x",INDEX($H$2:$I$9,MATCH(A2,$H$2:$H$9,0),2))
</code></pre>

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 <code>$H$2:$I$9</code> (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 <code>$H$2:$H$9</code> 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:

<pre><code>32 1420

36 1650

40 1790

44 1860

55 2010
</code></pre>

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.

<pre><code>=IF(B2&gt;0,"x",INDEX([Book2]Sheet2!$A$1:$B$8,MATCH(A2,[Book2]Sheet2!$A$1:$A$8,0),2))
</code></pre>

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.

<pre><code>=IF(B2&gt;0,"",INDEX...
</code></pre>

blocks|key|949469|text|不考虑最初的问题(我怀疑这个问题早就解决了)，我最近发现了一个巧妙的技巧，它使Choose函数的工作方式与select+case语句完全一样，不需要修改任何数据。只有一个问题:在任何时候，只有一个选择条件可以为真。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|949470|语法如下：|949471|CHOOSE(+
++++(1+*+(CONDITION_1))+%2B+(2+*+(CONDITION_2))+%2B+...+%2B+(N+*+(CONDITION_N)),
++++RESULT_1,+RESULT_2,+...+,+RESULT_N
)|code-block|syntax|javascript|949472|假设条件1到N中只有一个为真，其他条件都为0，这意味着数值将对应于适当的结果。|949473|如果您不能100%25确定所有条件都是互斥的，您可能更喜欢这样的条件：|949474|CHOOSE(
++++(1+*+TEST1)+%2B+(2+*+TEST2)+%2B+(4+*+TEST3)+%2B+(8+*+TEST4)+...+(2%5EN+*+TESTN)
++++OUT1,+OUT2,+,+OUT3,+,+,+,+OUT4+,+,+<LOTS+OF+COMMAS>+,+OUT5
)|949475|也就是说，如果Excel对一个函数可以接受的参数数量有一个上限，那么你很快就会达到这个上限。|949476|老实说，不敢相信我花了几年时间才弄明白，但我以前没有见过它，所以我想我应该把它留在这里帮助别人。|949477|编辑:通过使用以下形式的公式，可以消除愚蠢的逗号数量(因此，choose语句最多可用于254种情况)：|949478|CHOOSE(
++++1+%2B+LOG(1+%2B+(2*TEST1)+%2B+(4*TEST2)+%2B+(8*TEST3)+%2B+(16*TEST4),2),+
++++OTHERWISE,+RESULT1,+RESULT2,+RESULT3,+RESULT4
)|949479|请注意LOG子句的第二个参数，它将其设置为基数2，并使整个过程正常工作。|949480|编辑:根据David's+answer，现在有一个实际的switch语句，如果你足够幸运地在office+2016上工作。除了阅读困难之外，这也意味着你得到了switch的效率，而不仅仅是行为！|949481|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/38849439/847721^0|1H|B|0|0|0|0|0|0|0|0|0|0|3|3|0|5|E|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1F|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1G|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1H|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|1I|8|@]|D|@]|E|$]]|$1|Q|3|R|5|J|7|1J|8|@]|D|@]|E|$K|L]]|$1|S|3|T|5|6|7|1K|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1L|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|1M|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|J|7|1N|8|@]|D|@]|E|$K|L]]|$1|10|3|11|5|6|7|1O|8|@$9|1P|A|1Q|B|C]]|D|@]|E|$]]|$1|12|3|13|5|6|7|1R|8|@]|D|@$9|1S|A|1T|1|1U]]|E|$]]|$1|14|3|-4|5|6|7|1V|8|@]|D|@]|E|$]]]|15|$16|$5|17|18|19|E|$1A|1B]]]]

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 <code>select case</code> 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:

<pre><code>CHOOSE( 
 (1 * (CONDITION_1)) + (2 * (CONDITION_2)) + ... + (N * (CONDITION_N)),
 RESULT_1, RESULT_2, ... , RESULT_N
)
</code></pre>

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:

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

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:

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

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

Edit: Per <a href="https://stackoverflow.com/a/38849439/847721">David's answer</a>, 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!

blocks|key|949548|text|我使用此解决方案将单字母颜色代码转换为它们的描述：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|949549|=CHOOSE(FIND(H5,"GYR"),"Good","OK","Bad")|code-block|syntax|javascript|949550|基本上就是在数组中查找要解码的元素，然后使用CHOOSE()选择相关的项。它比为VLOOKUP()构建一个表要紧凑一些。|offset|length|style|CODE|949551|entityMap^0|0|0|M|8|14|9|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>=CHOOSE(FIND(H5,"GYR"),"Good","OK","Bad")
</code></pre>

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

blocks|key|949612|text|Microsoft将SWITCH、IFS和IFVALUES替换为CHOOSE功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|949613|=CHOOSE($L$1,"index_1","Index_2","Index_3")|code-block|syntax|javascript|949614|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>=CHOOSE($L$1,"index_1","Index_2","Index_3")
</code></pre>

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

<pre><code>| A - B - C - D |
|------|------|------|------|
| 36 | 0 | 0 | x |
| 0 | 600 | 700 | x |
|___________________________|
</code></pre>

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, <code>32 = 1420</code> and place into <code>D</code>. 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 <code>=IF()</code> statements together, which I did about four times before deciding it was a bad idea (story of my life).

Case Function Equivalent in Excel

我有一个有趣的挑战-我需要在Excel中对以下数据运行检查：|   A  -   B  -   C  -  D   ||------|------|------|------||  36  |   0  |   0  |   x  ||   0  |  600 |  700 |   x  ||_____________...

问Excel中等效的Case函数
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel中等效的Case函数EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel中等效的Case函数
EN