Question

有没有大神可不可以帮我简化一下?excel函数if嵌套次数太多

有没有大神可不可以帮我简化一下?excel函数if嵌套次数太多，改起来很麻烦
=IF(AND(N17<=100,I17<20),4.73,IF(AND(N17<=100,I17>20),3.31,IF(AND(N17<=200,I17<20),6.16,IF(AND(N17<=200,I17>20),4.31,IF(AND(N17<=300,I17<20),7.59,IF(AND(N17<=300,I17>20),5.31,IF(AND(N17<=400,I17<20),9.02,IF(AND(N17<=400,I17>20),6.31,IF(AND(N17<=500,I17<20),10.45,IF(AND(N17<=500,I17>20),7.32,IF(AND(N17<=600,I17<20),11.88,IF(AND(N17<=600,I17>20),8.32,IF(AND(N17<=700,I17<20),13.31,IF(AND(N17<=700,I17>20),9.32,IF(AND(N17<=1000,I17<20),12.1,IF(AND(N17<=1000,I17>20),8.47,IF(AND(N17<=1500,I17<20),18.15,IF(AND(N17<=1500,I17>20),12.7))))))))))))))))))

Answer 1

公式简化为
=IF(N17<=100,IF(I17<20,4.73,3.31),IF(N17<=200,IF(I17<20,6.16,4.31),IF(N17<=300,IF(I17<20,7.59,5.31),IF(N17<=400,IF(I17<20,9.02,6.31),IF(N17<=500,IF(I17<20,10.45,7.32),IF(N17<=600,IF(I17<20,11.88,8.32),IF(N17<=700,IF(I17<20,13.31,9.32),IF(N17<=1000,IF(I17<20,12.1,8.47),IF(N17<=1500,IF(I17<20,18.15,12.7),0)))))))))
或
=IFS(N17<=100,IF(I17<20,4.73,3.31),N17<=200,IF(I17<20,6.16,4.31),N17<=300,IF(I17<20,7.59,5.31),N17<=400,IF(I17<20,9.02,6.31),N17<=500,IF(I17<20,10.45,7.32),N17<=600,IF(I17<20,11.88,8.32),N17<=700,IF(I17<20,13.31,9.32),N17<=1000,IF(I17<20,12.1,8.47),N17<=1500,IF(I17<20,18.15,12.7),TRUE,0)
下拉填充

Answer 2

=INDEX({4.73;3.31;6.16;4.31;7.59;5.31;9.02;6.31;10.45;7.32;11.88;8.32;13.31;9.32;12.1;8.47;18.15;12.7},MATCH(I17%,{0,1,2,3,4,5,6,7,10,15}+1%%,1)*2-(N17<20))