Question

求助，我要做一个EXCEL表格，需要函数公式，但是我搞不明白，请高手帮忙做一下

±6%的时候表格上显示“合理”，±6~10%的时候表格上要显示“较合理偏高”“较合理偏低”±10~20的时候显示“偏高”“偏低”，±20的时候要显示“不合理偏高”“不合理偏低”

Answer 1

=LOOKUP((G5-$O5)/$O5,{-1,-0.2,-0.1,-0.06,0.0600001,0.100001,0.200001},{"不合理偏低","偏低","较合理偏低","合理","较合理偏高","偏高","不合理偏高"})

或者：
=IF((G5-$O5)/$O5<-0.2,"不合理偏低",IF((G5-$O5)/$O5<-0.1,"偏低",IF((G5-$O5)/$O5<-0.06,"较合理偏低",IF((G5-$O5)/$O5<=0.06,"合理",IF((G5-$O5)/$O5<=0.1,"较合理偏高",IF((G5-$O5)/$O5<=0.2,"偏高","不合理偏高"))))))
使用if 函数 要注意 条件的逻辑关系 最后 一定要封闭……

Answer 2

逻辑混乱了
应该是<-20不合理偏低 ,<-10 偏低 <-6 <6 <10 <20
按上面的顺序编写就可以了

给你个正确的公式，但是是另一个思路的

=LOOKUP(G5-$O5)/$O5,{-1000,-20,-10,-6,6,10,20},{"不合理偏低","偏低","较合理偏低","合理","较合理偏高","偏高","不合理偏高"})

Answer 3

公式改为
=IF(ABS((G5-$O5)/$O5)<=6%,"合理",LOOKUP(ABS((G5-$O5)/$O5),{6,10,20}%,{"较合理","","不合理"})&IF(G5>$O5,"偏高","偏低"))
试试。

Answer 4

这个公式测试通过
=LOOKUP((G5-$O5)/O$5,{-999,-20,-10,-6,6.01,10.01,20.01}%,{"不合理偏低","较合理偏低","偏低","合理","偏高","较合理偏高","不合理偏高"})

Answer 5

IF函数的调用,你可以把文件发来共同研究下么.

992219343@qq.com