Question

求表格函数公式

多条件判断进行求结果

Answer 1

问题不完整，没说清楚取数据表中数据后怎么处理。

参考取数据表公式：

=OFFSET($G$3,LOOKUP(C2,{0,20,40,60,80,100},{0,1,2,3,4,5}),LOOKUP(B2,{0,0.5,0.6,0.7,0.8,0.9,1},{1,2,3,4,5,6,7}))

另外，建议修改数据表，不规范的数据很害人的，如果数据表直接是范围，就无需写这么长的公式了。

Answer 2

E2单元格写公式：
=IFERROR(INDEX($H$3:$N$8,MATCH(C2,{0,20,40,60,80,100,120},1),MATCH(B2,{0,0.5,0.6,0.7,0.8,0.9,1,1.1},1))*D2,"数据问题，请检查！")
然后向下拖动填充公式即可。

Answer 3

E2公式：
=D2 *
INDEX($H$3:$N$8,
IF(C2<20,1,IF(C2<=40,2,IF(C2<=60,3,IF(C2<=80,4,IF(C2<=100,5,6))))),
IF(B2<50%,1,IF(B2<=60%,2,IF(B2<=70%,3,IF(B2<=80%,4,IF(B2<=90%,5,IF(B2<=100%,6,7)))))))