第7章Excel综合应用1成绩表统计与分析课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第7章Excel综合应用1成绩表统计与分析,*,第7章Excel综合应用1成绩表统计与分析,2024/11/15,第7章Excel综合应用1成绩表统计与分析,第7章Excel综合应用1成绩表统计与分析2023/10/9,1,学习目标,熟练掌握统计函数,COUNT、COUNTIF,的使用方法,熟练掌握逻辑判断函数,IF,的使用方法,掌握条件格式的设置方法,熟练使用图表的创建、修改,2024/11/15,第7章Excel综合应用1成绩表统计与分析,2,学习目标熟练掌握统计函数COUNT、COUNTIF的使用方法,目录,7.1,成绩表统计案例分析,7.2 实现方法,7.2.1 用统计函数与公式制作“成绩统计表”,跨工作表的单元格引用,统计函数COUNTA及COUNT的使用,条件统计函数COUNTIF的使用,公式计算,7.2.2 用IF函数与条件格式制作“各科等级表”,逻辑判断函数IF的使用,条件格式,7.2.3,用图表向导制作成绩统计图,使用“图表向导”创建图表,修改图表,格式化图表,7.3,案例总结,7.4,课后练习,2024/11/15,第7章Excel综合应用1成绩表统计与分析,3,目录7.1 成绩表统计案例分析 2023/10/9第7章Ex,7.1成绩表统计案例分析,任务的提出,班主任王老师要制作“成绩统计表”、“成绩等级表”及“成绩统计图。,解决方案,2024/11/15,第7章Excel综合应用1成绩表统计与分析,4,7.1成绩表统计案例分析 任务的提出2023/10/9第7章,跨工作表的单元格引用,打开“第7章素材”文件夹下的“统计表（素材）.xls”文件，选择“成绩统计表”工作表。,从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到如右图所示的“成绩统计表”中的相应单元格中。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,5,跨工作表的单元格引用打开“第7章素材”文件夹下的“统计表（素,统计函数COUNTA及COUNT的使用,将“各科成绩表”中各门课程的“应考人数”和“参考人数”的统计结果放置到“成绩统计表”中的相应单元格中。,在“插入函数”对话框中，选择“统计”，在“选择函数”列表框中，选择“COUNTA”函数，单击“确定”按钮。,打开“函数参数”对话框。进行如下图所示的设置。,“COUNT”函数的设置类似（略）。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,6,统计函数COUNTA及COUNT的使用 将“各科成绩表”中各,条件统计函数COUNTIF的功能,COUNTIF函数的功能：,统计指定区域内满足给定条件的单元格数目。,语法格式：,COUNTIF(range,criteria),其中：Range 指定单元格区域，Criteria表示指定的条件表达式。,条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为 60、60、=90 或 缺考等。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,7,条件统计函数COUNTIF的功能 COUNTIF函数的功能：,条件统计函数COUNTIF的使用,用,COUNTIF,函数，将“各科成绩表”中各门课程的缺考人数以及各分数段人数的统计结果放置到“成绩统计表”中的相应单元格。,在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的函数为,=COUNTIF(各科成绩表!D2:D38,缺考,)。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,8,条件统计函数COUNTIF的使用用COUNTIF函数，将“各,各分数段人数的统计,条件,对应函数,90-100(人),=COUNTIF(各科成绩表!D2:D38,=90,80-89(人),=COUNTIF(各科成绩表!D2:D38,=80)-B9,70-79(人),=COUNTIF(各科成绩表!D2:D38,=70)-B9-B10,60-69(人),=COUNTIF(各科成绩表!D2:D38,=60)-B9-B10-B11,59以下(人),=COUNTIF(各科成绩表!D2:D38,=60)/COUNT(各科成绩表!D$2:D$38),或：1-B13/B7,计算优秀率：,=COUNTIF(各科成绩表!D$2:D$38,=90)/COUNT(各科成绩表!D$2:D$38),或：B9/B7,2024/11/15,第7章Excel综合应用1成绩表统计与分析,10,公式计算 在“成绩统计表”中，计算各门课程的及格率和优秀率,思考题（一）,如何引用同一工作簿中不同工作表的数据？,引用单元格的数据与复制粘贴数据有何区别？,统计函数COUNT与COUNTA的区别是什么？,条件统计函数COUNTIF的应用场合是什么？其中的两个参数各代表什么？,在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,11,思考题（一）如何引用同一工作簿中不同工作表的数据？2023/,单元格数据的删除与清除,将“各科成绩表”工作表复制一份，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中，清除“大学英语”、“计算机应用”、“高等数学”及“应用文写作”列中的分数内容，并清除“总分”、“名次”列的所有属性；删除“班级平均分”、“班级最高分”、“班级最低分”所在单元格区域。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,12,单元格数据的删除与清除 将“各科成绩表”工作表复制一份，并将,逻辑判断函数IF的功能,IF函数的功能是：,判断给出的条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。,语法格式：,IF(logical_test,value_if_true,value_if_false),共包括三个参数，其中：,Logical_test 逻辑判断表达式；,value_if_true 表达式为真时返回的值；,value_if_false 表达式为假时返回的值。,IF函数的逻辑判断方法,2024/11/15,第7章Excel综合应用1成绩表统计与分析,13,逻辑判断函数IF的功能 IF函数的功能是：IF函数的逻辑判断,逻辑判断函数IF的使用,利用If函数对“各科成绩表”中的“应用文写作”成绩在60分以上的，在“各科等级表”中“应用文写作”的对应位置设置为“及格”，否则为“不及格”。,选择目标单元格G2，在“函数参数”对话框中进行如下图所示的设置。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,14,逻辑判断函数IF的使用利用If函数对“各科成绩表”中的“应用,逻辑判断函数IF的使用（续）,按下表所示的分数与等级的对应关系，利用If嵌套函数对“各科成绩表”中的“大学英语”、“计算机应用”、“高等数学”三门课程的分数，在“各科等级表”中的对应科目中进行相应的等级设置。,分数,等级,缺考,缺考,90=分数,A,80=分数90,B,70=分数80,C,60=分数70,D,分数=90 优秀,90分数=80良好,80分数=70中等,70分数=60及格,分数=90,优秀,if(90A2=80,良好,if(80A2=70,中等,if(70A2=60,及格,if(A260,不及格,false),2024/11/15,第7章Excel综合应用1成绩表统计与分析,20,问题分析 在下图所示的单元格中，有“分数”、“等级”两列数据,制作各科成绩统计图,图表比数据更易于表达数据之间的关系以及数据变化的趋势。“成绩统计图”可以直观地表现各门课程各分数段人数。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,21,制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数,使用“图表向导”创建图表,在“成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下：,图表类型为“簇状柱形图”；,数据系列产生在“列”；,图表标题为“成绩统计图”；,分类（X）轴为“等级”；数值（Y）轴为“人数”；,将图表“作为其中的对象插入”到“成绩统计表”中。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,22,使用“图表向导”创建图表 在“成绩统计表”中，根据各分数段人,选择图表类型,在菜单栏中选择“插入”“图表”命令，打开“图表向导-4步骤之1-图表类型”对话框，进行如右图所示的设置。,单击“下一步”按钮,2024/11/15,第7章Excel综合应用1成绩表统计与分析,23,选择图表类型在菜单栏中选择“插入”“图表”命令，打开“图表,选择源数据,按住Ctrl键的同时，分别在当前工作表中选择不连续的两个区域，如下图所示，并在“图表向导-4步骤之2-图表源数据”对话框中，显示相应的设置。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,24,选择源数据按住Ctrl键的同时，分别在当前工作表中选择不连续,设置图表选项,单击“下一步”按钮，打开“图表向导-4步骤之3-图表选项”对话框，并进行如下图所示的设置。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,25,设置图表选项单击“下一步”按钮，打开“图表向导-4步骤之3-,选择图表位置,“下一步”按钮，打开“图表向导-4步骤之 4-图表位置”对话框，进行如下图所示的设置。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,26,选择图表位置“下一步”按钮，打开“图表向导-4步骤之 4-图,修改图表,在图表的制作过程中四步中的任何一步出错，都不必从头开始，只需单击图表，使图表处于激活状态，从“图表”菜单中，选择对应的命令即可；也可单击图表后，单击鼠标右键，从快捷菜单中，选择对应的命令。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,27,修改图表在图表的制作过程中四步中的任何一步出错，都不必从头开,修改图表要求,在“成绩统计表”工作表中，对图表进行如下修改：,将图表类型改为“三维簇状柱形图”；,数据系列产生在“行”，并从图表中删除缺考人数；,在图表中“显示数据表”；,将图表“作为新工作表插入”，并将新工作表命名为“成绩统计图”。,操作步骤略,2024/11/15,第7章Excel综合应用1成绩表统计与分析,28,修改图表要求 在“成绩统计表”工作表中，对图表进行如下修改：,图表的组成,2024/11/15,第7章Excel综合应用1成绩表统计与分析,29,图表的组成 2023/10/9第7章Excel综合应用1成绩,格式化图表,在“成绩统计图”工作表中，对图表的外观进行如下修饰：,将图表标题设置为“幼圆、22号、蓝色、加粗”；,将图表区的填充效果设置为“羊皮纸”；,将背景墙的填充效果设置为“水滴”；,为图例添加“阴影边框”。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,30,格式化图表 在“成绩统计图”工作表中，对图表的外观进行如下修,设置图表区的填充效果,在“图表”工具栏上的“图表对象”下拉列表中，选择“图表区”，单击“图表”工具栏上的“图表区格式”按钮，打开“图表区格式”对话框，,在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框，在“渐变”选项卡中，选择“预设”单选按钮，在“预设颜色”下拉列表中，选择“羊皮纸”选项，在“底纹样式”栏中，选择“中心辐射”单选项，单击“确定”按钮。“填充效果”对话框的设置，如右图所示。,2024/11/15,第7章Excel综合应用1成绩表统计与分析,31,设置图表区的填充效果在“图表”工具栏上的“图表对象”下拉列表,设置背景墙的填充效果,鼠标指向“背景墙”，单击鼠标右键，在弹出的快捷菜单中选择“背景墙格式”命令，打开“背景墙格式”对话框，在“图案”选项卡中，单击“填充效果”按钮，打开“填充效果”对话框。,选择“纹理”选