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

1、第8章 excel综合应用1成绩表统计与分析22021-11-14学习目标学习目标熟练掌握统计函数count、countif的使用方法熟练掌握逻辑判断函数if的使用方法掌握条件格式的设置方法熟练使用图表的创建、修改第8章 excel综合应用1成绩表统计与分析32021-11-14目录目录8.1 成绩表统计案例分析 8.2 实现方法8.2.1 用统计函数与公式制作“成绩统计表”跨工作表的单元格引用跨工作表的单元格引用 统计函数统计函数countacounta及及countcount的使用的使用 条件统计函数条件统计函数countifcountif的使用的使用 公式计算公式计算 8.2.2 用if

2、函数与条件格式制作“各科等级表”逻辑判断函数逻辑判断函数ifif的使用的使用 条件格式条件格式 8.2.3用图表向导制作成绩统计图 使用使用“图表向导图表向导”创建图表创建图表 修改图表修改图表 格式化图表格式化图表 8.3 案例总结8.4 课后练习第8章 excel综合应用1成绩表统计与分析42021-11-148.1成绩表统计案例分析成绩表统计案例分析 任务的提出班主任王老师要制作 “成绩统计表”、“成绩等级表” 及“成绩统计图。解决方案第8章 excel综合应用1成绩表统计与分析52021-11-14跨工作表的单元格引用跨工作表的单元格引用打开“第8章素材”文件夹下的“统计表（素材）.x

3、ls”文件，选择“成绩统计表”工作表。从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到如右图所示的“成绩统计表”中的相应单元格中 。第8章 excel综合应用1成绩表统计与分析62021-11-14统计函数统计函数countacounta及及countcount的使用的使用 将“各科成绩表”中各门课程的“应考人数”和“参考人数”的统计结果放置到“成绩统计表”中的相应单元格中。在“插入函数”对话框中，选择“统计”，在“选择函数”列表框中，选择“counta”函数，单击“确定”按钮。打开“函数参数”对话框。进行如下图所示的设置。“count”函数的设置类似

4、（略）。第8章 excel综合应用1成绩表统计与分析72021-11-14条件统计函数条件统计函数countif的功能的功能 countif函数的功能：统计指定区域内满足给定条件的单元格数目。语法格式：countif(range,criteria)其中：range 指定单元格区域，criteria表示指定的条件表达式。条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为 60、60、=90 或 缺考等。第8章 excel综合应用1成绩表统计与分析82021-11-14条件统计函数条件统计函数countif的使用的使用用countif函数，将“各科成绩表”中各门课程的缺考人数以及各分数

5、段人数的统计结果放置到“成绩统计表”中的相应单元格 。在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的函数为 =countif(=countif(各科成绩表各科成绩表!d2:d38,!d2:d38,缺考缺考 )。 第8章 excel综合应用1成绩表统计与分析92021-11-14各分数段人数的统计各分数段人数的统计 条件对应函数90-100(人)=countif(各科成绩表各科成绩表!d2:d38,=90 80-89(人)=countif(各科成绩表各科成绩表!d2:d38,=80)- b9 70-79(人)=countif(各科成绩表各科成绩表!d2:d38,=70)-b9-b10

6、60-69(人)=countif(各科成绩表各科成绩表!d2:d38,=60)-b9-b10-b11 小于60(人)=countif(各科成绩表各科成绩表!d2:d38,=60)/count(各科成绩表!d$2:d$38) 或：或：1-b13/b7 1-b13/b7 计算优秀率 ：=countif(=countif(各科成绩表各科成绩表!d$2:d$38,=90)/count(!d$2:d$38,=90)/count(各科成绩各科成绩表表!d$2:d$38) !d$2:d$38) 或：或：b9/b7 b9/b7 第8章 excel综合应用1成绩表统计与分析112021-11-14思考题（一）思

7、考题（一）如何引用同一工作簿中不同工作表的数据？引用单元格的数据与复制粘贴数据有何区别？统计函数count与counta的区别是什么？条件统计函数countif的应用场合是什么？其中的两个参数各代表什么？在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。第8章 excel综合应用1成绩表统计与分析122021-11-14单元格数据的删除与清除单元格数据的删除与清除 将“各科成绩表”工作表复制一份，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中，清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列中的分数内容，并清除“总分”、“名次”列的所

8、有属性；删除“班级平均分”、“班级最高分”、“班级最低分”所在单元格区域 。第8章 excel综合应用1成绩表统计与分析132021-11-14逻辑判断函数逻辑判断函数if的功能的功能 if函数的功能是：判断给出的条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。语法格式：if(logical_test,value_if_true,value_if_false)共包括三个参数，其中：logical_test 逻辑判断表达式；value_if_true 表达式为真时返回的值；value_if_false 表达式为假时返回的值。if函数的逻辑判断方法函数的逻辑判断方法第8章 excel综

9、合应用1成绩表统计与分析142021-11-14逻辑判断函数逻辑判断函数if的使用的使用利用if函数对“各科成绩表”中的“应用文写作”成绩在60分以上的，在“各科等级表”中“应用文写作”的对应位置设置为“及格”，否则为“不及格” 。选择目标单元格g2 ，在“函数参数”对话框中进行如下图所示的设置。 第8章 excel综合应用1成绩表统计与分析152021-11-14逻辑判断函数逻辑判断函数if的使用（续）的使用（续）按下表所示的分数与等级的对应关系，利用if嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的等级设置 。

10、分数等级缺考缺考90=分数a80=分数90b70=分数80c60=分数70d分数=90 优秀90分数=80良好80分数=70中等70分数=60及格分数=90,优秀,if(90a2=80,良好,if(80a2=70,中等,if(70a2=60,及格,if(a260,不及格,false)第8章 excel综合应用1成绩表统计与分析212021-11-14制作各科成绩统计图制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数据变化的趋势。 “成绩统计图”可以直观地表现各门课程各分数段人数。第8章 excel综合应用1成绩表统计与分析222021-11-14使用使用“图表向导图表向导”创建图表

11、创建图表 在“成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下：图表类型为“簇状柱形图”；数据系列产生在“列”；图表标题为“成绩统计图”；分类（x）轴为“等级”；数值（y）轴为“人数”；将图表“作为其中的对象插入”到“成绩统计表”中 。第8章 excel综合应用1成绩表统计与分析232021-11-14选择图表类型选择图表类型在菜单栏中选择“插入”“图表”命令，打开“图表向导-4步骤之1-图表类型”对话框，进行如右图所示的设置。 单击“下一步”按钮 第8章 excel综合应用1成绩表统计与分析242021-11-14选择源数据选择源数据按住ctrl键的同时，分别在当前工作表中选择不

12、连续的两个区域，如下图所示，并在“图表向导-4步骤之2-图表源数据”对话框中，显示相应的设置。 第8章 excel综合应用1成绩表统计与分析252021-11-14设置图表选项设置图表选项单击“下一步”按钮，打开“图表向导-4步骤之3-图表选项”对话框，并进行如下图所示的设置。 第8章 excel综合应用1成绩表统计与分析262021-11-14选择图表位置选择图表位置“下一步”按钮，打开“图表向导-4步骤之 4-图表位置”对话框， 进行如下图所示的设置。第8章 excel综合应用1成绩表统计与分析272021-11-14修改图表修改图表在图表的制作过程中四步中的任何一步出错，都不必从头开始，

13、只需单击图表，使图表处于激活状态，从 “图表”菜单中，选择对应的命令即可；也可单击图表后，单击鼠标右键，从快捷菜单中，选择对应的命令。第8章 excel综合应用1成绩表统计与分析282021-11-14修改图表要求修改图表要求 在“成绩统计表”工作表中，对图表进行如下修改：将图表类型改为“三维簇状柱形图”；数据系列产生在“行”，并从图表中删除缺考人数；在图表中“显示数据表”；将图表“作为新工作表插入”，并将新工作表命名为“成绩统计图”。操作步骤略第8章 excel综合应用1成绩表统计与分析292021-11-14图表的组成图表的组成 第8章 excel综合应用1成绩表统计与分析302021-1

14、1-14格式化图表格式化图表 在“成绩统计图”工作表中，对图表的外观进行如下修饰：将图表标题设置为“幼圆、22号、蓝色、加粗”；将图表区的填充效果设置为“羊皮纸”；将背景墙的填充效果设置为“水滴”；为图例添加“阴影边框”。第8章 excel综合应用1成绩表统计与分析312021-11-14设置图表区的填充效果设置图表区的填充效果在“图表”工具栏上的“图表对象”下拉列表中，选择“图表区”，单击“图表”工具栏上的“图表区格式”按钮，打开“图表区格式”对话框，在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框，在“渐变”选项卡中，选择“预设”单选按钮，在“预设颜色”下拉列表中，选择“羊

15、皮纸”选项，在“底纹样式”栏中，选择“中心辐射”单选项，单击“确定”按钮。“填充效果”对话框的设置，如右图所示。 第8章 excel综合应用1成绩表统计与分析322021-11-14设置背景墙的填充效果设置背景墙的填充效果鼠标指向“背景墙”，单击鼠标右键，在弹出的快捷菜单中选择“背景墙格式”命令，打开“背景墙格式”对话框，在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框。选择“纹理”选项卡，在“纹理”列表框中，选择“水滴”，如右图所示，单击“确定”按钮。 第8章 excel综合应用1成绩表统计与分析332021-11-14思考题（三）思考题（三）选择图表的数据源时，如何选择不连

16、续的数据区域？如何修改图表？对已建立完成的图表，应如何添加“图表标题”？对图表进行格式化时，应该如何操作？将“图表区”的填充效果设置为“雨后初晴”，应如何操作？第8章 excel综合应用1成绩表统计与分析342021-11-14案例总结案例总结 本章主要介绍了：统计函数count、counta、countif；在使用在使用countifcountif函数时应该注意，当公式需要复制时，如果参数函数时应该注意，当公式需要复制时，如果参数“rangerange”的引用区域固定不变，通常应使用绝对引用或区域命名方式的引用区域固定不变，通常应使用绝对引用或区域命名方式实现；如果参数实现；如果参数“criteriacriteria”是表达式或字符串，必须在两边加上西是表达式或字符串，必须在两边加上西文双引号。文双引号。逻辑判断函数if以及嵌套函数的使用；当逻辑判断给出的条件多于两个时，通常采用当逻辑判断给出的条件多于两个时，通常采用ifif函数的嵌套。在使用函数的嵌套。在使用ifif函数时，应注意函数多层嵌套时的括号匹配，并且公式中的所有符函数时，应注意函数多层嵌套时的括号匹配，并且公式中的所有符号必须为西文字符。号必须为西文字符。 设置条件格式；图表的创建、修改及格式化特别要注意正确选择源数据。特别要注意正确选择源