未来教育Excel公式汇总.docx

《未来教育Excel公式汇总.docx》由会员分享，可在线阅读，更多相关《未来教育Excel公式汇总.docx（13页珍藏版）》请在三一办公上搜索。

1、未来教育Excel中的公式汇总第1套(2)图书名称=VLOOKUP(D3,编号对照!$A$3:$C$19,2,FALSE)2的含义：返回数据在查找区域的第2列数(3)单价=VLOOKUP(D3,编号对照!$A$3:$C$19,3,FALSE)3的含义：返回数据在查找区域的第3列(4)小计=单价*销量本(5)所有订单的总销售额=SUM(订单明细表!H3:H636)(6)MSOffice高级应用图书在2012年的总销售额=SUMPRODUCT(1*(订单明细表!E3:E262=MS Office高级应用),订单明细表!H3:H262)(7)隆华书店在2011年第3季度7月1日9月30日的总销售额=

2、SUMPRODUCT(1*(订单明细表!C305:C461=隆华书店),订单明细表!H350:H461)(8)隆华书店在2011年的每月平均销售额保存2位小数=SUMPRODUCT(1*(订单明细表!C263:C636=隆华书店),订单明细表!H263:H636)/12第2套(4)班级=LOOKUP(MID(A2,3,2),01,02,03,1班,2班,3班)(3)平均分的公式是：=AVERAGE(D2:J2)第3套(2)销售额排名=PANK(D2,$D$2:$D$21,0)第5套(2)是否加班=IF(WEEKDAY(A3,2)5,是,否)(3)地区=LEST(C3,3)(5)2013年第二季

3、度发生在市的差旅费用金额总计为：先排序再做=SUMPRODUCT(1*(费用报销管理!D74:D340=市),费用报销管理!G74:G340)(6)2013年钱顺卓报销的火车票总计金额为：先排序再做=SUMPRODUCT(1*(费用报销管理!B3:B401=钱顺卓),1*(费用报销管理!F3:F401=火车票),费用报销管理!G3:G401) (7)2013年差旅费用金额中，飞机票占所有报销费用的比例为保存2位小数这条公式要好好和下面的那条公式比照，感受一样点和不同点，理解1存在的意义=SUMPRODUCT(1*(费用报销管理!F3:F401=飞机票),费用报销管理!G3:G401)/SUM(

4、费用报销管理!G3:G401) (8)2013年发生在周末星期六和星期日中的通讯补助总金额为为什么这条公式就可以不用1，好好体会吧=SUMPRODUCT(费用报销管理!H3:H401=是)*(费用报销管理!F3:F401=通讯补助),费用报销管理!G3:G401)第6套(4)销售额=VLOOKUP(D4,商品均价,2,0)*E4第7套(5)应交个人所得税=ROUND(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=5500,K3*30%-275

5、5,IF(K380000,K3*45%-13505),2)细心体会：上面这条公式是答案自带的，但是存在错误，在软件里练习时也许这个错误的答案就是标准答案。但是在真正的考试中，是不能得分的因为我当年考试时就是遇到这道题，我按照自己的做法最对了。错误点在于：第5个If语句里的5500应该改为55000。而且这一步骤没有必要使用四舍五入的方法。=本人推荐使用下面这条公式=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/100-1005,IF(K3=55000,K3*30/1

6、00-2755,IF(K380000,K3*45/100-13505)(8)管理部门应付工资合计本人觉得在一步里他们给的参考答案是不能承受的，因为完全没有按照题目要求的使用“分类汇总的功能来做。建议大家按照题目的要求来做。这道题可以没有公式。第8套(3)年级排名=RANK(M3,M$3:M$102,0)(4)班级=法律&TEXT(MID(B3,3,2),DBNum1)&班=本人推荐使用下面这条公式=LOOKUP(MID(B3：B102,3,2),01,02,03,04,法律一班,法律二班,法律三班,法律四班) 细节体会：这套题的的第5小题：默认创立的数据透视表 “班级平均分是在“2012级法律

7、这个表的左边。谨记：你一定要将这新创立的表放置在“2012级法律这个表的右边。否那么，即使你上面的很多都做对了，也不会得高分的。第9套首先不得不提醒一下，这套题非常多坑。 坑一：在做第一步时就要先对订单编号的重复值设置成要求的颜色，才能对它排序。 坑二：即使你把前面的题都做对了，但是后面创立的“2012年书店销量数据透视表的的位置如果不是放在最前面，同样会得到很少的分数。 坑三：为了统计2013年各类图书在每月的销售量，你必须自建一个列，然后对这个列进展自定义设置日期。这套题有大家都熟悉的VLOOKUP公式=VLOOKUP(图书名称,表3,2,FALSE)还有Excel 中的 SUMIFS 函

8、数用于根据你指定的条件计算该函数的所有参数的总和=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,1)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,2)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,3)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,4)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,5)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,6)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,7)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,8)=SUMIFS(表1销量

9、本,表1图书名称,图书名称,表1列1,9)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,10)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,11)=SUMIFS(表1销量本,表1图书名称,图书名称,表1列1,12)第10套(3)性别：=IF(MOD(MID(C2,17,1),2)=1,男,女)出生日期：=-TEXT(MID(C2,7,8),0年00月00日)年龄：=DATEDIF(-TEXT(MID(C2,7,8),0-00-00),TODAY(),y)(4)语文=VLOOKUP(A2,初三学生档案!$A$2:$B$56,2,0)或是：=VLOOKUP(A2,初

10、三学生档案!$A$1:$B$56,2,FALSE)学期成绩：=SUM(C2*30%)+(D2*30%)+(E2*40%)班级名次=第&RANK(F2,$F$2:$F$45)&名 “期末总评=IF(F2=102,优秀,IF(F2=84,良好,IF(F2=72,与格,IF(F272,与格,不与格)第11套3课时标准：=VLOOKUP(F3,费用标准,2,FALSE)学时数=SUMIF(授课信息表!$D$3:$D$72,E3,授课信息表!$F$3:$F$72)第12套(3)方向：=IF(H2=0,平,IF(H20,借,贷)余额计算简单第13套(3)销量=VLOOKUP(A4,销量信息,3,FALSE

11、)销售额=C4*D4第14套公式很简单，不做汇总第15套(4)班级这是经常出现的题型，要求掌握=IF(MID(A3,4,2)=01,1班,IF(MID(A3,4,2)=02,2班,3班)(5)VLOOKUP函数出现的频率很高，要求掌握=VLOOKUP(A3,学号对照!$A$3:$B$20,2,FALSE)第16套(2)出生日期=MID(F3,7,4)&年&MID(F3,11,2)&月&MID(F3,13,2)&日(3)工龄=INT(TODAY()-I3)/365)(4)工龄工资=J3*工龄工资!$B$3(5)所有人的根底工资=SUM(员工档案!M3:M37)(6)项目经理的根本工资总额=员工档

12、案!K9+员工档案!K10 (7)本科生平均根本工资=AVERAGEIF(员工档案!H3:H44,本科,员工档案!K3:K44)第17套本套题可以参考第一套题第18套季度=第&INT(1+(MONTH(A3)-1)/3)&季度服装服饰本套题要掌握SUBTOTAL函数的使用=SUBTOTAL(1,C3:C3)第19套(2)停放时间=DATEDIF(F2,H2,d)*24+(I2-G2)(3)收费金额=E2*(TRUNC(HOUR(J2)*60+MINUTE(J2)/15)+1)拟收费金额=E2*TRUNC(HOUR(J2)*60+MINUTE(J2)/15)第20套(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日期,=2012-1-1,表1日期,=2013-7-1,表1日期,=2012-1-1,表1日期,=2013-1-1,表1日期,=2013-1-1,表1日期,=2013-12-31)13 / 13