题目要求
第32套
小许正在使用Excel表格分析中国主要城市的空气质量，根据下列要求帮助她完成数据整理和分析工作。
1．在考生文件夹下，将“Excel素材.xlsx”文件另存为“Excel.xlsx”（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。
2．删除“广州”、“北京”和“上海”3张工作表中的全部超链接，并将这3张工作表中“日期”列数字格式都修改为“××年×月×日”格式，例如“16年5月8日”。
3．根据“空气质量指数说明”工作表中的空气质量分级标准，在“广州”、“北京”和“上海”3张工作表的第I列和第J列分别统计每天空气“AQI指数”所对应的“空气质量指数级别”和“空气质量指数类别”。
4．设置“广州”、“北京”和“上海”3张工作表中数据区域的格式：
① 为3个工作表数据区域的标题行设置适当的单元格填充颜色、字体颜色，并为3个工作表数据区域添加所有框线。
② 在“北京”工作表中，比较每天的PM2.5和PM10数值，将同一天中数值较大的单元格颜色填充为红色、字体颜色设置为“白色，背景1”。
③ 在“上海”工作表中，如果某天PM2.5和PM10的数值都大于100，则将该天整行记录的字体颜色都设置为红色。
④ 在“广州”工作表中，如果某天为周末（周六或周日），且AQI指数大于广州市AQI指数的全年平均值，则将该天整行记录的字体颜色设置为红色。
5．取消保护工作簿，调整工作表标签的顺序，自左至右分别为“北京”、“上海”、“广州”和“空气质量指数说明”。
6．参照考生文件夹下“数据透视表和数据透视图.png”文件中的样例效果，按照如下要求，在新的工作表中创建数据透视表和数据透视图：
① 数据透视表置于A1:B16单元格区域，可以显示每个城市1-12月AQI指数的平均值，不显示总计值，数值保留0位小数，并修改对应单元格中的标题文字。
② 数据透视图置于C1:N16单元格区域中，图表类型为带数据标记的折线图。
③ 数据透视图中不显示纵坐标轴、图例和网格线。
④ 数据透视图数据标记类型为实心圆圈，大小为7。
⑤ 数据透视图显示数据标签，且当数据标签的值大于100时，标签字体颜色为红色（颜色应可以根据选择城市的不同而自动变化）。
⑥ 数据透视图图表标题为城市名称，且应当随数据透视表B1单元格中所选择城市的变化而自动更新。
⑦ 隐藏数据透视图中的所有按钮。
⑧ 将数据透视表和数据透视图所在工作表命名为“月均值”，并置于所有工作表右侧。
⑨ 将数据透视图最终显示的城市切换为“北京”。
7．按照如下要求设置文档的页面布局：
① 设置“广州”、“北京”和“上海”3张工作表中标题所在行，使得在打印时，会在每页重复出现。
② 将所有工作表的纸张方向设置为横向，并适当调整每张工作表的列宽和页边距，使得每个工作表中表格和图表的宽度不超过一页。
③ 为整个工作簿添加页脚，格式为“页码of总页数”（例如“3of10”），且位于页脚正中。
视频讲解
真题练习32-Excel电子表格_哔哩哔哩_bilibili
题库下载
文字解析
第32组
1．【解析】在考生文件夹中打开Excel素材.xlsx文件，然后单击“文件”|“另存为”按钮，文件名称中的“Excel素材”改成“Excel”，然后单击“确定”按钮。
2．【解析】步骤1：打开“广州”工作表，选中A列，然后单击鼠标右键，在弹出的下拉菜单中单击“删除超链接”命令，采用同样的方法删除“北京”和“上海”工作表中的超链接；
步骤2：选中“广州”工作表标签，然后按住<Shift>键，再单击“上海”工作表标签，这样就同时选中了3个工作表，选中A列；
步骤3：按<Ctrl+1>组合键，打开“设置单元格格式”对话框，在“数字”选项卡中的“分类”中选中“自定义”命令，然后在类型中输入“yy年m月d日”，单击“确定”按钮，这样3个工作表中的日期格式就设置好了。
3．【解析】步骤1：打开“空气质量指数说明”工作表，在A列前插入一个空白列，然后按新的B列最低值设置参数A2到A7单元格分别输入：1、51、101、151、201、301；
步骤2：选中“广州”工作表标签，然后按住<Shift>键，再单击“上海”工作表标签，这样就同时选中了3个工作表，在I2中输入函数公式“=VLOOKUP(B2,空气质量指数说明!$A$1:$E$7,3,1)”，按回车键；
【知识拓展】VLOOKUP函数
VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。本题中“=VLOOKUP(B2,空气质量指数说明!$A$1:$E$7,3,1)”的含义如下：
参数1—查找目标：“B2”。将在参数2指定区域的第1列中查找与B2相同的单元格。
参数2—查找范围：“空气质量指数说明!$A$1:$E$7”表示在“空气质量指数说明”工作表（即“编号对照”工作表）中数据表的全部区域（即A1:E7区域）。注意：查找目标一定要在该区域的第一列。
参数3—返回值的列数：“3”表示参数2中工作表的第3列。如果在参数2中找到与参数1相同的单元格，则返回第3列的内容。
参数4—精确OR模糊查找：“1”。最后一个参数是决定函数精确和模糊查找的关键。精确即完全一样，模糊即包含的意思。
第4个参数如果指定值是0或FALSE就表示精确查找，而值为1或TRUE时则表示模糊。注意：在使用VLOOKUP时不要把这个参数给漏掉了，如果缺少这个参数，则会默认为模糊查找。
步骤3：双击I2单元格的填充柄，这样3个工作表中的空气质量指数级别就设置好了；
步骤4：选中“广州”工作表标签，然后按住<Shift>键，再单击“上海”工作表标签，这样就同时选中了3个工作表，在J2中输入函数公式“VLOOKUP(B2,空气质量指数说明!$A$1:$E$7,4,1)”，按回车键；
步骤5：双击J2单元格的填充柄，这样3个工作表中的空气质量指数类别就设置好了。
4．【解析】①步骤1：选中“广州”工作表标签，然后按住<Shift>键，再单击“上海”工作表标签，这样就同时选中了3个工作表；
步骤2：选中A1~J1单元格，然后单击“开始”|“字体”分组中的“填充颜色”按钮，在下拉列表中选中一种颜色（例如：黄色）。单击“字体颜色”按钮，在下拉列表中选中一种字体颜色（例如：红色）；
步骤3：选中A1单元格，然后按<Ctrl+Shift+向右箭头>组合键，然后按<Ctrl+Shift+向下箭头>组合键就选取所有数据单元格；
步骤4：单击“开始”|“字体”|“边框线”按钮，在下拉列表中选中“所有框线”，即可完成框线添加。
②步骤1：单击“北京”工作表，选中其中的C2~D367单元格区域，单击“开始”|“样式”分组中的“条件格式”按钮，在下拉列表中单击“新建规则”；
步骤2：在弹出的对话框中选中“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式：”中填写公式“=C2=MAX($C2:$D2)”，单击“格式”按钮；
【知识拓展】MAX函数
MAX是一个查找函数，在指定区域内查找最大值的函数。本题中“=MAX($C2:$D2)”的含义如下：
参数1—查找区域：在“C2:D2”区域内查找。
步骤3：在打开的对话框中设置“字体”选项卡中的字体颜色为“白色，背景1”，“填充”选项卡中的填颜色为“红色”，单击“确定”按钮，再次单击“确定”按钮。
③步骤1：单击“上海”工作表，选中其中的A2~J367单元格区域，单击“开始”|“样式”分组中的“条件格式”按钮，在下拉列表中单击“新建规则”；
步骤2：在弹出的对话框中选中“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式：”中填写公式“=And($C2>100,$D2>100)”，单击“格式”按钮；
【知识拓展】And函数
And是一个比较函数，所有参数的逻辑值为真时，返回TRUE；只要有一个参数的逻辑值为假，即返回 FALSE。参数必须是逻辑值TRUE或FALSE，或者包含逻辑值的数组（用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量）或引用。
如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。
如果指定的单元格区域内包括非逻辑值，则AND将返回错误值#VALUE!。
步骤3：在打开的对话框中设置“字体”选项卡中的字体颜色为“红色”，单击“确定”按钮，再次单击“确定”按钮。
④步骤1：单击“广州”工作表，选中其中的A2~J367单元格区域，单击“开始”|“样式”分组中的“条件格式”按钮，在下拉列表中单击“新建规则”；
步骤2：在弹出的对话框中选中“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式：”中填写公式“=And(Weekday($A2,2)>5,$B2>Average($B$2:$B$367))”，单击“格式”按钮；
【知识拓展】
（1）WEEKDAY函数
WEEKDAY是返回代表一周中第几天的数值，是一个1到7（或0到6）之间的整数。
语法格式：WEEKDAY(date,type)
其中，参数（date）为日期；参数（type）表示返值是从1到7还是从0到6，以及从星期几开始计数，如省略则返值为1到7，且从星期日起计。
在默认情况下，它的值为1(星期天)到7(星期六)之间的一个整数。
（2）AVERAGE函数
AVERAGE函数是计算平均值函数，在数据库中average使用简写avg，参数可以是数字，或者是涉及数字的名称、数组或引用，如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。
语法格式：AVERAGE(Number1,Number2,…)
返回参数平均值（算术平均）。
步骤3：在打开的对话框中设置“字体”选项卡中的字体颜色为“红色”，单击“确定”按钮，再次单击“确定”按钮。
5．【解析】步骤1：单击“审阅”|“保护”分组中的“保护工作薄”按钮，使之取消选中状态；
步骤2：鼠标左键按住“北京”工作表标签，然后拖动到最左侧，再松开鼠标，这就把北京工作表移动到最前面了；
步骤3：采用同样的方法拖动其他工作表到指定位置。
6．【解析】①步骤1：在“北京”工作表中A列选中任意单元格，然后按<Alt+D>组合键，然后按<P>键，此时打开“数据透视表和数据透视图向导”对话框；
步骤2：选中“请指定待分析数据的数据源类型”中的“多重合并计算数据区域”，然后单击“下一步”；
步骤3：选中“请指定所需的页字段数目”中的“自定义页字段”，然后单击“下一步”；
步骤4：在“选定区域”中选择“A1:B1”然后按<Ctrl+Shift+向下箭头>，选择完成后，显示的是“北京!$A$1:$B$367”，单击“添加”按钮，此时在所有区域中就有了选定的“北京”工作表区域；
步骤5：采用同样的方法添加“上海”和“广州”工作表中的区域；
步骤6：选中“所有区域”中的“北京!$A$1:$B$367”，然后选中“请先指定要建立在数据透视表中的页字段数目”中的“1”，在“字段1”中输入“北京”；
步骤7：采用同样的方法，分别在“广州!$A$1:$B$367”和“上海!$A$1:$B$367”区域的“字段1”中输入“广州”和“上海”；
步骤8：单击“下一步”，选中“新工作表”，单击“完成”按钮，此时就新建了一个数据透视表；
步骤9：将数据透视表名称改为“月均值”，拖动“月均值”工作表拖动到最右边；
步骤10：在行标签列数据区任意单元格上单击鼠标右键，然后单击“组合”命令，打开“组合”对话框，选中步长中的“月”，单击“确定”按钮；
步骤11：单击“数据透视表工具”|“设计”|“布局”分组中的“总计”按钮，在下拉列表中单击“对行和列禁用”命令；
步骤12：在筛选条件中选中“北京”，选中B5:B16单元格区域，在该区域上单击鼠标右键，然后单击“值汇总依据”级联菜单中的“平均值”，设置“开始”|“数字”分组中的类型为“数字”，单击两次“减少小数位”，使之保留整数；
步骤13：根据“数据透视表和数据透视图.png”图示例修改“平均值项:值”改为“月平均值”，行标签改为“月份”，筛选名称改为“城市”，删除列标签名称内容。
②步骤1：选中数据透视表中的任意单元格，单击“数据透视表工具”|“分析”|“工具”分组中的“数据透视图”按钮，此时打开“插入图表”对话框；
步骤2：选中“折线图”中的“带数据标记的折线图”，单击“确定”按钮；
步骤3：拖动缩放图表到C1:N16单元格区域.
③选中纵坐标轴，然后按删除键<Del>，选中网格线再按删除键<Del>，选中“图例”再按删除键<Del>。
④步骤1：选中数据标记，单击“数据透视图工具”|“格式”|“当前所选内容”分组中的“设置所选内容格式”，此时打开“设置数据系列格式”对话框；
步骤2：单击“填充与线条”按钮，单击“标记”按钮，选中“标记选项”中的“内置”单选按钮，设置类型为“圆”，设置大小为“7”，单击“关闭”按钮。
⑤步骤1：在“数据标记”上单击鼠标右键，单击“添加数据标签”；
步骤2：选中数据标签，单击鼠标右键，单击“设置数据标签格式”命令，打开“设置数据标签格式”对话框；
步骤3：选中“数字”选项，在格式代码中输入“[红色][>100]0;0”，然后单击“添加”按钮，单击“关闭”按钮。
⑥选中图表标题，在地址输入栏中输入“=月均值!$B$1”，按回车键即可完成。
⑦取消“数据透视图工具”|“分析”|“显示/隐藏”分组汇总的“字段按钮”选中状态，即可隐藏数据透视图中的所有按钮。
⑧前面①中的步骤9中已经操作完成。
⑨前面①中的步骤12中已经操作完成。
7．【解析】①步骤1：将光标定位于“北京”工作表，单击“页面布局”|“页面设置”分组中的“打印标题”按钮，在打开的“页面设置”对话框中，单击顶端标题后的选择按钮，选中“北京”工作表的第一行，再次单击选择按钮，然后单击“确定”按钮；
步骤2：采用同样的方法设置“上海”和“广州”工作表的重复打印标题行。
②步骤1：选中“北京”、“上海”和“广州”工作表，单击“页面布局”|“页面设置”分组中的“纸张方向”下拉按钮中的“横向”；
步骤2：单击“页面设置”分组中的启动对话框按钮，在“页面”选项卡中，选中“调整为”按钮，设置为1页宽和1页高，单击“确定”按钮。
③步骤1：选中所有工作表，单击“页面布局”|“页面设置”分组中的启动对话框按钮；
步骤2：单击“页眉/页脚”选项卡，单击“自定义页脚”按钮；
步骤3：将光标定位于“中”输入框中，单击“插入页码”按钮，然后在页码后面输入“of”，再单击“插入页数”按钮，单击“确定”按钮，再次单击“确定”按钮。
保存并关闭Excel工作簿。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
