任务4.2 公式与函数——学生成绩表的计算（课件）-《信息技术》同步教学（电子工业出版社）

项目4 Excel 2016 电子表格 1 1 2 3 4 任务 Task 工作表的制作与美化 公式与函数 数据统计 数据分析 2 项目描述 当今社会，人们生活在数据的世界里，应用表格对数据进行处理是经常要做的事情。通过熟练掌握数据处理软件 Excel 2016 可以快速高效地制作电子表格，并对表格中的数据进行运算、统计和分析等，从而方便为管理者提供决策依据。 3 知识目标 Excel 2010基本操作 1 掌握 掌握 掌握 掌握 了解 工作表格式化，公式和函数的应用（重点） 2 工作表屏幕显示与打印 3 Excel 2010图表的使用 （重点） 4 数据管理与分析 （重点/难点） 5 4 能力目标 能够熟练应用 Excel 2016 制作和处理各种电子表格 具备灵活运用 Excel 2016 对数据进行收集及分析，并最终解决实际问题的能力 5 任务4.2 公式与函数——学生成绩表的计算 任务描述 知识拓展 任务要点 任务实施 能力拓展 辅导员让张晓雁同学对学生成绩册中的成绩进行计算与统计，结果如图4-31、4-32所示。 图 4-31  “学生成绩册”工作表的计算结果 操作要点 任务实施 任务小结 拓展训练 任务描述 6 任务4.2 公式与函数——学生成绩表的计算 任务描述 知识拓展 任务要点 任务实施 能力拓展 图 4-31  “学生成绩册”工作表的计算结果 操作要点 任务实施 任务小结 拓展训练 任务描述 7 任务4.2 公式与函数——学生成绩表的计算 任务要点 知识拓展 任务描述 任务实施 能力拓展 操作 任务实施 任务小结 拓展训练 任务描述 操作要点 8 公式的应用 单元格引用 插入函数 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.1 打开“学生成绩登记册 4-2.xlsx”工作簿 任务描述 任务实施 知识拓展 反思与改进 任务要点 9 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.2 计算总分和平均分 （1）总分录入：切换到“学生成绩册”工作表，单击 J4 单元格，在“开始”选项卡单击“编辑”|“自动求和”按钮，系统自动选中数值区域并填充公式，如图 4-33 所示。确认公式中所选择的单元格区域为 E4:I4，按 Enter 键得出结果。再次选中 J4 单元格，向下拖动复制柄到 J13 单元格。 （2）平均分录入：“学生成绩册”工作表单击 K4 单元格，在“开始”选项卡中单击“编辑”|“自动求和”右侧的下拉按钮，在弹出的菜单中选择“平均值”命令，然后将自动插入的求平均值公式中的 J4 改成 I4，如图 4-34 所示。按 Enter 键得出结果，然后再次选中 K4 单元格，向下拖动复制柄到 K13 单元格，结果如图 4-31 所示。 图 4-33 自动求和公式 图 4-34 求平均值公式 任务描述 任务实施 知识拓展 反思与改进 任务要点 10 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.3 在“学生成绩汇总”工作表中录入成绩 （1）切换到“学生成绩汇总”工作表，选择 E3 单元格，输入“=学生成绩册!K4”，如图 4-35 所示。按Enter键确认。 （2）再次选中 E3 单元格，向下拖动复制柄到 E12 单元格，结果如图 4-32 所示。 图 4-35 输入公式 任务描述 任务实施 知识拓展 反思与改进 任务要点 11 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.4 用公式计算总评成绩 （1）在 G3 单元格中输入“=E3*60%+F3*40%”，按Enter键，如图 4-36 所示。 图 4-15  “新建格式规则”对话框 （2）再次选择 G3 单元格，拖动复制柄到 G12 单元格，结果如图 4-32 所示。 任务描述 任务实施 知识拓展 反思与改进 任务要点 12 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.5 计算及奖学金 1. 计算排名 （1）选择单元格 H3，在“公式”选项卡中单击“函数库”|“插入函数”按钮，打开“插入函数”对话框，在“或选择类别”中选择“全部”，在“选择函数”列表框中选择“RANK”选项，打开“函数参数”对话框，如图 4-37 所示。 图 4-37  “插入函数”对话框 任务描述 任务实施 知识拓展 反思与改进 任务要点 13 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.5 计算及奖学金 1. 计算排名 （2）当光标位于“Number”框时，选择单元格 G3，再将光标移至“Ref”框，选定工作表区域 G3:G12，并将其修改为“G$3:G$12”，如图 4-38 所示，最后单击“确定”按钮，计算出该学生的排名。 （3）利用复制柄，填充其他学生的排名，结果如图 4-32 所示。 图 4-38  “函数参数”对话框 任务描述 任务实施 知识拓展 反思与改进 任务要点 14 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.5 计算及奖学金 2. 计算奖学金 一、二、三等奖学金分别为一人、两人和三人。 （1）在单元格 I3 中输入公式“=IF（H3<2，"一等"，IF（H3<4，"二等"，IF（H3<7，"三等"，" "）））”，计算出该学生是否获得奖学金。如果不满足条件，该单元格中不显示任何字符。 （2）利用复制柄，计算出其他学生获得奖学金情况，结果如图 4-32 所示。 任务描述 任务实施 知识拓展 反思与改进 任务要点 15 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.6 计算优秀人数，不及格成绩人数字段 （1）计算优秀人数字段：按照图 4-39 所示学生成绩分析的要求，选中 E19 单元格，输入“=COUNTIF（E3:E12，">=90"）”，按Enter键，向右复制公式至 G19 完成计算。 （2）计算不及格成绩人数字段：选中 E23 单元格，输入“=COUNTIF（E3:E12，"<60"）”，按Enter键，向右复制公式至 G23 完成计算。 图 4-39 学生成绩分析 任务描述 任务实施 知识拓展 反思与改进 任务要点 16 任务实施 知识拓展 任务描述 任务要点 能力拓展 4.2.7 计算良好人数字段 选中 E20 单元格，输入“=COUNTIF（E3:E12，">=80"）-E19”后按 Enter 键，向右复制公式至 G20 完成计算。 4.2.8 计算中等人数和及格成绩人数字段 （1）选中 E21 单元格，输入“=COUNTIFS（E3:E12，"<80"，E3:E12，">=70"）”，按Enter键，复制公式到 G21 单元格位置。 （2）单击 E22 单元格，输入“=COUNTIFS（E3:F12，"<70"，E3:E12，">=60"）”，按Enter键，复制公式至 G22 单元格位置。 （1）单击 E24 单元格，输入“=E19/COUNT（E3:E12）”，按 Enter 键，设置单元格格式中数字类型为百分比，并复制公式到 G24 单元格。 （2）单击 E25 单元格，输入“=MAX（E3:E12）”，按 Enter 键，并复制公式到 G25 单元格，结果如图4-32所示。 4.2.9 计算优秀率和最高分 任务描述 任务实施 知识拓展 反思与改进 任务要点 17 4.2.10 Excel 2016 公式简介 知识拓展 任务实施 任务描述 任务要点 能力拓展 在单元格中输入“=”表示进入公式编辑状态。 在 Excel 的公式中，可以使用运算符、单元格引用、值或常量、函数等几种元素。运算符是对公式中的元素进行特定类型的计算，一个运算符就是一个符号，如“+、−、*、/”等。 1. 常数类型 （1）数值型：直接输入数字，如=29。 （2）字符型：加引号表示字符型数据，如="abc"表示字符 abc，如果不加引号，则被认为是变量 abc。 （3）逻辑型：逻辑型常数只有两个分别为逻辑真和逻辑假，表示为 True 和 False。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 18 4.2.10 Excel 2016 公式简介 知识拓展 任务实施 任务描述 任务要点 能力拓展 2. 运算符和运算符优先级 （1）算术运算符：用来进行基本的数学运算，如“+、-、*、/、%”等。 （2）比较运算符：一般用在条件运算中，用于对两个数值进行比较，其计算结果为逻辑值，当计算结果为真时返回 True，否则返回 False。运算符号包括“=、>、>=、<、<=、<>”。 （3）连接运算符：使用连接运算符“&”连接一个或多个文本字符串形成一串文本。例如，需要将“FBHSJD”和“销售明细表”两个文本连接在一起，那么输入公式应为“=FBHSJD&销售明细表”。 （4）引用运算符：用来表示单元格在工作表中位置的坐标集，为计算公式指明引用的位置，包括“：”“，”“ ”（空格）。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 19 4.2.11 单元格的引用方式 知识拓展 任务实施 任务描述 任务要点 能力拓展 在 Excel 中，引用的关键在于标识单元格或单元格区域，Excel 中的引用包括相对引用、绝对引用、混合引用三种类型。 1. 相对引用 2. 绝对引用 3. 混合引用 4. 不同工作簿单元格的引用 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 20 4.2.12 输入公式 知识拓展 任务实施 任务描述 任务要点 能力拓展 在 Excel 工作表中输入的公式都是以“=”开始的，在输入“=”后，再输入单元格地址和运算符。输入公式的方法非常简单，与输入数据一样，可以在单元格中直接输入，也可以在编辑栏中进行编辑。 方法一：在单元格中直接输入公式。选择目标单元格，先输入“=”，再单击需要参与运算的单元格，输入运算符，然后单击参与运算的另一个单元格，即可完成公式的编辑。 方法二：通过编辑栏输入公式。选择目标结果单元格，在编辑栏中输入正确的公式，然后单击编辑栏左侧的“输入”按钮，或者按Enter键，即可得到计算的结果。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 21 4.2.13 复杂公式的使用 知识拓展 任务实施 任务描述 任务要点 能力拓展 算术运算符是通过从高到低的优先级进行计算的，如果需要改变运算顺序，则可在公式中使用括号，将需要先进行计算的公式用括号括起来，使其最先计算，从而得到正确结果。也就是说，在使用复杂公式时，需要注意的是算术符号的优先级。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 22 4.2.14 函数的类型与结构 知识拓展 任务实施 任务描述 任务要点 能力拓展 按使用函数计算应用的方面不同，Excel 将函数分为统计函数、财务函数、逻辑函数等 11 种类型。函数与公式一样，是以“=”开始的，其结构为“=函数名称（参数）”。 函数的结构分为函数名和参数两部分，其结构表达式为：函数名（参数 1，参数 2，参数 3，……）。其中函数名为需要执行运算函数的名称。参数为函数使用的单元格或者数值，它可以是数字、文本、数组、单元格区域的引用等。函数的参数中还可以包括其他函数，这就是函数的嵌套使用。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 23 4.2.15 插入函数 知识拓展 任务实施 任务描述 任务要点 能力拓展 要想使用函数来计算数据，首先需要在结果单元格中插入函数，并设置该函数的参数。 1. 通过对话框插入函数 2. 直接输入函数 3. 通过“自动求和”按钮插入函数 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 24 4.2.16 复制函数 知识拓展 任务实施 任务描述 任务要点 能力拓展 复制函数和复制数据的方法相同，可以通过快捷菜单中的命令进行复制操作，也可以使用复制柄来进行复制操作。 4.2.17 修改与删除函数 1. 修改函数 方法一：在单元格中修改。单击需要修改函数的单元格，如将 AVERAGE 函数修改为 MAX 函数，直接输入“=MAX（num1，num2…）”。 方法二：在编辑栏中对函数进行修改。如将 AVERAGE 函数修改为 SUM 函数，单击单元格，在编辑栏中可看到当前应用的函数，将其中的“AVERAGE”直接替换为“SUM”即可。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 25 4.2.17 修改与删除函数 知识拓展 任务实施 任务描述 任务要点 能力拓展 2. 删除函数 方法一：通过快捷菜单删除函数。选择需要删除函数的结果单元格并右击，选择“清除内容”命令。 方法二：使用功能区的“清除”功能。选择需要删除函数的结果单元格，在“开始”选项卡中单击“编辑”|“清除”按钮，然后在展开的下拉列表中选择“清除内容”命令。 方法三：通过键盘删除函数。选择需要删除函数的结果单元格，按 Delete 键或Backspace键可以删除函数。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 26 4.2.18 函数的参数和嵌套 知识拓展 任务实施 任务描述 任务要点 能力拓展 1. 以 IF 函数为例确定函数参数 IF 函数功能：根据对指定的条件计算结果为 True 或 False，来返回不同的结果，可用于对数值和公式执行条件检测。 语法：IF（Logical_test，Value_if_true，Value_if_false） 参数：Logical_test 参数表示计算结果为 True 或 False 的任意值或条件表达式。 Value_if_true 参数是 Logical_test 为 True 时的返回的值。 Value_if_false 参数是 Logical_test 为 False 时的返回的值。 条件表达式是把两个表达式用关系运算符（=，<>，>，<，>=，<=）连接起来构成的。 例 1：判断 A1 单元格中的成绩如果大于 60 分，在 B2 单元格中显示“及格”，否则显示为“不及格”，则在 B2 中输入公式“=IF（A1>=60，"及格"，"不及格"）”。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 27 4.2.18 函数的参数和嵌套 知识拓展 任务实施 任务描述 任务要点 能力拓展 2. 同样以 IF 函数为例讲解函数嵌套 例 2：在 Excel 中，如果 A1=B1=C1，则在 D1 中显示 1；如果它们不相等，则返回 0。 分析：条件为 A1=B1=C1，不可能用一个表达式表达出来，需要引入 AND()——与函数，可以将条件写为：AND（A1=B1，A1=C1），则在 D1 中输入如下函数：“=IF（AND（A1=B1，A1=C1），1，0）”。 也就是说 AND（A1=B1，A1=C1）函数作为 IF 函数的条件参数嵌套进了 IF 函数中。 同时，此公式也可以改为：“=IF（A1<>B1，0，IF（A1<>C1，0，1））”，在这个公式中 IF（A1<>C1，0，1）作为错误的返回值参数嵌套进了 IF 函数中。 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 28 4.2.19 常用函数 知识拓展 任务实施 任务描述 任务要点 能力拓展 1. 求和函数 SUM 2. 求平均值函数 AVERAGE 3. 求最大值函数 MAX 4. 求最小值函数 MIN 5. 计数函数 COUNT 6. 取整函数 INT 7. 四舍五入函数 ROUND 8. 取子串函数 （1）LEFT() （2）RIGHT() 教学整体设计 学生学习效果 特色与创新 反思与改进 教学实施过程 29 能力拓展 任务实施 任务描述 任务要点 知识拓展 1. 打开 4-2 拓展训练.xlsx，计算出应扣小计，实发工资。 2. 对数据进行分析，按实发工资进行排名，根据排名标识出“高”“中”“低”，效果图如图4-41所示。 图 4-41 拓展练习效果图 1 任务4.2 公式与函数——学生成绩表的计算 学生学习效果 特色与创新 教学整体设计 教学实施过程 反思与改进 30 能力拓展 任务实施 任务描述 任务要点 知识拓展 3. 统计出最高工资、最低工资、人均工资。 4. 统计出实发工资分段人数以及所占比例，效果图如图4-42所示。 图 4-42 拓展练习效果图 2 任务4.2 公式与函数——学生成绩表的计算 学生学习效果 特色与创新 教学整体设计 教学实施过程 反思与改进 31 Chart1 掌握知识 理解知识 熟悉知识 2.5 2.5 销售额 2.5 2.5 2.5 Sheet1 销售额 掌握知识 2.5 理解知识 2.5 熟悉知识 2.5 2.5 2.5 若要调整图表数据区域的大小，请拖拽区域的右下角。 $$