2. 计算机
  3. 人事部统计员小马负责本次公务员考试成绩数据的整理，按照下列要求帮助小马完成相关的整理、统计和分析工作： 1．将考生文件夹下的工作簿文档“Excel素材．xlsx”另存为“Excel．xlsx”(“．xlsx”为文件扩展名)，之后所有的操作均基于此文

人事部统计员小马负责本次公务员考试成绩数据的整理，按照下列要求帮助小马完成相关的整理、统计和分析工作： 1．将考生文件夹下的工作簿文档“Excel素材．xlsx”另存为“Excel．xlsx”(“．xlsx”为文件扩展名)，之后所有的操作均基于此文

admin2019-09-10  44
问题 人事部统计员小马负责本次公务员考试成绩数据的整理，按照下列要求帮助小马完成相关的整理、统计和分析工作：
    1．将考生文件夹下的工作簿文档“Excel素材．xlsx”另存为“Excel．xlsx”(“．xlsx”为文件扩展名)，之后所有的操作均基于此文件，否则不得分。操作过程中，不可以随意改变工作表中数据的顺序。
    2．将考生文件夹下的工作簿“行政区划代码对照表．xlsx”中的工作表“Sheet1”复制到工作表“名单”的左侧，并重命名为“行政区划代码”、且工作表标签颜色设为标准紫色；以考生文件夹下的图片“map.jpg”作为该工作表的背景，不显示网格线。
    3．按照下列要求对工作表“名单”中的数据进行完善：
     ①在“序号”列中输入格式为“00001、00002、00003……”的顺序号。
    ②在“性别”列的空白单元格中输入“男”。
    ③在“性别”和“部门代码”之间插入一个空列，列标题为“地区”。自左向右准考证号的第5、6位为地区代码，依据工作表“行政区划代码”中的对应关系在“地区”列中输入地区名称。
    ④在“部门代码”列中填入相应的部门代码，其中准考证号的前3位为部门代码。
    ⑤准考证号的第4位代表考试类别，按照下列计分规则计算每个人的总成绩：

    4．按照下列要求对工作表“名单”的格式进行设置：
    ①修改单元格样式“标题1”，令其格式变为“微软雅黑”、14磅、不加粗、跨列居中、其他保持默认效果。为第1行中的标题文字应用更改后的单元格样式“标题1”，令其在所有数据上方居中排列，并隐藏其中的批注内容。
    ②将笔试分数、面试分数、总成绩3列数据设置为形如“123．320分”、且能够正确参与运算的数值类数字格式。
    ③正确的准考证号为12位文本，面试分数的范围为0一100之间整数(含本数)，试检测这两列数据的有效性，当输入错误时给出提示信息“超出范围请重新输入!”，以标准红色文本标出存在的错误数据。
    ④为整个数据区域套用一个表格格式，取消筛选并转换为普通区域。
    ⑤适当加大行高、并自动调整各列列宽至合适的大小。
    ⑥锁定工作表的第1～3行，使之始终可见。
    ⑦分别以数据区域的首行作为各列的名称。
    5．以工作表“名单”的原始数据为依据，在工作表“统计分析”中按下列要求对各部门数据进行统计：
    ①首先获取部门代码及报考部门，并按部门代码的升序进行排列。
    ②将各项统计数据填入相应单元格，其中统计男女人数时应使用函数并应用已定义的名称，最低笔试分数线按部门统计。
    ③对工作表“统计分析”设置条件格式，令其只有在单元格非空时才会自动以某一浅色填充偶数行、且自动添加上下边框线。
    ④令第G列数字格式显示为百分数、要求四舍五入精确到小数点后3位。
    6．以工作表“统计分析”为数据源，生成如表中数据右侧示例所示的图表，要求如下：
    ①图标标题与数据上方第l行中的标题内容一致并可同步变化。
    ②适当改变图表样式、图表中数据系列的格式、调整图例的位置。
    ③坐标轴设置应与示例相同。
    ④将图表以独立方式嵌入到新工作表“分析图表”中，令其不可移动。
选项
答案1．打开考生文件夹下的工作簿文档“Excel素材．xlsx”，然后单击“文件”选项卡中的“另存为”命令，打开“另存为”对话框。在文件名文本框中，将文件名修改为“Excel．xlsx”，单击“保存”按钮。 2．步骤1：双击打开考生文件夹下的“行政区划代码对照表．xlsx”工作表，选择“Sheet1”工作表，然后单击鼠标右键，在弹出的快捷菜单中选择“移动或复制”命令，打开“移动或复制工作表”对话框。 步骤2：在“将选定工作表移至工作簿”下拉列表中选择“Excel．xlsx”，在“下列选定工作表之前”下拉列表中选择“名单”，勾选“建立副本”复选框，单击“确定”按钮。 步骤3：在Excel．xlsx文件中，选中“Sheet1”工作表，单击鼠标右键，在弹出的快捷菜单中选择“重命名”命令，输入“行政区划代码”。 步骤4：选中“行政区划代码”工作表，单击鼠标右键，在弹出的快捷菜单中选择“工作表标签颜色”命令，在其下级菜单中选择标准色中的“紫色”。 步骤5：选中“行政区划代码”工作表的A1单元格。单击“页面布局”分组中“页面设置”分组中的“背景”按钮，打开“工作表背景”对话框。 步骤6：找到并选中考生文件夹中的map.jpg图片，单击“插入”按钮。 步骤7：在“页面布局”选项卡中“工作表选项”分组中，取消选中的“网格线”中的“查看”复选框。 3．①步骤1：选中“名单”工作表的A列，在鼠标右键的快捷菜单中，选择“设置单元格格式”命令，在“数字”选项卡的“分类”中选择“文本”，单击“确定”按钮。 步骤2：选中A4单元格，在其中输入00001，选中A5单元格，在其中输入00002，同时选中A4和A5单元格，然后双击其后面的智能填充句柄，完成序号列序号的智能填充。 ②步骤1：选中D列，单击“数据”选项卡中“排序和筛选”分组中的“筛选”按钮，单击筛选按钮(D1单元格中的倒三角按钮)，然后只选中“空白”复选框并单击“确定”按钮。 步骤2：选中“D4”单元格并输入“男”，然后拖动D4单元格后面的填充柄到D1777单元格。 步骤3：再次单击“数据”选项卡中“排序和筛选”分组中的“筛选”按钮，取消筛选状态。 ③步骤1：选中E列，单击右键，在弹出的快捷菜单中选择“插入”命令，即在“性别”和“部门代码”之间插入一个空列，在E3单元格中输入“地区”。 步骤2：题中说明准考证号的第5、6位为地区代码，我们可以采用取字符函数MID来获取这两位数字。本题中，B4单元格的地区代码获取公式可写成：MID(B4,5,2)。 步骤3：行政区名称可以通过行政区代码来查询，这里我们可以使用垂直查询函数(VLOOKUP)来查询。根据该函数的特性，我们先将“行政区划分代码”表中的“代码-名称”给拆分出来。拆分过程如下： 选中“行政区划代码”工作表的(B3：B38)单元格区域，在“数据”选项卡中“数据工具”分组中单击“分列”工具。 在打开的“文本分列向导-第1步，共3步”对话框中，在“原始数据类型”选项下，选择“分隔符号”，单击“下一步”按钮。 在“文本分列向导-第2步，共3步”对话框中的“分隔符号”中选择“其它”在其后的文本框中输入“-”，单击“下一步”到“第3步”。 在“文本分列向导．第3步，共3步”单击“完成”按钮，完成(B3：B38)单元格区域的拆分。 拆分完成后，就可以开始编写查询公式了。 步骤4：选中“名单”工作表的E4单元格，然后单击“插入函数”按钮，此时弹出“插入函数”对话框。在“在选择函数”中选中“VLOOKUP”函数，然后单击“确定”按钮。 步骤5：在弹出的“函数参数”对话框中的第一个参数中输入要在表格或区域的第1列中搜索到的值，也就是我们前面计算出来的区域代码值公式(MID(B4．，5，2))。因为前面拆分时，默认的“代码”列格式是常规，所以这里需要将MID函数返回的值转换成“数值型”才能正确。这里我们使用函数INT(MID(B4，5，2))。注意：如果前面拆分的是数值格式，那么这里就不用加INT函数。 步骤6：在第二个参数中，是要输入垂直查询区域，这里单击插入按钮“[*”这是“函数参数”对话框会变成简约化状态。此时单击“行政区划代码”工作表，并选中该工作表中的B4：C38单元格区域。再次单击插入按钮“[*]”，返回“函数参数”完整界面。注意：由于要填充其他E列单元格，所以这里要将B4：C38单元格区域设置为绝对引用，也就是“$B$4：$C$38”。 步骤7：在第三个参数中，满足条件的单元格在数组区域(第二个参数)中的序列号。本题是第2列“B列”，所以此处列号是“2”，因此应填“2”。 步骤8：在第四个参数中，是对查找值的匹配情况，0为精确查找，非0位近似查找。本题中要求精确查找。因此参数应填“0”。 步骤9：4个参数设置完成后，单击“确定”按钮即可完成这个单元格的查找填充。 步骤10：在“E4”单元格中输入的公式为：“=VLOOKUP(INT(MID(B4，5，2))，行政区划代码!$B$4：$C$38，2，0)”，得到结果值“北京市”。 步骤11：选中“E4”单元格，然后双击该单元格的填充句柄，即可完成整列填充。或者拖动E4单元格的智能填充句柄，一直到E1777，得到全部考生所在的地区。 ④题中说明准考证号的前3位是部门代码，因此只需要使用截取函数截取前3位即可。这里可以使用LEFT函数。LEFT函数有两个参数，第一个参数为要截取的字符串，第二个参数为截取字符的个数。因此，在F4单元格中输入公式“=LEFT(B4,3)”，即可获取这位考生的部门代码。双击F4单元格的填充句柄，即可填充整列。注意：F列是文本格式，需要先转换成常规或数值格式，再进行公式计算。 ⑤要判断该考试是属于哪类考试，可以使用IF函数通过准考证号来判断。选中L4单元格，单击“插入函数”按钮，选中“IF”，单击“确定”按钮。 第一个参数为判断表达式，我们这里需要判断准考证号的第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％加面试成绩*400，计算公式为：J4*60％+K4*40％。 所以最终计算公式为： IF(INT(MID(B4，4，1))=1，J4*50％+K4*50％，J4*60％+K4*40％)。 双击L4单元格的智能填充柄，完成整列填充。 4．①步骤1：在“开始”选项卡的“样式”分组中，单击“单元格样式”右边的向下箭头，在列表中的“标题1”样式上单击鼠标右键，在弹出的快捷菜单中选择“修改”命令，弹出“样式”对话框。 步骤2：单击对话框中的“格式”按钮，在弹出的“设置单元格格式”对话框中，单击“对齐”选项卡，在“文本对齐方式”中的“水平对齐式”中设置为“跨列居中”。 步骤3：单击“字体”选项卡，设置字体为“微软雅黑”、字形为常规不加粗，字体大小为14磅、连续两次单击“确定”按钮。 步骤4：选中A1：L1单元格，单击选中“开始”选项卡中“样式”分组中的“标题1”样式。在“审阅”选项卡“批注”分组中单击“显示／隐藏批注”按钮，隐藏批注。 ②步骤1：选中J4：J1777单元格区域，单击J4单元格左侧的“黄色感叹号”按钮，在打开的下拉列表中单击“转换为数字”命令。 步骤2：选中J4：L1777单元格区域，单击“开始”选项卡中“数字”分组中的对话框启动器按钮，打开“设置单元格格式”对话框。 步骤3：在“数字”选项卡的“分类”中选择“自定义”，在“类型”中选择输入“0．000分”，单击“确定”按钮。 ③步骤1：选中B4：B1777单元格区域，单击“数据”选项卡中“数据工具”分组中的“数据有效性”向下的箭头，选择“数据有效性”命令，弹出“数据有效性”对话框。 步骤2：在“设置”选项卡“有效性条件”分组中的“允许”下拉列表框中选择“文本长度”，在“数据”中选中“等于”，在“长度”框中输入“12”。单击“出错警告”选项卡，在“样式”下拉列表中选择“信息”，在“错误信息”文本框中输入“超出范围请重新输入!”，单击“确定”按钮。 步骤3：单击“开始”选项卡中“样式”分组中的“条件格式”下拉按钮。在列表中单击“新建规则”命令。在弹出的“新建格式规则”对话框中的“选择规则类型”中选中“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入公式“LEN(B4)＜＞12”。单击“格式”按钮，在弹出的“设置单元格格式”对话框中单击“字体”选项卡，设置“颜色”为“红色”。连续两次单击“确定”按钮。 步骤4：选中K4：K1777单元格区域，单击“数据”选项卡“数据工具”分组中的“数据有效性”向下的箭头，选择“数据有效性”命令，弹出“数据有效性”对话框。 步骤5：在“设置”选项卡“有效性条件”分组中的“允许”下拉列表框中选择“整数”，在“最小值”框中输入“0”，“最大值”框中输入100。单击“出错警告”选项卡，在“样式”下拉列表中选择“信息”，在“错误信息”文本框中输入“超出范围请重新输入!”，单击“确定”按钮。 步骤6：单击“开始”选项卡中“样式”分组中的“条件格式”下拉按钮。在下列表中单击“新建规则”命令。在弹出的“新建格式规则”对话框中的“选择规则类型”中选中“只为包含以下内容的单元格设置格式”，在“只为满足以下条件的单元格设置格式”中第一个选择列表中选择“单元格值”，第二个选择列表中选择“未介于”，第一个输入框中输入“0”，第二个输入框中输入“100”。单击“格式”按钮，在弹出的“设置单元格格式”对话框中单击“字体”选项卡，设置“颜色”为“红色”。连续两次单击“确定”按钮。 ④步骤1：选中A3：L1777数据区域，单击“开始”选项卡中“样式”分组中的“套用表格格式”向下的箭头，在其中选择一种样式(例如：表样式浅色样式9)，在弹出的“套用表格样式”对话框中勾选“包含标题”复选框，单击“确定”按钮。 步骤2：在选中整张表格情况下，单击“表格工具”|“设计”选项卡中“工具”分组中的“转换为区域”按钮，将表转换为区域。 ⑤步骤1：选中整张表格，单击“开始”选项卡“单元格”分组中的“格式”下拉箭头，在其列表中选择“行高”，弹出“行高”对话框，输入行高比原行高值大些的值(例如：14)，单击“确定”按钮。 步骤2：选中整张表格，单击“开始”选项卡中“单元格”分组中的“格式”下拉箭头，在其列表中单击“自动调整列宽”命令。 ⑥选中第4行，然后单击“视图”选项卡中“窗口”分组中的“冻结窗格”下拉按钮，在下拉列表中单击“冻结拆分窗格”命令，即可冻结1—3行，使其始终可见。 ⑦选择A3：L1777数据区域，单击“公式”选项卡中“定义的名称”分组中的“根据所选内容创建”按钮。在弹出的“以选定区域创建名称”对话框中，选中“首行”复选框来指定首行作为各列的名称，单击“确定”按钮。 5．①选中“统计分析”工作表中的“B5”单元格，在其编辑框中输入公式：“=INDEX(名单!$F$4：$F$1777,SMALL(IF(MATCH(名单!$F$1：$F$1778&””．名单!$F$1：$F$1778&“”，)=ROW($F$1：$F$1778),ROW($F$1：$F$1778)，1778)，ROW(F3)))&”””，同时按下输入(注意：由于公式中有数组，因此必须要使用这3个组合键，否则会出错)。选中这个单元格的智能填充句柄，拖动到B24单元格，就获得所有的部门代码。 本题公式的解题思路如下： 首先使用MATCH函数返回数组中每个元素第一次出现的位置； 其次用ROW函数返回数组中每个元素在数组中所处的位置，与第1步返回的数组逐一比对，如果相同说明该元素是首次出现，标识相应的行号；如果不同说明是重复出现，标识一个大于表的最大行号即可，本表中最大行号是1777，这里用“1778”： 然后使用IF函数生成一个由不重复元素所在的行号与“1778”组成的数组： 再使用SMALL函数在第3步返回的数组中依次提取第1个值、第2个值、……第N个值： 最后使用INDEX函数提取相应位置上的数据，即列数据中的不重复值。 同理选中“统计分析”工作表的C5单元格，在其编辑框中输入公式：“=INDEX(名单!$G$4：$G$1 777,SMALL(IF(MATCH(名单!$G$1：$G$1778&””，名单!$G$1：$G$1 778&””，)=ROW($G$1：$G$1778)，ROW($G$1：$G$1778)，1778)，ROW(G3)))&”””，同时按下，选中这个单元格的智能填充句柄，拖动直到C24单元格，就获得所有的报考部门。 选中B5：C24数据区域，单击“开始”选项卡“编辑”分组中的“排序与筛选”下拉按钮，在其中选择“升序”按钮，完成排序功能。 注意：由于需要排序，这里以上两个公式最好将最后一个函数“ROW”的参数改成绝对引用，否则排序后，会自动返回排序前的状态导致排序失败。 小提示；这里也可以通过人工比较并输入部门代码和报考部门信息(题目没有要求一定要用函数公式)。 ②步骤1：选中D5单元格，在其中输入公式“COUNTIFS(部门代码，B5，性别，”女”)”，同时按下，就得到报考财政部的女生人数。拖动D5单元格的智能句柄到D24，得到报考其它部门女生的人数。 步骤2：选中E5单元格，在其中输入公式“=COUNTIFS(部门代码，B5，性别，”男”)”，同时按下，就得到报考财政部的男生人数。拖动E5单元格的智能句柄到E24，得到报考其它部门男生的人数。 步骤3：选中F5单元格，在其中输入公式“=D5+E5”，就得到各部门合计面试人数。选中G5单元格，在其中输入公式“=D5／F5”，选中G5单元格的智能填充句柄，拖动其到G24，得到其他部门女性所占比例的数据。 步骤4：选中H5单元格，在其中输入公式“=MIN(IF(报考部门=C5，总成绩))”，同时按下，得到报考财政部的考生的最低分数，拖动智能句柄到H24，得到报考其它部门的考生的最低分数。 ③步骤1：选中B4：H24单元格区域，单击“开始”选项卡“样式”分组中的“条件格式”向下箭头，在其列表中选择“突出显示单元格规则”“其它规则”，打开“新建格式规则”对话框。 步骤2：在“选择规则类型”中选择“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式” 文本框中输入公式：“=AND((B4＜＞””)，(MOD(ROW()，2)=0))”。 步骤3：公式说明：ROW函数返回一个引用的行号，MOD函数是一个求余函数，用于返回两数相除的余数，这里用行号除于2的余数为0，那么说明是偶数行；题中还有一个条件是“非空单元格”，所以这填写了“B4＜＞”””。要同时满足这两个条件，可以用AND函数来实现。 步骤4：单击“格式”按钮，打开“设置单元格格式”对话框，单击“填充”选项卡，在“背景色”下拉列表框中选择一种浅色(例如：深蓝，文字2，淡色60％)。 步骤5：单击“边框”选项卡，选择中“边框”中的“上边框”和“下边框”按钮，单击“确定”按钮。再次单击“确定”按钮。 ④步骤1：选中G5：G24单元格区域，单击“开始”选项卡中“数字”分组中“数字格式”下拉按钮，在下拉列表中，选中“百分比”选项。 步骤2：在“开始”选项卡中“数字”分组中单击“增加小数位数”和“减少小数位数”按钮，使得小数位调整为3位。 6．①步骤1：选中C4：E24单元格区域和G4：G24单元格区域。 步骤2：单击“插入”选项卡中“图表”分组中的簇状柱形图下拉按钮，在下拉列表中单击“堆积柱形图”命令。 步骤3：单击“图表工具”|“布局”选项卡中“标签”分组中的“图表标题”按钮，在下拉列表中选择“图表上方”。 步骤4：选中“图标标题”，然后在编辑栏中输入要应用的单元格名称，本题中应写成“统计分析!B1”，然后按回车键即可。 ②步骤1：选中图表，然后拖动右下角，将其缩放到合适的大小。 步骤2：选中图表，单击“图表工具”|“设计”选项卡中“图表样式”分组中的“其他”按钮，打开样式列表，在样式列表中选中一个和示例图中相似的样式(例如：样式26)。 步骤3：选中图例，单击“图表工具”|“布局”选项卡中“标签”分组中的“图例”下拉按钮，在下拉列表中，选择“在顶部显示图例”。 步骤4：选中“其中，女性所占比例”图例，单击“图表工具”|“设计”选项卡中“类型”分组中的“更改图表类型”按钮，在弹出的“更改图表类型”对话框中选中一种散点图(例如：带直线和数据标识的散点图)，单击“确定”按钮。 步骤5：单击“图表工具”|“布局”选项卡中“坐标轴”分组中的“坐标轴”下拉按钮，在下拉列表中选中“次要横坐标轴”|“无”。 步骤6：在折线图上单击鼠标右键，单击弹出菜单中的“设置数据序列格式”命令。 步骤7：在对话框中选中“数据标记选项”，单击选中“数据标记类型”中的“内置”单选按钮，设置类型为“圆点”图标，修改合适的大小(例如：3)。 步骤8：单击“数据标记填充”，单击选中“数据标记填充”下的“纯色填充”，然后设置和示例图中类似的颜色(例如：标准色“深红”)。 步骤9：单击“线条颜色”，选中线条颜色下的“实线”，然后设置一种颜色(例如：标准色“绿色”)。 步骤10：单击“线型”，设置一个合适的线型宽度(例如：2磅)。单击“关闭”按钮。 ③步骤1：单击“图表工具”|“布局”选项卡中“坐标轴”分组中的“坐标轴”下拉按钮，在下拉列表中选中“主要纵坐标轴”|“其他主要纵坐标轴选项”，弹出“设置坐标轴格式”对话框中。 步骤2：单击“坐标轴选项”，单击选中“最小值”后面的“固定”单选按钮并设置值为0；单击选中“最大值”后面的“固定”单选按钮并设置值为“330”；单击选中“主要刻度单位”后面的“固定”单选按钮并设置值为“30”，单击“关闭”按钮。 ④在图表上单击鼠标右键，在下拉菜单中单击“移动图表”命令。在打开的“移动图表”对话框中选中“新工作表”单选按钮，并在后方输入框中输入“分析图表”，然后单击“确定”按钮。 保存并关闭Excel．xlsx表格。
解析
转载请注明原文地址:https://jikaoti.com/ti/I55iFFFM
本试题收录于： 二级MS Office高级应用题库NCRE全国计算机二级分类
0

二级MS Office高级应用

NCRE全国计算机二级

相关试题推荐
随机试题
最新回复(0)