百毒不侵 桑尤 小说:利用Excel制作功能完备的学生成绩统计通用模板

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

2010-01-07 16:56:10|  分类： 电子表格 |  标签： |字号大中小 订阅

以下是我二年前在初中部和高中部上信息课时做的一些尝试，现在看来很不成熟，但毕竟我当时还是很用心的在做这件事。希望能激励本人继续努力，也希望能得到大家的批评指正！        众所周知，Excel是目前功能最强大的电子表格软件之一。它的表格生成功能及数据处理能力一直是受人们称道的。作为老师，在每次考试后，最麻烦的事莫过于统计和分析学生成绩了。利用Excel的强大的函数功能和各种自动生成功能制作一个学生成绩统计通用模板，可以很好的解决老师们这个烦恼。 一个功能较完备的学生成绩统计模板至少应具备以下功能：1、可统计学生个人总分、平均分；2、可统计学生个人总分排名、平均分等级；3、可统计本班各科总分、平均分；4、可统计各科应考人数、实考人数；5、可统计各科优秀人数、不及格人数、优秀率、及格率；6、可统计各科最高分、最低分；7、能方便地查看各统计结果；8、能有效地防止无效数据的产生，适合多种情况的成绩统计。 针对以上需求，我们可以利用Excel的相关功能逐个解决：一、建立主体框架这一步要完成下面几件工作：1、    确立表头，如“潮阳实验学校初二（1）班学生成绩统计表”。表头位于整个表的最上端，跨越19列，利用合并居中功能，使之居中并设置好字体字号等。2、    确立列标题。在第2行前两列输入学号和姓名，从第三列开始，添加语文、数学、英语、政治、历史、地理、物理、化学、生物、音乐、体育、美术、信息等科目的列标题，在科目列标题后添加总分、平均分、等级、名次四列。这样一来，整个统计表一共有19列，13个科目所在的列从C列到O列。学生具体信息及成绩的输入从第三行开始。如果实际应用时用不着一些科目，可以选中相应列，将其隐藏，需要时再取消隐藏即可。如果是应用于单科多次成绩统计，只要更改列标题即可。3、    输入学生学号及姓名。输入学生学号时可以应用Excel的自动填充功能。4、    在学号姓名下确立几项行标题。包括各科总分、平均分、最高分、最低分、优秀人数、不及格人数、优秀率、及格率、应考人数、实考人数等常用统计项目。假设班上有40名学生，这里确立的行标题应从第43行开始。 二、确立表格格式       作好第一步后，就确定好各行的行高和各列的列宽，行高和列宽要适中；再给表格（不包括表头）加上边框；给各列标题和行标题单元格加上底纹，字体加粗，以区别于其它项目。这一步完成后，这个成绩表在外观、形式上已基本确立了。 三、确立各单元格的数据格式       这一步很重要，关系到数据输入后的最终效果。这里的数据指的是对单元格内容。而单元格内的数据格式一般包括常规、数值、文本、日期时间、百分比等常用类别。设置方法为，选中单元格后点击鼠标右键，选择单元格格式，再选择数字项，即可在出现的选项框内完成对单元格内数据格式的设置。另一种方法是：在选中单元格后，选择格式菜单中的单元格选项，同样可以实现数据格式的设置。 Excel中几种常用数据格式的区别如下：常规格式：不包含任何特定的数字格式，这是单元格的默认设置。这种设置对数字内容有如下特点：1、忽略数字最高位的“0”，如“0123”将变成“123”；2、对于“95.00”这种形式，会自动去除小数点及后面的“0”；3、对于“85.66666”这种数字形式，会根据单元格的宽度自动“四舍五入”。如：当单元格宽只能容下五个字符时，内容将变成“85.67”。4、这种格式小数点后默认最多允许八位。 数值格式：用于一般数字的表示。这种格式是专门针对数字的，它也会忽略最高位的“0”，如：“0123”将变成“123”。这种格式还可以设置固定的小数位数，还可以设置负数格式及是否使用千位分隔符。百分比格式：将单元格中的数值乘以100，并以百分数形式表示。如单元格中原来的数值是“0.56”，将单元格数值格式设置成百分比格式后，原来的内容即变成了“56%”。文本格式：在文本格式中，单元格内的数字将作为文本来处理。也就是说，在文本格式中，输入任何内容都不会被自动改变，将始终保持原样。这样，就没有最高位不能为“0”及小数位数的限制了。特殊格式中的邮政编码格式：单元格中输入任何内容均保持原样，不加任何改变。自定义格式：可以根据需要定义不同的格式。如自定义为“000000”形式的格式时，Excel会自动判断单元格内的内容是否足6位，如果不足，则在首位前用“0”补足。             本例中的单元格的数据格式设置具体如下：学号列（A列）的数据格式设置为“邮政编码”，或根据学生学号的形式自定义。如我校学生的统编学号形式为“030101”，自定义格式即可设为“000000”，这样就可以保证首位的“0”不被Excel忽略。姓名列（B列）的数据格式设置可保持为常规格式不变。语文列（C列）至总分列（P列）的数据格式也可保持为常规格式不变，但列宽需保证足够宽，以防分数有小数时出现自动四舍五入的现象。平均分列（Q列）的数据格式应设置成数值格式，并将小数位数设为两位。最后两列的数据格式保持为常规格式不变。各科目总分、平均分所在单元格的数据格式应设置成数值格式，并将小数位数设置成两位。最后再将优秀率、及格率所在单元格的数据格式设置成百分比格式，按需求设置其小数位数。 四、利用函数完善功能       这一步是制作成绩统计模板过程中最重要的。如果没有这一步，这个“模板”顶多算个“表格”，没有什么“智能”统计功能，起不到提高工作效率的作用。       在正式使用函数前，让我们先来认识一下Excel中的函数。 一般来说，在Excel中函数指的是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。例如，SUM 函数对单元格或单元格区域进行加法运算，TEXT 函数将一数值转换为按指定数字格式表示的文本。参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。如：常用的SUM函数使用时，常有这样的形式：SUM（A1,A3:A8,A10）。在这里，括号内的参数根据不同的函数要求会有些不同，有些函数不需要参数。如果有多个参数，参数之间必需用英文逗号隔开。 在引用Excel中的函数一般有两种方法。如果对该函数的参数要求很熟悉，就可以在Excel的公式栏中直接输入；如果对该函数的具体参数要求不是很清楚，还可以调出“插入函数”对话框进行选择。在 “插入函数”对话框中选中某个函数时，对话框底部还会出现该函数的简单说明。而当确定选取一个函数后，还会出现与该函数参数要求对应的对话框，用来确定各个参数。Excel中共有九大类几百个函数，每个函数都有其特殊功能。如此众多的函数要完全记住是不太现实的事，况且很多函数并不常用。我们只要知道Excel中有哪些功能能用函数实现，要用到时再到相应类别中去选择相应的函数就行了。要制作功能完备的成绩统计表至少需要以下几个函数：COUNT（）：返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。COUNTA（）：返回参数列表中非空值的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数。COUNTIF（）：计算区域中满足给定条件的单元格的个数。AVERAGE（）：返回参数的平均值（算术平均值）。SUM（）：返回某一单元格区域中所有数字之和。RANK（）：返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小（如果数据清单已经排过序了，则数值的排位就是它当前的位置）。MAX（）：返回一组值中的最大值。MIN（）：返回一组值中的最小值。IF（）：执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。（这里只列出函数的简单说明，如果想更详细了解函数的语法及参数说明请参考Excel自带的帮助文件）将以上列出的8个函数合理利用就可以做出功能完备的成绩统计模板了。具体使用方法如下： 1、  先确定第一位学生的各项统计公式。（1）    选中列标题“总分”下的单元格P3，在公式编辑栏中输入“=SUM(C3:O3)”，再回车确认或点击左边的“√”号确认输入。经过设置，单元格P3的内容就等于单元格C3到O3内容中所有数字的总和，起到了统计总分的作用。（2）    用同样的方法，选中列标题“平均分”下的单元格Q3，在公式编辑栏中输入公式“=AVERAGE（C3:O3）”，使之具备自动求平均分的功能。（3）    确定学生成绩的等级一般有“优”、“良”、“及格”、“待及格”四等。假设四个等级的分数线分别为85分、75分、60分，在第一位学生的等级栏（单元格R3）内的公式可以是“=IF(Q3>=75,IF(Q3>=85,"优秀","良好"),IF(Q3>=60,"及格","不及格"))”，也可以是“=IF(Q3>=85,"优秀",IF(Q3>=75,"良好",IF(Q3>=60,"及格","不及格")))”。这两个公式都是利用IF函数判断平均分单元格内的数值，从而显示不同的内容。（4）    RANK函数可以返回一个数值在一组数值中的排位。我们利用RANK函数来实现学生成绩自动排名。RANK函数需要两个参数，第一个参数指要排位的内容，第二个参数指定一个数据清单。在成绩表中，要排位的内容应该是总分，即单元格P3中的内容，而数据清单自然就是所有学生的总分了。假设有40名学生，这个范围应该是“P3：P42”。这个范围对于所有学生的名次统计都是一样的，为了使它不因位置变化而变化，我们应该利用Excel的绝对引用功能，在单元格名称前加上“$”符号。第二个参数即变成了“$P$3：$P$42”。单元格S3中的公式也就是“＝RANK（P3，$P$3：$P$42）”。2、  确定所有学生的各统计公式。确定好上面四个单元格的公式之后，选中单元格P3至S3。将鼠标移到选中区域的右下角的小黑点上，当鼠标变成十字形时，按下鼠标向下拖动，一直拖到最后一位学生所在行（在本例中为第42行）。经过上面的操作，所有学生的总分、平均分、等级、名次的公式已自动填充完毕。现在，只要学生成绩一确定，每位学生的总分、平均分、等级、名次也就自动计算出来了。 3、  确定其它统计项目的公式。剩下的统计项目都是针对全班学生的了。这些项目包括：各科总分、平均分、最高分、最低分、优秀人数、不及格人数、优秀率、及格率、应考人数、实考人数等。在这一步骤中，我们也应该先确定第一个科目的各项统计公式，如语文科。单科总分、平均分的统计公式与个人总分平均分的统计公式相似，只是范围有所不同。如语文科的总分、平均分统计公式分别为“＝SUM（C3：C42）”和“＝AVERAGE（C3：C42）”。单科最高分、最低分的统计公式分别为“＝MAX（C3：C42）”和“＝MIN（C3：C42）”。优秀人数和不及格人数的统计公式中用到了COUNTIF函数。公式分别为“=COUNTIF(C3:C42,">=80")”和“=COUNTIF(C3:C42,"<60")”。应考人数的统计公式中用到了COUNTA函数。由于可能有缺考的学生，所以统计应考人数时不能以科目成绩列的内容作参数，而应以学号列或姓名列的内容作参数。考虑到这个参数是固定的，也应该用绝对引用功能，即“＝COUNTA（$A$3：$A$42）”或“＝COUNTA（$B$3：$B$42）”。实考人数的统计公式为“＝COUNT（C3：C42）”。有了优秀人数、不及格人数、实考人数，统计优秀率和及格率就容易了。假设语文科优秀人数、不及格人数和实考人数统计公式所在的单元格分别为C47、C48和C52，优秀率和及格率的统计公式就分别为“＝C47/C52”和“＝（C52－C48）/C52”。设置好第一个科目的各项统计公式后，其它科目的相关统计公式只要利用Excel的自动填充功能，用鼠标拖放就能轻松实现了。至此，这个学生成绩统计模板的主体设计已基本完成，剩下的只是些细微的补充了。 五、进一步完善成绩统计模板       这一步的目的是为了更方便的使用成绩统计模板。1、  设置数据有效性验证为了防止输入成绩时出现无效数据（如：误输入字母或负数等）造成统计公式出错，我们为成绩输入区域的单元格设置数据有效性验证。设置方法如下：选中成绩输入区域的所有单元格，点击“数据”菜单中的“有效性”，调出“数据有效性”设置面板。在出现的设置面板中有四个选项卡，分别用来设置“有效性条件”、“选定单元格时的显示信息”、“输入无效数据时的出错警告”及“输入法时否关闭”。在“设置”选项卡中，设置条件为介于0至100之间的小数。在“出错警告”选项卡中，样式设置成“中止”，标题设置成“输入内容有错！”，内容设置成“请输入介于0到100的数字！”。输入法模式设置为“关闭”。经过上面的设置，就不怕输入成绩时出现无效数据了！             2、设置单元格的条件显示成绩统计出来后，老师们往往想一眼看出哪些学生获得优秀，哪些学生不及格，哪些学生是单科最高分，哪些学生是单科最低分。如果能根据条件的不同而使单元格纹底纹显示不同颜色，就能起到一目了然的效果。要实现这些功能必须用到Excel中的条件格式功能。选中第一位学生的第一科成绩所在单元格，点击“格式”菜单中的“条件格式”，调出“条件格式”设置面板。一个单元格可以同时设置三个条件来控制其格式，如果三个条件有重叠交叉现象，则优先满足排在前面的条件。设置条件时有两种方式，一种是直接针对“单元格数值”，另一种方式则是直接编辑公式。本例中使用的是第一种方式。我们为单元格设置两个条件。第一个条件：当“单元格数值”“等于”“＝C$45”时，单元格底纹为绿色；第二处条件：当“单元格数值”“等于”“＝C$46”时，单元格底纹为红色。这里“C45”和“C46”分别提的是第一科最高分和最低分的单元格名称。而在行号前加上“$”是为了在后面的选择性粘贴中起到行绝对定位的目的。选中设置好条件格式的单元格，点击鼠标右键，选择“复制”;再选中成绩输入区域的所有单元格，点击右键，选择“选择性粘贴”;在出现的对话框中，选择粘贴“格式”，再点击“确定”。就完成了成绩输入区域所有单元格条件格式的设置。如果要用颜色区分等级，设置条件格式的方法与上面的方法差不多，这里就不再展开说明了。经过设置后，当单元格中的内容等于本科目最高分时，单元格的底纹颜色将自动变成绿色，而当单元格中的内容等于本科目最低分时，单元格的底纹颜色将自动变成红色；等级不同的学生相应的单元格也会有不同的格式。老师在分析学生成绩时就更方便了！        3、设置窗口冻结一个班的学生数一般都有40人左右，加上表头及各统计项目，整个统计表所占行列都比较多。在本例中，整个统计表共有52行19列，在电脑屏幕中一页根本显示不完全。在这样的表格中输入和查看统计内容时往往会出现数据内容与行列标题对应不上的现象，查看起来很不方便。为了进一步便于使用，可以使用Excel的窗口冻结功能来帮忙。先选中第一位学生的第一科成绩所在的单元格（C3），再选择窗口菜单中的“冻结窗格”命令。表格中将出现一横一竖两条黑线，将表格的列标题与数据内容、学号姓名与数据内容分隔开来。经过“冻结窗格”的表格的被冻结部分在查看数据时能固定在原处，便于数据的查看。如果只想冻结行或列，也可以只选中某行或某行再执行“冻结窗格”命令。如果想取消“窗口冻结”，可选择窗口菜单中的“取消窗口冻结”命令。经过合理“冻结”的窗口，对于数据量大的表格来说，大大的增加了使用的方便性，而且并不影响打印。 4、  其它补充设置1）考虑到有些班级学生数较多，一页内打印不完整，为了打印后的查看方便，可以设置一个“打印标题行”，使打印出来后每一页都有表头和列标题。具体做法是：选择文件菜单中的页面设置，在出现的“页面设置”对话框中选择“工作表”选项，再在“打印标题”中的“顶端标题行”中设置为“$1:$2”，确定即可。设置成功后，打印出来的内容每一页都会有第一行的表头和第二行的列标题。 2）由于本成绩统计模板绝大部分自动功能都是用公式得出的，公式一旦改变势必会改变相关的结果。为了保证模板的功能在使用中不被误操作所破坏，还应给模板加上“智能锁”――文档保护。Excel的文档保护功能非常灵活，可以设置哪些区域允许编辑，哪些区域禁止编辑。就本例来说，应只允许编辑学生学号姓名和成绩区域，即单元格A3至单元格O42之间的区域，其余区域均禁止编辑。具体做法如下：①选择“工具”菜单中的“保护”子菜单；保护子菜单有四个子项，先选择“允许用户编辑区域”项目；在出现的对话框中新建一个保护区域，标题自定，“引用单元格”确定为“=$A$3：$O$42”。② 再选择“工具”菜单中的“保护”子菜单中的“保护工作表”项目，在出现的对话框中选择好要允许所有用户所进行的操作，有必要的话还可以设置解除保护的密码，再点击确定。这样，成绩表模板就有了一把安全的“智能锁”了！如要更改一些设置，或添加一些功能，可以先解除文档保护再对文件进行编辑。         历经“千辛万苦”，一个拥有完备功能的成绩表模板终于呈现在我们面前了。在这个模板中，只要输入学生姓名、学号及成绩，所有相关的统计内容都会自动出现，给老师们带来了极大的方便！       总结一下，本例的成绩统计模板没有用到什么高深的技术，只用了Excel中的8个函数及一些基本功能，如：条件格式、数据有效性验证、绝对引用、窗口冻结、页面设置和文档保护功能等。这些函数和基本功能相互“协作”，共同完成一系列统计功能，形成有一定固定模式的“统计模板”。当然，这个模板还存在一些不足之处，我们继续深入研究Excel的其它功能，如：宏的应用等，进一步完善成绩统计模板的功能，使之更“智能”更“灵活”。