任务12 使用公式和函数

第3章WPS电子表格处理任务12：使用公式和函数

公式和函数的综合应用01任务描述02任务实现03课后练习目录任务描述任务描述一学期结束，班主任将学生的期末考试成绩导出后，对班级考试成绩作统计分析。考点分析1.单元格的引用。2.公式的使用。3.常用函数的使用。最终效果学生成绩表效果图课程成绩统计表效果图任务实现打开素材中的“学生成绩表”工作簿，选择“学生成绩表”工作表打开素材文件01选中C3：F22数据区域，单击功能区中的“开始”选项卡→“条件格式”下拉按钮，在下拉列表中选择“突出显示单元格规则”下拉列表→“小于”选项，打开“小于”对话框。在“小于”的对话框中输入“60”，如图3.2.3所示，单击“确定”按钮。利用条件格式将不及格的学生成绩突出显示。设置条件格式02任务实现任务1：突出不及格的学生成绩任务实现任务2：转换实训成绩选择H列并右击，在弹出的快捷菜单中选择中“在左侧插入列”命令，在实训一列后面添加一列，输入列标题“实训成绩转换”。改变列宽到适当大小。选中H3：H22数据区域，打开“数据有效性”对话框，单击“全部清除”按钮，单击“确定”按钮，清除数据有效性的设置。01①选中H3单元格，输入“=IF(G3="优",95,IF(G3="良",85,IF(G3="中",75,IF(G3="及格",65,55))))”，最后按“Enter”键，将第一个学生的实训成绩转换成百分制。利用填充柄，将其他学生的实训成绩转换成百分制，并对单元格格式进行相关设置，结果如图3.2.5所示.02插入“实训成绩转换”一列利用公式求出实训成绩转换数据任务实现任务3：计算每位学生的总分、平均分和名次选中I3单元格，输入“=C3+D3+E3+F3+H3”，按“Enter”算出第一位学生的总成绩。在输入过程中，用户可以单击选定每门课程成绩的单元格。利用填充柄，计算出其他学生的总分成绩。01选中J3单元格，单击功能区中的“公式”选项卡→“求和”下拉按钮，在打开的下拉列表中选择“平均值”选项，在J3单元格中显示“=AVERAGE(H3:I3)”，使用鼠标选中C3：F3数据区域，再按住“Ctrl”键选中H3，此时在J3单元格中显示“=AVERAGE(C3:F3,H3)”，按“Enter键”计算出第一位学生的平均分。利用填充柄，计算出其他学生的平均分成绩。选中J3：J22数据区域，打开“单元格格式”对话框，在“数字”选项卡的“分类”列表中选择“数值”选项，设置“小数位数”为“2”，单击“确定”按钮，使平均分保留2位小数。02计算总分设置工作表标签的颜色任务实现任务3：计算每位学生的总分、平均分和名次选中K3单元格，单击功能区中的“公式”选项卡→“插入”按钮，打开“插入函数”对话框，选择“全部函数”选项卡中的“或选择类别”下拉按钮，在下拉列表框中选择“统计”，然后在“选择函数”列表框中选择“RANK”，如图3.2.6所示，单击“确定”按钮，打开“函数参数”对话框。当光标位于“数值”框时，单击单元格J3选中第一位学生的平均分成绩，再将光标移到“引用”框，选定J3：J22数据区域，按F4键，将相对地址修改为绝对地址“$J$3:$J$22”，光标移到“排位方式”框中输入数字“0”，如图3.2.7所示，单击“确定”按钮。03计算名次任务实现任务4：统计各门课程成绩打开“课程成绩统计表”工作表。选中B3单元格，单击功能区中的“公式”选项卡→“求和”按钮，在打开的下拉列表中选择“平均值”命令，选择“学生成绩表”工作表，选择C3：C22数据区域，按“Enter”键。计算出高等数学课程的平均成绩。选中B3单元格，使用填充柄计算其他课程成绩。其中“实训”课程出错，效果如图3.2.8所示。选中F3单元格，在编辑框中将G3：G22修改为H3：H22，按“Enter”键。计算出实训课程正确的平均成绩。使用求平均分相同的方法，借助函数最大值（MAX）、和最小值（MIN），求出班级的最高分与最低分。01计算各门课程的平均分，最高分与最低分任务实现任务4：统计各门课程成绩选中B6单元格，单击功能区中的“公式”选项卡→“插入”按钮，打开“插入函数”对话框，选择“全部函数”选项卡中的“或选择类别”下拉按钮，在下拉列表框中选择“全部”，然后在“选择函数”列表框中选择“COUNTIF”选项，打开“函数参数”对话框。当光标位于“区域”框时，选择“学生成绩表”的C3：C22数据区域，再将光标移到“条件”框中输入条件“>=90”，如图3.2.9所示，单击“确定”按钮，计算出高等数学在90分以上的人数。利用填充柄，计算出其他课程90分以上的人数，并修改“实训”课程的函数数据区域，得到正确的结果。02计算每门课程90分以上的人数任务实现任务4：统计各门课程成绩选定B7单元格，打开“插入函数”对话框，选择“全部函数”选项卡中的“或选择类别”下拉按钮，在下拉列表框中选择“全部”，然后在“选择函数”列表框中选择“COUNTIFS”选项，打开“函数参数”对话框。当光标位于“区域1”框时，选择“学生成绩表”的C3：C22数据区域，再将光标移到“条件1”框中，输入条件“<90”。当光标移到“区域2”框时，同样选择“学生成绩表”的C3：C22数据区域，再将光标移到“条件2”框中，输入条件“>=80”，如图3.2.10所示，单击“确定”按钮，计算出高等数学在80-89分的人数。03计算其他分数段人数利用填充柄，计算出其他课程80-89分的人数，并修改“实训”课程的函数数据区域，得到正确的结果将单元格B8、B9、B10中分别设置为“=COUNTIFS(学生成绩表!C3:C22,"<80",学生成绩表!C3:C22,">=70")”、“=COUNTIFS(学生成绩表!C3:C22,"<70",学生成绩表!C3:C22,">=60")”和“=COUNTIF(学生成绩表!C3:C22,"<60")”，统计出高等数学各分段的人数，然后用填充柄，计算其他课程的各分段人数，并修改“实训”课程公式中的数据区域，得出正确结果。任务实现任务4：统计各门课程成绩选中B11单元格，输入“=COUNTIF(学生成绩表!C3:C22,">=60")/COUNT(学生成绩表!C3:C22)”，按“Enter”键统计出高等数学的及格率。利用填充柄，计算出其他及格率，并修改“实训”课程公式中的数据区域，得到正确的结果。按照同样方法计算出各门课程的优秀率。选中B11：F12数据区域，设置数据为“百分比”，保留“0”位