未来教育Excel公式汇总.docx
未来教育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) MS Office高级应用图书在2012年的总销售额 =SUMPRODUCT(1*(订单明细表!E3:E262="MS Office高级应用"),订单明细表!H3:H262)(7)隆华书店在2011年第3季度7月1日9月30日的总销售额 =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年第二季度发生在市的差旅费用金额总计为 ：先排序再做=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(费用报销管理!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%-2755,IF(K3<=80000,K3*35%-5505,IF(K3>80000,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/100-2755,IF(K3<=80000,K3*35/100-5505,IF(K3>80000,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级法律这个表的左边。谨记：你一定要将这新创立的表放置在“2012级法律这个表的右边。否那么，即使你上面的很多都做对了，也不会得高分的。第9套 首先不得不提醒一下，这套题非常多坑。 坑一：在做第一步时就要先对订单编号的重复值设置成要求的颜色，才能对它排序。 坑二：即使你把前面的题都做对了，但是后面创立的“2012年书店销量数据透视表的的位置如果不是放在最前面，同样会得到很少的分数。 坑三：为了统计2013年各类图书在每月的销售量，你必须自建一个列，然后对这个列进展自定义设置日期。这套题有大家都熟悉的VLOOKUP公式=VLOOKUP(图书名称,表3,2,FALSE)还有Excel 中的 SUMIFS 函数用于根据你指定的条件计算该函数的所有参数的总和=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销量本,表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,初三学生档案!$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(F2>72,"与格","不与格")第11套 3课时标准：=VLOOKUP(F3,费用标准,2,FALSE)学时数 =SUMIF(授课信息表!$D$3:$D$72,E3,授课信息表!$F$3:$F$72) 第12套 (3) 方向：=IF(H2=0,"平",IF(H2>0,"借","贷")余额计算简单第13套 (3)销量 =VLOOKUP(A4,销量信息,3,FALSE) 销售额=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)项目经理的根本工资总额 =员工档案!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日期,"<=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")/122013年隆华书店销售额占公司全年销售总额的百分比保存2位小数 =SUMIFS(表1销售额小计,表1书店名称,订单明细!C14,表1日期,">=2013-1-1",表1日期,"<=2013-12-31")/SUMIFS(表1销售额小计,表1日期,">=2013-1-1",表1日期,"<=2013-12-31")13 / 13
