1 / 35

第八章 Excel 综合应用 1 ---- 成绩表

第八章 Excel 综合应用 1 ---- 成绩表. 焦作大学信息工程学院 计算机基础教研室. 学习目标. 熟练掌握统计函数 COUNT 、 COUNTIF 的使用方法 熟练掌握逻辑判断函数 IF 的使用方法 掌握条件格式的设置方法 熟练使用图表的创建、修改. 目录. 8.1 成绩表统计案例分析 8.2 实现方法 8.2.1 用统计函数与公式制作 “ 成绩统计表 ” 跨工作表的单元格引用 统计函数 COUNTA 及 COUNT 的使用 条件统计函数 COUNTIF 的使用 公式计算

connor-finch
Download Presentation

第八章 Excel 综合应用 1 ---- 成绩表

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第八章 Excel综合应用1----成绩表 焦作大学信息工程学院 计算机基础教研室

  2. 学习目标 • 熟练掌握统计函数COUNT、COUNTIF的使用方法 • 熟练掌握逻辑判断函数IF的使用方法 • 掌握条件格式的设置方法 • 熟练使用图表的创建、修改 第8章 Excel综合应用1—成绩表统计与分析

  3. 目录 8.1 成绩表统计案例分析 8.2 实现方法 8.2.1 用统计函数与公式制作“成绩统计表” 跨工作表的单元格引用 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF的使用 公式计算 8.2.2 用IF函数与条件格式制作“各科等级表” 逻辑判断函数IF的使用 条件格式 8.2.3用图表向导制作成绩统计图 使用“图表向导”创建图表 修改图表 格式化图表 8.3 案例总结 8.4 课后练习 第8章 Excel综合应用1—成绩表统计与分析

  4. 8.1成绩表统计案例分析 • 任务的提出 班主任王老师要制作 “成绩统计表”、“成绩等级表” 及“成绩统计图。 • 解决方案 第8章 Excel综合应用1—成绩表统计与分析

  5. 跨工作表的单元格引用 • 打开“第8章素材”文件夹下的“统计表（素材）.xls”文件，选择“成绩统计表”工作表。 • 从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到如右图所示的“成绩统计表”中的相应单元格中 。 第8章 Excel综合应用1—成绩表统计与分析

  6. 统计函数COUNTA及COUNT的使用 • 将“各科成绩表”中各门课程的“应考人数”和“参考人数”的统计结果放置到“成绩统计表”中的相应单元格中。 • 在“插入函数”对话框中，选择“统计”，在“选择函数”列表框中，选择“COUNTA”函数，单击“确定”按钮。 • 打开“函数参数”对话框。进行如下图所示的设置。 • “COUNT”函数的设置类似（略）。 第8章 Excel综合应用1—成绩表统计与分析

  7. 条件统计函数COUNTIF的功能 • COUNTIF函数的功能： 统计指定区域内满足给定条件的单元格数目。 • 语法格式： COUNTIF(range,criteria) • 其中：Range 指定单元格区域，Criteria表示指定的条件表达式。 • 条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为 60、"60"、">=90" 或 "缺考"等。 第8章 Excel综合应用1—成绩表统计与分析

  8. 条件统计函数COUNTIF的使用 • 用COUNTIF函数，将“各科成绩表”中各门课程的缺考人数以及各分数段人数的统计结果放置到“成绩统计表”中的相应单元格 。 • 在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的函数为 =COUNTIF(各科成绩表!D2:D38,"缺考")。 第8章 Excel综合应用1—成绩表统计与分析

  9. 各分数段人数的统计 第8章 Excel综合应用1—成绩表统计与分析

  10. 公式计算 • 在“成绩统计表”中，计算各门课程的及格率和优秀率 。 • 计算及格率 ： COUNTIF(各科成绩表!D$2:D$38,">=60")/COUNT(各科成绩表!D$2:D$38) 或：1-B13/B7 • 计算优秀率 ： =COUNTIF(各科成绩表!D$2:D$38,">=90")/COUNT(各科成绩表!D$2:D$38) 或：B9/B7 第8章 Excel综合应用1—成绩表统计与分析

  11. 思考题（一） • 如何引用同一工作簿中不同工作表的数据？ • 引用单元格的数据与复制粘贴数据有何区别？ • 统计函数COUNT与COUNTA的区别是什么？ • 条件统计函数COUNTIF的应用场合是什么？其中的两个参数各代表什么？ • 在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。 第8章 Excel综合应用1—成绩表统计与分析

  12. 单元格数据的删除与清除 • 将“各科成绩表”工作表复制一份，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中，清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列中的分数内容，并清除“总分”、“名次”列的所有属性；删除“班级平均分”、“班级最高分”、“班级最低分”所在单元格区域 。 第8章 Excel综合应用1—成绩表统计与分析

  13. 逻辑判断函数IF的功能 • IF函数的功能是： • 判断给出的条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。 • 语法格式： • IF(logical_test,value_if_true,value_if_false) • 共包括三个参数，其中： • Logical_test 逻辑判断表达式； • value_if_true 表达式为真时返回的值； • value_if_false 表达式为假时返回的值。 IF函数的逻辑判断方法 第8章 Excel综合应用1—成绩表统计与分析

  14. 逻辑判断函数IF的使用 • 利用If函数对“各科成绩表”中的“应用文写作”成绩在60分以上的，在“各科等级表”中“应用文写作”的对应位置设置为“及格”，否则为“不及格” 。 • 选择目标单元格G2 ，在“函数参数”对话框中进行如下图所示的设置。 第8章 Excel综合应用1—成绩表统计与分析

  15. 按下表所示的分数与等级的对应关系，利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的等级设置 。 逻辑判断函数IF的使用（续） 第8章 Excel综合应用1—成绩表统计与分析

  16. 按下表所示的分数与等级的对应关系，利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的等级设置 。 逻辑判断函数IF的使用（续） 第8章 Excel综合应用1—成绩表统计与分析

  17. 条件格式 • 在“各科等级表”中，利用条件格式将所有“不及格”或所有等级为“E”的单元格设置成“黄色底纹红色 加 粗字体”；将所有“缺考”的单元格设置为“浅青绿底纹梅红色 加 粗字体” 。 在菜单栏中选择“格式”→“条件格式”命令，打开“条件格式”对话框 ，进行如下图所示的设置。 第8章 Excel综合应用1—成绩表统计与分析

  18. 条件格式（续） • 在“各科成绩表”中，将“标题”行及“班级平均分”行之间（不包括这两行）的偶数行数据区域设置为“冰蓝色底纹深蓝色字体” 。 在菜单栏中选择“格式”→“条件格式”命令，打开“条件格式”对话框 ，进行如下图所示的设置。 第8章 Excel综合应用1—成绩表统计与分析

  19. 思考题（二） • 对单元格数据的清除和删除操作有何区别？ • IF函数有几个参数，每个参数的意义是什么？ • IF函数最多可以有几层嵌套？ • 如何设置条件格式？ 第8章 Excel综合应用1—成绩表统计与分析

  20. 问题分析 • 在下图所示的单元格中，有“分数”、“等级”两列数据，其中“等级”数据是根据“分数”按以下条件计算所得： 条件 分数 等级 分数>=90 优秀 90>分数>=80 良好 80>分数>=70 中等 70>分数>=60 及格 分数<60 不及格 • 请分析以下表达式能否根据给定条件求出正确的结果？如果有错，请说明出错原因和改正方法。 =if(A2>=90,优秀,if(90>A2>=80,良好,if(80>A2>=70,中等,if(70>A2>=60,及格,if(A2<60,不及格,false))))) 第8章 Excel综合应用1—成绩表统计与分析

  21. 制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数据变化的趋势。 “成绩统计图”可以直观地表现各门课程各分数段人数。 第8章 Excel综合应用1—成绩表统计与分析

  22. 使用“图表向导”创建图表 • 在“成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下： • 图表类型为“簇状柱形图”； • 数据系列产生在“列”； • 图表标题为“成绩统计图”； • 分类（X）轴为“等级”；数值（Y）轴为“人数”； • 将图表“作为其中的对象插入”到“成绩统计表”中 。 第8章 Excel综合应用1—成绩表统计与分析

  23. 选择图表类型 • 在菜单栏中选择“插入”→“图表”命令，打开“图表向导-4步骤之1-图表类型”对话框，进行如右图所示的设置。 • 单击“下一步”按钮 第8章 Excel综合应用1—成绩表统计与分析

  24. 选择源数据 • 按住Ctrl键的同时，分别在当前工作表中选择不连续的两个区域，如下图所示，并在“图表向导-4步骤之2-图表源数据”对话框中，显示相应的设置。 第8章 Excel综合应用1—成绩表统计与分析

  25. 设置图表选项 • 单击“下一步”按钮，打开“图表向导-4步骤之3-图表选项”对话框，并进行如下图所示的设置。 第8章 Excel综合应用1—成绩表统计与分析

  26. 选择图表位置 • “下一步”按钮，打开“图表向导-4步骤之 4-图表位置”对话框， 进行如下图所示的设置。 第8章 Excel综合应用1—成绩表统计与分析

  27. 修改图表 • 在图表的制作过程中四步中的任何一步出错，都不必从头开始，只需单击图表，使图表处于激活状态，从 “图表”菜单中，选择对应的命令即可；也可单击图表后，单击鼠标右键，从快捷菜单中，选择对应的命令。 第8章 Excel综合应用1—成绩表统计与分析

  28. 修改图表要求 • 在“成绩统计表”工作表中，对图表进行如下修改： • 将图表类型改为“三维簇状柱形图”； • 数据系列产生在“行”，并从图表中删除缺考人数； • 在图表中“显示数据表”； • 将图表“作为新工作表插入”，并将新工作表命名为“成绩统计图”。 • 操作步骤略 第8章 Excel综合应用1—成绩表统计与分析

  29. 图表的组成 第8章 Excel综合应用1—成绩表统计与分析

  30. 格式化图表 • 在“成绩统计图”工作表中，对图表的外观进行如下修饰： • 将图表标题设置为“幼圆、22号、蓝色、加粗”； • 将图表区的填充效果设置为“羊皮纸”； • 将背景墙的填充效果设置为“水滴”； • 为图例添加“阴影边框”。 第8章 Excel综合应用1—成绩表统计与分析

  31. 设置图表区的填充效果 • 在“图表”工具栏上的“图表对象”下拉列表中，选择“图表区”，单击“图表”工具栏上的“图表区格式”按钮，打开“图表区格式”对话框， • 在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框，在“渐变”选项卡中，选择“预设”单选按钮，在“预设颜色”下拉列表中，选择“羊皮纸”选项，在“底纹样式”栏中，选择“中心辐射”单选项，单击“确定”按钮。“填充效果”对话框的设置，如右图所示。 第8章 Excel综合应用1—成绩表统计与分析

  32. 设置背景墙的填充效果 • 鼠标指向“背景墙”，单击鼠标右键，在弹出的快捷菜单中选择“背景墙格式”命令，打开“背景墙格式”对话框，在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框。 • 选择“纹理”选项卡，在“纹理”列表框中，选择“水滴”，如右图所示，单击“确定”按钮。 第8章 Excel综合应用1—成绩表统计与分析

  33. 思考题（三） • 选择图表的数据源时，如何选择不连续的数据区域？ • 如何修改图表？ • 对已建立完成的图表，应如何添加“图表标题”？ • 对图表进行格式化时，应该如何操作？ • 将“图表区”的填充效果设置为“雨后初晴”，应如何操作？ 第8章 Excel综合应用1—成绩表统计与分析

  34. 案例总结 本章主要介绍了： • 统计函数COUNT、COUNTA、COUNTIF； • 在使用COUNTIF函数时应该注意，当公式需要复制时，如果参数“Range”的引用区域固定不变，通常应使用绝对引用或区域命名方式实现；如果参数“Criteria”是表达式或字符串，必须在两边加上西文双引号。 • 逻辑判断函数IF以及嵌套函数的使用； • 当逻辑判断给出的条件多于两个时，通常采用IF函数的嵌套。在使用IF函数时，应注意函数多层嵌套时的括号匹配，并且公式中的所有符号必须为西文字符。 • 设置条件格式； • 图表的创建、修改及格式化 • 特别要注意正确选择源数据。 • 在图表的制作过程中，“图表类型”、“源数据”、“图表选项”、“位置”四步中的任何一步出错都不必重新开始，只要选定图表使之处于激活状态，就可以在“图表”菜单中选择对应的命令进行修改。 第8章 Excel综合应用1—成绩表统计与分析

  35. 课后练习 打开“工资表(素材).xls”，并另存为“工资表.xls”，参见教材，完成对工资表的数据计算及图表绘制。 第8章 Excel综合应用1—成绩表统计与分析

More Related