EXCEL进行学生成绩管理（通用12篇）
一、快速转换学生考试成绩等级
有的时候，会遇到要将学生的考试成绩按实际考试分数转换成相应成绩等级的情况，如将考试成绩在90分以上的成绩转换成“A+”形式，85-89分的成绩转换成“A”形式...。一般情况，在EXCEL表格中大家会采用IF函数来设计公式进行转换，这样所设计的公式会变得很复杂，如果进行转换的成绩等级类型超过IF()函数的最大嵌套(7层)时，IF()函数就无能为力了。这时我们可用如下的方法来简化操作。
1、打开学生成绩工作表（图1）。
2、在G2到I12单元格录入考试成绩分数段与考试成绩等级对照表。
3、在D3单元格录入公式“=INDEX(I$3:I$12,MATCH(1,(C3>=G$3:G$12)*(C3<=H$3:H$12),0))”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl+Shift+Enter”键，为上述公式内容加上数组公式标志即大括号“{}”。该公式的作用就是，根据C3单元格中的学生成绩，在D3单元格自动将该成绩转换成相应的成绩等级。
4、将光标移到D3单元格，向下拖动填充柄至D12单元格，将公式进行快速复制，这样就可以迅速完成转换学生成绩等级的工作（图2），
5、还可以按照自己的喜好，将G2至I12的单元格区域设置为“隐藏”，以使表格更加美观。
二、快速统计学生考试成绩分布情况
在利用EXCEL管理学生考试成绩时，常常要统计各分数段学生考试成绩的分布情况，如果采用下面介绍的这种方法，就能使这项工作变得非常方便。
1、打开学生成绩工作表（本例仍使用上例的工作表）。
2、在G3至G6单元格录入学生考试成绩的统计分段点。如在本例中采用的统计分段点为：60、69、79、89，即统计60分以下、61-69、70-79、80-89、90分以上五个学生考试成绩区段的人数分布情况，当然你也可以根据自己的实际需要在此进行不同的设置。
3、选中要进行公式设计的单元格区域B14至B18，按下F2键，录入公式“=FREQUENCY(C3:C12,G3:G6)”，由于该公式为数组公式，在录入完上述内容后，必须同时按下“Ctrl+Shift+Enter”键，为上述公式内容加上数组公式标志即大括号“{}”。
4、当上述操作完成后，在B14至B18单元格就迅速得到了正确的学生考试成绩分布情况（图3）。
5、值得注意的是，在我们设计统计区段时，这个统计区段必须比统计分段点的数据个数多一个。这个多出来的统计区段表示超出最高间隔的数值个数。例如，在本例中，我们设计的统计分段点为60、69、79、89四个数值，这时要想取得正确的统计区段分布数据，就必须在B14至B18五个单元格中输入 FREQUENCY() 函数计算的结果，多出来的这一个单元格将返回学生成绩表中大于90分的成绩的人数。
关键词：教学评价,考试,学生成绩,分析,函数,有效途径,Word,Excel,Access
一问题的提出
在新课改教学评价过程中, 学生考试评价扮演着重要的角色。考试安排的科学性和有效性是评价的基础, 它为每个学生落实了“公开、公平、公正”的考试原则, 而学生成绩的分析评价可以为教师的教学提供指导性意见, 教师利用评价的结果可以分析试题的难度系数、教学活动中使用的方式是否有效、学生的接受程度和学习状况, 从而调整下一阶段的教学行为, 反思和改善教学计划与教学方法, 不断提高教学水平, 同时为学生的学习调整提供强有力的数据分析和保障。为优化学生考试安排, 简化考试成绩及教学质量分析, 笔者在教学工作中不断探索与实践, 找到了利用教师们都常用、熟知的办公软件Word、Excel、Access来安排和分析学生的每一次考试的有效途径。
二解决问题的有效途径
1. 考试信息的安排
每一次考试教师都要安排学生的考试信息:考场、考号、座位号。而合理、科学、有效地安排考生的考场显得尤为重要, 随机安排的考试对每个考生体现了“公平、公开、公正”的原则。在Excel中我们可以用随机数的产生实现考场考号的编排。
第一, 考生考试信息模板的制作。打开Excel表格, 制作一个模板, 在Excel表格第一行输入“**年级期末考试考场安排表”, 在第二行中依次输入考生基本信息A列姓名、B列班级、C列学号、D列考场号、E列座位号、F列随机数。从第三行起填写学生的基本信息, 把该文件命名为“学生考试考场安排模板”。
第二, 产生随机数。随机数的产生目的主要是用来打乱学生顺序。随机数的产生利用RAND函数实现。在随机数一列的第一个单元格F3中输入“=RAND () ”按回车, 然后选中F3单元格的填充柄向下拖动, 这样就为每个学生产生了一个随机数, 然后再按照随机数进行升序或降序, 这样就将考生的班级打乱了。如果觉得还不够乱, 可以重复以上的工作几次, 之后便可以将该列隐藏或删除。
第三, 考号的产生。在考号一列中输入前两个考号, 例如:20140001, 20140002, 选定这两个单元格, 拖动填充句柄 (鼠标停在选定框的右下角时变成的“+”) 至最后一人, 这样就完成了考号的安排。
第四, 座位号的产生。方法一:假定每个考场安排30人, 我们在座号列中E2、E3单元格中分别输入1、2, 选定这两个单元格, 拖动填充句柄至E32。在E33单元格中输入“=E3”回车, 拖动填充句柄至最后一人, 这样就完成了座位号。方法二:假定每个考场安排30人, 这样座位号就是以1~30这组数为周期, 根据这个规律, 可以使用取模函数MOD完成座位号的安排。在座位号列下E2单元格中输入“=MOD ( (MOD (D2, 20140001) ) , 30) +1”按回车, 然后拖动E2的填充柄向下拖动, 这样考生的考号也就顺利完成了。
第五, 考场号的产生。因为每个考场安排30人, 且排在前面的30人的考场数均为1, 然后利用取整函数INT, 来产生考生的考场号。考场下的D2单元格中输入函数“=INT (G2/30) +1”, 然后拖动D2的填充柄向下填充, 这样就产生了考场。
第六, 制作桌贴或准考证。在新的工作表中制作一个桌贴或准考证模板 (见图1) , 然后在相应空白的地方输入“=所对应的单元格”, 选定模板并拖动填充句柄向下填充, 可得到全部准考证。还可以利用Word中的邮件合并功能 (具体方法见成绩条的制作) 。
第七, 打印各班学生考场安排。由于在排考的考号时, 对学生的班级进行打乱, 随机编排, 而在对各班学生的考场安排进行打印时, 又要分班进行打印。Excel为我们提供了数筛选功能, 具体操作是:选中任意单元格, 执行“数据”菜单下的“自动筛选”命令, 按班级升序或降序排列, 选择要打印的班级, 就能很快地将各班的考场安排打印出来。
2. 学生考试成绩分析模板的制作
该模板制作后只要输入成绩或更改成绩, Excel软件会自动显示分析结果, 这样就为我们节省了更多复杂计算的时间。
第一, 全校 (全年级) 成绩分析。
总分。用SUM () 函数, 它的语法为SUM (数值1, 数值2) , 例如总分为求F列至K列, 输入“=SUM (F4:K4) ”并向下拖动填充句柄。
各科、总分排名。由于学生成绩会出现相同, 因此排名会出现并列。利用RANK () 函数可以实现并列名次, 它的语法为:RANK (数值, 引用, [排序方式]) , 例如:总分为N列, 则在总分列下输入“=IF (N4="", "", RANK (N4, $N$4:$N$428) ) ” (共428行) , 然后向左拖动单元格N4的填充句柄可对相应列的数据做出排序, 选中要排序的列的单元格向下拖动填充句柄至最后一个可完成所有人的排序。
分数段统计。COUNT函数、COUNTIF函数或FREQUENCY函数可以统计某一区域中符合条件的单元格数。COUNT函数, 它的语法为COUNT (数值, …) 例如:统计参加语文 (语文为F列) 考试的人数可输入“=COUNT (F4:F428) ”COUNTIF函数, 它的语法为COUNTIF (区域, 条件) 。其中条件必须是以数字、表达式或文本形式定义的条件, 其中数字可以直接写入, 表达式和文本必须加引号。如统计总分成绩低于30分的人数可输入“=COUNTIF ($N$4:$N$428, "<=30") ”。FREQUENCY函数, 它的语法为 (一组数值, 一组间隔值) , 例如:统计语文成绩在90~99分之间的人数, 先选取Z列为一组间隔值, 间隔值为150 (Z4单元格) 、149.9、139.9…29.9 (Z17单元格) 然后在90~99对应的单元格内输入“=FREQUENCY (F$4:F$428, $Z$4:$Z$17) ”, 其余分数段及科目类似的可以完成。
平均分、最高分、最低分。AVERAGE函数计算平均分, 它的语法为AVERAGE (数值1, …) 。例如计算语文的平均分, 可输入“=AVERAGE (F4:F428) ”。MAX函数计算最大值, MIN计算最小值, 它们的语法为MAX (或MIN) (数值1, …) , 例如:计算语文的最高分和最低分可分别输入“=MAX ($F$4:$F$428) ”或“=MIN ($F$4:$F$428) ”。
及格率、优秀率。及格率=及格人数/总人数, 例如计算语文成绩的及格率 (满分150分, 及格分数为90分) =COUNTIF (F$4:F$428, ">=90") /COUNTIF (F$4:F$428, ">0") 。优秀率=优秀人数/总人数, 例如计算语文成绩的优秀率 (满分150分, 优秀分数为135分) =COUNTIF (F$4:F$428, ">=135") /COUNTIF (F$4:F$428, ">0") 。
样本估计。每一次考试我们都会抽取部分学生的试卷作为样本来估计总体。一种是利用样本的频率分布估计总体的分布, 另一种是用样本的数字特征 (如平均数、标准差等) 估计总体的数字特征。在Word、Excel中选择抽取的学生成绩点击插入图表, 就可以制作条形图或折线图。在Excel中利用方差函数VARP或标准差函数STDEVPA可以得到方差或标准差。
第二, 各班、各科成绩分析。利用以上函数改变分析的区域就可完成各科、各班的总分、各分数段人数、平均分、最高分、最低分、及格人数、及格率、优秀率等。
说明:如果制作的这些表不在同一个工作表中, 需要在输入时加上工作表名称。如:=MAX (成绩表!$N$4:$N$428) 。
以上所有数据完成后保存文件为“学生成绩模板.xls”。下面列举两个我们制作出来的模板见图2、图3:
3. 考试成绩的输入
成绩输入是一个非常繁琐的工作, 要求教师非常细心, 稍有不慎就会出错。可利用成绩的特殊性 (它都有一定的上限和下限) , 为教师所要输入的某科成绩设定一个上限和下限, 具体的操作是:选中某科成绩所对应的列, 然后点击“数据”菜单下的“有效性”命令, 在打开的对话框中设定成绩的最大值和最小值即可。语文、数学、英语成绩最高分的满分为150分, 可同时设置如图4:
当各科成绩统计后要汇总在一个表格中, 因为表格一致, 可以选择复制、粘贴的方法汇总。有时可能会出现表格信息变动, 例如有老师筛选过学生信息。这时可以利用Access数据库来处理, 具体的操作办法是:单开Access数据库→文件→新建空白数据库→命名为“学生成绩表”→文件→获取外部数据→导入 (成绩表、语文、数学等) →查询→在设计视图中创建查询 (如图5) →保存→导出 (文件保存类型为.excel) 。最后将成绩复制、选择性粘贴到**年级**考试成绩模板中, Excel软件便会自动产生成绩分析。
4. 成绩打印
第一, 各班成绩打印。利用Excel中的自动筛选功能, 具体操作是:选中任意单元格, 执行“数据”菜单下的“自动筛选”命令, 按班级升序或降序排列, 选择要打印的班级, 就能很快地将各班的考试成绩打印出来。
第二, 个人成绩条打印。个人成绩打印可以利用Word软件中的邮件合并功能。具体的做法是 (以Word 2003为例) :启动Word, 根据“成绩条”的大小, 设置好文档页面, 将页面设置成多个标签的大小。点击“视图→工具栏→邮件合并”, 在新出现的工具栏中选择“打开数据源”, 并选择“学生成绩”工作簿, 单击“确定”按钮。将鼠标定位在需要填入“姓名”的单元格中, 单击“邮件合并”工具栏上的“插入合并字段”按钮, 打开“插入合并域”对话框, 选中“姓名”选项后, 单击“插入”按钮即可完成姓名的插入 (如图6) , 照同样的方法可以完成其他插入。
将上述表格复制一份, 根据页面的大小, 在文档下面粘贴若干份。然后将鼠标定位在两份表格之间, 按“邮件合并”工具栏上的“插入Word域”按钮, 在随后弹出的快捷菜单中, 选“下一记录”就可以分别浏览合并后的效果, 选择“合并到新文档”按钮, 这样就生成一个包含所有成绩的Word文档。点击“打印”即可被打印出来, 最后裁剪、分发就行了。
三归纳与小结
关键词： Excel高职院校 成绩管理 分析统计
目前国家大力发展高职教育，高职院校学生的数量较以前已增加了很多，因此教学管理工作量远远大于从前，如果不能有效地使用现代技术，那么教学管理工作的开展就会变得十分困难。学生成绩管理是教学管理工作的重点，也是教师经常要进行的工作，如果手工计算，则不但效率低，工作量还大，由于每学期每门课程都要重复做这项工作，因此容易造成统计错误。
Excel强大的数据处理能力完全可以胜任成绩分析统计工作，且其丰富的图表功能还能使统计数据更加直观[1]。借助Excel的模板保护功能，可以使设计好的成绩统计功能重复应用于每门课程，这不仅大大减少了教师工作量，而且避免了错误统计数据的产生。下面就如何使用Excel制作成绩分析统计模板[2]进行介绍。
1.输入学生成绩构建工作簿
在工作簿中设计两个工作表，第一个工作表命名为“高职某班XX课成绩表”，包含该班111名学生的姓名、学号、平时成绩、考试成绩、实践和总评成绩几项内容，其中总评成绩=平时成绩+40%考试成绩+10%实践。
此项录入成绩工作量较大，为了减少数据录入的人为错误，还须对成绩录入区数据有效性进行设置，当录入错误及时提示[3]。基本结构如图1所示，后续分析统计时可以利用函数直接调用工作表名称，且该工作表的A至F列也是在数据修改的区域。
第二个工作表命名为“高职某班XX课成绩分析统计”，统计班级总人数、缺考、及格人数、及格率、最高成绩、最低成绩和平均成绩的情况，还会生成数据图表。该工作表是模板保护的区域。
2.成绩分析统计
2.1总评成绩计算
根据总评成绩的构成，可在总评成绩单元格中输入公式直接计算，如谭诗姚的总评成绩可在图1中F2单元格中输入函数公式“=IF（D2="*"，"缺考"，ROUND（（C2+0.4*D2+0.1*E2），0））”后回车。鼠标定位到F2单元格右下角，待光标呈实心黑十字时，按鼠标左键向下拖动填充其他需总评成绩计算单元格即可。
为了突出显示不及格的总评成绩，需将不及格的总评成绩显示为红色，具体步骤是：（1）选中“高职某班XX课成绩表”工作表F列；（2）单击菜单栏上的“开始”，找到“条件格式”快捷图标单击；（3）在下拉菜单中选中“突出显示单元格规则小于”图标，然后在出现对话框输入60并设置“文本红色”，单击“确认”按钮。
2.2班级总人数、缺考、及格人数、最高（低）成绩和平均成绩
计算班级总人数时，在图2中B2单元格输入“=COUNT（高职2013级某班XX课成绩表！F：F）”；而缺考人数在对应的单元格输入“=COUNTIF（高职2013级某班XX课成绩表！F：F，"缺考"）”；同样的计算及格人数时在单元格输入“=COUNTIF（高职2013级某班XX课成绩表！F：F，">=60"）”即可。
计算最高成绩时，在单元格中输入公式：=MAX（高职2013级某班XX课成绩表！F：F）&"分"；最低成绩将MAX改为MIN。平均成绩的计算需要使用AVERAGE函数，在图2中B6单元格输入“=ROUND（AVERAGE（高职2013级某班XX课成绩表！F：F），1）&"分"”，其中ROUND函数作用是对数值四舍五入保留有效位数。
2.3各分数段人数及比例确定
根据学生的总评成绩划分为5个分数段，“90分以上”、“80～89分”、“70～79分”、“60～69分”和“60分以下”，分别对应的分段点是99、89、79、69和59，使用FREQUENCY函数[1]进行分数段人数确定。在图2中选定E2：E6单元格，输入公式“=FREQUENCY（高职2013级某班XX课成绩表！F：F，G2：G6）”，然后按Ctrl+Shift+Enter，填充E2：E6单元格区域，所得结果即为各分数段人数。
各分数段人数比例的计算，使用刚得到各分数段人数与总人数进行比较，如“90分以上”的人数比例值在F2单元格中输入“=ROUND（E2/$B$1，3）”后回车。再将鼠标定位到F2单元格右下角，待光标呈实心黑十字时，按鼠标左键向下拖动填充其他需人数比例计算单元格即可。最后选定单元格F2：F6，将其设置为百分比显示。
2.4生成成绩分析三维饼图和柱状图
计算完各分数段占总人数的比例后，可以通过插入图表的方式，绘制出成绩分布的三维饼图（如图3）和柱状图（如图4），使成绩的分布更直观地显示出来，便于授课老师分析学生的知识掌握情况。
3.成绩分析模板的保护和使用
为了让成绩分析统计的数据只能自动生成，不能因人为而改动，就必须将“高职某班XX课成绩分析统计”工作表保护起来，具体步骤：（1）选中“高职某班XX课成绩分析统计”工作表；（2）单击菜单栏上的“审阅”，找到“保护工作表”快捷图标；（3）在出现的对话窗口中选中要保护的选项，然后输入保护密码，单击“确认”按钮。通过这些设置，只有知道保护密码才能对这些单元格进行修改，从而确保统计数据的安全和准确性。
需要使用成绩分析模板时，只需修改第一个工作表“高职某班XX课成绩表”中A到F列的数据即可，Excel就会自动生成出“高职某班XX课成绩分析统计”表中的成绩分析统计数据、三维饼图和柱状图。
4.结语
该成绩分析统计模板已经在很多教师中进行了推广，完成了大量课程成绩统计工作，取得了较好效果。熟练掌握Excel模板，不仅提高了教师成绩分析统计工作的效率，减少了统计数据的错误，而且图形显示使统计的结果更加直观，从而实现了使用专业软件管理和数据处理，增强了应对现代技术高速发展的能力。
参考文献：
[1]谢启祥.中文版Excel 2007电子表格实用教程[M].北京：清华大学出版社，2010.
[2]ExcelHome.Excel实战技巧精粹[M].北京：人民邮电出版社，2007.
【摘要】：正上级部门来检查,其中有一项是近三年音体美等考查科目的成绩单。近三千名学生三学年六学期的成绩单,说起来容易,做起来是真难呀!为了减轻大家的工作量, ,我最终利用Excel的随机函数功能轻松的完成了此项工作。具体方法如下: 【关键词】： 成绩单 单元格 随机函数 利用
数字 随机数
【正文】：
上级部门来检查，其中有一项是近三年音体美等考查科目的成绩单近三千名学生三学年六学期的成绩单，说起来容易，做起来是真难呀，为了减轻大家的工作t，并保住我这个同事眼中“能人”的称号，我最终利用Excel的随机函数功能轻松的完成了此项工作。具体方法如下:
1、利用随机函数ROUND(number,num_digits)和随机函数RAND()。
2、利用取整函数INT()很随机函数RAND()。
3、都必须在英文状态下输入函数。
RAND返回大于等于 0 及小于 1 的均匀分布随机数，每次计算工作表时都将返回一个新的数值。
语法 RAND()说明
若要生成 a 与 b 之间的随机实数，请使用：
RAND()*(b-a)+a 如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9，将公式永久性地改为随机数。
示例
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
操作方法
1.创建空白工作簿或工作表。
2.请在“帮助”主题中选取示例。不要选取行或列标题。
从帮助中选取示例。
3.按 Ctrl+C。
4.在工作表中，选中单元格 A1，再按 Ctrl+V。
5.若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。=RAND()3 =RAND()*100 A 公式
B 说明（结果）
介于 0 到 1 之间的一个随机数（变量）大于等于 0 但小于 100 的一个随机数（变量）
比如要输入成绩在60-100分范围中则可以编写函数，然后下面的利用填充柄填充。=ROUND（RAND（）*34，0）+60
其中因为要取整数所用要用到函数ROUND，该函数的使用方法是
ROUND 请参阅
返回某个数字按指定位数取整后的数字。语法
ROUND(number,num_digits)Number
需要进行四舍五入的数字。
Num_digits
指定的位数，按此位数进行四舍五入。说明
如果 num_digits 大于 0，则四舍五入到指定的小数位。
如果 num_digits 等于 0，则四舍五入到最接近的整数。
如果 num_digits 小于 0，则在小数点左侧进行四舍五入。
示例
如果您将示例复制到空白工作表中，可能会更易于理解该示例。
操作方法
1.2.3.4.5.6.创建空白工作簿或工作表。
请在“帮助”主题中选取示例。不要选取行或列标题。
从帮助中选取示例。按 Ctrl+C。
在工作表中，选中单元格 A1，再按 Ctrl+V。
若要在查看结果和查看返回结果的公式之间切换，请按 Ctrl+`（重音符），或在“工具”菜单上，指向“公式审核”，再单击“公式审核模式”。
A 公式
B 说明（结果）
将 2.15 四舍五入到一个小数位(2.2)将 2.149 四舍五入到一个小数位(2.1)将-1.475 四舍五入到两小数位(-1.48)将 21.5 四舍五入到小数点左侧一位(20)=ROUND(2.15, 1)3 =ROUND(2.149, 1)4 =ROUND(-1.475, 2)5 =ROUND(21.5,-1)
第二种方法利用函数INT 很RAND ：=60+INT(RAND()*40)比如要输入成绩在60-100分范围中则可以编写函数，然后下面的利用填充柄填充。
(1) “学生基本信息表”的姓名与“成绩表”中的姓名不一样，“学生基本信息表”中的“王一”在“成绩表”中为“ 王 一”，出现了全角或半角空格。
(2) “学生基本信息表”中王小平在“成绩表”中无此人，即“学生基本信息表”的人数多于“成绩表”的人数。
(3) “成绩表”中成绩列为文本方式，且出现了全角数字。
(4) 每个表的数据为几千条。如果对“成绩表”中的姓名列进行排序，把成绩列进行复制粘贴到“学生基本信息表”中的成绩列，出现错位。
我通过Excel函数SUBSTITUTE和LOOKUP来解决，将“学生基本信息表”和“成绩表”进行了一些修改，实现将“成绩表”中的数据复制到“学生基本信息表”中，并且保持最终表格的清爽和数据的正确。
除去“成绩表”中全角或半角空格
首先，我要解决的问题是将“成绩表”中姓名的空格去掉，让“成绩表”中的学生姓名显示和“学生基本信息表”中的一样。此时我利用替换公式 SUBSTITUTE(SUBSTITUTE(A2，“半角空格 ”，“”)，“全角空格”，“”)。在D2单元格输入公式=SUBSTITUTE(SUBSTITUTE(A2，“ ”，“”)，“ ”，“”)，然后在整个D列复制公式。选择D列数据→进行复制，再选择A列所有数据→选择性粘贴→值和数字格式。
转化“成绩表”中成绩列为数字
删除了空格，下面的工作就是将“成绩表”中的数字规范为半角形式。同样利用函数SUBSTITUTE。在E2单元格输入公式= (SUBSTITUTE(C2，“。”，“.”))*1，其中SUBSTITUTE(C2，“。”，“.”)表示句号“。”转化为点号“.”，“*1”表示转化为数字。然后在E列复制公式。同样进行选择性粘贴。选择E列数据→进行复制，再选择C列所有数据→选择性粘贴→值和数字格式。删除“成绩表”中D 列、E列，
复制“成绩表”中数据到“学生基本信息表”
最后一步就是复制“成绩表”中的数据到“学生基本信息表”了，但是我们不能简单地利用复制粘贴来实现，因为我们的这个具体案例中包含没有成绩的同学，所以为了数据的正确性，我们知道查询函数LOOKUP有一个特性就是在查询结束后会在指定的区域返回查询结果，我就用它来达到复制“成绩表”中数据的效果。
其语法为LOOKUP(lookup_value，lookup_vector，result_vector)。其中Lookup_value为要查找的数值，Lookup_vector为只包含一行或一列的区域，且必须按升序排列，否则要返回错误，Result_vector 返回只包含一行或一列的区域。
如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值，如果 lookup_value 小于lookup_vector 中的最小值，函数LOOKUP 返回错误值 #N/A，利用这个特性，我们把公式改为=LOOKUP(1，0/(条件)，引用区域)，条件――产生的是逻辑值True、False数组，0 /True=0，0/false=#DIV0!，即Lookup的第2参数便是由0、#DIV0!组成的数组(都比1小)，如果找到满足条件，就返回对应行引用区域的值;如果没有找到满足条件的记录则返回#N/A错误，从而实行精确查找。
在“学生基本信息表”中D2输入公式=LOOKUP(1，0/(成绩表!A$2:A$5=B2)，成绩表!C$2:C$5)。在没找到数据的一栏出现了#N/A，影响了表格的美观。稍微改进一下，利用ISNA函数判断是否为#N/A，如果是，设置为空。
因此在D2输入公式=IF(ISNA(LOOKUP(1，0/(成绩表!A$2:A$5=B2)，成绩表!C$2:C$5))，“”，LOOKUP(1，0/(成绩表!A$2:A$5=B2)，成绩表!C$2:C$5))，这样#N/A不会出现在单元格中，最后在D列进行公式复制即可。
点击阅读更多学院相关文章>>
“成绩通知单”在格式上有点像工资条，方便发给学生，让学生了解自己一段时间内的收获。下面我们介绍利用Excel软件的排序函数rank以及筛选功能轻松整理工资条格式的学生成绩单的方法和技巧。
用rank()函数计算排名
首先，我们要计算学生各科及总成绩在班里的排名，让学生了解自己各科成绩及总成绩在班上的位置。计算过程使用rank函数(图1)。
在C2单元格内输入“=rank(B2，B：B)”即可得到B2数值在B列中的位置(名次)。回车后双击C2单元格右下角的小黑点，即可完成所 有同学的班级名次。用同样的方法完成其他各科的名次。然后将整个表单按姓名排序，以便发放成绩条时学生知道下一个可能是他，节省时间。注意，很多班主任将 总成绩从高到低排序，然后在右边一列中填充数列1、2、3……，这样是没有并列名次的。
添加标题行
首先，将标题栏一行剪切到最后，并在最左边插入列，
然后在A1、A2处分别输入2、5，并选中这两个单元格，双击(或往下拉)A2单元格右下角的小黑点，实现等差数列“2、5、8……”的填充。
用同样的方法实现标题栏前面数列“1、4、7……”的填充，填充个数与上次相同。并选中全部标题栏(不含最左边的数)，用鼠标选中右下角的小黑点，往下拉，实现标题栏的复制。再在最后(标题栏后)用同样的方法实现数列“3、6、9……”的填充，填充个数与上次相同，且每行只有填充的数列。然后按所填充的数列从小到大排列。到此，完成标题行的添加(图2)。
添加表格框及页面设置
选中整个表单(一定要选中整个表单)，主菜单选择“数据”→“筛选”→“自动筛选”，“姓名”一栏内选择“非空白”，然后选中文字并给文字加上表格框(之前没有任何表格框)(图3)。
一、赛前制订好成绩统计表等电子表格
所有的统计数据都必须来自成绩统计表。因此, 制订好成绩统计表, 是进行运动会成绩统计的关键。而成绩统计表的制作, 必须根据竞赛规程中名次录取及计分方法的要求进行。下面以取前八名为例介绍制作成绩统计表的方法。
(一) 制作好前八名成绩统计表
1. 建立一个Excel工作簿, 并命名为“××学校田径运动会成绩册”。
2. 根据运动会的分组情况在工作簿中建立六个工作表, 分别为“七年级组男子”、“七年级组女子”、“八年级组男子”、“八年级组女子”、“九年级组男子”, 以及“九年级组女子”。
3. 根据规程中规定的项目和名次录取, 编制表格。打开“九年级组男子”工作表, 把第一行中A至I单元格合并, 输入标题“××学校×届田径运动会前八名成绩统计表”;在A2单元格中输入“九年级组男子” (设置单元格格式为不自动换行) ;在A3单元格内输入“名次”, 按住Ctrl键敲回车键, 输入“项目”, 进行格式调整使“名次”在右上, “项目”在左下, 添加斜线将A3单元格一分为二;在B3至I3单元格中分别输入“第一名”、“第二名”“第八名”;选中A3至I3单元格, 为各单元格添加边框;合并A4至A6、A7至A9、A10至A12在合并后的单元格内输入比赛项目, 如100米、200米等, 并为各单元格添加边框;按住Ctrl键, 用鼠标选中B4至B6单元格、C4至C6单元格I4至I6单元格, 在格式工具栏上选择边框为外边框, 此时, 从B4至I6就出现了8个方框, 每个方框为3行;将B4至I6选中并复制, 在B7单元格内粘贴, 这样从B7至I9单元格也出现如同B4至I6单元格一样的8个方框。同样道理, 根据比赛项目的多少制作出相应的方框。见表1。
(二) 制作各单位各名次个数与总分统计表
1. 制作各单位各名次个数与总分统计表空表
在“九年级组男子”工作表第50行 (根据项目数可调整) A50单元格内输入“单位”, 把第3行的B至I列单元格的内容 (“第一名”至“第八名”) 复制到第50行B至I列单元格内;在J50单元格内输入“总分”。在A51至A66中分别输入单位名称, 如在A51单元格内输入九 (1) 班、A52单元格内输入九 (2) 班在A67单元格内输入“合计”。选中A50至J67单元格, 通过格式工具栏上的边框按钮为每个单元格添加边框。见表2。
2. 输入查找各名次个数的函数
在B 5 1单元格内输入函数“=COUNTIF (B$3:B$50, “九 (1) ”) ”, 回车后B51立刻显示为“0”, 此函数的作用是:查找B3至B50单元格内九 (1) 班的个数, 并显示出来, 即九 (1) 班男子在比赛中所获第一名的个数;利用填充柄把这个函数向下填充到A66单元格;对B52至B66单元格内的函数进行修改, 使之与同一行A列单元格内的名称相符。如, 把B52内填充的函数“=COUNTIF (B$3:B$50“九 (1) ”) ”修改为“=COUNTIF (B$3:B$50, “九 (2) ”) ”。选中B51至B66单元格, 拖动填充柄至I66, 即可将B51至B66单元格内的函数填充到C51至I66中。此操作的作用是:使各单元格显示相对应的单位所获得相应名次的个数, 如I66单元格显示九 (16) 班所获第八名的个数。
3. 输入计算各名次合计个数的函数
点击B67单元格, 点击常用工具栏中的“∑”, 当出现函数=SUM (B51:B66) 时敲回车键。再拖动填充柄至I67。它的作用是:显示所对应名次的总个数, 以此对运算进行检验, 如B67显示的数字应等于九年级男子项目数;C67显示的数字应等于或大于B67显示的数字 (大于B67的情况出现于第二名并列时) , 否则运算出了问题, 需进行检查, 直至所显示的数字与事实相符;D67至I67显示的数字可大于、等于或小于B67 (小于B67的情况出现于前一名次并列或未录取该名次时) , 当大于或小于时, 要查明是否是因为并列或是未录取此名次的原因, 如果是, 说明正确;不是, 则运算出了问题, 需进行检查。
4. 输入求总分公式
根据竞赛规程中的计分方法, 在J51单元格内输入公式:“=B51*9+C51*7+D51*6+E51*5+F51*4+G51*3+H51*2+I51*1”, 此公式的涵义是第一名得9分, 第二名得7分第八名得1分;把前八名的得分相加。按回车键, 拖动填充柄至J67单元格, 则总分列的单元格会自动计算出相应班级男子组的总分。
(三) 制作金牌总数和团体总分表
在工作簿中新建一个工作表, 并命名为“九年级金牌总数和团体总分表”, 把表5复制到工作表中。选中B2至J18, 删除其中的公式。在B2单元格内输入公式:“=九年级组男子!B51+九年级组女子!B51”。此公式的作用是:把九年级组男子与女子第一名的个数进行相加。通过填充柄在其余单元格内输入公式。此时, B2至B17单元格内数字多的金牌数第一, J2至J17单元格内数字多的总分第一。第18行的作用是对运算进行检验, 作用同“ (二) 制作各单位各名次个数与总分统计表”中“3、输入计算各名次合计个数的函数”中的第67行。见表3。
二、及时准确地输入各项目的名次成绩, 金牌总数与团体总分自动生成
(一) 在运动会进行中, 及时准确地把各项目的名次成绩公布表输入到“前八名成绩统计表”中
打开“九年级组男子”工作表, 在第4行B4至I4共计8个单元格内分别输入获第一名至第八名运动员所代表的单位名称 (切记输入的单位名称要与在“表3输入计算各名次合计个数的函数”中输入的相应函数中的单位名称完全一致, 若格式不正确、错字、多字、少字时, 电脑将无法辨认, 会自动放弃查找功能, 影响比赛成绩自动显示的准确性。) 在第5行B5至I5共计8个单元格内分别输入第一名至第八名运动员的名称。在第6行B6至I6共计8个单元格内分别输入第一名至第八名运动员的比赛成绩。见表4。
各项目的名次成绩公布表准确输入后, 九年级男子各班级的各名次的个数与总分会自动显示。
(二) 及时检查修正, 保证数据的准确
要认真检查表8第67行中显示的数字是否与事实相符, 以防因名次并列造成不同单位记在同一个单元格内, 使电脑计数错误 (漏计) , 从而影响名次、总分统计的准确性。如有名次并列情况, 可采用手工方法直接对相关单位名次个数进行修改。对于因接力与破纪录等加分, 而造成的总分不准确的问题, 可采用手工方法, 直接对总分进行修改。
(三) 打开“九年级金牌总数和团体总分表”, 九年级各班级的各名次的个数与总分同时自动生成。
三、制作“金牌总数与团体总分前八名”表
(一) 新建一个工作表并命名为“金牌总数与团体总分前八名” (见表5)
(二) 制作“金牌总数与团体总分前八名”表
在“九年级金牌总数和团体总分表”中, 选中A1至J17单元格并复制;在A31处进行选择性粘贴, 选择“数值” (此操作的作用是只粘贴数值, 不复制公式与函数) ;选中B31至B47单元格, 点击常用工具栏中的排序按钮, 在“给出排序依据”中选择“扩展选定区域”, 即可得到金牌总数的排名。选中A32至B39并复制。打开“金牌总数与团体总分前八名”工作表, 在B4单元格处进行选择性粘贴, 选择“转置”, 当金牌数相同时以银牌为依据, 银牌多者列前, 如银牌仍相同则以铜牌为依据, 这样即可得到金牌总数排名。同理可得到团体总分排名, 如总分相同则以金牌数为依据, 金牌数多者列前。最后, 通过格式修改与手工调整即可完成。
四、制作成绩册
同理, 按上述操作把七年级、八年级的前八名成绩统计表、金牌总数与团体部分排名表制作好, 打印出来, 并与相关文件进行装订, 即可完成成绩册的制作。
事实证明, 运用Excel进行田径运动会的成绩统计是可行、准确、省时、省力的好方法。在比赛进行中, 它能随时准确地显示各单位的总分与金牌数;比赛结束时, 即可获得金牌总数与团体总分的排名, 并能及时准确地形成成绩册, 便于及时对各参赛单位进行评比和表彰。
关键词：Excel；教学管理；成绩统计分析系统
中图分类号：TP315 文献标识码：B 文章编号：1673-8454（2008）22-0065-03
在学校教学管理中，成绩管理与分析是考察教师和学生教与学情况的重要工作。目前，大多数学校基本上都是采用Excel进行处理，但他们大多只是用了Excel的表格功能，计算功能用得相当少。实际上，Excel中的公式及内置函数，为数据的分析与计算提供了强有力的工具，在教学管理中，特别是在成绩的处理和统计方面，利用Excel的强大功能完全可以实现复杂的成绩统计分析。它还有高效、灵活的编辑手段、直观的界面设计方法和强大的数据管理功能。
本文讨论的是利用Excel，不写程序实现成绩统计分析的系统。该系统具有：单次多科考试成绩管理、整体成绩统计分析、任意科目按班级进行对比分析、任意科目分数段对比分析图等功能，该系统工作簿整体效果如图 1所示。现举例说明制作过程。
一、建立工作簿、工作表及各图表框架
先创建一个成绩管理系统工作簿，右击工作表标签“Sheet 1”，改名为“成绩管理系统”， 再按图 1所示格式，在此工作表中建立考试成绩册、学生整体成绩统计分析表、单科成绩按班级对比分析统计表和图。
二、 设置“考试成绩册”
1.设置表头内容
为了使此表能扩展到较多的课程进行成绩管理，可以多预留一些成绩列，在此图中L和Q列间预留了4列，这4列的标题可以暂时不输入表头，当然，使用时还可以按实际的考试科目设置表头和输入具体的成绩，不用的列可以先隐藏起来（不能删除），这将不会影响下文所描述的公式的定义。
2.设置学生总分、平均分公式
由于预留了一些成绩列，故可在Q5单元中输入总分公式：=SUM(E5:P5)。
同样，可以在R5单元中输入平均分公式：=AVERAGE(E5:P5)，但此公式是假定各科以100分制为前提的。为了使此表统计功能更通用，可以为不同的科目设置不同的计分制，为此可以在成绩统计分析区的Y5至AJ5分别设置各科目的分制，如果各科的分制不同，则在计算平均分时，要将各科折合成100分制再计算。因此，要使此表变得更通用，则R5的公式就定义为：=AVERAGE(IF(Y$5:AJ$5>0,E5:P5*100/Y$5:AJ$5))，输入后按Ctrl+Shift+Enter离开此单元，即完成了数组公式，公式会自动用一对大括号括起来（下文中用大括号括起来的公式表示数组公式，输入方法与此处相同），显示形如：{=AVERAGE(IF(Y$5:AJ$5>0,E5:P5*100/Y$5:AJ$5))}。公式的意义是：对分制>0的科目（即考试的科目）的成绩乘以100后除以分制（即转换成100分制）后再求平均值。
3.设置总分名次公式
名次是学生成绩管理中重要的指标，一般是按总分排名，但并不一定要按总分排序，只要统计总分这一列中比当前行的总分多的记录数就可确定当前学生的名次了，为此，只需在S5单元中输入名次计算公式：=COUNTIF(Q:Q,">"&Q5)+1 。
4.设置单科名次公式
此表可以计算任意科目的单科名次，为此，可将AE20作为指定统计科目的单元，使用时可以在此单元格中输入要统计的科目名称，AF20中将自动用公式=MATCH(AE20,E4:S4,0)计算出此科目的序号。为了按指定的科目计算学生的排名，在T5单元中输入名次计算公式：
=COUNTIF(INDEX($E$1:$S$2004,0,AF$20),">0"&INDEX($E$1:$S$2004,ROW(),AF$20))+1
在T4单元中输入公式：=AE20&“的名次” ，则单科名次的标题将自动根据AE20中输入的科目变化。如果AE20中输入“语文”，则AF20中值为：1，T4中将显示“语文的名次”，T5中的公式相当于：=COUNTIF阶段(E:E,">0"&E5)+1，表示统计语文这列中比当前行的值大的记录数，加1后即为该生语文的排名。
5.复制公式
将以上设置好公式的Q5:T5这四个单元内容复制到以下若干行相应位置（也可以通过使用填充柄复制）。此表可以处理一个班的成绩，也可以管理一个年级的成绩，只要将Q5:T5依人数复制相应行数即可，当然也可以多复制一些行作为预留，多余行将不会影响各种统计公式的计算结果。为了考虑通用性，本文复制至第2004行，这样最多能统计2000个学生成绩。
三、设置“学生整体成绩统计分析”表
1.定义表头科目公式
在Y4:AJ4中输入公式引用成绩册中的科目，不必重新输入科目名，为此可以在Y4中输入公式：=E4，并将此公式复制到Z4:AJ4单元中。
2.设置考试科目分制
先在Y5:AJ5中输入各科目的总分（即定义分制），没有考试的科目的分制设置为0。并在AK5中设置各科总分和公式：=SUMIF(E5:P5,">0",Y5:AJ5)。然后在AL5中输入平均分计算的分制：100。
3.设置单科各项统计公式
在Y6至Y16中输入语文科目的各项统计公式：
=AVERAGE(E:E)
=MIN(E:E)
=MAX(E:E)
=COUNTIF(E:E,"<"&(Y5*0.6))
=COUNTIF(E:E,">="&(Y5*0.6))-Y11-Y12-Y13
=COUNTIF(E:E,">="&(Y5*0.7))-Y12-Y13
=COUNTIF(E:E,">="&(Y5*0.8))-Y13
=COUNTIF(E:E,">="&(Y5*0.9))
=COUNTIF(E:E,">="&(Y5*0.85))
=1-Y9/SUM(Y9:Y13)
=Y14/SUM(Y9:Y13)
并将这些公式复制到AL6:AL16中，以使这些统计项扩展到其它科目。
4.调整AK6:AL9的公式
由于成绩册中预留了多余的行，而这些行中的总分及平均分均为0，在统计平均分、最低分、不及格人数时均会将0统计进去，故在进行这几项数据（AK6:AL9）统计时，公式应该重新调整，调整的方法是：将这几项统计中Q:Q改为Q5:INDEX(Q5:Q2004,$Y23,1)，R:R改为R5:INDEX(R5:R2004,$Y23,1)，其中INDEX(R5:R2004,$Y23,1)表示最后一项有效平均分的位置，当然也可以直接修改为形如Rx具体的单元地址（x表示最后一条记录的行号）。
四、设置“单科成绩按班级对比分析统计”表
1.设置统计科目及成绩册的有关参数公式
为了使本表更通用，能按任意科目进行整体统计分析，可将本表中AE20设置为指定科目的单元，用户在使用时，只要在此单元中输入E4:S4中任意一门科目，此表将可按指定科目进行统计，形成按班级对比分析表。为了便于其它公式的编写，要计算出指定科目在E4:S4科目表中的序号，故AF20中公式为：=MATCH(AE20,E4:S4,0) ，找出指定科目的分制，以便给单科统计表中其它公式引用，故在AH20中输入公式：=INDEX(Y5:AL5,1,AF20)，计算出成绩册有效行数，故AL20公式为：=COUNTIF(C:C,"<>")+3，由于有效行数是按姓名进行统计的，故有效的学生记录必须保证姓名不为空，无效的学生记录保持学号、姓名及各科成绩为空。
还可为AE20设置下拉框提供科目名称选择，方法是：选择AE20单元→“数据”菜单→有效性…→允许：序列→来源：=E4:S4 ，使用时就会有一个下拉框供选择。
2.设置分班统计的参数区
为了进行分班对比分析，需要设置班级条件区，此表假定最多统计20个班的成绩，故在AM24:AM43中分别输入公式：=(D5=$X$24), =(D5=$X$25),…, =(D5=$X$43)，使用时只要在X24到X43中分别输入成绩册中各班的名字，参数区的20个单元自动从输入的班级名中选取要统计的班名生成条件，设置完成后，可以将AM列隐藏。
3.统计单科的整体情况
在Y23:AL23中分别输入以下公式：
=COUNTIF(C5:C2004,"＜＞")
=AVERAGE(OFFSET(E5:R2004,0,AF20-1,Y23,1))=MIN(OFFSET(E5:R2004,0,AF20-1,Y23,1))=MAX(OFFSET(E5:R2004,0,AF20-1,Y23,1))=COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.6)=COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.7)-AC23=COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.8)-AD23-AC23=COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＜"&AH20*0.9)-AE23-AD23-AC23=COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＞="&AH20*0.9)=COUNTIF(OFFSET(E5:R2004,0,AF20-1,Y23,1),"＞="&AH20*AH21/100)=1-AC23/Y23
=COUNTIF($T:$T,"＜="&AJ22)
=COUNTIF($T:$T,"＜="&AK22)
=COUNTIF($T:$T,"＜="&AL22)
以上公式中“OFFSET(E5:R2004,0,AF20-1,Y23,1)”表示指定科目的有效数据区域。AH21中存放要统计的分数点，可由用户自行输入。AJ22:AL22中分别存放要统计的名次段，以便使用时设置名次段。
4.设置班级各项统计公式
在Y24:AL24中分别输入以下公式：
=DCOUNT($B$4:$S$2004,$AE$20,AM23:AM24)=DAVERAGE($B$4:$S$2004,$AE$20,AM23:AM24)
=DMIN($B$4:$S$2004,$AE$20,AM23:AM24)
=DMAX($B$4:$S$2004,$AE$20,AM23:AM24)=SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.6*$AH$20))=SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.7*$AH$20))-AC24=SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.8*$AH$20))-AD24-AC24
=SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＜0.9*$AH$20))-AE24-AD24-AC24=SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＞=0.9*$AH$20))=SUMPRODUCT(($D$5:$D$2004=$X24)*(INDEX($E$5:$S$2004,0,$AF$20)＞=AH$21*$AH$20/100))=1-AC24/Y24
=SUMPRODUCT(($D$5:$D$2004=$X24)*($S$5:$S$2004＜=AJ$22))=SUMPRODUCT(($D$5:$D$2004=$X24)*($S$5:$S$2004＜=AK$22))=SUMPRODUCT(($D$5:$D$2004=$X24)*($S$5:$S$2004＜=AL$22))
将Y24:AL24中的公式按行复制到Y25:AL43中。
五、插入“单科成绩分数段按班对比分析图”
先按图1所示的位置，插入“图表”→“折线图”，数据区设置为：
= $X$21:$X$43, $AC$21:$AG$43，设置系列产生在“行”。
通过绘图工具，在Z46处插入文本框，并设置文本内容为：“单科成绩分数段按班对比分析图”。
设置AE48公式为：=AE20。
六、对成绩分析表进行加密保护
为了数据的安全性，防止用户有意或无意更改成绩公式，可对成绩统计分析表中部分单元进行加密保护。具体步骤如下：
选择X4:AM67→单元格格式→保护→选择“锁定”、“隐藏”。
用同样的方法取消Y5:AJ5、AE20、X24:X43、AH21及AJ22:AL22等单元的“锁定”、“隐藏”。
设置Q4:T2004单元“锁定”、“隐藏”。
取消B2:P2004单元的“锁定”、“隐藏”。
单击菜单“工具—保护—保护工作表”，输入密码。以后如果想改动统计公式，必须输入密码才行。最后保存工作表，命名为“成绩管理系统”。
七、结束语
至此成绩统计分析表的各项统计公式全部设置完成。使用时只要修改成绩册中的学生姓名及科目名称，输入各项成绩，并清除不考试的科目及成绩，清除B5:P2004中无用的学生成绩记录，处理完后，其它图表将自动进行统计与绘图。经多所学校的使用，效果良好。
但是在共享的状态下有很多功能不支持，如下：
补充一下，还不支持VBA的运行，我们可以从中看到，在共享工作簿的状态下有很多不能使用，无法发挥EXCEL最大的威力，但大家仔细看看，其实在数据录入的过程支持的可用功能已经足够使用了，下面就把详细的步骤图文并茂的展示给大家，
一、首先要保证你在局域网内网中联网正常，在你的盘符下新建一个文件夹，比如我们在F盘建立一名为“heisha”的文件夹，新建后右键→属性选择该文件夹，在共享中共享此文件，如图：
在网络共享和安全中，两个“√”都要选择上，否则其他用户无法保存。确定后该文件夹图标会变成共享状态
二、下面我们就要将我们的录入程序设置为共享模式并放进“heisha”这个共享文件夹中：
1.打开我们需要共享的工作薄，这里我们用城镇低保录入程序为例(以下简称程序)，在审阅选项卡上的更改组中，单机共享工作簿。如图：
打开后我们会看见共享工作簿的选项卡，如图：
需要注意的是必须勾选上允许多用户同时编辑，同时...的选项，确定后EXCEL会提示你保存，如果你共享的工作薄包含VBA，他还会提醒你VBA与宏在共享下无法编辑，最后我们可以看到，这个程序的名称后多了一个共享的字样，
-2-24 14:51:17 上传下载附件 (5.08 KB) 现在说明这个工作薄就是共享状态了，最我们将这个设置成共享工作薄的程序放到“heisha”这个共享文件夹当中。如图：
好了，录入的前期工作我们都作好了，那么怎么才能让别人也进入到这里一起使用该程序呢?首先要知道该电脑在内网中的IP地址或者电脑的名称是什么，关于这两个问题不说太多。比如这台机器的名称叫“SUN” >
好了，录入的前期工作我们都作好了，那么怎么才能让别人也进入到这里一起使用该程序呢?首先要知道该电脑在内网中的IP地址或者电脑的名称是什么，关于这两个问题不说太多。比如这台机器的名称叫“SUN” >
好了，录入的前期工作我们都作好了，那么怎么才能让别人也进入到这里一起使用该程序呢?首先要知道该电脑在内网中的IP地址或者电脑的名称是什么，关于这两个问题不说太多，
比如这台机器的名称叫“SUN” >
好了，录入的前期工作我们都作好了，那么怎么才能让别人也进入到这里一起使用该程序呢?首先要知道该电脑在内网中的IP地址或者电脑的名称是什 么，关于这两个问题不说太多。比如这台机器的名称叫“SUN”，那么我们在文件夹的地址栏中输入sunheisha就能通过局域网进入这台电脑的， 并且可以编辑保存如图：
2.下面演示一下局域网共享协作：
先看一下图片：
从上面的图片可以看出，在局域网内，其他用户将这个程序修改后保存，我再保存，就可以看见其他用户所修改的信息，需要注意的是，如果不保存是看不见对方所录入或者修改的信息，当然了，如果你修改并保存，其他用户保存后也可以看见你修改的数据。
3. 接受或拒绝修订。什么是接受或拒绝修订呢?上图我们看见，某用户修改了七个heisha，我是这个设置工作薄共享的人，这个程序在我的机器中，这里的我， 相当于主机，而某用户相当于访问、协助我的作用，但是某用户修改的这七个heisha我是否同意修订(修改)呢?或者说，这七个heisha是谁修订的 呢?看图说话：
时间：选择当天日期或者选择无;
修订人：你可以选择某个修改过这个程序的用户，如果不知道就选择每个人或者除我之外每个人;
位置：你需要审核修订的范围，在这个例子中，七个heisha就是我们的位置。
确定后我们就开始接受或者拒绝了，继续看图：
这样我们即知道了是谁修改的数据，发现数据错误后也可以选择拒绝来返回原来的文字，非常不错。
4.突出显示修订。这个功能就不必多说了，这个功能就是在某用户修改并保存数据后，我们再保存数据，就能看见类似于批注的东西，看图：
看见了吧!这就是所谓的突出显示修订。
5.共享工作簿里的高级选项。看图：
修订：这里是保存修订纪录的天数，也可以选择不保存。
更新：默认是在保存的时候才更新其他用户的数据，也可以自己设定自动间隔时间。
用户间的修订冲突：我一直没遇到过冲突问题，只要分工明确就不会遇到这种问题，所以在这里不做解释。
在个人视图中包括：对勾去掉的话其他用户将无法看见打印设置和筛选设置。
三、如何取消共享：
如果上面的你全部掌握的话，取消共享就非常简单了，审阅→更改→共享工作薄→编辑→将允许多用户同时编辑的对勾取消，确定后会出现提示，如图。
最后选择是，工作薄同时保存。
案例演示
①首先，我们单击菜单栏--数据--筛选，这样标题行就会出现下拉箭头，单击D列的下拉箭头，勾选广东，确定。
②这样就选出了广东地区的所有人，选择E2：E9单元格，按下F5键，弹出定位对话框，点击定位条件按钮。
③在弹出的定位条件界面，勾选可见单元格，确定，
④然后直接输入公式：=COUNTA($E$1:E1)。
⑤按下Ctrl+Enter，才能显示正确的结果，然后取消筛选，还原最开始的数据，我们看到编号依然不变。
公式说明
counta(区域)：统计区域内非空单元格的个数。
Excel2010作为常用office2010办公软件的一个组件,是一款功能强大的电子表格处理软件[1]。它具有强大的数据计算、数据统计和分析功能[2],能够处理我们生活工作中遇到的大多数数据统计分析问题,并且操作简单便捷,深受人们的喜爱。大部分学校的学生成绩管理都用到此软件。快捷的数据输入,直观的数据显示和有效的数据分析能够减轻老师的工作负担,提高工作效率,更好服务于教学[3]。本文介绍了几种在应用EXCEL2010管理同学成绩过程中总结出的实用小技巧。
2 巧用自定义数列填充,快速输入学生名单
一般来说,我们在录入成绩时,经常需要输入本班同学的姓名,这时候,我们可以把本班学生名单做成一组自定义序列,这样如果我们下次再需要输入学生名单,就可以直接输入第一个同学,然后拖动填充柄,全班同学的名字就都可以调出来了。
步骤如下:
1)选择文件—选项—高级—编辑自定义列表如图1。
2)将学生名单导入或者写入输入序列栏,然后点击添加,学生名单就添加到自定义列表中了。如图2:
3)点击确定,这样我们就可以在excel中轻松调用名单了。如图3:
3 巧用自定义数字格式,进行长数据的快速录入
我们在录入学生数据时,需要录入一些比较长的连续数据,比如学号。因为学生的学号大多是连续的,所以我们可以用等差数列快速填充的办法来实现快速录入。但是,有时候因为学号太长,输入后会自动转化为科学计数形式,而影响显示效果。将其变成文本格式虽然能解决显示问题,但是前面所说的快速填充的办法又不能再用了。有没有既可快速填充,又能正确显示的好办法呢?我们可以用自定义数字格式的方式来实现。具体步骤是:选择学号列,右击调出设置单元格格式窗口,选择选项卡“数字”中的“自定义”,在右边的类型中输入数字,如图4,有几位数就输入几个零。这样,再填充长数字序列时,就不会变成科学计数显示了。
4 巧用条件格式分色显示学生成绩
在统计学生成绩时,我们需要直观的特别关注一些学生的情况:比如前三名,后三名,低于平均成绩的学生。我们可以用条件格式的办法,给不同层次的成绩设定不同的颜色,使得成绩分析更加直观。比如如图5,我们将语文数学英语三门课的前两名用红色显示,后两名用绿色显示。具体操作步骤:首先选择“开始”菜单“样式”选项卡中的“条件格式”,然后选择“项目选择规则”中的最大项或者最小项并设置它们的项目数为2就可以了。
5 巧用数据透视图分析学生成绩变化
在excel2010中还有一个非常好用,却常常被忽视的工具,那就是数据透视图表,用它可以动态的分类统计数据。比如我们可以用它分别统计并用图表形式显示每个学生的在一段时间内的成绩变化,也可筛选某个考试时间内的某门课的平均分总分等等。具体的操作是:首先选择“插入”菜单“表格”选项卡中的“数据透视表”。然后将需要统计的数据选入数据透视区域。如图:点击确定,进入数据透视表的设计界面。
数据透视表设计界面有四个区域,分别是报表筛选,行标签、列标签和数值。我们将源表中的字段分别拖动这个四个区域即可。其中报表筛选的作用是筛选数据,比如,当我们想查看某位学生的各科成绩时,就可把学生姓名放到报表筛选中。行标签和列标签分别表示行和列要显示的字段。比如:我们想分析某位学生在一段时间内各科成绩变化,则将考试时间拖到行标签中。数值是要汇总的数据,汇总方式可在“值字段设置”中选择,此处,我们汇总的是各科成绩的平均值。四个区域设置好,数据透视表就做好了如图7。在数据透视表的基础上,我们还可做出更加直观的数据透视图。
总之,Excel2010中藏着很多非常实用的小技巧,我们只要善加利用,就可以快速准确省时省力的把学生成绩统计分析好。
参考文献
[1]欧阳红东.数据处理中Excel的应用分析[J].电脑知识与技术,2014(9):2051-2053.
[2]孟春利.Excel在信息技术应用中的技巧[J].电脑知识与技术,2014(9):2047-2048.
关键词：Excel函数,成绩表
Excel函数在Excel电子表格中是很重要的一部分内容,也是Excel使用方便的关键所在。Excel函数的使用渗透于生活的方方面面,本文就Excel函数在学生成绩表中的应用做简单的举例说明。我们在使用Excel函数之前,首先要把学生成绩的原始数据输入到Excel电子表格中。下面我们就对成绩表中常用的几个函数做一一说明。
1 用Sum函数统计每个学生的总成绩
Sum函数的语法为:sum(number1,number2……)其功能是计算所有参数数值的和。number1,number2……代表需要计算的值或引用的单元格(区域),它们可以是连续的单元格区域,也可以是不连续的。例如图1所示,我们要在G3单元格中计算张民同学的总分,先选中G3单元格,然后再单击fx插入sum函数,出现“函数参数”对话框,可以直接在数值1框中输入D3:F3;也可以用鼠标在表格中拖动选择D3:F3单元格区域,然后按Enter键,总分就计算出来了。然后利用Excel的自动填充功能向下拖动鼠标,这列的总分就全算出来了。我们这里计算的”D3:F3”是一个连续的单元格区域,Sum函数还可以对不连续的单元格区域求和。只要在出现的“函数参数”对话框中的数值1输入框中输入一个连续的区域,数值2输入框中再输入另外一个连续的区域,数值1和数值2之间可以是不连续的。如果有更多的不连续区域求和,将鼠标定位在数值2后面的输入框中,函数会自动出现数值3输入框。用同样的办法可以出现数值4等更多的数值输入框让我们进行输入。当然我们也可以直接在函数表达式中输入这些不连续的区域,区域之间用逗号分隔。大部分人在用Excel函数求和的时候,只知道用∑自动求和或用sum函数对连续的单元格区域求和,却不知道不连续的单元格的和也是可以求的。
2 用Average函数计算学生的平均成绩
Average函数的语法为:Average(number1,number2……)其功能是计算所有参数数值的平均值。其用法和sum函数类似,也是先选中单元格,再插入Average函数,然后再选择要计算的单元格区域,之后回车。这里不同的就是平均分算出来有时小数点后面的位数会很多,这时可以单击工具栏上的增加或减少小数位数按钮来实现;也可以打开“设置单元格格式”对话框在里面的”数字”选项卡中设置。
3 用Rank函数统计学生的名次
Rank函数的语法为:Rank(Number,ref,order),主要功能是返回某一数值在一列数值中相对于其他数值的排位。其中”Number”表示需要排序的数值(单元格地址);”ref”表示排序数值所处的单元格区域;”order”表示排序方式参数。”Order”参数值如果为“0”或者省略,则按降序排名,如果为非“0”值,则按升序排名。如图2,假定根据总分排出名次,在I3单元格中输入=Rank(G3,$G$3:$G$11),日常生活中我们的名次一般是按总分的降序排列的,所以这里的第三个参数我们省略,当然它也可以写为“0”。这里的第二个参数“$G$3:$G$11”用了绝对引用符“$”,因为在算出第一个学生的成绩后,鼠标在向下拖动填充计算其他同学的名次时,如果不加这个绝对引用符,名次列就会有很多重复名次出现。这是因为我们在排名次时第二个参数“ref”的单元格地址区域在鼠标向下拖动时改变了,也就是到I4单元格时第二个参数”G3:G11”就变成了”G4:G12”,到下面的单元格时会依次类推,这样就改变了排序范围。而其实我们需要排序的单元格区域一直都是G3:G11,所以要加上绝对引用符,结果才会正确。这就是很多同学在这里出错的原因。
图
4 用If函数判断成绩等级
If函数的语法为if(Logical_test,Value_if_true,Val ue_if_false)。它是根据指定条件判断真假,返回不同结果的函数。假如我们均分在60分及60分以上的同学为“及格”,60分以下的成绩为“不及格”。那么我们可以在图3的I3单元格中输入:=if(H3>=60,”及格”,”不及格”)。这是最简单的if语句,if函数还可以嵌套if函数,最多可嵌套7层。我们就可以用if函数的嵌套来完成学生成绩等级的判定。假如我们规定90分以上为“优秀”;80~90为“良好”;70~80为“中等”;60~70为“及格”;60分以下为“不及格”。那么要判断均分H3的等级,其if语句如图3所示=IF(H3>=90,"优秀",IF(H3>=80,"良好",IF(H3>=70,"中等",IF(H3>=60,"及格","不及格"))))。关于这样的if语句用如图4的树形结构图来表示其嵌套关系就更明了了。
5 用COUNTIF()条件计数函数和FREQUENCY()频率分布函数统计各分数段的学生人数
1)COUNTIF()函数是统计某个单元格区域中符合指定条件的单元格数目;有两个参数,第一个参数代表要统计的单元格区域,第二个参数表示指定的条件表达式。如图5,如果要统计平均分在90~100分的人数可以用函数表达式:=COUNTIF(H3:H11,">=90")
如果要计算80~90分之间的人数我们可以用80分以上的人数减去90分以上的人数,在编辑栏中输入=COUNTIF(H3:H11,">=80")-H12就可以完成。其余分数段的人数用类似的方法得出。
2)Frequency(data_array,bins_array)函数是一个频率分布函数,它具有统计各区间的频数的功能,它也有两个参数,第一个参数是要进行统计的数据,第二个参数是分组的依据,也就是分段的界值。该函数返回的是数组,必须以数组公式的形式进行输入。所以在输入时首先要选中输出结果的一串区域,并不是某一个单元格(这往往是同学们容易出错的地方),在编辑栏输入完公式后按下组合键Ctrl+Shift+Enter,使之成为数组公式(公式会自动加上花括号,不需要手动输入)。假如我们要求0~59,59~69,69~79,79~89,89以上这五个分数段的人数,那么需要的4个界值分别为59,69,79,89;。如图6,我们先把“59,69,79,89”四个界值放在H4:H7的单元格区域,四个界值代表五个分数段,所以我们要选中五个单元格区域I4:I8,然后再插入函数,在出现的参数框中输入相应的区域,最后按下Ctrl+Shift+Enter组合键,使之成为数组公式,公式会自动加上花括号,这样函数才能用对。很多同学在完成之后直接输入Enter键,函数肯定是会出错的。对于H4:H7我们也可直接输入为59,69,79,89这四个数值,不过在直接输数值的时候我们要在数值的两边加上花括号(这和组合键生成数组的花括号是两码事),如图7函数语句我们可以这样输入=FREQUENCY(E3:E12,{59,69,79,89}),然后按下组合键Ctrl+Shift+Enter,公式会自动变成{=FREQUENCY(E3:E12,{59,69,79,89})}。
FREQUENCY()函数比COUNTIF()函数难理解,但是用的熟练了,FREQUENCY()函数比COUNTIF()函数计算起来更快,更方便。可能这里有人说实际上我们一般算的是X<60,60<=x<70,70<=X<80,80<=X<90,X>=90这样的等级区域,而X<=59和X<60还是有区别的。是的,但是如果写成60,70,80,90,各个分数段又包含了60等这几个临界值,如果要统计不及格的人数就连60分的人也包含在内了,就更不符合实际了。所以为了更贴合实际一点,我们可以把临界值设置为59.99,69.99,79.99,89.99,因为成绩分数一般最多都是保留到两位小数的。
6 结语
利用上面这些函数我们可以方便的统计一个成绩表,只要学生的原始成绩要更改,这些用函数算出来的值也会相应的变化,工作起来是很方便的,学生慢慢地掌握这些函数,在以后的生活中也是大有用处的。这里需要特别说明的是:Excel函数使用中用到的标点都应输入英文标点,要不然函数就会提示出错。
参考文献
[1]黄国兴,周南岳.计算机应用基础[M].高等教育出版社,2012.3
[2]吴亚坤,王三虎.大学计算机基础[M].辽宁大学出版社,2013.8
[3]王宇川,刘继华,郭思延.计算机应用基础(上)案例教程[M].中国商业出版社,2010.1.
【EXCEL进行学生成绩管理】推荐阅读：
从中考成绩进行教学反思12-14
小学生如何进行自我介绍10-12
课堂上如何对学生进行评价11-13
怎样对学生进行民族团结教育12-15
高三复习如何高效进行(学生篇)12-16
对小学生进行评价的方式07-07
《教师怎样进行学生评价》读后感09-10
午餐进行曲小学生作文10-01
浅谈如何对学生进行孝敬父母教育06-12
把握学生心理特征进行音乐教学反思11-17
