真题练习41-Excel电子表格-全国计算机等级考试二级MS Office高级应用与设计考试【汪老师的个人课堂】

视频讲解

真题练习41-Excel电子表格_哔哩哔哩_bilibili

题库下载

全国计算机等级考试题库下载（用电脑下载安装）：请点击

题目要求

第41套

国家统计局每10年进行一次全国人口普查，以反映全国人口的增长速度及规模。按照下列要求完成对第五次、第六次人口普查数据的统计分析工作：

1．打开考生文件夹下的空白文档Excel.xlsx（“.xlsx”为文件扩展名），后续的操作均基于此文件，否则不得分。

2．将工作表Sheet1更名为“第六次普查数据”，在该工作表中按下列要求进行操作：

① 浏览考生文件夹下的网页“第六次全国人口普查公报.htm”，将其中的表格“2010年第六次全国人口普查主要数据”自单元格A1开始导入到工作表“第六次普查数据”中。不得改变所导入数据的排列顺序。

② 取消所导入的数据表与外部数据源的所有连接关系。

③ 删除C列内容，仅保留A、B两列数据。

④ 将B列中的数值统一缩小10000倍，并四舍五入为整数且以显示精度参与后续计算；同时将单元格B1中的单位改为“万人”。

⑤ 将该数据表格添加到数据模型，数据模型表名更改为“2010年”。

3．按照下列要求创建查询并在查询编辑器窗口中对数据进行转换整理：

① 从工作簿“第五次全国人口普查公报.xlsx”的工作表Sheet1中获取数据创建一个名称为“2000年”新查询。不得改变导入数据的排列顺序。

② 将地区与人口数分成两列显示（注意删除不可见字符并适当调整数据格式）。

③ 将标题依次改为“地区”、“2000年人口数（万人）”。

④ 删除最下面的3个空行。

⑤ 将查询加载回工作簿Excel.xlsx的新工作表“第五次普查数据”中。

⑥ 将查询表添加到数据模型，并将数据模型表名更改为“2000年”。

4．基于数据模型表“2000年”和“2010年”，按照下列要求及相关提示进行操作：

① 在数据模型表“2000年”和“2010年”之间建立关系。

② 自新工作表“透视分析”的单元格B3开始生成数据透视表。参考示例文档“透视分析示例.png”调整透视表布局、按示例更改所有字段标题。

③ 透视表中只显示排除港澳台3个地区后的大陆人口（包括大陆31个省、自动区、直辖市以及现役军人和难以确定常住地的人口）。

④ 在透视表中显示的各年大陆人口比重是指除港澳台之外每个地区人口占大陆总人口数的比重。

⑤ “人口增长数”和“人口增长率”两个计算字段的计算公式分别为：

人口增长数=2010年人口数-2000年人口数；

人口增长率=人口增长数÷2000年人口数。

⑥ 将透视表按照网页“第六次全国人口普查公报.htm”中所示数据表的地区顺序进行排序。

⑦ 将有关人口数的数据列格式设置为带千分位的整数，并改变数据透视表样式。

5．将工作簿“统计指标.xlsx”中的工作表“统计数据”移动到正在编辑的文档Excel.xlsx中所有工作表的最右侧，并在相应单元格内填入统计结果。

文字解析

第41组

1．【解析】打开考生文件夹下的素材文档“Excel.xlsx”。

2．【解析】步骤1：双击“Sheet1”工作表标签，然后将名称改为“第六次普查数据”，

① 选中A1单元格，单击【数据】|【获取外部数据】分组中的“自其它来源”下拉按钮，在列表中单击“来自XML数据导入”命令，打开“选取数据源”对话框；

步骤2：在文件类型中选中“所有文件”，找到考生文件夹下的“第六次全国人口普查公报.htm”，单击“打开”按钮，打开“新建web查询”对话框；

步骤3：选中表对象，单击“导入”按钮，打开“导入数据”对话框，单击“确定”按钮。

② 步骤1：单击【数据】|【链接】分组中的“链接”按钮，打开“工作簿连接”对话框；

步骤2：选中其中的所有链接，单击“删除”按钮，单击“确定”按钮，单击“关闭”按钮。

③ 选中C列，按<Del>键删除内容。

④ 步骤1：在其他空白单元格输入10000，并复制这个单元格，然后选中B2:B37单元格区域，在选中的区域中单击鼠标右键，然后单击“选择性粘贴”命令，打开“选择性粘贴”对话框，选中“运算”中的“除”按钮，单击“确定”按钮，删除之前输入的“10000”；

步骤2：选中B2:B37单元格区域，在C2单元格中输入：=ROUND(B2,0)，双击C2单元格的智能填充柄完成其他行填充；

步骤3：复制C2:C37单元格内容，选中B2单元格，单击【开始】|【剪贴板】分组中的“粘贴”下拉按钮，在列表中单击“值”按钮；

步骤4：设置B2:B37单元格区域格式为数值型、0位小数位，删除C列内容，将B1单元格内容修改为“2010年人口数（万人）”。

⑤ 步骤1：全选数据区域单元格，单击【开始】|【样式】分组中的“套用表格格式”下拉按钮，在列表中选中一种表格格式，单击“确定”按钮，单击“是”按钮；

步骤2：单击【Power Pivot】|【表格】分组中的“添加到数据模型”按钮，打开“Power Pivot for Excel”对话框；

步骤3：在左下方将表名称改为“2010年”，单击“保存”按钮，单击“关闭”按钮。

3．【解析】① 步骤1：单击【数据】|【获取和转换】分组中的“新建查询”下拉按钮，在列表中单击“从文件”级联菜单中的“从工作簿”命令，打开“导入数据”对话框；

步骤2：在对话框中找到考生文件夹下的“第五次全国人口普查公报.xlsx”，单击“导入”按钮，打开“导航器”对话框；

步骤3：选中显示选项中的“Sheet1”，单击“加载”按钮，单击【查询工具】|【查询】|【编辑】分组中的“属性”按钮，打开“查询属性”对话框，将名称改为“2000年”，单击“确定”按钮；

② 步骤1：单击【查询工具】|【查询】|【编辑】分组中的“编辑”按钮，打开“2000年 – Power Query编辑器”对话框；

步骤2：单击【主页】|【转换】分组中的“拆分列”下拉按钮，在列表中单击“按照从非数字到数字的转换”命令，拆分成两列；

步骤3：选中第一列，单击【主页】|【转换】分组中的“拆分列”下拉按钮，在列表中单击“按分隔符”命令，打开“按分隔符拆分列”对话框，不修改默认属性，直接单击“确定”按钮；

步骤4：选中空白列，单击【主页】|【管理列】分组中的“删除列”按钮，单击“关闭并上载至”.

③ 步骤1：选中新生成的工作表，单击【表格工具】|【设计】|【工具】分组中的“转换为区域”按钮；

步骤2：选种第一行并删除，将新A1单元格内容修改为“地区”，B1单元格内容修改为“2000年人口数（万人）”。

④ 选中最后三行并删除。

⑤ 双击查询工作表名称，修改为“第五次普查数据”。

⑥ 全选“第五次普查数据”工作表数据区域，单击【开始】|【样式】分组中的“套用表格格式”下拉按钮，在列表中选中一种表格格式，单击“确定”按钮，单击“是”按钮；

步骤2：单击【Power Pivot】|【表格】分组中的“添加到数据模型”按钮，打开“Power Pivot for Excel”对话框；

步骤3：在左下方将表名称改为“2000年”，单击“保存”按钮，单击“关闭”按钮。

4．【解析】① 步骤1：单击【设计】|【数据工具】分组中的“管理数据模型”按钮，打开“Power Pivot for Excel”对话框；

步骤2：单击【主页】|【查看】分组中的“关系图视图”按钮，拖动“2010年”中的“地区”到“2000年”中的“地区”上松开鼠标，即可建立关系，单击“关闭”按钮。

② 步骤1：单击“新工作表”按钮插入一个新的工作表，将名称改为“透视分析”，选中B3单元格；

步骤2：打开考生文件夹下的“透视分析示例.png”图，单击【插入】|【表格】分组中的“数据透视表”按钮，打开“创建数据透视表”对话框；

步骤3：选中“使用此工作簿的数据模型”，单击“确定”按钮。

③ 单击“2000年”中的“地区”的向下按钮，在列表中取消“澳门特别行政区”、“台湾地区”和“香港特别行政区”复选框的选中；

④ 步骤1：拖动“2000年”中的“地区”到“行”中；

步骤2：拖动“2000年人口数（万人）”到“值”中，再次“2000年人口数（万人）”到“值”中；

步骤3：在第二次拖拉的2000人口数上单击鼠标右键，然后单击“值显示方式”分组中的“总计的百分比”命令；

步骤4：拖动“2010年”中的“2010年人口数（万人）”到“值”中，再次“2010年人口数（万人）”到“值”中；

步骤5：在第二次拖拉的2010人口数上单击鼠标右键，然后单击“值显示方式”分组中的“总计的百分比”命令。

⑤ 步骤1：单击【Power Pivot】|【计算】分组中的“度量值”下拉按钮，在列表中单击“新建度量值”按钮，打开“度量值”对话框；

步骤2：在公式中输入“=SUM('2010年'[2010年人口数（万人）])-SUM('2000年'[2000年人口数（万人）])”，单击“确定”按钮；

步骤3：再次单击“新建度量值”按钮，在公式中输入“=(SUM('2010年'[2010年人口数（万人）])-SUM('2000年'[2000年人口数（万人）]))/SUM('2000年'[2000年人口数（万人）])”，单击“确定”按钮；

步骤4：拖动“度量值1”到“值”中，拖动“度量值2”到“值”中；

步骤5：双击“行标签”猛如虎膝盖名称为“地区”，双击C3单元格打开“值字段设置”对话框，将自定义名称改为“2000年人口数”，单击“确定”按钮，采用同样方法为后面几个标题修改名称。

⑥ 步骤1：选中“第六次普查数据”工作表中的“A2:A37”单元格区域，单击【文件】|【选项】按钮，打开“Excel选项”对话框；

步骤2：在“高级”选项卡中单击“编辑自定义列表”按钮，打开“自定义序列”对话框，单击“导入”按钮，单击“添加”按钮，然后单击“确定”按钮；

步骤3：返回到“透视分析”工作表中，单击“地区”后面的按钮，在弹出菜单中单击“其他排序选项”按钮，打开“排序(地区)”对话框；

步骤4：选中“升序排序”按钮，在下面的列表中选中“地区”，单击“其他选项”按钮，打开“其他排序选项”对话框，取消“每次更新报表时自动排序”复选框，在“主关键字排序顺序”中选中前面步骤2添加的排序，单击“确定”按钮。

⑦ 步骤1：选中C4:C37、E4:E37和G4:G37单元格区域，单击【开始】|【数字】分组中的对话框启动器，打开“设置单元格格式”对话框；

步骤2：在分类中选中“数值”，设置小数位数为“0”，选中“使用千位分隔符”复选框，在负数中选中“-1,23”，单击“确定”按钮；

步骤3：选中D4:D37、F4:F37和H4:H37单元格区域，单击【开始】|【数字】分组中的对话框启动器，打开“设置单元格格式”对话框；

步骤4：在分类中选中“百分比”，设置小数位数为“2”，单击“确定”按钮；

步骤5：选中透视表数据区域，单击【数据透视表工具】|【设计】|【数据透视表样式】分组中的一个样式（例如：浅蓝，数据透视表样式中等深色20）。

5．【解析】① 步骤1：打开考生文件夹下的素材文档“统计指标.xlsx”。

步骤2：在“统计数据”工作表标签上单击鼠标右键，然后单击“移动或复制”命令，打开“移动或复制工作表”对话框，在工作簿中选中“Excel.xlsx”，在“下列选定工作表之前”中选中“(移至最后)”，然后单击“确定”按钮；

步骤3：在C3单元格中输入公式：=SUM(表2[2000年人口数（万人）])；

步骤4：在C4单元格中输入公式：=SUM(表1[2010年人口数（万人）])；

步骤5：在C4单元格中输入公式：=D3-C3；

步骤6：在C5单元格中输入公式：=MAX(透视分析!C4:C36)得出结果“9256”，返回“透视分析”工作表，查看可知这是河南省，将C5单元格内容改为为“河南省”

步骤7：采用同样的方法，在D5中输入“广东省”，C6单元格中输入“西藏自治区”，D6单元格中输入“西藏自治区”，D7

单元格中输入“北京市”，D8单元格中输入“重庆市”；

步骤8：在D9单元格中输入公式：=COUNTIF(透视分析!G4:G34,"<0")。

保存并关闭Excel.xlsx工作簿。