任务四应用软件Excel2003操作技术

1、任务四 应用软件Excel 2003操作技术4.4 学生成绩管理报表1教学目标教学内容操作步骤知识技能点退 出训练任务考核与评价2教学目标使用公式和函数进行数据计算。分析和处理Excel工作表中的数据，离不开公式和函数。公式是函数的基础，它是单元格中的一系列值、单元格引用、名称或运算符的组合，可以生成新的值；函数是Excel预定义的内置公式，可以进行数学、文本、逻辑的运算或者查找工作表的信息，与直接使用公式相比，使用函数进行计算的速度更快，同时减少了错误的发生。返 回3教学内容此项目要求使用公式和函数计算学生成绩表中每个学生的总分、平均分、名次和成绩等级，还计算每门课程的平均分和最高分，如图所

2、示。其中，平均分要求计算结果保留1位小数。学生的成绩等级，是按学生的“平均分”的多少转换成“优”、“合格”和“不合格”三个等级。其评价标准为：“优”：平均分85分“合格”：平均分60分，并且平均分85分“不合格”：平均分60分返 回4操作步骤1建立“学生成绩管理”报表启动Excel2003，Excel系统自动建立一个空白的工作簿“Book1”，将其保存命名为“学生成绩表.XLS”。然后，参照教学内容中的示例图输入学生成绩表中的数据，包括报表标题“学生成绩表”、列标题行中的“学号、姓名、英语、数学、计算机基础、C语言、政治、体育、总分、平均分、名次、等级”和“学号、姓名、英语、数学、计算机基础、

3、C语言、政治、体育”8列数据。返 回5操作步骤2计算学生的总分计算总分，即是将总分左侧6项成绩相加。先计算第一个学生的总分，单击I3单元格，输入公式计算，其操作方法有2种：直接输入公式“=C3+D3+E3+F3+G3+H3”。单击工具栏上的【自动求和】按钮“ ”，则系统自动插入函数“=sum(C3:H3)”。在完成第一个学生的总分计算后，下面学生的总分可以采用【填充】操作来复制公式，从而快速地完成计算。即先选择已有公式的单元格，再用鼠标直接拖动该单元格右下角的填充柄，直到最后一个需要填充的单元格I12为止。求和函数sum的语法格式：sum(Number1，Number2，)其中的参数“Numb

4、er1, number2, .”是要计算求和的 1 到 255 个数字参数。参数Number可以是一个数字、数字的名称或单元格的引用，还可以是数组及单元格区域的引用。返 回6操作步骤3计算学生的平均分计算平均分，是计算其左侧6项成绩的平均分，并要求保留1位小数。计算第一个学生的平均分，在J3单元格内输入公式： “=ROUND(AVERAGE(C3:H3),1)”。由于此项计算有2项要求，因此需要使用2个函数嵌套完成这项计算。它们分别是四舍五入函数ROUND（）和平均值函数AVERAGE（）。ROUND函数的语法：ROUND(number,num_digits)其中的参数含义如下：Number

5、需要进行四舍五入的数字。Num_digits 指定的位数，按此位数进行四舍五入。AVERAGE函数的语法：AVERAGE(number1,number2,.)其中的参数“Number1, number2, .”是要计算其平均值的 1 到 255 个数字参数。在完成第一个学生的平均分计算后，下面学生的平均分采用【填充】操作来完成计算，操作方法与计算总分相同。返 回7操作步骤4计算学生的名次计算名次，是计算每个学生的平均分在班级内的排名。计算第一个学生的名次，在K3单元格内输入公式“=RANK(J3,$J$3:$J$12)”。RANK函数是计算一个数值在一系列数值中相对于其它数值的大小排名。其格式

6、如下：RANK（Number，Ref，Order）其中的参数含义如下：Number：指定的数值。Ref：一组数或一个数据列表的引用。Order：0或忽略时，表示降序；非0时，表示升序。公式中，参数Ref中单元格区域的引用采用“$J$3:$J$12”，这种引用在单元格的列标和行号前加上“$”，表示引用工作表中绝对位置的单元格，故称为单元格的绝对引用。这种引用的主要特点是它不会跟着公式复制后位置发生相对改变而发生变化。参数Number中的单元格引用是相对引用，它会跟着公式复制后位置发生相对改变而发生变化。第一个学生的名次计算完成后，拖动K3单元格右下角的填充柄，直到最后一个学生名次的单元格K12为

7、止。返 回8操作步骤5计算学生的成绩等级计算第一个学生的成绩等级。，在L3单元格中输入如下公式： “=IF(J360,“不合格”,IF(J385,“合格”,“优”)”此公式中使用了2个IF函数进行嵌套实现此项计算功能。IF函数的语法格式：IF(Logicl_test,value_if_true,value_if_false)Logical_test，是计算结果为TRUE或FALSE的逻辑判断表达式，可使用任何比较运算符，用来表示判断条件。Value_if_true，是当logical_test为TRUE时，即条件成立时返回的值。Value_if_false，是当logical_test为FAL

8、SE时，即条件不成立时返回的值。公式中，“J360”是第一个条件，若条件成立，则等级为“不合格”；若条件不成立，表示平均分60分，继续判断第二个条件“J3=85, “优”,IF(J3=60, “合格” , “不合格”)”完成第一个学生的成绩等级计算后，下面学生的成绩等级采用【填充】操作来完成计算，操作方法与计算总分相同。返 回9操作步骤6计算每一课程的平均分计算每一课程的平均分，是计算其上面一列10名学生成绩的平均分，要求计算结果保留1位小数。计算第一门课程的平均分，在C14单元格内输入公式： “=ROUND(AVERAGE(C3:C12),1)”。然后向右拖动C14单元格的填充柄，直到最后一

9、门课程的单元格H14为止。7计算每门课程的最高分计算每门课程的最高分，是计算其上面一列10名学生成绩中的最大值。先计算第一门课程的最高分，在C15单元格内输入公式： “=MAX(C3:C12)”。MAX函数的语法格式：MAX(number1,number2,.)其中，参数“Number1, number2, .”是要从中找出最大值的1到255个数字参数。在完成第一门课程的最高分计算后，向右拖动C15单元格右下角的填充柄，直到最后一门课程的单元格H15为止。返 回10知识技能点1单元格引用在公式和函数中，经常要引用某一单元格或单元格区域中的数据。单元格的引用有3种：相对引用、绝对引用和混合引用。

10、（1）相对引用相对引用是直接使用工作表中的列标和行号来表示单元格的地址，表示某一单元格相对于公式所在的当前单元格的相对位置。因此，采用相对引用表示的单元格地址会在公式位置发生变化时也随之而改变。这种变化的规律是公式位置若在水平方向上或在行方向上左右发生变化时，单元格相对引用中的列号将随之改变，而行号不变；若公式位置在垂直方向上或在列方向上上下发生变化时，单元格相对引用中的行号将随之改变，而列号不变。例如，若在E8单元格中输入公式“E4E5E6E7”后，将公式自动填充到F8单元格，将得到公式“F4F5F6F7”的值。即在对F8单元格填充公式时，公式中相对引用的单元格地址中的列号发生了变化，而行号

11、不变。例如，若在F2单元格中输入公式“B2C2D2E2”后，将公式自动填充到F3单元格，将得到公式“B3C3D3E3”的值。即在对F3单元格填充公式时，公式中相对引用的单元格地址中的行号发生了变化，而列号不变。返 回11知识技能点1单元格引用（2）绝对引用绝对引用是在行号和列标前加一个“$”符号，其表示某一单元格在工作表中的绝对位置。采用绝对引用的单元格地址不会在公式位置发生变化时随之而改变。例如，在E8单元格中输入“=E4E5$E$6$E$7”，自动填充F8单元格时将得到公式“=F4F5$E$6$E$7”。因为，在对F8单元格填充公式时，由于公式中的单元格地址$E$6、$E$7行号和列标前引

12、用了“$”符号，即采用了单元格的绝对引用，所以单元格地址$E$6、$E$7不发生变化，而公式中的单元格地址E4、E5采用单元格的相对引用，所以其单元格地址会随着公式的填充而变化成“F4、F5”。（3）混合引用混合引用是相对地址与绝对地址的混合使用，例如：“E4”中，E（列号）是相对引用，4（行号）是绝对引用，这样引用引用使列号可能变化而行号保持不变。返 回12知识技能点2公式公式是在工作表中对数据进行分析的等式，由数字、单元格引用、函数及运算符等组成，它可以对工作表数值进行加、减和乘等运算。在公式中，可以引用同一工作表中的其他单元格、同一工作簿不同工作表中的单元格或者其他工作薄中工作表的单元格

13、参与公式运算。Excel2003包含三种类型的运算符：算术运算符，可以完成基本的数学运算，并产生数字结果。它们分别是、/、%。比较运算符，可以比较两个数值并产生逻辑值TRUE（成立）或FALSE（不成立）的结果。它们分别是、=、=、=。文本运算符，只有一个“&”，可以将一个或多个文本连接为一个组合文本。在Excel中输入公式通常以等号“=”开始，用以表示此时在单元格内输入的内容是一条公式。返 回13训练任务利用学生成绩管理报表，再增加10名学生，删除“总分”列，然后，重新计算。要求如下：1“平均分”为专业课 “计算机基础”和“C语言”占60%，基础课“政治”、“体育”、“英语”、“数学”占40%；2对20名学生排名次；3计算各科的平均分和最高分；4计算20名学生的成绩等级，规则如下：“优”：平均分90分；“良”：平均分80分，并且平均分90分；“中”：平均分70分，并且平均分80分；“及格”：平均分60分，并且平均分70分；“不及格”：平