用EXCEL做一个学生成绩管理系统.doc

用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,=&D2)公式中的表达式意义：$B5=0,即B5单元格数字为“0”时此单元显示空白，否则，按下一层函数执行。总!H1=0，意义是“H1是语文的标题，如果语文这一科没有考试就把：“总”工作表的语文标题清除，相应的是D3这个单元格中也会显示空白（而不显示错误符号），否则，按下一函数执行即：COUNTIF(OFFSET(总!H682,0,0,$B5,1),=&D2)。这才是真正重要的内容。“OFFSET(总!H682,0,0,$B5,1)”前面已经提到;而“=&D2”这个条件却是需要介绍的，意义是：大于和等于D2单元数值。（而D2单元格又嵌套着一个可变函数值，下面要说到）。下面就把整个函数式的意思解释：“如果B5=0，则D3显示空白;如果H10，则D3还显示空白;否则显示在“总”工作表中从H682算起向下“$B5”个（这里为8个）单元中大于等于D2（这里为74.42）数值的单元格个数。这个个数就我们所要的语文上线人数。关于D2、C2、J2的公式编写。以D2为例：=AVERAGE(OFFSET(总!H$682,0,0,$C$5,1),OFFSET(总!H$782,0,0,$C$6,1),OFFSET(总!H$882,0,0,$C$7,1),OFFSET(总!H$982,0,0,$C$8,1)这个公式解释起来比较简单：求年级前200名的语文平均分。其中，总!H$682、总!H$782、总!H$982为各班语文在总表中的起始位置（也是参照位置），$C$5、$C$6、$C$8为以各参照位置为准向下包括的单元格个数。“$C$5、$C$6、$C$8”又是一个个可变函数。下面接着介绍。以$C$5为例：=COUNTIF(总!$F$682:$F$781,=&C2)这个函数前面也已经提到过在这里只把它的应用意思讲解一下：求三（9）班年级名次中小于等于“C2”的单元格个数。这样我们就得出来前200名中三（9）班有几人，也便于前面讲到的函数进行引用。那么，C2是一个可变值，因为我们会根据需要选择参考前多少名的平均成绩为参照。C2的值一变，上面我们介绍的内容也就跟着变。另外，在这里介绍一下“$”作用。“$”只有在进行填充公式时才用得着。有些公式我们希望随着单元格的变化而进行变化，而更多时候我们需要引用的单元格只向行的方向变化或者只向列的方向变化，这时就需要“$”来帮忙了。上例中的公式我们需要填充到五段中，十二个班，七个科目的单元格中（1257420个单元格中）。如果我们希望向左右列中的同一行填充公式而所引用的单元格不变，那么我们只须代表此单元格的列标字母前加上“$”即可。就向上面公式里的“$B7”，在该班级该段中各科引用的都是“B7”这个单元格，我们当然希望从语文这里向右填充时B7不变化就。如果不加“$”，就会随着填充而变为C7、D7、。有人知道有个“绝对引用”是字母前后都加“$”，但是，不要忘了我们的公式还需要向下填充；而向下填充时我们又希望B7会变成B8、B9、，如果字母后面加了“$”，那向下填充时就不会变了，我们还得一个一个的修改。最后，我想大家这样理解和记忆是有效的：“$”就是“绝对引用”的意思。而且，我们又知道“B7”中的B就是B列，“7”既第7行。例如：第一种情况：绝对引用某一单元格，如： “$B$7”读作：“绝对引用B列又绝对引用7行”，这样一来不管往个方向填充公式B7的引用不变。第二种情况（绝对引用列）：“$B7”读作：“绝对引用B列而不限制为第7行”，那么填充时数字7就会随着行的变化而进行变化，第三种情况（绝对引用行）：“B$7”读作：“不限制B列而绝对引用第7行”，无论向哪个方向填充都引用的是第7行。七、个人查分成绩单的查询1、个人单次考试的成绩查询（也可查多人）公式格式为：=IF($A3&$B3=,IF($A3=,VLOOKUP($B3, 总!$D$2:P$1081,13,FALSE),VLOOKUP($A3,总!$C$2:P$1081,14,FALSE)释意：如果A3和B3都为空，那么查询也为空，否则按第二判断；如果A3为空，则按B3内容在“总”表中查找$D$2:P$1081区域首列中与B3相同的单元格，然后返回这一行的第13中的数据。“FALSE”的意思为精确查找。这样，我们只要把公式左右填充之后修改其中的返回列数字即可得到该学生的各科成绩。这时我们只要再选中这一行的各科公式单元格向下填充至平时所能用的人数为止。一般情况下填充30行足够。2、个人多次考试成绩的查询比较注意：在进行这一步之前我们应该确定以往各次的成绩单同当前的成绩单工作表完全一致，否则是进行不成的。公式格式为：=IF($A$3&$B$3=,IF($