用EXCEL做提成表

答案:2 悬赏:80 手机版

解决时间 2021-02-02 11:29

提问者网友：辞取
2021-02-02 02:15

我用excel做个提成表，分为ABCD四类，现在做我想把提成统计出来，我自己了个IF语句，现在不成功，高手帮看一下，语句如下：
提成比率：
=IF(J2/H2>=3.5,J2*0.1,IF(3.5>J2/H2>=3,J2*0.08,IF(3>J2/H2>=2,J2*0.05,IF(J2/H2<2,J2*0.02))))

提成级别
=IF(J2/H2>=3.5,"A",IF(3.5>J2/H2>=3,"B",IF(3>J2/H2>=2,C,IF(J2/H2<2,"D"))))

最佳答案

五星知识达人网友：woshuo
2021-02-02 02:28

=IF(J2/H2>=3.5,J2*0.1,IF(J2/H2>=3,J2*0.08,IF(J2/H2>=2,J2*0.05,J2*0.02)))

=IF(J2/H2>=3.5,"A",IF(J2/H2>=3,"B",IF(J2/H2>=2,"C","D")))

=========
你的公式太臃肿了如果你实在要表示并且那就用

=IF(AND(J2/H2>=3,J2/H2<3.5) 来表示大于等于3 且小于3.5

3.5 >J2/H2>=3 是错误的表达方式

============

告诉你个一点都不臃肿的

=LOOKUP(J2/H2,{0,2,3,3.5},{0.02,0.05,0.08,0.1})*J2

=LOOKUP(J2/H2,{0,2,3,3.5},{"D","C","B","A"})

全部回答

1楼网友：低血压的长颈鹿
2021-02-02 02:44

=if(j2/h2>=3.5,j2*0.1,if(j2/h2>=3,j2*0.08,if(j2/h2>=2,j2*0.05,j2*0.02))) =if(j2/h2>=3.5,"a",if(j2/h2>=3,"b",if(j2/h2>=2,"c","d"))) ========= 你的公式太臃肿了如果你实在要表示并且那就用 =if(and(j2/h2>=3,j2/h2<3.5) 来表示大于等于3 且小于3.5 3.5 >j2/h2>=3 是错误的表达方式 ============ 告诉你个一点都不臃肿的 =lookup(j2/h2,{0,2,3,3.5},{0.02,0.05,0.08,0.1})*j2 =lookup(j2/h2,{0,2,3,3.5},{"d","c","b","a"})

我要举报

如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！