Microsoft Excel如果语句 [英] Microsoft Excel If Statements
问题描述
我已经从以前的答案中更改了一个声明现在看起来像这样：
= IF（C6 = $ R $ 3，IF（D6 <= 0.99，$ U $ 2，IF（AND（D6> 0.99，D6 < = 4.99），$ U $ 3 IF（AND（D6> 4.99，D6< = 14.99），$ U $ 4中，IF（AND（D6> 14.99，D3< = 29.99），$ U $ 5中，IF（AND（D6> 29.99，D6 <99.99），$ U $ 6，））））），$ S $ 8）
一切正常，直到您更改单元格D6中的值如果在U5中仍然拿起这个数字，就说45英镑。
你或任何人可以帮我调整一下吗？我需要一个声明来执行以下操作：
如果C2 = R2和D2是< T2然后U2，如果D2是> T但是T3， T4然后U4，如果D2> T4， T5然后U5，如果D2> T5， T6然后U6但如果C2不等于R2，那么S8
把所有的问题解决掉：
如果C2 = R2和D2是< T2然后U2，如果D2是> T但是T3， T4然后U4，如果D2> T4， T5然后U5，如果D2> T5， T6然后U6但如果C2不等于R2，则S8
从此开始，使用 NA（）表示零件它们尚未完成（这将显示单元格中的＃N / A 值）：
= IF（C2 = R2，NA（），S8）
添加基于D2的查找：
= IF（C2 = R2，IF（D2 假设下一部分是D2> T2和D2 < T3（尽管这个公式说D2> = T2），结果是U3：
= IF（C2 = R2，IF D2< T2，U2，IF（D2 现在在T3和T4之间添加：
= IF（C2 = R2，IF（D2 T4和T5之间：
= IF（C2 = R2，IF（D2 $ b $最后在T5和T6之间：
= IF（C2 = R2，IF（D2 我们还有 NA（），因为您尚未定义C2 = R2和D2> = T6的行为
正如Stobor在您的原始问题的评论中所说，使用 VLOOKUP 会好多了 - 见 http://office.microsoft.com/en-us/excel/HP052093351033.aspx 了解详情
您当前的结构在T和U列不能与 VLOOKUP 一起使用，因为：
这意味着 VLOOKUP 将返回 U3 ，当您想要 U2 ， U4 而不是 U3 等等。要解决这个问题，您需要将U列中的所有条目向下移动一行，将虚拟值或 = NA（）放入U2中，并创建一个值T7大于T6中现有值
I have altered a statement I got from a previous answer a bit and it now looks like this:
=IF(C6=$R$3,IF(D6<=0.99,$U$2,IF(AND(D6>0.99,D6<=4.99),$U$3,IF(AND(D6>4.99,D6<=14.99),$U$4,IF(AND(D6>14.99,D3<=29.99),$U$5,IF(AND(D6>29.99,D6<99.99),$U$6,""))))),$S$8)
It all works fine until you change the value in cell D6 to say £45 when it still picks up the figure in cell U5.
Can you or anyone else help me tweak this so that it works? I need a statement to do the following:
If C2=R2 and D2 is < T2 then U2, if D2 is >T but T3 but < T4 then U4 if D2 is > T4 but < T5 then U5, if D2 is > T5 but < T6 then U6 BUT if C2 does not equal R2 then S8
Take all your problems and rip them apart:
If C2=R2 and D2 is < T2 then U2, if D2 is >T but T3 but < T4 then U4 if D2 is > T4 but < T5 then U5, if D2 is > T5 but < T6 then U6 BUT if C2 does not equal R2 then S8
Start with this using NA() to represent parts which haven't been completed yet (this will show the #N/A value in the cell):
=IF(C2=R2,NA(),S8)
Add the lookup based on D2:
=IF(C2=R2,IF(D2<T2,U2,NA()),S8)
Assuming that the next part is D2 > T2 and D2 < T3 (althought strictly this formula says D2 >= T2) and result is U3:
=IF(C2=R2,IF(D2<T2,U2,IF(D2<T3,U3,NA())),S8)
Now add between T3 and T4:
=IF(C2=R2,IF(D2<T2,U2,IF(D2<T3,U3,IF(D2<T4,U4,NA()))),S8)
Between T4 and T5:
=IF(C2=R2,IF(D2<T2,U2,IF(D2<T3,U3,IF(D2<T4,U4,IF(D2<T5,U5,NA())))),S8)
Finally between T5 and T6:
=IF(C2=R2,IF(D2<T2,U2,IF(D2<T3,U3,IF(D2<T4,U4,IF(D2<T5,U5,IF(D2<T6,U6,NA()))))),S8)
We still have NA() because you haven't defined the behaviour for C2=R2 and D2 >= T6
As Stobor said in the comment to your original question, using VLOOKUP would be much better - see http://office.microsoft.com/en-us/excel/HP052093351033.aspx for details
Your current structure in the T and U columns won't work with VLOOKUP because:
the next largest value that is less than lookup value is returned
This would mean that VLOOKUP would return U3 when you wanted U2, U4 instead of U3 and so on. To solve this you would need to move all of the entries in the U column down by one row, put a dummy value or =NA() into U2 and create a value in T7 that was greater than the existing value in T6
这篇关于Microsoft Excel如果语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
