计算机二级考试MSOFFICE表格题步骤详解.docx

1、计算机二级考试MSOFFICE表格题步骤详解计算机二级考试MS-OFFICE表格题-步骤详解第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图书名称,M

2、S Office高级应用,表3日期,=2012-1-1,表3日期,=2011-7-1,表3日期,=2011-1-1,表3日期,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）201

3、3年差旅费用金额中，飞机票占所有报销费用的比例为（保留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嵌套）小李是东方公司的会计，利用自己所学的

4、办公软件进行记账管理，为节省时间，同时又确保记账的准确性（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(K380000,K3*45%-13505)（请不要吐在屏幕或者键盘上） 第8套 小李是北京某政法学院教务处的工作人员，法律系提交了2012级四个法律专业教学班的期末成绩单。（3）年级排名 =RANK(总分,总分,0)

5、（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月Offi

6、ce商务办公好帮手销售分析=SUMIFS(表1销量（本）,表1图书名称,A4,表1日期,=2013-1-1,表1日期,=102,优秀,IF(F2=84,良好,IF(F2=72,及格,IF(F272,及格,不及格) 第11套小李是某政法学院教务处的工作人员，为更好地掌握各个教学班级学习的整体情况，教务处领导要求她制作成绩分析表。 （2）班内排名=RANK(总分,$L$3:$L$27,0) 第12套 某公司销售部门主管大华拟对本公司产品前两季度的销售情况进行统计 （2）产品单价、销售额=VLOOKUP(B2,产品基本信息表!$B$1:$C$21,2,0)销售额=D2*E2 （3）一季度销量、销售额

7、，二季度销量、销售额一季度销量=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) 第

8、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)最高分、最低分、平均分还是用数据

9、透视表做完了粘贴过去客观题平均分，主观题平均分： 太恶心，不整了 第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)收费金额 =ROUND

10、UP(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-

11、1,表1日期,=2012-1-1,表1日期,=2013-7-1,表1日期,=2012-1-1,表1日期,=2013-1-1,表1日期,=2013-1-1,表1日期,0,借,贷) 第19套 小赵是某书店的销售人员，负责计算机类图书的销售情况，并按月份上报分析结果。销量=VLOOKUP(A4,销量信息,3,0) 销售额=C4*D4 第20套 小伟在自己在读的学院里面勤工助学，兼职当副院长的助理一职，平时主要负责对各种文案或者数据的整理。 最简单的sum和average一个容易出错地方：分类汇总，“并将每组结果分页显示” 第21套 小刘是一所初中的学生处负责人，负责本院学生的成绩管理。 （4）根据学

12、号提取班级=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）本科生基本工资=AVER

13、AGEIF(员工档案!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(表

14、1一月份,1) 第25套 李东阳是某家电器企业的战略规划人员，正在参与制定本年度的生产与营销计划。（5）在“销售记录”工作表中，价格=VLOOKUP(C4,价格表!$B$2:$C$5,2,0) （6）金额=D4*E4*(1-VLOOKUP(C4,折扣表!$B$9:$F$11,IF(D41000,2,IF(D41500,3,IF(D42000,4,5) （7）条件格式的公式=OR(WEEKDAY($B4,2)=6,WEEKDAY($B4,2)=7)第26套 每年年终，太平洋公司都会给在职员工发放年终奖金，公司会计小任负责计算工资奖金的个人所得税。（4）“员工基础档案”男女=IF(MOD(MID(

15、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(K21,0,IF(K210,K2*20,IF(K230,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=4

16、500,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,I

17、F(D3=2800,D3*0.08,D3*0.1)+30,IF(D3=1000,100,IF(D3=2014-1-1,表1咨询日期,=2014-2-1,表1咨询日期,=2014-3-1,表1咨询日期,=2014-1-1,表1咨询日期,=2014-1-31) 第29套晓雨任职人力咨询部门，她需要对企业员工office应用能力考核报考进行完善和分析。 （5）核算5个考核科目平均成绩=AVERAGE(G3:K3)（6）在“等级”中填写考核等级=IF(OR(G360,H360,I360,J360,K3=85,优,IF(L3=75,良,及格)函数嵌套，需要把函数一步一步的增加 新增1小方正在为本部分报考

18、会计职称的考生准备相关通知及准考证（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