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

第1套小李今年毕业，在一家计算机图书销售公司担任市场部助理，重要的工作职责是为部门经理提供销售信息的分析和汇总。（2）图书名称=VLOOKUP([@图书编号],表2[#所有],2,FALSE))（2的含义：返回数据在查找区域的第2列数）（3）单价=VLOOKUP([@图书编号],表2[#所有],3,FALSE)（3的含义：返回数据在查找区域的第3列）（4）小计=[@单价]*[@销量（本）]（5）所有订单的总销售额=SUM(表3[小计])（6）《MSOffice高级应用》图书在2023年的总销售额=SUMIFS(表3[小计],表3[图书名称],"《MSOffice高级应用》",表3[日期],">=2023-1-1",表3[日期],"<=2023-12-31")（7）隆华书店在2023年第3季度（7月1日~9月30日）的总销售额=SUMIFS(表3[小计],表3[书店名称],"隆华书店",表3[日期],">=2023-7-1",表3[日期],"<=2023-9-30")（8）隆华书店在2023年的每月平均销售额（保存2位小数）=SUMIFS(表3[小计],表3[书店名称],"隆华书店",表3[日期],">=2023-1-1",表3[日期],"<=2023-12-31")/12先求全年的，然后除以12第2套小蒋是一位中学教师，在教务处负责初一年级学生的成绩管理。（3）求总分和平均分=SUM(D2:J2)=AVERAGE(D2:J2)（4）提取班级=MID(A2,4,1)&"班"第3套（最简朴的一套题）某公司拟对其产品季度销售情况进行记录。有几个跨表引用的公式。=C2*产品基本信息表!C2第4套中国的人口发展形势非常严峻，为此国家记录局每2023进行一次全国人口普查，以掌握全国人口的增长速度及规模。（7）记录数据-2023年总人数=SUM(表3[2023年人口数（万人）])第5套（函数很多一套题）财务部助理小王需要向主管报告2023年度公司差旅报销情况，现在请按照如下需求，在EXCEL.XLSX文档中完毕工作（2）"是否加班"列的单元格中显示"是"，否则显示"否"（必须使用公式）。=IF(WEEKDAY(A3,2)>5,"是","否")（3）地区=LEFT(C3,3)（5）2023年第二季度发生在北京市的差旅费用金额总计为：=SUMIFS(费用报销管理!G3:G401,费用报销管理!A3:A401,">=2023-4-1",费用报销管理!A3:A401,"<=2023-6-30",费用报销管理!D3:D401,"北京市")（6）2023年钱顺卓报销的火车票总计金额为：=SUMIFS(费用报销管理!G3:G401,费用报销管理!B3:B401,"钱顺卓",费用报销管理!F3:F401,"火车票")（7）2023年差旅费用金额中，飞机票占所有报销费用的比例为（保存2位小数）=SUMIF(费用报销管理!F3:F401,"飞机票",费用报销管理!G3:G401)/SUM(费用报销管理!G3:G401)（8）2023年发生在周末（星期六和星期日）中的通讯补贴总金额为=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套小李是北京某政法学院教务处的工作人员，法律系提交了2023级四个法律专业教学班的期末成绩单。（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大集合）销售部助理小王需要根据2023年和2023年的图书产品销售情况进行记录分析，以便制订新一年的销售计划和工作任务。（1）图书编号=VLOOKUP([@图书名称],表3,2,0)（3）在“2023年图书销售分析”工作表中，记录2023年各类图书在每月的销售量，并将记录结果填充在所相应的单元格中。为该表添加汇总行，在汇总行单元格中分别计算每月图书的总销量。1月《Office商务办公好帮手》销售分析=SUMIFS(表1[销量（本）],表1[图书名称],A4,表1[日期],">=2023-1-1",表1[日期],"<2023-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),"00202300月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套销售部助理小王需要根据2023年和2023年的图书产品销售情况进行记录分析，以便制订新的销售计划和工作任务。（3）“图书定价”=VLOOKUP([@图书名称],表2,2,0)（4）“销量额小计”=IF([@销量（本）]>=40,[@单价]*[@销量（本）]*0.93,[@单价]*[@销量（本）])（5）所在区域=VLOOKUP(MID([@发货地址],1,3),表3,2,0)（7）记录报告2023年所有图书订单的销售额=SUMIFS(表1[销售额小计],表1[日期],">=2023-1-1",表1[日期],"<=2023-12-31")《MSOffice高级应用》图书在2023年的总销售额=SUMIFS(表1[销售额小计],表1[图书名称],订单明细!D7,表1[日期],">=2023-1-1",表1[日期],"<=2023-12-31")隆华书店在2023年第3季度（7月1日~9月30日）的总销售额=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2023-7-1",表1[日期],"<=2023-9-30")隆华书店在2023年的每月平均销售额（保存2位小数）=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2023-1-1",表1[日期],"<=2023-12-31")/122023年隆华书店销售额占公司全年销售总额的比例（保存2位小数）=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!C14,表1[日期],">=2023-1-1",表1[日期],"<=2023-12-31")/SUMIFS(表1[销售额小计],表1[日期],">=2023-1-1",表1[日期],"<=2023-12-31")第17套小林是北京某师范大学财务处的会计，计算系计算机基础教研室提交了该教研室2023年的课程授课情况。（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<2023,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(2023,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[征询日期],">=2023-1-1",表1[征询日期],"<=2023-1-31")张乐2月=SUMIFS(表1[成交金额],表1[销售经理],A3,表1[征询日期],">=2023-2-1",表1[征询日期],"<=2023-2-28")张乐