2024年3月23日发(作者：)

第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年度公司差旅报销情况，现在请按照如下需求，在文

档中完成工作

〔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%-135

05)))))))

〔请不要吐在屏幕或者键盘上〕

第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套

小赵是某书店的销售人员，负责电脑类图书的销售情况，并按月份上报分析结果。

销量