作业帮EXCEL表格的IF函数超过7层,怎么办?
来源：学生作业帮 编辑：拍题作业网作业帮 分类：综合作业 时间：2024/04/30 06:41:45
EXCEL表格的IF函数超过7层,怎么办?
A7=IF(M7="F",O7*$G$343,IF(M7="LK",O7*$G$343,IF(M7="T",O7*$G$344,IF(M7="L",O7*$G$345,IF(M7="X",O7*$G$346,IF(M7="A",O7*$G$347,IF(M7="B",O7*$G$348,IF(M7="C",O7*$G$349,IF(M7="D",O7*$G$350)))))))))
请问这个IF公式超过了7层,用OFFICE2003有什么办法解决?
我的意思是：单元格M7,有很多个分类,F、T、A、B、C、D……,而根据不同的分类,得出的结果是等于O7乘于相应的比例（每种分类有不同的比率）.希望我的表达能让你们看得懂.
A7=IF(M7="F",O7*$G$343,IF(M7="LK",O7*$G$343,IF(M7="T",O7*$G$344,IF(M7="L",O7*$G$345,IF(M7="X",O7*$G$346,IF(M7="A",O7*$G$347,IF(M7="B",O7*$G$348,IF(M7="C",O7*$G$349,IF(M7="D",O7*$G$350)))))))))
请问这个IF公式超过了7层,用OFFICE2003有什么办法解决?
我的意思是：单元格M7,有很多个分类,F、T、A、B、C、D……,而根据不同的分类,得出的结果是等于O7乘于相应的比例（每种分类有不同的比率）.希望我的表达能让你们看得懂.
按你公式改成如下
=IF(OR(M7={"F","LK"}),G343,IF(M7="T",G344,IF(M7="L",G345,IF(M7="X",G346,IF(M7="A",G347,IF(M7="B",G348,IF(M7="C",G349,IF(M7="D",G350,""))))))))*O7
再缩简
=IF(OR(M7={"F","LK"}),G343,IF(M7="T",G344,IF(M7="L",G345,IF(M7="X",G346,IF(OR(M7={"A","B","C","D"}),INDIRECT("G"&346+CODE(M7)-64),"")))))*O7
=IF(OR(M7={"F","LK"}),G343,IF(M7="T",G344,IF(M7="L",G345,IF(M7="X",G346,IF(M7="A",G347,IF(M7="B",G348,IF(M7="C",G349,IF(M7="D",G350,""))))))))*O7
再缩简
=IF(OR(M7={"F","LK"}),G343,IF(M7="T",G344,IF(M7="L",G345,IF(M7="X",G346,IF(OR(M7={"A","B","C","D"}),INDIRECT("G"&346+CODE(M7)-64),"")))))*O7