Excel学生成绩统计常用公式

1、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:C18Z">=80")60-69 分：=COUNTIF(C4:C18/,>=60")-COUNTIF(C4：C18/">=70

2、")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,04:013)总分：=SUM(C4:C18,G4:G

3、18,K4:K18,04:013)平均分：=AVERAGE(B4:D4)3、与考人数、记分人数：=COUNT(C4:C18,G4:G18ZK4:K18,04:018)优生人数：=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)假设

4、B列存放学生 的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生 的成绩之和；5、学生分数转化为等级评定：=IF(A4>=80，”优”,IF(A4>=70,“ 良”,IF(A4>=60,“合格，“待合格”)二 CHOOSE(IF(A1>=80,1JF(A1>=70,2,IF(A1>=6034),''优"，"良合格=”待合格”)1、2、3、4要与优、良、合格、待合格相对应合”,IF(ISBLANK(C4)J")二 IF(C4>=80, “优”,IF(C4>=70，”良”,IF(C4>=6

5、0,“合格”,IF(ISNUMBER(C4)J 待这两个函数指不录入成绩时，单元格为空。 录入。时，评定为待合=LOOKUP(A1,0,60,70,80,"D"/"C","B"；,A") = 1_001<1/1,0,60,70,80,90,不及格”,”及格”,中,良”,'优")=IF(E4<60；'D',JF(E4<70,C,/IF(E4<80/"B"/"A")二 if(Al<60,”E“,if(Al<70JD”,i

6、f(Al<80JC”,if(Al<9(VBTA”)=IF(A1<6OJ 不及格”,IF(Al<70,“及格”,IF(A1<8OJ 中”,IF(Al<90,“良“，”优 ")=IF(Al>=90，“优”,IF(Al<90,“ 良”3的1<70,“中”,if(al=<60,“及格，"很差")=IF(AND(C5>=90/C5<=100)/"A",IF(AND(C5>=80/C5<90)；,B"JF(AND(C5>=7 0,C5<80),&quo

7、t;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"zIF(E2>=60；,B","C")=IF(C2>=80/"A"/IF(C2>=60,"B"

8、z"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的格式。补充说明：提取后格式为670520

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

10、ot;女"J男”)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要设置成：格式一单元格一数字,选择“数值”, 并将“小数位数”设为0,其中IF语名是判断学生的出生月份是否超过8月，如 超过则要用年份之差再减1。第一个学生年龄计算出来后，将鼠标移到此单元格 的右下角，光标变成“ + ”后，向下拖动复制单元

11、格，快速完成每个学生“年龄” 的计算。= 亍 T 史 J / 3彩包品 口 2a / 1 7 TM 工 2灯曲1。族C2/ =(YEAR (B2) -YEAR (A2) ) +1F ( (MONTH (B2) -flONTH (A2) ) <0, -1. 0)ABCDEFGHI231967-05-201967To - 202013-08-312013-08-31464541988-11-152013-08-312456一 10、男女混合的表格中，计算男或女的年龄和(条件求和)=SUMIF(C5:C19,"",E5:E19)11、计算男的个数(计算区域内符合条件的个数)

12、 =COUNTIF(C479:C493,"55")COUNTIFS符合多条件的个数，如，=COUNTIFS(A2:A5,“>=60，B2:B5,“>=60”)可计算语文合格,数学也合格 (双科合格)的人数。n=SUMPRODUCT(C4:C18>=80)*(D4:D18>=80) 是一样的。但是在Excel 2003中没有COUNTIFS函数，在WPS20007、 excel2007以上版本才有。12、计算符合“男生、4岁”这两个条件的单元格的个数:=SUMPRODUCT(C479:C493="")*1,(E479:E493=4)

13、*1)用SUMPRODUCT函数代替COUNTIFS。符合多个条件的，方法相同。(1)还可以用：=SUM(IF(C88:C102二”男")*(E88:E102=4),l,0)注意：1、输入完公式后要按Ctrl+Shift+Enter键,让它自动加上数组公 式符号”“。2、也可以用于双科合格率的人数统计，还可以是符合多 个条件的单元格人数统计。3、文本的参数加"数字的参数不加” 4、年龄不能用文本格式，用常规，其它的符号如用文本格式。(2)还可以使用COUNT函数和IF函数嵌套计算：二COUNT(IF(B1:B 3>80)*(Cl:C3>80)*(Dl:D3>

14、;80)*(El:E3>80)*(Fl:F3>80),Al:A3),B语文C数学D物理E化学F英语A学生姓名，按Ctrl+Shift+Enter 键,让它自动加上数组公式符号”/大于0且小于30的单元格个数/=SUM(IF($V$4:$V$75<30)*($V$4:$V$750),l,0)/uCOUNTlFWdA'yCOUNTlFWdMSjyO11) / =SUMPRODUCT(V4:V75<30)*(V4:V75>0)Q=SUM(V4:V75>0)*(V4:V75<30)13、直接计算女生平均年龄的三种方法：二 SUMIF(C2:C21,“女

15、”,B2:B21)/COUNTIF(C2:C21J 女”)二 AVERAGEIF(C2:C21J 女”,B2:B21)二 AVERAGE(IF(C2:C21 二”女，B2:B21)1 /性别区域：1%计算与 30岁年龄段步晨飞12的两个公式也行)=COUNTIFS(C2:C21J 女”,B2:B21J>20)B2:B21J<30”)15、COUNT和COUNTA公式的区别(都是求录入内容的单元格的个、 pt数) 区域的条件是数域的条件是文字16、将两个单元格Al、Bl的内容合并到一个单元格Cl：Cl 输入=A1&B1将一个单元格Al的内容如“福建古田杉洋”，分解到两个单元格Bl、Cl： B1 输入=LEFT (Al, 2), Bl 输入二RIGHT (Al, 4) .k)k_)YY左2个字符右4个字符17、平方、立方的计算：二5A2,二5A3也可以用函数二POWER (4, 2)计算4的平方。18、19、计算教龄:工作时间二时间显示牌：如距放假还有 二DATE(2013,6,29卜TODAYOG,天,不计月份20、计算年龄=YEAR($A$1 )-YEAR ( Bl )年龄的单元格要设置为常规TODAY ()差生的 日期放置的单元出生的日期单元 格21、