计算机二级Msoffice考试excel题目Word文件下载.docx
《计算机二级Msoffice考试excel题目Word文件下载.docx》由会员分享,可在线阅读,更多相关《计算机二级Msoffice考试excel题目Word文件下载.docx(14页珍藏版)》请在冰豆网上搜索。
现在,第一学期期末考试刚刚结束,小蒋将初一年级三个班的成绩均录入了文件名为"
学生成绩单.xlsx"
的Excel工作簿文档中。
请你根据下列要求帮助小蒋老师对该成绩单进行整理和分析:
1.对工作表"
第一学期期末成绩"
中的数据列表进行格式化操作:
将第一列"
学号"
列设为文本,将所有成绩列设为保留两位小数的数值;
适当加大行高列宽,改变字体、字号,设置对齐方式,增加适当的边框和底纹以使工作表更加美观。
2.利用"
条件格式"
功能进行下列设置:
将语文、数学、英语三科中不低于110分的成绩所在的单元格以一种颜色填充,其他四科中高于95分的成绩以另一种字体颜色标出,所用颜色深浅以不遮挡数据为宜。
3.利用sum和average函数计算每一个学生的总分与平均成绩。
4.学号第3、4位代表学生所在的班级,例如:
120105"
代表12级1班5号。
请通过函数提取每个学生所在的班级并按下列对应关系填写在"
班级"
列中:
"
的3、4位对应班级
011班
022班
033班
5.复制工作表"
,将副本放置到原表之后;
改变该副本表标签的颜色,并重新命名,新表名需包含"
分类汇总"
字样。
6.通过分类汇总功能求出每个班各科的平均成绩,并将每组结果分页显示。
7.以分类汇总结果为基础,创建一个簇状柱形图,对每个班各科平均成绩进行比较,并将该图表放置在一个名为"
柱状分析图"
新工作表中。
操作题3:
请在[答题]菜单下选择[进入考生文件夹]命令,并按照题目要求完成下面的操作。
注意:
以下的文件必须保存在考生文件夹下。
在考生文件夹下打开工作簿Excel.xlsx,按照要求完成下列操作并以该文件名(Excel.xlsx)保存工作簿。
某公司拟对其产品季度销售情况进行统计,打开"
文件,按以下要求操作:
〔1〕分别在"
一季度销售情况表"
、"
二季度销售情况表"
工作表内,计算"
一季度销售额"
二季度销售额"
列内容,均为数值型,保留小数点后0位。
〔2〕在"
产品销售汇总图表"
内,计算"
一二季度销售总量"
一二季度销售总额"
列内容,数值型,保留小数点后0位;
在不改变原有数据顺序的情况下,按一二季度销售总额给出销售额排名。
〔3〕选择"
内A1:
E21单元格区域内容,建立数据透视表,行标签为产品型号,列标签为产品类别代码,求和计算一二季度销售额的总计,将表置于现工作表G1为起点的单元格区域内。
操作题4:
中国的人口发展形势非常严峻,为此国家统计局每10年进行一次全国人口普查,以掌握全国人口的增长速度与规模。
按照下列要求完成对第五次、第六次人口普查数据的统计分析:
〔1〕新建一个空白Excel文档,将工作表sheet1更名为"
第五次普查数据"
,将sheet2更名为"
第六次普查数据"
,将该文档以"
全国人口普查数据分析.xlsx"
为文件名进行保存。
〔2〕浏览网页"
第五次全国人口普查公报.htm"
,将其中的"
20XX第五次全国人口普查主要数据"
表格导入到工作表"
中;
浏览网页"
第六次全国人口普查公报.htm"
20XX第六次全国人口普查主要数据"
中〔要求均从A1单元格开始导入,不得对两个工作表中的数据进行排序〕。
〔3〕对两个工作表中的数据区域套用合适的表格样式,要求至少四周有边框、且偶数行有底纹,并将所有人口数列的数字格式设为带千分位分隔符的整数。
〔4〕将两个工作表内容合并,合并后的工作表放置在新工作表"
比较数据"
中〔自A1单元格开始〕,且保持最左列仍为地区名称、A1单元格中的列标题为"
地区"
,对合并后的工作表适当的调整行高列宽、字体字号、边框底纹等,使其便于阅读。
以"
为关键字对工作表"
进行升序排列。
〔5〕在合并后的工作表"
中的数据区域最右边依次增加"
人口增长数"
比重变化"
两列,计算这两列的值,并设置合适的格式。
其中:
人口增长数=20XX人口数-20XX人口数;
比重变化=20XX比重-20XX比重。
〔6〕打开工作簿"
统计指标.xlsx"
,将工作表"
统计数据"
插入到正在编辑的文档"
中工作表"
的右侧。
〔7〕在工作簿"
的工作表"
中的相应单元格内填入统计结果。
〔8〕基于工作表"
创建一个数据透视表,将其单独存放在一个名为"
透视分析"
的工作表中。
透视表中要求筛选出20XX人口数超过5000万的地区与其人口数、20XX所占比重、人口增长数,并按人口数从多到少排序。
最后适当调整透视表中的数字格式。
〔提示:
行标签为"
,数值项依次为20XX人口数、20XX比重、人口增长数〕。
操作题5:
在考生文件夹下打开文档EXCEL.XLSX。
[背景素材]
财务部助理小王需要向主管汇报20XX度公司差旅报销情况,现在请按照如下需求,在EXCEL.XLSX文档中完成工作:
〔1〕在"
费用报销管理"
工作表"
日期"
列的所有单元格中,标注每个报销日期属于星期几,例如日期为"
20XX1月20日"
的单元格应显示为"
20XX1月20日星期日"
,日期为"
20XX1月21日"
的单元格应显示为"
20XX1月21日星期一"
。
〔2〕如果"
列中的日期为星期六或星期日,则在"
是否加班"
列的单元格中显示"
是"
,否则显示"
否"
〔必须使用公式〕。
〔3〕使用公式统计每个活动地点所在的省份或直辖市,并将其填写在"
列所对应的单元格中,例如"
市"
XX省"
〔4〕依据"
费用类别编号"
列内容,使用VLOOKUP函数,生成"
费用类别"
列内容。
对照关系参考"
工作表。
〔5〕在"
差旅成本分析报告"
工作表B3单元格中,统计20XX第二季度发生在市的差旅费用总金额。
〔6〕在"
工作表B4单元格中,统计20XX员工钱顺卓报销的火车票费用总额。
〔7〕在"
工作表B5单元格中,统计20XX差旅费用中,飞机票费用占所有报销费用的比例,并保留2位小数。
〔8〕在"
工作表B6单元格中,统计20XX发生在周末〔星期六和星期日〕的通讯补助总金额。
操作题6:
文涵是大地公司的销售部助理,负责对全公司的销售情况进行统计分析,并将结果提交给销售部经理。
年底,她根据各门店提交的销售报表进行统计分析。
打开"
计算机设备全年销量统计表.xlsx"
,帮助文涵完成以下操作:
1.将"
sheet1"
工作表命名为"
销售情况"
,将"
sheet2"
命名为"
平均单价"
2.在"
店铺"
列左侧插入一个空列,输入列标题为"
序号"
,并以001、002、003……的方式向下填充该列到最后一个数据行。
3.将工作表标题跨列合并后居中并适当调整其字体、加大字号,并改变字体颜色。
适当加大数据表行高和列宽,设置对齐方式与销售额数据列的数值格式〔保留2位小数〕,并为数据区域增加边框线。
4.将工作表"
中的区域B3:
C7定义名称为"
商品均价"
运用公式计算工作表"
中F列的销售额,要求在公式中通过VLOOKUP函数自动在工作表"
中查找相关商品的单价,并在公式中引用所定义的名称"
5.为工作表"
中的销售数据创建一个数据透视表,放置在一个名为"
数据透视分析"
的新工作表中,要求针对各类商品比较各门店每个季度的销售额。
商品名称为报表筛选字段,店铺为行标签,季度为列标签,并对销售额求和。
最后对数据透视表进行格式设置,使其更加美观。
6.根据生成的数据透视表,在透视表下方创建一个簇状柱形图,图表中仅对各门店四个季度笔记本的销售额进行比较。
7.保存"
操作题7:
小李是东方公司的会计,利用自己所学的办公软件进行记账管理,为节省时间,同时又确保记账的准确性,她使用Excel编制了20XX3月员工工资表"
请你根据下列要求帮助小李对该工资表进行整理和分析〔提示:
本题中若出现排序问题则采用升序方式〕:
〔1〕通过合并单元格,将表名"
东方公司20XX3月员工工资表"
放于整个表的上端、居中,并调整字体、字号。
〔2〕在"
列中分别填入1到15,将其数据格式设置为数值、保留0位小数、居中。
〔3〕将"
基础工资"
〔含〕往右各列设置为会计专用格式、保留2位小数、无货币符号。
〔4〕调整表格各列宽度、对齐方式,使得显示更加美观。
并设置纸X大小为A4、横向,整个工作表需调整在1个打印页内。
〔5〕参考考生文件夹下的"
工资薪金所得税率.xlsx"
,利用IF函数计算"
应交个人所得税"
列。
(提示:
应交个人所得税=应纳税所得额*对应税率-对应速算扣除数)
〔6〕利用公式计算"
实发工资"
列,公式为:
实发工资=应付工资合计-扣除社保-应交个人所得税。
〔7〕复制工作表"
20XX3月"
,将副本放置到原表的右侧,并命名为"
工作表中通过分类汇总功能求出各部门"
应付工资合计"
的和,每组数据不分页。
操作题8:
小X是一所初中的学生处负责人,负责本院学生的成绩管理。
他通过Excel来管理学生成绩,现在第一学期期末考试刚刚结束,小X将初一年级三个班级部分学生成绩录入了文件名为"
第一学期期末成绩.xlsx"
请你根据下列要求帮助小X同学对该成绩单进行整理和分析:
1.请对"
工作表进行格式调整,通过套用表格格式方法将所有的成绩记录调整为一致的外观格式,并对该工作表"
列设为文本,将所有成绩列设为保留两位小数的数值,设置对齐方式,增加适当的边框和底纹以使工作表更加美观。
2.利用"
将语文、数学、外语三科中不低于110分的成绩所在的单元格以一种颜色填充,所用颜色深浅以不遮挡数据为宜。
4.学号第4、5位代表学生所在的班级,例如:
C120101"
代表12级1班。
请通过函数提取每个学生所在的专业并按下列对应关系填写在"
的4、5位对应班级
5.根据学号,请在"
XX"
列中,使用VLOOKUP函数完成XX的自动填充。
学号对照"
6.在"
成绩分类汇总"
中通过分类汇总功能求出每个班各科的最大值,并将汇总结果显示在数据下方。
7.以分类汇总结果为基础,创建一个簇状条形图,对每个班各科最大值进行比较。
操作题9:
小李在东方公司担任行政助理,年底小李统计了公司员工档案信息的分析和汇总。
请你根据东方公司员工档案表〔"
员工档案表"
工作表进行格式调整,将所有工资列设为保留两位小数的数值,适当加大行高列宽。
2.根据XX号,请在"
出生日期"
列中,使用MID函数提取员工生日,单元格式类型为"
yyyy'
年'
m'
月'
d'
日'
3.根据入职时间,请在"
工龄"
列中,使用TODAY函数和INT函数计算员工的工龄,工作满一年才计入工龄。
4.引用"
工龄工资"
工作表中的数据来计算"
工作表员工的工龄工资,在"
列中,计算每个人的基础工资。
〔基础工资=基本工资+工龄工资〕
工作表中的工资数据,统计所有人的基础工资总额,并将其填写在"
工作表的B2单元格中。
工作表中的工资数据,统计职务为项目经理的基本工资总额,并将其填写在"
工作表中的数据,统计东方公司本科生平均基本工资,并将其填写在"
8.通过分类汇总功能求出每个职务的平均基本工资。
9.创建一个饼图,对每个员工的基本工资进行比较,并将该图表放置在"
中。
10.保存"
操作题10:
小王今年毕业后,在一家计算机图书销售公司担任市场部助理,主要的工作职责是为部门经理提供销售信息的分析和汇总。
请你根据销售统计表〔“Excel.xlsx〞文件〕,按照如下要求完成统计和分析工作:
1.将“sheet1〞工作表命名为“销售情况〞,将“sheet2〞命名为“图书定价〞。
2.在“图书名称〞列右侧插入一个空列,输入列标题为“单价〞。
3.将工作表标题跨列合并后居中并适当调整其字体、加大字号,并改变字体颜色。
设置数据表对齐方式与单价和小计的数值格式〔保留2位小数〕。
根据图书编号,请在“销售情况〞工作表的“单价〞列中,使用VLOOKUP函数完成图书单价的填充。
“单价〞和“图书编号〞的对应关系在“图书定价〞工作表中。
4.运用公式计算工作表“销售情况〞中H列的小计。
5.为工作表“销售情况〞中的销售数据创建一个数据透视表,放置在一个名为“数据透视分析〞的新工作表中,要求针对各书店比较各类书每天的销售额。
书店名称为列标签,日期和图书名称为行标签,并对销售额求和。
6.根据生成的数据透视表,在透视表下方创建一个簇状柱形图,图表中仅对博达书店一月份的销售额小计进行比较。
7.保存“Excel.xlsx〞文件。
操作题11:
小林是某师X大学财务处的会计,计算机系计算机基础室提交了该教研室20XX的课程授课情况,希望财务处尽快核算并发放他们室的课时费。
请根据考生文件夹下"
素材.xlsx"
中的各种情况,帮助小林核算出计算机基础室20XX度每个教员的课时费情况。
具体要求如下:
1.将"
另存为"
课时费.xlsx"
的文件,所有的操作基于此新保存好的文件。
2.将"
课时费统计表"
标签颜色更改为红色,将第一行根据表格情况合并为一个单元格,并设置合适的字体、字号,使其成为该工作表的标题。
对A2:
I22区域套用合适的中等深浅的、带标题行的表格格式。
前6列对齐方式设为居中;
其余与数值和金额有关的列,标题为居中,值为右对齐,学时数为整数,金额为货币样式并保留2位小数。
3."
中的F至I列中的空白内容必须采用公式的方式计算结果。
根据"
教师基本信息"
工作表和"
课时费标准"
工作表计算"
职称"
课时标准"
列内容,根据"
授课信息表"
课程基本信息"
学时数"
列内容,最后完成"
课时费"
列的计算。
[提示:
建议对"
中的数据按XX排序后增加"
列,并通过VLOOKUP查询"
表获得相应的值。
]
4.为"
创建一个数据透视表,保存在新的工作表中。
其中报表筛选条件为"
年度"
,列标签为"
教研室"
,行标签为"
,求和项为"
并在该透视表下方的A12:
F24区域内插入一个饼图,显示计算机基础室课时费对职称的分布情况。
并将该工作表命名为"
数据透视图"
,表标签颜色为蓝色。
5.保存"
操作题12:
小李是公司的出纳,单位没有购买财务软件,因此她只能用手工记账。
为了节省时间并保证记账的准确性,小李使用Excel编制银行存款日记账。
请根据该公司九月份的"
银行流水账表格.docx"
,并按照下述要求,在Excel中建立银行存款日记账:
1.按照表中所示依次输入原始数据,其中:
在"
月"
列中以填充的方式输入"
九"
,将表中的数值的格式设为数值、保留2位小数。
2.输入并填充公式:
余额"
列输入计算公式,余额=上期余额+本期借方-本期贷方,以自动填充方式生成其他公式。
方向列中"
只能有借、贷、平三种选择,首先用数据有效性控制该列的输入X围为借、贷、平三种中的一种,然后通过IF函数输入"
方向"
列内容,判断条件如下所列:
余额大于0等于0小于0
方向借平贷
4.设置格式:
将第一行中的各个标题居中显示;
为数据列表自动套用格式后将其转换为区域。
5.通过分类汇总,按日计算借方、贷方发生额总计并将汇总行放于明细数据下方。
6.以文件名"
银行存款日记账.xlsx"
进行保存。
操作题13:
小赵是某书店的销售人员,负责计算机类图书的销售情况,并按月份上报分析结果。
20XX1月份时,她需要将20XX12月份的销售情况进行汇总,请根据提供的"
文件帮助她完成下列工作:
1.对"
进行如下设置:
将"
销售统计"
表中的"
列数值的格式设为会计专用、保留2位小数。
2.对数据表"
进行适当的格式化,操作如下:
合并A1:
E1单元格,为标题名"
12月份计算机图书销售情况统计表"
设置适当的对齐方式、字体、字号以与颜色;
为数据区域设置边框底纹以使工作表更加美观等。
3.将工作表"
销量"
D16定义名称为"
销量信息"
列右侧增加一列"
销售额"
,根据"
销售额=销量×
构建公式计算出各类图书的销售额。
要求在公式中通过VLOOKUP函数自动在工作表"
中查找相关商品的具体销量。
并在公式中引用所定义的名称"
工作表创建一个数据透视表,放在一个名为"
的新的工作表中。
5.为数据透视表数据创建一个类型为饼图的数据透视图,设置数据标签显示在外侧,将图表的标题改为"
12月份计算机图书销量"
6.将工作表另存为"
计算机类图书12月份销售情况统计.xlsx"
操作题14:
小伟在自己在读的学院里面勤工助学,兼职当副院长的助理一职,平时主要负责对各种文案或者数据的整理。
现在,信息与计算科学专业的期末考试的部分成绩需要录入文件名为"
考生成绩单.xlsx"
的Excel工作薄文档中去。
请根据下列要求帮助小伟对该成绩单进行分析整理:
1.利用"
将大学物理和大学英语两科中低于80分的成绩所在的单元格以一种颜色填充,其他五科中大于或等于95分的成绩以另一种颜色标出,所用颜色以不遮挡数据为宜。
2.对工作表"
中的数据列表进行如下格式化操作:
设置为文本,设置成绩列为保留两位小数的数值。
改变数据列表中的行高、列宽,改变字体、字号,设置边框和底纹、设置对齐方式。
3.利用sum和average函数计算每一个学生的总分以与平均成绩。
4.复制工作表"
,将副本放置于原表之后,新表重新命名为"
成绩单分类汇总"
5.通过分类汇总功能求出每个班各科的平均成绩,并将每组结果分页显示。
6.创建一个簇状柱形图,对每个班各科平均成绩进行比较。
操作题15:
请根据考生文件夹下的“素材.xlsx〞文档,帮助小李完成2012级法律专业学生期末成绩分析表的制作。
1.将“素材.xlsx〞文档另存为“年级期末成绩分析.xlsx〞,以下所有操作均基于此新保存的文档。
2.在“2012级法律〞工作表最右侧依次插入“总分〞、“平均分〞、“年级排名〞列;
将工作表的第一行根据表格实际情况合并居中为一个单元格,并设置合适的字体、字号,使其成为该工作表的标题。
对班级成绩区域套用带标题行的“表样式中等深浅15〞的表格格式。
设置所有列的对齐方式为居中,其中排名为整数,其他成绩的数值保留1位小数。
3.在“2012级法律〞工作表中,利用公式分别计算“总分〞、“平均分〞、“年级排名〞列的值。
对学生成绩不与格〔小于60〕的单元格套用格式突出显示为“黄色〔标准色〕填充色红色〔标准色〕文本〞。
4.在“2012级法律〞工作表中,利用公式、根据学生的学号、将其班级的名称填入“班级〞列,规则为:
学号的第三位为专业代码、第四位代表班级序号,即01为“法律一班〞,02为“法律二班〞,03为“法律三班〞,04为“法律四班〞。
5.根据“2012级法律〞工作表,创建一个数据透视表,放置于表名为“班级平均分〞的新工作表中,工作表标签颜色设置为红色。
要求数据透视表中按照英语、体育、计算机、近代史、法制史、刑法、民法、法律英语、立法法的顺序统计各班各科成绩的平均分,其中行标签为班级。
为数据透视表格内容套用带标题行的“数据透视表样式中等深浅15〞的表格格式,所有列的对齐方式设为居中,成绩的数值保留1位小数。
6.在“班级平均分〞工作表中,针对各课程的班级平均分创建二维的簇状柱形图,其中水平簇标签为班级,图例项为课程名称,并将图表放置在表格下方的A10:
H30区域中。
操作题16:
销售部助理小王需要根据20XX和20XX的图书产品销售情况进行统计分析,以便制订新一年的销售计划和工作任务。
现在,请你按照如下需求,在文档“EXCEL.XLSX〞中完成以下工作并保存。
1.在“销售订单〞工作表的“图书编号〞列中,使用VLOOKUP函数填充所对应“图书名称〞的“图书编号〞,“图书名称〞和“图书编号〞的对照关系请参考“图书编目表〞工作表。
2.将“销售订单〞工作表的“订单编号〞列按照数值升序方式排序,并将所有重复的订单编号数值标记为紫色〔标准色〕字体,然后将其排列在销售订单列表区域的顶端。
3.在“20XX图书销售分析〞工