Excel综合应用-成绩表统计与分析

计算机应用根底学习目标熟练掌握统计函数COUNT、COUNTIF及COUNTIFS的使用方法熟练掌握逻辑判断函数IF的使用方法掌握条件格式的设置方法熟练使用图表的创立、修改及格式化2024/1/13第8章Excel综合应用1—成绩表统计与分析2目录8.1成绩表统计案例分析8.2实现方法8.2.1用统计函数与公式制作“成绩统计表〞跨工作表的单元格引用统计函数COUNTA及COUNT的使用条件统计函数COUNTIF与COUNTIFS的使用公式计算8.2.2用IF函数与条件格式制作“各科等级表〞单元格数据的去除与删除逻辑判断函数IF的使用条件格式用图表向导制作成绩统计图创立图表修改图表格式化图表8.3案例总结8.4课后练习2024/1/13第8章Excel综合应用1—成绩表统计与分析38.1成绩表统计案例分析任务的提出班主任王老师要制作“成绩统计表〞、“成绩等级表〞及“成绩统计图。解决方案

2024/1/13第8章Excel综合应用1—成绩表统计与分析4跨工作表的单元格引用翻开“统计表〔素材〕.xlsx〞文件，选择“成绩统计表〞工作表。从“各科成绩表〞中，将四门课程的“班级平均分〞、“班级最高分〞和“班级最低分〞的数据引用到“成绩统计表〞中的相应单元格中。2024/1/13第8章Excel综合应用1—成绩表统计与分析5统计函数COUNT及COUNTA的使用

将“各科成绩表〞中各门课程的“参考人数〞和“应考人数〞的统计结果放置到“成绩统计表〞中的相应单元格中。在“开始〞选项卡的“编辑〞组中单击“自动求和〞按钮旁边的下拉箭头，在弹出的列表中选择“计数〞选项。可计算出“参考人数〞。在“公式〞选项卡的“函数库〞组中单击“其他函数〞按钮，在下拉列表中选择“统计〞选项，在弹出的下拉列表框中选择“COUNTA〞。可计算出“应考人数〞。2024/1/13第8章Excel综合应用1—成绩表统计与分析6条件统计函数COUNTIF的功能COUNTIF函数的功能：统计指定区域内满足给定条件的单元格数目。语法格式：COUNTIF(range,criteria)其中：Range指定单元格区域，Criteria表示指定的条件表达式。条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为60、"60"、">=90"或"缺考"等。2024/1/13第8章Excel综合应用1—成绩表统计与分析7条件统计函数COUNTIF的使用在“成绩统计表〞的相应单元格中，用COUNTIF函数统计“各科成绩表〞中4门课程的“缺考〞人数、“90-100〞以及“小于60〞分数段的人数。在“函数参数〞对话框中，进行如以下图所示的设置：编辑栏中的函数为=COUNTIF(各科成绩表!D2:D38,"缺考")。用相同方法可以统计出“90-100〞以及“小于60〞分数段的人数。2024/1/13第8章Excel综合应用1—成绩表统计与分析8条件统计函数COUNTIFS的功能COUNTIFS函数的功能是：

将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。语法格式：COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2]…)其中：Criteria_range1必需。在其中计算关联条件的第一个区域，Criteria1必需。条件表达式；Criteria_range2,Criteria2,...可选。附加的区域及其关联条件。最多允许127个区域/条件对。2024/1/13第8章Excel综合应用1—成绩表统计与分析9条件统计函数COUNTIFS的使用在“成绩统计表〞的相应单元格中，用COUNTIFS函数统计“各科成绩表〞中4门课程的“80-89〞、“70-79〞以及“60-69〞分数段的人数。在翻开的“函数参数〞对话框中填写上两组单元格范围和条件，如以下图所示，单击“确定〞按钮。用相同方法可以统计出“70-79〞以及“60-69〞分数段的人数。2024/1/13第8章Excel综合应用1—成绩表统计与分析10公式计算在“成绩统计表〞中，计算各门课程的优秀率和及格率。计算优秀率：=COUNTIF(各科成绩表!D$2:D$38,">=90")/COUNT(各科成绩表!D$2:D$38)或：=B9/B6计算及格率：=COUNTIF(各科成绩表!D$2:D$38,">=60")/COUNT(各科成绩表!D$2:D$38)或：=1-B13/B62024/1/13第8章Excel综合应用1—成绩表统计与分析11思考题〔一〕如何引用同一工作簿中不同工作表的数据？引用单元格的数据与复制粘贴数据有何区别？统计函数COUNT与COUNTA的区别是什么？条件统计函数COUNTIF和COUNTIFS的应用场合是分别什么？在“各科成绩表〞工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。2024/1/13第8章Excel综合应用1—成绩表统计与分析12单元格数据的删除与去除复制“各科成绩表〞工作表，并将复制后的工作表更名为“各科等级表〞。在“各科等级表〞中，去除4门课程列中的分数内容；去除“总分〞列的所有属性；删除分数统计所在单元格区域。按Delete键，只去除选定单元格的内容，而保存了单元格的格式。在“开始〞选项卡的“编辑〞组中单击“去除〞按钮，在弹出的列表中选择“全部去除〞选项。此时单元格中的内容和格式全部被去除。在“开始〞选项卡的“单元格〞组中单击“删除〞按钮，这时单元格区域的内容会由其他内容替代。2024/1/13第8章Excel综合应用1—成绩表统计与分析13逻辑判断函数IF的功能IF函数的功能是：判断给出的条件是否满足，如果满足返回一个值，如果不满足那么返回另一个值。语法格式：IF(logical_test,value_if_true,value_if_false)共包括三个参数，其中：Logical_test逻辑判断表达式；value_if_true表达式为真时返回的值；value_if_false表达式为假时返回的值。2024/1/13第8章Excel综合应用1—成绩表统计与分析14逻辑判断函数IF的使用在“各科等级表〞的“应用文写作〞列中，利用IF函数引用“各科成绩表〞中“应用文写作〞列的对应分数进行逻辑判断，如果分数在60分以上的返回“及格〞，否那么返回“不及格〞。选择目标单元格G2，在“函数参数〞对话框中进行如以下图所示的设置。2024/1/13第8章Excel综合应用1—成绩表统计与分析15逻辑判断函数IF的使用〔二〕在“各科等级表〞的“应用文写作〞列中，利用IF函数再添加一个条件：对“各科成绩表〞的“应用文写作〞列中成绩为“缺考〞的考生，在对应位置仍设置为“缺考〞。在“各科等级表〞中选择目标单元格G2，选择除“=〞以外的其他内容，按快捷键Ctrl+X。在对话框中进行如以下图所示的设置。其中在“Value_if_false〞编辑框中，将插入点定位后，按快捷键Ctrl+V。2024/1/13第8章Excel综合应用1—成绩表统计与分析16逻辑判断函数IF的使用〔三〕分数等级缺考缺考分数>=90A90>分数>=80B80>分数>=70C70>分数>=60D分数<60E2024/1/13第8章Excel综合应用1—成绩表统计与分析17在“各科等级表〞的“大学英语〞、“计算机应用〞、“高等数学〞列中，利用IF嵌套函数，引用“各科成绩表〞中对应的3门课程的分数，按下表中分数与成绩等级的对应关系，在相应的科目中进行成绩等级设置。逻辑判断函数IF的使用〔四〕〔1〕在目标单元格D2中输入“=〞。〔2〕单击“名称框〞处的“IF〞，翻开“函数参数〞对话框。〔3〕在“函数参数〞对话框中进行如以下图所示的设置。并将插入点定位在第3个参数“Value_if_false〞编辑框中。〔4〕再次单击“名称框〞处的“IF〞。第2次翻开“函数参数〞对话框，在“Logical_test〞处输入“各科成绩表!D2<70〞、在“Value_if_true〞处，输入“D〞。再将插入点定位在第3个参数“Value_if_false〞编辑框中。〔5〕重复步骤〔4〕共3次，注意修改每次的参数。2024/1/13第8章Excel综合应用1—成绩表统计与分析18条件格式〔一〕2024/1/13第8章Excel综合应用1—成绩表统计与分析19在“各科等级表〞中，利用条件格式将4门课程中所有“缺考〞的单元格设置为“浅红填充色深红色文本〞；选择目标单元格区域D2:G38在“开始〞选项卡的“样式〞组中单击“条件格式〞按钮，在弹出的列表中选择“突出显示单元格规那么〞选项，在弹出的下级列表中选择“等于〞选项。在翻开的“等于〞对话框中，进行如以下图所示的设置。条件格式〔二〕2024/1/13第8章Excel综合应用1—成绩表统计与分析20在“各科等级表〞中，利用条件格式将4门课程中所有成绩等级为“不及格〞或为“E〞的单元格设置成“黄色底纹红色加粗字体〞。选择目标单元格区域D2:G38单击“条件格式〞按钮，在弹出的列表中选择“管理规那么〞选项，翻开“条件格式规那么管理器〞对话框。在对话框中单击“新建规那么〞按钮。在“新建格式规那么〞对话框中进行如右图所示的设置。重复以上步骤，将所有成绩等级为“E〞的单元格进行相同的设置。条件格式〔三〕2024/1/13第8章Excel综合应用1—成绩表统计与分析21在“各科成绩表〞中，用条件格式中的图标集形象化地表现“高等数学〞列的数据。对于分数大于等于85的用“〞表示、分数小于85并且大于等于60的用“！〞表示、分数小于60的用“×〞表示。选择目标单元格区域F2:F38。单击“条件格式〞按钮，在弹出的列表中选择“图标集〞选项，在弹出的下级列表中选择“标记〞区域中的“三个符号〔无圆圈〕〞选项。在“条件格式〞下拉列表中选择“管理规那么〞选项，翻开“条件格式规那么管理器〞对话框，单击“编辑规那么〞按钮。在“编辑格式规那么〞对话框中，进行如右图所示的设置。思考题〔二〕对单元格数据的去除和删除操作有何区别？IF函数有几个参数，每个参数的意义是什么？如何设置条件格式？2024/1/13第8章Excel综合应用1—成绩表统计与分析22问题分析在以下图所示的单元格中，有“分数〞、“等级〞两列数据，其中“等级〞数据是根据“分数〞按以下条件计算所得： 条件 分数 等级 分数>=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)))))2024/1/13第8章Excel综合应用1—成绩表统计与分析23制作各科成绩统计图图表比数据更易于表达数据之间的关系以及数据变化的趋势。“成绩统计图〞可以直观地表现各门课程各分数段人数。2024/1/13第8章Excel综合应用1—成绩表统计与分析24创立图表2024/1/13第8章Excel综合应用1—成绩表统计与分析25在“成绩统计表〞中，根据各分数段人数及缺考人数制作图表。要求如下：图表类型为“簇状柱形图〞。选择目标单元格区域A8:E13。在“插入〞选项卡的“图表〞组中单击“柱形图〞按钮，在弹出的下拉列表中选择“二维柱形图〞区域的“簇状柱形图〞选项。生成的图表如右图所示。这里有错误

选择数据源2024/1/13第8章Excel综合应用1—成绩表统计与分析26选中图表，选择“图表工具-设计〞选项卡，在“数据〞组中单击“选择数据〞按钮，弹出“选择数据源〞对话框。在保存原选定区域的根底上，按住Ctrl键不放，再选择“A2:E2〞区域，如以下图所示。可以看到，工作表中选定区域出现了两个闪动的虚线框，虚线框的单元格以绝对地址的形式出现在“图表数据区域〞文本框中，新增的标题区域“A2:E2〞，已反映在图例项中，替换了“系列1〞、……、“系列4〞。单击“确定〞按钮后，图表也同步更新。设置图表布局与图表样式2024/1/13第8章Excel综合应用1—成绩表统计与分析27设置图表布局为“布局9〞、图表样式为“样式26〞。在“图表工具-设计〞选项卡的“图表布局〞组中单击列表框旁的“其他〞按钮，弹出“图表布局〞列表框，选择“布局9〞。在“图表工具-设计〞选项卡的“图表样式〞组中单击列表框旁的“其他〞按钮，弹出“图表样式〞列表框，选择“样式26〞。设置效果如以下图所示。修改图表〔一〕2024/1/13第8章Excel综合应用1—成绩表统计与分析28在“成绩统计表〞工作表中，为图表添加“模拟运算表〞、将“图例〞位置移动到顶部显示。选中图表，选择“图表工具-布局〞选项卡，在“标签〞组中单击“模拟运算表〞按钮，在弹出的下拉列表中选择“显示模拟运算表和图例项标示〞选项。在“标签〞组中单击“图例〞按钮，在弹出的下拉列表中选择“在顶部显示图例〞选项。设置效果如右图所示。修改图表〔二〕在“成绩统计表〞工作表中，将图表类型改为“簇状圆柱图〞、“切换行/列〞，从图表中删除“缺考人数〞、将图表移动到工作簿的新工作表中，并将新工作表命名为“成绩统计图〞。单击图表。选择“图表工具-设计〞选项卡。在“类型〞组中单击“更改图表类型〞按钮，翻开“更改图表类型〞对话框，在“柱形图〞区域中选择“簇状圆柱图〞选项。在“数据〞组中单击“选择数据〞按钮，翻开“选择数据源〞对话框，单击“切换行/列〞按钮，图例项变为行标题。在“图例项〔系列〕〞栏中，选择“缺考人数〞系列，单击“删除〞按钮。在“位置〞组中单击“移动图表〞按钮，翻开“移动图表〞对话框，选择“新工作表〞单项选择按钮，在右边的文本框中输入“成绩统计图〞。2024/1/13第8章Excel综合应用1—成绩表统计与分析29修改图表〔三〕在“成绩统计图〞工作表中，调整三维视图的角度，修改图表标题及坐标轴标题。选择“成绩统计图〞工作表中的图表。单击鼠标右键，在弹出的快捷菜单中选择“三维旋转〞命令，翻开“设置图表区格式〞对话框，在“三维旋转〞的“图表缩放〞区域中勾选“直角坐标轴〞复选框。鼠标单击并选中“图表标题〞文字，输入“成绩统计图〞。鼠标右键单击横坐标轴位置，从快捷菜单中选择“编辑文字〞，删除原有文字，输入“等级〞。用相同的方法，在纵坐标轴位置输入“人数〞。2024/1/13第8章Excel综合应用1—成绩表统计与分析30格式化图表2024/1/13第8章Excel综合应用1—成绩表统计与分析31在“成绩统计图〞工作表中，设置“图表区〞、“背景墙〞的填充效果；修饰“图表标题〞。效果如右图所示。选择“图表区〞。在“形状样式〞组中选择“细微效果-蓝色，强调颜色1〞。在“形状效果〞列表中选择“阴影〞中的“内部〞区域中的“内部右上角〞选项。选择“背景墙〞。在“形状样式〞组中选择“纹理〞中的“蓝色面巾纸〞选项。选择图表标题。设置字体为“隶书〞、字号为“28〞。选择第4行第2列的艺术字样式。在“文本效果〞的“阴影〞选项中选择“外部〞区域中的“左下斜偏移〞选项。思考题〔三〕选择图表的数据源时，如何选择不连续的数据区域？如何修改图表？对已建立完成的图表，应如何添加“图表标题〞？对图表进行格式化时，应该如何操作？将“图表区〞的填充效果设置为“雨后初晴〞，应如何操作？2024/1/13第8章Excel综合应用1—成绩表统计与分析32