搜索
利用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)，用于统计“语文”学科的总分。9、选中C20单元格，输入公式：=COUNTIF(C3:C12,'=80')/COUNTIF(C3:C12,'0')，用于统计“语文”学科的优秀率。同样在C21内输入相应公式统计良好率。10、同时选中C16至C21单元格，用“填充柄”将上述公式复制到D16至J21单元格中，完成其它学科及总分的最高分、最低分、平均分、总分、优秀率和良好率的统计工作。至此，一个基本的成绩统计表制作完成，下面我们来进一步处理一下。任务二：根据自定条件以不同的颜色显示分数。（在此例中，让每科分数高于等于平均分的分数显示蓝色，低于的则显示红色）11、选中C3单元格，执行“格式、条件格式”命令，打开“条件格式”对话框（如图2），在中间方框选中“大于或等于”，在右侧的方框中输入公式：=C18(平均分所在单元格)，然后按“格式”按钮，打开“单元格格式”对话框，将字体颜色设置为“蓝色”。再按“添加”按钮，仿照上面的操作，设置小于平均分的分数字体颜色为“红色”。注意：经过这样的设置后，当学生的“语文”成绩大于或等于平均分时，显示蓝色，反之显示红色12、再次选中C3单元格，按“格式”工具条上的“格式刷”按钮，然后在C3至J12单元格区域上拖拉一遍，将上述条件格式复制到相应的区域中，完成其他学科及总分的条件格式设置工作。把学生的成绩填入到表格中试试看，效果不错吧。任务二：将制作完成的统计表保存为模板。如果你经常要统计学生的成绩，我们将其保存为模板，方便随时调用。13、将工作表中的学生成绩等内容删除，执行“文件、保存（另存为）”命令，打开“另存为”对话框（如图4），按“保存类型”右侧的下拉按钮，在随后出现的下拉列表中，选“模板（*.xlt）”选项，然后给定一个名称（如“成绩统计”），按下保存按钮。14、以后需要统计成绩时，启动Excel，执行“文件、新建”命令，展开“新建工作簿”任务窗格（如图5），点击其中的“本机上的模板”选项，打开“模板”对话框，双击“成绩统计”模板，即可新建一份新的成绩统计表。15、将学生的成绩填入相应的单元格中，取名保存即可快速完成成绩统计处理工作。在Excel中用宏实现自动生成简单的成绩条Excel具有强大的数据处理和打印输出功能，并且易学易用，是广大用户喜欢使用的电子表格处理软件。现在一些学校人员喜欢用Excel打印本学校的学生成绩条，但在Excel中要将成绩总表(手工地转换为成绩条则是一件比较烦琐的事，下面是我编写的一个Excel宏，运行这个宏就可将编辑好了的成绩总表很方便地转换为成绩条打印输出。在Excel中新建一个文件，将其命名为“成绩条”，在工作表“sheet1”中输入并编辑成绩表,成绩共30条，如图：然后，点击“工具”菜单→“宏”→“宏…”→输入宏名“编辑成绩生成成绩条”→创建，输入如下的宏的各行文本，输入完成后保存该宏：SubMacro1()Worksheets('Sheet1').ActivateForhang=1To(30-1)*3Rows('1:1').SelectSelection.CopyCells(hang+2,1).SelectSelection.InsertShift:=xlDownSelection.EntireRow.Inserthang=hang+2NexthangEndSub最后执行宏，成绩条如下：以后每月要打印成绩条时，只需将“30”改成总成绩表的记录数就可以了。用Excel的VBA来制作精美学生成绩条。解决问题的思路因为每个学生只有一个学号，所以学号是惟一的，根据学号惟一性这一特点，使用VBA里的判断语句，如果学号不同，就在两者之间插入一个空白行，然后再在每个空白行粘贴复制的表头，最后使用循环语句，自动制作每个学生成绩记录的表头。解决问题的方法打开学生的成绩表，我们需要另存为另外一个表来制作成绩条，以免影响成绩表的原貌。在VBA的工程资源管理器中双击Sheet1，然后出现代码窗口，在代码窗口输入如下代码：Subcjt()Application.ScreenUpdating=FalseSheets(1).[A1].CurrentRegion.CopySheets(2).[A1]'将表一的成绩表复制到表二a=(Application.WorksheetFunction.CountA(Sheets(2).[b2:b2000]))*2'sheets(1).[b2:b2000]的字符数的2倍Sheets(2).[A1:R1].Borders(xlEdgeTop).LineStyle=xlDouble'sheets(2).[a1:r1]的下边框是双线Fori=2ToaIfSheets(2).Cells(i,3)Sheets(2).Cells(i+1,3)And(Sheets(2).Cells(i,3)'')ThenSheets(2).Rows(i+1).InsertEndIf'如果第三列的上下单元格的值不相等，则在它们之间插入一个空白行IfSheets(2).Cells(i,3)=''ThenSheets(2).[A1:R1].CopySheets(2).Cells(i,1)EndIf'如果第三列中的单元格是空的，则将Sheets(2).[A1:R1]复制到此行NextApplication.ScreenUpdating=TrueEndSub