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

1、用EXCEL做一个学生成绩管理系统Excel的数据处理功能的强大鲜为人知，许多老师在教育教学的辅助应用上对EXCEL的应用只局限在 Excel的简单功能应用上，对于 Excel的函数功能却始终停留在求和、求平 均值等简单的函数应用上。其实，Excel函数作为处理数据的一个最重要手段，功能是十分强大的，许多知名企业 在数据管理上还喜欢应用Excel。本人经过一年多的学习研究， 在学生成绩管理上应用 Excel函数可以把它做成你可能想不到的复杂的统计管理表格或者小型的数据库系统。另外，应用Excel函数功能制作出的表格具有一次设计好后可重复使用，只需要改变原始数据，就可以得到相应的结果，并且不用重

2、复进行打印设置。本人所做的这个成绩管理系列表格。虽然对于使用函数的高手来说，这些函数可能很熟悉，但是，各种统计技巧的应用可以为参考。我写此文章的目的不是为了让教师去研究函数的应用，仅为了便于各个水平段的老师参考应用在自己的工作中去，实现快捷化的成绩统计的自动生成。下面就依照整个系统的制作过程进行描述，便于同志们同步制作与使用。选择区域技巧：在填充公式或者数值时，我们常常会因为自动填充速度快拖拉位置难以确定而烦恼。那么我们只要选中要复制填充其内容的这个单元格，然后用鼠标流动或者点击滑条翻页找到要填充至此的那个单元格，先按住shift键再点击此单元格，我们会看到整个要填充的区域被选中。按shift

3、键加上下光标键，可以自由选取区域大小，直到满意为止，再松开shift键。填充技巧：在填充区域被选中的情况下我们按ctrl键加enter键，整个区域就自动填充该数值或者该公式。一、总成绩表的制作总成绩表是所有数据的源。如图:踐1MIJ Y A辽A£1 i4/ LOOT7768aBCEJ0PQL班者号姓名班常缺曲语英魏逊历物化1)1)君场9950B7344&549323447T69968363<»取461re6G33323145770g575妙迪S935434S64S67525292E4L771j尚阳90359323閑684f的30北4DT729915600m99

4、】3600T引991360(:781g91360T32101002百让12464空969743qgIdiaoi254帥7?96蔚454750匸41010113114557勺刑9047淅4950T55L010024吗4477392100讯415047此表的作用是：1、原始成绩的输入；2、各班成绩单的打印。 成绩总表的制作：1、 求和。也就是计算每个学生的总分成绩，图例中是G列。选中G2单元格，然后点击求和按钮或者直接输入“=SUM (H2 : P2) ”按ENTER，正确后就拖拉填充至整个成绩表的最后一行即可。2、求班名次。新建一列(图中的 E列)，函数：=RANK($G768,$G$682:$

5、G$781) 函 数意思：G768单元格中的数值在 G682至G781中的排名。公式可以上下填充。 不同的班级 只须把区域范围变一下。如 10班改为“ $G$782:$G$881 ”后填充10班的该列区域。3、求在年级中的名次。 公式与求班名次一样。 不同的是总分范围： 从第2个单元格至最后一个单元格。可以手写输入也可以在公式编辑状态下用鼠标拖拉选中总分范围。图例中没有从第2个单元格起是因为我们在 12个班分两部分。4、插入班级一列，以便于按班级排序和按班级打印成绩单。5、插入学号一列，以便于输入学生成绩时参考。6、每个班固定一定的行数并且比人数最多的班再多几行，整十整百更好，以便于以后公式的

6、编辑和打印各班成绩单时的纸张设置。这里前六个班每班80行，后六个班每班100行。7、 此表制好后应输入或者从以往的成绩单中复制来一些名单、数据便于制作其他工作 表时参考也便于编写后面公式时可以看出效果。制作年级“前X名”的工作表1、 表格样式如图:ABC DEFG H I J K L1级名班姓名总分圾名班姓名总分级名班2110李晶晶W5i r 11叶春助47310012312陈天然50652? U吕瑞鹤472,810112 _4310魏頁让504.B5310张睿丽471. 410311t5411宋杨503.45411聂飞翔47L 41021265g韦广5022559方塑470.61051176

7、12養雪50256 t 9王倩君470,410312J8710徐场498.55610孟楠470. 41099要得到这样一张既有级名次、班级、姓名、总分项目，又能在每次打印时不用重新排版设计，一劳永逸，我们就要做一个动态连接的表，以不变应万变。请按以下步骤做：1、 新建一个工作表，我们把它取名叫“桥”。其意是在总表与前 X名之间建立一种连 接和过度。我们把总表中的“级名”、“班”、“姓名”、“总分”名列分别复制，再在“桥”中 粘贴连接，就形成下图中表的样式。2、 再新建一个前 X名的工作表。如上图。本例所示意为A4纸张，每张三栏，一栏50名学生。先设计好标题后，就可以从“桥”中选取前四列，如图例

8、选取前四列的前50人的区域复制。在此表中也粘贴连接于前50名预留处。依此类推，完成前 X名的连接工作。D 0 Bl聖亀妙1 D珈 TD2 T=二呂! RY62DEF陀铮超-er姓易铭亚而谢钱蔡审I 4 乳 11414. 5/im 匚3、我们要打印前150名、前300名等时，只须要把“桥”中的数据按总分递减排序即可。另外，注意每次修改完成绩，“桥”中都要重排序。图例中总分427.1来源于总成绩表的R762,意为：这个成绩就是总成绩表中的R列（总分列）第762个单元格的内容（成绩）。三、及格人数、及格率、平均分、优秀人数、优秀率表格的设计见下页图， （为了能看到更多的项目，图中的其他班级被隐藏）也

9、可以设计 成其他形式。 如果我们设计的列标与 “总” 表各科列标顺序一致， 在填充公式时会大大方便。 下面介绍的公式编写内容是在我们所需要显示内容的单元格里进行。“语文 60”制作方法：单元格中输入 60 数字后击鼠标右键进行设置。选择窗体中的自定义，再在右边框内选取“ G/通用格式”，然后在上面的小编辑框中编辑为“语文G/通用格式”确定之后我们在单元格内就看到 “语文 60”。任意改数值语文不变。其他各科方法同。同类型公式填充的方法与技巧 ：前面我们所提到相同公式的填充技巧，这里我介绍的 是同类型的公式填充的技巧。 如我们编辑每个班的及格人数时， 不同的班级公式中仅是区域 的不同， 所以我们

10、拖拉填充公式后再逐个的改动区域数字就行了； 不同科目的填充可以用鼠 标按住填充柄向左右填充即可得到其他各科的各种数据。此表中的“次”（既名次）这列是在所有公式填充完成后再逐个插入列编写（方法同班名次，只是范围小）。语文各项编写填充完成之后选中全部各项名次， 按住 ctrl 键用鼠标拖拉复制到其他各科目后的名次列既可得 到其他各科的各项目班级排名。1、 及格人数和优秀人数：公式的编辑为：=COUNTIF（总!H982:H1081,">="&C1）。COUNTIF 的函数意义是“计算满足条件的单元格的数目” ，其中“总 !”意是工作表名称 （后 面不再重述 ），“

11、H982:H1081 ”指的是一个区域范围（在示例中是三 （12）班语文）， ">="&C1 这是表达式的条件意思：大于等于 C1 单元格中的数值，这个条件是一个可以变化的值，同 样我们可以用来计算优秀人数，只要我们把 C1 中的 60改为 80既可。当把及格人数的各班 各科公式编好后复制并且改为优秀人数， 既得优秀人数栏。 注意双引号必须是非中文输入状 态。公式写完按 ENTER 按完成编辑。我们会看到在这个单元格里显示的数字就是某班的语 文学科及格人数。2、及格率和优秀率：公式的编写为：=C7/COUNTA（总!$H$982:$H$1081）。C7是三（1

12、2） 班的及格人数， COUNTA 的函数意是“计算非空单元格的数目” 。也就是计算某班语文学科 的参考人数。 公式写完按 ENTER 按完成编辑。 这时我们看到的只是一个小数而不是百分数。 仅需要在此单元格内单击右键选择单元格格式， 在弹出的对话框里选择数字标贴， 在选择框 中找到百分数选中，按确定即可显示百分数。优秀率参考及格率制作。3、平均分：公式的编写为：=AVERAGE（总!H982:H1081）。AVERAGE的函数意义是：“计算某数字单元格区域的算术平均值” 。示例中意为：计算“总”工作表中 H 列第 982个单元格至第 1081 个单元格的所有非空单元格数字的平均值。既为：三

13、（12）语文。V | rl» * Z f亠 u-j f |»i* rrIf丄UL=！*#-5.182： Hl(3Ac-1-EFGIHK-u班 u 项60 谄 英kA11s 6 7及格人o ( 三人58人3 oT8A8H人 goILAJr1 JI c'人98n人90b人38k人 g 0M人nHX172i0 二人7000人9人007E人6 ocw人9Ri人87nu12 31A 11 T1及格率J o 三%44.9Hr%22.9F2.r1 1» c 三-rLXJ-2 - 三HEHrL7 8 A t 1 .平均甘o1J- fv r一H42FHndll - c 三5

14、264,4nd21三J1-1 _x_7J0113四、求各班在年级总分前X名的学生人数1、表格样式如下图：公式编写为：=COUNTIF(总!$D$402:$D$501,"<=36")。COUNTIF这个函数前面我们已经熟悉。完整意为：计算在D列中第402个单元格至第501个单元格中数值小于等于36的单元格数目。“ 36”是年级前36名的意思。式中的“ $D$”指的“总” 工作表的D列他就是学生的年级名次这一列 )，其中的“ $D$”等同与“ D”。“$”的作用以 及单元格中出现的“人”字的方法将会在其它相关文章中介绍。I_ 计算各班前50名、前80名、前X名只需把公式中

15、的 36改为50、80、X即可。 自动更新前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名”

16、改为清除后再直接改为数字“ 36”，这 样就会看到我们所需要的数值。为了看到“前 36名”这样的效果，我们再回头把 B2单元 格进行设置，方法前面已经介绍过。 公式以为：=COUNTIF(总!$D$402:$D$501,"<= "&B2)之后，我们会看到这样的效果：当bjm- ”*_rrj- i rr _*«*-* _w丁 ' XEB1? 二 =8UNTI亍(总!$D$4D2ADDEF1三年级第一次月考各班各段儘秀人数情况n£班级前36龙前50芒前80名曲120启前200名5三5人8人13人21A41人6三(10)13人15人26人

17、36 ABOA7三)9人13人23人32Z>5。人把B2中输入数字“ 50”时所得到的结果与 C列前50名的结果一样。如果第一个单元格中 的公式都改为：=COUNTIF（总!$D$402:$D$501,"<= "&B2）样式，那么，我们想要前几名、前十几名、前几百名时仅需要在列标题行改动一下数字即可。五、分段、分班、分科平均分1lki r ml, rm 4= p -m4AT*" J> 口 rr JJ竺=-| =1、首先，我们把“各班优秀人数表”复制一份。把我们所需要的单元格进行拖拉重新 组合（为了保证公式所引用的区域不变必须是拖拉而非复制

18、）为我们所需要的形式如图:BCDGHIJIT1n 去5三年级身%-次月考分段比较情况班级菅孔念I语奂数历tt化三5S3E7. 694. 2fiL 64矢g39G一 (10)13臥3：W 779 7+ 62S644+ ?2、讥55/ 27二（5|協刘也T心+ 22B7. 3347, 9G57.73£-612)955. 7B91+ 22RG 674工565& 273Sg荊50立鮎至旳诵英貧貶历物化12三8人3人&2, 3351,R7. 678 3, 5746, 535 6. 23 7, 914三（15人2人£0, 5们% 5462北三L3人赴人83, 5S3.

19、2537. 5£2. 54656t S53715三（人吕人4£J&4. 6% £% 40 9213班级訂旳名駅至議悟典S咬厉樹ft1.9三K3, U82.480. 881.-4455人3620三(10)26A11人也647S. L991+ ：8n. 2?42. 18873 6t 72、A列B列就是原表的所有内容。 我们再建一列 C列为段中人数。相信这个数字的得 来大家都知道是同一个班的前50名单元格减去前36名单元格（均用单元格的位置编码如B12-B5这种形式）。3、 C列每个小标题：如：36至50的自动生成。编写：= B2&”至”&B9即

20、可；再向下 的小标题仅需要在引用单元格后面加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行

21、），第二个“ 0”就是从 本列开始向右第0列为起点（即I列），$B7代表区域的行数（即 9行），引用“ B7”的目的 就是为了适应每次成绩的变化，因为每次考试该段的人数一定是变化的，如果我们每次改公式中的数据，那么我们编写公式也就没有意义了。“T代表区域的列数为 1列（在此即为I 列）。前36名至前50名这个区间的公式稍有改动为：=AVERAGE（OFFSET（总!I402,$B7,0,$C14,1）由于“总”工作表我们是按照班级递增和总分递减的顺序排列的，所以前36名至前50名我们要计算的第二个区域。这个公式中的“$B7”的位置与上面的公式中的位置不同，所代表的意思也不同，在这意思是：从“总

22、”工作表 I402单元格向下第“ $B7”（即9）行为 起点。“ $C14”引用的是可变量（每次考试进入该段的人数是变化的），也就是这个区域包含的行数。六、有效上线人数有效上线人数的意义在于我们能通过各分数段学生的偏科情况。见下图:r LJq gq亠1 P斗髀P”* 一 一D5& =F :！If (5.1111=0,OOUNriF(OFFJ£T(总H阴2,心 Q壮:&1人祚=勺加)ACD询也芒1FGHI丨JN亠.1三年级第次月考各名次段上綫人数2H7442 英90.ES&H1.518t39K物45化历邛Ea三g1ft 1 fiR1GR7E-(W):19911f

23、t9997-N-：79106910 10三仃刃141213ll10149974 42英 90.6*45475HL2二20人12 Aid&g£4MT134斗6627514-a：22A12 AI1089E9n15三仃2)20人6人463I544tft74.42英90怎粉4S化也历删JI二28AB人425S224a JL11氏A公式：=IF($B5=0,"",IF(总!H1=0,"",COUNTIF(OFFSET(总!H682,0,0,$B5,1),">="&D2)公式中 的表达式意义：$B5=0,即B5单元

24、格数字为“ 0”时此单元显示空白，否则，按下一层函数 执行。总!H1=0，意义是“ H1是语文的标题，如果语文这一科没有考试就把：“总”工作表的语文标题清除，相应的是D3这个单元格中也会显示空白(而不显示错误符号)，否则，按下一函数执行即：COUNTIF(OFFSET(总!H682,0,0,$B5,1),">="&D2)。这才是真正重要的内容。“OFFSET(总!H682,0,0,$B5,1) ”前面已经提到；而“ ">="&D2 ”这个条件却是需要介绍 的，意义是：大于和等于D2单元数值。(而D2单元格又嵌套着一个可变函数值

25、，下面要说到)。下面就把整个函数式的意思解释：“如果B5=0 ,贝U D3显示空白；如果H1 = 0,贝U 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)这个公式解释起来比较简单：求年级前

26、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是一个可变值，因为我们会根据需要

27、选择参考前多少名的平均成绩为参照。C2的值一变，上面我们介绍的内容也就跟着变。另外，在这里介绍一下“ $”作用。“ $ ”只有在进行填充公式时才用得着。有些公式我 们希望随着单元格的变化而进行变化，而更多时候我们需要引用的单元格只向行的方向变化或者只向列的方向变化，这时就需要“ $”来帮忙了。上例中的公式我们需要填充到五段中，十二个班，七个科目的单元格中(12X 5X 7= 420个单元格中)。如果我们希望向左右列中的同一行填充公式而所引用的单元格不变，那么我们只须代表此单元格的列标字母前加上“$”即可。就向上面公式里的“$B7”，在该班级该段中各科引用的都是“ B7”这个单元格，我们当然希望

28、从语文这里向右填充时B7不变化就。如果不加“ $”，就会随着填充而变为C7、D7、。有人知道有个“绝对引用”是字母前后都加“$”，但是，不要忘了我们的公式还需要向下填充；而向下填充时我们又希望B7会变成B8、B9、，如果字母后面加了“$”，那向下填充时就不会变了，我们还得一个一个的修改。最后，我想大家这样理解 和记忆是有效的：“$”就是“绝对引用”的意思。而且，我们又知道“B7”中的B就是B列，“ 7”既第7行。例如：第一种情况：绝对引用某一单元格，如：“ $B$7”读作：“绝对引用B列又绝对引用7行”，这样一来不管往个方向填充公式B7的引用不变。第二种情况(绝对引用列)：“ $B7”读作：“

29、绝对引用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都为空，那么查询也为空，否则按第二判断；如果

30、 A3为空，则 按B3内容在“总”表中查找 $D$2:P$1081区域首列中与 B3相同的单元格，然后返回这一 行的第13中的数据。“FALSE”的意思为精确查找。这样，我们只要把公式左右填充之后修改其中的返回列数字即可得到该学生的各科成 绩。这时我们只要再选中这一行的各科公式单元格向下填充至平时所能用的人数为止。一般情况下填充30行足够。严 广幷-场($也4釘B4二"：刖二匕 VLODKUF($B£ 意！侗气 " B总14,FALSE)第软月考个人成绩单2学号r姓名'级畧次性别总分i吾英数历物oFl阎璐F甜2010as94劣41624赵云！17126604659486105523S475闰娇562080血98105825938556闫寒110101113120讯48707游俐37147n4船S310296654&572、个人多次考试成绩的查询比较； Mt>! IT YLOOKUP J1" !3,