计算机二级考试公式大全.docx
《计算机二级考试公式大全.docx》由会员分享,可在线阅读,更多相关《计算机二级考试公式大全.docx(6页珍藏版)》请在冰点文库上搜索。
小李今年毕业:
(图书名称)=VLOOKUP(D3,编号对照!
$A$2:
$C$19,2,FALSE)
(订单明细工作表)=VLOOKUP(D3,编号对照!
$A$2:
$C$19,3,FALSE)
SUMIFS=(求和区域,条件区域1,条件1,[条件区域2,条件2],...)
小蒋是一位中学教师:
(班级)=MID(A2,4,1)&"班"
某公司拟对其产品:
(销售额排名)=RANK(D2,$D$2:
$D$21,0)
财务部助理小王:
(是否加班)=IF(WEEKDAY(A3,2)>5,"是","否")
(地区)=LEFT(C3,3)
(费用类别)=VLOOKUP(E3,费用类别!
$A$2:
$B$12,2,FALSE)
(二季度北京差旅费)=SUMIFS(费用报销管理!
G3:
G401,费用报销管理!
A3:
A401,">=2013-04-01",费用报销管理!
A3:
A401,"<=2013-06-30",费用报销管理!
D3:
D401,"北京市")
(钱顺卓报销火车票)=SUMIFS(费用报销管理!
G3:
G401,费用报销管理!
B3:
B401,"钱顺卓",费用报销管理!
F3:
F401,"火车票")
(飞机票所占比例)=SUMIF(费用报销管理!
F3:
F401,"飞机票",费用报销管理!
G3:
G401)/SUM(费用报销管理!
G3:
G401)
(周末补助总额)=SUMIFS(费用报销管理!
G3:
G401,费用报销管理!
H3:
H401,"是",费用报销管理!
F3:
F401,"通讯补助")
文涵是大地公司的:
(销售额)=VLOOKUP(D4,商品均价,2,0)*E4
小李是东方公司的会计:
(应交个人所得税)=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)))))))
小李是北京某政法学院:
(年级排名)=RANK(M3,M$3:
M$102,0)
(班级)=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二班",IF(MID(B3,3,2)="03
销售部助理小王:
(图书编号)=VLOOKUP(D3,图书编目表!
$A$2:
$B$9,2,FALSE)
期末考试结束了:
(性别)=IF(MOD(MID(C2,17,1),2)=1,"男","女")
(出生日期)=MID(C2,7,4)&"年"&MID(C2,11,2)&"月"&MID(C2,13,2)&"日"
(年龄)=INT((TODAY()-E2)/365)
“=IF(F2>=90,"优秀",IF(F2>=75,"良好",IF(F2>=60,"及格",IF(F2>60,"及格","不及格")))”,“=VLOOKUP(A3,初三学生档案!
$A$2:
$B$56,2,0)
“=VLOOKUP(A3,数学!
$A$2:
$F$45,6,0)”,
“=VLOOKUP(A3,语文!
$A$2:
$F$45,6,0)”,
小李是某政法学院:
4.(排名)=RANK(L3,$K$3:
$K$6,0)(总平均)=AVERAGE(C3:
C27)
6.=RANK(K3,$K$3:
$K$6,0)(总平均)=AVERAGE(B3:
B6)
销售部门主管大华:
2.(一季度销售情况表)=VLOOKUP(B2,产品基本信息表!
$B$1:
$C$21,2,0)3.(C2填)=SUMIF(一季度销售情况表!
$B$2:
$B$44,B2,一季度销售情况表!
$D$2:
$D44)
(D2填)=SUMIF(一季度销售情况表!
$B$2:
$B$44,B2,一季度销售情况表!
$F$2:
$F44)(E2填)=SUMIF(一季度销售情况表!
$B$2:
$B$43,B2,一季度销售情况表!
$D$2:
$D43)(F2填)=SUMIF(一季度销售情况表!
$B$2:
$B$43,B2,一季度销售情况表!
$F$2:
$F43)(G2处填)=C2+E2(H2处填)=D2+F24.(I2填)=RANK(H2,$H$2:
$H$21,0)
滨海市对重点:
2.(按班级汇总)=COUNTIFS(成绩单!
$A$2:
$A$950,按班级汇总!
$A2,成绩单!
$B$950,按班级汇总!
$B2)(D2填)=MAX((成绩单!
$A$2:
$A$950=按班级汇总!
$A2)*(成绩单!
$B$2:
$B$950=按班级汇总!
$B2)*成绩单!
$D$2:
$D$950)按shift+ctri+enter
(E2填)=MIN(IF((成绩单!
$A$2:
$A$950=按班级汇总!
$A2)*(成绩单!
$B$2:
$B$950=按班级汇总!
$B2),成绩单!
$D$2:
$D$950)按shift+ctri+enter
(F2填)=AVERAGEIFS(成绩单!
$D$2:
$D$950,成绩单!
$A$2:
$A$950,按班级汇总!
$A2,成绩单!
$B$950,按班级汇总!
$B2)(G2填)=SUM(小分统计!
$C2:
$AP2)(H2填)=SUM(小分统计!
$AQ2:
$AZ2)
(按学校汇总)=COUNTIFS(成绩单!
$A$2:
$A$950,按学校汇总!
$A2)
C2单元格=MAX((成绩单!
$A$2:
$A$950=按学校汇总!
$A2)*成绩单!
$D$2:
$D$950)
D2单元格=MIN(IF(成绩单!
$A$2:
$A$950=按学校汇总!
$A2,成绩单!
$D$2:
$D$950))
E2单元格=AVERAGEIFS(成绩单!
$D$2:
$D$950,成绩单!
$A$2:
$A$950,按学校汇总!
$A2)F2单元格=SUM((按班级汇总!
$A$2:
$A$33=按学校汇总!
$A2)*(按班级汇总!
$C$2:
$C$33)*(按班级汇总!
$G$2:
$G$33))/$B2
G2单元格=SUM((按班级汇总!
$A$2:
$A$33=按学校汇总!
$A2)*(按班级汇总!
$C$2:
$C$33)*(按班级汇总!
$H$2:
$H$33))/$B2
H2单元格=SUM((小分统计!
$A$2:
$A$33=$A2)*小分统计!
C$2:
C$33*按班级汇总!
$C$2:
$C$33)/$B2/分值表!
B$3”
小赵是参加不就大学生:
(4)M3单元格=SUM(B3:
L3)B15单元格=AVERAGE(B3:
B14)
(6)B3单元格=INT(1+(MONTH(A3)-1)/3)&"季度"
为让利消费者:
(2)停车收费记录=VLOOKUP(C2,收费标准!
A$3:
B$5,2,0)
J2单元格=DATEDIF(F2,H2,”YD”)*24+(I2-G2)
(3)计算收费金额K2单元格=ROUNDUP((HOUR(J2)*60+MINUTE(J2))/15,0)*E2
计算拟收费金额L2单元格=INT((HOUR(J2)*60+MINUTE(J2))/15)*E2
计算差值M2单元格=K2-L2
(4)K551单元格=sum(k2:
k550)
销售部助理小王:
(3)E3单元格=VLOOKUP(D3,$A$3:
$B$19,2,0)
(4)“订单明细”I3单元格=IF([@销量(本)]>=40,[@单价]*[@销量(本)]*0.93,[@单价]*[@销量(本)])
(5)“订单明细”H3单元格=VLOOKUP(MID([@发货地址],1,3),表3,2,0)
(6)“统计报告”B3单元格=SUMIFS(表1[销售额小计],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")
“统计报告”B4单元格=SUMIFS(表1[销售额小计],表1[图书名称],订单明细!
D7,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")
“统计报告”B5单元格=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],">=2013-7-1",表1[日期],"<=2013-9-30")
“统计报告”B6单元格=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],">=2012-1-1",表1[日期],"<=2012-12-31")/12
“统计报告”B7单元格=SUMIFS(表1[销售额小计],表1[书店名称],订单明细!
C14,表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")/SUMIFS(表1[销售额小计],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31")
小林是北京某师范大学财务处的会计:
(3)“课时费统计表”F3单元格=VLOOKUP(E3,教师基本信息!
$D$3:
$E$22,2,FALSE)
“课时费统计表”G3单元格“=VLOOKUP(F3,课时费标准!
$A$3:
$B$6,2,FALSE)
先在“授课信息表”中增加“学时数”列。
在“授课信息表”的F2单元格输入“学时数”,回车选择“F3”,同理使用VLOOKUP函数计算“学时数”。
第1个参数框中用鼠标选择“E3”;第2个参数框中选择“课程基本信息”工作表中的B3:
C16区域;第3个参数框中输入“2”;第4个参数框中输入“FALSE”或者“0”
小李是公司的出纳:
(3)“G2”单元格=IF(H2=0,"平",IF(H2>0"借","贷"))
小赵是某书店的销售人员:
(3)D3单元格VLOOKUP:
1参“A4”;2参“销量信息”;3参“3”,4参“0”
小伟在自己在读的学院里面:
(3)K2单元格=SUM(D2:
J2)L2单元格=AVERAGE(D2:
J2)
小刘是一所中学负责人:
(3)k2=SUM(D3:
J3)L2=AVERAGE(D3:
J3)
(4)对应班级C3=MID(A3,5,1)
(5)姓名填充=vlookup(A3,学号对照!
$A$3:
$B$20,2FALSE)
小李在东方公司:
2提取生日=MID(F3,7,4)&”年’’&MID(F3,11,2)&’’月’’&MID(F3,13,2)&’’日’’
3工龄=INT(TODAY()-I3/365)4基础工资L3=J3*工龄工资!
$B$3M3=K3+L3
5基础工资总额=SUM(员工档案!
M3:
M37)6项目经理基本工资=员工档案!
k6+员工档案!
k77平均基本工资average(员工档案!
H3:
H37,”本科’’,员工档案!
k3:
K37)
小王今年毕业:
3图书单价填充F3=VIOOKUP(D3,图书定价!
$A$3:
$C$19,3,FALSE)
4销售情况H列=F3*G3
销售部助理小王2销售总和=SUM(D3:
I3)3销售排行榜=”第’’&RANK.EQ([@个人销售总计],[个人销售总计]&”名”4销售达标率=COUNTIF(表1[一月份],”>60000”)/COUNT(表1[一月份])5销售业绩B4=LARGE(表1[一月份],1)B5=LARGE(表1[一月份],2)E5=LARGE(表1[四月份],3)B6=LARGE(表1[一月份],3)E6=LARGE(表1[四月份],4)))
李东阳是某家用电:
5、产品价格E4=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)
7、填充公式=OR(WEEKDAY($B4,2)=6,WEEKDAY($B4,2)=7,)
每年年终4:
F2“=IF(MOD(MID(E2,17,1),2)=1,"男","女")”
步骤2:
G2“=MID(E2,7,4)&"年"&MID(E2,11,2)&"月"&MID(E2,13,2)&"日"”
步骤3:
H2“=INT(DAYS360([@出生日期],DATE(2015,9,30))/360)”,
步骤4:
M2“=IF(K2<1,0,IF(K2<10,K2*20,IF(K2<30,K2*30,K2*50)))”
步骤5:
N2单元格中输入公式“=L2+M2”
5、B4“=VLOOKUP(A4,档案,2,0)”,
“年终奖金”表C4“=VLOOKUP(A4,档案,3,0)”
“年终奖金”表D4“=VLOOKUP(A4,档案,14,0)”
“年终奖金”表E4“=D4*12*0.15”,
6、“年终奖金”表F4=E4/12,“年终奖金”工表G4=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))))))
“年终奖金”表H4=E4-G4
7.“12月工资表”表E4“=VLOOKUP(A4,年终奖金!
$A$4:
$H$71,5,0)”,
“12月工资表”表L4“=VLOOKUP(A4,年终奖金!
$A$4:
$H$71,7,0)”,
在“12月工资表”表M4单“=H4-I4-K4-L4”,
8.“工资条”表A2单元格“=CHOOSE(MOD(ROW(),3)+1,OFFSET('12月工资表'!
A$3,ROW()/3,),"",'12月工资表'!
A$3)”,
正则明事务所统计员5、I3单元格=IF(AND(F3="是",G3="是",H3="是"),"完成","未完成")”,
6.J3=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.
“员工个人情况统计”表B3单元格=COUNTIF(表1[责任人],[@姓名])”
C3单元格,=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],0)
D3单元格=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],1)
E3单元格=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],2)
F3单元格=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],3)
G3单元格=COUNTIFS(表1[责任人],[@姓名],表1[报告修改次数],4)
H3单元格=SUMIF(表1[责任人],[@姓名],表1[报告奖金])
B8单元格,输入公式“=SUM(B3:
B7)
销售经理小李6.F3=VLOOKUP(E3,商品单价!
$A$3:
$B$7,2,0)”,
7.J3“=I3*F3”,9.“月统计表”工作表,B3“=SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],">=2014-1-1",表1[咨询日期],"<=2014-1-31")
C3“=SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],">=2014-2-1",表1[咨询日期],"<=2014-2-28")”
D3“=SUMIFS(表1[成交金额],表1[销售经理],A3,表1[咨询日期],">=2014-3-1",表1[咨询日期],"<=2014-3-31")
E3=SUM(B3:
D3)B6=SUM(B3:
B5)
晓雨任职人力资源部门:
3.N3“=LEFT(C3,LENB(C3)-LEN(C3))5.L3“=AVERAGE(G3:
K3)
6.“成绩单”M3“=IF(OR(G3<60,H3<60,I3<60,J3<60,K3<60),"不合格",IF(L3>=85,"优",IF(L3>=75,"良","及格")))
马老师负责:
(3)E4“=LOOKUP(MID(B4,5,2),LEFT(行政区划代码!
B$4:
B$38,2),MID(行政区划代码!
B$4:
B$38,4,LEN(行政区划代码!
B$4:
B$38)-3))在F4“=LEFT(B4,3)
L4“=IF(MID(B4,4,1)="1",J4*0.5+K4*0.5,J4*0.6+K4*0.4)
(5)D5单元格=COUNTIFS(部门代码,B5,性别,"女")
E5单元格=COUNTIFS(部门代码,B5,性别,"男")
F5单元格=D5+E5
G5单元格=D5/F5
H5单元格=MIN(IF(名单!
F$4:
F$1777=统计分析!
B5,名单!
J$4:
J$1777))
“新建规则”,弹出“新建格式规则”对话框:
输入公式“=MOD(ROW(),2)=0”,
在“应用于”文本框中设置数据区域“=$B$3:
$H$26”,单击“确定”按钮。
某企业员工小韩
(2)F3单元格=$C$2/E3*$C$3(3)G3单元格=$C$4*E3*0.5”(4)H3单元格=F3+G3(7)C5单元格=SQRT(2*C2*C3/C4)(8)B7单元格=SQRT(2*C2*C3/C4)