利用Excel制作功能完备的学生成绩统计通用模板

1、利用 Excel 制作功能完备的学生成绩统计通用模板 以下是我二年前在初中部和高中部上信息课时做的一些尝试，现在看来很不成 熟，但毕竟我当时还是很用心的在做这件事。希望能激励本人继续努力，也希 望能得到大家的批评指正！众所周知， Excel 是目前功能最强大的电子表格软件之一。 它的表格生成功 能及数据处理能力一直是受人们称道的。作为老师，在每次考试后，最麻烦的 事莫过于统计和分析学生成绩了。利用 Excel 的强大的函数功能和各种自动生 成功能制作一个学生成绩统计通用模板，可以很好的解决老师们这个烦恼。 一 个功能较完备的学生成绩统计模板至少应具备以下功能：1、可统计学生个人总分、平均分；2

2、、可统计学生个人总分排名、平均分等级；3、可统计本班各科总分、平均分；4、可统计各科应考人数、实考人数；5、可统计各科优秀人数、不及格人数、优秀率、及格率；6、可统计各科最高分、最低分；7、能方便地查看各统计结果；8、能有效地防止无效数据的产生，适合多种情况的成绩统计。 针对以上需求，我们可以利用 Excel 的相关功能逐个解决：一、建立主体框架这一步要完成下面几件工作：1、确立表头，如“潮阳实验学校初二（ 1）班学生成绩统计表”。表头位 于整个表的最上端，跨越 19 列，利用合并居中功能，使之居中并设置好字体字 号等。2、确立列标题。在第 2 行前两列输入学号和姓名，从第三列开始，添加语 文

3、、数学、英语、政治、历史、地理、物理、化学、生物、音乐、体育、美术、 信息等科目的列标题，在科目列标题后添加总分、平均分、等级、名次四列。 这样一来，整个统计表一共有19列，13个科目所在的列从C列到0列。学生具 体信息及成绩的输入从第三行开始。如果实际应用时用不着一些科目，可以选 中相应列，将其隐藏，需要时再取消隐藏即可。如果是应用于单科多次成绩统 计，只要更改列标题即可。3、 输入学生学号及姓名。输入学生学号时可以应用Excel 的自动填充功4、在学号姓名下确立几项行标题。包括各科总分、平均分、最高分、最低 分、优秀人数、不及格人数、优秀率、及格率、应考人数、实考人数等常用统 计项目。假设

4、班上有 40名学生，这里确立的行标题应从第 43行开始。二、确立表格格式 , 作好第一步后，就确定好各行的行高和各列的列宽，行 高和列宽要适中；再给表格（不包括表头）加上边框；给各列标题和行标题单 元格加上底纹，字体加粗，以区别于其它项目。这一步完成后，这个成绩表在 外观、形式上已基本确立了。三、确立各单元格的数据格式 , 这一步很重要，关系到数据输入后的最终效 果。这里的数据指的是对单元格内容。而单元格内的数据格式一般包括常规、 数值、文本、日期时间、百分比等常用类别。设置方法为，选中单元格后点击 鼠标右键，选择单元格格式，再选择数字项，即可在出现的选项框内完成对单 元格内数据格式的设置。另

5、一种方法是：在选中单元格后，选择格式菜单中的 单元格选项，同样可以实现数据格式的设置。 Excel 中几种常用数据格式的区 别如下：常规格式：不包含任何特定的数字格式，这是单元格的默认设置。这种设置对数字内容有如下特点：1、忽略数字最高位的“ 0”，如“ 0123”将变成“ 123”；2、对于“ 95.00 ”这种形式，会自动去除小数点及后面的“ 0”；3、对于“ 85.66666 ”这种数字形式，会根据单元格的宽度自动“四舍五 入”。如：当单元格宽只能容下五个字符时，内容将变成“85.67 ”。4、这种格式小数点后默认最多允许八位。数值格式：用于一般数字的表示。这种格式是专门针对数字的，它也

6、会忽略最高位的“ 0”，如：“ 0123”将 变成“ 123”。这种格式还可以设置固定的小数位数，还可以设置负数格式及是 否使用千位分隔符。百分比格式：将单元格中的数值乘以100，并以百分数形式表示。如单元格中原来的数值是“ 0.56 ”，将单元格数值格式设置成百分比格 式后，原来的内容即变成了“ 56%”。文本格式：在文本格式中，单元格内的数 字将作为文本来处理。也就是说，在文本格式中，输入任何内容都不会被自动 改变，将始终保持原样。 这样，就没有最高位不能为“ 0”及小数位数的限制了。 特殊格式中的邮政编码格式：单元格中输入任何内容均保持原样，不加任何改 变。自定义格式：可以根据需要定义不

7、同的格式。如自定义为“000000”形式的格式时， Excel 会自动判断单元格内的内容是否足 6位，如果不足，则在首位 前用“ 0”补足。本例中的单元格的数据格式设置具体如下：学号列（ A 列）的数据格式设置 为“邮政编码”，或根据学生学号的形式自定义。如我校学生的统编学号形式 为“ 030101”，自定义格式即可设为“ 000000”，这样就可以保证首位的“ 0” 不被 Excel 忽略。姓名列（ B 列）的数据格式设置可保持为常规格式不变。语文 列（C列）至总分列（P列）的数据格式也可保持为常规格式不变，但列宽需保 证足够宽，以防分数有小数时出现自动四舍五入的现象。平均分列（Q列）的数据

8、格式应设置成数值格式，并将小数位数设为两位。最后两列的数据格式保持 为常规格式不变。各科目总分、平均分所在单元格的数据格式应设置成数值格 式，并将小数位数设置成两位。最后再将优秀率、及格率所在单元格的数据格 式设置成百分比格式，按需求设置其小数位数。四、利用函数完善功能 , 这一步是制作成绩统计模板过程中最重要的。如果 没有这一步，这个“模板”顶多算个“表格”，没有什么“智能”统计功能， 起不到提高工作效率的作用。在正式使用函数前，让我们先来认识一下 Excel 中的函数。 一般来说， 在 Excel 中函数指的是一些预定义的公式，它们使用一些称为参数的特定数值 按特定的顺序或结构进行计算。例

9、如，SUM函数对单元格或单元格区域进行加法运算， TEXT 函数将一数值转换为按指定数字格式表示的文本。参数可以是数 字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元 格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函 数。函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆 括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。如：常用的SUM函数使用时，常有这样的形式：SUM（A1,A3:A8,A10 ）。在这里，括号 内的参数根据不同的函数要求会有些不同，有些函数不需要参数。如果有多个 参数，参数之间必需用英

10、文逗号隔开。 在引用 Excel 中的函数一般有两种方法。 如果对该函数的参数要求很熟悉，就可以在 Excel 的公式栏中直接输入；如果 对该函数的具体参数要求不是很清楚，还可以调出“插入函数”对话框进行选 择。在 “插入函数”对话框中选中某个函数时，对话框底部还会出现该函数的简单说明。而当确定选取一个函数后，还会出现与该函数参数要求对应的对话 框，用来确定各个参数。 Excel 中共有九大类几百个函数，每个函数都有其特殊 功能。如此众多的函数要完全记住是不太现实的事，况且很多函数并不常用。 我们只要知道 Excel 中有哪些功能能用函数实现，要用到时再到相应类别中去 选择相应的函数就行了。要

11、制作功能完备的成绩统计表至少需要以下几个函数： COUN（T ）：返回包含数字以及包含参数列表中的数字的单元格的个数。利用函 数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。COUNTA（）:返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。COUNTI（F ）：计算区域中满足给定条件的单元格的个数。AVERAGE）：返回参数的平均值（算术平均值）。SU（）: 返回某一单元格区域中所有数字之和。RANK（）:返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小（如果数据清单已经 排过序了，则数值的排位

12、就是它当前的位置）。MAX（）:返回一组值中的最大值。MIN（）:返回一组值中的最小值。IF（）：执行真假值判断，根据逻辑计 算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。（这里只列出函数的简单说明，如果想更详细了解函数的语法及参数说明请参 考 Excel 自带的帮助文件）将以上列出的 8 个函数合理利用就可以做出功能完 备的成绩统计模板了。具体使用方法如下:1、先确定第一位学生的各项统计公式。（1） 选中 列标题“总分” 下的 单元 格 P3， 在公式编辑栏中 输入“ =SUM（C3:O3”，再回车确认或点击左边的“V”号确认输入。经过设置，单 元格P3的内容就等于单元

13、格C3到03内容中所有数字的总和，起到了统计总分 的作用。（2用同样的方法，选中列标题“平均分”下的单元格Q3在公式编辑栏中输入公式“ =AVERAGEC3:O3）”，使之具备自动求平均分的功能。（3）确定学生成绩的等级一般有“优”、“良”、“及格”、“待及格” 四等。假设四个等级的分数线分别为 85 分、75 分、60 分，在第一位学生的等 级栏（单元格 R3）内的公式可以是“ =IF（Q3>=75,IF（Q3>=85,"优秀","良好 "）,IF（Q3>=60," 及 格 "," 不 及 格 "

14、;） ” ， 也 可 以 是 “=IF（Q3>=85," 优 秀 ",IF（Q3>=75,"良好",IF（Q3>=60,"及格","不及格"）”。这两个公式都是利用 IF 函数判断平均分单元格内的数值，从而显示不同的内容。（4） RANK函数可以返回一个数值在一组数值中的排位。我们利用RANK函 数来实现学生成绩自动排名。RANK函数需要两个参数，第一个参数指要排位的 内容，第二个参数指定一个数据清单。在成绩表中，要排位的内容应该是总分，即单元格P3中的内容，而数据清单自然就是所有学生的总分了。假

15、设有40名学生，这个范围应该是“ P3: P42'。这个范围对于所有学生的名次统计都是一 样的，为了使它不因位置变化而变化，我们应该利用 Excel 的绝对引用功能， 在单元格名称前加上“ $”符号。第二个参数即变成了“ $P$3: $P$42'。单元 格 S3 中的公式也就是“=RANK（ P3, $P$3: $P$42）”。2、确定所有学生的各统计公式。确定好上面四个单元格的公式之后，选中单元格P3至S3o将鼠标移到选中区域的右下角的小黑点上，当鼠标变成十字形时，按下鼠标向下拖动， 一直拖到最后一位学生所在行 （在本例中为第 42 行）。 经过上面的操作，所有学生的总分、平

16、均分、等级、名次的公式已自动填充完毕。现在，只要学生成绩一确定，每位学生的总分、平均分、等级、名次也就自动计算出来了3、确定其它统计项目的公式。剩下的统计项目都是针对全班学生的了。这 些项目包括：各科总分、平均分、最高分、最低分、优秀人数、不及格人数、 优秀率、及格率、应考人数、实考人数等。在这一步骤中，我们也应该先确定 第一个科目的各项统计公式，如语文科。单科总分、平均分的统计公式与个人 总分平均分的统计公式相似，只是范围有所不同。如语文科的总分、平均分统 计公式分别为“=SUM(C3: C42”和“=AVERAGEC3: C42)”。单科最高 分、最低分的统计公式分别为MAX(C3: C4

17、2)”和“=MIN (C3: C42)”。 优秀人数和不及格人数的统计公式中用到了 COUNTIF函数。公式分别为“ =COUNTIF(C3:C42,">=80")” 和“ =COUNTIF(C3:C42,"<60")”。应考人数的统 计公式中用到了 COUNT函数。由于可能有缺考的学生，所以统计应考人数时不 能以科目成绩列的内容作参数，而应以学号列或姓名列的内容作参数。考虑到 这个参数是固定的，也应该用绝对引用功能，即“=COUNTA$A$3: $A$42)”或“=COUNTA$B$3: $B$42) ”。实考人数的统计公式为“= COU(

18、C3: C42) ”。 有了优秀人数、不及格人数、实考人数，统计优秀率和及格率就容易了。假设 语文科优秀人数、 不及格人数和实考人数统计公式所在的单元格分别为C47、 C48和C52,优秀率和及格率的统计公式就分别为“=C47/C52'和“=(C52- C48)/C52”。设置好第一个科目的各项统计公式后，其它科目的相关统计公式只要 利用 Excel 的自动填充功能，用鼠标拖放就能轻松实现了。至此，这个学生成 绩统计模板的主体设计已基本完成，剩下的只是些细微的补充了。五、进一步完善成绩统计模板 , 这一步的目的是为了更方便的使用成绩统计 模板。1、设置数据有效性验证为了防止输入成绩时出

19、现无效数据(如：误输入字 母或负数等)造成统计公式出错，我们为成绩输入区域的单元格设置数据有效 性验证。设置方法如下：选中成绩输入区域的所有单元格，点击“数据”菜单 中的“有效性”，调出“数据有效性”设置面板。在出现的设置面板中有四个 选项卡，分别用来设置“有效性条件”、“选定单元格时的显示信息”、“输 入无效数据时的出错警告”及“输入法时否关闭”。在“设置”选项卡中，设 置条件为介于 0至 100之间的小数。 在“出错警告”选项卡中， 样式设置成“中 止”，标题设置成“输入内容有错！”，内容设置成“请输入介于 0 到 100 的 数字！”。输入法模式设置为“关闭”。经过上面的设置，就不怕输入

20、成绩时 出现无效数据了！2、设置单元格的条件显示成绩统计出来后，老师们往往想一眼看出哪些学 生获得优秀，哪些学生不及格，哪些学生是单科最高分，哪些学生是单科最低 分。如果能根据条件的不同而使单元格纹底纹显示不同颜色，就能起到一目了 然的效果。要实现这些功能必须用到 Excel 中的条件格式功能。选中第一位学 生的第一科成绩所在单元格，点击“格式”菜单中的“条件格式”，调出“条 件格式”设置面板。一个单元格可以同时设置三个条件来控制其格式，如果三 个条件有重叠交叉现象，则优先满足排在前面的条件。设置条件时有两种方式， 一种是直接针对“单元格数值”，另一种方式则是直接编辑公式。本例中使用 的是第一

21、种方式。我们为单元格设置两个条件。第一个条件：当“单元格数值” “等于”“二C$45'时，单元格底纹为绿色；第二处条件：当“单元格数 值” “等于”“二C$46'时，单元格底纹为红色。这里“ C45'和“ C46'分别 提的是第一科最高分和最低分的单元格名称。而在行号前加上“$”是为了在后 面的选择性粘贴中起到行绝对定位的目的。选中设置好条件格式的单元格，点 击鼠标右键，选择“复制” ; 再选中成绩输入区域的所有单元格，点击右键，选 择“选择性粘贴” ; 在出现的对话框中，选择粘贴“格式”，再点击“确定”。 就完成了成绩输入区域所有单元格条件格式的设置。如果要用

22、颜色区分等级， 设置条件格式的方法与上面的方法差不多，这里就不再展开说明了。经过设置 后，当单元格中的内容等于本科目最高分时，单元格的底纹颜色将自动变成绿 色，而当单元格中的内容等于本科目最低分时，单元格的底纹颜色将自动变成 红色；等级不同的学生相应的单元格也会有不同的格式。老师在分析学生成绩 时就更方便了！3 、设置窗口冻结一个班的学生数一般都有 40 人左右，加上表头及各统计 项目，整个统计表所占行列都比较多。在本例中，整个统计表共有 52行 19 列， 在电脑屏幕中一页根本显示不完全。在这样的表格中输入和查看统计内容时往 往会出现数据内容与行列标题对应不上的现象，查看起来很不方便。为了进

23、一 步便于使用，可以使用 Excel 的窗口冻结功能来帮忙。先选中第一位学生的第 一科成绩所在的单元格（C3）,再选择窗口菜单中的“冻结窗格”命令。表格 中将出现一横一竖两条黑线，将表格的列标题与数据内容、学号姓名与数据内 容分隔开来。经过“冻结窗格”的表格的被冻结部分在查看数据时能固定在原 处,便于数据的查看。如果只想冻结行或列,也可以只选中某行或某行再执行 “冻结窗格”命令。如果想取消“窗口冻结”,可选择窗口菜单中的“取消窗 口冻结”命令。经过合理“冻结”的窗口,对于数据量大的表格来说,大大的 增加了使用的方便性,而且并不影响打印。4、其它补充设置 1）考虑到有些班级学生数较多,一页内打印不完整,为 了打印后的查看方便,可以设置一个“打印标题行”,使打印出来后每一页都 有表头和列标题。具体做法是：选择文件菜单中的页面设置,在出现的“页面 设置”对话框中选择“工作表”选项,再在“打印标题”中的“顶端标题行”