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

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,=80)差生人数：=COUNTIF(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,合格,待合格)1、2、3、4要与优、良、合格、待合格相对应=CHOOSE(IF(A1=80,1,IF(A1=70,2,IF(A1=60,3,4),优,良,合格,待合格)这两个函数指不录入成绩时，单元格为空。录入0时，评定为待合=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(A1=90,优,IF(A190,良,if(a170,中,if(a1=90,C5=80,C5=70,C580),C,IF(C5=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的格式。补充说明：注意检查括号是否成对出现其余的为18位的身证，就从第9位开始提取8位是15位的，就从第7位开始，提取6位（1）=IF(LEN(A1)=15，MID（A1，7，6），MID（A1，7，8）)注意检查括号是否成对出现判断是几位的身份证提取后格式为670520或19670520。将多个文本字符窜成一个（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.057、根据身份证号码自动录入男女：=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()-MID(D4,7,4),YEAR(TODAY()-MID(D4,7,2)-1900)9、学生年龄的公式应用：=(YEAR(B2)-YEAR(A2)+IF(MONTH(B2)-MONTH(A2)=60,B2:B5,=60)可计算语文合格，数学也合格（双科合格）的人数。和=SUMPRODUCT(C4:C18=80)*(D4:D18=80)是一样的。 但是在Excel 2003中没有COUNTIFS函数，在WPS20007、excel2007以上版本才有。12、计算符合“男生、4岁”这两个条件的单元格的个数：=SUMPRODUCT(C479:C493=男)*1,(E479:E493=4)*1) 用SUMPRODUCT函数代替COUNTIFS。符合多个条件的，方法相同。（1）还可以用：=SUM(IF(C88:C102=男)*(E88:E102=4),1,0) 注意: 1、输入完公式后要按Ctrl+Shift+Enter键,让它自动加上数组公式符号。2、也可以用于双科合格率的人数统计，还可以是符合多个条件的单元格人数统计。3、文本的参数加 ,数字的参数不加 。4、年龄不能用文本格式，用常规，其它的符号如“”用文本格式。（2）还可以使用COUNT函数和IF函数嵌套计算：=COUNT(IF(B1:B380)*(C1:C380)*(D1:D380)*(E1:E380)*(F1:F380),A1:A3)，B语文C数学D物理E化学F英语A学生姓名，按Ctrl+Shift+Enter键,让它自动加上数组公式符号。大于0且小于30的单元格个数=SUM(IF($V$4:$V$750),1,0)=COUNTIF(V4:V75,=0)-COUNTIF(V4:V75,=30)=SUMPRODUCT(V4:V750)=SUM(V4:V750)*(V4:V7520,B2:B21,30)区域的条件是文字区域的条件是数字15、COUNT和COUNTA公式的区别（都是求录入内容的单元格的个数）16、将两个单元格A1、B1的内容合并到一个单元格C1：C1输入=A1&B1将一个单元格A1的内容如“福建古田杉洋”，分解到两个单元格B1、C1： B1输入=LEFT（A1，2），C1输入=RIGHT（A1，4）左2个字符右4个字符17、平方、立方的计算：=52, =53 也可以用函数=POWER（4，2）计算4的平方。今年的年月日或用TODAY（）或设定截止时间。18、时间显示牌：如距放假还有 =DATE(2013,6,29)-TODAY()&天19、计算教龄：=DATEDIF(A1，B1，”Y”)参加工作时间代表年，改为“M”则算月，改为“D”则算天工作时间=出生的日期单元格TODAY（）差生的日期放置的单元格20、计算年龄=YEAR($A$1 )-YEAR（B1） 不计月份 年龄的单元格要设置为常规21、生日提醒函数：=IF(AND(MONTH(TODAY()=MONTH(E3),DAY(TODAY()=DAY(E3),生日,非生日)生日显示，非生日不显示出生的日期单元格TODAY（）差生的日期放置的单元格=IF($A$1=DATE(YEAR($A$1),MONTH(B1),DAY(B1), 生日， )22、