在Excel中使用嵌套的IF语句 [英] working with Nested IF statement in excel

问题描述

ID   Height   Phase   Corrected_Height  Final
1    0        A       0                 0
2    1.2      A       1.2               1.2
3    3.9      A       3.9               3.9
4    5.8      A       5.8               5.8
5    4.6      A       NA                7.7
6    7.7      A       7.7               9.3
7    9.3      A       9.3              10.8
..
300  237.5    P       237.5             ..
301  234.7    D       234.7             ..
302  233.3    D       233.3             ..
303  235.1    D       NA                ..
555  1.0      D       1.0   

我有一组结构相似的数据.根据解决方案在Excel中命名行为 并计算我使用=IF(C4="A" & B4>B3;B4; IF(C4="D" & B4<B3;B4;"NA")))的Corrected_Height列.但是我没有得到所需的结果.想法是，当处于"A"阶段时，如果出现一个比前一个值低的值，则应更改为NA；而在"D"阶段，如果出现一个值大于前一个值，则应再次更改为NA.有什么建议我应该改变配方吗?而且我还想要最后一栏，该栏为我提供不带NA的值.相中的A，P和D表示上升，峰值和下降.

I have a set of data of similar structure. Calculation of the Phase column was done according to the formula =IF(B2=MAX(B:B);"P";IF(ROW(B2)<MATCH(MAX(B:B);B:B;0);"A";"D"))thanks to @Scott Craner for the solution Naming a behavior in Excel and to calculate the Corrected_Height column I used =IF(C4="A" & B4>B3;B4; IF(C4="D" & B4<B3;B4;"NA"))). However I did not get the required result. The idea is when in "A" phase, should a lower value arise than the previous one it should change to NA and in "D" phase, should a value be higher than the previous one it should again change to NA. Any suggestion what should I change in the formula? And I also want a final column that gives me the values without NA in it. A,P,and D in phase means Ascent,Peak, and Descent.

推荐答案

在Excel公式中，不能使用&运算符对两个条件进行逻辑和运算.而是使用AND()函数:

The & operator cannot be used to logically and together two conditions in an Excel formula. Instead, use the AND() function:

=IF(AND(C4="A", B4>B3), B4, IF(AND(C4="D", B4<B3), B4, "NA"))

这篇关于在Excel中使用嵌套的IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！