统计函数如何忽略空白单元格？

痛痛痛痛

如下图和要求，9门科目成绩，但不是每个人都上了9门，即有些科目成绩为空格，如果按照下列要求统计获奖情况



K列利用公式计算，返回几等奖
1.一等奖：平均成绩80分以上，单科成绩不得低于70分；
2.二等奖：平均成绩75分以上，单科成绩不得低于70分；
3.三等奖：平均成绩70分及以上，单科成绩不得低于70分；
4、不符合条件返回空格
空格不统计，比如9门课程，有些只有7门课程成绩，只按照7门课程进行统计


源文件：

痛痛痛痛

想用下面公式，结果出错，用average函数会自动忽略空格，但是and函数无法忽略空格

=IFS(AND(AVERAGE(B2:J2)>=80,AND(B2:J2>=70)),"一等奖",AND(AVERAGE(B2:J2)>=75,AND(B2:J2>=70)),"二等奖",AND(AVERAGE(B2:J2)>=70,AND(B2:J2>=70)),"三等奖")

良良

K2=IF((SUM((B2:J2>=70)*(B2:J2<>""))<>COUNTA(B2:J2))+(AVERAGE(IF(B2:J2,B2:J2))<70),"",LOOKUP(AVERAGE(IF(B2:J2,B2:J2)),{70;75;80},{"三";"二";"一"})&"等奖")，数组三键。

痛痛痛痛

良良 发表于 2023-4-19 12:35
K2=IF((SUM((B2:J2>=70)*(B2:J2""))COUNTA(B2:J2))+(AVERAGE(IF(B2:J2,B2:J2))

公式修改了？
原来的公式结果是对的，SUM((B2:J2>=70)*(B2:J2<>""))=COUNTA(B2:J2)，这个判断条件，就可以判断是是否能获得奖学金，然后再通过平均分去查找等级。结果我测试没有问题。


此外，AVERAGE(IF(B2:J2,B2:J2))，这个地方是求平均值，现在的average函数自动忽略空格，应该不用if转换了吧

w4275

1. =IFNA(LOOKUP(IF(AND(IF(B2:J2<>"",B2:J2)>=70),AVERAGE(B2:J2),""),{70,75,80},{"三","二","一"})&"等奖","")

复制代码

xzlu13014

=LOOKUP(AVERAGE(B2:J2)*1*(SUM(IFERROR(IF(B2:J2<>"",B2:J2,"")*1>=70,0)*1)>=COUNTIF(B2:J2,"<>")),{0,70,75,80},{" ","三等奖","二等奖","一等奖"})


=IF(SUM(N((B2:J2<>"")*(B2:J2<70)))>=1,"",LOOKUP(AVERAGE(B2:J2),{70;75;80},{"三";"二";"一"})&"等奖")

橒♂蝣

=LET(x,SUM(B2:J2)/COUNT(B2:J2), y,SUM((FILTER(B2:J2,B2:J2<>"")<70)*1)=0, rr,XLOOKUP(x*y,{80,75,70,0},{"一等奖","二等奖","三等奖",""},"",-1),rr)

疏且浅

=IFERROR(IFS(AND(AVERAGE(B7:J7)>80,MIN(B7:J7)>=70),1,AND(AVERAGE(B7:J7)>75,MIN(B7:J7)>=70),2,AND(AVERAGE(B7:J7)>70,MIN(B7:J7)>=70),3),"")