学生单元成绩统计表制作
Excel在学校中的应用25-多班级学生单科成绩分数段统计(三)
Excel在学校中的应用25-多班级学生单科成绩分数段统计（三）4.4多班级学生单科成绩分数段统计（三）案例背景某中学初三年级有9个教学班，期末考试后，需要统计全年级9个班级学生的期末考试各个分数段人数分布情况，以便进行综合分析。
本案例通过尝试使用数据透视表技术以数学学科为例来统计全年级9个班级学生的期末考试各个分数段人数分布情况。
要实现本案例中的功能，学员应该掌握以下EXCEL技术点●数据透视表●数据透视图●综合应用逻辑判断公式设计与操作，用逻辑判断替代 IF函数和AND函数的嵌套。
最终效果展示4.3.1创建原始数据表Step 1创建工作簿、重名工作表创建工作簿“年级单科成绩分数段统计表.xls”，然后将工作表重命名为“成绩表”，删除多余工作表。
Step2输入原始成绩①在单元格A1中输入“班级”，单元格B1中输入“姓名”，单元格C1中输入“性别”，单元格D1中输入“数学”。
②输入初三年级1班、2班……9班全部251名学生的的班级、姓名、性别和数学成绩。
Step 3输入分数段分段标准在单元格E1，F1，G1，H1，I1分别输入“60分以下”，“60-70”, “70-80”, “80-90”, “90-100”。
Step 4设计“计数“公式①在单元格E2输入公式:” =IF(AND($D2>=0,$D2<60),1,0)”。
②在单元格F2输入公式:” =IF(AND($D2>=60,$D2<70),1,0)”③在单元格G2，H2，I2中陆续输入公式:” =IF(AND($D2>=70,$D2<80),1,0)”” =IF(AND($D2>=80,$D2<90),1,0)”” =IF(AND($D2>=90,$D2<100),1,0)”Step 5改进公式①在单元格E3输入公式:” = ($D3>=0)*($D3<60)“②在单元格F3输入公式:” = ($D3>=60)*($D3<70)“③在单元格G3，H3，I3中陆续输入公式:” = ($D3>=70)*($D3<80)“” = ($D3>=80)*($D3<90)“” = ($D3>=90)*($D3<100)“Step6复制填充公式选中单元格区域E3:I3，双击I3右下角的填充柄即可完成第4行至最后一行的公式填充。
用EXCEL轻松处理学生成绩的方法
用EXCEL轻松处理学生成绩期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。
流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。
有了EXCEL，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！我迅速地打开电脑，启动EXCEL2000，录入学生的考试成绩，如图1所示。
然后在J2单元格处输入公式"=sum(c2:i2)",然后拖动填充柄向下填充，便得到了每人的总分。
接着在k2单元格处输入公式"=average(c2:i2)"，然后拖动填充柄向下填充，便得到了每人的平均分。
平均分只需保留一位小数，多了没用。
所以选中第k列，用鼠标右键单击，从弹出的快捷菜单中选"设置单元格格式（F）…",如图2所示，在数字标签中选中"数值"，小数位数设置为1位。
下面按总分给学生排出名次。
在L2单元格处输入公式"RANK(J2,J$2:J$77,0)",然后拖动填充柄向下填充，即可得到每人在班中的名次（请参考图1）。
说明：此处排名次用到了RANK函数，它的语法为：RANK(number,ref,order)其中number为需要找到排位的数字。
Ref为包含一组数字的数组或引用。
Ref 中的非数值型参数将被忽略。
Order为一数字，指明排位的方式。
·如果order 为0 或省略，Microsoft Excel 将ref 当作按降序排列的数据清单进行排位。
·如果order 不为零，Microsoft Excel 将ref 当作按升序排列的数据清单进行排位。
最后，单击L1单元格，然后在“工具”菜单中选“排序”－＞“升序”，即可按照名次顺序显示各学生成绩。
另外，我们还希望把不及格的学科突出显示，最好用红色显示。
利用EXCEL对学生成绩进行分析与制作学生成绩条
利用EXCEL对学生成绩进行分析与制作学生成绩条每到学期结束时，教师的一项重要工作就是要统计学生的成绩，在电脑逐步普及的今天，我们就把这项繁杂的工作交给Excel去自动完成吧。
本节任务：制作一个学生成绩自动统计表，可以自动统计最高分、最低分、总分、平均分、名次、三率等数据信息，还可以根据自定条件以不同的颜色显示分数。
自动统计表做好以后还可以保存成模板，以便以后使用。
涉及术语：单元格、工作表、工作薄、引用(相对/绝对)、自动填充、排序、条件格式等。
涉及函数：AVERAGE、COUNTIF 、MAX、MIN、RANK、SUM任务一：统计最高分、最低分、总分、平均分、名次、三率等数据信息。
1、启动Excel，同时选中A1至L1单元格，按“格式”工具条上的“合并及居中”按钮，将其合并成一个单元格，然后输入统计表的标题“高一（1）班期末成绩统计表2、根据统计表的格式，将有关列标题及相关内容输入到相应的单元格中。
提示：其中学号的输入可通过“填充柄”快速完成。
3、选中K3单元格，输入公式：=SUM(C3:J3)，用于计算第一位学生的总分。
！4、选中L3单元格，输入公式：=RANK(K3,$K$3:$K$12)，计算出第一位学生总分成绩的名次（此处，假定共有10位学生）。
5、同时选中K3和L3单元格，将鼠标移至L3单元格右下角的成“细十字”状时（通常称这种状态为“填充柄”状态），按住左键向下拖拉至L12单元格，完成其他学生的总分及名次的统计处理工作。
6、分别选中C16、C17单元格，输入公式：=MAX(C3:C12)和=MIN(C3:C12)，用于统计“语文”学科的最高分和最低分。
7、选中C18单元格，输入公式：=AVERAGE(C3:C12)，用于统计“语文”学科的平均分。
注意：如果成绩表中没有输入成绩时，这一公式将显示出一个错误的值“#DIV/0!”,这个错误代码将在数据输入后消失。
8、选中C19单元格，输入公式：=SUM(C3:C12)，用于统计“语文”学科的总分。
用EXCEL进行学生成绩统计
图2
我们还可以对制作的班级工作表据进行条件设置，如语文、数学、英语的总分是120分，那么要达到对输入的数据不能大于120分。对全校所有班可以进行批处理。选中全部班级工作表，再选择语文、数学、英语三科的成绩，选中后打开“格式”菜单栏下的“条件格式”，如图3。我们在输入数据时，如果输入的数据大于120分将无法输入，可以设置为语音提示。其它学科根据学科总分可以进行相应的设置。以免在输入成绩时造成一些错误。
=COUNTIF(C$2:C$21,">=300")-COUNTIF(C$2:C$21,">=350")(同上)；
=COUNTIF(D$2:D$21,"<=300")(总分小于或等于300的学生人数)。
2.用If函数选取优秀学生
学校对取得优异成绩的学生进行表彰时，往往要根据学生成绩和在校表现等进行筛选，有时只要满足一个条件就行了，有的要同时满足两个条件。这时就要用到Excel中的If函数来进行筛选。如：我校每学期都要根据成绩对学生进行表彰，得分率大于或等于85%的表彰为“特优生”；得分率大于或等于80%的表彰为“优秀生”；得分率大于或等于75%的表彰为“三好生”；在筛选这三类学生时用下列函数来选择：
4、百分制均分：C4*10/12百分制均分，在学校评价中，为了便宜于不同学科由于分值的不同无法进行比较，把所有不同分数的都转化为百分制均分。在公式C4*10/12中，C4是存放语文均分的单元格，它是120分制时的均分，转化为百分制时要除1.2。
图5
5、优分人数：COUNTIF('801'!$D$3:$D$65,">=96")，COUNTIF是选取区域内单元格内满足条件的单元格个数。COUNTIF('801'!$D$3:$D$65,">=96")是指在801工作表中D列从第3行到第65行内数据满足大于或等于96（语文、数学、外语均为96分及以上为优分）的单元格个数。
学生成绩综合统计表
学生成绩综合统计表4.6学生成绩综合统计表（一）案例背景期末考试后，除了要统计各个分数段人数分布情况外（前面章节已经介绍了多种统计分数段的方法）可能还要统计年级和各班的及格人数与及格率，优秀人数与优秀率，以及各班里前、后若干名（如年级前20名和后20名）的人数和比例等。
本案例以某中学高一年级4个教学班100名学生的期末考试的3科成绩和总分为样本，统计全年级4个班级学生的期末考试及格人数与及格率，优秀人数与优秀率，以及各班里年级前20名和后20名的人数和比例，以便进行综合分析。
同时将前20名和后20名学生的成绩在统计表中标示出来，以使得前、后20名的成绩能够在成绩表突出显示。
要实现本案例中的功能，学员应该掌握以下EXCEL技术点•基础知识条件格式•函数应用ISNUMBER函数SUM函数，LARGE 函数，SMALL函数•综合应用函数嵌套数组公式。
最总效果展示461创建原始数据表Step 1创建工作簿、重名工作表创建工作簿“综合统计表.xls”，然后将工作表重命名为"统计表”，删除多余工作表。
Step2输入原始成绩①在单元格A49输入表格标题：“高一年级期末考试成绩统计表”，选择你好单元格区域A49:G49，单击“合并及居中”按钮，字体、字型、字号设置为黑体加粗16号。
②在单元格区域A50: G50输入字段名：“班级”，“学号”，“姓名”，“语文”，“数学“，“英语”和“总分“。
③在单元格区域A51 : F150输入全年级4个班100名学生的“语文”，“数学“，“英语”成绩④在单元格G51输入公式：“ =SUM(D51:F51) ”, 然后按键确认，计算出第一个学生的“总分”。
⑤选中单元格G51，双击单元格G51右下角的填充柄即可完成其余学生“总分”的统计工作。
⑥选中单元格区域A50：G150,按图2样式设置边框，添加底纹，文本居中显示462统计及格人数与及格率Step 1创建统计表框架在单元格区域A1:J9按图3样式输入“高一年级各班及格人数与及格率统计表”框架信息。
用Excel统计成绩
利用 Excel 进行学生成绩统计个旧市第十四中学 张海笑图1 Excel 是Microsoft Office 办公软件的组件之一，是进行数值运算、数值分析等的电子表格软件。
用它可以方便地进行学生学习成绩的统计，经笔者多次使用，效果很好，可以大大简化教师的工作，提高了工作效率。
以下是本人进行学生成绩统计的过程：首先，我们要创建一个成绩统计表模板，以便今后在它的基础上进行工作。
可以分为以下步骤：1．启动Excel 后，在表单的第一行A1、B1、C1……等单元格中输入成绩表的项目：学号、姓名、各考试科目，并在其中录入学生的学号、姓名。
学号是连续的数字，输入时，可利用Excel 的自动填充功能很方便地填入：先输入第1、2两个学生的学号并选定这两个单元格，将光标移到选定的单元格右下角，当光标变成细十字形时，往下拖拉，直到最后一个学生所在的一行，即可填入所有学生的学号。
（图1）2．在第一行要计算总分的科目后加入“总分”一列，选中第一位学生的“总分”单元格后，单击【∑】按钮，拖动鼠标横向选定该同学的各科成绩单元格。
也可直接在编辑栏中输入公式：=SUM （C2:G2），[注：C2、G2为要统计的起、止单元格名称，按实际情况输入，其间用“：”相隔，下面的公式与此相同]然后单击编辑栏中的【√】，则在“总分”单元格中有了该学生的总分计算公式。
选取该单元格，用鼠标拖拉到最后一名学生的“总分”单元格，每位学生的总分计算公式自动设置完成。
（图2）3．在第一行“总分”后加入“平均分”一列，选中第一位学生的“平均分”单元格后，单击【fx 】函数按钮，在【粘贴函数】对话框中选取常用函数中的 AVERAGE 函数，（图3）拖动鼠标选定该同学的各科成绩单元格。
也可直接在编辑栏中输入公式：=AVERAGE （C2:G2），用鼠标拖拉自动填充平均分计算公式。
[注：如果有些科目不计入总分，则设置项目时就将这些科目安排在最后，不能插入到要统计科目之间。
学生单元成绩统计表
###### ###### ###### ###### ###### ###### ###### ###### ###### ###### ######
2014秋期末
95 89 96 93 94 95 95 91 93 96 87 95 94 96 87 93 95 92 86 93 92 92 94 97 96 95 96 93
2014秋期末
95 88 94 92 97 90
93.11765 29 5 0 0 0 0 262 34 7.705882
中税小学2017级（1）班数学目标考核成绩统计表
项目
平时成绩
学 号
分数
一
姓名
二
Байду номын сангаас
三
四
五
六
七
八
九
十
十一
29 30 31 32 33 34
贺俊桦 陈磊 蔡沂峰 秦凤娇 余秦河 陈坤龙
平均分 90-100 80-89 70-79 60-69 40-59 40以下 总积分 总人数 平均积分
###### ###### ###### ###### ###### ###### ###### ###### ###### ###### ###### 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
中税小学2017级（1）班数学目标考核成绩统计表
项目
批量打印学生成绩工具制作
批量打印学生成绩工具制作在学校工作中，每一次考试后免不了需要进行成绩分析，给每一个班打印成绩表。
在给班级打印成绩表时还需根据每个班的数据调整行高和列宽。
这些工作都是重复，锁碎的。
我们可以做成一个模板，计算好各班均分，优生率等数据。
这样下次分析时只需要把学生成绩复制到模板中就可以实现秒算成绩分析，再利用wps+vba实现批量打印班级的成绩表。
原来需要加班加点的工作，现在可以几分钟内完成了。
剩下时间可以喝茶，休息了。
下面是我制作批量打印工具的过程1.准备工作（1）准备一个excel工作薄，工作表有：文科成绩，理科成绩，基本设置，统计表，班级成绩表。
这些工作表用来存放学生的成绩，班级的设置等信息。
班级基本设置部分理科学生成绩（2）办公软件，office或wps。
特别说明，由于批量打印需要用vba来编写一些让电脑操作的过程，所以在使用office或wps时需要允许宏运行。
Wps不直接支持vba，可以下载插件来实现对宏的支持，下载地点可以百度，或上wps论坛查找。
如上图所示，在“开发工具”选项卡中有“宏”和“vb编辑器”2.vba组织结构如下图，包含ThisWorkbook应用程序工作薄，5个工作表，一个窗体，一个模块2.1打印控制窗体（UserForm)窗体中包含三个按钮，分别命名为”选中全部班级”，”CommandButton2”，”CommandButton3”。
可以根据自己的习惯命名2.2模块内容包含两个过程”复制班级成绩(n As Integer)”,”页面设置()”2.2.1 过程1Sub 复制班级成绩(n As Integer)Dim shtdata As Worksheet '成绩表Dim row_end As Integer '成绩表最后一行的行号Dim col_end As Integer '成绩表最后一列的列号Dim calss_col As Integer '查找“班级”列的列号Dim str As String '定义一个字符串，表示文科或者理科工作表Dim str_col As Integer'Dim n As Integer '打印班级工作表的班级的序号str_col = Worksheets("基本设置").Range("4:4").Find(打印控制.Controls("myCheckBox1" & n).Caption,SearchOrder:=xlByColumns).Columnstr = Worksheets("基本设置").Cells(5, str_col).Value '通过查找获得班级所属的类型是文科还是理科Dim i As Integer'循环变量Dim j As Integer '循环变量j = 3Worksheets("班级成绩表").Range("2:100").Delete '先删除班级成绩表中2-100行的内容。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
学生单元成绩统计表
1、期初人数：在A16中输入=12，表示12是固定的人数。
2、与考人数：在A17中输入=COUNT(B2:B13)，表示计算B2到13的含有数值的单元格个数，即参加
考试的人数。
3、总分：在B18中输入=SUM(B2:B13)，表示计算B2到13的单元格的数值总和。
4、平均分：在B19中输入=B18/B17，表示用总分除以与考人数。
5、及格人数：在B20中输入=COUNTIF(B2:B13,">=60")，表示计算B2到B13的单元格中满足>=60条
件的单元格个数，即及格人数。
6、及格率：在B21中输入=B20/B17，表示用B20中的及格人数除以B17中的与考人数。
7、优秀人数：在B21中输入=COUNTIF(B2:B13,">=85")，表示计算B2到B13的单元格中满足>=85条
件的单元格个数，即优秀人数。
8、优秀率：在B22中输入=B22/B17，表示用B22中的优秀人数除以B17中的与考人数。
（上面8项设置好后，后面的单元考试只要先输入每位学生的成绩后，1-8项的数据统计，只要把手柄向右
拖动，即可自动跳出相应的数据。
）
9、选中所有的数值（可以把未考的单元的无分数的分数区域同时选中），格式，条件格式，设置<60为
红色字体，再添加格式>=85为绿色字体。
以后输入分数后，根据分数的范围自动跳出不同颜色。
表格附后，表格的移动要选择全部表格，表格属性，对齐方式“居中”，文字环绕“无”。
A B C D E F G H I
1
姓名第一单
元
第二单
元
第三单
元
半期考
第四
单元
第五
单元
第六
单元
期末
考
2 陈嘉龙88 96 94 93.5
3 马建兰86 9
4 84.
5 90.5
4 马鑫光89 94 9
5 94
5 胡司镐87.5 83 70 84
6 罗赛琴85.5 79 75 76.5
7 马建华62 74 70 76
8 肖金水92 81 89 82
9 马天华87 73.5 61.5 73.5
10 马嵚崟74 51 37 71
11 肖良章72.5 69 76 56
12 马雄辉80 56 41 34
13 陈梅芳73
14
15
16 期初人
数
12 12 12 12 17 与考人
数
12 11 11 11
18 总分976.5 850.5 793 831
19 平均分81.38 77.32 72.09 75.55
20 及格人
数
12 9 9 9
21 及格率100.00% 81.82% 81.82% 81.82%
22 优秀人
数
7 3 3 3 23 优秀率58.33% 27.27% 27.27% 27.27%。