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

视频讲解

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

题库下载

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

题目要求

小马负责本次公务员考试成绩的数据整理工作，按照下列要求帮助小马完成相关数据整理、统计和分析。

1．打开考生文件夹下的素材文档Excel.xlsx（“.xlsx”为文件扩展名），后续操作均基于此文件，否则不得分。操作过程中，不可随意改变工作表中数据的顺序。

2．将工作簿“行政区划代码对照表.xlsx”中的工作表Sheet1复制到工作簿Excel.xlsx中的工作表“名单”左侧；将该工作表名称命名为“行政区划代码”，工作表标签颜色设为标准紫色；以考生文件夹下的图片map.jpg作为该工作表背景，并设置不显示网格线。

3．按照下列要求，对工作表“名单”中的数据进行完善：

① 在“序号”列中输入显示格式为“00001、00002、00003……”的顺序号。

② 在“性别”列的空白单元格中输入“男”。

③ 在“性别”列和“部门代码”列之间插入一个空列，列标题为“地区”。准考证号自左向右数第5、6位为地区代码，依据工作表“行政区划代码”中的对应关系，在新增的“地区”列中输入各准考证号所属的地区名称。

④ 在“部门代码”列中填入相应的部门代码，其对应各准考证号的前3位。

⑤ 将笔试分数、面试分数、总成绩3列设置为如“123.320分”类型的数字格式，并能够正确参与后续数值运算。

⑥ 各准考证号的第4位代表考试类别，按照下列计分规则计算每个人的总成绩：

准考证号的第4位	考试类别	计分方法
1	A类	笔试、面试各占50%
2	B类	笔试占60%、面试占40%

4．按照下列要求，对工作表“名单”中的数据进行格式设置：

① 修改名为“标题1”的单元格样式，字体为“微软雅黑”、字号为14磅、不加粗，并设置跨列居中的水平对齐方式，该样式的其他设置保持不变。

② 为工作表“名单”中的第1行标题文字应用更改后的单元格样式“标题1”，令其在所有数据上方居中排列，并隐藏其批注内容。

③ 正确的准考证号为12位文本，面试分数的范围为0-100之间整数（含本数），对这两列数据进行数据验证，当输入错误时给出提示信息“超出范围请重新输入！”，以标准深红色文本标出已存在的错误数据。

④ 为整个数据区域套用一个表格格式，取消筛选，并将其转换为普通区域。

⑤ 适当加大行高，并自动调整各列列宽至合适的大小。

⑥ 锁定工作表的第1～3行，使之在滚动浏览时始终可见。

⑦ 定义名称，分别以数据区域的首行作为各列的名称。

5．以工作表“名单”为数据源，在工作表“统计分析”中按下列要求对各部门数据进行统计：

① 获取并填写不重复的部门代码及报考部门，并按部门代码的升序进行排列。

② 将各项统计数据填入相应单元格。其中，统计男性人数和女性人数时应使用函数，且应用已定义的名称；笔试最低分数线需按部门统计。

③ 对整个工作表“统计分析”设置条件格式，若单元格非空、且处于偶数行时，该单元格自动以某一浅色填充并添加上下边框线。

④ 设置第G列数字格式显示为百分数，且四舍五入精确到小数点后3位。

6．以工作表“统计分析”为数据源，参照考生文件夹下的图片“图表示例.png”生成图表，并按照如下要求设置图表格式：

① 图表标题与数据上方第1行中的标题内容一致，并可同步变化。

② 适当改变图表样式；设置数据系列的格式，并设置特定的数据系列数据标记为内置“菱形”，大小为10。

③ 调整图例的位置。

④ 坐标轴设置应与“图表示例.png”所示相同。

⑤ 将图表以独立方式嵌入到新工作表“分析图表”中，令其不可移动。

文字解析

第24组【解析】

1．打开考生文件夹下的工作簿文档“Excel.xlsx”。

2．步骤1：双击打开考生文件夹下的“行政区划代码对照表.xlsx”文件，选择“Sheet1”工作表，然后单击鼠标右键，在弹出的快捷菜单中选择“移动或复制”命令，打开“移动或复制工作表”对话框。

步骤2：在“将选定工作表移至工作簿”下拉列表中选择“Excel.xlsx”，在“下列选定工作表之前”下拉列表中选择“名单”，勾选“建立副本”复选框，单击“确定”按钮。关闭“行政区划代码对照表.xlsx”文件。

步骤3：在Excel.xlsx文件中，选中“Sheet1”工作表，单击鼠标右键，在弹出的快捷菜单中选择“重命名”命令，输入“行政区划代码”，按键盘上Enter回车键。

步骤4：在“行政区划代码”工作表标签上，单击鼠标右键，在弹出的快捷菜单中选择“工作表标签颜色”命令，在其下级菜单中选择标准色中的“紫色”。

步骤5：选中“行政区划代码”工作表的A1单元格。单击“页面布局”|“页面设置”分组中的“背景”按钮，打开“插入图片”对话框，单机“从文件”按钮，打开“工作表背景”对话框。

步骤6：找到并选中考生文件夹中的map.jpg图片，单击“插入”按钮。

步骤7：在“页面布局”|“工作表选项”分组中，取消选中的“网格线”中的“查看”复选框。

3．①步骤1：选择“名单”工作表，选中A4单元格，在其中输入1，选中A5单元格，在其中输入2，同时选中A4和A5单元格，然后双击其后面的智能填充句柄，完成序号列序号的智能填充。

步骤2：单击“开始”|“数字”分组中的“数字格式”按钮，弹出“设置单元格格式”对话框，在“数字”选项卡的“分类”中选择“自定义”，在类型中输入“00000”，单击“确定”按钮。

②步骤1：选中D1单元格，单击“数据”|“排序和筛选”分组中的“筛选”按钮，单击筛选按钮（D1单元格中的倒三角按钮），然后只选中“空白”复选框并单击“确定”按钮。

步骤2：选中“D4”单元格并输入“男”，然后拖动D4单元格后面的填充柄到D1777单元格。

步骤3：再次单击“数据”|“排序和筛选”分组中的“筛选”按钮，取消筛选状态。

③步骤1：选中E列，单击右键，在弹出的快捷菜单中选择“插入”命令，即在“性别”和“部门代码”之间插入一个空列，在E3单元格中输入“地区”。

步骤2：题中说明准考证号的第5、6位为地区代码，也就是行政区划代码，我们可以采用取字符函数MID来获取这两位数字。

行政区名称可以通过行政区划代码来查询，这里我们可以使用垂直查询函数（VLOOKUP）来查询。

根据VLOOKUP函数的特性，我们先将“行政区划分代码”表中的“代码-名称”给拆分出来。拆分过程如下：

• 选中“行政区划代码”工作表的（B3:B38）单元格区域，在“数据”|“数据工具”分组中单击“分列”工具。
• 在打开的“文本分列向导-第1步，共3步”对话框中，在“原始数据类型”选项下，选择“分隔符号”，单击“下一步”按钮。
• 在“文本分列向导-第2步，共3步” 对话框中的“分隔符号”中，取消“Tab键”的勾选，选择“其它”，在其后的文本框中输入“-”，单击“下一步”到“第3步”。
• 在“文本分列向导-第3步，共3步”单击“完成”按钮，完成（B3:B38）单元格区域的拆分。

拆分完成后，就可以开始编写查询公式了。

步骤3：选中“名单”工作表的E4单元格，单击“插入函数”按钮，此时弹出“插入函数”对话框。在“在选择函数”中选中“MID”函数，然后单击“确定”按钮。

在弹出的“函数参数”对话框中，第一个参数Text，准备从什么地方提取文本字符串，选中B4单元格；第二个参数Start_num，是开始提取的位置，填入5；第三个参数Num_chars，是提取的字符数，填入2。注意MID函数的结果是文本类型。

步骤4：在编辑栏中选中“MID(B4,5,2)”，在“开始”|“剪切贴”分组中单击“剪贴”按钮，然后单击“插入函数”按钮，此时弹出“插入函数”对话框。在“在选择函数”中选中“VLOOKUP”函数，然后单击“确定”按钮。

步骤5：在弹出的“函数参数”对话框中的第一个参数中，粘贴我们前面计算出来的区域代码值公式（MID(B4,5,2)），MID函数的运算结果作为查找值。

步骤6：在第二个参数中，查找范围，单击“行政区划代码”工作表，并选中该工作表中的B4:C38单元格区域。注意：因为任何一个“代码”都在B4:C38单元格区域里面查找相对应的“地区”，所以这里要将B4:C38单元格区域设置为绝对引用，按键盘上F4，也就是“$B$4:$C$38”。

步骤7：在第三个参数中，要返回查找范围的哪一列。本题是需要返回查找范围的“地区”值，所以此处列号是“2”，因此应填“2”。

步骤8：在第四个参数中，FALSE为精确查找，TRUE或者省略为近似查找。本题中是精确查找，因此参数应填“0”或“FALSE”。

步骤9：4个参数设置完成后，单击“确定”按钮，发现是N/A错误。

这是因为前面拆分时，默认的“代码”列格式是常规，所以这里需要将MID函数返回的值转换成“数值型”才能正确。

单击“插入函数”按钮，返回到VLOOKUP“函数参数”对话框，对第一个参数函数INT(MID(B4,5,2))，将MID函数返回的值转换成“数值型”才能正确。

步骤10：双击“E4”单元格的填充句柄，即可完成整列填充。或者拖动E4单元格的智能填充句柄，一直到E1777，得到全部考生所在的地区。

④步骤1：题中说明准考证号的前3位是部门代码，因此只需要使用截取函数截取前3位即可。这里可以使用LEFT函数。

步骤2：选择F4单元格，单击“插入函数”按钮，此时弹出“插入函数”对话框。在“在选择函数”中选中“MID”函数，然后单击“确定”按钮。

LEFT函数有两个参数，第一个参数Text为要截取的字符串，单击B4单元格；第二个参数Num_chars为截取字符的个数，输入“3”。单击“确定”确定。

步骤3：此时，发现在F4单元格里只显示公式，没有显示公式的计算结果。在“开始”|“数字”分组中，可以看到F4单元格是“文本”格式。可以单击“数字格式”的下拉格式，选择“常规”，光标定位在编辑栏内，单击“ü”，即可显示公式的计算结果。

步骤4：双击F4单元格的填充句柄，即可填充整列。

⑤步骤1：选中J4:J1777单元格区域，单击选中范围左侧的“黄色感叹号”按钮，在打开的下拉列表中单击“转换为数字”命令。

步骤2：继续选中J4:L1777单元格区域，单击“开始”|“数字”分组中的“数字格式”对话框启动器按钮，打开“设置单元格格式”对话框。

步骤3：在“数字”选项卡的“分类”中选择“自定义”，在“类型”中选择输入“0.000分”，单击“确定”按钮。

⑥步骤1：要判断该考试是属于哪类考试，可以使用IF函数通过准考证号来判断。选中L4单元格，单击“插入函数”按钮，在“选择函数”列表框，选中“IF”，单击“确定”按钮，弹出“函数参数”对话框。

步骤2：第一个参数为判断表达式，我们这里需要判断准考证号的第4位是1还是2。因此，我们可以将表达式写成“MID(B4,4,1)=1”，由于MID返回值为字符串，需要转换成整数，这里我们采用INT函数转换“INT(MID(B4,4,1))=1”；

第二个参数是参数一的结果为真时返回的参数，所以第二个参数应填“A类”成绩公式。“A类”考试总成绩为笔试和面试各占50%，总成绩计算公式为：笔试成绩*50%加面试成绩*50%，计算公式为：J4*50%+K4*50%；

第三个参数为参数一的结果为假时返回的参数。由于本题只有两个类别，所以参数三应填“B类”成绩公式。“B类”考试的总成绩为笔试占60%和面试占40%，总成绩计算公式为：笔试成绩*60%加面试成绩*40%，计算公式为：J4*60%+K4*40%。

所以最终计算公式为：

IF(INT(MID(B4,4,1))=1,J4*50%+K4*50%,J4*60%+K4*40%)。

步骤3：双击L4单元格的智能填充柄，完成整列填充。

4.①步骤1：在“开始”|“样式”分组中，单击“单元格样式”右边的向下箭头，在列表中的“标题1”样式上单击鼠标右键，在弹出的快捷菜单中选择“修改”命令，弹出“样式”对话框。

步骤2：单击对话框中的“格式”按钮，在弹出的“设置单元格格式”对话框中，单击“对齐”选项卡，在“文本对齐方式”中的“水平对齐式”中设置为“跨列居中”。

步骤3：单击“字体”选项卡，设置字体为“微软雅黑”、字形为常规，字体大小为14磅、连续两次单击“确定”按钮。

②步骤1：选中A1:L1单元格，单击 “开始”|“样式”分组中“单元格样式”命令，在下拉菜单选择“标题1”命令；

步骤2：在“审阅”|“批注”分组中单击“显示/隐藏批注”按钮，隐藏批注。

③步骤1：选中B4:B1777单元格区域，单击“数据”|“数据工具”分组中的“数据验证”向下的箭头，选择“数据验证”命令，弹出“数据验证”对话框。

步骤2：在“设置”选项卡“验证条件”分组中的“允许”下拉列表框中选择“文本长度”，在“数据”中选中“等于”，在“长度”框中输入“12”。单击“出错警告”选项卡，在“样式”下拉列表中选择“信息”，在“错误信息”文本框中输入“超出范围请重新输入！”，单击“确定”按钮。

步骤3：单击“开始”|“样式”分组中的“条件格式”下拉按钮。在列表中单击“新建规则”命令。在弹出的“新建格式规则”对话框中的“选择规则类型”中选中“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入公式“=LEN(B4)<>12”。单击“格式”按钮，在弹出的“设置单元格格式”对话框中单击“字体”选项卡，设置“颜色”为“红色”。连续两次单击“确定”按钮。

步骤4：选中K4:K1777单元格区域，单击“数据”|“数据工具”分组中的“数据验证”向下的箭头，选择“数据验证”命令，弹出“数据验证”对话框。

步骤5：在“设置”选项卡“验证条件”分组中的“允许”下拉列表框中选择“整数”，在“最小值”框中输入“0”，“最大值”框中输入100。单击“出错警告”选项卡，在“样式”下拉列表中选择“信息”，在“错误信息”文本框中输入“超出范围请重新输入！”，单击“确定”按钮。

步骤6：单击“开始”|“样式”分组中的“条件格式”下拉按钮。在下列表中单击“新建规则”命令。在弹出的“新建格式规则”对话框中的“选择规则类型”中选中“只为包含以下内容的单元格设置格式”，在“只为满足以下条件的单元格设置格式”中第一个选择列表中选择“单元格值”，第二个选择列表中选择“未介于”，第一个输入框中输入“0”，第二个输入框中输入“100”。单击“格式”按钮，在弹出的“设置单元格格式”对话框中单击“字体”选项卡，设置“颜色”为“红色”。连续两次单击“确定”按钮。

④步骤1：选中A3:L1777数据区域，单击“开始”|“样式”分组中的“套用表格格式”向下的箭头，在其中选择一种样式（例如：蓝色，表样式浅色9），在弹出的“套用表格样式”对话框中勾选“表包含标题”复选框，单击“确定”按钮。

步骤2：单击“开始”|“编辑”分组中的“排序和筛选”按钮，在下拉菜单中，取消“筛选”选中状态。

步骤3：保持选中范围不变，单击“表格工具”|“设计”|“工具”分组中的“转换为区域”按钮，弹出对话框“是否将表转换为普通区域？”，点击“是”按钮，将表转换为区域。

⑤步骤1：保持选中范围不变，单击“开始”|“单元格”分组中的“格式”下拉箭头，在其列表中选择“行高”，弹出“行高”对话框，输入行高比原行高值大些的值（例如：14），单击“确定”按钮。

步骤2：保持选中范围不变，单击“开始”|“单元格”分组中的“格式”下拉箭头，在其列表中单击“自动调整列宽”命令。

⑥选中第4行，然后单击“视图”选项卡中“窗口”分组中的“冻结窗格”下拉按钮，在下拉菜单中单击“冻结拆分窗格”命令，即可冻结1~3行，使其始终可见。

⑦步骤1：选择A3:L1777数据区域，单击“公式”|“定义的名称”分组中的“根据所选内容创建”按钮。

步骤2：在弹出的“以选定区域创建名称”对话框中，选中“首行”复选框来指定首行作为各列的名称，单击“确定”按钮。

5.①步骤1：选中“名单”工作表中的F4:G1777区域，右键，在快捷菜单中选择“复制”命令。

步骤2：在“统计分析”工作表中的B5上进行右击，在快捷菜单“粘贴选项”中选择“值”。

步骤3：保持选中B5:C24数据区域不变，单击“数据”|“数据工具”分组中的“删除重复项”按钮，弹出“删除重复项”对话框，单击“确定”按钮，弹出“发现了1754个重复值，已将其删除；保留了20个唯一值。”信息框，单击“确定”按钮。

步骤4：单击“开始”|“编辑”分组中的“排序与筛选”下拉按钮，在其中选择“升序”按钮，完成排序功能。

②步骤1：选中D5单元格，单击“插入函数”按钮，在“选择函数”列表框，选中“COUNTIFS”，单击“确定”按钮，弹出“函数参数”对话框。

第一个参数Criteria_range1为区域范围，填入“部门代码”，第二个参数Criteria1为条件，填入“B5”。第三个参数Criteria_range2为区域范围，填入“性别”，第二个参数Criteria2为条件，填入“女”。

单击“确定”按钮，就得到报考财政部的女生人数。双击D5单元格的填充柄，得到报考其它部门女生的人数。

步骤2：选中E5单元格，单击“插入函数”按钮，在“选择函数”列表框，选中“COUNTIFS”，单击“确定”按钮，弹出“函数参数”对话框。

第一个参数Criteria_range1为区域范围，填入“部门代码”，第二个参数Criteria1为条件，填入“B5”。第三个参数Criteria_range2为区域范围，填入“性别”，第二个参数Criteria2为条件，填入“男”。

单击“确定”按钮，就得到报考财政部的男生人数。双击D5单元格的填充柄，得到报考其它部门男生的人数。

步骤3：选中F5单元格，在其中输入公式“=D5+E5”，按键盘上Enter回车键，G5单元格的填充柄，双击就得到各部门合计面试人数。

步骤4：选中G5单元格，在其中输入公式“=D5/F5”，按键盘上Enter回车键，G5单元格的填充柄，得到女性所占比例。

步骤5：选中H5单元格，单击“插入函数”按钮，“在“选择函数”列表框，选中“IF”，单击“确定”按钮，弹出“函数参数”对话框。

第一个参数为判断表达式，填入“报考部门=C5”。

第二个参数是参数一的结果为真时返回的参数，填入“笔试分数”。

第三个参数为参数一的结果为假时返回的参数，不需要返回任何值。

因为这里数组运算，所以同时按下<Ctrl+Shift+Enter>。

在编辑选中IF(报考部门=C5,笔试分数))，右击，在快捷菜单中选择“剪切”。

单击“插入函数”按钮，“在“选择函数”列表框，选中“MIN”，单击“确定”按钮，弹出“函数参数”对话框。

第一个参数为一组数，在文本框上右击，在快捷菜单中选择“粘贴”，IF函数运算结果作为MIN函数第一个参数的值。

在键盘同时按下<Ctrl+Shift+Enter>，得到报考国家发展和改革部门委员会的考生的最低分数，拖动H5单元格填充柄到H24，得到报考其它部门的考生的最低分数。

③步骤1：选中B4:H24单元格区域，单击“开始”|“样式”分组中的“条件格式”命令，在下拉菜单中，选择“新建规则”命令，打开“新建格式规则”对话框。

步骤2：在“选择规则类型”中选择“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”文本框中输入公式：“=AND((B4<>""),(MOD(ROW(),2)=0))”。

步骤3：公式说明：题中第一个条件是“非空单元格”，所以这填写了“B4<>""”；第二个条件是“偶数行”，ROW函数返回行号的引用，MOD函数是一个求余函数，用于返回两数相除的余数，这里用行号除于2的余数为0，那么说明是偶数行；要同时满足这两个条件，可以用AND函数来实现。

步骤4：单击“格式”按钮，打开“设置单元格格式”对话框，单击“填充”选项卡，在“背景色”下拉列表框中选择一种浅色（例如：深蓝，文字2，淡色60%）。

步骤5：单击“边框”选项卡，选择中“边框”中的“上边框”和“下边框”按钮，单击“确定”按钮。再次单击“确定”按钮。

④步骤1：选中G5:G24单元格区域，单击“开始”|“数字”分组中“数字格式”下拉按钮，在下拉列表中，选中“百分比”选项。

步骤2：在“开始”|“数字”分组中单击“增加小数位数”，使得小数位调整为3位。

6.步骤1：选中C4:E24单元格区域和G4:G24单元格区域。

步骤2：单击“插入”|“图表”分组中的对话框启动器按钮，弹出“插入图表”对话框，选择“所有所表”选项卡，在左侧列表中单击“组合图”命令。

在“为您的数据系列选择图表类型和轴”中，“女性人数”选择“堆积柱形图”；“男性人数”选择“堆积柱形图”；“其中：女性所占比例”后面选中“带数据标记的折线图”，勾选上“次坐标轴”复选框。单击“确定”按钮。

①步骤1：选中图表，然后拖动右下角，将其缩放到合适的大小。

步骤2：单击“图表工具”|“设计”|“图表布局”分组中的“添加图表元素”下拉按钮，在列表中单击“图表标题”级联菜单中的“图表上方”命令。

步骤3：选中“图标标题”，然后在编辑栏中输入要应用的单元格名称，本题中应写成“=统计分析!B1”，然后按回车键即可。

② 步骤1：选中图表，单击“图表工具”|“设计”选项卡中“图表样式”分组中的“其他”按钮，打开样式列表，在样式列表中选中一个和示例图中相似的样式（例如：样式8）。

步骤2：在折线图上单击鼠标右键，单击弹出菜单中的“设置数据系列格式”命令。

步骤3：在右侧“设置数据系列格式”窗格中，单击“填充与线条”按钮，然后单击“标记”按钮，单击“数据标记选项”选项卡，选中“内置”单选按钮，设置类型为“菱形”图标，修改大小为“10”。

步骤4：展开“填充”选项卡，选中“纯色填充”，然后设置和示例图中类似的颜色（例如：标准色“深红”）。

步骤5：单击“线条”按钮，选中“实线”，然后设置一种颜色（例如：标准色“绿色”）。设置一个合适的线型宽度（例如：2磅）。单击“关闭”按钮。

③ 选中图例，在右侧“设置数据系列格式”窗格中，单击“图例选项”，“图例位置“选择“向上”。

④ 步骤1：选中左侧垂直轴，展开“坐标轴选项”，设置边界中的“最小值”为“0”，“最大值”为“330”；在“单位”中设置“主要”为“30”，单击“关闭”按钮。

步骤2：展开“刻度线”，“主要类型”选择“外部”。

步骤3：单击“填充与线条”按钮，展开“线条”，颜色选择“白色，背景1，深色15%”。

步骤4：选中右侧垂直轴，展开“刻度线”，“主要类型”选择“外部”。

⑤ 在图表上单击鼠标右键，在下拉菜单中单击“移动图表”命令。在打开的“移动图表”对话框中选中“新工作表”单选按钮，并在后方输入框中输入“分析图表”，然后单击“确定”按钮。

保存并关闭Excel.xlsx表格。