考试成绩汇总函数Word文档下载推荐.docx
《考试成绩汇总函数Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《考试成绩汇总函数Word文档下载推荐.docx（51页珍藏版）》请在冰豆网上搜索。
然后按下“CtrlShiftEnter”快捷键，可以看到在公式的最外层加上了一对大括号。
现在，我们就已经得到了语文学科各分数段人数了。
在K列中的那些数字，就是我们统计各分数段时的分数分界点。
现在再选中M8:
M15单元格，拖动其填充句柄向右至Q列，那么，其它学科的分数段人数也立即显示在我们眼前了。
如果觉得K列的数据有碍观瞻，那么可以选中它们，然后设置它们的字体颜色为白色就可以了。
一、前言
对于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见问题难住。
碰到的难题主要有：
如何统计不同分数段人数、如何在保持学号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等，本文着重对这些问题的解决方法与技巧加以分析和讨论。
本文假设读者已对Excel的基本操作已经有一定基础，已经掌握如何进行求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这些一般性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。
二、Excel统计学生成绩时的四个难题
假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。
图1 学生成绩统计所需要的结果图
这里，假设学号、姓名、成绩等列及行15都已经事先输好，需要让Excel统计其他的相关数据结果。
这时，成绩统计中主要难解决的问题及它们在图中的位置如下：
问题1：
如何统计不同分数段的学生人数?
（图中A16~E16）
问题2：
如何在保持学号顺序不变的前提下进行学生成绩名次排定?
（图中F2~F13）
问题3：
如何将百分制转换成不同的等级分?
（图中“等级1”与“等级2”列）
问题4：
如何使不及格的分数以红色显示?
（图中红色显示部分，即第12行）
三、解决统计学生成绩时的四个难题的方法
下面，针对上面提出的四个难题分别讨论解决的方法与技巧。
1、统计不同分数段的学生人数
统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中A16~E16所示。
这里，假设需要统计90~100、80~89、70~79、60~69及低于60分五个不同分数段的人数。
通常，统计不同分数段最好的方法是利用COUNTIF（X，Y）函数。
其中有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;
第二个参数Y为统计条件，要加引号。
对于小于60分的人数只要用一个COUNTIF（）函数，如在E16单元格中输入公式：
=COUNTIF（$C$2:
$C$13,"
<
60"
）。
对于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF（）函数相减。
如在A16单元格中输入公式：
=100"
）-COUNTIF（$C$2:
$C$13,"
90"
），即用小于等于100的人数减去小于90的人数。
如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把"
与"
作相应的修改，就可以得到正确的结果。
2.保持学号顺序不变的前提下进行成绩排名
学生成绩排定在学生成绩统计中经常用到。
特别要强调的是，这里所谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。
这里所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，其所需结果如图1中F2~F13所示。
要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使用RANK（X，Y，Z）函数。
其中有三个参数，第一个参数X为某个学生的成绩所在单元格;
第二个参数Y为整个班级成绩所在的区域;
第三个参数Z是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。
为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F2单元格中输入公式：
=RANK（C2,$C$2:
$C$13,0），然后，利用自动填充柄将其复制到下方的几个单元格。
注意，这里$C$2:
$C$13用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级成绩所在的区域，这个区域是相同的。
3、将百分制转换成不同的等级分
将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1中“等级1”与“等级2”列所示。
这里，“等级1”列是将百分制的分数转换成A（90~100）、B（80~89）、C（70~79）、D（60~69）与E（低于60）五个等级;
“等级2”列是将百分制的分数转换成优（90~100）、良（75~89）、中（60~74）与不及格（低于60）四个等级。
具体使用哪种等级划分方法可根据实际情况自己确定。
在百分制转换成不同的等级分时，一般使用IF（X，Y，Z）函数。
其中有三个参数，第一个参数X为条件，不能加引号;
第二个参数为条件成立时的结果，如果是显示某个值，则要加引号;
第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。
该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。
为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：
=IF（C2>
=90,"
A"
IF（C2>
=80,"
B"
=70,"
C"
=60,"
D"
"
E"
）））），然后，利用自动填充柄将其复制到下方的几个单元格。
为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式：
=90,"
优"
=75,"
良"
中"
不及格"
））），然后，利用自动填充柄将其复制到下方的几个单元格。
4、使不及格的分数以红色显示
统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分（如第12行）。
使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。
该命令会弹出一个对话框，其中要求确认条件与相应的格式。
对于“成绩”列，可先选中C2：
C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。
对于“等级1”列，可先选中D2：
D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。
“等级2”列类似。
对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机优良率与合格率等功能，应该比较简单，本文此处不赘述。
用Excel函数轻松搞定学生成绩等级评定《转》
2009-01-3102:
42
Excel函数可以帮助各位老师用字母A、B、C给学生打成绩。
我们定的标准是80分以上为A，60分至79分为B，60分以下为C。
下面我们看如何实现这一效果。
一、利用公式计算等级：
在Excel中把成绩录入完成，然后在表头行的最后边再添加上一个“等级”列。
在F2单元格中输入：
=CONCATENATE（IF（C2>
=80，"
，IF（C2>
=60，"
，"
）），IF（D2>
，IF（D2>
）），IF（E2>
，IF（E2>
））），然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。
（如图）
输入公式
也可以在F2单元格中输入：
））&
IF（D2>
=80，"
IF（E2>
）），然后把鼠标指针指向F2单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。
二、函数和公式说明：
上面的公式中我们主要是利用IF条件函数的嵌套。
IF函数是根据对指定条件的逻辑判断的真假结果，返回相对应的内容的条件函数。
利用此函数逻辑计算出真假值，会返回不同的结果，从而可达到筛选数据的目的。
其语法格式是：
IF（logical_test，value_if_true，value_if_false），logical_test：
表示计算结果为TRUE或者FALSE的任意值或表达式，该参数可使用任何比较运算符；
value_if_true：
表示logical_test为TRUE（也就是真或正确）时返回的值；
value_if_false：
表示logical_test为FALSE（也就是假或错误）时返回的值。
而本例中的公式IF（C2>
））意思是如果C2单元格中的语文分数大于或等于80分，则在F2单元格中显示字母A，而如果小于80分则再重新判断是否大于等于60分，如果大于或等于60分则在F2单元格中显示字母B，而如果小于60分则显示字母C。
CONCATENATE是将若干个文字项合并至一个文字项中的函数。
CONCATENATE（text1，text2……）。
而&
是将多个单元格字符串连接成一个字符串的函数。
numb1&
numb2&
……。
这里text1和numb1是指IF（C2>
）），即语文这列成绩的等级；
text2和numb2是指IF（D2>
）），即数学这列成绩的等级；
text3和numb3是指IF（E2>
=80，"
）），即英语这列成绩的等级
在教学管理工作中，学生成绩的处理是一项枯燥烦琐的工作。
Excel2000提供了一个宏命令，利用它可以大大地缩短工作时间，提高工作效率。
准备工作
在完成某年级所有班的成绩录入之后，首先打开班级人数最多的那个成绩文件（例如，该班学生人数为70名）。
成绩表中A列为学号（A1内容为“学号”，后同）、B列为姓名、C列为语文、D列为数学、E列为政治、F列为总分、G列为名次。
录制宏命令
单击菜单“工具→宏→录制新宏”，出现“录制新宏”的对话框，输入宏名，如“高一”，选择“保存在”项目中的“个人宏工作簿”，单击确定按钮，开始录制宏命令。
1．计算总分并按“总分”排序
选中单元格C2至E2，单击工具栏上汇总命令“∑”，得出第一个学生的总分。
选中F2单元格，拖动填充柄至F71。
选中F列中任一有内容的单元格，打开菜单“数据→排序”的对话框，以“总分”为主要关键字递减，以学号为次要关键字递增，单击确定按钮。
2．排名次
排名次一般来说有两种情况，一是从小到大的顺序排序：
在G2单元格中输入“1”，G3单元格中输入“2”，选中单元格G2和G3，双击填充柄或拖动填充柄至G71；
另一个是要求同分同名次：
在G2单元格中输入“＝RANKF2FF”，然后选中单元格G2，双击填充柄或拖动填充柄至G71单元格。
如果按第二种情况，还要进行以下操作：
选中G列，复制，利用“选择性粘贴”中的“数值”把G列内容复制到G列（或复制到H列后再删除G列，H列自动变为G列）。
3．计算全班各科平均分
在C72单元格输入“＝AVERAGEC2C71”，求出“语文”的平均分。
选中C72，拖动填充柄至E72，就求出了所有科目的平均分。
求总分平均分时要在F72单元格中输入公式“＝SUMF2F71/COUNTAB2B71”。
其中“COUNTAB2B71”是用来计算该班人数，不要使用如“COUNTAC2C71”，以防出错。
4．计算前N名学生的各科平均分
比如计算前30名学生的各科平均分。
在C73单元格中输入“＝AVERAGEC2C31”，求出“语文”前30名的平均分，选中C73单元格，拖动填充柄至F73，就计算出了该班前30名的各科平均分及总分的平均分。
5．计算达优率、及格率
按前两科的达优分数段为120计算，政治的达优分数段为80计算，在C74单元格中输入“＝COUNTIFC2C71″＞＝120″/COUNTA＄B2＄B71?
100”，在D74单元格中输入“＝COUNTIFD2D71″＞＝120″/COUNTA＄B2＄B71?
100”，在E74单元格中输入“＝COUNTIFE2E71″＞＝80″/COUNTA＄B2＄B71?
100”，就会得出各科达到优秀分数段的百分比；
同样，计算及格率时在C75单元格中输入“＝COUNTIFC2C71″＞＝90″/COUNTA＄B2＄B71?
100”，在D75单元格中输入“＝COUNTIFD2D71″＞＝90″/COUNTA＄B2＄B71?
100”，在E75单元格中输入“＝COUNTIFE2E71″＞＝60″/COUNTA＄B2＄B71?
100”，就可以算出各科达到及格分数段的百分比。
6．计算各分数段的人数
一般按10分一个分数段。
以计算语文分数段为例，在C75单元格中输入“＝COUNTIF＄C2＄C71″＞＝140″”，D75中输入“＝COUNTIF＄C2＄C71″＞＝130″”，……H75单元格中输入“＝COUNTIF＄C2＄C71″＞＝90″”，I75单元格中输入“＝COUNTIF＄C2＄C71″＜90″”，就可以求出各分数段的人数。
求其他科目时，只需把公式中“C”换成对应的列号即可，如计算政治“＞＝90”的人数，在H77单元格中输入“＝COUNTIF＄E2＄E71″＞＝90″”。
7．结束录制
单击“停止录制”按钮，宏的录制过程结束。
利用所创建的宏命令
打开第二个班的成绩文件后，选中C72单元格使其成为活动单元格，选择菜单“工具→宏→宏”，出现“宏”对话框，在“宏名”列表框中选择刚建立的宏文件名“高一”，单击执行按钮，宏命令就会运行。
运行完毕后，第二个班的成绩就计算完了。
同样，其他各班的成绩都可以这样来处理。
数据的归类
把各班平均分和各科分数段分别应用“选择性粘贴”中的“数值”复制到对应的工作表或工作簿中，就得到了完整的各班平均分（或者各班前N名的平均分）和各分数段人数的统计表。
在录制“宏命令”的过程中要注意，录制的都是所有成绩文件具有共性的内容。
如果是某个文件所特有的，如按“高一·
1班成绩”为名来保存文件，就不能出现在录制宏的过程中，否则就会出现错误。
录制好宏命令后，在以后的考试成绩处理中，就可以直接用来处理这个年级的成绩文件了。
这个宏命令也可以用来处理其他年级的成绩文件，如果科目数量不同或要求不同，按照这一方法再建立一个新的宏命令就可以了。
虽然建立宏命令的过程有些麻烦，但是如果能够很好利用的话，会起到事半功倍的效果。
注：
本文中有些公式用到“＄”，在复制公式时引用范围不会发生变化。
如计算达优率时在C74单元格中输入公式后，复制公式至E74，然后再依次修改对应的内容即可。
如采用复制公式的方法，在D74中只需把“″＞＝120″”改为“″＞＝80″”即可。
Excel做网页学生成绩网上查
━━━━━━━━━━━━━━━━━━━━━━━━━━
本文介绍一个由Excel2003生成成绩网页的实例，其人机交互性和信息保密性都很强。
该网页实现的效果是：
在“请输入你的标识码”处输入学生的标识码，按回车键或单击空白处，这个学生的各种信息就会一目了然。
标识码具有通常所说的用户名和密码双重含义，所以要叮嘱学生注意保密。
给每个学生添加唯一的标识码（以6位数字为例）
1.打开“成绩单”工作表，选中A列，右击鼠标，执行“插入”命令，插入一个新列，表头命名为“标识码”。
2.选中该列，执行“格式/单元格”命令，打开“单元格格式”对话框，选择“数字”选项卡。
在“分类”列表中选择“自定义”，在“类型”文本框中填写“000000”。
单击〔确定〕按钮。
其目的是当A列输入数字少于6位时补足6位。
3.激活A2单元格，输入函数“=TRUNC（RAND（）?
?
1000000）”（函数解释：
先产生一个整数位数最多为6位的随机数，再截取整数部分），将鼠标指针移到单元格右下角，用填充柄向下填充所有记录。
4.为使产生的随机数固定不变，选定本列，右击鼠标，执行“复制”命令，在原区域执行“选择性粘贴”命令，打开“选择性粘贴”对话框，选中“粘贴”栏目下的“数值”单选按钮，单击〔确定〕。
5.检查标识码是否重复。
（1）选中A列，执行“格式/条件格式”命令，打开如图2所示的“条件格式”对话框，在“条件1”下拉列表中选择“公式”，在右边的折叠框中输入“=A1=A2”。
单击〔格式〕按钮，弹出新的对话框，在“颜色”下拉列表中选择比较显眼的颜色，如“红色”，重复单击〔确定〕按钮，关闭各对话框。
（2）激活A列任意一个单元格，单击工具栏上的“排序”图标，重复的数字变成红色，需要人工修改。
为了降低标识码的重复率，可以增加位数。
插入“成绩查询”工作表创建查询系统
1.插入新工作表，命名为“成绩查询”，如图3所示。
由于此表要在网上“安家”，有必要“美化”一番：
利用“单元格格式”对话框，设置适当的字体、字号、字体颜色、边框颜色、单元格底纹颜色，适当调整行高和列宽。
2.在显示姓名的C6单元格输入函数“=IF（ISERROR（VLOOKUP（$C$2,成绩单!
$A$1:
$I$17,3,FALSE））,"
查无此人"
VLOOKUP（$C$2,成绩单!
$I$17,3,FALSE））”。
函数解释：
从“成绩单”工作表$A$1:
$I$17区域的第1列（标识码列）查找有没有在$C$2单元格输入的数字，如果没有，则输出“查无此人”；
如果有（精确匹配），则输出该区域第3列的对应数据（学生的姓名）。
在需要显示某学科成绩的单元格输入函数：
“=IF（ISERROR（VLOOKUP（$C$2,成绩单!
$I$17,X,FALSE））,"
"
$I$17,X,FALSE））”，其中X表示“成绩单”工作表中$A$1:
$I$17区域的第X列，例如将X替换为4返回语文成绩，替换为5返回数学成绩……
3.为了只允许在C2单元格输入数据，而其他单元格都拒绝输入，单击该工作表行号和列标的交叉处或按“Ctrl+A”键，选中全部区域，打开“单元格格式”对话框，选择“保护”选项卡，勾选“锁定”复选框。
激活要输入标识码的单元格C2，再取消“锁定”复选框的选择。
执行“工具/保护/保护工作表”命令，打开“保护工作表”对话框，在“取消工作表保护时使用的密码”文本框中输入密码，单击〔确定〕按钮。
隐藏数据源，另存为交互式网页
1.打开“成绩单”工作表，选中全部区域，执行“格式/行/隐藏”命令，隐藏“成绩单”工作表的行。
执行“格式/工作表/隐藏”命令，隐藏工作表。
2.执行“文件/另存为”命令，打开“另存为”对话框，在“保存类型”下拉列表中选择“网页”，如图4所示。
勾选“添加交互”复选框，输入文件名，单击〔更改标题〕按钮，在弹出的“设置标题”对话框中输入查询系统的标题，单击〔确定〕、〔保存〕按钮，将工作簿另存为网页文件。
给网页添加代码更安全
1.打开网页文件，在空白处右击鼠标，执行“查看源文件”命令，以记事本形式打开源文件，在与之间添加代码“”，使网页不能保存，在内部的body后添加代码“oncontextmenu=self.event.returnValue=false”，使右击无效。
当然还可以通过Dreamweaver进行美化，做出一张更美观的网页。
网页制作好了，就可以上传了。
整个制作过程虽然简单，却实现了“自己信息随便看，别人想看看不见”的效果。
用Excel自动统计学生成绩
2009-02-0511:
23
Excel自动统计学生成绩
这个自动统计表可以自动统计最高分、最低分、总分、平均分、名次等数据信息，还可以根据自定条件以不同的颜色显示分数。
自动统计表做好以后还可以保存成模板，以便以后使用。
文章末尾提供.xls文件供大家下载参考。
每到学期结束时，教师的一项重要工作就是要统计学生的成绩，在电脑逐步普及的今天，我们就把这项繁杂的工作交给Excel去自动完成吧。
涉及函数：
AVERAGE、COUNTIF、IF、ISERROR、MAX、MIN、RANK、SUM
1、启动Excel，同时选中A1至K1单元格，按“格式”工具条上的“合并及居中”按钮，将其合并成一个单元格，然后输入统计表的标题（参见图1）。
2、根据统计表的格式，将有关列标题及相关内容输入到相应的单元格中（参见图1）。
3、选中J3单元格，输入公式：
=SUM（C3:
I3），用于计算第一位学生的总分。
4、选中K3单元格，输入公式：
=RANK（J3,$J$3:
$J$62），计算出第一位学生总分成绩的名次（此处，假定共有60位学生）。
注意：
如果将上述公式修改为：
=COUNTIF（$J$3:
$J$62,"
>
="
J3），同样可以计算出第一位学生的名次。
5、同时选中J3和K3单元格，将鼠标移至K3单元格右下角的成“细十字”状时（通常称这种状态为“填充柄”状态），按住左键向下拖拉至K62单元格，完成其他学生的总分及名次的统计处理工作。
6、分别选中C63、C64单元格，输入公式：
=MAX（C3:
C62）和=MIN（C3:
C62），用于统计“语文”学科的最高分和最低分。
7、选中C65单元格，输入公式：
=IF（ISERROR（AVERAGE（C3:
C62））,"
AVERAGE（C3:
C62）），用于统计“语文”学科的平均分。
直接用“=AVERAGE（C3:
C64）”公式也可以计算平均分，但
升级会员 