用EXCEL做一个学生成绩管理系统
Excel的数据处理功能的强大鲜为人知，许多老师在教育教学的辅助应用上对EXCEL的应用只局限在Excel的简单功能应用上，对于Excel的函数功能却始终停留在求和、求平均值等简单的函数应用上。
其实，Excel函数作为处理数据的一个最重要手段，功能是十分强大的，许多知名企业在数据管理上还喜欢应用Excel。本人经过一年多的学习研究，在学生成绩管理上应用Excel函数可以把它做成你可能想不到的复杂的统计管理表格或者小型的数据库系统。
另外，应用Excel函数功能制作出的表格具有一次设计好后可重复使用，只需要改变原始数据，就可以得到相应的结果，并且不用重复进行打印设置。
本人所做的这个成绩管理系列表格。虽然对于使用函数的高手来说，这些函数可能很熟悉，但是，各种统计技巧的应用可以为参考。我写此文章的目的不是为了让教师去研究函数的应用，仅为了便于各个水平段的老师参考应用在自己的工作中去，实现快捷化的成绩统计的自动生成。下面就依照整个系统的制作过程进行描述，便于同志们同步制作与使用。
excel自动生成排名
选择区域技巧：①在填充公式或者数值时，我们常常会因为自动填充速度快拖拉位置难以确定而烦恼。那么我们只要选中要复制填充其内容的这个单元格，然后用鼠标流动或者点击滑条翻页到要填充至此的那个单元格，先按住shift键再点击此单元格，我们会看到整个要填充的区域被选中。②按shift键加上下光标键，可以自由选取区域大小，直到满意为止，再松开shift键。
填充技巧：在填充区域被选中的情况下我们按ctrl键加enter键，整个区域就自动填充该数值或者该公式。
一、总成绩表的制作
总成绩表是所有数据的源。如图：
此表的作用是：1、原始成绩的输入；2、各班成绩单的打印。
成绩总表的制作：
1、求和。也就是计算每个学生的总分成绩，图例中是G列。选中G2单元格，然后点击求和按钮或者直接输入“＝SUM（H2：P2）”按ENTER，正确后就拖拉填充至整个成绩表的最后一行即可。
2、求班名次。新建一列（图中的E列），函数：=RANK($G768,$G$682:$G$781)　函数意思：G768单元格中的数值在G682至G781中的排名。公式可以上下填充。不同的班级只须把区域范围变一下。如10班改为“$G$782:$G$881”后填充10班的该列区域。
3、求在年级中的名次。公式与求班名次一样。不同的是总分范围：从第2个单元格至最后一个单元格。可以手写输入也可以在公式编辑状态下用鼠标拖拉选中总分范围。图例中没有从第2个单元格起是因为我们在12个班分两部分。
4、插入班级一列，以便于按班级排序和按班级打印成绩单。
5、插入学号一列，以便于输入学生成绩时参考。
6、每个班固定一定的行数并且比人数最多的班再多几行，整十整百更好，以便于以后公式的编辑和打印各班成绩单时的纸张设置。这里前六个班每班80行，后六个班每班100行。
7、此表制好后应输入或者从以往的成绩单中复制来一些名单、数据便于制作其他工作表时参考也便于编写后面公式时可以看出效果。
二、制作年级“前X名”的工作表
1、表格样式如图：
要得到这样一张既有级名次、班级、姓名、总分项目，又能在每次打印时不用重新排版设计，
一劳永逸，我们就要做一个动态连接的表，以不变应万变。请按以下步骤做：
1、新建一个工作表，我们把它取名叫“桥”。其意是在总表与前X名之间建立一种连接和过度。我们把总表中的“级名”、“班”、“姓名”、“总分”名列分别复制，再在“桥”中粘贴连接，就形成下图中表的样式。
2、再新建一个前X名的工作表。如上图。本例所示意为A4纸张，每张三栏，一栏50名学生。先设计好标题后，就可以从“桥”中选取前四列，如图例选取前四列的前50人的区域复制。在此表中也粘贴连接于前50名预留处。依此类推，完成前X名的连接工作。
3、我们要打印前150名、前300名等时，只须要把“桥”中的数据按总分递减排序即可。另外，
注意每次修改完成绩，“桥”中都要重排序。图例中总分427.1来源于总成绩表的R762，意为：这个成绩就是总成绩表中的R列（总分列）第762个单元格的内容（成绩）。
三、及格人数、及格率、平均分、优秀人数、优秀率
表格的设计见下页图，（为了能看到更多的项目，图中的其他班级被隐藏）也可以设计成其他形式。如果我们设计的列标与“总”表各科列标顺序一致，在填充公式时会大大方便。下面介绍的公式编写内容是在我们所需要显示内容的单元格里进行。
“语文60”制作方法：单元格中输入60数字后击鼠标右键进行设置。选择窗体中的自定义，再在右边框内选取“G/通用格式”，然后在上面的小编辑框中编辑为“语文G/通用格式”确定之后我们在单元格内就看到 “语文60”。任意改数值语文不变。其他各科方法同。
同类型公式填充的方法与技巧：前面我们所提到相同公式的填充技巧，这里我介绍的是同类型的公式填充的技巧。如我们编辑每个班的及格人数时，不同的班级公式中仅是区域的不同，所以我们拖拉填充公式后再逐个的改动区域数字就行了；不同科目的填充可以用鼠标按住填充柄向左右填充即可得到其他各科的各种数据。此表中的“次”（既名次）这列是在所有
公式填充完成后再逐个插入列编写（方法同班名次，只是范围小）。语文各项编写填充完成之后选中全部各项名次，按住ctrl键用鼠标拖拉复制到其他各科目后的名次列既可得到其他各科的各项目班级排名。
1、及格人数和优秀人数：公式的编辑为：=COUNTIF(总!H982:H1081,">="&C1)　。COUNTIF的函数意义是“计算满足条件的单元格的数目”，其中“总!”意是工作表名称(后面不再重述)，“H982:H1081”指的是一个区域范围（在示例中是三(12)班语文），">="&C1这是表达式的条件意思：大于等于C1单元格中的数值，这个条件是一个可以变化的值，同样我们可以用来计算优秀人数，只要我们把C1中的60改为80既可。当把及格人数的各班各科公式编好后复制并且改为优秀人数，既得优秀人数栏。注意双引号必须是非中文输入状态。公式写完按ENTER按完成编辑。我们会看到在这个单元格里显示的数字就是某班的语文学科及格人数。
2、及格率和优秀率：公式的编写为：＝C7/COUNTA(总!$H$982:$H$1081)。C7是三（12）班的及格人数，COUNTA的函数意是“计算非空单元格的数目”。也就是计算某班语文学科的参考人数。公式写完按ENTER按完成编辑。这时我们看到的只是一个小数而不是百分
数。仅需要在此单元格内单击右键选择单元格格式，在弹出的对话框里选择数字标贴，在选择框中到百分数选中，按确定即可显示百分数。优秀率参考及格率制作。
3、平均分：公式的编写为：=AVERAGE(总!H982:H1081) 。AVERAGE的函数意义是：“计算某数字单元格区域的算术平均值”。示例中意为：计算“总”工作表中H列第982个单元格至第1081个单元格的所有非空单元格数字的平均值。既为：三(12)语文。
四、求各班在年级总分前X名的学生人数
1、表格样式如下图：公式编写为：=COUNTIF(总!$D$402:$D$501,"<=36")。COUNTIF这个函数前面我们已经熟悉。完整意为：计算在D列中第402个单元格至第501个单元格中数值小于等于36的单元格数目。“36”是年级前36名的意思。式中的“$D$”指的“总”工作表的D列(也就是学生的年级名次这一列)，其中的“$D$”等同与“D”。“$”的作用以及单元格中出现的“人”字的方法将会在其它相关文章中介绍。
2、计算各班前50名、前80名、前X名只需把公式中的36改为50、80、X即可。
3、自动更新前X名
我们已经设置好的前X名，有时需要变换具体的数字。比如前36名要改为前40名等等。按照第1步的公式，我们必须进行每一列的替换，把36替换为40，那么，我们就来修改一下这个公式，让它能适应前X的变化。在原有公式中：
=COUNTIF(总!$D$402:$D$501,"<=36")中的36替换为B2单元格中的数字。公式应该写为：=COUNTIF(总!$D$402:$D$501,"<= "&B2) 这时又发现结果是“0”，这是因为B2单元格的设置问题。我们必须把B2单元格中“前36名”改为清除后再直接改为数字“36”，这样就会看到我们所需要的数值。为了看到“前36名”这样的效果，我们再回头把B2单元格进行设置，方法前面已经介绍过。
公式以为：=COUNTIF(总!$D$402:$D$501,"<= "&B2)之后，我们会看到这样的效果：当把B2中输入数字“50”时所得到的结果与C列前50名的结果一样。如果第一个单元格中的公式都改为：=COUNTIF(总!$D$402:$D$501,"<= "&B2)样式，那么，我们想要前几名、前十几名、前几百名时仅需要在列标题行改动一下数字即可。
五、分段、分班、分科平均分
1、首先，我们把“各班优秀人数表”复制一份。把我们所需要的单元格进行拖拉重新组合（为了保证公式所引用的区域不变必须是拖拉而非复制）为我们所需要的形式如图：
2、A列B列就是原表的所有内容。我们再建一列C列为段中人数。相信这个数字的得来大家都知道是同一个班的前50名单元格减去前36名单元格（均用单元格的位置编码如B12-B5这种形式）。
3、C列每个小标题：如：36至50的自动生成。编写：＝B2＆”至”&B9即可；再向下的小标题
仅需要在引用单元格后面加1，如：＝B9+1＆”至”&B16。
4、分段平均分的计算：公式：
=AVERAGE(OFFSET(总!I402,0,0,$B7,1))
函数示意：AVERAGE是示平均值；OFFSET是一个以某个单元格为参照的区域引用函数。AVERAGE (OFFSET(总!I402,0,0,$B7,1) )的表达式意思是：求OFFSET(总!I402,0,0,$B7,1)这个区域的平均值，OFFSET(总!I402,0,0,$B7,1)的区域是：以“总”工作表的I402这个单元格为参照，第一个“0”就是从本行开始向下0行为起点（即402行），第二个“0”就是从本列开始向右第0列为起点（即I列），$B7代表区域的行数（即9行），引用“B7”的目的就是为了适应每次成绩的变化，因为每次考试该段的人数一定是变化的，如果我们每次改公式中的数据，那么我们编写公式也就没有意义了。 “1”代表区域的列数为1列（在此即为I列）。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。