条件赋值求和

廖月

在右边的统计列内赋值：凡遇到30赋值2；凡遇到25赋值1；凡遇到20赋值0.5；凡遇到15赋值1；凡遇到10赋值0.5；凡遇到空赋值-1；凡遇到假赋值0；最后把每个人的分值求和，这个能办到吗？

loirol

=SUM(XLOOKUP(B2:H2,{30,25,20,15,10,"假"},{2,1,0.5,1,1,0},-1))

fgq5910

=SUM(IF(B2:H2="", -1, XLOOKUP(B2:H2, {30,25,20,15,10,"假"}, {2,1,0.5,1,1,0}, -1)))

=SUM(IFS(B2:H2="", -1, B2:H2="假", 0, TRUE, LOOKUP(B2:H2, {10,15,20,25,30}, {1,1,0.5,1,2})))

廖月

fgq5910 发表于 2025-12-11 08:34
=SUM(IF(B2:H2="", -1, XLOOKUP(B2:H2, {30,25,20,15,10,"假"}, {2,1,0.5,1,1,0}, -1)))

=SUM(IFS(B2:H2 ...

老师，您算的是对的

廖月

loirol 发表于 2025-12-11 08:07
=SUM(XLOOKUP(B2:H2,{30,25,20,15,10,"假"},{2,1,0.5,1,1,0},-1))

老师，没有求和

hxj119

1. =SUM(XLOOKUP(B2:H2,{"假",10,15,20,25,30},{0,0.5,1,0.5,1,2},-1,0))

复制代码

石头怪

1. =SUM(XLOOKUP(B2:H2,{30,25,20,15,10,"假"},{2,1,0.5,1,0.5,0},-1,0))

复制代码

公式下拉

alanyao168

1. =BYROW(B2:H157,LAMBDA(x,SUM(TOROW(SWITCH(x,{30;25;20;15;10;0;"假"},{2;1;0.5;1;0.5;-1;0}),3))))

复制代码

hxj119

参考公式2：

1. =MAP(A2:A157,LAMBDA(z,REDUCE(0,OFFSET(z,,1,,7),LAMBDA(x,y,x+IF(y="",-1,SUM(({"假",10,15,20,25,30}=y)*{0,0.5,1,0.5,1,2}))))))

复制代码

loirol

廖月 发表于 2025-12-11 09:19
老师，没有求和

sure？