EXCEL多个范围 - 每个范围需要不同的答案 [英] EXCEL Multiple Ranges - need different answers for each range

问题描述

我花了几个小时研究如何做到这一点，这就是为什么我现在在这里发布...如果你想在一个单元格中返回不同的值，根据在另一个单元格中输入的值的范围，那么我已经知道如何做到!! （请记住，这是我的电子表格特有的，用于计算价格，即0.99 =£0.99）

I have spent a few hours working out how to do this which is why im posting it here now... If you want to return different values in a cell based on which range the value entered in another cell comes under then I have worked out how to do it!! (bear in mind that this is specific to my spreadsheet and was for calculating prices i.e. 0.99 = £0.99)

例如：

1. 如果G2超过0但低于1那么显示0.1


2. 如果G2超过0.99但是低于 5那么显示0.15


3. 如果G2超过4.99但是低于15那么显示0.2


4. 如果G2是上面的话14.99但是低于30那么显示0.5


5. 如果G2超过29.99但是低于100那么显示1.0


6. IF G2超过99.99然后显示1.30

1. IF G2 is ABOVE "0" BUT BELOW "1" THEN display "0.1"
2. IF G2 is ABOVE "0.99" BUT BELOW "5" THEN display "0.15"
3. IF G2 is ABOVE "4.99" BUT BELOW "15" THEN display "0.2"
4. IF G2 is ABOVE "14.99" BUT BELOW "30" THEN display "0.5"
5. IF G2 is ABOVE "29.99" BUT BELOW "100" THEN display "1.0"
6. IF G2 is ABOVE "99.99" THEN display "1.30"

所以如果G2是£18.75那么<输入strong>此公式将根据上面的值显示£0.50。

So IF G2 was "£18.75" then the cell that this formula is entered in would display "£0.50" based on the value's above.

您将在此答案中看到上述公式问题如下......

You will see the above formula contained within the answer to this question below......

推荐答案

使用

=VLOOKUP(D4,F4:G9,2)

范围F4：G9：

0   0.1
1   0.15
5   0.2
15  0.3
30  1
100 1.3

和 D4 是有问题的值，例如 18.75 - >结果： 0.3

and D4 being the value in question, e.g. 18.75 -> result: 0.3

这篇关于EXCEL多个范围 - 每个范围需要不同的答案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！