2023年全国计算机等级考试二级MSOffice高级应用Excel部分第110套.docx

全国计算机等级考试 二级MS Office高级应用
（Excel部分：第1-10套）
【第1套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须保存在考生文献夹下
小李今年毕业后，在一家计算机图书销售公司担任市场部助理，重要的工作
职责是为部门经理提供销售信息的分析和汇总。
请你根据销售数据报表（"Excel.xlsx" 文献），按照如下规定完毕记录和分析工作：
1. 请对"订单明细表"工作表进行格式调整，通过套用表格格式方法将所有的销售记录调整为一致的外观格式，并将"单价"列和"小计"列所包含的单元格调整为"会计专用"（人民币）数字格式。
2. 根据图书编号，请在"订单明细表"工作表的"图书名称"列中，使用VLOOKUP函数完毕图书名称的自动填充。"图书名称"和"图书编号"的相应关系在"编号对照"工作表中。
3. 根据图书编号，请在"订单明细表"工作表的"单价"列中，使用VLOOKUP函数完毕图书单价的自动填充。"单价"和"图书编号"的相应关系在"编号对照"工作表中。
4. 在"订单明细表"工作表的"小计"列中，计算每笔订单的销售额。
5. 根据"订单明细表"工作表中的销售数据，记录所有订单的总销售金额，并将其填写在"记录报告"工作表的B3单元格中。
6. 根据"订单明细表"工作表中的销售数据，记录《MS Office高级应用》图书在2023年的总销售额，并将其填写在"记录报告"工作表的B4单元格中。
7. 根据"订单明细表"工作表中的销售数据，记录隆华书店在2023年第3季度的总销售额，并将其填写在"记录报告"工作表的B5单元格中。
8. 根据"订单明细表"工作表中的销售数据，记录隆华书店在2023年的每月平均销售额（保存2位小数），并将其填写在"记录报告"工作表的B6单元格中。
9. 保存"Excel.xlsx"文献。
【第2套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须保存在考生文献夹下
小蒋是一位中学教师，在教务处负责初一年级学生的成绩管理。由于学校地处偏远地区，缺少必要的教学设施，只有一台配置不太高的PC可以使用。他在这台电脑中安装了Microsoft Office，决定通过 Excel 来管理学生成绩，以填补学校缺少数据库管理系统的局限性。现在，第一学期期末考试刚刚结束，小蒋将初一年级三个班的成绩均录入了文献名为"学生成绩单.xlsx"的Excel工作簿文档中。
请你根据下列规定帮助小蒋老师对该成绩单进行整理和分析：
1. 对工作表"第一学期期末成绩"中的数据列表进行格式化操作：将第一列"学号"列设为文本，将所有成绩列设为保存两位小数的数值；适当加大行高列宽，改变字体、字号，设立对齐方式，增长适当的边框和底纹以使工作表更加美观。
2. 运用"条件格式"功能进行下列设立：将语文、数学、英语三科中不低于110 分的成绩所在的单元格以一种颜色填充，其他四科中高于95分的成绩以另一种字体颜色标出，所用颜色深浅以不遮挡数据为宜。
3. 运用sum和average函数计算每一个学生的总分及平均成绩。
4. 学号第 3、4 位代表学生所在的班级，例如："120235"代表12级1班5号。请通过函数提取每个学生所在的班级并按下列相应关系填写在"班级"列中：
"学号"的3、4位 相应班级
01 1班
02 2班
03 3班
5. 复制工作表"第一学期期末成绩"，将副本放置到原表之后；改变该副本表标签的颜色，并重新命名，新表名需包含"分类汇总"字样。
6. 通过度类汇总功能求出每个班各科的平均成绩，并将每组结果分页显示。
7. 以分类汇总结果为基础，创建一个簇状柱形图，对每个班各科平均成绩进行比较，并将该图表放置在一个名为"柱状分析图"新工作表中。
【第3套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须保存在考生文献夹下。
在考生文献夹下打开工作簿Excel.xlsx，按照规定完毕下列操作并以该文献名(Excel.xlsx)保存工作簿。
某公司拟对其产品季度销售情况进行记录，打开"Excel.xlsx"文献，按以下规定操作：
（1）分别在"一季度销售情况表"、"二季度销售情况表"工作表内,计算 "一季度销售额"列和"二季度销售额"列内容，均为数值型，保存小数点后0位。
　（2） 在 "产品销售汇总图表"内,计算"一二季度销售总量"和"一二季度销售总额"列内容，数值型，保存小数点后0位；在不改变原有数据顺序的情况下，按一二季度销售总额给出销售额排名。
　（3）选择"产品销售汇总图表"内A1：E21 单元格区域内容，建立数据透视表，行标签为产品型号，列标签为产品类别代码，求和计算一二季度销售额的总计，将表置于现工作表G1为起点的单元格区域内。
【第4套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须都保存在考生文献夹下。
中国的人口发展形势非常严峻，为此国家记录局每2023进行一次全国人口普查，以掌握全国人口的增长速度及规模。按照下列规定完毕对第五次、第六次人口普查数据的记录分析：
（1） 新建一个空白Excel文档，将工作表sheet1更名为"第五次普查数据"，将sheet2更名为"第六次普查数据"，将该文档以"全国人口普查数据分析.xlsx"为文献名进行保存。
（2） 浏览网页"第五次全国人口普查公报.htm"，将其中的"2023年第五次全国人口普查重要数据"表格导入到工作表"第五次普查数据"中；浏览网页"第六次全国人口普查公报.htm"，将其中的"2023年第六次全国人口普查重要数据"表格导入到工作表"第六次普查数据"中（规定均从A1单元格开始导入，不得对两个工作表中的数据进行排序）。
（3） 对两个工作表中的数据区域套用合适的表格样式，规定至少四周有边框、且偶数行有底纹，并将所有人口数列的数字格式设为带千分位分隔符的整数。
（4）将两个工作表内容合并，合并后的工作表放置在新工作表"比较数据"中（自A1单元格开始），且保持最左列仍为地区名称、A1单元格中的列标题为"地区"，对合并后的工作表适当的调整行高列宽、字体字号、
边框底纹等，使其便于阅读。以"地区"为关键字对工作表"比较数据"进行升序排列。
（5）在合并后的工作表"比较数据"中的数据区域最右边依次增长"人口增长数"和"比重变化"两列，计算这两列的值，并设立合适的格式。其中：人口增长数=2023年人口数-2023年人口数；比重变化=2023年比重-2023年比重。
（6） 打开工作簿"记录指标.xlsx"，将工作表"记录数据"插入到正在编辑的文档"全国人口普查数据分析.xlsx"中工作表"比较数据"的右侧。
（7） 在工作簿"全国人口普查数据分析.xlsx"的工作表"比较数据"中的相应单元格内填入记录结果。
（8） 基于工作表"比较数据"创建一个数据透视表，将其单独存放在一个名为"透视分析"的工作表中。透视表中规定筛选出2023年人口数超过5000万的地区及其人口数、2023年所占比重、人口增长数，并按人
口数从多到少排序。最后适当调整透视表中的数字格式。（提醒：行标签为"地区"，数值项依次为2023年人口数、2023年比重、人口增长数）。
【第5套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须都保存在考生文献夹下。
在考生文献夹下打开文档EXCEL.XLSX。
【背景素材】
财务部助理小王需要向主管报告2023年度公司差旅报销情况，现在请按照如下需求，在EXCEL.XLSX文档中完毕工作：
（1） 在"费用报销管理"工作表"日期"列的所有单元格中，标注每个报销日期属于星期几，例如日期为"2023年1月20日"的单元格应显示为 "2023年1月20日 星期日"，日期为"2023年1月21日"的单元格应显 示为"2023年1月21日 星期一"。
（2） 假如"日期"列中的日期为星期六或星期日，则在"是否加班"列的单元格中显示"是"，否则显示"否"（必须使用公式）。
（3） 使用公式记录每个活动地点所在的省份或直辖市，并将其填写在"地区"列所相应的单元格中，例如"北京市"、"浙江省"。
（4） 依据"费用类别编号"列内容，使用VLOOKUP函数，生成"费用类别"列内容。对照关系参考"费用类别"工作表。
（5） 在"差旅成本分析报告"工作表B3单元格中，记录2023年第二季度发生在北京市的差旅费用总金额。
　　（6） 在"差旅成本分析报告"工作表B4单元格中，记录2023年员工钱顺卓报销的火车票费用总额。
（7） 在"差旅成本分析报告"工作表B5单元格中，记录2023年差旅费用中，飞机票费用占所有报销费用的比例，并保存2位小数。
（8） 在"差旅成本分析报告"工作表B6单元格中，记录2023年发生在周末（星期六和星期日）的通讯补贴总金额。
【第6套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须保存在考生文献夹下

文涵是大地公司的销售部助理，负责对全公司的销售情况进行记录分析，并将结果提交给销售部经理。年终，她根据各门店提交的销售报表进行记录分析。
打开"计算机设备全年销量登记表.xlsx"，帮助文涵完毕以下操作：
1. 将"sheet1"工作表命名为"销售情况"，将"sheet2"命名为"平均单价"。
2. 在"店铺"列左侧插入一个空列，输入列标题为"序号"，并以001、002、003 ……的方式向下填充该列到最后一个数据行。
3. 将工作表标题跨列合并后居中并适当调整其字体、加大字号，并改变字体颜色。适当加大数据表行高和列宽，设立对齐方式及销售额数据列的数值格式（保存2位小数），并为数据区域增长边框线。
4. 将工作表"平均单价"中的区域B3:C7定义名称为"商品均价"。运用公式计算工作表"销售情况"中F列的销售额，规定在公式中通过VLOOKUP函数自动在工作表"平均单价"中查找相关商品的单价，并在公式中引用所定义的名称"商品均价"。
5. 为工作表"销售情况"中的销售数据创建一个数据透视表，放置在一个名为"数据透视分析"的新工作表中，规定针对各类商品比较各门店每个季度的销售额。其中：商品名称为报表筛选字段，店铺为行标签，季度为列标签，并对销售额求和。最后对数据透视表进行格式设立，使其更加美观。
6. 根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对各门店四个季度笔记本的销售额进行比较。
7. 保存"计算机设备全年销量登记表.xlsx"文献。
【第7套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须都保存在考生文献夹下。
小李是东方公司的会计，运用自己所学的办公软件进行记账管理，为节省时间，同时又保证记账的准确性，她使用Excel编制了2023年3月员工工资表"Excel.xlsx"。
请你根据下列规定帮助小李对该工资表进行整理和分析（提醒：本题中若出现排序问题则采用升序方式）：
（1） 通过合并单元格，将表名"东方公司2023年3月员工工资表"放于整个表的上端、居中，并调整字体、字号。
（2） 在"序号"列中分别填入1到15，将其数据格式设立为数值、保存0位小数、居中。
（3） 将"基础工资"（含）往右各列设立为会计专用格式、保存2位小数、无货币符号。
（4）调整表格各列宽度、对齐方式，使得显示更加美观。并设立纸张大小为A4、横向，整个工作表需调整在1个打印页内。
（5） 参考考生文献夹下的"工资薪金所得税率.xlsx"，运用IF函数计算 "应交个人所得税"列。 (提醒：应交个人所得税=应纳税所得额*相应税率-相应速算扣除数)
（6） 运用公式计算"实发工资"列，公式为：实发工资=应付工资合计-扣除社保-应交个人所得税。
（7）复制工作表"2023年3月"，将副本放置到原表的右侧，并命名为"分类汇总"。
（8） 在"分类汇总"工作表中通过度类汇总功能求出各部门"应付工资合计"、"实发工资"的和，每组数据不分页。
【第8套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须都保存在考生文献夹下。
小李是北京某政法学院教务处的工作人员，法律系提交了2023级四个法律专业教学班的期末成绩单，为更好地掌握各个教学班学习的整体情况，教务处领导规定她制作成绩分析表，供学院领导掌握宏观情况。请根据考生文献夹下的“素材.xlsx”文档，帮助小李完毕2023级法律专业学生期末成绩分析表的制作。具体规定如下：
1. 将“素材.xlsx”文档另存为“年级期末成绩分析.xlsx”，以下所有操作均基于此新保存的文档。
2. 在“2023级法律”工作表最右侧依次插入“总分”、“平均分”、“年级排名”列；将工作表的第一行根据表格实际情况合并居中为一个单元格，并设立合适的字体、字号，使其成为该工作表的标题。对班级成绩区域套用带标题行的“表样式中档深浅 15”的表格格式。设立所有列的对齐方式为居中，其中排名为整数，其他成绩的数值保存1位小数。
3. 在“2023级法律”工作表中，运用公式分别计算“总分”、“平均分”、 “年级排名”列的值。对学生成绩不及格（小于60）的单元格套用格式突出显示为“黄色（标准色）填充色红色（标准色）文本”。
4. 在“2023级法律”工作表中，运用公式、根据学生的学号、将其班级的名称填入“班级”列，规则为：学号的第三位为专业代码、第四位代表班级序号，即01为“法律一班”，02为“法律二班”，03为“法律三班”，04为“法律四班”。
5. 根据“2023级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中，工作表标签颜色设立为红色。规定数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序记录各班各科成绩的平均分，其中行标签为班级。为数据透视表格内容套用带标题行的“数据透视表样式中档深浅 15”的表格格式，所有列的对齐方式设为居中，成绩的数值保存1位小数。
6. 在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10:H30区域中。
【第9套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须都保存在考生文献夹下
销售部助理小王需要根据2023年和2023年的图书产品销售情况进行记录分析，以便制订新一年的销售计划和工作任务。现在，请你按照如下需求，在文档“EXCEL.XLSX”中完毕以下工作并保存。
1. 在“销售订单”工作表的“图书编号”列中，使用VLOOKUP函数填充所相应“图书名称”的“图书编号”，“图书名称”和“图书编号”的对照关系请参考“图书编目表”工作表。
2. 将“销售订单”工作表的“订单编号”列按照数值升序方式排序，并将所有反复的订单编号数值标记为紫色（标准色）字体，然后将其排列在销售订单列表区域的顶端。
3. 在“2023年图书销售分析”工作表中，记录2023年各类图书在每月的销售量，并将记录结果填充在所相应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。
4. 在“2023年图书销售分析”工作表中的N4:N11单元格中，插入用于记录销售趋势的迷你折线图，各单元格中迷你图的数据范围为所相应图书的1月～12月销售数据。并为各迷你折线图标记销量的最高点和最低点。
5. 根据“销售订单”工作表的销售列表创建数据透视表，并将创建完毕的数据透视表放置在新工作表中，以A1单元格为数据透视表的起点位置。将工作表重命名为“2023年书店销量”。
6. 在“2023年书店销量”工作表的数据透视表中，设立“日期”字段为列标签，“书店名称”字段为行标签，“销量（本）”字段为求和汇总项。并在数据透视表中显示2023年期间各书店每季度的销量情况。
提醒：为了记录方便，请勿对完毕的数据透视表进行额外的排序操作。
【第10套】
请在【答题】菜单下选择【进入考生文献夹】命令，并按照题目规定完毕下面的操作。
注意：以下的文献必须都保存在考生文献夹下。
期末考试结束了，初三（14）班的班主任助理王老师需要对本班学生的各科考试成绩进行记录分析，并为每个学生制作一份成绩告知单下发给家长。按照下列规定完毕该班的成绩记录工作并按原文献名进行保存：
1. 打开工作簿“学生成绩.xlsx”，在最左侧插入一个空白工作表，重命名为“初三学生档案”，并将该工作表标签颜色设为“紫色(标准色)”。
2. 将以制表符分隔的文本文献“学生档案.txt”自A1单元格开始导入到工作表“初三学生档案”中，注意不得改变原始数据的排列顺序。将第1列数据从左到右依次提成“学号”和“姓名”两列显示。最后创建一个名为“档案”、包含数据区域A1:G56、包含标题的表，同时删除外部链接。
3. 在工作表“初三学生档案”中，运用公式及函数依次输入每个学生的性别“男”或“女”、出生日期“××××年××月××日”和年龄。其中：身份证号的倒数第2位用于判断性别，奇数为男性，偶数为女性；身份证号的第7~14位代表出生年月日；年龄需要按周岁计算，满1年才计1岁。最后适当调整工作表的行高和列宽、对齐方式等，以方便阅读。
4. 参考工作表“初三学生档案”，在工作表“语文”中输入与学号相应的“姓名”；按照平时、期中、期末成绩各占30%、30%、40%的比例计算每个学生的“学期成绩”并填入相应单元格中；按成绩由高到低的顺序记录每个学生的“学期成绩”排名并按“第n名”的形式填入“班级名次”列中；按照下列条件填写“期末总评”：
语文、数学的学期成绩 其他科目的学期成绩 期末总评
≥102 ≥90 优秀
≥84 ≥75 良好
≥72 ≥60 及格
〈72 〈60 不合格
5. 将工作表“语文”的格式所有应用到其他科目工作表中，涉及行高（各行行高均为22默认单位）和列宽（各列列宽均为14默认单位）。并按上述4. 中的规定依次输入或记录其他科目的“姓名”、“学期成绩”、“班级名次”和“期末总评”。
6. 分别将各科的“学期成绩”引入到工作表“期末总成绩”的相应列中，
在工作表“期末总成绩”中依次引入姓名、计算各科的平均分、每个学生的总分，并按成绩由高到底的顺序记录每个学生的总分排名、并以1、2、3……形式标记名次，最后将所有成绩的数字格式设为数值、保存两位小数。
7. 在工作表“期末总成绩”中分别用红色（标准色）和加粗格式标出各科第一名成绩。同时将前10名的总提成绩用浅蓝色填充。
8. 调整工作表“期末总成绩”的页面布局以便打印：纸张方向为横向，缩减打印输出使得所有列只占一个页面宽（但不得缩小列宽），水平居中打印在纸上。