求助帖 IF函数多级嵌套 求助帖

阿唯123

两个条件同时满足输出优/良...
优	良	差	休眠
A>66.7%	66.7%>=A>27.2%	27.2%>=A>0	A=0
B>50%	50%>=B>14.3%	14.3%>=B>0	B=0

A	B	健康度
89.3%	89.3%
1.3%	1.0%
13.2%	13.2%
41.3%	38.4%
83.0%	81.8%
21.4%	21.4%
70.4%	70.4%
65.4%	50.0%
1.1%	0.3%
3.5%	3.1%
9.8%	8.8%
11.0%	10.7%
0.6%	0.0%
5.1%	0.9%
2.4%	0.0%
23.1%	23.0%
2.1%	2.1%
2.7%	2.3%
0.8%	0.0%
37.6%	37.1%
46.4%	43.4%
46.4%	43.4%
46.4%	43.4%

根据第一张图里的条件，判断健康度，返回值为优良差休眠

阿唯123

怎么写这个函数呀

lss001

假如要判断的数据在A6开始的单元格
则c6公式
=if(and(a6>66.7%,b6>50%),"优",if(and(a6>27.2%,a6<=66.7%,b6>14.3%,b6<=50%),"良",if(and(a6>0,a6<=27.2%,b6>0,b6<=14.3),"差","休眠")))

gykg-excel

=INDEX($A$1:$D$1,MAX(LOOKUP(A7,{0,0.01,27.2,66.71}%,{4,3,2,1}),LOOKUP(B7,{0,0.01,14.3,50.01}%,{4,3,2,1})))

yryxqg

1. =IF(AND(A6>66.7%,B6>50%),"优",IF(AND(A6>27.2%,B6>14.3%),"良",IF(AND(A6>0,B6>0),"差","休眠")))

复制代码