《第5章Excel综合应用成绩表统计及分析课件》由会员分享，可在线阅读，更多相关《第5章Excel综合应用成绩表统计及分析课件（35页珍藏版）》请在金锄头文库上搜索。
1、计算机应用基础学习目标熟练掌握统计函数COUNT、COUNTIF及COUNTIFS的使用方法熟练掌握逻辑判断函数IF的使用方法掌握条件格式的设置方法熟练使用图表的创建、修改及格式化2024/8/14第8章 Excel综合应用1成绩表统计与分析2目录8.1 成绩表统计案例分析 8.2 实现方法8.2.1 用统计函数与公式制作“成绩统计表”跨工作表的单元格引用 统计函数COUNTA及COUNT的使用 条件统计函数COUNTIF与COUNTIFS的使用 公式计算 8.2.2 用IF函数与条件格式制作“各科等级表”单元格数据的清除与删除逻辑判断函数IF的使用 条件格式 8.2.3用图表向导制作成绩统计
2、图 创建图表 修改图表 格式化图表 8.3 案例总结8.4 课后练习2024/8/14第8章 Excel综合应用1成绩表统计与分析38.1成绩表统计案例分析 任务的提出班主任王老师要制作 “成绩统计表”、“成绩等级表” 及“成绩统计图。解决方案 2024/8/14第8章 Excel综合应用1成绩表统计与分析4跨工作表的单元格引用打开“统计表（素材）.xlsx”文件，选择“成绩统计表”工作表。从“各科成绩表”中，将四门课程的“班级平均分”、“班级最高分”和“班级最低分”的数据引用到“成绩统计表”中的相应单元格中 。2024/8/14第8章 Excel综合应用1成绩表统计与分析5统计函数COUNT
3、及COUNTA的使用 将“各科成绩表”中各门课程的“参考人数”和“应考人数”的统计结果放置到“成绩统计表”中的相应单元格中。在“开始”选项卡的“编辑”组中单击“自动求和”按钮 旁边的下拉箭头，在弹出的列表中选择“计数”选项。可计算出“参考人数”。在“公式”选项卡的“函数库”组中单击“其他函数”按钮 ，在下拉列表中选择“统计”选项，在弹出的下拉列表框中选择“COUNTA”。可计算出“应考人数”。2024/8/14第8章 Excel综合应用1成绩表统计与分析6条件统计函数COUNTIF的功能 COUNTIF函数的功能：统计指定区域内满足给定条件的单元格数目。语法格式：COUNTIF(range,c
4、riteria)其中：Range 指定单元格区域，Criteria表示指定的条件表达式。条件表达式的形式可以为数字、表达式或文本。例如，条件可以表示为 60、60、=90 或 缺考等。2024/8/14第8章 Excel综合应用1成绩表统计与分析7条件统计函数COUNTIF的使用在“成绩统计表”的相应单元格中，用COUNTIF函数统计“各科成绩表”中4门课程的“缺考”人数、 “90-100”以及“小于60”分数段的人数。在“函数参数”对话框中，进行如下图所示的设置：编辑栏中的函数为 =COUNTIF(各科成绩表!D2:D38,缺考)。 用相同方法可以统计出“90-100”以及“小于60”分数段
5、的人数。2024/8/14第8章 Excel综合应用1成绩表统计与分析8条件统计函数COUNTIFS的功能 COUNTIFS函数的功能是：将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。语法格式：COUNTIFS (Criteria_range1, Criteria1, Criteria_range2, Criteria2)其中：Criteria_range1 必需。在其中计算关联条件的第一个区域，Criteria1必需。条件表达式；Criteria_range2, Criteria2, .可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。2024/8/14第8章 Ex
6、cel综合应用1成绩表统计与分析9条件统计函数COUNTIFS的使用在“成绩统计表”的相应单元格中，用COUNTIFS函数统计“各科成绩表”中4门课程的“80-89”、“70-79”以及“60-69”分数段的人数。在打开的“函数参数”对话框中填写上两组单元格范围和条件，如下图所示，单击“确定”按钮。 用相同方法可以统计出“70-79”以及“60-69”分数段的人数。2024/8/14第8章 Excel综合应用1成绩表统计与分析10公式计算 在“成绩统计表”中，计算各门课程的优秀率和及格率 。计算优秀率 ：=COUNTIF(各科成绩表!D$2:D$38,=90)/COUNT(各科成绩表!D$2:
7、D$38) 或：=B9/B6 计算及格率 ：=COUNTIF(各科成绩表!D$2:D$38,=60)/COUNT(各科成绩表!D$2:D$38) 或：=1-B13/B6 2024/8/14第8章 Excel综合应用1成绩表统计与分析11思考题（一）如何引用同一工作簿中不同工作表的数据？引用单元格的数据与复制粘贴数据有何区别？统计函数COUNT与COUNTA的区别是什么？条件统计函数COUNTIF和COUNTIFS的应用场合是分别什么？在“各科成绩表”工作表中分别统计出男、女生人数应该用什么函数？试写出函数的表达式。2024/8/14第8章 Excel综合应用1成绩表统计与分析12单元格数据的删
8、除与清除 复制“各科成绩表”工作表，并将复制后的工作表更名为“各科等级表”。在“各科等级表”中，清除4门课程列中的分数内容；清除“总分”列的所有属性；删除分数统计所在单元格区域。按Delete键，只清除选定单元格的内容，而保留了单元格的格式。在“开始”选项卡的“编辑”组中单击“清除”按钮 ，在弹出的列表中选择“全部清除”选项。此时单元格中的内容和格式全部被清除。在“开始”选项卡的“单元格”组中单击“删除”按钮 ，这时单元格区域的内容会由其他内容替代。2024/8/14第8章 Excel综合应用1成绩表统计与分析13逻辑判断函数IF的功能 IF函数的功能是：判断给出的条件是否满足，如果满足返回一
9、个值，如果不满足则返回另一个值。语法格式：IF(logical_test,value_if_true,value_if_false)共包括三个参数，其中：Logical_test 逻辑判断表达式；value_if_true 表达式为真时返回的值；value_if_false 表达式为假时返回的值。2024/8/14第8章 Excel综合应用1成绩表统计与分析14逻辑判断函数IF的使用在“各科等级表”的“应用文写作”列中，利用IF函数引用“各科成绩表”中“应用文写作”列的对应分数进行逻辑判断，如果分数在60分以上的返回“及格”，否则返回“不及格”。选择目标单元格G2 ，在“函数参数”对话框中进行
10、如下图所示的设置。 2024/8/14第8章 Excel综合应用1成绩表统计与分析15逻辑判断函数IF的使用（二）在“各科等级表”的“应用文写作”列中，利用IF函数再添加一个条件：对“各科成绩表”的“应用文写作”列中成绩为“缺考”的考生，在对应位置仍设置为“缺考”。在“各科等级表”中选择目标单元格G2，选择除“=”以外的其他内容，按快捷键Ctrl+X。在对话框中进行如下图所示的设置。其中在“Value_if_false”编辑框中，将插入点定位后，按快捷键Ctrl+V。2024/8/14第8章 Excel综合应用1成绩表统计与分析16逻辑判断函数IF的使用（三）分数等级缺考缺考分数=90A90分
11、数=80B80分数=70C70分数=60D分数60E2024/8/14第8章 Excel综合应用1成绩表统计与分析17在“各科等级表”的“大学英语”、“计算机应用”、“高等数学”列中，利用IF嵌套函数，引用“各科成绩表”中对应的3门课程的分数，按下表中分数与成绩等级的对应关系，在相应的科目中进行成绩等级设置。逻辑判断函数IF的使用（四）（1）在目标单元格D2中输入“=”。（2）单击“名称框”处的“IF”，打开“函数参数”对话框。（3）在“函数参数”对话框中进行如下图所示的设置。并将插入点定位在第3个参数“Value_if_false”编辑框中。 （4）再次单击“名称框”处的“IF”。第2次打开
12、“函数参数”对话框，在“Logical_test”处输入“各科成绩表!D2=90 优秀90分数=80良好80分数=70中等70分数=60及格分数=90,优秀,if(90A2=80,良好,if(80A2=70,中等,if(70A2=60,及格,if(A260,不及格,false)2024/8/14第8章 Excel综合应用1成绩表统计与分析23制作各科成绩统计图 图表比数据更易于表达数据之间的关系以及数据变化的趋势。 “成绩统计图”可以直观地表现各门课程各分数段人数。2024/8/14第8章 Excel综合应用1成绩表统计与分析24创建图表2024/8/14第8章 Excel综合应用1成绩表统计
13、与分析25在“成绩统计表”中，根据各分数段人数及缺考人数制作图表。要求如下：图表类型为“簇状柱形图” 。选择目标单元格区域A8:E13。在“插入”选项卡的“图表”组中单击“柱形图”按钮 ，在弹出的下拉列表中选择“二维柱形图”区域的“簇状柱形图”选项。生成的图表如右图所示。这里有错误选择数据源2024/8/14第8章 Excel综合应用1成绩表统计与分析26选中图表，选择“图表工具-设计”选项卡，在“数据”组中单击“选择数据”按钮 ，弹出“选择数据源”对话框。在保留原选定区域的基础上，按住Ctrl键不放，再选择“A2:E2”区域，如下图所示。可以看到，工作表中选定区域出现了两个闪动的虚线框，虚线
14、框的单元格以绝对地址的形式出现在“图表数据区域”文本框中，新增的标题区域“A2:E2”，已反映在图例项中，替换了“系列1”、“系列4”。单击“确定”按钮后，图表也同步更新。设置图表布局与图表样式 2024/8/14第8章 Excel综合应用1成绩表统计与分析27设置图表布局为“布局9”、图表样式为“样式26”。在“图表工具-设计”选项卡的“图表布局”组中单击列表框旁的“其他”按钮 ，弹出“图表布局”列表框，选择“布局9”。在“图表工具-设计”选项卡的“图表样式”组中单击列表框旁的“其他”按钮 ，弹出“图表样式”列表框，选择“样式26”。设置效果如下图所示。修改图表（一）2024/8/14第8章
15、 Excel综合应用1成绩表统计与分析28在“成绩统计表”工作表中，为图表添加“模拟运算表”、将“图例”位置移动到顶部显示。选中图表，选择“图表工具-布局”选项卡，在“标签”组中单击“模拟运算表”按钮 ，在弹出的下拉列表中选择“显示模拟运算表和图例项标示”选项。在“标签”组中单击“图例”按钮 ，在弹出的下拉列表中选择“在顶部显示图例”选项。设置效果如右图所示。修改图表 （二）在“成绩统计表”工作表中，将图表类型改为“簇状圆柱图”、“切换行/列”，从图表中删除“缺考人数”、将图表移动到工作簿的新工作表中，并将新工作表命名为“成绩统计图”。单击图表。选择“图表工具-设计”选项卡。在“类型”组中单击
16、“更改图表类型”按钮 ，打开“更改图表类型”对话框，在“柱形图”区域中选择“簇状圆柱图”选项。在“数据”组中单击“选择数据”按钮 ，打开“选择数据源”对话框，单击“切换行/列”按钮，图例项变为行标题。在“图例项（系列）”栏中，选择“缺考人数”系列，单击“删除”按钮。在“位置”组中单击“移动图表”按钮 ，打开“移动图表”对话框，选择“新工作表”单选按钮，在右边的文本框中输入“成绩统计图”。2024/8/14第8章 Excel综合应用1成绩表统计与分析29修改图表（三）在“成绩统计图”工作表中，调整三维视图的角度，修改图表标题及坐标轴标题。选择“成绩统计图”工作表中的图表。单击鼠标右键，在弹出的快
17、捷菜单中选择“三维旋转”命令，打开“设置图表区格式”对话框，在“三维旋转”的“图表缩放”区域中勾选“直角坐标轴”复选框。鼠标单击并选中“图表标题”文字，输入“成绩统计图”。鼠标右键单击横坐标轴位置，从快捷菜单中选择“编辑文字”，删除原有文字，输入“等级”。用相同的方法，在纵坐标轴位置输入“人数”。2024/8/14第8章 Excel综合应用1成绩表统计与分析30格式化图表2024/8/14第8章 Excel综合应用1成绩表统计与分析31在“成绩统计图”工作表中，设置“图表区”、“背景墙”的填充效果；修饰“图表标题”。效果如右图所示。选择“图表区”。在“形状样式”组中选择“细微效果-蓝色，强调颜
18、色1”。在“形状效果” 列表中选择“阴影” 中的“内部”区域中的“内部右上角”选项。选择“背景墙”。在“形状样式”组中选择“纹理”中的“蓝色面巾纸”选项。选择图表标题。设置字体为“隶书”、字号为“28”。选择第4行第2列的艺术字样式。在“文本效果” 的“阴影”选项中选择“外部”区域中的“左下斜偏移”选项。思考题（三）选择图表的数据源时，如何选择不连续的数据区域？如何修改图表？对已建立完成的图表，应如何添加“图表标题”？对图表进行格式化时，应该如何操作？将“图表区”的填充效果设置为“雨后初晴”，应如何操作？2024/8/14第8章 Excel综合应用1成绩表统计与分析32案例总结 （一）统计函数
19、COUNT、COUNTA、COUNTIF、COUNTIFS在使用COUNTIF函数时应该注意，当公式需要复制时，如果参数“Range”的引用区域固定不变，通常应使用绝对引用或区域命名方式实现；如果参数“Criteria”是表达式或字符串，必须在两边加上西文双引号。在使用COUNTIFS函数时应该注意，虽然多个指定区域无需彼此相邻，但必须具有相同的行数和列数。条件之间是“与”关系。逻辑判断函数IF以及嵌套函数的使用；当逻辑判断给出的条件多于两个时，通常采用IF函数的嵌套。在使用IF函数时，应注意函数多层嵌套时的括号匹配，并且公式中的所有符号必须为西文字符。 2024/8/14第8章 Excel综
20、合应用1成绩表统计与分析33案例总结 （二）设置条件格式在设置“条件格式”之前，必须正确选择设置条件格式的区域。否则，不能达到预期的效果。图表的创建、修改及格式化制作图表时，应了解表现不同的数据关系时，如何选择合适的图表类型，特别要注意正确选择数据源，在选择数据的同时还应该正确选择这些数据对应的行标题和列标题，才能构成一个完整的图表。在图表的制作过程中，只要按照选择“数据源图表类型图表布局图表样式图表位置”5步就可以完成图表的制作。对其中任何一步不满意，只要选择图表使之处于激活状态，就可以在“图表工具”的“设计”和“布局”两个选项卡中选择相应的功能进行修改。在对图表进行格式化时，要注意正确选择图表中的元素，不同元素的格式化设置选项是不同的。课后练习 打开“工资表(素材).xlsx”，并另存为“工资表.xlsx”，参见教材，完成对工资表的数据计算及图表绘制。2024/8/14第8章 Excel综合应用1成绩表统计与分析35
