分享
 
 
 

利用Excel2007完成试卷分析

王朝other·作者佚名  2008-05-30
窄屏简体版  字體: |||超大  

作者:IT168 长江边上

如果你是一位教师,那么每次考试结束后都会需要对考试进行质量分析,相信老师们应该很少去使用计算器进行手工作业,取而代之的是强大的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"组合键进行确认才行。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有