办公自动化高级应用案例教程 8.2 综合实例-成绩单处理.ppt

8.2 综合实例-成绩单处理 新课导入 学期末临近，大家即将进行期末考试，考试后我们老师又得面临大量的成绩评定与统计等工作，用传统手工统计肯定是out了，费时又费力，用Excel相关函数方便地进行多种数据的分析和统计，可明显地提高工作效率，减轻劳动强度。我们今天就一起来学习学习统计的方法。 预备知识 成绩单处理流程：录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率→制作各科统计分析图表→打印成绩单等。 1．RANK（ ）函数 2．COUNTIF（ ）函数 3．FREQUENCY（ ）函数 功能：这是一个专门用于统计某个区域中数据的频率分布函数。 语法：FREQUENCY(data_array,bins_array) 预备知识 参数：其中Data_array为一数组或对一组数值的引用，用来计算频率。如果 data_array 中不包含任何数值，函数 FREQUENCY 返回零数组。Bins_array为一数组或对数组区域的引用，设定对data_array 进行频率计算的分段点。如果bins_array 中不包含任何数值，函数 FREQUENCY 返回data_array 元素的数目。 应用举例： 预备知识 注意：由于函数 FREQUENCY 返回一个数组，必须以数组公式的形式输入。当您输入函数后, 按 Ctrl + Shift + Enter组合键确认，在Microsoft Excel中输入任何数组时必需按此组合键表示确认。 4．IF（ ）函数 学习案例 使用Excel进行成绩单数据的统计分析工作。 任务1 统计每位学生的总分、平均分 任务2 保持学号顺序不变按总分排名 提示：要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使用RANK(X，Y，Z)函数。其中有三个参数，第一个参数X为某个学生的成绩所在单元格；第二个参数Y为整个班级成绩所在的区域；第三个参数Z是可选的，表示排名方式，若省略或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。 任务3 将体育百分制转换成不同的等级 根据教务处要求，把体育成绩转换成对应的等级，转换规则如下：优秀(90~100)、良好(75~89)、合格(60~74)与不合格(低于60)四个等级。 任务4 统计各学科相应分数段学生数 方法一：用COUNTIF函数统计 方法二：用FREQUENCY函数统计（这是一个专门用于统计某个区域中数据的频率分布函数） 任务5 突出显示部分成绩 问题1：使大于等于95分的成绩用蓝色加粗显示 问题2：将低于该科平均分的各科成绩以浅绿色底纹显示 任务6 制作各科统计分析图表 例如： 任务7 生成一个成绩单模板文件 考试结束后,为了不再重复上述繁琐的工作，可把上述工作表另存为一个模板,具体操作如下: 步骤1．把上述工作表复制一份到另一工作簿中，然后删除所有学生的单科成绩（即表中D3:N62部分）。 步骤2．执行“文件|另存为”菜单命令，在“保存类型”下拉列表框中选“模板”（即*.xlt文件），单击“保存”按钮，把它保存为一个模板文件，可一劳永逸。 小结和作业 1. 小结：请大家根据上课内容自行作小结。 2. 作业： 1．以学习案例中的原始成绩单为数据源，计算各科成绩的“低分率”、“及格率”和“优秀率”。 低分率：指各科40分以下人数与总人数的比值。 及格率：指各科60分以上人数与总人数的比值。 优秀率：指各科90分以上人数与总人数的比值。 2．以“练习2数据源.xls”文件为基础，进行以下操作： （1）冻结“标题栏”、“字段栏”和“序号”、“姓名”列数据。 （2）在“序号”列输入序号列内容（001～042）。 （3）计算各科目的平均分，结果取整。 （4）求每位学生的“总分”和 “名次”，最高分对应“1”名次。 作业 （5）计算各科“学分”及总学分，学分转换规则如下： [85,100]:5个学分 [75,84]:4个学分 [60,74]:3个学分 [50,59]:2个学分 [40,49]:1个学分 [0，39]:0个学分 （6）插入如右上图所示的Excel图表。 （7）将各科目不及格的成绩用红色标记。