Office高级应用教程教案08Word文件下载.docx

1、内容要点教学方法与手段时间分配实例引入销售奖金表的实例需求与展示案例法5分钟实例制作使用公式知识讲授演示制作 10分钟使用sumif函数条件求和使用if函数统计销售级别使用rank函数进行排名使用vlookup函数统计奖金金额学生练习制作销售奖金表实验法35分钟作业检查与总结检查学生实例制作情况并打分总结总结法教学效果及改进思路一、实例引入为了对上个学期学生学习情况进行排名并进行奖学金评定，应用专业1班的班主任需要对同学的期末考试成绩进行统计与分析，要求如下：统计考试成绩的平均分。统计不同分数段的学生人数以及最高、最低平均分。效果如图8-1所示。使用学校规定的加权公式，计算每位同学必修课程的加

2、权平均成绩。按照德、智、体分数以2：7：1的比例计算每名学生的总评成绩，并进行排名。效果如图8-2所示。图8-1 计算平均分及分段统计效果图二、实例制作 Excel具有强大的计算功能，借助于其提供的丰富的公式和函数，可以大大方便对工作表中数据的分析和处理。本实例中对学生成绩的统计与分析就是一个典型的案例。需要注意的是：Excel中的公式遵循一个特定的语法，在输入公式或函数前必须先输入一个等号。8.2.1利用IF函数转换成绩IF函数是Excel中常用的函数之一。它是一个执行真假值判断的函数，根据逻辑计算的真假值，返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。IF函数语法为：IF（l

3、ogical_test,value_if_true,value_if_false）参数说明：Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。Value_if_true 是logical_test 为 TRUE 时返回的值。Value_if_false是 logical_test 为 FALSE 时返回的值。IF函数中包含IF函数的情况叫做IF函数的嵌套。利用IF函数将实训成绩由五级制转换为百分制，具体操作如下：1）打开实例7习题中的“学生成绩单”工作簿，并将Sheet1工作表重命名为“原始成绩数据”。2）按住Ctrl键的同时拖动工作表标签，创建该工作表的副

4、本，并将其重命名为“课程成绩”。3）在工作表“课程成绩”的“实训”列后添加列标题“实训成绩转换”。4）将光标移至I3单元格，并在其中输入公式“=if（h3=优,95,if（h3=良,85,if（h3=中,75,if（h3=及格,65,55）”，按Enter键，将序号为“1”的学生的实训成绩转换成百分制。5）将鼠标移到I3单元格右下角，当鼠标变成黑色实心指针时，按住鼠标左键向下拖动至I12单元格，松开鼠标，利用控制句柄，将其他学生的实训成绩转换成百分制，调整I列的列宽，如图8-3所示。图8-3 用if函数转换后效果图8.2.2 利用公式计算平均成绩公式是对单元格中的数据进行处理的等式，用于完成算

5、术、比较或逻辑等运算。Excel中的公式遵循一个特定的语法，即最前面是等于号，后面是运算数和运算符。每个运算数可以是数值、单元格区域的引用、标志、名称或函数。按照学校的计算公式，学生的平均成绩是由每门课的成绩乘以相对应的学分，加和之后除以总学分得到。具体操作如下：1）在单元格A15、B15中分别输入文本“课程名称”和“学分值”。2）选择D2：H2单元格区域，之后按CtrlC组合键，将其复制到剪贴板中。3）右击A16单元格，从快捷菜单中选择“选择性粘贴”命令，打开“选择性粘贴”对话框，选择“转置”复选框，如图8-4所示。单击“确定”按钮，将课程名称粘贴到单元格A16开始的列中的连续单元格区域，之

6、后将这些单元格的填充颜色去掉，并在其后相应的单元格中输入学分值。图8-4 “选择性粘贴”对话框4）在A21单元格中输入“总学分”，然后将光标置于单元格B21中，切换到“公式”选项卡，在“函数库”功能组中单击“自动求和”按钮，如图8-5所示。在B21单元格中显示“=SUM（B16:B20）”，按Enter键，得到总学分。图8-5 “自动求和”按钮5）选中单元格区域A15：B21，切换到“开始”选项卡，通过“字体”功能组中的“边框”按钮下拉列表中的“所有框线”命令，对此单元格区域添加边框。并设置单元格区域中文本内容“居中”对齐，结果如图8-6所示。图8-6 课程学分表6）单击J2单元格并在其中输入

7、文本“平均成绩”，按Enter键J3单元格将变成活动单元格，根据学生平均成绩计算公式，在J3单元格中输入公式“=（D3*$B$16+E3*$B$17+F3*$B$18+G3*$B$19+I3*$B$20）/$B$21”，按Enter键，计算出序号为“1”的学生的平均成绩。输入过程中可单击选中课程成绩、学分值所在的单元格，并将对所选单元格的相对引用改为绝对引用。（注：此处用了Excel中的相对引用与绝对引用，详见小结。）7）利用控制句柄，计算出所有学生的平均成绩。8）选中单元格区域A1：J1，两次单击“开始”“对齐方式”“合并后居中”按钮，实现表格标题的居中操作。9）选中单元格区域A2：J12，

8、单击“开始”“字体”“边框”按钮，选择“所有线框”命令为表格添加边框。10）选中单元格区域J3：J12，单击“字体”功能组右下角的对话框启动器按钮，打开“设置单元格格式”对话框，切换到“数字”选项卡，选择“分类”列表框中的“数值”选项，其他设置保持默认值，如图8-7所示。单击“确定”按钮，将平均成绩保留两位小数。图8-7 “设置单元格格式”对话框11）选中单元格区域D3：G12，在“开始”选项卡“样式”功能组中单击“条件格式”按钮，从下拉列表中选择“清除规则”“清除所选单元格的规则”命令，如图8-8所示。将考试成绩中的条件格式删除。图8-8 “清除规则”命令12）选中单元格区域A2：J12，单

9、击两次“对齐方式”中的“居中对齐”按钮，使表格内容居中。如图8-9所示。图8-9 表格内容格式化后效果图8.2.3 利用COUNTIF函数统计分段人数Countif函数是用来统计某个单元格区域中符合指定条件的单元格数目的一个函数。Countif函数的语法为：Countif（range，criteria）range 要计算其中非空单元格数目的区域（为了便于公式的复制，最好采用绝对引用）；criteria 以数字、表达式或文本形式定义的条件。分段统计考试成绩的人数及比例，有助于班主任开展工作。1）在D15开始的单元格区域建立统计分析表，之后为该区域添加边框、设置对齐方式，如图8-10所示。图8-1

10、0 分段统计表2）单击E17单元格，切换到“公式”选项卡，单击“插入函数”按钮，打开“插入函数”对话框，在“选择函数”列表中选择“COUNTIF”，如图8-11所示。单击“确定”按钮，打开“函数参数”对话框，将对话框中“Range”框内显示内容修改为“$J$3:$J$12”，接着在“Criteria”框中输入条件“=90”，如图8-12所示。单击“确定”按钮，统计出90分以上的人数。 图8-11 “插入函数”对话框 图8-12 设置COUNTIF函数参数3）利用填充句柄将E17单元格公式复制到E18单元格，并将公式中的“=90”改为“=80”并在公式后添加“COUNTIF（$J$3:$J$12

11、, “=90”）”，按Enter键，统计出平均分在80-89之间的人数。4）将E19、E20、E21单元格中的公式分别设置为：“=COUNTIF（$J$3:$J$12,=70）-COUNTIF（$J$3:=80）”、“=COUNTIF（$J$3:=60）-COUNTIF（$J$3:=7060）”，统计各分数段人数，并设置数值格式为整数。5）单击E22单元格，按AltEnter键，利用求和的快捷键求出总计。6）单击F17，在其中输入“E17/$E$22”，按Enter键统计出90分以上所占的比例。7）利用控制句柄，自动填充其他分数段的比例数据。8）选中单元格区域F17：F22，切换到“开始”选项

12、卡，在“数字”功能组中单击“数字格式”下拉按钮，从下拉列表中选择“百分比”选项。单击“确定”按钮，数值均以百分比形式显示。9）将光标移到E23单元格中，切换到“公式”选项卡，在“函数库”功能组中单击“自动求和”按钮下方的箭头按钮，在下拉列表中选择“最大值”命令，如图8-13所示。拖动鼠标选中平均成绩所在的单元格区域J3：J12，按Enter键计算出平均成绩最高分。 图8-13 “最大值”命令 图8-14 分段统计效果图10）用同样的方法在E24中求出最小值，设置对齐方式后，如图8-14所示。8.2.4 计算总评成绩学生的总评成绩是由德、智、体三方面的成绩以2：1的比例计算的。学生的德育分数是以

13、100分为基础，根据学生的出勤、参加集体活动、获奖等情况，以班级制定的加、减分规则积累获得。为了班级之间具有参照性，需要以班级德育分数最高的学生为100分，然后按比例换算得到其他同学的分数。1）打开素材中工作簿文件“学生学期总评.xlsx”。2）在“德育文体分数”工作表中，右击E列，从弹出的快捷菜单中选择“插入”命令，在“德育”列和“文体”列之间插入一个空列。3）单击E2单元格，输入文本“德育换算分数”，在E3单元格中输入公式“=D3/MAX（$D$3:$D$12）*100”，按Enter键，换算出该学生的德育换算分数。4）利用控制句柄，自动填充其他学生换算后的德育分数。5）双击“学生学期总评

14、”工作簿中的Sheet2工作表，将其重命名为“总评及排名”，并在A1单元格中输入文本“应用专业1班学生总评成绩及排名”。6）将工作表“德育文体分数”单元格区域A2：C12中的内容复制到工作表“总评及排名”中以A2单元格开始的区域。7）在“总评及排名”工作表的D2：H2单元格区域依次输入文本“德育”、“智育”、“文体”、“总评”、“排名”。8）选择“德育文体分数”工作表中的E3：E12单元格区域（即德育换算分数），按CrtlC键进行复制，切换到“总评及排名”工作表，右击D3单元格，在弹出的快捷菜单中选择粘贴选项中的“值”按钮，如图8-15所示。实现德育分数的复制。图8-15 粘贴选项9）选择“学

15、生成绩单”工作簿中“课程成绩”表中的单元格区域J3：J12，用同样的方法，将数值复制到“学生学期总评”工作簿“总评及排名”工作表的以E3单元格开始的区域。10）将工作表“德育文体分数”中“文体”分数复制到工作表“总评及排名”中以单元格F3开始的区域。11）在工作表“总评及排名”的G3单元格中输入公式“=D3*0.2+E3*0.7+F3*0.1”，按Enter键，计算出序号为“1”的学生的总评成绩。12）利用控制句柄，填充其他学生的总评成绩。8.2.5 利用RANK函数排名RANK函数的功能是返回某数字在一列数字中相对于其他数值的大小排位。RANK函数的语法：RANK（number, ref,

16、order）number是需要排名次的单元格名称或数值；ref是引用单元格（区域）；order是排名的方式，1为由小到大，即升序，0为由大到小，即降序。学生总评成绩出来之后就可以利用RANK函数对其进行排名了。1）切换到工作表“总评及排名”，选择H3单元格，单击“名称框”右侧的插入函数按钮。如图8-16所示。图8-16 “插入函数”按钮2）在弹出的“插入函数”对话框中选择函数“RANK”，单击“确定”按钮，如图8-17所示。弹出“函数参数”对话框。图8-17 “插入函数”对话框3）在“函数参数”对话框中分别输入各参数，当光标位于number参数框时，单击单元格G3选中序号为“1”的学生的总评成

17、绩；之后将光标移至ref参数框，选定工作表区域G3：G12，并按F4键将其引用方式修改为绝对引用；最后将光标移至order参数框，输入“0”。如图8-18所示。单击“确定”按钮，计算机出序号为“1”学生的排名。4）利用控制句柄填充其他学生的排名。5）将A1：H1单元格进行合并居中，并设置文本字体为“黑体”，20号字。6）选中单元格区域A2：H12，为此区域设置边框，并将文本对齐方式设置为“居中”。7）选中单元格区域“D3：G12”，为此区域设置数字格式，将数值保留两位小数。图8-18 “函数参数”对话框四、小结：本实例通过制作销售奖金表向学生讲解了excel中函数和公式的使用方法，主要公式的使用、常见函数的操作方法、单元格的相对引用和绝对引用、表格单单元格的引用等，函数操作部分为学生学习的难点，学生对参数的理解有一定的难度，需要多练习才能掌握。