计算机应用基础电子教案Excel综合应用PPT课件.pptx

第6章Excel综合应用 计算机基础 课程组 2020 3 18 1 6 1学生成绩处理6 1 1知识点解析 排位函数RANK EQ RANK EQ函数的功能是返回一个数字在数据区域中的排位 其排位大小与数据区域中的其他值相关 如果多个值具有相同的排位 则返回该组数值的最高排位 要计算B3单元格在B2 B10单元格区域中排在第几位 其对应的公式为 RANKD EQ B3 B3 B10 0 2020 3 18 2 6 1学生成绩处理6 1 1知识点解析 排位函数RANK EQ RANK EQ函数的语法格式为RANK EQ Number Ref Order 三个参数的含义分别是 Number 需要找到排位位置的数字或单元格 Ref 需要参与排位的数据区域 Ref中的非数值型值将被忽略 Order 如果order为0或省略 是按降序排位 否则为升序排位 函数RANK EQ对重复数的排位相同 但重复数的存在将影响后续数值的排位 例如B3和B6单元格的数值一样 它们对应的排位都为4 B9单元格的排位则为6 没有排位为5的数值 2020 3 18 3 6 1学生成绩处理6 1 1知识点解析 绝对引用 在计算出3单元格的排名后 通过公式复制计算其他人的排名时会发现 B6单元格的数值与B3单元格相等 而其排名却是2 查看C6单元格的公式 其公式为 RANK EQ B6 B6 B13 0 公式显示的是 B6单元格在B6 B13单元格区域中排在第几位 而实际上是要计算 B6单元格在B2 B10单元格区域中排在第几位 之所以出现错误 是因为在进行公式复制时 公式中的单元格引用是相对引用 2020 3 18 4 6 1学生成绩处理6 1 1知识点解析 绝对引用 如果希望公式中引用的单元格或单元格区域不随公式位置的变化而变化 可以使用绝对引用 绝对引用是指当把公式复制到其它单元格时 公式中引用的单元格或单元格区域中行和列的引用不会改变 绝对引用的单元格名称中其行和列之前均会加上 以示区分 如 A 4表示绝对引用A4单元格 因此 在C3单元格的编辑框中 选择 B3 B13 按下F4键 实现相对引用转换到绝对引用 B3 B13 变化为 B 3 B 10 然后进行公式复制时 就能得到正确的结果了 2020 3 18 5 6 1学生成绩处理6 1 1知识点解析 多条件统计函数COUNTIFS 如何统计游戏积分15000 20000 含15000 不含20000 的人数 可以转换为 统计B3 B10单元格区域中数值在15000 20000 含15000 不含20000 的单元格个数可以采用COUNTIFS函数来实现 2020 3 18 6 6 1学生成绩处理6 1 1知识点解析 多条件统计函数COUNTIFS 如何统计游戏积分15000 20000 含15000 不含20000 的人数 可以转换为 统计B3 B10单元格区域中数值在15000 20000 含15000 不含20000 的单元格个数可以采用COUNTIFS函数来实现 2020 3 18 7 6 1学生成绩处理6 1 1知识点解析 多条件统计函数COUNTIFS COUNTIFS函数语法格式为COUNTIFS criteria range1 criteria1 criteria range2 criteria2 其参数的含义分别是 criteria range1 为第一个需要计算其中满足某个条件的单元格数目的单元格区域 简称条件区域 criteria1 为第一个区域中将被计算在内的条件 简称条件 其形式可以为数字 表达式或文本 criteria range2为第二个条件区域 criteria2为第二个条件 依次类推 2020 3 18 8 6 1学生成绩处理6 1 1知识点解析 多条件统计函数COUNTIFS 对应的公式为 COUNTIFS B3 B10 15000 B3 B10 20000 2020 3 18 9 6 1学生成绩处理6 1 1知识点解析 复杂条件格式与混合引用 将排行榜 在C列 前3名的整行数据 从A列C列 都用 灰色背景 标识出来 2020 3 18 10 6 1学生成绩处理6 1 1知识点解析 复杂条件格式与混合引用 将排行榜 在C列 前3名的整行数据 从A列C列 都用 灰色背景 标识出来 需要对A3 C10单元格区域运用复杂条件格式 2020 3 18 11 6 1学生成绩处理6 1 1知识点解析 复杂条件格式与混合引用 第3行数据 A3 C3 的所有单元格对应的条件格式可以描述为 如果第3行中的第C列单元格中的数据 3 C3 3 那么对该单元格应用 灰色背景 格式 第4行数据 A4 C4 的所有单元格对应的条件格式可以描述为 如果第4行中的第C列单元格中的数据 3 C4 3 那么对该单元格应用 灰色背景 格式 从以上分析可以知道 对第3到第10行 A3 C10 的所有单元格对应的条件格式的公式为 C3 3 在该条件表达式中 地址 C3 为混合引用单元格地址 如在用条件 C3 3 对A3 C10的单元格进行条件格式判断时 A3 B3 C3 第3行 单元格的格式判断条件为 C3单元格的内容是否 3 在A8 B8 C8单元格 第8行 单元格的格式判断条件为 C8单元格的内容是否 3 2020 3 18 12 6 2学生成绩处理6 2 1任务实现 1 填写考勤记录 平时成绩 期中成绩及期末成绩 2 根据考勤成绩计算办法 考勤以100分计 迟到1次扣5分 请假1次扣10分 旷课1次扣20分 计算考勤成绩 3 根据总评成绩计算办法 总评成绩 考勤成绩 10 平时成绩 20 期中成绩 20 期末成绩 50 成绩四舍五入 旷考计为旷考 计算总评成绩 4 将总评成绩转换为总评等级 总评成绩 90分为A 80分为B 70分为C 60分为D 60分以下为E 旷考为F 5 统计每个分数段的人数 2020 3 18 13 6 2学生成绩处理6 2 1任务实现 请参照书上步骤 自行完成 上 下页边距为3cm 左 右页边距为2cm 纸张采用B5纸横向打印 打印的表格在页面中采用水平居中 总评成绩分段人数统计信息不打印 如果成绩单需要分多页打印 则每页需要打印标题 第1行到第4行 信息 2020 3 18 14 6 3学生成绩处理6 3 1总结与提高 1 IF函数嵌套 如果O5单元格的值为64公式 IF O5 60 E IF O5 70 D IF O5 80 C IF O5 90 B A 的执行结果为多少 公式 IF O5 60 E IF 60 O5 70 D IF 70 O5 80 C IF 80 O5 90 B A 的执行结果为多少 AD 2020 3 18 15 6 3学生成绩处理6 3 3总结与提高 1 IF函数嵌套 为防止出现条件交叉包含 在我们进行多条件嵌套时 1 条件判断要么从最大到最小 要么从最小到最大 不要出现大小交叉情况 2 如果条件判断为从大到小 通常使用的比较运算符为大于 或大于等于 2 如果条件判断为从小到大 通常使用的比较运算符为小于 或小于等于 2020 3 18 16 6 3学生成绩处理6 3 1总结与提高 2 用IF函数防止异常结果出现 如果某个同学迟到了10次 旷课了3次 请问他的考勤成绩是多少 与现实相符吗 怎么解决 判断 考勤成绩 是不是小于0 若是 考勤成绩 计为0分 否则计为计算出来的成绩 IF 100 COUNTIF C5 J5 迟到 5 COUNTIF C5 J5 请假 10 COUNTIF C5 J5 旷课 20 0 100 COUNTIF C5 J5 迟到 5 COUNTIF C5 J5 请假 10 COUNTIF C5 J5 旷课 20 0 2020 3 18 17 6 3学生成绩处理6 3 1总结与提高 3 排位函数RANK 除了可以使用RANK EQ函数来排名外 也可以使用RANK函数 RANK函数返回一个数字在数字列表中的排位 RANK函数是在Excel2007版本及之前的版本中提供的函数 在Excel2010版本中 提供了RANK EQ等新函数来替代它 而Excel2010版本中仍提供RANK函数是为了保持与Excel早期版本的兼容性 2020 3 18 18 6 3学生成绩处理6 3 1总结与提高 4 COUNTIFS函数 统计 全勤 并且总评成绩为80 89的学生数 统计 考勤成绩 100 并且总评成绩为80 89的学生数 1 O5 O32单元格区域中单元格的值 80 2 O5 O32单元格区域中单元格的值 80 O5 O32 90 K5 K32 100 2020 3 18 19 6