在公式中添加许多IF [英] Add many IFs to a formula

问题描述

有没有更简单，更紧凑的方法来编写此公式?

Is there an easier and more compact way to write this formula?

[1]       [2]
A         0
B         5940
C         13860
D         22500
E         87300
F         378000

 =IF(Q27="A",0,IF(Q27="B",5940,IF(Q27="C",13860,IF(Q27="D",22500,IF(Q27="E",87300,IF(Q27="F",378000,"ERROR"))))))

因此，如果Q27等于[1]中的字母之一，则返回[2]中的相应值. 我设法获得了预期的结果，但是我想知道代码是否可以改进.

So what it does is return the respective values in [2] if Q27 equals one of the letters in [1]. I managed to get the result I expected but I'd like to know if the code can be improved.

谢谢

推荐答案

这就是VLOOKUP的目的.将您的桌子放在A1:B6中，然后:

That is what VLOOKUP is for. Put your table in A1:B6 then:

=VLOOKUP(Q27,A:B,2,FALSE)

或者您可以像这样硬编码" VLOOKUP:

Or you can "Hard Code" the VLOOKUP like this:

=VLOOKUP(Q27,{"A",0;"B",5940;"C",13860;"D",22500;"E",87300;"F",378000},2,FALSE)

或者:

=INDEX({0,5940,13860,22500,87300,378000},MATCH(Q27,{"A","B","C","D","E","F"},0))

这篇关于在公式中添加许多IF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！