未来教育Excel公式汇总情况.docx

未来教育Excel公式汇总情况.docx

《未来教育Excel公式汇总情况.docx》由会员分享，可在线阅读，更多相关《未来教育Excel公式汇总情况.docx（15页珍藏版）》请在冰豆网上搜索。

未来教育Excel公式汇总情况

未来教育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="《MSOffice高级应用》"）,订单明细表!

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套 

首先不得不提醒一下，这套题非常多坑。

1坑一：

在做第一步时就要先对订单编号的重复值设置成要求的颜色，才能对它排序。

2坑二：

即使你把前面的题都做对了，但是后面创建的“2012年书店销量”数据透视表的的位置如果不是放在最前面，同样会得到很少的分数。

3坑三：

为了统计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"）/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"）