excel函数在学生成绩统计中的应用

1、excel 函数在学生成绩统计中的应用Excel 俗称电子表格，是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函数和数据库，本文仅介绍用Excel2KX进行成绩统计和一些日常统计的函数。一考试成绩的统计（一）对班学生成绩的总体评估统计1. 假设E4： E53 单元格存放某班学生的语文考试成绩，若要计算该科的总分、人平分、及格人数、及格率、优秀人数、优秀率、最高（低）分、分段统计等。（ 1）参考人数统计：若要将参考人数放在 E54单元格中，只需选定该单元格并插入“计数函数”：“COUNT'并选定要计数的单元格区间E4:E53或（输入）公式："=COUNT（E4:E5

2、3）回车即可；（ 2）学科总分的统计：若要将该科的总分存放在E55 单元格中，只需选定该单元格并插入 “求和函数” “： SUM”并选定要计数的单元格区间E4:E53或（输入）公式：“二SUM （E4:E53）'回车后即可计算出该科总分（注：在E55 单元格只显示计算结果。如要修改该公式，只需选定该单元格，在编辑栏内即可修改）。（ 3）学科平均分的统计：若要将该科人平分存放在E56单元格中，选定该单元格插入平均值函数“ AVERAGE并选定求值区间“E4: E53”或输入公式："=AVERAGE（E4E53）”，回车后即可计算出人平分 （注： 数组或单元格引用中的文字、逻辑值

3、或空白单元格将被忽略，但单元格中的“0”则参与运算 ）。（ 4）学科及格人数统计：若要将该科的及格人数存放在E57 单元格中，选定该单元格插入计算某个区域内满足指定条件的单元格数目函数的条件函数：“COUNTIF',插入函数的方法是单击“插入”菜单中的“插入函数（F） , ”命令，弹出一个“插入函数”对话框，在其中的“选择函数”下的列表框中选定选定" COUNTIF函数，单击“确定”，弹出一个“函数参数”指定对话框，在 其中的“ Range”右边矫形框中输入统计的区间 E4： E53或（单击矫形框右端的压缩图标， 再在工作表中选择统计区间 E4: E53,再单击“函数参数”压

4、缩框中右端的扩展图标，返回 扩展的“函数参数”对话框中） ，再单击对话框中的“Criteria ”右边的矩形框，并键入及格条件：“>=60”，最后单击“确定”即可；也可以手动编辑输入公式及条件：“=COUNTIF（E4E53, >= 60）”回车，即可计算出该科的及格人数（空白单元格、文字、逻辑值、错误值均被忽略）。（ 5）学科及格率统计：若要将该科的及格率存放在E58中，选定该单元格并输入公式“=COUNTIF（E4 E53, >=60）/ COUNT （E4: E53）”回车（COUNT为计数函数，用法是“计数函数”加上“计数区 间”），即可计算出该科的及格率 （其中“C

5、OUNT （E4 E53）”是t算E4: E53区间内单元格中 的实际参考人数，空白单元格将被忽略）。（ 6）学科优生人数统计：若要统计学科优生人数与学科及格人数统计类似。如：将该科的优秀人数存放在E59单元格中，选定该单元格插入条件函数：“COUNTIF',方法与统计及格人数一样，只是函数参数对对话框中“Criteria”右边的矩形框中的函数参数要修改为：">=80”（优分标准）；（ 7）学科优分率统计：学科优生百分率的统计与学科及格率的统计相同。如：将该科的优秀率存放在 E60单元格中，选定该单元格并输入公式： "=COUNTIF（E4 E53, >

6、= 80）/COUNT （E4: E53）”。 这与及格率公式的编辑只是参数“>= 60”变成了 “>= 80”而已。求优秀率：例如：B2:B50存放的是初一一班的语文期末考试成绩，B2:B500存放的是初一全年级语文考试的成绩，如果规定全年级20%的学生为优秀，那么初一一班语文的优秀率应该这样计算：T(B2:B50)”， 其中所求的是全年级语文分数前20%中最低的一个同学的分数，COUNTIF(B2:B50,">="&&LARGE(B2:B500,INT(0.2*COUNT(B2:B500)则是求出了初班语文 高于或等于这个同学分数的人数

7、，最后再除以初一一班的总人数COUNT(B2:B50)所得就是初一一班的语文优秀率，如果想求其他班其他科目的优秀率，道理都是一样。( 8)学科最高分的查询统计：若要查询统计学科最高分，则可利用“ MAX”(最大值)函数。如将E4: E53区域中存放 着的全班50名学生语文科的考试成绩，将其学科最高分、最低分别存放在E61和E62单元格中，则可选中 E61单元格，插入“最大值”函数： “MAX”并指定统计数据的区域：“E4: E53”或在编辑栏输入公式“= MAX(E4: E53)”回车后即可计算出其中的最高分。( 9) 学科最低分的查询统计：如将E4： E53 区域中存放着的全班50 名学生语

8、文科的考试成绩，将其学科最低分别存放在E62单元格中，则可选中 E62单元格，插入“最小值”函数：“MIN”并指定统计数据的区域：“E4: E53”或在编辑栏输入公式“= MIN(E4: E53)”回车后即可计算出其中的最低分。(10)若在“ F4:F53"、“G4:G53”，“ K4:K53'区域中分别存放数学、英语、计算机、音、体、美等学科成绩，则只需选定E54： E62 区域，移动空心十字光标至单元格右下角。待光标变成小实心十字光标时，按住鼠标左键沿行拖动控制手柄至K列即可，放开鼠标左键后，则各科的科总分、人平分、及格人数、及格率、优秀人数、优秀率、最高分、最低分等 立

9、即计算出来。2. 班学生成绩的分段统计：若要将学生成绩按分数段进行统计，则可利用FREQUENCY数(以一列垂直数组及对该数组设定进行频率颁布计算的分段点计算出一组数据的频率颁布)。 假设E4： E53 共有 50 个学生的语文科测试成绩，那么可在B66： B70 单元格分别输入59、 69、 79、 89、 100，则表示统计59(包括59)分以下、6069、7079、8089、90100 分数段的成绩个数。若要将计算结果存入 C66: C70,则选中C66: C70单元格区域，在编辑栏内输入公式 “= FREQUENCY (E4 E53, $B$66: $B$70)”，(注：公式中区域：“

10、E4: E53'是用来计算频率的 数组区域一一班学生学科成绩的单元区域，公式中区域：“$B$70: $B$70”是对所计算频率数组班学生学科成绩进行频率计算的分段界点的数组区域的绝对引用) ，输完后让光标 停在公式末尾，按下 SHIFTS CTR逊敲回车(这是生成数组公式的关键环节)，Excel会自动在公式两侧加上大括号，公式成为“= FREQUENCY(E4E53, $B$66: $B$70)”则区间C66:C70各单元格中立刻计算出对应各分数段中成绩的个数。(二)对班中学生个体成绩的评估统计1. 学生个体总成绩的统计：若在工作表中E4： K53 区域存放某班50 个学生各科的成绩，

11、而 E4:K4、 E5： K5、 , E53：K53中分别各个学生语文、数学、 ，、美术等各科成绩记录。则需在L列中的L4: L53区间 单元格中分别存放各学生的各门学科成绩总分，则先选定L4单元格插入求和函数“SUM”并选定求和区域回车或手动输入公式“=SUM (E4:K4)”回车即可计算出该生各科的总分；然后选定L4 单元格，并将鼠标指针指向选择框右下角的控制柄并沿列拖动鼠标至L53 即可算出全班其余学生各科的总分。若某生未考试，但不希望在其总分单元格填入“0”， 只需将公式修改为“=IF(ISNUMBER(E4 L4), SUM(E4: L4), )”回车，单元格中会填入空格而 不会出现

12、“0”。2. 学生个体班级名次的统计：若要将该班学生的班级排名计算出来，但不希望打乱学生原来按学号的顺序，则可利用RANK函数轻松完成，该函数的最大优点是，可以处理重名次，可使数值相同的单元格计算 出的名次相同。如上例在L4： L53 区域中存放某班学生的总分，要将学生在班级排名的结果放在 N4: N53区域中，只需在 N4中输入公式“= RANK(L4, $L$4: $L$53)”回车，即可计 算出该生在班级中所排名次，然后沿列拖动复制，即将全班学生的排名计算出来。当有并列重复名次时，并列名次后缺少名次数等于名次并列重复数。如果某些学生未参加考试，又不希望在其存放名次的单元格内显示出错信息(

13、如：#VALU日),则可将公式修改为 “=IF(ISNUMBER(L4) RANK(L4, $L$4:$L$53), )”。在计算的过程中我们需要注意两点：首先当RANK函数中的Number不是一个数时，其返回值为" #VALUE!,影响美观。另外，Excel有时将空白单元格当成是数值“ 0”处理，造 成所有成绩空缺者都是最后一名，看上去也很不舒服。此时，可将上面的公式 “ =RANK(E2,$E$2:$E$50”) 改为 “ =IF(ISNUMBER(E2),RANK(E2,$e$2:$E$50),""”)。 其含义是先判断E2单元格里面有没有数值，如果有则计算

14、名次，没有则空白。其次当使用RANK函数计算名次时，相同分数算出的名次也相同，这会造成后续名次的空缺，但这并不影响我们的工作。 同样的道理，我们也可以算出一个学生的总分在年级内的名次以及各科的班名次和年级名次，但是必须注意参与计算的数字单元格区域不一样。3. 学生个体等级评定成绩的统计：如学校采用等级评定考试成绩，若标准为：考试各科平均分数高于或等于85分为A等；考试分数低于85 分且高于或等于70 分为 B 等； 考试分数低于70 分且高于或等于60 分为 C等；考试分数低于60 分为 D 等；没有参加考试的不划等级。假设平均分数存放在M 列的M4:M53区域，计算结果存入。列O4:O53区

15、域，则用Excel计算等第的公式如下：“=IF(M4 >=85, A，IF(M4>=70, B，IF(M4>=60, C，IF(ISNUMBER(M4), D， IF(ISBLANK(M4), )”，(公式中的等级代码为大写时显示大写，小写时显示小写，其余字母不区分大小写)这是一个IF函数嵌套公式，式中第二个IF语句是第一个IF语句的参数，第 三个IF语句则是第二个IF语句的参数，以此类推。如果第一个逻辑判断表达式M4>=85为TRUE真)，则O4单元格式被填入“ A”；如果为FALSEg),则计算第二个IF语句“ IF(M4 >=70"；以此类推直至计

16、算结束。其中ISNUMBER函数在M4为空时返回FALSEg),接着执行最后一个IF语句，否则在O4单元格中填入“D”°ISBLANK!数在M4为空时返回TRUE真)， 则在O4单元格中填入一个鉴定会格。使用ISNUMBER函数和ISBLANK0数，可防止某个学生没有参加考试(即考试成绩为空)，但仍然给他评定为D 等的情况发生。计算其他学生的成绩等级时，选中O4,鼠标指向选定框右下角的控制手柄并拖动到O53单元格将公式复制即可。如果成绩等级划分标准发生了变化，只须改变逻辑判断式中的值(85， 70， 60)即可，也可将等级代码“ A、B、C、D”分别换成“优秀、良好、及格、不及格”等

17、。一建立一个成绩统计表格启动 Excel建立一个如下图一表一 II样式的表格，在第一行至第三行输入下图所示的文字， 按学生数预留表格的行数。以班额 29 人为例，预留 4 32 行。 从 33 行开始向下一行依次输入考试人数、总分、平均分、最高分、最低分、及格人数、及格率、优秀人数、优秀率。未命名 .JPG二输入函数公式，实现自动统计功能我们先以一语文II学科的成绩统计为例。1、考试人数的统计一COUNTII是计算包含数字单元格以及参数列表中数字的个数的函数，在C33单元格内 输入一=COUNT(C4:C32)| (引号内的函数式，以下亦同)，就可以在输入学号或姓名后显示 参加考试的人数。2

18、.总分和平均分的统计总分和平均分是平行班之间学生成绩分析的主要数据，也是一个班级学习质量升降的重要指标。一SUM II能 够计算单元格区域中所有数 值之和，在 C34单元格内输入一 =SUM(C4:C32)|计算出语文成绩的总分。-AVERAGEI是计算数值的算术平均值，在 C35单元格内入一= AVERAGE(C4:C32)1 可以计算语文成绩的平均分。 有时候结果会出现循环小数， 我们可以设定位数， 一ROUND | 按指定的位数对数值进行四舍五入。在C35单元格内输入一=ROUND(AVERAGE(C4:C32),1)可以将统计结果设定为保留小数点后一位的数值。3 .最高分和最低分的统计

19、最高分与最低分的统计能够看到同年级的学生成绩的差距，用排序的方法很麻烦。一MAX | 一 MIN |是返回数据区域的最大、最小数值的函数。在C36单元格内输入一=MAX(C4:C32) | ,在C37单元格内输入一=MIN(C4:C32) |就可以显示出 C4至C32内29个单 元格内数据的最大、最小数值，即语文成绩的最高分和最低分4 .及格人数的统计和及格率的计算统计及格的人数一般我们采用找出60 分以上的成绩，再统计人数。有一个函数可以自动统计出及格的人数，那就是一COUNTIF II ,在 C38单元格内输入一 =COUNTIF(C4:C32,">=60")|可

20、以统计 30个单元格内的不小于 60分成绩的的人数。 在C39单 元格内输入一=C38/C33 II可以计算语文的及格率。输入一 =ROUND(AVERAGE(C38/C33*100),1)&"%') II显示的结果是保留小数点后一位的百分数。5 .优秀人数和优秀率的统计在C40单元格内输入一 =COUNTIF(C4:C32,">=90")|可以统计不小于 90分成绩的人数， 就是优秀学生的人数。 根据学校不同的优秀标准，改动一90 II即可。在C41单元格内输入一=ROUND(AVERAGE(C40/C33*100),1)&&quo

21、t;%") II计算出结果为保留一位小数的百分数，就是语 文学科的优秀率。6 .个人的总分和名次的统计虽然学校不允许给学生排名次，但是任课教师需要掌握学生的测试名次，分析学生学习的变化情况。在H4单元格输入函数公式一= SUM(C4:G4) | ,可以计算出第一位学生的所有成绩的总 分。向下拖拉时要出现十字架才能拉。J3单元格内输入函数一= RANK(J4 $J$4: $J$115)| ,显示第一位学生总成绩在班级的名次。利用一自动填充|1功能，输入其他学科的统计函数公式，这样就完成了能够自动统计学 生成绩的模板，你马上输入一组数据试一下，是不是很快捷。根据许多学校的统计要求，可以在

22、以上电子表格模板中增加以下的功能：7 .分数段人数统计在C42单元格内输入一= COUNTIF(C4:C32,">=90")|统计成绩为90分及以上的人数。在 C43单元格内输入一 =COUNTIF(C4:C32,">=80")- COUNTIF(C4:C32,">=90") II 统计 80 89 分的人数。此公式是利用80 分及以上人数与90 分及以上人数的差计算出本分数段的人数。同理，在 C44 单元格输入一=COUNTIF(C4:C32,">=60") - COUNTIF (C4:C

23、32,">=80")| 计算 6079分的人数。C45单元格输入一=COUNTIF(C4:C32,"<60") II统计不及格人数。8 .小学双科成绩统计上级业务部门要求统计小学语文、数学双科及格率和优秀率。原始的方法是把学生成绩按从大到小排序，费一番周折找到双科均合格的学生数，再进行计算。这里我们可以利用函数迅速找到相关的数字，省去这些劳神之事。在 L4 输入 =COUNTIF(C44,">=60") II表示两个单元格内及格的科目数。显示一1 II表示只有一科及格，一2 |表示双科均及格，利用一填充柄II在L列向

24、下复制函数，得到班级所有学生双科及格情况。在C46输入一=COUNTIF(L432,"=2")|自动统计出语文、数学双科及格的人数。在 C47输入一 =ROUND(L46/C33*100,1)&"%" II就可以轻易得到双科及格率的数据。同样的方法，在 M4输 入一=COUNTIF(C43,">=90") II显示语文、数学双科达优秀的科数，在 C48输入一 =COUNTIF(M4:M32,"=2") | , C49 输入一=ROUNDM(C48/C33*100,1)&"%"

25、; II 统计出双科的优秀 人数和优秀率。为了不因学生缺考一科时统计出现错误，在L33输入一二IF(C33>=D33,"人数正确","人数错误，请核查")，当语文、数学科参加考试的人数不一致时，会出现相应提示。如果班额较大，只要在A4 至 A32 之间插入相应数量的行， H 至 M 列出现的空白，可以利用口填充柄一自上向下复制相应函数即可，其他统计函数会自动调整。至此，小学阶段要求统计的数据均可以利用这个电子模板自动统计出来。三制作及使用经验：1 .表格要预留不少于班额的的行数。可以制作较少行数的表格，输入全部函数公式后，再插入相应数量的行。为了便

26、于全校教学班级学生考试数据的输入，模板要按学校最大班额设计。2 .设置相应的列宽，以适应数字位数和文字数。3 .利用一自动填充II功能，省略输入函数式的麻烦。 上面只介绍了语文学科的数据统计，只要同时选中C33 至 C41， 鼠标指向右下角看到空心十字变为黑十字，按住左键别放向右拖动四格，就可以得到数学等四学科的统计数据了。选中 H4、J4向下拖动之J32得到每位学 生的总分和名次。4 .要求学生考试时填好学号(或考号)，方便按顺序录入数据。在学校局域网上，各班级填完模板中学生成绩后，所有学生成绩统计的数据都会立刻显示，也可以要求教师上报学生考试成绩的数据，填充到相应的工作表中，能自动完成统计

27、。把完成数据统计后的电子簿另存为一个文件名，例如一2008下学期期末成绩统计表II,设置工作表一保护II ,将原工作簿学生成绩清空，下次可以继续使用，做到一一劳永逸II。1 .考试人数的统计=COUNT(E4:E115)2 .总分和平均分的统计=SUM(E4:F115)计算出语文成绩的总分。=ROUND(AVERAGE(I4:I115)，侬以计算语文成绩的平均分3 .优秀人数和优秀率的统计一=COUNTIF(I4:I115,">=43") II可以统计不小于 90分成绩的人数=ROUND(AVERAGE(E125/E120*100),1)&"%")计算出结果为保留一位小数的百分数，就 是语文学科的优秀率。4 .个人的总分和名次的统计一=COUNTIF(C44,">=60")|表示两个单元格内及格的科目数。 显示一1 八表示只有一科及格，一2 |表示双科均及格，利用一填充柄II在 L列向下复制函数， 得到班级所有学生双