扔掉计算器 利用Excel2007完成试卷分析.doc

扔掉计算器 利用Excel2007完成试卷分析如果你是一位教师，那么每次考试结束后都会需要对考试进行质量分析，相信老师们应该很少去使用计算器进行手工作业，取而代之的是强大的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表示考试成绩介于3039分的分数个数，其余的依次类推(见图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表示考试成绩在9099之间的学生数，其余的依次类推。图8 第2步：计算满分的学生数 选中F3单元格，在这里输入公式=COUNTIF(C$3:C$44,&$E3)，这里的C$3:C$44表示需要计数的单元格区域，而&$E3则表示大于99.9分，计算结果如图9所示。图9 第3步：计算9099分数区间段的学生数 选中F4单元格，在这里输入公式=COUNTIF(C$3:C$44,&$E4)-COUNTIF(C$3:C$44,&$E3)，很快就可以得到图10所示处于9099分数区间段的学生人数。图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, .为2255个数组，其相应元素需要进行相乘并求和。需要注意的是，数组参数必须具有相同的维数，否则将会错误值#VALUE!。 使用SUMPRODUCT函数时，前面的步骤都与COUNTIF函数相同，但在F3单元格统计满分段的学生人数时，输入的公式应该是=SUMPRODUCT(C$3:C$44=100)*(C$3:C$440)，执行后可以获得满分段的学生人数(见图12)；接下来选中F4单元格，输入公式=SUMPRODUCT(C$3:C$44$E4)*(C$3:C$44$E3)，执行后即可