案例函数等价于Excel [英] Case Function Equivalent in Excel

问题描述

我有一个有趣的挑战 - 我需要对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（）语句连在一起是一件很疯狂的事情，我做了大约四次，然后才决定这是一个坏主意我的生活）

解决方案

听起来像一个工作 VLOOKUP ！

你可以把你的32 - > 1420类型的映射在几个列的某处，然后使用VLOOKUP函数执行查找。

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).

解决方案

Sounds like a job for VLOOKUP!

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

这篇关于案例函数等价于Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！