胶合板加工厂:Excel应对特殊成绩统计

 

1． 考试混合编，成绩统一理——老方法遇到新问题

　　关于使用Excel进行学生成绩处理，已经是老话题了。但在实际工作中还是会有很多新问题，例如，现在很多学校都是全年级各班混在一起考试，以防考试改卷中的不正当竞争。而统计成绩时，则是将已判分但未拆封的考卷统一交到教务处，先按座位号顺序（每本考卷的自然顺序）录入各科分数，再分析统计出全年级各科成绩。举例说明，如图1（记录11至830隐藏了），要统计二（1）班优秀人数，传统做法就是先按考试号排序，再通过公式“=COUNTIF(分数!D2:D69,">=96")”求出。它的弊端是要手工逐个修改 “D2:D69”这个参数中的两个行号（2和69），这可是一项工作量很大的工作。当然，简单的方法还是有的，往下看吧。

　　　　　　　　　　　　　　　　　　　　　图1 原始成绩表

　　2． 初步准备——考试号里提班级

　　如图1，从B列的考试号中取出前三位（班级编号）放在S列，即在单元格S2输入公式“=LEFT(B2,3)”，然后双击（或拖动）S2单元格右下角的填充柄即可。

　　3． 再做辅表——班级等级二合一

　　在图1所示的工作簿中再新建一工作表，并将其命名为“等级”，在单元格A1中输入公式“=分数!A1”，回车，选定A1，按住A1右下角的填充柄向右下拖至C840单元格，将“分数”工作表中的姓名、考号、座位号引用到“等级”工作表中（注意，千万不能复制粘贴过来，这样不能保持两表数据的一致性）。再选定C1，按住C1右下角的填充柄向右拖至L1单元格，将语文、数学等9个学科科目引用过来。接着，在D2单元格中输入IF嵌套公式“=IF(分数!D2>=96,分数!$S2&&"a",IF(分数!D2>=72,分数!$S2&&"b",IF(分数!D2<48,分数!$S2&&"d",分数!$S2&&"c")))”。D2单元格中公式的含义是：看“分数!D2”单元格中的分数（即“分数”工作表中李悦的语文分数）是否大于等于96。如果是，则在D2单元格中填入“201a”——“分数”工作表中S2单元格中的字符“201”加上“a”（“201”表示二(1)班，“a”表示成绩等级为“优秀”）；如果不是（即小于96），再看是否大于等于72。如果是，则在D2单元格中填入“201b”；如果不是（即小于72），再看是否小于48。如果是，则在D2单元格中填入“201d”；如果不是（即小于72大于48），则在D2单元格中填入“201c”。最后按住D2单元格右下角的填充柄向右下拖至L840单元格，就可以将每个学生各科成绩的等级及所属班级都填好了

[ 本帖最后由 lisaxu 于 2007-11-27 16:49 编辑 ] 私は日本鬼子を最も喜ばない。 工信部网站备案企业端 通过电信研究院评测 回复 引用

TOP

lisaxu

• 发短消息
• 加为好友

lisaxu (潜水的鱼)当前离线

灵魂工程师

UID

242674 

帖子

1221 

精华

0 

来自

私は日本鬼子を最も喜ばない。 

注册时间

2003-12-27 

最后登录

2011-1-4 

高中一年级

帖子

1221 

精华

0 

积分

0 

注册时间

2003-12-27 

4^# 发表于 2007-11-27 16:47 | 只看该作者 4． 最终统计——所需数据瞬间齐

　　辅表制好之后，言归正传回到“统计”工作表（如图2）中，在A17到E28单元格区域中利用自动填充功能再制作一小块辅助数据（如图2）。



　　　　　　　　　　　　　　　　图2 “统计”工作表
　
　　万事俱备，下面开始班级总人数及优秀率、及格率等的统计了。仍以二（1）班优秀率为例，现在就改用这样的公式了“COUNTIF(等级!$D:$D,$B17)”，即对“等级”工作表中D列所有单元格进行统计（等级!$D:$D），找出值为“201a”（本工作表即“统计”工作表的$B17的值，代表二（1）班优秀率）的单元格数目。 具体做法如下：

　　（1）班级总人数（在B4单元格中输入）：“=COUNTIF(分数!$S:$S,A17)”；

　　（2）优秀人数（在C4单元格中输入）：“=COUNTIF(等级!$D:$D,$B17)”；

　　（3）优秀率（在D4单元格中输入）：“=C4/$B4 100”；

　　（4）及格人数（在E4单元格中输入）：“=COUNTIF(等级!$D:$D,$B17)+COUNTIF(等级!$D:$D,$C17)”；

　　（5）及格率（在F4单元格中输入）：“=E4/$B4 100”；

　　（6）低分人数（在G4单元格中输入）：”=COUNTIF(等级!$D:$D,$E17)”；

　　（7）低分率（在H4单元格中输入）：“=G4/$B4 100”；

　　到此为止，其余数据通过自动填充功能，瞬间即可完成。

　　5． 方法点评——一表成，终年用，一劳而永逸

　　（1）不同年级成绩统计的简单套用：比如，首先制作好了一年级的统计表，通过复制粘贴将第一个工作表（“分数”工作表）的内容更改为二年级的数据表，则二年级的成绩统计便自然而成。

　　（2）多次考试成绩统计的简单套用：这次考试的统计表，到下次考试成绩统计时，照用不误，只将第一个工作表换成新生的成绩记载就可以了。 私は日本鬼子を最も喜ばない。 工信部网站备案企业端 通过电信研究院评测 回复 引用

TOP

joke

• 发短消息
• 加为好友

joke 当前离线

UID

497556 

帖子

13297 

精华

1 

来自

江苏盐城 

注册时间

2005-12-9 

最后登录

2011-1-7 

版主

帖子

13297 

精华

1 

积分

3 

注册时间

2005-12-9 

5^# 发表于 2007-11-27 16:50 | 只看该作者 哦　　　　　　　
看看

[ 本帖最后由 joke 于 2007-11-27 16:51 编辑 ] 工信部网站备案企业端 通过电信研究院评测 回复 引用

TOP

 

 

6^# 发表于 2007-11-27 17:05 | 只看该作者

几个教师用于成绩统计的 excel 函数

EXCEL函数功能非常强大,这里只介绍几个可以辅助教师统计分数的常用函数,希望对大家有所帮助.

SUM函数
这是求总和函数,可用于计算总分.
语法:SUM(number1,number2, ...)
Number1, number2, ...    为 1 到 30 个需要求和的参数,可以是单元格引用,也可以是数值。
如:SUM(B2:D4)    '计算B2至D4单元格区域的总和

AVERAGE函数
这是求平均数函数,可用于计算平均分.
语法:AVERAGE(number1,number2,...)
Number1, number2, ...    为需要计算平均值的 1 到 30 个参数,可以是单元格引用,也可以是数值。
如:AVERAGE(B2:D4)    '计算B2至D4单元格区域的平均数

RANK函数
这是排位(名)函数,可用于成绩自动排名.
语法:RANK(number,ref,order)
Number    为需要找到排位的数字。
Ref    为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。
Order    为一数字，指明排位的方式。
如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。 如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。
如:RANK(K2,K$2:K$90)    '计算K2单元格的数值在 K2至K90单元格区域 中按从大到小排序的位置,也就是第几名.

COUNTIF函数
计算区域中满足给定条件的单元格的个数.可用于统计分数段.
语法:COUNTIF(range,criteria)
Range    为需要计算其中满足条件的单元格数目的单元格区域。
Criteria    为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。
如:COUNTIF(A2:A90,">=90")    '计算A2至A90单元格区域中  大于90分 的人数;
同理,COUNTIF(A2:A90,">=80")-COUNTIF(A2:A90,">=90")    '为计算80-89分的人数.

今天就先介绍这么多,以后再补上,excel函数功能是非常强大的,合理使用可以方便我们日常工作.如果有其它好用的函数,也请大家跟贴告诉一声,先谢了. 私は日本鬼子を最も喜ばない。 工信部网站备案企业端 通过电信研究院评测 回复 引用

TOP

 

 

 

 

 

 

7^#  

成绩统计分析中两个重要的EXCEL函数

考试结束后的成绩分析工作是教师头疼的问题，但有了EXCEL后成绩分析成了轻松愉快的事。左手茶杯，右手鼠标，点击中得到想要的结果的确是件很享受的事。成绩统计分析中通常需要计算学科平均、优秀率、及格率、低分率和分段数频率分布。
500){this.resized=true;this.style.width=500;}" twffan="done">
500){this.resized=true;this.style.width=500;}" twffan="done">
     利用Countif函数计算优秀率
     在计算优秀率、及格率和低分率时，教师通常采用人工和公式相结合的办法。方法是先以学科为关键字进行排序的方法统计出优秀学生数，然后以“优秀学生数/参加考试人数”计算出优秀率。此类方法在分析班级成绩时还可行，但如果要对全年级甚至全区学生成绩进行统计分析，就容易出错。出错的关键还于优秀学生人数的统计。其实EXCEL中提供的Countif函数就是用来统计满足给定条件的单元格的个数。
在COUNTIF(range,criteria)中，Range表示需要计算其中满足条件的单元格数目的单元格区域，Criteria 计数条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、"32"、">32" 或 "apples"。因此优秀率的公式可以表述为：“=COUNTIF(C3:C55,">=90")/COUNT(C3:C55)”

      利用FREQUENCY函数计算频率分布
      学生成绩分数段人数的计算比较简单的方法是右击EXCEL状态栏，在弹出的菜单中选择“计数”，然500){this.resized=true;this.style.width=500;}" align=right twffan="done">后选中分数段内的所有成绩，在状态栏中就可以看到结果了。其实EXCEL为我们提供了专门用于计算数据频繁分布的FREQUENCY函数。
     设置分段点
由于函数 FREQUENCY 返回一个数组，所以必须以数组公式的形式输入。分段点必须以数组形式设置，29表示小于等于29的个数，39表示30-39之间的个数，以此类推。如果成绩最高分为100分，满100的学生单独计数，则分段点中无需设置100这个分段点。因为返回的数组中的元素个数比 分段点数组中的元素个数会多 1。500){this.resized=true;this.style.width=500;}" align=left twffan="done">
    设置公式
FREQUENCY函数的语法为FREQUENCY(引用成绩数组,分段点数组)。第一步选中存入结果的单元格区域，因为FREQUENCY 将返回一个数组，并且返回的数组中的元素个数比分段点数组中的元素个数多 1，即返回的数组中所多出来的元素表示超出最高间隔的数值个数，所以此列中将选中9个纵向的单元格区域。第二步在函数参数对话框中引用要统计的成绩数组和分段点数组。最终公式如“=FREQUENCY(D3:D55,M3:M10)”
    生成结果
由于此公式必须以数组公式的形式输入，因些在函数参数对话框确定后按 F2，再按 Ctrl+Shift+Enter，此时最终结果就出现了。一般我们所要的分数段统计是横向表格，因此要把结果剪切到相应的单元格区域中。方法为复制后选中目标单元格区域，打开编辑菜单中的“选择性粘贴”，选择粘贴为“数值”，勾选“转置”。 私は日本鬼子を最も喜ばない。 工信部网站备案企业端 通过电信研究院评测    

 

 

 

 

 

 

8^# 发表于 2007-11-27 17:15 | 只看该作者

根据分数给出优，良，中，差

如果只有两个分数段（如及格、不及格两个级别）很好办，假设成绩数据已经输入单元格A1，直接用Excel公式输入条件A1>=60，为真时填写“及格”，为假时填写“不及格”就可以了。这时显示在Excel单元格里的公式是：=IF(A1>=60, “及格”,“不及格”)。

如果有多个分数段，比如要求:

x<60-----------------------不及格

x>=60 AND x<75--------及格

x>=75 AND x<85--------良

x>=85----------------------优

其实很简单，只要了解Excel里IF函数参数和返回值的用法，第一个参数是条件，第二个参数是条件为真的返回值，第三个参数是条件为假的返回值；而且IF函数可以嵌套(根据Excel2003的帮助文件，最多嵌套可达七层)，这样上面的问题可以用如下公式来解决：

=IF(A1<60,"不及格",IF(A1<75,"及格",IF(A1<85,"良","优")))

(这时无法使用Excel的交互式的方法输入，可以把这个公式修改后复制到单元格里)

因为Excel特有的单元格拖拽功能，很容易就把这个公式给复制到一整列上了，这样就实现了一系列成绩自动的给出等级，使用起来还是比较方便的。这个小技巧比较初级，但是我认为还是比较的实用的，见笑了。 私は日本鬼子を最も喜ばない。