Question

EXCEL中多条件公式，

一、每天按综合完成率排名胜方达70%及以上奖励500元，80%及以上奖励800元，100%及以上奖励1200元； 二、扣罚：每天败方扣罚300元，综合完成率达60%不扣罚，败方若达70%及以上不扣罚奖励300元；PK双方综合完成率低于50%，败方扣罚500元、胜方扣罚400；

Answer 1

如图，将表格格式稍作改动，A2公式：

=LOOKUP(SUM(--(IF(SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜")>0,SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜"),"")<C2))/(COUNT(IF(SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜")>0,SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜"),""))-1),{0,0.7,0.8,1},{0,500,800,1200})*(D2="胜")+IF(D2="负",IF(C2>=0.7,300,IF(C2>=0.6,0,-300)),0)+IF(AND(C2<0.5,G2<0.5,D2="胜"),-400,IF(AND(C2<0.5,G2<0.5,D2="负"),-500,0))

ctrl+shift+回车下拉

I2公式：

=LOOKUP(SUM(--(IF(SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜")>0,SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜"),"")<G2))/(COUNT(IF(SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜")>0,SUMIFS($C$2:$G$8,$B$2:$F$8,$B$2:$F$8,$D$2:$H$8,"胜"),""))-1),{0,0.7,0.8,1},{0,500,800,1200})*(H2="胜")+IF(H2="负",IF(G2>=0.7,300,IF(G2>=0.6,0,-300)),0)+IF(AND(C2<0.5,G2<0.5,H2="胜"),-400,IF(AND(C2<0.5,G2<0.5,H2="负"),-500,0))

ctrl+shift+回车下拉

Answer 2

你把后面公式的引号去掉
=IF（CI<1.4,“1”，IF（C1<=5,"1.5",(CI-5)*0.1+1.5))
加上“”后，它以为是文本，所以就直接输出了，并没计算

追问

我感觉 你不是在回答的我的问题

Answer 3

在图中D2单元格输入：=IF(C2="胜",IF(B2>=1,1200,IF(B2>=0.8,800,IF(B2>=0.7,500,IF(B2<0.5,-400,0)))),IF(B2>=0.7,300,IF(B2>=0.6,0,IF(B2<0.5,-500,-300))))

下拉填充，把B2单元格内容复制到I2单元格，也下拉填充，OK。

Answer 4

如图，B列是综合完成率，C列是结果，D列是奖罚金额，具体公式（多层IF函数嵌套）如下:

=IF(C2="胜",IF(B2>100%,1200,IF(B2>80%,800,IF(B2>70%,500,IF(B2<50%,-400,)))),IF(B2<50%,-500,IF(B2<60%,-300,IF(B2>70%,300,))))

Answer 5

=if(AND(B2>=1,C2="胜"),1200,if(AND(B2>=0.8,C2="胜"),800,if(AND(B2>=0.7,C2="胜"),500,0)))