Excel学生成绩统计常用公式样稿

Excel学生成绩、多种报表统计常见公式1、分数段人数统计90-100分：=COUNTIF(C4:C18,">=90")80-89分：=COUNTIF(C4:C18,">=80")-COUNTIF(C

Excel 学生成绩、多种报表统计常见公式 1 、分数段人数统计 90-100= COUNTIF(C4:C18,">=90") 分： 80-89= COUNTIF(C4:C18,">=80")-COUNTIF(C4:C18,">=90") 分： 70-79= COUNTIF(C4:C18,">=70")-COUNTIF(C4:C18,">=80") 分： 60-69= COUNTIF(C4:C18,">=60")-COUNTIF(C4:C18,">=70") 分： 50-59= COUNTIF(C4:C18,">=50")-COUNTIF(C4:C18,">=60") 分： 41-49= COUNTIF(C4:C18,">40")-COUNTIF(C4:C18,">=50") 分： 40= COUNTIF(C4:C18,"<=40") 分及以下： 2= MAX(C4:C18,G4:G18,K4:K18,O4:O13) 、最高分： = MIN(C4:C18,G4:G18,K4:K18,O4:O13) 最低分： = SUM(C4:C18,G4:G18,K4:K18,O4:O13) 总分： = AVERAGE(B4:D4) 平均分： 3= COUNT(C4:C18,G4:G18,K4:K18,O4:O18) 、和考人数、记分人数： = COUNTIF(C4:C18,">=80") 优生人数： = COUNTIF(C4:C18,"<=40") 差生人数： = SUMPRODUCT((C4:C18>=60)*(D4:D18>=60)) 双科合格人数： =SUMPRODUCT((C4:C18>=80)*(D4:D18>=80)) 双科优异人数： 4 、 条件求和：假设列存放 男， ——B =SUMIF(B2:B56,""K2:K56) 学生性别，列存放学生分数，则此函数返回结果表示求该班男生成 K 绩之和； 、学生分数转化为等级评定： 5