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、条件求和：=SUMIF(B2:B56," 男"，K2:K56) ——假设 B 列存放学生的性别， K 列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；5、学生分数转化为等级评定：=IF(A4>=80,"优",IF(A4>=70,"良",IF(A4>=60,"合格"," 待合格 "))) =CHOOSE(IF(A1>=80,1,IF(A1>=70,2,IF(A1>=60,3,4))),"优"," 良"," 合格"," 待合格 ") 1、 2、3、4 要与优、良、合格、待合格相对应=IF(C4>=80,"优",IF(C4>=70," 良",IF(C4>=60,"合格",IF(ISNUMBER(C4)," 待合",IF(ISBLANK(C4),""))))) =LOOKUP(A1,{0,60,70,80},{"D","C","B","A"}) =LOOKUP(A1,{0,60,70,80,90},{"不及格 "," 及格"," 中"," 良"," 优"}) =IF(E4<60,"D",IF(E4<70,"C",IF(E4<80,"B","A"))) =if(A1<60,"E",if(A1<70,"D",if(A1<80,"C",if(A1<90,"B","A")))) =IF(A1<60," 不及格 ",IF(A1<70," 及格",IF(A1<80," 中",IF(A1<90," 良"," 优")))) =IF(A1>=90,"优",IF(A1<90," 良","if(a1<70," 中",if(a1=<60,"及格","很差"))) =IF(AND(C5>=90,C5<=100),"A",IF(AND(C5>=80,C5<90),"B",IF(AND(C5>=70,C5<80),"C",IF(C5<70,"D",E)))). 多科目等级评定：=CONCATENATE(IF(C2>=80,"A",IF(C2>=60,"B","C")),IF(D2>=80,"A",IF(D2>=60,"B","C")),IF(E2>=80,"A",IF(E2>=60,"B","C"))) =IF(C2>=80,"A",IF(C2>=60,"B","C"))&IF(D2>=80,"A",IF(D2>=60,"B","C"))&IF(E2>=80,"A",IF(E2>=60,"B"...
