利用Excel统计分析学生成绩目标掌握Excel中的公式使用方法，能够利用函数统计学生成绩并生成图表；任务创建学生成绩表并按教学实际中的要求进行统计；第1步 初识Excel电子表格软件Microsoft Excel是微软公司的办公软件Microsoft office的组件之一。直观的界面、出色的计算功能和图表工具，再加上成功的市场营销，使Excel成为最流行的微机数据处理软件。如图所示。 利用Excel统计分析学生成绩 可以想象一个装满报表的账簿，这个账簿在电子表格中就是“工作簿”（Book2），而一张张报表就是工作表（Sheet1）。一个电子表格文件对应一个工作簿，一个工作簿可保存1256张工作表，一个工作表又包含无数个单元格。 利用Excel统计分析学生成绩 什么是“单元格”我们在书中会经常看到“单元格”这个词，电子表格就是按行和列排列的单元格的集合。单元格中可容纳数字、文本或公式。每个单元格中都享有自己唯一的地址，即行列交点处坐标，当我们说“B3”的时候，就是电子表格网格上方标记为“B”的列和左侧标记为“3”的行的交点。 第2步 建立“学生成绩”工作表当你第一次启动Excel 时，它会打开一本首页为“Sheet1”的空白工作簿。工作簿默认由为三个工作表组成，即Sheet1、Sheet2、Sheet3。在Sheet1中的单元格中，输入下列表格： 利用Excel统计分析学生成绩 8 操作指南 输入数据的技巧输入数字时，须遵照下面的规则：在数字前输入的正号“+”被忽略。可以在数字中包括逗号，如：26,468,508。数值项目中的单个圆点“.”作为小数处理。输入分数（如5/7）时，要先输入“0”和空格，然后输入分数，否则系统将按日期对待。对于全部由数字组成的字符串（如电话号码、邮政编码等），常常当作字符处理。为了不使系统把它当作数字看待，输入时，可在其前面添加一个英文状态下的单引号（例如：24346688）。日期的输入形式有多种,常见的日期格式为：mm/dd/yy、dd-mm-yy、例如我们要输入1999年9月10日，只要输入09/10/99或者10-09-99就可以了。若想在单元格中输入当前日期，只要按组合键Ctrl + ；即可。 第3步 处理学生成绩数据在这一个步骤中，学员将根据一下过程，对前面步骤中已经输入的学生成绩进行统计分析。计算每位同学的总分利用函数计算总分的方法如下：在总分栏目中的G2单元格内输入“=sum(D2:F2)”，表示计算D2到F2的总和。然后，利用自动填充方式，即可计算出每名学生的总分。 利用Excel统计分析学生成绩 8 操作指南 如何使用“自动填充”方法选中单元格，把鼠标指针指向填充柄（选定框右下角的黑色小方框），指针就会变成黑色十字状，若要自动填充，按住左键，向下拖动。也可以用鼠标双击填充柄来实现自动填充功能。利用Excel统计分析学生成绩 求和函数Sum ()的用法功能：返回某一单元格区域中所有数字之和。语法：Sum(number1,number2,)。其中number1, number2,为1到30个需要求和的参数。举例：=Sum(F2:F18) 计算F2到F18单元格区域内数据之和。 利用公式计算总分的方法如下：在总分栏目中的G2单元格内输入“=D2+E2+F2”，表示求D2、E2、F2三个单元格内数据之和。再利用自动填充，即可计算出每位同学的总分。 利用Excel统计分析学生成绩运算符及引用数学运算符：+（加）、-（减）、*（乘）、/（除）、（乘方）、%（百分比）。(见4-1)文本运算符：&（连接）比较运算符：=（等于）、（大于）、（小于）、=（大于等于）、=（小于等于）、（不等于）。冒号（:）、空格及逗号（,）“A3:A7”表示A3，A4，A5，A7五个单元格组成的区域；（见4-2、4-3）“A3:A5 A4:A7”代表A4和A5两个单元格区域；“A3:A4,A6:A7”代表A3、A4、A6、A7四个单元格区域。注释：空格是一种交集运算符，逗号是一种并集运算符每个单元格都有自己的地址，在公式中，单元格的引用有相对引用、绝对引用、混合引用等方式。相对引用：通常用行、列地址表示（如E3）绝对引用：通常在行、列地址前加“$”表示（$E$3）混合引用：E$3、$E3（见4-4） 计算数学成绩平均分在D14单元格内输入“=average(d2:d12)”，表示计算单元格D2到D12的平均值。利用自动填充（鼠标指向填充柄向右拖动），可以计算出每一学科的平均分。 利用Excel统计分析学生成绩 求平均值函数Average()的用法功能：返回参数的算术平均值。语法：Average(number1,number2，.)。其中number1, number2，.为要计算平均值的130 个参数。举例：=Average(F2:F18) 计算F2到F18单元格区域内数据平均值。 统计数学成绩中85分以上的人数在D15单元格内输入“=countif(d2:d12,”=85”)”，表示统计单元格D2到D12的中大于或等于85分的人数。 利用Excel统计分析学生成绩 条件统计函数Countif()的用法功能：计算给定区域内满足特定条件的单元格的数目。语法：Countif(range，criteria)。其中range为需要计算其中满足条件的单元格数目的单元格区域。criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。条件可以表示为32、32、32、apples。举例：=Countif(F2:F18,”60”) 计算数学成绩中的最高分数在D16单元格内输入“=max(d2:d12)”，表示计算单元格D2到D12的最大值。利用自动填充（鼠标指向填充柄向右拖动），可以计算出每一学科的最高分。 利用Excel统计分析学生成绩最大值函数MAX()的用法 功能：返回数据集中的最大数值。语法：MAX(number1,number2,.)。其中number1,number2,.为需要找出最大数值的1 到30个数值。举例：=MAX(F2:F18) 计算F2到F18单元格区域内数据最大值。 计算数学成绩中的最低分在D16单元格内输入“=min(d2:d12)”，表示计算单元格D2到D12的最小值。利用自动填充（鼠标指向填充柄向右拖动），可以计算出每一学科的最低分。 利用Excel统计分析学生成绩最小值函数MIN()的用法功能：返回给定参数表中的最小值。语法：MIN(number1,number2, .)。其中number1, number2,.是要从中找出最小值的1 到30 个数字参数。举例：=MIN(F2:F18) 计算F2到F18单元格区域内数据最小值。 依据学生总成绩进行排名如果需要对学生的总成绩进行排名，利用函数RANK()即可。这个函数的作用是把某数在一组数中的排位计算出来。新建一列（H列），在H1单元格内输入“名次”。在H2单元格内输入“=rank(g2,g:g)”，表示计算G2单元格的数据在G列范围内的排名。然后，利用自动填充方式，即可计算出其他同学的名次。 利用Excel统计分析学生成绩（注：g:g意思是g列到g列） 按照名次排序为了方便观察学生排名情况，可以按照名次进行排序。鼠标选择数据区内任一单元格，然后，选择菜单栏中的“数据”“排序”命令。出现对话框，在主关键字下拉框中选择“名次”，选择“升序”，然后单击“确定”。即可实现学生按总成绩的排名。 利用Excel统计分析学生成绩 用Excel做数据分析使用Excel自带的数据分析功能可以完成很多专业软件才有的数据统计、分析，这其中包括：直方图、相关系数、协方差、各种概率分布、抽样与动态模拟、总体均值判断，均值推断、线性、非线性回归、多元回归分析、时间序列等内容。注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。利用Excel统计分析学生成绩 第4步 利用图表显示学生成绩Excel具有完整的图表功能，它不仅可以生成诸如条形图、折线图、饼图等标准图表，还可以生成较为复杂的三维立体图表。对学生成绩数据进行图表处理，可以更直观地进行教学分析，找出工作表格不容易发现的问题，使得教学评价更为有效。输入数学成绩统计表（B19:H21） 利用Excel统计分析学生成绩 利用countif()函数统计各分数段人数在D20单元格中输入“=COUNTIF(D2:D12,50)”，表示计算D2到D12区间的数学成绩中数据小于50的人数。 利用Excel统计分析学生成绩 提示：统计50-59分数段人数应输入：“=COUNTIF(D2:D12,60)-COUNTIF(D2:D12,50)”计算各分数段所占百分比在D21单元格中输入“=C20/(C20+D20+E20+F20+G20+H20)”，表示计算成绩小于50分的人数占总人数的百分比。（提示：应将D21单元格的格式设置为百分比） 利用Excel统计分析学生成绩 8 操作指南 如何设置单元格的格式选定单元格或区域，单击“格式”“单元格”，选择“数字”，在选择“百分比”，然后单击“确定”。利用Excel统计分析学生成绩 创建数学成绩各分数段分布图选定数据区域，单击“插入”菜单“图表”或单击常用工具栏中的“图表向导”。 利用Excel统计分析学生成绩 选择“自定义类型”“平滑直线图”，然后，依据向导，单击“下一步”，直至完成图表。 利用Excel统计分析学生成绩 数学成绩各分数段分布图如下： 利用Excel统计分析学生成绩 如果需要修改图表，鼠标指向图表区域中某一处，单击右键，弹出快捷菜单，选择相应修改项即可。注：鼠标指向图表中不同区域，弹出的菜单内容不同。利用Excel统计分析学生成绩 第5步 修饰工作表在这一步骤中，学员根据实际需要，参照下列操作方法修饰学生成绩单。 设置行高与列宽：选中区域，单击“格式行”或单击“格式列”）； 格式化单元格中的数据：选择某一单元格或某一区域，单击“格式单元格数字”； 修改字符外观：选定单元格或区域，单击“格式单元格字体”； 标题合并居中：先选定标题居中区域，再单击“格式”工具栏中“合并及居中”按钮； 设置对齐方式：选定单元格，单击“格式单元格对齐” 设置边框和底纹：单击菜单栏中的“格式单元格边框”。