【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《公式优生率》,欢迎阅读!
举例,如果C列从第2行到40行是成绩分数,则有: 总分公式:=SUM(C2:C40) 平均分公式:=AVERAGE(C2:C40)
优秀率公式(85分优秀为例):=COUNTIF(C2:C40,">=85")/COUNT(C2:C40) 及格率公式(60分及格):=COUNTIF(C2:C40,">=60")/COUNT(C2:C40)
将公式复制到你需要计算的格里即可。
80分以上人数: =countif(b2:b100,">=80")
=countif(b2:b100,">=80")-countif(b2:b100,">=90")
80分至90分人数: =sumproduct((b2:b100>=80)*(b2:b100<90))
同时显示二部、104分以上、重本的人员。
=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))
=IF(ISNA(VLOOKUP($D5,IF({1,0},表1!$L$2:$L$8000,表
1!D$2:D$8000),2,)),"",VLOOKUP($D5,IF({1,0},表1!$L$2:$L$8000,表1!D$2:D$8000),2,))
=IF(ISNA(VLOOKUP($D5,IF({1,0},表1!$L$2:$L$8000,表
1!A$2:A$8000),2,)),"",VLOOKUP($D5,IF({1,0},表1!$L$2:$L$8000,表1!A$2:A$8000),2,))
本文来源:https://www.wddqxz.cn/8f1a4fd94593daef5ef7ba0d4a7302768e996f8b.html