计算机二级考试MS OFFICE表格题 步骤详解.doc

第1套
小李今年毕业，在一家计算机图书销售公司担任市场部助理，主要的工作职责是为部门经理提供销售信息的分析和汇总。（2）图书名称
=VLOOKUP([@图书编号],表2[#全部],2,FALSE))
（2的含义：返回数据在查找区域的第2列数）
（3）单价
=VLOOKUP([@图书编号],表2[#全部],3,FALSE)
（3的含义：返回数据在查找区域的第3列）
（4）小计
=[@单价]*[@销量（本）]
（5）所有订单的总销售额
=SUM(表3[小计])

（6）《MS Office高级应用》图书在2012年的总销售额
=SUMIFS(表3[小计],表3[图书名称],"《MS Office高级应用》",表3[日期],">=2012-1-1",表3[日期],"<=2012-12-31")
（7）隆华书店在2011年第3季度（7月1日~9月30日）的总销售额
=SUMIFS(表3[小计],表3[书店名称],"隆华书店",表3[日期],">=2011-7-1",表3[日期],"<=2011-9-30")
（8）隆华书店在2011年的每月平均销售额（保留2位小数）
=SUMIFS(表3[小计],表3[书店名称],"隆华书店",表3[日期],">=2011-1-1",表3[日期],"<=2011-12-31")/12
先求全年的，然后除以12

第2套
小蒋是一位中学教师，在教务处负责初一年级学生的成绩管理。
（3）求总分和平均分
=SUM(D2:J2)
=AVERAGE(D2:J2)
（4）提取班级
=MID(A2,4,1)&"班"

第3套（最简单的一套题）
某公司拟对其产品季度销售情况进行统计。
有几个跨表引用的公式。
=C2*产品基本信息表!C2

第4套
中国的人口发展形势非常严峻，为此国家统计局每10年进行一次全国人口普查，以掌握全国人口的增长速度及规模。
（7）统计数据-2000年总人数
=SUM(表3[2000年人口数（万人）])

第5套（函数很多一套题）
财务部助理小王需要向主管汇报2013年度公司差旅报销情况，现在请按照如下需求，在EXCEL.XLSX文档中完成工作
（2）"是否加班"列的单元格中显示"是"，否则显示"否"（必须使用公式）。
=IF(WEEKDAY(A3,2)>5,"是","否")（3）地区
=LEFT(C3,3)
（5）2013年第二季度发生在北京市的差旅费用金额总计为 ：
=SUMIFS(费用报销管理!G3:G401,费用报销管理!A3:A401,">=2013-4-1",费用报销管理!A3:A401,"<=2013-6-30",费用报销管理!D3:D401,"北京市")
（6）2013年钱顺卓报销的火车票总计金额为 ：
=SUMIFS(费用报销管理!G3:G401,费用报销管理!B3:B401,"钱顺卓",费用报销管理!F3:F401,"火车票")
（7）2013年差旅费用金额中，飞机票占所有报销费用的比例为（保留2位小数）
=SUMIF(费用报销管理!F3:F401,"飞机票",费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)（8）2013年发生在周末（星期六和星期日）中的通讯补助总金额为
=SUMIFS(费用报销管理!G3:G401,费用报销管理!H3:H401,"是",费用报销管理!F3:F401,"通讯补助")
第6套
文涵是大地公司的销售部助理，负责对全公司的销售情况进行统计分析，并将结果提交给销售部经理。
（4）销售额
=VLOOKUP(D4,商品均价,2,0)*E4
第7套（这尼玛就是IF嵌套）
小李是东方公司的会计，利用自己所学的办公软件进行记账管理，为节省时间，同时又确保记账的准确性
（5）应交个人所得税（提示：应交个人所得税=应纳税所得额*对应税率-对应速算扣除数）
=IF(K3<=1500,K3*3/100,IF(K3<=4500,K3*10/100-105,IF(K3<=9000,K3*20/100-555,IF(K3<=35000,K3*25%-1005,IF(K3<=55000,K3*30%-2755,IF(K3<=80000,K3*35%-5505,IF(K3>80000,K3*45%-13505)))))))
（请不要吐在屏幕或者键盘上）

第8套
小李是北京某政法学院教务处的工作人员，法律系提交了2012级四个法律专业教学班的期末成绩单。
（3）年级排名
=RANK([@总分],[总分],0)
（4）班级
=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班",IF(MID(B3,3,2)="03","法律三班","法律四班")))
（又是一个IF嵌套，下面图片是依次从上往下的步骤）
第9套（SUMIFS大集合）
销售部助理小王需要根据2012年和2013年的图书产品销售情况进行统计分析，以便制订新一年的销售计划和工作任务。
（1）图书编号
=VLOOKUP([@图书名称],表3,2,0)（3）在“2013年图书销售分析”工作表中，统计2013年各类图书在每月的销售量，并将统计结果填充在所对应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。
1月《Office商务办公好帮手》销售分析
=SUMIFS(表1[销量（本）],表1[图书名称],A4,表1[日期],">=2013-1-1",表1[日期],"<2013-2-1")其余月份设置方式：选中单元格，在编辑栏中将公式复制，然后双击2月，光标在单元格里面时再粘贴，然后修改日期就好了。

第10套
期末考试结束了，初三（14）班的班主任助理王老师需要对本班学生的各科考试成绩进行统计分析，并为每个学生制作一份成绩通知单下发给家长。
（3）性别：（又是一个嵌套）
=IF(MOD(MID(C2,17,1),2)=1,"男","女")出生日期：（两种方法）
=MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日"
这个是用三次MID函数，用&连接
也可以用：
=TEXT(MID(C2,7,8),"0000年00月00日") 年龄：
=INT((TODAY()-E2)/365) （4）语文姓名
=VLOOKUP(A2,档案[[学号]:[姓名]],2,0)
学期成绩：
= C2*30%)+(D2*30%)+(E2*40%)班级名次
="第"&RANK(F2,$F$2:$F$45)&"名期末总评
=IF(F2>=102,"优秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格"))))
第11套
小李是某政法学院教务处的工作人员，为更好地掌握各个教学班级学习的整体情况，教务处领导要求她制作成绩分析表。
（2）"班内排名"
=RANK([@总分],$L$3:$L$27,0)
第12套
某公司销售部门主管大华拟对本公司产品前两季度的销售情况进行统计
（2）产品单价、销售额
=VLOOKUP(B2,产品基本信息表!$B$1:$C$21,2,0)
销售额
=D2*E2

（3）一季度销量、销售额，二季度销量、销售额
一季度销量
=SUMIF(一季度销售情况表!$B$2:$B$44,[@产品型号],一季度销售情况表!$D$2:$D$44)
一季度销售额：
=SUMIF(一季度销售情况表!$B$2:$B$44,[@产品型号],一季度销售情况表!$F$2:$F$44)
二季度销量
=SUMIF('二季度销售情况表'!$B$2:$B$43,[@产品型号],'二季度销售情况表 '!$D$2:$D$43)
二季度销售额
=SUMIF('二季度销售情况表'!$B$2:$B$43,[@产品型号],'二季度销售情况表 '!$F$2:$F$43)
一二季度销售总量
=C2+E2
一二季度销售总额
=D2+F2
总销售额排名
=RANK(H2,$H$2:$H$21)

第13套（最没人性的一道题，都可以用数据透视表解决非要用公式）
滨海市对重点中学组织了一次物理统考，并生成了所有考生和每一个题目的得分
（2）完成“按班级汇总”
考试学生数
=COUNTIFS(成绩单!$A$2:$A$950,按班级汇总!$A2,成绩单!$B$2:$B$950,按班级汇总!$B2)
最高分、最低分、平均分个人建议用数据透视表做完了粘贴过去，因为函数你根本看不懂。
客观题平均分
=SUM(小分统计!$C2:$AP2)
主观题平均分
=SUM(小分统计!$AQ2:$AZ2)

（2）按学校汇总
考试学生数
=COUNTIF(成绩单!$A$2:$A$950,按学校汇总!$A2)
最高分、最低分、平均分还是用数据透视表做完了粘贴过去客观题平均分，主观题平均分：

太恶心，不整了

第14套
小赵是一名参加工作不久的大学生。他习惯使用Excel表格来记录每月的个人开支情况。
（6）插入新的列，整个“季度”（很烂的做法）
=INT(1+(MONTH(A14)-1)/3)&"季度"
另外一种做法：
=ROUNDUP(MONTH(A3)/3,0)&"季度"（Roundup是用月份除以3，然后向上舍入数字）第15套
为让利消费者，提供更优惠的服务，某大型收费停车场规划调整收费标准，
（2）收费标准
=VLOOKUP(C2,standard,2,FALSE)
（3）停放时间
=(H2-F2)*24+(I2-G2)
收费金额
=ROUNDUP((HOUR(J2)*60+MINUTE(J2))/15,0)*E2
拟收费金额
=INT((HOUR(J2)*60+MINUTE(J2))/15)*E2

第16套
销售部助理小王需要根据2012年和2013年的图书产品销售情况进行统计分析，以便制订新的销售计划和工作任务。
（3）“图书定价”
=VLOOKUP([@图书名称],表2,2,0)
（4）“销量额小计”
=IF([@销量（本）]>=40,[@单价]*[@销量（本）]*0.93,[@单价]*[@销量（本）])
（5）所在区域
=VLOOKUP(MID([@发货地址],1,3),表3,2,0)
（7）统计报告
2013年所有图书订单的销售额
=SUMIFS(表1[销售额小计],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")
《MS Office高级应用》图书在2012年的总销售额
=SUMIFS(表1[销售额小计],表1[图书名称],订单明细!D7,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")
隆华书店在2013年第3季度（7月1日~9月30日）的总销售额
=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2013-7-1",表1[日期],"<=2013-9-30")
隆华书店在2012年的每月平均销售额（保留2位小数）
=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")/12
2013年隆华书店销售额占公司全年销售总额的百分比（保留2位小数）
=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")/SUMIFS(表1[销售额小计],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")

第17套
小林是北京某师范大学财务处的会计，计算系计算机基础教研室提交了该教研室2012年的课程授课情况。
（3）课时费
职称
=VLOOKUP([@姓名],教师基本信息!$D$3:$E$22,2,0)
课时标准
=VLOOKUP([@职称],课时费标准!$A$3:$B$6,2,0)
学时数
=SUMIF(授课信息表!$D$3:$D$72,E3,授课信息表!$F$3:$F$72)
课时费
=[@学时数]*[@课时标准]

第18套
小李是公司的出纳，单位没有购买财务软件，因此她只能用手工记账。
（3）方向列
=IF(H2=0,"平",IF(H2>0,"借","贷"))
第19套
小赵是某书店的销售人员，负责计算机类图书的销售情况，并按月份上报分析结果。
销量
=VLOOKUP(A4,销量信息,3,0)
销售额
=C4*D4

第20套
小伟在自己在读的学院里面勤工助学，兼职当副院长的助理一职，平时主要负责对各种文案或者数据的整理。

最简单的sum和average
一个容易出错地方：分类汇总，“并将每组结果分页显示”

第21套
小刘是一所初中的学生处负责人，负责本院学生的成绩管理。
（4）根据学号提取班级
=MID([@学号],5,1)&"班"
（5）根据学号提取姓名
=VLOOKUP([@学号],学号对照!$A$3:$B$20,2,0)
第22套
小李在东方公司担任行政助理，年底小李统计了公司员工档案信息的分析和汇总
（2）根据身份证号码提取生日
=MID(F3,7,4)&"年"&MID(F3,11,2)&"月"&MID(F3,13,2)&"日"

（3）根据入职时间，计算工龄
=INT((TODAY()-I3)/365)
（4）计算工龄工资，基础工资
=J3*50
=K3+L3（5）所有人基础工资总额
=SUM(员工档案!K3:K37)（6）项目经理基础工资总额
=员工档案!K6+员工档案!K7

（7）本科生基本工资
=AVERAGEIF(员工档案!H3:H37,"本科",员工档案!K3:K37)

第23套
小王今年毕业后，在一家计算机图书销售公司担任市场部助理
（3）使用Vlookup函数完成填充
=VLOOKUP(D3,表2[#全部],3,0)
第24套
销售部助理小王对上半年产品销售统计分析
（2）前6个月销售总额
=SUM(D3:I3)

（3）销售排名
="第"&RANK.EQ([@个人销售总计],[个人销售总计])&"名"
="第"&RANK([@个人销售总计],[个人销售总计])&"名"
（这道题用Rank函数和Rank.EQ没有区别。）

（4）达标率
=COUNTIF(表1[一月份],">60000")/COUNT(表1[一月份])
（5）每个月第一名销售业绩
=LARGE(表1[一月份],1)
第25套
李东阳是某家电器企业的战略规划人员，正在参与制定本年度的生产与营销计划。
（5）在“销售记录”工作表中，价格
=VLOOKUP(C4,价格表!$B$2:$C$5,2,0)
（6）金额
=D4*E4*(1-VLOOKUP(C4,折扣表!$B$9:$F$11,IF(D4<1000,2,IF(D4<1500,3,IF(D4<2000,4,5)))))
（7）条件格式的公式
=OR(WEEKDAY($B4,2)=6,WEEKDAY($B4,2)=7)第26套
每年年终，太平洋公司都会给在职员工发放年终奖金，公司会计小任负责计算工资奖金的个人所得税。
（4）“员工基础档案”
男女
=IF(MOD(MID(E2,17,1),2)=1,"男","女")
出生日期
=MID(E2,7,4)&"年"&MID(E2,11,2)&"月"&MID(E2,13,2)&"日"
年龄
=INT(DAYS360([@出生日期],DATE(2015,9,30))/360)
月工龄工资
=IF(K2<1,0,IF(K2<10,K2*20,IF(K2<30,K2*30,K2*50)))
基本月工资
=L2+M2
（5）“年终奖金”
姓名
=VLOOKUP(A4,档案,2,0)
部门
=VLOOKUP(A4,档案,3,0)
月基本工资
=VLOOKUP(A4,档案,14,0)
应发奖金
=D4*12*0.15
月应税所得额
=E4/12
应交个税
=IF(F4<=1500,E4*0.03,IF(F4<=4500,E4*0.1-105,IF(F4<=9000,E4*0.2-555,IF(F4<=35000,E4*0.25-1005,IF(F4<=55000,E4*0.3-2755,IF(F4<=80000,E4*0.35-5505,E4*0.45-13505))))))
实发奖金
=E4-G4

（8）“工资条”
放弃比较好，做别的吧

第27套
正则明事务所的统计员小任需要对本所外汇报告的完成情况进行统计分析

（5）在数据区域的最左侧增加“完成情况”列，判断其完成情况
=IF(AND(F3="是",G3="是",H3="是"),"完成","未完成")
（6）在“完成情况”列的右侧增加“报告奖金”
=IF(I3="完成",IF(D3<=1000,100,IF(D3<=2800,D3*0.08,D3*0.1))+30,IF(D3<=1000,100,IF(D3<=2800,D3*0.08,D3*0.1)))9）在工作表“员工个人情况统计”中对每个员工完成情况统计
撰写报告数
=COUNTIF(表1[责任人],[@姓名])
修改过0次的报告数
=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],0)
修改过1次的报告数
=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],1)
第28套
销售经理小李通过Excel制作了销售情况统计表，根据下列要求帮助小李对数据进行整理和分析
（6）商品单价-vlookup函数
=VLOOKUP(E3,商品单价!$A$3:$B$7,2,0)
（7）成交金额-简单公式
=[@成交数量]*[@商品单价]

（9）计算每位销售经理每月的成交金额-sumifs函数
张乐1月
=SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],">=2014-1-1",表1[咨询日期],"<=2014-1-31")
张乐2月
=SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],">=2014-2-1",表1[咨询日期],"<=2014-2-28")
张乐3月
=SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],">=2014-3-1",表1[咨询日期],"<=2014-3-31")
总和
=SUM(B3:D3)
李耀1月
=SUMIFS(表1[成交金额],表1[销售经理],A4,表1[咨询日期],">=2014-1-1",表1[咨询日期],"<=2014-1-31")

第29套
晓雨任职人力咨询部门，她需要对企业员工office应用能力考核报考进行完善和分析。
（5）核算5个考核科目平均成绩
=AVERAGE(G3:K3)（6）在“等级”中填写考核等级
=IF(OR(G3<60,H3<60,I3<60,J3<60,K3<60),"不合格",IF(L3>=85,"优",IF(L3>=75,"良","及格")))
函数嵌套，需要把函数一步一步的增加

新增1
小方正在为本部分报考会计职称的考生准备相关通知及准考证
（3）插入列，
地区
=LOOKUP(MID(B4,5,2),LEFT(行政区划代码!B$4:B$38,2),MID(行政区划代码!B$4:B$38,4,3))
部门代买
=LEFT(B4,3)
总成绩
=IF(MID(B4,4,1)="1",J4*0.5+K4*0.5,J4*0.6+K4*0.4)
（5）统计分析
女性人数
=COUNTIFS(名单!F$4:F$1777,统计分析!B5,名单!D$4:D$1777,"女")
男性人数
=COUNTIFS(名单!F$4:F$1777,统计分析!B5,名单!D$4:D$1777,"男")
合计面试总人数
=D5+E5
其中：女性所占比例
=D5/F5
笔试最低分数线
=MIN(IF(名单!F$4:F$1777=统计分析!B5,名单!J$4:J$1777))

新增2
某企业员工小韩需要使用Excel来分析采购成本并进行辅助决策。
（1）成本分析
年订货成本
=$C$2/E3*$C$3
年储存成本
=$C$4*E3*0.5
年总成本
=F3+G3