Excel学生成绩统计常用公式

1、Excel学生成绩、各种报表统计常用公式1、分数段人数统计90-100分：二COUNTIF(C4:C28/二90”)80-89分：二COUNTIF(C4:C18,”=80“)-COUNTIF(C4:C28,“二90”)70-79分：=COUNTIF(C4：C18/,=70,)-COUNTIF(C4:C18；,=80)60-69分：二COUNTIF(C4:C18,”x60“)-COUNTIF(C4:C28,“二70”)50-59分:=COUNTIF(C4:C18/=50,)-COUNTIF(C4:C18；,=60)41-49分：二COUNTIF(C4:C18,”40”)-COUNTIF(C4:C

2、1&”二50”)40分及以下：=COUNTIF(C4:C18；,=40n)2、最高分：=MAX(C4:C18/G4:G18/K4:K18,04:013)最低分：=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:G18ZK4:K18,O4:O18)优生人数：二COUNTIF(C4:C18,”x80“)差生人数：=COUNTIF(C4:C18/=60)*(D4:D28二60)双科优秀人数：二SUMPRODUC

3、T(C4:C28=80)*(D4:D18=80)4、条件求和：=SUMIF(B2：B56；男“，K2:K56)假设B列存放学生 的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生 的成绩之和；5、学生分数转化为等级评定:=IF(A4=80/优”,IF(A4二70,”良“,IF(A4=60,“合格”，”待合格“)二CHOOSE(IF(Al=8(UF(Al=702IF(Al=6034)r优T良T合格丁二IF(C4*8(V优,F(C4=7(V，良,F(C4x6(V合格l,F(ISNUMBER(C4)/待这两个函数指不录入成绩时，单元格为空 录入o时，评立为待合二LOOKUPSbQGCVOK

4、OLCDTCTBTA”)=LOOKUP(Al/0/60/70/80,90/11不及格罗及格T中T良丁优) 二IF(E4v6(VD： IF(E4v70C,F(E4v8(VBTA”)二if(Alv60ETf(A2v7(V，DTf(A：lv80rC,f(Alv90rBTA”)=IF(Al60,不及格”,IF(Alv70,”及格JFfAKSO；*中”,IF(Alv90,良优“)二IF(A1X9(V ttlFfAKgO；1良 丁肝佃270中恥2二=90/C5=80/C5=70/C580)；,C,/IF(C5=80；,A,/IF(C2=60；,B,/C,)JF(D2=80；,A/IF(D2二60,“BTC

5、“),IF(E2=80/A“,IF(E2=60,”BTC”)待合格冷r 2、3. 4要与优、良、合格、待合格相对应合JF(ISBLANK(C4)J)二IF(C2=80,“A”,IF(C2=60,”BTC“)&IF(D2=80,“A”,IF(D2=6(VBTC”) &IF(E2=80；,A,JF(E2=60；,B,/,C)6、28位身份证号码转换成出生日期的函数公式： 如果E2中是身份 证， 在F2中求出出生日期,=DATE(MIDB(E2/7,4)/MIDB(E2/ll/2)/MIDB(E 2,13,2)格式为yyyy-m-d,可更改为yyyy-mm-dd的格式。补充说明：提取

6、后格式为670520或。(2)如果身份证都是18位的，也可能直接用二MID (Al, 7, 8)将多个文本字符竺二二巴提取后的出生日期是连续的，可以中间加或=CONCATENATE (LEFT (Al, 2),年”,MID (Al, 3, 2),月”,RIGHT (Al, 2),日)格式为67年05月20日，年月日换成 就成为67-05-20(4)也可以从18位身份证号码中直接提取=CONCATENATE(MID(A1?9,2), MID(A1,H,2)格式为7、根据身份证号码自动录入男女：=IF(MOD(IF(LEN(e2)=18/MID(e2,17zl),MID(e2,15,1) ,2)=

7、0,女“，”男“)8、根据身份证号码转化在年龄：(适合于不计月份的)=YEAR(TODAY()-VALUE(MID(A1,7,4)第2利|利用身份证号码生成年龄：=IF(LEN(D4)=18,YEAR (TODAY()-MID(D4,7,4),YEAR (TODAY()-MID(D4,7,2)-1900)9、学生年龄的公式应用:说明：生成年龄的单元格C2要设置成： 格式一单元格-数字,选择“数值”,并将“小数位数”设为 6 其中IF语名是判断学生的出生月份是否超过8月，如 超过则要用年份之差再减1。 第一个学生年龄计算出来后， 将鼠标移到此单元格 的右下角，光标变成“ + ”后，向下拖动复制单

8、元格，快速完成每个学生“年龄” 的计算。ABCDEFGH121 二60”,B2:B5,“二60”)可计算语文合格， 数学也合格 (双科合格)的人数。n=SUMPRODUCT(C4：C18=80)*(D4:D18=80)是一样的。 但是在Excel 2003中没有COUNTIFS函数， 在WPS20007、exce!2007以上版本才有。12、计算符合“男生、4岁”这两个条件的单元格的个数:=SUMPRODUCT(C479:C493=,男”)*1,(E479:E493二4)*2)用SUMPRODUCT函数代替COUNTIFSo符合多个条件的，方法相同。(1)还可以用：二SUM(IF(C8&

9、;C102=男“)*(E88：102=4),1,0)注意：2、输入完公式后要按Ctrl+Shift+Enter键,让它自动加上数组公 式符号“。2、也可以用于双科合格率的人数统计，还可以是符合多 个条件的单元格人数统计。3、文本的参数加“蔦数字的参数不加”“。4、年龄不能用文本格式，用常规，其它的符号如“丿”用文本格式。(2)还可以使用COUNT函数和IF函数嵌套计算:=COUNT(IF(B1:B380)*(Cl:C380)*(Dl:D380)*(El:E380)*(Fl:F380),Al:A3),B语文C数学D物理E化学F英语A学生姓名，按Ctrl+Shift+Enter键,让它自动加上数组

10、公式符号”“。/大于0且小于30的单元格个数/=SUM(IF($V$4:$V$75=0”)-COUNTIF(V4:V75,”=30“) /=SUMPRODUCT(V4:V750)冷=SUM(V4:V750)*(V4:V7520“,B2:B21,” Cl： Bl输入二LEFT (Al, 2), C2输入二RIGHT (Al, 4)“k_ _)k_)Y右4个字符17.平方.立方的计算：二5八乙也可以用函数二POWER (4, 2)计算4的平方。时间显示牌：如距放假还有=DATE(2013,6,29)-TODAY()&,天年龄的单元格要设置为常规TODAY ()差生的 日期放宜的单元 出生的日期单元 格21.生日提醒函数:二IF(AND(MONTH仃ODAY()二MONTH(E3),DAY(TODAY()二DAY(E3)生R左2个字符20、计算教龄:工作时间二计算年龄=YEAR($A$1 )-YEAR ( Bl )不计月份T非生日fl)22、电子表格的列中自动添