Excel学生成绩统计常用公式.docVIP

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列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；=CHOOSE(IF(A1=80,1,IF(A1=70,2,IF(A1=60,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(E460,D,IF(E470,C,IF(E480,B,A))) =if(A160,E,if(A170,D,if(A180,C,if(A190,B,A)))) =IF(A160,不及格,IF(A170,及格,IF(A180,中,IF(A190,良,优)))) =IF(A1=90,优,IF(A190,良,if(a170,中,if(a1=60,及格,很差))) =IF(AND(C5=90,C5=100),A,IF(AND(C5=80,C590),B,IF(AND(C5=70,C580),C,IF(C570,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,C)) 6、18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)) 格式为yyyy-m-d，可更改为yyyy-mm-dd的格式。 补充说明： （1）=IF(LEN(A1)=15，MID（A1，7，6），MID（A1，7，8）) 提取后格式为670520 （2）如果身份证都是18位的，也可能直接用=MID（A1，7，8） （3）提取后的出生日期是连续的，可以中间加“-”或“.” =CONCATENATE（LEFT（A1，2），”年”，MID（A1，3，2），”月”，RIGHT（A1，2），”日”） 格式为67年05月20日，年月日换成“-”就成为67-05-20 （4）也可以从18位身份证号码中直接提取 =CONCATENATE(MID(A1,9,2)，.，MID(A1,11,2)) 格式为67.05 7、根据身份证号码自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)=0,女,男) 8、根据身份证号码转化在年龄：（适合于不计月份的） =YEAR(TODAY())-VALUE(MID(A1,7,4)) 第2种 利用身份证号码生成年龄： =IF(LEN(D4)=18,YEAR(TODAY())-MI