(电子表格题)滨海市对重点中学组织了一次物理统考,并生成了所有考生和每一个题目的得分。市教委要求小罗老师根据已有数据,统计分析各学校及班级的考试情况。请根据考生文件夹下“Excel素材.xlsx”中的数据,帮助小罗完成此项工作。具体要求如下: 利用“成绩

admin2022-04-08  22

问题 (电子表格题)滨海市对重点中学组织了一次物理统考,并生成了所有考生和每一个题目的得分。市教委要求小罗老师根据已有数据,统计分析各学校及班级的考试情况。请根据考生文件夹下“Excel素材.xlsx”中的数据,帮助小罗完成此项工作。具体要求如下:
利用“成绩单”“小分统计”和“分值表”工作表中的数据,完成“按班级汇总”和“按学校汇总”工作表中相应空白列的数值计算。具体提示如下:
①“考试学生数”列必须利用公式计算,“平均分”列由“成绩单”工作表数据计算得出。
②“分值表”工作表中给出了本次考试各题的类型及分值。(备注:本次考试一共50道小题,其中【1】至【40】为客观题,【41】至【50】为主观题)。
③“小分统计”工作表中包含了各班级每一道小题的平均得分,通过其可计算出各班级的“客观题平均分”和“主观题平均分”。(备注:由于系统生成每题平均得分时已经进行了四舍五入操作,因此通过其计算“客观题平均分”和“主观题平均分”之和时,可能与根据“成绩单”工作表的计算结果存在一定误差)。
④利用公式计算“按学校汇总”工作表中的“客观题平均分”和“主观题平均分”,计算方法为:每个学校的所有班级相应平均分乘以对应班级人数,相加后再除以该校的总考生数。
⑤计算“按学校汇总”工作表中的每题得分率,即:每个学校所有学生在该题上的得分之和除以该校总考生数,再除以该题的分值。
⑥所有工作表中“考试学生数”“最高分”“最低分”显示为整数;各类平均分显示为数值格式,并保留2位小数;各题得分率显示为百分比数据格式,并保留2位小数。

选项

答案操作步骤为: 第①小题: 【步骤1】切换到“成绩单”工作表,在数据区域最右侧的E1单元格中输入文字“学校班号”,在E2单元格中输入公式“=A2&B2”,按下回车键(Enter)完成输入;选中E2单元格,双击其右下角的填充柄填充其他单元格。 【步骤2】切换到“按班级汇总”工作表,在数据区域最右侧的I1单元格中输入文字“学校班号”,在12单元格中输入公式“=A2&B2”,按下回车键(Enter)完成输入;选中12单元格,双击其右下角的填充柄填充其他单元格。 【步骤3】切换到“按班级汇总”工作表,在C2单元格中输入公式“=COUNTIF(成绩单!$E$2:$E$950,I2)”,按下回车键(Enter)完成输入;选中C2单元格,双击其右下角的填充柄填充其他单元格。 【步骤4】切换到“按学校汇总”工作表,在B2单元格中输入公式“=COUNTIF(成绩单!$A$2:$A$950,A2)”,按下回车键(Enter)完成输入;选中B2单元格,双击其右下角的填充柄填充其他单元格。 【步骤5】切换到“成绩单”工作表,选中任意一个有数据的单元格,在“插入”选项卡下“表格”功能组中单击“数据透视表”按钮,打开“创建数据透视表”对话框。保持默认设置(注意:“表/区域”后面应显示为“成绩单!$A$1:$E$950”,“选择放置数据透视表的位置”为“新工作表”),单击“确定”按钮。 【步骤6】在“选择要添加到报表的字段”列表中选中“学校班号”字段将其拖动到“行”下方;连续3次将“物理”字段将拖动到“∑值”下方。 【步骤7】选中B3(求和项:物理)单元格,在“数据透视表工具1分析”选项卡下“活动字段”功能组中单击“字段设置”按钮,打开“值字段设置”对话框。在“计算类型”列表中选择“最大值”,单击“确定”按钮。 【步骤8】选中C3(求和项:物理2)单元格,在“活动字段”功能组中单击“字段设置”按钮,打开“值字段设置”对话框。在“计算类型”列表中选择“最小值”,单击“确定”按钮。 【步骤9】选中D3(求和项:物理3)单元格,在“活动字段”功能组中单击“字段设置”按钮,打开“值字段设置”对话框。在“计算类型”列表中选择“平均值”,单击“确定”按钮。 【步骤10】切换到“接班级汇总”工作表,在D2单元格中输入函数公式“=VLOOK-.UP(12,Sheet1!$A$4:$D$35.2.0)”(“Sheet1”是辅助计算的数据透视表所在的工作表),按下回车键(Enter)完成输入,双击D2单元格右下角的填充柄填充其他单元格。 【步骤11】在E2单元格中输入函数公式“=VLOOKUP(12,Sheet1!$A$4:$D$35.3,0)”(“Sheet1”是辅助计算的数据透视表所在的工作表),按下回车键(Enter)完成输入,双击E2单元格右下角的填充柄填充其他单元格。 【步骤12】在F2单元格中输入函数公式“=VLOOKUP(12,Sheet1!$A$4:$D$35.4,0)”(“Sheet1”是辅助计算的数据透视表所在的工作表),按下回车键(Enter)完成输入,双击F2单元格右下角的填充柄填充其他单元格。 【步骤13】切换到“成绩单”工作表,选中任意一个有数据的单元格,在“插入”选项卡下“表格”功能组中单击“数据透视表”按钮,打开“创建数据透视表”对话框。保持默认设置(注意:“表/区域”后面应显示为“成绩单!$A$1:$E$950”,“选择放置数据透视表的位置”为“新工作表”),单击“确定”按钮。 【步骤14】在“选择要添加到报表的字段”列表中选中“学校名称”字段将其拖动到“行”下方;连续3次将“物理”字段将拖动到“∑值”下方。 【步骤15】选中B3(求和项:物理)单元格,在“数据透视表工具1分析”选项卡下“活动字段”功能组中单击“字段设置”按钮.打开“值字段设置”对话框。在“计算类型”列表中选择“最大值”,单击“确定”按钮。 【步骤16】选中C3(求和项:物理2)单元格,在“活动字段”功能组中单击“字段设置”按钮,打开“值字段设置”对话框。在“计算类型”列表中选择“最小值”,单击“确定”按钮。 【步骤17】选中D3(求和项:物理3)单元格,在“活动字段”功能组中单击“字段设置”按钮,打开“值字段设置”对话框。在“计算类型”列表中选择“平均值”,单击“确定”按钮。 【步骤18】切换到“按学校汇总”工作表,在C2单元格中输入公式“=vLOOKUP(A2,Sheet2!$A$4:$D$7,2.0)”(“Sheet2”是辅助计算的数据透视表所在的工作表),按下回车键(Enter)完成输入:选中C2单元格,双击其右下角的填充柄填充其他单元格。 【步骤19】在D2单元格中输入公式“=VLOOKUP(A2,Sheet2!$A$4:$D$7,3,0)”(“Sheet2”是辅助计算的数据透视表所在的工作表),按下回车键(Enter)完成输入;选中D2单元格,双击其右下角的填充柄填充其他单元格。 【步骤20】在E2单元格中输入公式“=VLOOKUP(A2,Sheet2!$A$4:$D$7.4,0)”(“Sheet2”是辅助计算的数据透视表所在的工作表),按下回车键(Enter)完成输入;选中E2单元格,双击其右下角的填充柄填充其他单元格。 第②③小题: 【步骤1】切换到“按班级汇总”工作表,在G2单元格中输入公式“=SUM(小分统计!C2:AP2)”,按下回车键(Enter)完成输入;选中G2单元格,双击其右下角的填充柄填充其他单元格。 【步骤2】在H2单元格中输入公式“=SUM(小分统计!AQ2:AZ2)”,按下回车键(Enter)完成输入;选中H2单元格,双击其右下角的填充柄填充其他单元格。 第④小题: 【步骤1】切换到“按班级汇总”工作表,在儿单元格中输入文字“客观题总分”;在J2单元格中输入公式“=C2*G2”,按下回车键(Enter)完成输入;选中J2单元格,双击其右下角的填充柄填充其他单元格。 【步骤2】在K1单元格中输入文字“主观题总分”;在J2单元格中输入公式“=C2$H2”,按下回车键(Enter)完成输入;选中K2单元格,双击其右下角的填充柄填充其他单元格。 【步骤3】切换到“按学校汇总”工作表,在F2单元格中输入公式“=SUMIF(按班级汇总!$A$2:$A$33,A2,按班级汇总!$J$2:$J$33)/B2”,按下回车键(Enter)完成输入;选中F2单元格,双击其右下角的填充柄填充其他单元格。 【步骤4】切换到“按学校汇总”工作表,在G2单元格中输入公式“=SUMIF(按班级汇总!$A$2:$A$33,A2,接班级汇总!$K$2:$K$33)/B2”,按下回车键(Enter)完成输入;选中G2单元格,双击其右下角的填充柄填充其他单元格。 第⑤小题: 【步骤1】在“小分统计”工作表标签名称上单击鼠标右键,在弹出的快捷菜单中选择“移动或复制”,打开“移动或复制工作表”对话框,在“下列选定工作表之前”列表中选择“成绩单”,勾选“建立副本”复选框,单击“确定”按钮。 【步骤2】在副本表“小分统计(2)”工作表中,选中C2:AZ33单元格区域,按下Delte键清除内容;在C2单元格中输入公式“=小分统计!C2+按班级汇总!$C2”,按下回车键(Enter)完成输入;选中C2单元格,按“Ctrl+C”复制,再选中C2:AZ33单元格区域,按下“Ctrl+V”粘贴。 【步骤3】切换到“按学校汇总”工作表,在H2单元格中输入公式“=SUMIF(小分统计(2)1$A$2:$A$33,$A2,小分统计(2)!C$2:C$33)/$B2/分值表!B$3”,按下回车键(Enter)完成输入;选中H2单元格,按“Ctrl+C”复制,再选中H2:BE5单元格区域,按下“Ctrl+V”粘贴。 第⑥小题: 【步骤1】切换到“按班级汇总”工作表,选中C2:E33单元格区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框。在“数字”选项卡下“分类”列表中选择“数值”,在右侧设置“小数位数”为“0”,单击“确定”按钮。 【步骤2】选中F2:H33单元格区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框。在“数字”选项卡下“分类”列表中选择“数值”,在右侧设置“小数位数”为“2”,单击“确定”按钮。 【步骤3】切换到“按学校汇总”工作表,选中B2:D5单元格区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框。在“数字”选项卡下“分类”列表中选择“数值”,在右侧设置“小数位数”为“0”,单击“确定”按钮。 【步骤4】选中E2:G5单元格区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框。在“数字”选项卡下“分类”列表中选择“数值”,在右侧设置“小数位数”为“2”,单击“确定”按钮。 【步骤5】选中H2:BE5单元格区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框。在“数字”选项卡下“分类”列表中选择“百分比”,在右侧设置“小数位数”为“2”,单击“确定”按钮。

解析
转载请注明原文地址:https://kaotiyun.com/show/BMap777K
0

最新回复(0)