首页 > 单独文章 > 正文

如何利用Excel 2007来完成试卷分析

时间:2007-05-10 13:12:43 作者:officeba 【认证】
如果你是一位教师,那么每次考试结束后都会需要对考试进行质量分析,相信老师们应该很少去使用计算器进行手工作业,取而代之的是强大的Excel,而下面的两份表格可以说是老师们非常熟悉的样表,当然还需要绘制正态分析曲线图。

  

  成绩统计表

  一、成绩统计表快速填写

  现在,当完成考试成绩的录入工作后,你并不需要去借助函数或公式计算最高分、总分、平均分什么的,因为当前工作表窗口底部的状态栏中已经显示了平均值(即平均分)、计数、求和这三个数值,如图1所示,右击后可以从快捷菜单中选择最小值(最低分)、最大值(最高分),虽然及格率、差分率、优分率在这里无法直接体现出来,但只要通过简单的排序和计算就可以完成。

  

  图1

  这里需要说明的是,"计数"是包含数据的单元格数,而"数值计数"则仅仅是显示包含数值数据的单元格数,两种计数方法是有一些区别的(见图2),请老师们在使用时注意到这一点。

  

  图2

  二、成绩分布频数

  这里,我们以满分100分为例进行说明,假如使用计算器或者更为古老的算盘,一方面会相当繁琐,另外也非常容易出错,既然是在Excel中统计成绩,自然要充分利用Excel的强大功能,而最新版本的Excel 2007则更为简单。下面,我们介绍获取成绩分布频数的三种方法:

  (一)利用FREQUENCY函数

  FREQUENCY函数可以计算数值在某个区域内的出现频率,然后返回一个垂直数值,我们正好利用这个函数来统计考试成绩的频数分布。不过,由于FREQUENCY函数返回的是一个数组,因此它必须以数组公式的形式输入。

  语法:FREQUENCY(data_array,bins_array)

  Data_array:是一个数组或对一组数值的引用,我们主要通过该值计算频率,如果此处不包含任何数值,那么函数FREQUENCY 将返回一个零数组。

  Bins_array:是一个区间数组或对区间的引用,该区间用于对data_array中的数值进行分组,如果此处不包含任何数值,那么函数FREQUENCY返回的值与 data_array中的元素个数相等。

  第1步:插入列

  在适当位置手工插入区间分割点、人数、百分比这三列,注意请完全按照教务部门发下来的样表进行输入(见图3),这样做的好处当然是免得做无用功。

  

  图3

  第2步:设置区间分割点

  区间分割点的设置是极为重要的一个步骤,这里以应该参考"分数段"进行设置,例如"29"表示考试成绩小于或等于29分的分数个数,"39"表示考试成绩介于30~39分的分数个数,其余的依次类推(见图4)。

  

  图4

  第3步:输入数组公式

  选择准备放置各分数段人数的单元格区域,这里选择"F3:F11",选择结束后按下F2键,如图5所示,在这里输入公式"=FREQUENCY(C3:C44,E3:E10)",为了避免出错,建议通过插入函数的方法进行,这样比较简单方便。

  

  图5

  有些朋友可能会发现,这里选择了"F3:F11"而非""F3:F10",这是为什么呢?也就是说,这里较左侧的区间分割点多选择了一个单元格,其实这个多出来的单元格主要用于返回大于99分的学生人数,原因是因为返回的数组中的元素个数(分布频数)比bins_array(分段点)中的元素个数多1,多出来的单元格将返回大于最末一个间隔值的数值个数,否则返回结果会出错。

  第4步:返回数组个数

  前面已经说明,由于FREQUENCY返回的结果是数组形式,因此公式输入完成后必须按下"Ctrl+Shift+Enter"组合键进行确认,其标志是出现一对大括号"{}",计算结果如图6所示。

  

  图6

  如果需要在查看结果和查看返回结果的公式之间切换,请按 Ctrl+`(重音符),或在"工具"菜单上,指向"公式审核",再单击"公式审核模式"。

  第5步:计算百分比

  接下来的工作就非常简单了,选中G3单元格,输入"=F3/42",将单元格数值设置为百分比格式,这一步工作可以在"单元格格式→数字"窗口中完成,最终结果如图7所示。

  

  图7

  (二)利用COUNTIF函数

  COUNTIF函数可以计算区域中满足给定条件的单元格的个数,命令语法是"COUNTIF(range,criteria)",这里的"range"是一个或多个要计数的单元格,期中包括数字或名称、数组或包含数字的引用,当然如果是空值或文本值将被忽略;而"criteria"的形式可以为数字、表达式、单元格引用或文本。

  第1步:设置区间分割点

  首先,我们还是手工创建区间分割点、人数、百分比三个空白列,但在设置区间分割点时,注意与使用FREQUENCY函数时有一些区别,请按照图8所示进行设置,这里的"99.9"表示考试成绩为满分100的学生数,"89.9"表示考试成绩在90~99之间的学生数,其余的依次类推。

  

  图8

  第2步:计算满分的学生数

  选中F3单元格,在这里输入公式"=COUNTIF(C$3:C$44,">"&$E3)",这里的"C$3:C$44"表示需要计数的单元格区域,而"">"&$E3"则表示大于99.9分,计算结果如图9所示。

  

  图9

  第3步:计算90~99分数区间段的学生数

  选中F4单元格,在这里输入公式"=COUNTIF(C$3:C$44,">"&$E4)-COUNTIF(C$3:C$44,">"&$E3)",很快就可以得到图10所示处于90~99分数区间段的学生人数。

  

  图10

  这个公式分成两个环节:

  (1) COUNTIF(C$3:C$44,">"&$E4):计算出考试成绩大于89.9分的学生人数

  (2) COUNTIF(C$3:C$44,">"&$E3):计算出考试成绩大于99.9分的学生人数

  根据成绩分布频数样表的要求,我们需要的各个区间分割点的学生人数,而非累加的数字,因此需要对这两份数字进行减运行,否则最后所得到的结果就不是我们所需要的了。

  接下来的工作就相当简单了,选中F3单元格,拖曳右下角的填充柄即可得到图11所示的成绩分布频数,至于各个区间学生数的百分比,可以按照前面的方法进行计算,这里就不再多作介绍。

  

  图11

  (三)利用SUMPRODUCT函数

  SUMPRODUCT函数的功能是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和,命令语法是"SUMPRODUCT(array1,array2,array3, ...),这里的"Array1, array2, array3, ..."为2~255个数组,其相应元素需要进行相乘并求和。需要注意的是,数组参数必须具有相同的维数,否则将会错误值"#VALUE!"。

  使用SUMPRODUCT函数时,前面的步骤都与COUNTIF函数相同,但在F3单元格统计满分段的学生人数时,输入的公式应该是"=SUMPRODUCT((C$3:C$44=100)*(C$3:C$44>0))",执行后可以获得满分段的学生人数(见图12);接下来选中F4单元格,输入公式"=SUMPRODUCT((C$3:C$44>$E4)*(C$3:C$44<$E3))",执行后即可获取90~99分数区间段的学生人数,最后拖曳填充柄即可得到其它区间段的学生人数,最终结果如图13所示。

  

  图12

  

  图13

  通过上面的介绍,综合比较,我们可以很明显的看出,其实三个函数中还是FREQUENCY函数最为简单,虽然执行这个数组公式时需要按下"Ctrl+Shift+Enter"组合键进行确认才行。

相关文章

同类最新