用EXCEL做一个学生成绩管理系统
用 EXCEL 做一个学生成绩管理系统
Excel 的数据处理功能的强大鲜为人知，许多老师在教育教学的辅助应用上对 EXCEL 的应用只局限在 Excel 的简单功能应用上，对于 Excel 的函数功能却始终停留在求和、求平 均值等简单的函数应用上。
其实， Excel 函数作为处理数据的一个最重要手段，功能是十分强大的，许多知名企业 在数据管理上还喜欢应用 Excel 。本人经过一年多的学习研究， 在学生成绩管理上应用 Excel 函数可以把它做成你可能想不到的复杂的统计管理表格或者小型的数据库系统。
另外，应用 Excel 函数功能制作出的表格具有一次设计好后可重复使用， 只需要改变原 始数据，就可以得到相应的结果，并且不用重复进行打印设置。
本人所做的这个成绩管理系列表格。 虽然对于使用函数的高手来说， 这些函数可能很熟 悉，但是， 各种统计技巧的应用可以为参考。 我写此文章的目的不是为了让教师去研究函数 的应用， 仅为了便于各个水平段的老师参考应用在自己的工作中去， 实现快捷化的成绩统计 的自动生成。下面就依照整个系统的制作过程进行描述，便于同志们同步制作与使用。
选择区域技巧： ①在填充公式或者数值时， 我们常常会因为自动填充速度快拖拉位置难 以确定而烦恼。 那么我们只要选中要复制填充其内容的这个单元格， 然后用鼠标流动或者点 击滑条翻页找到要填充至此的那个单元格，先按住 shift 键再点击此单元格，我们会看到整 个要填充的区域被选中。②按 shift 键加上下光标键，可以自由选取区域大小，直到满意为 止，再松开 shift 键。
填充技巧：在填充区域被选中的情况下我们按 ctrl 键加 enter 键，整个区域就自动填充 该数值或者该公式。
一、 总成绩表的制作
总成绩表是所有数据的源。如图：
成绩总表的制作：
1、 求和。也就是计算每个学生的总分成绩，图例中是
G 列。选中 G2 单元格，然后点
击求和按钮或者直接输入“＝ SUM （H2：P2）”按 ENTER ，正确后就拖拉填充至整个成绩 表的最后一行
即可。
2、 求班名次。新建一列（图中的 E 列），函数： =RANK （$G768,$G$682:$G$781） 函 数意思： G768 单元格中的数值在 G682 至 G781 中的排名。 公式可以上下填充。 不同的班级 只须把区域范围变一
下。如 10 班改为“ $G$782:$G$881”后填充 10 班的该列区域。
1、原始成绩的输入；
2、各班成绩单的打印。
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 列)。
前36 名至前50 名这个区间的公式稍有改动为：
＝AVERAGE(OFFSET( 总!I402,$B7,0,$C14,1))
由于“总”工作表我们是按照班级递增和总分递减的顺序排列的，所以前36 名至前50 名我们要计算的第二个区域。这个公式中的“$B7 ”的位置与上面的公式中的位置不同，所
代表的意思也不同，在这意思是：从“总”工作表I402 单元格向下第“ $B7 ”(即9)行为起点。“ $C14”引用的是可变量(每次考试进入该段的人数是变化的) ，也就是这个区域包
含的行数。
六、有效上线人数
有效上线人数的意义在于我们能通过各分数段学生的偏科情况。见下图：
公式：=IF($B5=0,"",IF( 总!H1=0,"",COUNTIF(OFFSET( 总!H682,0,0,$B5,1),">="&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 显示空白; 如果H1 ＝0，则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 的值一变，上面我们介绍的内容也就跟着变。
另外，在这里介绍一下“ $”作用。“ $”只有在进行填充公式时才用得着。有些公式我们希望随着单元格的变化而进行变化，而更多时候我们需要引用的单元格只向行的方向变化或者只向列的方向变化，这时就需要“ $”来帮忙了。上例中的公式我们需要填充到五段中，十二个班，七个科目的单元格中( 12×5×7＝420 个单元格中) 。如果我们希望向左右列中的同一行填充公式而所引用的单元格不变，那么我们只须代表此单元格的列标字母前加上
“$”即可。就向上面公式里的“$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($A$3="",VLOOKUP($B$3,'1'!$D$2:$Q$1081,3,FALSE),VLOOKUP( $A$3,'1'!$C $2:$Q$1081,4,FALSE)))
“ $A$3&$B$3="","", ”这部分作用是保证在没有查询内容时，公式单元格显示空白。
“IF($A$3="",VLOOKUP($B$3,'1'!$D$2:$Q$1081,3,FALSE),VLOOKUP($A$3,'1'!$C$2:$Q$10
81,4,FALSE))) ”这是主公式，意思为：A3 中如果没有学号就查询B3 中的姓名，在表“ '1'! ” 中区域
$D$2:$Q$1081 的首列与B3 完全相同的单元格，再返回该行在该区域的第3 列，列数是可变量；否则还按A3 中的考号查询??
以考号为主的查询好处是没有重复的可能。而名字可能有相同的。
我们注意到公式中的“ '1'! ”这是我们把第一次考试成绩放在同一文件中作为一个工作表出现，并且把该工作表命名为“ 1”。依次类推，第二次考试成绩表我们把它起名为“2”
等。这样我们把同样的公式复制到或者填充到以下一二十行中。我们在表明哪次考试成绩的这一行公式中的表引用替换成我们要引用的工作表名称即可。由于是用数字命名，替换时就在数字前后的单引号带上一并替换，否则，将会把公式中的其它“ 1”也替换掉，从而造成公式错误。
只要输入学号应能把所有信息查询出来的公式留给大家练习吧！