excel表格_excel函数公式大全_execl从入门到精通
用Excel表格做考试成绩分析2024-04-11 20:28:11
用Excel表格做考试成绩分析
EXCEL许多学校要对任课教师进行成绩统计，依据任课教师所教该学科的“三率和”即某科的平均分+及格率（及格人数/参加考试的人数*100）+优秀率（优秀人数/参考人数*100）进行教学评比。用Excel就可以轻松做到。只需将学生的考试成绩按照一定的格式录入，那么Excel就可以自动去完成计算三率和的工作。
下面我们通过一个实例来进行分析，比如录入18级某次考试成绩。
2018 级某次考试成绩录入结束后，利用 Excel 自带的一些函数，就能方便地把结果统计出来。
开始学习前，有必要了解一些Excel函数。公式和函数是Excel最基本、最重要的应用工具，是Excel的核心，应对公式和函数熟练掌握，才能在实际应用中得心应手。
1．数组公式的输入步骤：
（1）选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。（2）输入数组公式。（3）同时按“Crtl+Shift+Enter”组合键，则Excel自动在公式的两边加上大括号{}。
特别注意的是，第（3）步只有输入公式后同时按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。否则只按Enter键，则输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示计算结果，如下图所示。2．条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和，公式为：=SUMIF(range,criteria,sum_range)式中range用于条件判断的单元格区域，只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。如果省略sum_range，则直接对range中的单元格求和。
3．AVERAGE函数AVERAGE函数的功能是计算给定参数的算术平均值。公式为=AVERAGE(参数1,参数2,…,参数N)
4．COUNT函数和COUNTIF函数COUNT函数的功能是计算给定区域内数值型参数的数目。公式为=COUNT(参数1,参数2,…,参数N)COUNTIF函数的功能是计算给定区域内满足特定条件的单元格的数目。公式为=COUNTIF(range,criteria)式中range需要计算其中满足条件的单元格数目的单元格区域；criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。
有了这些基础知识以后，下面，笔者就将这一过程详细展现给各位老师。首先，新建一个EXCEL文档，将文档的第一个工作表sheet1重命名为“成绩”，将第二个工作表sheet2重命名为“统计”，如下图所示。回到成绩工作表中，从第一个单元格开始，依次输入年级、班级、学号、姓名、语文、数学、英语、物理等科目……然后选择“年级”所在A2单元格，单击鼠标右键，选择“名称定义”菜单，如下图所示。
弹出的“新建名称”对话框在将引用位置中的“=成绩！$A$2”，改写为“=成绩！$A$2:$B$1000”，它的意思是说定义“班级”这个名称是由B列的第2行开始到第1000行结束，在这个区域有效（也可以根据自己的实际情况进行定义，但一定要保证所定义的名称范围超出数据输入的范围，其它的定义和它是类似的）。“年级”定义结束后，我们采用同样的方法去定义“班级”这个名称。单击B2单元格，单击鼠标右键，选择“名称定义”菜单，弹出“新建名称”对话框。这时由于“班级”这一列是在B列上，因此需将它的引用位置“=成绩！$B$2”改写成“=成绩！$B$2:$B$1000”。
采用类似的方法依次定义“姓名”、“语文”、“数学”、“英语”、……注意要将它们的引用位置写正确！这样，在“成绩”工作表中我们所要做的准备工作就基本完成了。现在就可以在此“成绩”工作表中进行学生成绩的录入工作，学生的成绩也可以是从其它的Excel文档中复制－粘贴过来，但是顺序必须要和“成绩”表中的顺序保持一致。再打开“统计”这个工作表。建立如下图所示的表。
注意，第21行的全年级表示这一行是统计分析全年级的情况。单击B3这个单元格，在公式编辑栏中输入“=COUNTIF(班级，A3)”，它的意思是要统计刚才定义的名称“班级”当中的A3这个班（即1801班）的参加考试人数。接着同时按下“Crtl+Shift+Enter”组合键，生成一个计算班级参加考试人数的数组公式，如下图所示。
接着再单击C3这个单元格，在公式编辑栏中输入“=AVERAGE(IF((班级=A3),语文))”，它的意思是说要计算A3这个班的语文平均分，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。
单击D3这个单元格，在公式编辑栏中输入“=SUM((班级=A3)*(语文>=72))/COUNTIF(班级，A3)*100”，意思是说要统计A3这个班级当中语文成绩大于72分（及格分数是自己定义），也就是说统计该班的语文科的及格人数，然后除以这个班实际参加考试的人数再乘以100，就得出了A3这个班的及格率。同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。注意“=SUM((班级=A3)*(语文>=72))”中的这个*，表示的是逻辑运算中的“与”。而“COUNTIF(班级，A3)*100”中的*表示运算关系的“乘以”的意思，这两个*的含义是不一样的。
同理，单击E3这个单元格，在公式编辑栏中输入“=SUM((班级=A3)*(语文>=96))/COUNTIF(班级，A3)*100”，便可以得出这个班的语文优秀率（实际上优秀的分数也是根据实际情况进行定义的）。同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击F3这个单元格，在公式栏中输入“＝C3+D3+E3”，就得出了三率和。然后再用鼠标拖拽选中B3到F3这些连续的单击格，向下填充到第20行。如下图所示。接下来要做的工作是，单击B21这个单击格，在公式栏中输入“=COUNTIF(年级，2018)”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击C21单元格，在公式栏中输入“=AVERAGE(IF((年级=2018),语文))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击D21单元格，在公式栏中输入“=SUM((年级=2018)*(语文>=72))/COUNTIF(年级，2018)*100”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击E21单元格，在公式栏中输入“=SUM((年级=2018)*(语文>=96))/COUNTIF(年级，2018)*100”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。
单击F21单元格，在公式栏中输入“＝D21+E21+C21”，得出全年级的三率和。接着我们要做的是各分数段人数的统计工作。单击H3单元格，在公式栏中输入“=MAX(IF((班级=A3),语文))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。它的意思是统计出该班的最高分。单击I3这个单元格，在公式栏中输入“=MIN(IF((班级=A3),语文))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击J3这个单击格，在公式栏中输入“=SUM((班级=A3)*(语文<72))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。
单击K3单元格后，在公式栏中输入“=SUM((班级=A3)*(语文>=72)*(语文<96))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击L3单元格，在公式栏中输入“=SUM((班级=A3)*(语文>=96)*(语文<120))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。然后用鼠标拖拽选中H3到L3，向下进行填充。如下图所示。然后单击H21这个单击格，在公式栏中输入“=MAX(IF((年级=2018),语文))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击I21这个单元格，在公式栏中输入“=MIN(IF((年级=2018),语文))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。
单击J21单元格，在公式栏中输入“=SUM((年级=2018)*(语文<72))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击K21单元格，在公式栏中输入“=SUM((年级=2018)*(语文>=72)*(语文<96))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。单击L21单元格，在公式栏中输入“=SUM((年级=2018)*(语文>=96)*(语文<120))”，同时按下“Crtl+Shift+Enter”组合键，生成一个数组公式。
到此18级语文科成绩部分完成，接着参照此方式，依次完成其他所有的学科即可，保存起来下次考试清空成绩后直接导入就能出结果了。
标签: excel表格优秀率