电子表格题 小许正在使用Excel表格分析中国主要城市的空气质量,根据下列要求帮助她完成数据整理和分析工作。
问答题 7.在考生文件夹下,将“Excel素材.xlsx”文件另存为“Excel.xlsx”(“.xlsx”为扩展名),后续操作均基于此文件,否则不得分。
【正确答案】步骤1:打开考生文件夹下的“Excel素材.xlsx”文件。
步骤2:单击【文件】选项卡下“另存为”命令,弹出“另存为”对话框,将文件名修改为“Excel”,单击“保存”按钮。
【答案解析】
问答题 8.删除“广州”、“北京”和“上海”3张工作表中的全部超链接,并将这3张工作表中“日期”列数字格式都修改为“××年×月×日”格式,例如“16年5月8日”。
【正确答案】步骤l:在“Excel.xlsx”工作簿中,单击“广州”工作表,然后选中A列,单击鼠标右键,在弹出的快捷菜单中选择“删除超链接”;选中“A2:A367”数据区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,弹出“设置单元格格式”对话框,在“数字”选项卡的“分类”列表中,选择“自定义”,在右侧的“类型”文本框中输入“yy年m月d日”,如图1.12所示,单击“确定”按钮。
【答案解析】
问答题 9.根据“空气质量指数说明”工作表中的空气质量分级标准,在“广州”、“北京”和“上海”3张工作表的第I列和第J列分别统计每天空气“AQI指数”所对应的“空气质量指数级别”和“空气质量指数类别”。
【正确答案】步骤l:单击选中“广州”工作表,然后按住键盘上的“Shift”键,再单击选中“北京”和“上海”工作表,此时三张工作表成为一个工作组。
步骤2:在“广州”工作表的12单元格中输入公式“=IF(B2<=50,”一级”,IF(B2<=100,”二级”,IF(B2<=150,”三级”,IF(B2<=200,”四级”,IF(B2<=300,”五级”,”六级”)))))”,按Enter键确认输入,双击12单元格右下角的填充句柄,将公式填充至I367单元格。此时。三张表格的I列均生成数据。
步骤3:在“广州”工作表的J2单元格中输入公式“=IF(B2<=50,"优",IF(B2<=100,"良",IF(B2<=150,"轻度污染",IF(B2<=200,"中度污染",lF(B2<=300,"重度污染","严重污染"))))),按Enter键确认输入,双击J2单元格右下角的填充句柄,将公式填充至J367单元格。此时,三张表格的J列均生成数据。
【答案解析】
问答题 10.设置“广州”、“北京”和“上海”3张工作表中数据区域的格式:
①为3个工作表数据区域的标题行设置适当的单元格填充颜色、字体颜色,并为3个工作表数据区域添加所有框线。
②在“北京”工作表中,比较每天的PM2.5和PM10数值,将同一天中数值较大的单元格颜色填充为红色、字体颜色设置为“白色,背景l”。
③在“上海”工作表中,如果某天PM2.5和PM10的数值都大于100,则将该天整行记录的字体颜色都设置为红色。
④在“广州”工作表中,如果某天为周末(周六或周日),且AQI指数大于广州市AQI指数的全年平均值,则将该天整行记录的字体颜色设置为红色。
【正确答案】步骤1:继续保持三张工作表成组状态,选中“广州”工作表的标题行“A1:Jl”,单击【开始】选项卡下【字体】功能组中的“填充颜色”按钮,在下拉列表中选择一种填充颜色;单击“字体颜色”按钮,在下拉列表中选择一种合适的字体颜色。
步骤2:选择“广州”工作表的数据区域“A2:J367",单击【开始】选项卡下【字体】功能组中的“框线”按钮,在下拉列表中选择“所有框线”。
步骤3:在“广州”工作表名处单击鼠标右键,在弹出的快捷菜单中选择“取消组合工作表”。
步骤4:选中“北京”工作表的C2:D367单元格区域,单击【开始】选项卡下【样式】功能组中的“条件格式”,在下拉列表中选择“新建规则”,弹出“新建格式规则”对话框,在“选择规则类型”列表框中选中“使用公式确定要设置格式的单元格”,在下方的文本框中输入公式“=C2=MAX($C2:$D2)”,如图1.13所示,单击下方“格式”按钮,弹出“设置单元格格式”对话框,在该对话框的“填充”选项卡中将填充色设置为“红色”,在“字体”选项卡下将字体颜色设置为“白色,背景l”,单击“确定”按钮,最后关闭所有对话框。
步骤5:选中“上海”工作表的A2:J367单元格区域,单击【开始】选项卡下【样式】功能组中的“条件格式”,在下拉列表中选择“新建规则”,弹出“新建格式规则”对话框,在“选择规则类型”列表框中选中“使用公式确定要设置格式的单元格”,在下方的文本框中输入公式“=AND($C2>=100,$D2>=100)”,如图1.14所示,单击下方“格式”按钮,弹出“设置单元格格式”对话框,在该对话框的“字体”选项卡下将字体颜色设置为“红色”,单击“确定”按钮,最后关闭所有对话框。

步骤6:选中“广州”工作表的A2:J367单元格区域,单击【开始】选项卡下【样式】功能组中的“条件格式”,在下拉列表中选择“新建规则”,弹出“新建格式规则”对话框,在“选择规则类型”列表框中选中“使用公式确定要设置格式的单元格”,在下方的文本框中输入公式“=AND($B2>AVERAGE($B$2:$B$367),OR(WEEKDAY($A2,2)=6,WEEKDAY($A2,2)=7))”,如图1.15所示,单击下方“格式”按钮,弹出“设置单元格格式”对话框,在该对话框的“字体”选项卡下将字体颜色设置为“红色”,单击“确定”按钮,最后关闭所有对话框。
【答案解析】
问答题 11.取消保护工作簿,调整工作表标签的顺序,自左至右分别为“北京”、“上海”、“广州”和“空气质量指数说明”。
【正确答案】步骤1:单击【审阅】选项卡下【更改】选项组中的“保护工作簿”按钮,取消对工作簿的保护。
步骤2:单击选中“北京”工作表名,按住鼠标左键,将其移动到最左侧位置;选中“上海”工作表名,按住鼠标左键,将其移动到“北京”工作表右侧;选中“广州”工作表名,按住鼠标左键,将其移动到“上海”工作表右侧。
【答案解析】
问答题 12.参照考生文件夹下“数据透视表和数据透视图.png”文件中的样例效果,按照如下要求,在新的工作表中创建数据透视表和数据透视图:
①数据透视表置于Al:B16单元格区域,可以显示每个城市l—12月AQI指数的平均值,不显示总计值,数值保留0位小数,并修改对应单元格中的标题文字。
②数据透视图置于C1:N16单元格区域中,图表类型为带数据标记的折线图。
③数据透视图中不显示纵坐标轴、图例和网格线。
④数据透视图数据标记类型为实心圆圈,大小为7。
⑤数据透视图显示数据标签,且当数据标签的值大于100时,标签字体颜色为红色(颜色应可以根据选择城市的不同而自动变化)。
⑥数据透视图图表标题为城市名称,且应当随数据透视表B1单元格中所选择城市的变化而自动更新。
⑦隐藏数据透视图中的所有按钮。
⑧将数据透视表和数据透视图所在工作表命名为“月均值”,并置于所有工作表右侧。
⑨将数据透视图最终显示的城市切换为“北京”。
【正确答案】步骤l:单击快速访问工具栏右侧的黑色三角箭头,在弹出的快捷菜单中选择“其他命令”,打开“Excel选项”对话框。在中间的命令列表中选择“不在功能区中的命令”,在下拉列表中选择“数据透视表和数据透视图向导”,单击中间的“添加”按钮,如图1.16所示,最后单击“确定”按钮,关闭对话框,此时在快速访问工具栏中出现刚添加的“数据透视表和数据透视图向导”按钮。

步骤2:单击快速访问工具栏中的“数据透视表和数据透视图向导”按钮,弹出“数据透视表和数据透视图向导一步骤l(共3步)”对话框,选中“多重合并计算数据区域”和“数据透视图(及数据透视表)”选项,如图1.17所示,然后单击“下一步”按钮。

步骤3:在弹出的“数据透视表和数据透视图向导一步骤2a(共3步)”对话框中选择“自定义页字段”选项,如图1.18所示,然后单击“下一步”按钮。

步骤4:在弹出的“数据透视表和数据透视图向导一步骤2b(共3步)”对话框中,单击“选定区域”文本框右侧的“折叠按钮”,选取“北京”工作表的数据区域“Al:B367”,然后点击“添加”按钮,在下方的“请先指定要建立在数据透视表中的页字段数目”中选择“l”选项,在“字段l”中输入文本“北京”,如图1.19所示;继续单击上方“选定区域”文本框右侧的“折叠按钮”,选取“上海”工作表的数据区域“Al:B367”,然后点击“添加”按钮,在下方的“请先指定要建立在数据透视表中的页字段数目”中选择“1”选项,在“字段l”中输入文本“上海”,如图1.20所示;继续单击上方“选定区域”文本框右侧的“折叠按钮”,选取“广州”工作表的数据区域“Al:B367”,然后点击“添加”按钮,在下方的“请先指定要建立在数据透视表中的页字段数目”中选择“l”选项,在“字段l”中输入文本“广州”,如图1.21所示,最后单击“下一步”按钮。

步骤5:在弹出的“数据透视表和数据透视图向导一步骤3(共3步)”对话框中,采用默认设置,如图1.22所示,直接单击“完成”按钮。

步骤6:在出现的新工作表中选中A5单元格,单击鼠标右键,在弹出的快捷菜单中选择“创建组”,弹出“分组”对话框,采用默认设置,如图1.23所示,直接单击“确定”按钮。

步骤7:单击【数据透视表工具/设计】选项卡下【布局】功能组中的“总计”按钮,在下拉列表中选择“对行和列禁用”;然后选中B5单元格,单击鼠标右键,在弹出的快捷菜单中选择“值汇总依据/平均值”,选中“B5:B16”单元格区域,单击鼠标右键,在弹出的快捷菜单中选择“设置单元格格式”,弹出“设置单元格格式”对话框,在“数字”选项卡的“分类”列表框中选择“数值”,将右侧的“小数位数”设置为“0”,单击“确定”按钮。
步骤8:参考考生文件夹下的“数据透视表和数据透视图.png”文件,将A4单元格内容修改为“月份”,将A3单元格内容修改为“月平均值”,删除B3单元格内容,将Al单元格内容修改为“城市”,适当调整行高。
步骤9:将右侧的数据透视图移动到C1:N16单元格区域,单击【数据透视图工具/设计】选项卡下【类型】功能组中的“更改图表类型”,打开“更改图表类型”对话框,选择“折线图/带数据标记的折线图”,单击“确定”按钮。
步骤10:单击【数据透视图工具/布局】选项卡下【坐标轴】功能区中的“坐标轴”按钮,在下拉列表中选择“主要纵坐标轴/无”,取消纵坐标轴;单击右侧的【网格线】按钮,在下拉列表中选择“主要横网格线/无”,取消网格线;在【标签】功能组中,单击“图例”按钮,在下拉列表中选择“无”,取消图例。
步骤11:在【数据透视图工具/布局】选项卡下【当前所选内容】功能组中的“图表元素”下拉列表框中选择“系列AQI指数”,将绘图区域中所有数据标记点全部选中,然后鼠标右键单击任一标记点,在弹出的快捷菜单中选择“设置数据系列格式”,弹出“设置数据系列格式”对话框,在左侧的列表框中选择“数据标记选项”,在右侧的“数据标记类型”功能区域中,设置标记类型为“内置/实心圆圈”,大小为7.如图1.24所示,单击“关闭”按钮,关闭对话框。

步骤12:单击【数据透视图工具/布局】选项卡下【标签】功能组中的“数据标签”按钮,在下拉列表中选择“上方”;选中绘图区中的任一标签文本框,单击鼠标右键,在弹出的快捷菜单中选择“设置数据标签格式”,弹出“设置数据标签格式”对话框,在左侧的列表框中选择“数字”,在右侧的“类别”列表框中选择“自定义”,在下方的“格式代码”文本框中输入“[红色][>100]0;0”,单击“添加”按钮,如图1.25所示,最后单击“关闭”按钮,关闭对话框。
【答案解析】
问答题 13.按照如下要求设置文档的页面布局:
①设置“广州”、“北京”和“上海”3张工作表中标题所在行,使得在打印时,会在每页重复出现。
②将所有工作表的纸张方向设置为横向,并适当调整每张工作表的列宽和页边距,使得每个工作表中表格和图表的宽度不超过一页。
③为整个工作簿添加页脚,格式为“页码0f总页数”(例如“3 of 10”),且位于页脚正中。
【正确答案】步骤l:选中“北京”工作表,选择A1:J367数据区域,单击【页面布局】选项卡下【页面设置】功能组中的“打印区域”按钮,在下拉列表中选择“设置打印区域”;然后单击“打印标题”按钮,弹出“页面设置”对话框,将“顶端标题行”设置为“$1:$l”,如图1.26所示,单击“确定”按钮。

步骤2:按照上述同样方法,设置“上海”和“广州”工作表的打印区域和打印标题。
步骤3:在“北京”工作表中选择Al:J367数据区域,单击【页面布局】选项卡下【页面设置】功能组中的“打印区域”按钮,在下拉列表中选择“设置打印区域”,将“纸张方向”设置为“横向”;在右侧的【调整为合适大小】功能组中,将“宽度”设置为“1页”。
步骤4:按照上述同样方法,设置“上海”和“广州”工作表的纸张方向和表格宽度。
步骤5:在“空气质量指数说明”工作表中选择Al:D7数据区域,单击【页面布局】选项卡下【页面设置】功能组中的“打印区域”按钮,在下拉列表中选择“设置打印区域”,将“纸张方向”设置为“横向”;在右侧的【调整为合适大小】功能组中,将“宽度”设置为“l页”。
步骤6:选中“月均值”工作表中的Al:N16数据区域,单击【页面布局】选项卡下【页面设置】功能组中的“打印区域”按钮,在下拉列表中选择“设置打印区域”,将“纸张方向”设置为“横向”;在右侧的【调整为合适大小】功能组中,将“宽度”设置为“1页”。
步骤7:选中“北京”工作表,按住键盘上的Shift键,单击选中最后一张工作表“月均值”,将全部工作表全部选中,单击【页面布局】选项卡下【页面设置】功能组右下角的对话框启动器按钮,弹出“页面设置”对话框,切换到“页眉/页脚”选项卡,单击“自定义页脚”,弹出“页脚”设置对话框,将光标置于“中”文本框中,单击上方的“插入页码”按钮,输入“of”,然后单击上方的“插入页数”按钮,如图1.27所示,单击“确定”按钮,最后关闭所有对话框。
【答案解析】