计算机二级Msoffice考试excel题目.docx

1、计算机二级Msoffice考试excel题目操作题1： 小李今年毕业后，在一家计算机图书销售公司担任市场部助理，主要的工作职责是为部门经理提供销售信息的分析和汇总。 请你根据销售数据报表（Excel.xlsx 文件），按照如下要求完成统计和分析工作： 1. 请对订单明细表工作表进行格式调整，通过套用表格格式方法将所有的销售记录调整为一致的外观格式，并将单价列和小计列所包含的单元格调整为会计专用（人民币）数字格式。 2. 根据图书编号，请在订单明细表工作表的图书名称列中，使用VLOOKUP函数完成图书名称的自动填充。图书名称和图书编号的对应关系在编号对照工作表中。 3. 根据图书编号，请在订单明

2、细表工作表的单价列中，使用VLOOKUP函数完成图书单价的自动填充。单价和图书编号的对应关系在编号对照工作表中。 4. 在订单明细表工作表的小计列中，计算每笔订单的销售额。 5. 根据订单明细表工作表中的销售数据，统计所有订单的总销售金额，并将其填写在统计报告工作表的B3单元格中。 6. 根据订单明细表工作表中的销售数据，统计MS Office高级应用图书在2012年的总销售额，并将其填写在统计报告工作表的B4单元格中。 7. 根据订单明细表工作表中的销售数据，统计隆华书店在2011年第3季度的总销售额，并将其填写在统计报告工作表的B5单元格中。 8. 根据订单明细表工作表中的销售数据，统计隆

3、华书店在2011年的每月平均销售额（保留2位小数），并将其填写在统计报告工作表的B6单元格中。 9. 保存Excel.xlsx文件。操作题2：小蒋是一位中学教师，在教务处负责初一年级学生的成绩管理。由于学校地处偏远地区，缺乏必要的教学设施，只有一台配置不太高的PC可以使用。他在这台电脑中安装了Microsoft Office，决定通过 Excel 来管理学生成绩，以弥补学校缺少数据库管理系统的不足。现在，第一学期期末考试刚刚结束，小蒋将初一年级三个班的成绩均录入了文件名为学生成绩单.xlsx的Excel工作簿文档中。 请你根据下列要求帮助小蒋老师对该成绩单进行整理和分析： 1. 对工作表第一学

4、期期末成绩中的数据列表进行格式化操作：将第一列学号列设为文本，将所有成绩列设为保留两位小数的数值；适当加大行高列宽，改变字体、字号，设置对齐方式，增加适当的边框和底纹以使工作表更加美观。 2. 利用条件格式功能进行下列设置：将语文、数学、英语三科中不低于110 分的成绩所在的单元格以一种颜色填充，其他四科中高于95分的成绩以另一种字体颜色标出，所用颜色深浅以不遮挡数据为宜。 3. 利用sum和average函数计算每一个学生的总分及平均成绩。 4. 学号第 3、4 位代表学生所在的班级，例如：120105代表12级1班5号。请通过函数提取每个学生所在的班级并按下列对应关系填写在班级列中： 学号

5、的3、4位 对应班级 01 1班 02 2班 03 3班 5. 复制工作表第一学期期末成绩，将副本放置到原表之后；改变该副本表标签的颜色，并重新命名，新表名需包含分类汇总字样。 6. 通过分类汇总功能求出每个班各科的平均成绩，并将每组结果分页显示。 7. 以分类汇总结果为基础，创建一个簇状柱形图，对每个班各科平均成绩进行比较，并将该图表放置在一个名为柱状分析图新工作表中。操作题3： 请在【答题】菜单下选择【进入考生文件夹】命令，并按照题目要求完成下面的操作。 注意：以下的文件必须保存在考生文件夹下。 在考生文件夹下打开工作簿Excel.xlsx，按照要求完成下列操作并以该文件名(Excel.x

6、lsx)保存工作簿。 某公司拟对其产品季度销售情况进行统计，打开Excel.xlsx文件，按以下要求操作： （1）分别在一季度销售情况表、二季度销售情况表工作表内,计算 一季度销售额列和二季度销售额列内容，均为数值型，保留小数点后0位。 （2） 在 产品销售汇总图表内,计算一二季度销售总量和一二季度销售总额列内容，数值型，保留小数点后0位；在不改变原有数据顺序的情况下，按一二季度销售总额给出销售额排名。 （3）选择产品销售汇总图表内A1：E21 单元格区域内容，建立数据透视表，行标签为产品型号，列标签为产品类别代码，求和计算一二季度销售额的总计，将表置于现工作表G1为起点的单元格区域内。操作题

7、4： 中国的人口发展形势非常严峻，为此国家统计局每10年进行一次全国人口普查，以掌握全国人口的增长速度及规模。按照下列要求完成对第五次、第六次人口普查数据的统计分析：（1） 新建一个空白Excel文档，将工作表sheet1更名为第五次普查数据，将sheet2更名为第六次普查数据，将该文档以全国人口普查数据分析.xlsx为文件名进行保存。（2） 浏览网页第五次全国人口普查公报.htm，将其中的2000年第五次全国人口普查主要数据表格导入到工作表第五次普查数据中；浏览网页第六次全国人口普查公报.htm，将其中的2010年第六次全国人口普查主要数据表格导入到工作表第六次普查数据中（要求均从A1单元格

8、开始导入，不得对两个工作表中的数据进行排序）。（3） 对两个工作表中的数据区域套用合适的表格样式，要求至少四周有边框、且偶数行有底纹，并将所有人口数列的数字格式设为带千分位分隔符的整数。（4）将两个工作表内容合并，合并后的工作表放置在新工作表比较数据中（自A1单元格开始），且保持最左列仍为地区名称、A1单元格中的列标题为地区，对合并后的工作表适当的调整行高列宽、字体字号、边框底纹等，使其便于阅读。以地区为关键字对工作表比较数据进行升序排列。（5）在合并后的工作表比较数据中的数据区域最右边依次增加人口增长数和比重变化两列，计算这两列的值，并设置合适的格式。其中：人口增长数=2010年人口数-20

9、00年人口数；比重变化=2010年比重-2000年比重。（6） 打开工作簿统计指标.xlsx，将工作表统计数据插入到正在编辑的文档全国人口普查数据分析.xlsx中工作表比较数据的右侧。（7） 在工作簿全国人口普查数据分析.xlsx的工作表比较数据中的相应单元格内填入统计结果。 （8） 基于工作表比较数据创建一个数据透视表，将其单独存放在一个名为透视分析的工作表中。透视表中要求筛选出2010年人口数超过5000万的地区及其人口数、2010年所占比重、人口增长数，并按人口数从多到少排序。最后适当调整透视表中的数字格式。（提示：行标签为地区，数值项依次为2010年人口数、2010年比重、人口增长数）

10、。操作题5： 在考生文件夹下打开文档EXCEL.XLSX。 【背景素材】 财务部助理小王需要向主管汇报2013年度公司差旅报销情况，现在请按照如下需求，在EXCEL.XLSX文档中完成工作： （1） 在费用报销管理工作表日期列的所有单元格中，标注每个报销日期属于星期几，例如日期为2013年1月20日的单元格应显示为 2013年1月20日 星期日，日期为2013年1月21日的单元格应显 示为2013年1月21日 星期一。 （2） 如果日期列中的日期为星期六或星期日，则在是否加班列的单元格中显示是，否则显示否（必须使用公式）。 （3） 使用公式统计每个活动地点所在的省份或直辖市，并将其填写在地区列

11、所对应的单元格中，例如北京市、浙江省。 （4） 依据费用类别编号列内容，使用VLOOKUP函数，生成费用类别列内容。对照关系参考费用类别工作表。 （5） 在差旅成本分析报告工作表B3单元格中，统计2013年第二季度发生在北京市的差旅费用总金额。（6） 在差旅成本分析报告工作表B4单元格中，统计2013年员工钱顺卓报销的火车票费用总额。 （7） 在差旅成本分析报告工作表B5单元格中，统计2013年差旅费用中，飞机票费用占所有报销费用的比例，并保留2位小数。 （8） 在差旅成本分析报告工作表B6单元格中，统计2013年发生在周末（星期六和星期日）的通讯补助总金额。操作题6：文涵是大地公司的销售部助

12、理，负责对全公司的销售情况进行统计分析，并将结果提交给销售部经理。年底，她根据各门店提交的销售报表进行统计分析。打开计算机设备全年销量统计表.xlsx，帮助文涵完成以下操作：1. 将sheet1工作表命名为销售情况，将sheet2命名为平均单价。2. 在店铺列左侧插入一个空列，输入列标题为序号，并以001、002、003 的方式向下填充该列到最后一个数据行。3. 将工作表标题跨列合并后居中并适当调整其字体、加大字号，并改变字体颜色。适当加大数据表行高和列宽，设置对齐方式及销售额数据列的数值格式（保留2位小数），并为数据区域增加边框线。4. 将工作表平均单价中的区域B3:C7定义名称为商品均价。

13、运用公式计算工作表销售情况中F列的销售额，要求在公式中通过VLOOKUP函数自动在工作表平均单价中查找相关商品的单价，并在公式中引用所定义的名称商品均价。5. 为工作表销售情况中的销售数据创建一个数据透视表，放置在一个名为数据透视分析的新工作表中，要求针对各类商品比较各门店每个季度的销售额。其中：商品名称为报表筛选字段，店铺为行标签，季度为列标签，并对销售额求和。最后对数据透视表进行格式设置，使其更加美观。6. 根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对各门店四个季度笔记本的销售额进行比较。7. 保存计算机设备全年销量统计表.xlsx文件。操作题7： 小李是东方公司的会计

14、，利用自己所学的办公软件进行记账管理，为节省时间，同时又确保记账的准确性，她使用Excel编制了2014年3月员工工资表Excel.xlsx。 请你根据下列要求帮助小李对该工资表进行整理和分析（提示：本题中若出现排序问题则采用升序方式）： （1） 通过合并单元格，将表名东方公司2014年3月员工工资表放于整个表的上端、居中，并调整字体、字号。 （2） 在序号列中分别填入1到15，将其数据格式设置为数值、保留0位小数、居中。 （3） 将基础工资（含）往右各列设置为会计专用格式、保留2位小数、无货币符号。 （4）调整表格各列宽度、对齐方式，使得显示更加美观。并设置纸张大小为A4、横向，整个工作表需

15、调整在1个打印页内。 （5） 参考考生文件夹下的工资薪金所得税率.xlsx，利用IF函数计算 应交个人所得税列。 (提示：应交个人所得税=应纳税所得额*对应税率-对应速算扣除数) （6） 利用公式计算实发工资列，公式为：实发工资=应付工资合计-扣除社保-应交个人所得税。 （7）复制工作表2014年3月，将副本放置到原表的右侧，并命名为分类汇总。 （8） 在分类汇总工作表中通过分类汇总功能求出各部门应付工资合计、实发工资的和，每组数据不分页。操作题8： 小刘是一所初中的学生处负责人，负责本院学生的成绩管理。他通过 Excel 来管理学生成绩，现在第一学期期末考试刚刚结束，小刘将初一年级三个班级部

16、分学生成绩录入了文件名为第一学期期末成绩.xlsx的Excel工作簿文档中。 请你根据下列要求帮助小刘同学对该成绩单进行整理和分析： 1.请对第一学期期末成绩工作表进行格式调整，通过套用表格格式方法将所有的成绩记录调整为一致的外观格式，并对该工作表第一学期期末成绩中的数据列表进行格式化操作：将第一列学号列设为文本，将所有成绩列设为保留两位小数的数值，设置对齐方式，增加适当的边框和底纹以使工作表更加美观。2. 利用条件格式功能进行下列设置：将语文、数学、外语三科中不低于110分的成绩所在的单元格以一种颜色填充，所用颜色深浅以不遮挡数据为宜。 3. 利用sum和average函数计算每一个学生的总

17、分及平均成绩。 4. 学号第 4、5 位代表学生所在的班级，例如：C120101代表12级1班。请通过函数提取每个学生所在的专业并按下列对应关系填写在班级列中： 学号的4、5位 对应班级 01 1班 02 2班 03 3班 5根据学号，请在第一学期期末成绩工作表的姓名列中，使用VLOOKUP函数完成姓名的自动填充。姓名和学号的对应关系在学号对照工作表中。 6. 在成绩分类汇总中通过分类汇总功能求出每个班各科的最大值，并将汇总结果显示在数据下方。7. 以分类汇总结果为基础，创建一个簇状条形图，对每个班各科最大值进行比较。操作题9： 小李在东方公司担任行政助理，年底小李统计了公司员工档案信息的分析

18、和汇总。 请你根据东方公司员工档案表（Excel.xlsx 文件），按照如下要求完成统计和分析工作： 1. 请对员工档案表工作表进行格式调整，将所有工资列设为保留两位小数的数值，适当加大行高列宽。 2. 根据身份证号，请在员工档案表工作表的出生日期列中，使用MID函数提取员工生日，单元格式类型为yyyy年m月d日。3. 根据入职时间，请在员工档案表工作表的工龄列中，使用TODAY函数和INT函数计算员工的工龄，工作满一年才计入工龄。4.引用工龄工资工作表中的数据来计算员工档案表工作表员工的工龄工资，在基础工资列中，计算每个人的基础工资。（基础工资=基本工资+工龄工资） 5. 根据员工档案表工作

19、表中的工资数据，统计所有人的基础工资总额，并将其填写在统计报告工作表的B2单元格中。 6. 根据员工档案表工作表中的工资数据，统计职务为项目经理的基本工资总额，并将其填写在统计报告工作表的B3单元格中。 7. 根据员工档案表工作表中的数据，统计东方公司本科生平均基本工资，并将其填写在统计报告工作表的B4单元格中。8. 通过分类汇总功能求出每个职务的平均基本工资。 9. 创建一个饼图，对每个员工的基本工资进行比较，并将该图表放置在统计报告中。 10. 保存Excel.xlsx文件。操作题10：小王今年毕业后，在一家计算机图书销售公司担任市场部助理，主要的工作职责是为部门经理提供销售信息的分析和汇

20、总。 请你根据销售统计表（“Excel.xlsx” 文件），按照如下要求完成统计和分析工作： 1. 将“sheet1”工作表命名为“销售情况”，将“sheet2”命名为“图书定价”。 2. 在“图书名称”列右侧插入一个空列，输入列标题为“单价”。 3. 将工作表标题跨列合并后居中并适当调整其字体、加大字号，并改变字体颜色。设置数据表对齐方式及单价和小计的数值格式（保留2位小数）。根据图书编号，请在“销售情况”工作表的“单价”列中，使用VLOOKUP函数完成图书单价的填充。“单价”和“图书编号”的对应关系在“图书定价”工作表中。 4.运用公式计算工作表“销售情况”中H列的小计。 5. 为工作表“

21、销售情况”中的销售数据创建一个数据透视表，放置在一个名为“数据透视分析”的新工作表中，要求针对各书店比较各类书每天的销售额。其中：书店名称为列标签，日期和图书名称为行标签，并对销售额求和。 6. 根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对博达书店一月份的销售额小计进行比较。 7. 保存“Excel.xlsx”文件。操作题11：小林是北京某师范大学财务处的会计，计算机系计算机基础室提交了该教研室2012年的课程授课情况，希望财务处尽快核算并发放他们室的课时费。请根据考生文件夹下素材.xlsx中的各种情况，帮助小林核算出计算机基础室2012年度每个教员的课时费情况。具体要求如

22、下： 1.将素材.xlsx另存为课时费.xlsx的文件，所有的操作基于此新保存好的文件。 2.将课时费统计表标签颜色更改为红色，将第一行根据表格情况合并为一个单元格，并设置合适的字体、字号，使其成为该工作表的标题。对A2：I22区域套用合适的中等深浅的、带标题行的表格格式。前6列对齐方式设为居中；其余与数值和金额有关的列，标题为居中，值为右对齐，学时数为整数，金额为货币样式并保留2位小数。 3.课时费统计表中的F至I列中的空白内容必须采用公式的方式计算结果。根据教师基本信息工作表和课时费标准工作表计算职称和课时标准列内容，根据授课信息表和课程基本信息工作表计算学时数列内容，最后完成课时费列的计

23、算。【提示：建议对授课信息表中的数据按姓名排序后增加学时数列，并通过VLOOKUP查询课程基本信息表获得相应的值。】 4.为课时费统计表创建一个数据透视表，保存在新的工作表中。其中报表筛选条件为年度，列标签为教研室，行标签为职称，求和项为课时费。并在该透视表下方的A12：F24区域内插入一个饼图，显示计算机基础室课时费对职称的分布情况。并将该工作表命名为数据透视图，表标签颜色为蓝色。 5.保存课时费.xlsx文件。操作题12：小李是公司的出纳，单位没有购买财务软件，因此她只能用手工记账。为了节省时间并保证记账的准确性，小李使用Excel编制银行存款日记账。 请根据该公司九月份的银行流水账表格.

24、docx，并按照下述要求，在Excel中建立银行存款日记账： 1. 按照表中所示依次输入原始数据，其中：在月列中以填充的方式输入九，将表中的数值的格式设为数值、保留2位小数。 2 .输入并填充公式：在余额列输入计算公式，余额=上期余额+本期借方-本期贷方，以自动填充方式生成其他公式。 3.方向列中只能有借、贷、平三种选择，首先用数据有效性控制该列的输入范围为借、贷、平三种中的一种，然后通过IF函数输入方向列内容，判断条件如下所列：余额 大于0 等于0 小于0方向 借 平 贷4. 设置格式：将第一行中的各个标题居中显示；为数据列表自动套用格式后将其转换为区域。5. 通过分类汇总，按日计算借方、贷

25、方发生额总计并将汇总行放于明细数据下方。6.以文件名银行存款日记账.xlsx进行保存。操作题13：小赵是某书店的销售人员，负责计算机类图书的销售情况，并按月份上报分析结果。2013年1月份时，她需要将2012年12月份的销售情况进行汇总，请根据提供的Excel.xlsx文件帮助她完成下列工作： 1.对Excel.xlsx进行如下设置：将销售统计表中的单价列数值的格式设为会计专用、保留2位小数。 2.对数据表销售统计进行适当的格式化，操作如下：合并A1：E1单元格，为标题名12月份计算机图书销售情况统计表设置适当的对齐方式、字体、字号以及颜色；为数据区域设置边框底纹以使工作表更加美观等。 3.将

26、工作表销量中的区域B3:D16定义名称为销量信息。在销售统计表中的销量列右侧增加一列销售额，根据销售额=销量单价构建公式计算出各类图书的销售额。要求在公式中通过VLOOKUP函数自动在工作表销量中查找相关商品的具体销量。并在公式中引用所定义的名称销量信息。 4.为销售统计工作表创建一个数据透视表，放在一个名为数据透视分析的新的工作表中。 5.为数据透视表数据创建一个类型为饼图的数据透视图，设置数据标签显示在外侧，将图表的标题改为12月份计算机图书销量。 6.将工作表另存为计算机类图书12月份销售情况统计.xlsx文件。操作题14：小伟在自己在读的学院里面勤工助学，兼职当副院长的助理一职，平时主

27、要负责对各种文案或者数据的整理。现在，信息与计算科学专业的期末考试的部分成绩需要录入文件名为考生成绩单.xlsx的Excel工作薄文档中去。请根据下列要求帮助小伟对该成绩单进行分析整理： 1. 利用条件格式功能进行下列设置：将大学物理和大学英语两科中低于80分的成绩所在的单元格以一种颜色填充，其他五科中大于或等于95分的成绩以另一种颜色标出，所用颜色以不遮挡数据为宜。 2. 对工作表考生成绩单.xlsx中的数据列表进行如下格式化操作：将第一列学号设置为文本，设置成绩列为保留两位小数的数值。改变数据列表中的行高、列宽，改变字体、字号，设置边框和底纹、设置对齐方式。 3. 利用sum和averag

28、e函数计算每一个学生的总分以及平均成绩。 4. 复制工作表考生成绩单.xlsx，将副本放置于原表之后，新表重新命名为成绩单分类汇总。 5. 通过分类汇总功能求出每个班各科的平均成绩，并将每组结果分页显示。 6. 创建一个簇状柱形图，对每个班各科平均成绩进行比较。操作题15：请根据考生文件夹下的“素材.xlsx”文档，帮助小李完成2012级法律专业学生期末成绩分析表的制作。具体要求如下： 1. 将“素材.xlsx”文档另存为“年级期末成绩分析.xlsx”，以下所有操作均基于此新保存的文档。 2. 在“2012级法律”工作表最右侧依次插入“总分”、“平均分”、“年级排名”列；将工作表的第一行根据表

29、格实际情况合并居中为一个单元格，并设置合适的字体、字号，使其成为该工作表的标题。对班级成绩区域套用带标题行的“表样式中等深浅 15”的表格格式。设置所有列的对齐方式为居中，其中排名为整数，其他成绩的数值保留1位小数。 3. 在“2012级法律”工作表中，利用公式分别计算“总分”、“平均分”、 “年级排名”列的值。对学生成绩不及格（小于60）的单元格套用格式突出显示为“黄色（标准色）填充色红色（标准色）文本”。 4. 在“2012级法律”工作表中，利用公式、根据学生的学号、将其班级的名称填入“班级”列，规则为：学号的第三位为专业代码、第四位代表班级序号，即01为“法律一班”，02为“法律二班”，

30、03为“法律三班”，04为“法律四班”。5. 根据“2012级法律”工作表，创建一个数据透视表，放置于表名为“班级平均分”的新工作表中，工作表标签颜色设置为红色。要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分，其中行标签为班级。为数据透视表格内容套用带标题行的“数据透视表样式中等深浅 15”的表格格式，所有列的对齐方式设为居中，成绩的数值保留1位小数。6. 在“班级平均分”工作表中，针对各课程的班级平均分创建二维的簇状柱形图，其中水平簇标签为班级，图例项为课程名称，并将图表放置在表格下方的A10:H30区域中。操作题16： 销售部助理小王需要根据2012年和2013年的图书产品销售情况进行统计分析，以便制订新一年的销售计划和工作任务。现在，请你按照如下需求，在文档“EXCEL.XLSX”中完成以下工作并保存。 1. 在“销售订单”工作表的“图书编号”列中，使用VLOOKUP函数填充所对应“图书名称”的“图书编号”，“图书名称”和“图书编号”的对照关系请参考“图书编目表”工作表。 2. 将“销售订单”工