1、未來(lái)教育Excel中的公式匯總第1套 (2)圖書(shū)名稱 =VLOOKUP(D3,編號(hào)對(duì)照!$A$3:$C$19,2,FALSE) 2的含義：返回?cái)?shù)據(jù)在查找區(qū)域的第2列數(shù)(3)單價(jià) =VLOOKUP(D3,編號(hào)對(duì)照!$A$3:$C$19,3,FALSE) 3的含義：返回?cái)?shù)據(jù)在查找區(qū)域的第3列(4)小計(jì) =單價(jià)*銷量（本） (5)所有訂單的總銷售額 =SUM(訂單明細(xì)表!H3:H636) (6) MS Office高級(jí)應(yīng)用圖書(shū)在2012年的總銷售額 =SUMPRODUCT(1*(訂

2、單明細(xì)表!E3:E262="MS Office高級(jí)應(yīng)用"),訂單明細(xì)表!H3:H262)(7)隆華書(shū)店在2011年第3季度（7月1日9月30日）的總銷售額 =SUMPRODUCT(1*(訂單明細(xì)表!C305:C461 =”隆華書(shū)店”),訂單明細(xì)表!H350:H461) (8)隆華書(shū)店在2011年的每月平均銷售額（保留2位小數(shù)） =SUMPRODUCT(1*(訂單明細(xì)表!C263:C636="隆華書(shū)店"),訂單明細(xì)表!H263:H636)/12第2套 (4)班級(jí) =LOOKUP(MID(A2,3,

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)地區(qū) =LEST(C3,3) (5)2013年第二季度發(fā)生在北京市的差旅費(fèi)用金額總計(jì)為&

4、#160;：（先排序再做）=SUMPRODUCT(1*(費(fèi)用報(bào)銷管理!D74:D340="北京市"),費(fèi)用報(bào)銷管理!G74:G340) (6)2013年錢(qián)順卓報(bào)銷的火車票總計(jì)金額為 ：（先排序再做）=SUMPRODUCT(1*(費(fèi)用報(bào)銷管理!B3:B401="錢(qián)順卓"),1*(費(fèi)用報(bào)銷管理!F3:F401="火車票"),費(fèi)用報(bào)銷管理!G3:G401) (7)2013年差旅費(fèi)用金額中，飛機(jī)票占所有報(bào)銷費(fèi)用的比例為（保留2位小數(shù)） （這條公式要好好和下面的那條公式對(duì)比，感受相同點(diǎn)和不同點(diǎn)，理解1存在的意義）=

5、SUMPRODUCT(1*(費(fèi)用報(bào)銷管理!F3:F401="飛機(jī)票"),費(fèi)用報(bào)銷管理!G3:G401)/SUM(費(fèi)用報(bào)銷管理!G3:G401) (8)2013年發(fā)生在周末（星期六和星期日）中的通訊補(bǔ)助總金額為 （為什么這條公式就可以不用1，好好體會(huì)吧）=SUMPRODUCT(費(fèi)用報(bào)銷管理!H3:H401="是")*(費(fèi)用報(bào)銷管理!F3:F401="通訊補(bǔ)助"),費(fèi)用報(bào)銷管理!G3:G401) 第6套 (4)銷售額 =VLOOKUP(D4,商品均價(jià),2,0)*E4第7套 (5)應(yīng)交個(gè)人所

6、得稅 =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) 細(xì)心體會(huì)：上面這條公式是答案自帶的，但是存在錯(cuò)誤，在軟件里練習(xí)時(shí)也許這個(gè)錯(cuò)誤的答案就是標(biāo)準(zhǔn)答案。但是在真正的考試中，是不能得分的（因?yàn)槲耶?dāng)年考試時(shí)就是遇到這道題，我按

7、照自己的做法最對(duì)了）。錯(cuò)誤點(diǎn)在于：第5個(gè)If語(yǔ)句里的5500應(yīng)該改為55000。而且這一步驟沒(méi)有必要使用四舍五入的方法。=本人推薦使用下面這條公式=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)管理部門(mén)應(yīng)付工資合計(jì) 本

8、人覺(jué)得在一步里他們給的參考答案是不能接受的，因?yàn)橥耆珱](méi)有按照題目要求的使用“分類匯總的功能”來(lái)做。建議大家按照題目的要求來(lái)做。這道題可以沒(méi)有公式。第8套 (3)年級(jí)排名   =RANK(M3,M$3:M$102,0) (4)  班級(jí)="法律"&TEXT(MID(B3,3,2),"DBNum1")&"班"=本人推薦使用下面這條公式=LOOKUP(MID(B3：B102,3,2),"01","02","0

9、3","04","法律一班","法律二班","法律三班","法律四班") 細(xì)節(jié)體會(huì)：這套題的的第5小題：默認(rèn)創(chuàng)建的數(shù)據(jù)透視表 “班級(jí)平均分”是在“2012級(jí)法律”這個(gè)表的左邊。謹(jǐn)記：你一定要將這新創(chuàng)建的表放置在“2012級(jí)法律”這個(gè)表的右邊。否則，即使你上面的很多都做對(duì)了，也不會(huì)得高分的。第9套 首先不得不提醒一下，這套題非常多坑。 坑一：在做第一步時(shí)就要先對(duì)訂單編號(hào)的重復(fù)值設(shè)置成要求的顏色，才能對(duì)它排序。 坑二：即使你把前面的題都做對(duì)了，但是后面創(chuàng)建的“2012年書(shū)店銷量”

10、數(shù)據(jù)透視表的的位置如果不是放在最前面，同樣會(huì)得到很少的分?jǐn)?shù)。 坑三：為了統(tǒng)計(jì)2013年各類圖書(shū)在每月的銷售量，你必須自建一個(gè)列，然后對(duì)這個(gè)列進(jìn)行自定義設(shè)置日期。這套題有大家都熟悉的VLOOKUP公式=VLOOKUP(圖書(shū)名稱,表3,2,FALSE)還有Excel 中的 SUMIFS 函數(shù)用于根據(jù)你指定的條件計(jì)算該函數(shù)的所有參數(shù)的總和=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,1)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,2)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,3)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,

11、表1列1,4)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,5)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,6)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,7)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,8)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,9)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,10)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,11)=SUMIFS(表1銷量（本）,表1圖書(shū)名稱,圖書(shū)名稱,表1列1,12)第10套 

12、(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)語(yǔ)文姓名=VLOOKUP(A2,初三學(xué)生檔案!$A$2:$B$56,2,0) 或是：=VLOOKUP(A2,初三學(xué)生檔案!$A$1:$B$56,2,FALSE) 學(xué)期成績(jī)

13、：=SUM(C2*30%)+(D2*30%)+(E2*40%)班級(jí)名次="第"&RANK(F2,$F$2:$F$45)&"名" “期末總評(píng)” =IF(F2>=102,"優(yōu)秀",IF(F2>=84,"良好",IF(F2>=72,"及格",IF(F2>72,"及格","不及格")第11套 （3）課時(shí)標(biāo)準(zhǔn)：=VLOOKUP(F3,費(fèi)用標(biāo)準(zhǔn),2,FALSE)學(xué)時(shí)數(shù)   =SU

14、MIF(授課信息表!$D$3:$D$72,E3,授課信息表!$F$3:$F$72) 第12套 (3)   方向：=IF(H2=0,"平",IF(H2>0,"借","貸")余額計(jì)算簡(jiǎn)單第13套 (3)銷量   =VLOOKUP(A4,銷量信息,3,FALSE) 銷售額=C4*D4第14套 公式很簡(jiǎn)單，不做匯總第15套 (4)班級(jí) （這是經(jīng)常出現(xiàn)的題型，要求掌握）=IF(MID(A3,4,2)="

15、;01","1班",IF(MID(A3,4,2)="02","2班","3班")(5)姓名（VLOOKUP函數(shù)出現(xiàn)的頻率很高，要求掌握） =VLOOKUP(A3,學(xué)號(hào)對(duì)照!$A$3:$B$20,2,FALSE)第16套 (2)出生日期 =MID(F3,7,4)&"年"&MID(F3,11,2)&"月"&MID(F3,13,2)&"日"(3)工齡 =INT(TODAY(

16、)-I3)/365)(4)工齡工資 =J3*工齡工資!$B$3 (5)所有人的基礎(chǔ)工資   =SUM(員工檔案!M3:M37) (6)項(xiàng)目經(jīng)理的基本工資總額 =員工檔案!K9+員工檔案!K10   (7)本科生平均基本工資   =AVERAGEIF(員工檔案!H3:H44,"本科",員工檔案!K3:K44)第17套 本套題可以參考第一套題第18套 季度 ="第"&INT(1+(MONTH(A3)-1

17、)/3)&"季度"服裝服飾（本套題要掌握SUBTOTAL函數(shù)的使用）=SUBTOTAL(1,C3:C3)第19套 (2)停放時(shí)間 =DATEDIF(F2,H2,"d")*24+(I2-G2) (3)收費(fèi)金額 =E2*(TRUNC(HOUR(J2)*60+MINUTE(J2)/15)+1)擬收費(fèi)金額 =E2*TRUNC(HOUR(J2)*60+MINUTE(J2)/15)第20套 (3)單價(jià)   =VLOOKUP(圖書(shū)名稱,表2,2,0) (4)銷售

18、額小計(jì)  =IF(銷量（本）>=40,單價(jià)*銷量（本）*0.93,單價(jià)*銷量（本）)(5)所屬區(qū)域 =VLOOKUP(MID(發(fā)貨地址,1,3),表3,2,0)(7) 2013年所有圖書(shū)訂單的銷售額 =SUMIFS(表1銷售額小計(jì),表1日期,">=2013-1-1",表1日期,"<=2013-12-31")MS Office高級(jí)應(yīng)用圖書(shū)在2012年的總銷售額 =SUMIFS(表1銷售額小計(jì),表1圖書(shū)名稱,訂單明細(xì)!D7,表1日期,">=2012-1-1",表1日期,"<=2012-12-31")隆華書(shū)店在2013年第3季度（7月1日9月30日）的總銷售額 =SUMIFS(表1銷售額小計(jì),表1書(shū)店名稱,訂單明細(xì)!C14,表1日期,">=2013-7-1",表1日期,"<=2013-9-30") 隆華書(shū)店在2012年的每月平均銷售額（保留2位小數(shù)） =SUMIFS(表1銷售額小計(jì),表1