excel IF函数判定字母条件（在线等）

Question

等级：S/A/B/C/D（对应等级S=120%，A=100%，B=95%，C=90%，D=85%）比如A1单元格是字母S，那么B1单元格对应填入if函数，达到的效果是B1单元格判定为120%以此类推。该如何填写if函数。谢谢各位

Answer 1

直接在B1输入公式:

=sum((A1={"S","A","B","C","D"})*{1.2,1,0.95,0.9,0.85})

设置B1单元格格式，自定义为0%

若需批量处理，直接下拉即可。

Answer 2

用if函数的结果如下

使用的公式为
=IF(A1="S",1.2,
IF(A1="A",1,
IF(A1="B",0.95,
IF(A1="C",0.9,
IF(A1="D",0.85,
)))))
为了便于阅读，我特意加了回车。
其实，这个问题用if函数并不高明
用vlookup就方便多了

本回答被网友采纳

Answer 3

嵌套太多的时候，一般不建议用IF了，可参考下面这个方法：
B1输入公式：=LOOKUP(1,0/(A1={"S","A","B","C","D"}),{1.2,1,0.95,0.9,0.85})
设置B1单元格格式为百分比。
懂的话用下面这个更完美。
=IFERROR(LOOKUP(1,0/(A1={"S","A","B","C","D"}),{1.2,1,0.95,0.9,0.85}),"A1输入有误")

Answer 4

If函数嵌套的太多，烦，用Lookup简单方便
=Lookup(A1,{"S","A","B","C","D"},{"120%","100%","95%","90%","85%"})本回答被提问者采纳

Answer 5

请使用如下公式：
=if(a1="S","120%",IF(A1="A","100%",IF(A1="B","95%",IF(A1="C","90%",IF(A1="D","85%","")))))