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)
平均分：=A VERAGE(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(ISNUMBE
R(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","C"))
6、18位身份证号码转换成出生日期的函数公式：如果E2中是身份
证,在F2中求出出生日期，=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MI
DB(E2,13,2)) 格式为yyyy-m-d ，可更改为yyyy-mm-dd 的格式。
补充说明： 这两个函数指不录入成绩时，单元格为空。录入0时，评定为待合
（1）MID（A1，7，8
提取后格式为670520或19670520。
（2）如果身份证都是18位的，也可能直接用=MID（A1，7，8）
（3）提取后的出生日期是连续的，可以中间加“-”或“.”
（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())-V ALUE(MID(A1,7,4))
第2种利用身份证号码生成年龄：
=IF(LEN(D4)=18,YEAR(TODAY())-MID(D4,7,4),YEAR(TODAY())-MID(D4,7,2)-1 900)
9、学生年龄的公式应用：
=(YEAR(B2)-YEAR(A2))+IF((MONTH(B2)-MONTH(A2))<0,-1,0)
设置截止时间的区域，也可以设为绝对引用，只用一个单元格为
截止时间。学生的年龄计算时，截止时间设为如2013-08-31，大人的年龄计算截止时间设为如2013-12-31。出生年月区域
判断是几位的身份证是15位的，就从
第7位开始，提取
6位
注意检查括号是
否成对出现
其余的为18位的
身证，就从第9位
开始提取8位
将多个文本字符窜成一个
