未来教育Excel公式汇总情况

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) MS Office 高级应用图书在 2012 年的总销售额=SUMPRODUCT(1*(订 单 明 细 表 !E3:E262=MS Office 高级应用 ),订 单明 细表!H

2、3: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套班 ,2 班,3 班)(4) 班级 =LOOKUP(MID(A2,3,2),01,02,03,1(3) 平均分的公式是： =AVERAGE(D2:J2)第3套(2) 销售额排名=PANK(D

3、2,$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 年差旅费用金额中，飞机票占所有报销费用

4、的比例为（保留 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)*E

5、4第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(K380000,K3*45%-13505),2)细心体会 ：上面这条公式是答案自带的，但是存在错误，在软件里练习时也许这个错误的 答案就是标准答案。 但是在真正的考试中， 是不能得分的 (因为我当年考试时就是遇到这道 题，我按照自己的做法最对了) 。错误点在于：第 5 个 If 语句里的 5500 应该改为 55

6、000 。 而且这一步骤没有必要使用四舍五入的方法。= 本人推荐使用下面这条公式=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(K380000,K3*45/100-13505)(8)管理部门应付工资合计本人觉得在一步里他们给的参考答案是不能接受的， 因为完全没有按照题目要求的使用“分类汇总的功能”来做。建议大家按照题目的要求来做。这道题可以没有公式。第8套(3) 年级排名=RANK(M3

7、,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套首先不得不提醒一下，这套题非常多坑。 坑一：在做第一步时就要先对订单编号的重复值设置成要求的颜色，

8、才能对它排序。 坑二：即使你把前面的题都做对了，但是后面创建的“ 2012 年书店销量”数据透视表的的位置如果不是放在最前面，同样会得到很少的分数。 坑三：为了 统计 2013 年各类图书在每月的销售量，你必须自建一个列，然后对这个列进行自定义设置日期。这套题有大家都熟悉的 VLOOKUP 公式=VLOOKUP（ 图书名称 , 表 3,2,FALSE）还有 Excel 中的 SUMIFS 函数用于根据你指定的条件计算该函数的所有参数的总和=SUMIFS( 表1销量(本) ,表1图书名称 ,图书名称 ,表1列1,1)=SUMIFS( 表 1 销量(本),表1图书名称 ,图书名称 ,表 1列1,2

9、)=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

10、 销量(本),表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)或是：=VLOOKU

11、P(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(F272, 及格,不及格 )第 11 套（3）课时标准：=VLOOKUP（F3, 费用标准 ,2,FALSE）学时数=SUMIF( 授课信息表 !$D$3:$D$72,E3, 授课信息表 !$F$3:$F$72)第 12 套(3)方向：=IF(H2=0, 平 ,IF(H20, 借, 贷)余额计算简单第

12、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)工龄工资

13、=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