excel函數(shù)公式應(yīng)用大全

..精品精品.精品excel公式應(yīng)用大全1、SUMPRODUCT函數(shù)：該函數(shù)的功能是在給定的幾組數(shù)組中將數(shù)組間對應(yīng)的元素相乘并返回乘積之和。例如：如圖1，如果想計算B3：C6和C3：E6這兩組區(qū)域的值，可以用以下公式：“=Sumproduct(B3:C6,D3:E6)”。圖12、ABS函數(shù)：如果在A1、B1單元格中分別輸入120、90，那么如果要求A1與B1之間的差的絕對值，可以在C1單元格中輸入以下公式：“=ABS(A1-B1)”。3、IF函數(shù)：如圖2，如果C3單元格的數(shù)據(jù)大于D3單元格，則在E3單元格顯示“完成任務(wù)，超出：”，否則顯示“未完成任務(wù)，差額：”，可以在E3單元格中輸入以下公式：“=IF(C3>D3,“完成任務(wù)，超出：”,”未完成任務(wù)，差額：””。..精品精品.精品圖24、Ceiling函數(shù)：該數(shù)值向上舍入基礎(chǔ)的倍數(shù)。如圖3，在C3單元格中輸入以下公式：“=CEILING(B3,C3)”；而“=FLOOR(B3,C3)”則是向下舍入。圖35、GCD函數(shù)：該函數(shù)計算最大公約數(shù)。如圖4，如果要計算B3：D3這一區(qū)域中3個數(shù)字的最大公約數(shù)，可以在E3單元格中輸入以下公式：“=GCD(B3,C3,D3)”。圖46、INT函數(shù)：該函數(shù)是向下舍入取整函數(shù)。如圖5，如果要計算顯示器和機箱的購買數(shù)量，可以在E3單元格中輸入以下公式：“=INT(D3/C3)”。..精品精品.精品圖57、LCM函數(shù)：該函數(shù)是計算最小公倍數(shù)。如圖6，如果要計算B3：D3這一區(qū)域中3個數(shù)字的最小公倍數(shù)，可以在E3單元格中輸入以下公式：“=LCM(B3,C3,D3)”。圖68、LN函數(shù)：該函數(shù)是計算自然對數(shù)，公式為：“=LN(B3)”。9、LOG函數(shù)：該函數(shù)是計算指定底數(shù)的對數(shù)，公式為：“=LOG10(B3)”。10、MOD函數(shù)：該函數(shù)是計算兩數(shù)相除的余數(shù)。如圖7，判斷C3能否被B3整除，可以在D4單元格中輸入以下公式：“=IF(MOD(B3,C3)=0,"是","否")”。圖7..精品精品.精品11、PI函數(shù)：使用此函數(shù)可以返回數(shù)字3.14159265358979，即數(shù)學(xué)常量PI，可精確到小數(shù)點后14位。如圖8，計算球體的面積，可以在C4單元格中輸入以下公式：“=PI()*(B3^2)*4)”；計算球體的體積，可以在D4單元格中輸入以下公式：“=(B3^3)*（4*PI())）/3”。圖812、POWER函數(shù)：此函數(shù)用來計算乘冪。如圖9，首先在單元中輸入底數(shù)和指數(shù)，然后在D3中輸入以下公式：“=POWER(B3,C3)”。圖913、PRODUCT函數(shù)：此函數(shù)可以對所有的以參數(shù)形式給出的數(shù)字相乘，并返回乘積。例如：某企業(yè)2005年度貸款金額為100000元，利率為1.5%，貸款期限為12個月。如圖10所示，直接在單元格E4中輸入以下公式：“=PRODUCT(B4,C4,D4)”。..精品精品.精品圖1014、RADIANS函數(shù)：此函數(shù)是用來將弧度轉(zhuǎn)換為角度的?？梢栽贑3單元格中輸入以下公式：“=RADIANS(B3)”。15、RAND函數(shù)：此函數(shù)可以返回大于等于0及小于1的均勻分布隨機數(shù)，每次計算工作表時都將返回一個新的數(shù)值。如果要使用函數(shù)RAND生成一個隨機數(shù)，并且使之不隨單元格的計算而改變，可以在編輯欄中輸入“=RAND()”，保持編輯狀態(tài)，然后按[F9]鍵，將公式永久性地改為隨機數(shù)。例如：在全班50名同學(xué)中以隨機方式抽出20名進(jìn)行調(diào)查，如圖11，在單元格中輸入開始號碼以及結(jié)束號碼，然后在單元格B4中輸入以下公式：“=1+RAND()*49”。圖1116、ROUND函數(shù)：此函數(shù)為四舍五入函數(shù)。如圖12，例如：將數(shù)字“12.3456”按照指定的位數(shù)進(jìn)行四舍五入，可以在D3單元格中輸入以下公式：“=ROUND(B3,C3)”。..精品精品.精品17、ROUNDDOWN函數(shù)：此函數(shù)為向下舍入函數(shù)。例如：出租車的計費標(biāo)準(zhǔn)是：起步價為5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。輸入不同的公里數(shù)，如圖13所示，然后計算其費用?？梢栽贑3單元格中輸入以下公式：“=IF(B3<=10,5+ROUNDDOWN(B3,0)*2,20+ROUNDDOWN((B3-10)*2,0)*2)”。圖1318、ROUNDUP函數(shù)：此函數(shù)為向上舍入函數(shù)。例如：現(xiàn)在網(wǎng)吧的管理一般是采用向上舍入法，不滿一個單元按照一個單位計算?，F(xiàn)假設(shè)每30分鐘計價0.5元，請計算如圖14中所示的上網(wǎng)所花費的費用。1）計算上網(wǎng)天數(shù)：首先在單元格C3中輸入以下公式：“=B3-A3”；2）計算上網(wǎng)分鐘數(shù)：上網(wǎng)分鐘數(shù)實際上就等于上網(wǎng)天數(shù)乘以60再乘以24，所以應(yīng)在單元格D3中輸入以下公式：“=C3*60*24”；3）計算計費時間：本例中規(guī)定每30分鐘計費一次，不滿30分鐘以30分鐘計價，所以應(yīng)在單元格E3中輸入以下公式：“=ROUNDUP(D3/30,0)”；4）計算上網(wǎng)費用：在單元格G3中輸入以下公式：“=E3*F3”。..精品精品.精品圖1419、SUBTOTAL函數(shù)：使用該函數(shù)可以返回列表或者數(shù)據(jù)庫中的分類匯總。通常利用[數(shù)據(jù)]—[分類匯總]菜單項可以很容易地創(chuàng)建帶有分類匯總的列表。

Function_num

函數(shù)返回值

Function_num

函數(shù)返回值

Function_num

函數(shù)返回值

1

Average

5

Min

9

Sum

2

Count

6

Product

10

Var

3

Counta

7

Stdev

11

warp

..精品精品.精品4max8Stdevp例如某班部分同學(xué)的考試成績?nèi)鐖D15，1）顯示最低的語文成績：首先在單元格B9中輸入“顯示最低的語文成績”的字樣，然后在單元格E9中輸入以下公式：“=SUBTOTAL(5,C3:C7)”；2）顯示最高的數(shù)學(xué)成績：首先在單元格B10中輸入“顯示最高的數(shù)學(xué)成績”的字樣，然后在單元格E10中輸入以下公式：“=SUBTOTAL(4,D37)”。圖1520、計算庫存量和獎金：假設(shè)某公司在月底要根據(jù)員工的業(yè)績發(fā)放工資并進(jìn)行產(chǎn)品的庫存統(tǒng)計，本例中規(guī)定員工的基本工資為600元，獎金按照銷售業(yè)績的8%提成，總工資等于基本工資與獎金之和。如圖16，1）在工作表中輸入相應(yīng)的數(shù)據(jù)信息；2）計算“現(xiàn)存庫量”：在單元格C15中輸入以下公式：“=C14-SUM(C3:C9)”；3）計算“銷售業(yè)績”：在單元格G3中輸入以下公式：“=SUMPRODUCT(C3:F3,$C$13F$13)”,函數(shù)SUMPRODUCT是計算數(shù)組C3：F3與數(shù)組$C$13F$13乘積的和，用數(shù)學(xué)公式表示出來就是：“=10*3050.5+10*1560.99+5*4489.9+20*2119”；4）計算獎金：獎金是按照銷售業(yè)績的8%提成得到的，這樣計算出來的結(jié)果可能會是小數(shù)，不好找零錢，所以這里采用向上舍入的方式得到整數(shù)，在單元格H3中輸入以下公式：“=ROUNDUP(G3*8%,0)”；5）計算總工資：由于總工資=基本工資+獎金，所以在單元格J3中輸入以下公式：“=SUM(H3:I3)”。..精品精品.精品圖1621、計算工資和票面金額：假設(shè)某公司的銷售人員的銷售情況如圖17所示，按照銷售業(yè)績的5%計算銷售提成，下面需要結(jié)合上例中的函數(shù)來計算銷售人員的銷售業(yè)績以及獎金工資，然后再計算出發(fā)放工資時需要準(zhǔn)備的票面數(shù)量。1）計算銷售業(yè)績：在單元格H13中輸入以下公式：“=SUMPRODUCT(C3:G3,$C$11G$11)”；2）計算提成：在本例中假設(shè)提成后出現(xiàn)小于1元的金額則舍入為1，所以需要使用ROUNDUP函數(shù)，在單元格I3中輸入以下公式：“=ROUNDUP(H3*5%,0)”；3）計算工資：在單元格K3中輸入以下公式：“=I3+J3”；4）計算100元的面值：在單元格L3中輸入以下公式：“=INT(K3/$L$2)”；5）計算50元的面值：在單元格M3中輸入以下公式：“=INT(MOD(K3,$L$2)/$M$2)”，此公式是使用MOD函數(shù)計算發(fā)放“MOD(K3,$L$2)”張100元后剩下的工資，然后利用取整函數(shù)INT得到50元票面的數(shù)量；6）計算10元的面值：在單元格N3中輸入以下公式：“=INT(MOD(K3,$M$2)/$N$2)”；7）計算5元的面值：在單元格O3中輸入以下公式：“=INT(MOD(K3,$N$2)/$O$2)”；8）計算1元的面值：在單元格P3中輸入以下公式：“=INT(MOD(K3,$O$2)/$P$2)”。..精品精品.精品

圖1722、DATE函數(shù)：在實際工作中經(jīng)常會用到此函數(shù)來顯示日期。例如：如圖18，在單元格中輸入相應(yīng)的年、月和圖書館日等信息，然后在單元格E3中輸入以下公式：“=DATE(B3,C3,D3)”。圖18..精品精品.精品23、DATEIF函數(shù)：假設(shè)有兩個已知日期——開始日期和截止日期，那么可以利用DATEIF函數(shù)來計算它們之間相差的年數(shù)、月數(shù)或者天數(shù)等。如圖19，在單元格D3中輸入以下公式：“=DATEDIF(B3,C3,"y")”。圖1924、DAYS360函數(shù)：該函數(shù)計算兩個日期之間的天數(shù)，在財務(wù)中經(jīng)常會用到，如果財務(wù)系統(tǒng)是基于一年12個月并且每月30天，可以使用該函數(shù)幫助計算借款天數(shù)或者支付款項等。例如：某企業(yè)不同時間的貸款如圖20所示，然后利用DAYS360函數(shù)來計算其借款的時間，并且計算出還款利息。1）計算“借款天數(shù)”：在單元格D3中輸入以下公式：“=DAYS360(B3,C3)”；2）計算“還款利息”：在單元格G3中輸入以下公式：“=D3*E3*F”。圖2025、WEEKDAY函數(shù)：使用此函數(shù)可以返回某個日期為星期幾。語法：WEEKDAY（serial_number,return_type）：其中參數(shù)serial_number代表要查找的那一天的日期，參數(shù)return_type為確定返回值類型的數(shù)字，詳細(xì)內(nèi)容如下表：..精品精品.精品

參數(shù)值

函數(shù)返回值1或者省略

返回數(shù)字1（星期日）到數(shù)字7（星期六）之間的數(shù)字。

2

返回數(shù)字1（星期一）到數(shù)字7（星期日）之間的數(shù)字。

3

返回數(shù)字0（星期一）到數(shù)字6（星期日）之間的數(shù)字。

例如：計算當(dāng)前日期是星期幾：如圖21所示，在單元格B3中輸入計算當(dāng)前日期的公式：“=WEEKDAY(B3,2)”。圖2126、WEEKNUM函數(shù)：使用此函數(shù)可以計算一年中的第幾周。例如：已知2006年6月9日是星期五，下面利用WEEKNUM函數(shù)計算在參數(shù)不同的情況下返回的周數(shù)。如圖22所示，在單元格B3中輸入計算當(dāng)前日期的公式：“=WEEKNUM(B3,C3)”。..精品精品.精品圖2227、WORKDAY函數(shù)：使用此函數(shù)可以返回某個日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值，工作日不包括周末和專門指定的日期。假設(shè)某出版社要求某個編輯從2006年3月1日起開始寫稿，利用80天將其完成（其中不包括三天節(jié)假日），此時可以利用WORKDAY函數(shù)計算出完成日期。如圖23所示，在單元格中輸入上述信息，然后在單元格C7中輸入以下公式：“=WORKDAY(C2,C3,C4:C6)”。圖2328、計算年假天數(shù)和工齡補貼：假設(shè)某公司規(guī)定，員工任職滿1年的開始有年假，第1至5年每年7天，第6年開始每年10天。截止到2005年6月9日，以工齡計算每年補貼100元，任職不足一年的按每人50元計算。如圖24所示：1）首先在工作表中輸入已知數(shù)據(jù)信息，然后根據(jù)公司規(guī)定的內(nèi)容在單元格F5中輸入以下公式：“=IF(DATEDIF($D5,TODAY(),"y")<1,"入職不夠一年",IF(DATE(C$2,MONTH($D5),DAY($D5))>TODAY(),"今年沒到期",IF(DATEDIF($D5,TODAY(),"y")<6,7,10)))”，以此可以計算出員工的休假天數(shù)；2）在單元格G5中輸入以下公式：“=IF(DATEDIF($D5,DATE($C$2,6,9),"y")>=1,DATEDIF($D5,DATE($C$2,6,9),"y")*100,50)”，以此可計算出員工的工齡補貼。..精品精品.精品圖24..精品精品.精品29、計算火車站寄存包裹費用：在火車站寄存包裹是按小時數(shù)收費的。有些按整小時計數(shù)，有些按半小時計數(shù)，沒有超過半小時的以半小時計，半小時以上一小時以內(nèi)的按一小時計。同時包裹的大小不同收費也不同，在本例中假設(shè)大的每小時6元，中型的每小時4元，小型的每小時2元，計算在火車站寄存包裹的費用。如圖25所示：1）計算寄存天數(shù)：首先輸入相關(guān)的信息，然后在單元格E4中輸入以下公式：“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)),DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))-1,DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4)))”，此時可計算出所有型號的包裹寄存的天數(shù)，在此公式中用到了IF函數(shù)，函數(shù)中的條件為“TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”，它是用來判斷取走時間是否超過了寄存時間，如果條件為真則表示還沒有超過一天，那么寄存的天數(shù)就是“DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))-1”，即走取的日期減去寄存的日期再減1，如果時間超過了，那么寄存的天數(shù)就是“DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))”，即取走的日期與寄存時的日期之差；2)計算寄存小時數(shù)：在單元格F4中輸入以下公式：“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)),HOUR(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4))),HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))))”，此公式中的IF函數(shù)中的條件與計算天數(shù)時的條件是一樣的，也是判斷取走時間是否超過了寄存時間，如果沒有超過小時數(shù)則為“TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4))”，其中“TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”表示寄存時間的序列數(shù)，其中“TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”表示取走時間的序列數(shù)。再通過加減計算得到小時數(shù)，如果超過了小時數(shù)則為“HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)))”，即直接用取走時間減去存在時間，取小時數(shù)；3)計算寄存分鐘數(shù)：在單元格G4中輸入以下公式：“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)),MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4))),MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))))”，此時即可計算出所有型號的包裹寄存的分鐘數(shù)，其公式形式和計算小時數(shù)的公式相似，只是將HOUR換成了MINUTE，其判斷條件和前面的一樣，如果取走時間沒有超過寄存時間，分鐘數(shù)則為“MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE(D4),SECOND(D4)))”。如果超過了，分鐘數(shù)則為“MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)))”，即直接用取走時間減去寄存時間，取分鐘數(shù)；4)計算寄存的累計小時數(shù)：在單元格H4中輸入以下公式：“=E4*24+F4+IF(G4=0,0,IF(G4<=30,0.5,1))”，在該公式中，“E4*24”表示將天數(shù)轉(zhuǎn)換為小時數(shù)，在將分鐘轉(zhuǎn)換為小時數(shù)時，使用IF函數(shù)來判斷分鐘數(shù)的范圍，若分鐘數(shù)小于等于30則返回0.5小時，否則返回1小時，然后將所有的小時數(shù)相加即可得到累計小時數(shù)；5）計算寄存總費用：在單元格J4中輸入以下公式：“=I4*H4”，此時即可計算出寄存包裹的費用。圖2530、AND函數(shù)：當(dāng)所有參數(shù)的邏輯值為真時，AND函數(shù)的返回值為TRUE；只要有一個參數(shù)的邏輯值為假，該函數(shù)的返回值則為FALSE。例如：假設(shè)有一組民意調(diào)查數(shù)據(jù)或者調(diào)查結(jié)果，如圖26所示，下面根據(jù)各個年齡段（18～34、35～49、50～64和65以上）對數(shù)據(jù)進(jìn)行分類，以判斷出各個年齡段的調(diào)查結(jié)果。1）統(tǒng)計年齡在18～34歲之間的人的調(diào)查結(jié)果，在單元格E7中輸入以下公式：“=IF(AND(C7>=18,C7<=34),D7,"")”，在該公式中使用AND函數(shù)判斷單元格C7中的值是否在18～34歲之間，然后根據(jù)返回的邏輯值再利用IF函數(shù)得到結(jié)果，即如果為真則返回單元格D7中的值，否則返回空值；2）統(tǒng)計年齡在35～49歲之間的人的調(diào)查結(jié)果，在單元格F7中輸入以下公式：“=IF(AND(C7>=35,C7<=49),D7,"")”；3）統(tǒng)計年齡在65歲以上的人的調(diào)查結(jié)果，在單元格H7中輸入以下公式：“=IF(AND(C7>=50,C7<=64),D7,"")”。..精品精品.精品圖2631、OR函數(shù)：判斷邏輯值并集的計算結(jié)果，在所有的參數(shù)中只要有一個邏輯值為TRUE，該函數(shù)的返回值即為TRUE。例如已知某企業(yè)的員工姓名和出生年份兩列值，如圖27所示，然后根據(jù)輸入的年份判斷員工中是否有這一年出生的人，并且統(tǒng)計出共有幾個。1）在單元格D3中輸入判斷值“1975”，即判斷是否有1975年出生的人，然后在單元格E3中輸入以下公式：“{=OR(D3=C3:C8}”，在該公式中，表示將D2單元格中的值與數(shù)據(jù)區(qū)域“C3:C8”中的每一個值作比較，判斷是否相等。如果任何一人比較結(jié)果為真，函數(shù)OR則返回TRUE，也就是D3單元格中的值位于這個列表中。由于是在一個數(shù)組中查找是否存在某個指定的值，所以公式要以數(shù)組的形式輸入，輸入公式后要按[Ctrl]+[Shift]+[Enter]組合鍵完成；3)計算1975年出生的人數(shù)，在單元格E3中輸入以下公式：“{=SUM(IF(D3=C3:C8,1,0)}”，在該公式中先使用IF函數(shù)將單元格D3中的值與數(shù)據(jù)區(qū)域“C3:C8”中的每一個值進(jìn)行比較，如果兩個值相等則返回1，否則返回0。然后利用SUM函數(shù)對所有的返回值求和，最后得到的數(shù)據(jù)就是“1975”出現(xiàn)的次數(shù)，即有幾個人是1975年出生的。該公式要以數(shù)組公式的形式輸入。..精品精品.精品圖2732、ADDRESS函數(shù)：該函數(shù)使用方法如圖28所示。圖28..精品精品.精品33、AREAS函數(shù)：該函數(shù)使用方法如圖29所示。圖2934、CHOOSE函數(shù)：例如評定學(xué)生成績，利用該函數(shù)可以評定銷售人員的業(yè)務(wù)能力，還可以返回成績的檔次以及是否及格等，其計算方法都是一樣的。下面以學(xué)生成績表為例看一下CHOOSE函數(shù)的應(yīng)用方法。1)首先在工作表中輸入如圖30所示的學(xué)生成績，然后在單元格F3中輸入以下公式：“=SUM(C3:E3)/3”，此時即可計算出學(xué)生的平均成績；2）利用CHOOSE函數(shù)計算成績名次，在G3單元格中輸入以下公式：“=CHOOSE(IF(F3>=90,1,IF(F3>=80,2,IF(F3>=70,3,IF(F3>=60,4,5)))),"優(yōu)秀","良好","一般","及格","不及格")”，在該公式中用到了多個IF函數(shù)，用以判斷平均成績屬于哪個區(qū)間，再使用CHOOSE函數(shù)返回不同情況下的結(jié)果，這里把成績分為了5個檔次，即平均分90以上的是“優(yōu)秀”、80到90之間的是“良好”、70到80之間的為“一般”、60到70之間的為“及格”、60以下的為“不及格”。..精品精品.精品圖3035、COLUMN函數(shù)：該函數(shù)使用方法如圖31所示。圖3136、COLUMNS函數(shù)：該函數(shù)使用方法如圖32所示。圖3237、HLOOKUP函數(shù)：在實際工作中此函數(shù)的應(yīng)用非常廣泛，下面舉例說明。在計算銷售獎金時，不同的銷售業(yè)績對應(yīng)不同的獎金比例，因此首先需要使用HLOOKUP函數(shù)查詢獎金比例，然后再計算銷售獎金。1）輸入如圖33所示的業(yè)績獎金以及員工的銷售業(yè)績；2）查找適當(dāng)?shù)莫劷鸨壤?，在單元格D7中輸入以下公式：“=HLOOKUP(D3,$B$3..精品精品.精品G$4,2)”；3）分別在單元格D8、D9、D10中輸入以下公式：“=HLOOKUP(E3,$B$3G$4,2)”、“=HLOOKUP(F3,$B$3G$4,2)”、“=HLOOKUP(G3,$B$3G$4,2)”；3）計算獎金：在單元格E7中輸入以下公式：“=C7*D7”。圖3338、HYPERLINK函數(shù)：該函數(shù)使用方法如圖34所示。圖3439、INDEX函數(shù)：該函數(shù)返回指定單元格中的內(nèi)容。假設(shè)在圖35所示的課程表中：1）查找出星期三第4節(jié)課所上的課程：只需在單元格C13中輸入以下公式：“=INDEX(C3:H9,C12,C11)”；2）返回星期五的所有課程：選中單元格區(qū)域“J2:J9”，然后輸入以下公式：“{=INDEX(B2:H9,,6)}”，此時即可顯示出星期五的所有課程；3）計算路程：已知各地之間相隔的距離如圖36所示，那么如何計算A地和D地之間相隔的距離呢？只需在單元格C11中輸入以下公式：“=INDEX(B2:G7,MATCH(C9,B2:B7,0),MATCH(C10,B2:G2,0))”。..精品精品.精品圖35圖36..精品精品.精品40、INDIRECT函數(shù)：該函數(shù)使用方法如圖37所示。圖3741、LOOKUP函數(shù)：該函數(shù)用于在行（或列）中查找并返回數(shù)值。例如某公司員工的工資表如圖38所示，查找姓名：首先在單元格C11中輸入編輯“0004”，然后在單元格C12中輸入以下公式：“=LOOKUP(C11,B3:B9,C3:C9)”，也可輸入公式：“=LOOKUP(C11,B3:C9)”，此時即可查找到編輯為“0004”的員工的姓名。查找基本工資、實發(fā)工資的公式類似姓名的公式。圖38..精品精品.精品42、MATCH函數(shù)：在數(shù)組中查找數(shù)值的相應(yīng)位置。該函數(shù)使用方法如圖39所示。圖3943、OFFSET函數(shù)：OFFSET函數(shù)的功能是返回的引用可以為一個單元格或者單元格區(qū)域，并且可以指定返回的行數(shù)或者列數(shù)。其語法為：OFFSET(reference,rows,cols,height,width)。其中reference表示作為偏移量參照系的引用區(qū)域，此參數(shù)必須為單元格或相鄰單元格區(qū)域的引用，否則函數(shù)OFFSET返回錯誤值“#VALUE！”；rows表示相對于偏移量參照系的左上角單元格上（下）偏移的行數(shù)；cols表示相對于偏移量參照系的左上角單元格左（右）偏移的列數(shù)；height表示高度，即所要返回的引用區(qū)域的行數(shù)，此參數(shù)必須為正數(shù)；width表示寬度，即所要返回的引用區(qū)域的列數(shù)，此參數(shù)必須為正數(shù)。該函數(shù)的應(yīng)用方法如圖40所示。..精品精品.精品圖4044、ROW函數(shù)：該函數(shù)的應(yīng)用方法如圖41所示。圖4145、ROWS函數(shù)：該函數(shù)的應(yīng)用方法如圖42所示。圖4246、VLOOKUP函數(shù)：VLOOKUP函數(shù)的功能是在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。其語法為：VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）。其中l(wèi)ookup_value為需要在數(shù)組第一列中查找的數(shù)值；col_index_num為table_array中待返回的匹配值的序列號；range_lookup為一個邏輯值，用以指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。該函數(shù)的應(yīng)用方法如圖43所示。..精品精品.精品圖4347、計算所得稅：假設(shè)規(guī)定：工資、薪金所得按月征收，對每月收入超過800元以上的部分征稅，適用5%至45%的9級超額累進(jìn)稅率，即：納稅所得額（計稅工資）=每月工資（薪金）所得—800元（不計稅部分）；超額累進(jìn)應(yīng)納稅款=納稅所得額×按全額累進(jìn)所用稅率—速算扣除數(shù)。當(dāng)工資為“5800”和“3000”元的時候，計算其應(yīng)繳納的所得稅的金額，具體操作步驟如下：1）如圖44所示，在單元格C15和C16中輸入工資金額“5800”和“3000”，然后在單元格D15中輸入“=IF($C15<=$F$2,0,($C15-$F$2)*VLOOKUP(($C15-$F$2),$D$4F$12,2,1)-VLOOKUP(($C15-$F$2),$D$4F$12,3,1))”，此時即可計算出繳納的所得稅；2）在單元格E15中輸入以下公式“=$C15-$D15”，此時即可計算出實發(fā)工資。..精品精品.精品圖4448、計算考核成績：在公司或者企業(yè)內(nèi)部為了激勵員工更加積極地工作經(jīng)常會制定一些考核制度，下面以計算某公司員工第一季度的考核成績?yōu)槔榻B一下部分查找函數(shù)的實際應(yīng)用方法，具體的操作步驟如下：1）新建一個工作薄，將其中的工作表Sheet1、Sheet2和Sheet3分別命名為“各季度缺勤記錄”、“部長意見”和“第一季度考核表”，然后在前兩個工作表中輸入所需要的數(shù)據(jù)信息，如圖45、圖46所示；2）在工作表“第一季度考核表”中輸入員工編號、員工姓名以及相關(guān)的標(biāo)題項目，如圖47所示；3）計算“缺勤記錄”：在單元格D3中輸入以下公式：“=INDEX(各季度缺勤記錄!D2G$9,2,1)”；4）計算“出勤成績”：在單元格E3中輸入以下公式：“=IF(D3<30,30-D3,0)”，即如果缺勤30天以上出勤成績就是0分；5）計算“工作能力”：在單元格F3中輸入以下公式：“=INDEX(部長意見!D3:E9,1,1)”；6）計算“工作態(tài)度”：在單元格G3中輸入以下公式：“=VLOOKUP(B3,部長意見!$B$3..精品精品.精品E$9,4)”；7）計算“季度考核成績”：在單元格H3中輸入以下公式：“=SUM(E3:G3)”，即出勤成績、工作能力及工作態(tài)度之和。圖45圖46..精品精品.精品圖4749、ASC函數(shù)：此函數(shù)用來將全角轉(zhuǎn)換為半角。該函數(shù)的用法見圖48所示。圖4850、CONCATENATE函數(shù)：此函數(shù)用來合并字符串。該函數(shù)的用法見圖49所示。圖4951、DOLLAR函數(shù)：此函數(shù)用來將數(shù)字轉(zhuǎn)換為貨幣形式。該函數(shù)的用法見圖50所示。..精品精品.精品圖5052、RMB函數(shù)：此函數(shù)用來將數(shù)字轉(zhuǎn)換為貨幣形式。該函數(shù)的用法見圖51所示。圖5153、EXACT函數(shù)：此函數(shù)用來判斷字符串是否相同。該函數(shù)的用法見圖52所示。圖5254、FIND函數(shù)：此函數(shù)用來查找文本串。該函數(shù)的用法見圖53所示。..精品精品.精品圖5355、FIXED函數(shù)：此函數(shù)對數(shù)字進(jìn)行格式化。該函數(shù)的用法見圖54所示。圖5456、LEFT函數(shù)：返回第一個或前幾個字符。例如：在實際工作中，要取得電話號碼的區(qū)號或者取得人名的姓氏等都可以利用LEFT函數(shù)來完成。1）獲取區(qū)號：假設(shè)已知一些電話號碼，如圖55所示，下面利用LEFT函數(shù)獲取這些電話號碼的區(qū)域。在單元格C3中輸入以下公式：“=LEFT(B3,4)”；2）輸入稱呼：首先在工作表中輸入已知的姓名和性別，如圖56所示，然后在單元格E3中輸入以下公式：“=LEFT(C3,1)&IF(D3="男","先生","女士")”，該公式表示在姓名中取出左邊的第一個字，用&連接上"先生"或者"女士"稱呼。..精品精品.精品

圖55

圖5657、LEN函數(shù)：此函數(shù)用來查找文本的長度。該函數(shù)的用法見圖57所示。圖5758、LOW函數(shù)：此函數(shù)用來將文本轉(zhuǎn)換為小寫。該函數(shù)的用法見圖58所示。圖58..精品精品.精品59、MID函數(shù)：此函數(shù)可以返回文本字符串中從指定位置開始的特定字符。該數(shù)目由用戶指定。例如：1）如圖59所示：從身份證號碼中提取生日：在網(wǎng)上注冊一些表格時經(jīng)常需要填寫身份證號碼，填寫完畢系統(tǒng)就會自動地生成出生日期，這里以某公司員工為例，根據(jù)其身份證號碼提取出生年月日。首先在工作表中輸入員工的姓名和身份證號碼等數(shù)據(jù)信息，如圖59所示，然后在單元格D3中輸入以下公式：“=MID(C3,7,8)”，在該公式中，利用MID函數(shù)返回身份證號碼中從第7位字符開始的共8個字符，即該員工的出生日期，眾所周知，身份證前6位代表的是省份、市、縣編號，然后從第7位開始是出生年月日，共8位，后面的數(shù)字代表其他的意義；2）拆分電話號碼：工作表中輸入已知的電話號碼，如圖60所示，然后在單元格C3中輸入以下公式：“=MID(B3,5,7)”，此時即可獲得電話。

圖59

圖6060、PROPER函數(shù)：此函數(shù)可以自動轉(zhuǎn)換大小寫。首先在工作表中輸入一些字母或者英文句子，如圖61所示，然后在單元格C3中輸入以下公式：“=PROPER(B3)”。..精品精品.精品圖6161、REPLACE函數(shù)：此函數(shù)可以使用其他的文本字符串并根據(jù)所指定的字符數(shù)替換某個文本字符串中的部分。例如某市的電話號碼要升位，在原來的電話號碼的前面加一個“8”，下面使用REPLACE函數(shù)完成已知電話號碼的升位。具體的操作步驟如下：1）輸入已知的電話號碼，如圖62所示；2）計算升位后的電話號碼，在單元格C3中輸入以下公式：“=REPLACE(B3,1,4,"05328")”，在該公式中，使用REPLACE函數(shù)用“0108”替換B3中字符串中第一位開始的前4位數(shù)字，結(jié)果相當(dāng)于區(qū)號不變，在原電話號碼的前面加一個“8”。其中“05328”加引號是以文本的形式輸入的，否則忽略0。圖6262、REPT函數(shù)：此函數(shù)可以按照給寫的次數(shù)重復(fù)顯示文本，也可以通過REPT函數(shù)不斷地重復(fù)顯示某一個文本字符串來對單元格進(jìn)行填充。該函數(shù)的用法見圖63所示。..精品精品.精品圖6363、RIGHT函數(shù)：使用此函數(shù)可以根據(jù)所指定的字符數(shù)返回文本字符串中最后一個或者多個字符。例如：1）拆分姓名，在實際中人的姓名一般是由姓和名兩部分組成的，下面介紹如何利用RIGHT函數(shù)將其拆分開，具體的操作步驟如下：在單元格中輸入一些姓名，如圖64所示，然后在單元格C3中輸入以下公式：“=RIGHT(B3,2)”；2）判斷性別：假設(shè)有一個關(guān)于生活消費方面的調(diào)查，調(diào)查者為了書寫方便也為了便于進(jìn)行統(tǒng)計分析，在對被調(diào)查者編號時指定其最后一位表示性別，用“1”代表男性，用“2”代表女性，首先在工作表中輸入已知信息，如圖65所示，然后在單元格D3中輸入以下公式：“=IF(RIGHT(C3,1)="1","男","女")”，在該公式中，使用RIGHT函數(shù)返回編號中的最后一個字符，再利用IF函數(shù)判斷。如果返回的結(jié)果為“1”則為“男”，反之為“女”，由于函數(shù)返回的是字符，所以“1”要加引號，當(dāng)有多種情況時還可以使用嵌套的IF函數(shù)。..精品精品.精品

圖64

圖6564、SEARCH函數(shù)：此函數(shù)可以查找文本字符串。該函數(shù)的用法見圖66所示。圖6665、T函數(shù)：此函數(shù)可以返加引用的文本。該函數(shù)的用法見圖67所示。圖6766、TEXT函數(shù)：此函數(shù)用來將數(shù)值轉(zhuǎn)換為指定格式。該函數(shù)的用法見圖68所示。..精品精品.精品圖6867、TRIM函數(shù)：此函數(shù)用來清除文本中的空格。該函數(shù)的用法見圖69所示。圖6968、UPPER函數(shù)：此函數(shù)用來將文本轉(zhuǎn)換為大寫。該函數(shù)的用法見圖70所示。圖70..精品精品.精品69、處理人員信息：文本函數(shù)在實際工作中也是一種常用的函數(shù)類型。一些大型的企業(yè)為了提高員工的素質(zhì)，使員工能及時地接觸到該行業(yè)的最新科技信息，有關(guān)負(fù)責(zé)人會時常請一些專家對自己的員工進(jìn)行培訓(xùn)。下面介紹如何利用文本函數(shù)處理人員信息，具體的操作步驟如下：1）在工作表中輸入需要的標(biāo)題項目以及人員編號、姓名和性別等數(shù)據(jù)信息，以便于在后面使用，如圖71所示；2）從姓名中提取姓：在單元格E3中輸入以下公式：“=IF(LEN(C3)=4,LEFT(C3,2),LEFT(C3,1))”，由于中國人的姓名有兩個字的，有3個字的，還有4個字符，4個字的名字一般是復(fù)姓，所以要使用IF函數(shù)判斷姓名的長度是不是4，如果姓名的長度等于4，則使用LEFT函數(shù)返回左邊的兩個字符，否則返回左邊的1個字符；3）從姓名中提取名：在單元格E3中輸入以下公式：“=IF(LEN(C3)=2,RIGHT(C3,1),RIGHT(C3,2))”，在該公式中使用IF函數(shù)判斷姓名的長度是不是等于2，若等于2則利用RIGHT函數(shù)返回最右側(cè)的1個字符，若不等于2則返回最右側(cè)的兩個字符；4）添加稱呼：在單元格G3中輸入以下公式：“=IF(D3="男",CONCATENATE(E3,"先生"),CONCATENATE(E3,"女士"))”，在該公式中，首先使用IF函數(shù)判斷性別是“男”還是“女”，如果是“男”則返回先生，如果是“女”則返回女士，然后利用CONCATENATE函數(shù)將判斷結(jié)果和姓連接起來組成該專家的稱呼；5）安排入住的賓館房間號：在單元格H3中輸入以下公式：“=IF(B3<=3,"濱海假日"&TEXT(B3,"300"),"清泉賓館"&TEXT(B3,"200"))”，在安排專家的賓館房間時，假設(shè)前三名專家在賓館A中休息，其余的在賓館B中休息，房間號為他們的編號，在該公式中先使用TEXT函數(shù)將B列中的數(shù)據(jù)轉(zhuǎn)換為對應(yīng)格式的文本，再使用符號“&”將賓館和房間號連接起來，最后使用IF函數(shù)根據(jù)專家的編號判斷其入住哪個賓館；6）輸入各個專家的培訓(xùn)人數(shù)，然后選中單元格K2，選擇[插入]—[符號]菜單位項彈出[符號]對話框，切換到[符號]選項卡中，在[字體]下拉列表中選擇[（普通文本）]選項，在[子集]下拉列表中選擇[零雜丁貝符（示意符號）]選項，設(shè)置完畢單擊[插入]按鈕即可在單元格輸入選定的符號；7）繪制人數(shù)比較圖：在單元格G3中輸入以下公式：“=REPT($K$2,INT(I3/12))”，在該公式中，使用REPT函數(shù)將單元格K2中的方塊元素復(fù)制“INT(I3/12)”次，為了縮小空間也為了減小培訓(xùn)人數(shù)比例，將I列中的培訓(xùn)人數(shù)除以12再取整數(shù)即可得到需要復(fù)制的次數(shù)。..精品精品.精品圖7170、拆分工資金額：在前面已經(jīng)介紹過利用INT函數(shù)和MOD函數(shù)進(jìn)行工資數(shù)額的拆分，下面介紹如何使用文本函數(shù)將工資數(shù)額按其位數(shù)分隔開。例如已知某公司部分員工的工資，現(xiàn)要將工資按位數(shù)分開，具體的操作步驟如下：1）在工作表中輸入姓名和工資數(shù)額以及其他的標(biāo)題項目，如圖72所示；2）計算千位上的數(shù)字：在單元格D4中輸入以下公式：“=IF(LEN(C4)=4,LEFT(C4,1),0)”,在該公式中使用LEN函數(shù)得到C4中字符串的長度，再使用IF函數(shù)判斷該字符串的長度是否等于4，如果是的話則利用LEFT函數(shù)返回第一個字符，否則返回0；3）計算百位上的數(shù)字:在單元格E4中輸入以下公式：“=IF(D4=0,IF(LEN(C4)=3,LEFT(C4,1),0),LEFT(C4-D4*1000,1))”,在該公式中，首先使用IF函數(shù)判斷單元格D4中的值是否等于0，如果等于0則表明單元格C4中的數(shù)字共3位，將使用LEFT函數(shù)返回第一個字符；如果不等于0則返回“C4-D4*1000”所得結(jié)果的第一個字符；4）計算十位上的數(shù)字：在單元格F4中輸入以下公式：“=LEFT(C4-D4*1000-E4*100,1)”計算結(jié)果的第一個字符。由于工資最少是“988”，即3位數(shù)字，所以不必再判斷是否有兩位數(shù)的情況；5）計算個位上的數(shù)字：在單元格G4中輸入以下公式：“=LEFT(C4-D4*1000-E4*100-F4*10,1)”計算結(jié)果的第一個字符。..精品精品.精品圖7271、CELL函數(shù)：使用此函數(shù)可以返回某一個引用區(qū)域的左上角單元格的格式、位置或者內(nèi)容等信息。該函數(shù)的用法見圖73所示。圖73..精品精品.精品72、COUNTBLANK函數(shù)：此函數(shù)可以指定空白單元格的個數(shù)。該函數(shù)的用法見圖74所示。圖7473、ISBLANK函數(shù)：此函數(shù)可以判斷單元格是否為空。例如判斷員工是否到崗：1）輸入姓名和上班時間，如圖75所示；2）判斷其是否到崗，在單元格E3中輸入以下公式：“=IF(ISBLANK(D3),"請假","到崗")”。圖7574、ISERR函數(shù)：此函數(shù)可以判斷數(shù)值是否為任意錯誤值。例如：計算應(yīng)收賬款：1）輸入已知的數(shù)據(jù)信息，如貨物名稱、數(shù)量、單價和金額等，如圖76所示；2）在單元格E3中輸入以下公式：“=IF(ISERR(C3*D3),"確定價格后再做處理",C3*D3)”。..精品精品.精品圖7675、DAVERAGE函數(shù)：此函數(shù)可以返回列表或者數(shù)據(jù)庫中滿足指定條件的列中數(shù)值的平均值。例如：1）在單元格中輸入需要處理的問題，如計算“語文大于59分的平均成績”和“英語的平均成績”，如圖77所示；2）在單元格C12中輸入以下公式：“=DAVERAGE(B2:E8,C10,C10:C11)”；3）在單元格C13中輸入以下公式：“=DAVERAGE(B2:E8,4,E2:E8)”。圖7776、DCOUNT函數(shù)：使用此函數(shù)可以返回數(shù)據(jù)庫或者列表中滿足指定條件并且包含數(shù)字的單元格個數(shù)。具體的操作步驟如下：1）如圖78所示，首先在單元格中輸入需要處理的問題，然后在單元格C12中輸入以下公式：“=DCOUNT(B2:E8,B10,B10:B11)”，即可得到數(shù)學(xué)成績及格的單元格個數(shù)；2）在單元格C13中輸入以下公式：“=DCOUNT(B2:E8,2,B10:B11)”，即可得到語文成績大于70并且數(shù)學(xué)成績及格的單元格個數(shù)。..精品精品.精品圖7877、DGET函數(shù)：使用此函數(shù)可以從列表或者數(shù)據(jù)庫的列中提取符合指定條件的單個值。如圖79所示，在單元格C12中輸入以下公式：“=DGET(B2:E8,1,D1011)”，即可查找出英語成績大于89分的同學(xué)的姓名；在單元格C13中輸入以下公式：“=DGET(B2:E8,1,B10:C11)”，即可查找出語文和數(shù)學(xué)成績?nèi)看笥?0分的同學(xué)的姓名。..精品精品.精品圖7978、DMAX函數(shù)：此函數(shù)用以返回指定條件的最大數(shù)值。首先在單元格中輸入需要處理的問題，如圖80所示，然后分別在單元格C12和C13中輸入以下公式：“=DMAX(B2:E8,B10,B1011)”、“=DMAX(B2:E8,D10,B1011)”。DMIN函數(shù)的使用方法與DMAX函數(shù)相似，不過此函數(shù)用以返回指定條件的最小數(shù)值。..精品精品.精品圖8079、DSUM函數(shù)：此函數(shù)用以返回指定條件的數(shù)字之和。首先在單元格中輸入需要處理的問題，如圖81所示，然后在單元格C12和C13中輸入以下公式：“=DSUM(B1:F6,1,C8:C9)”、“=DSUM(B1:F6,5,C89)”。圖8180、處理采購數(shù)據(jù)：在EXCEL中提供有很多種數(shù)據(jù)庫函數(shù)，可以滿足采購管理中管理人員對大量數(shù)據(jù)處理的要求。下面以圖82所示，介紹如何使用數(shù)據(jù)庫函數(shù)處理采購數(shù)據(jù)：1）使用DAVERAGE函數(shù)計算采購數(shù)量的平均值，首先建立一個數(shù)據(jù)模型；2）在單元格E15中輸入以下公式：“=DAVERAGE(B2:F12,5,G15:G16)”，即可得到臺燈的平均采購數(shù)量；3）將單元格G16中的“臺燈”改為“瓷瓶”就可以計算出瓷瓶的平均采購數(shù)量。此外還可以利用DAVERAGE函數(shù)在相交或者相并兩種條件下計算數(shù)據(jù)的平均值。在Excel中輸入同行的條件為相交的條件，即必須全部滿足的條件，然后在單元格E19中輸入以下公式：“=DAVERAGE(B2:F12,5,C2122)”，即可計算出采購數(shù)量少于20的平均數(shù)；4）要計算購買臺燈的數(shù)量大于10的次數(shù)，此時可以使用DCOUNT函數(shù)來求解，首先輸入需要求解的條件，然后在單元格F26中輸入以下公式：“=DCOUNT(B2:F12,5,C2829)”；5）要計算新新家具公司沙發(fā)的價格，此時可以使用DGET函數(shù)來求解，首先輸入需要求解的條件，然后在單元格E33中輸入以下公式：“=DGET(B2:E12,4,C3536)”；6）要計算家具中沙發(fā)的最高價格，此時可以使用DMAX函數(shù)來求解，首先輸入需要求解的條件，然后在單元格E39中輸入以下公式：“=DMAX(B2:E12,4,C41:C42)”。..精品精品.精品圖8281、區(qū)分函數(shù)COUNT和COUNTA：例如：1）制作1月出勤加班統(tǒng)計表，表中包括員工1月出勤加班統(tǒng)計表以及需要統(tǒng)計的內(nèi)容，如圖83所示；2）使用COUNT函數(shù)統(tǒng)計各列單元格的個數(shù)，在單元格B13中輸入以下公式：“=COUNT(B3:B11)”，此時可以看到包含文字的單元格和空白單元格被忽略了，只統(tǒng)計包含數(shù)字的單元格；3）使用COUNTA函數(shù)統(tǒng)計各列單元格的個數(shù)，在單元格B14中輸入以下公式：“=COUNTA(B3:B11)”，此時可以看到包含文字的單元格也統(tǒng)計在內(nèi)了。..精品精品.精品圖8382、LARGE函數(shù)：該函數(shù)用來返回數(shù)據(jù)集中第K個最大值。例如：根據(jù)某企業(yè)在一次訂貨后檢驗所訂產(chǎn)品的合格情況，計算需求量的大小、次品的多少以及不合格率最高、最低的產(chǎn)品等。具體的操作步驟如下：1）制作檢驗訂購的產(chǎn)品合格情況表，如圖84所示；2）計算各個產(chǎn)品中訂購總量的最大需求與最小需求，分別在單元格G2和G3中輸入以下公式：“=LARGE(B3:B12,1)”、“=SMALL(B3:B12,1)”；3）計算次品的最大值和最小值，分別在單元格G6和G6中輸入以下公式：“=LARGE(C3:C12,1)”、“=LARGE(C3:C12,10)”；4）查找出不合格率最高的產(chǎn)品，在單元格G10中輸入以下公式：“=INDEX(A3:A12,MATCH(LARGE(D3..精品精品.精品12,1),D312,0),1)”,在該公式中首先利用LARGE函數(shù)得出不合格率的最大值，然后利用MATCH函數(shù)得到該最大值在數(shù)據(jù)區(qū)域“D3：D12”中的行號，最后使用INDEX函數(shù)在數(shù)據(jù)區(qū)域“A3：A12”中查找該行與第1列交叉處的單元格的值，即可得到不合格率最高的產(chǎn)品即“顯示器”；查找不合格率最低的產(chǎn)品同理。圖8483、區(qū)分函數(shù)MAX和MAXA：現(xiàn)以某公司1月份的出勤和加班記錄表為例，來看一下MAX和MAXA函數(shù)在實際工作中的具體的應(yīng)用。在此表中分別計算遲到、曠工和加班次數(shù)最多的員工的姓名，具體的操作步驟如下：1）制作如圖85所示的表格；2）計算遲到次數(shù)最多的員工的姓名，在單元格J5中輸入以下公式：“=INDEX(C3:C11,MATCH(MAX(E3:E11),E3:E11,0),1)”,即可得到遲到次數(shù)最多的員工的姓名即“李寧”；3）計算曠工次數(shù)最多的員工的姓名，在單元格J7中輸入以下公式：“=INDEX(C3:C11,MATCH(MAXA(F3:F11),F3:F11,0),1)”，即可得到曠工次數(shù)最多的員工的姓名即“邵剛”。..精品精品.精品圖8584、MODE函數(shù)：該函數(shù)用以返回出現(xiàn)頻率最高的數(shù)值。例如：假設(shè)已知某些同學(xué)的語文、數(shù)學(xué)和英語成績?nèi)鐖D86所示，現(xiàn)計算各科成績中出現(xiàn)次數(shù)最多的分?jǐn)?shù)，在單元格C14中輸入以下公式：“=MODE(C4:C13)”。圖8685、PERCENTRANK函數(shù)：該函數(shù)用以返回百分比排位。該函數(shù)的用法見圖87所示。..精品精品.精品圖8786、RANK函數(shù)：該函數(shù)用以排名次。該函數(shù)的用法見圖89所示。87、TRIMMEAN函數(shù)：該函數(shù)用以計算內(nèi)部平均值。例如：根據(jù)實際計算需求在工作表中添加相應(yīng)的項目，制作如圖88所示的表格，在單元格C15中輸入以下公式：“=TRIMMEAN(C3:C14,0.05)”，即可得到除去極值比例為5%的均值結(jié)果。..精品精品.精品圖8888、統(tǒng)計員工培訓(xùn)成績：一般來說公司招聘新員工后會對其進(jìn)行培訓(xùn)，隨后人事部會對他們的培訓(xùn)成績進(jìn)行統(tǒng)計排名，以此考核新員工的素質(zhì)狀況，具體的操作步驟如下：1）首先在工作表Sheet1中輸入相應(yīng)的數(shù)據(jù)信息，然后對輸入的內(nèi)容進(jìn)行字體、邊框等屬性的設(shè)置，如圖89所示；2）計算各位員工的平均成績：在單元格J4中輸入以下公式：“=AVERAGE(E4:I4)”；3）計算總成績：在單元格K4中輸入以下公式：“=SUM(E4:I4)”；4）計算員工的成績名次：在單元格L4中輸入以下公式：“=RANK(J4,$J$4J$21)”；5）計算培訓(xùn)人數(shù)：在單元格D23中輸入以下公式：“=COUNT(B4:B21)”；6）計算實際參加考試的人數(shù)：在單元格D24中輸入以下公式：“=COUNTA(E4:E21)”；7）計算無成績的人數(shù)：即缺考人數(shù)，在單元格D25中輸入以下公式：“=COUNTBLANK(E4:E21)”；8）計算參加培訓(xùn)的男、女員工人數(shù)：分別在單元格D26和D27中輸入以下公式：“=COUNTIF(D421,"女")”、“=COUNTIF(D421,"男")”；9）計算男、女員工總成績：分別在單元格D29和D30中輸入以下公式：“=SUMIF(D4..精品精品.精品21,"男",K4:K21)”、“=SUMIF(D421,"女",K4:K21)”；10）計算平均最高和最低分：分別在單元格D31和D32中輸入以下公式：“=MAXA(J4:J21)”、“=MIN(J4:J21)”；11）計算倒數(shù)第2名：在單元格D33輸入以下公式：“=SMALL(J4:J21,2)”；12）計算各個分?jǐn)?shù)段的人數(shù)：選中單元格區(qū)域“G29：G33”，然后輸入以下公式：“{=FREQUENCY(J4:J21,F24:G27)}”，在該公式中使用FREQUENCY函數(shù)得到各個分?jǐn)?shù)段的人數(shù)；13）計算各個分?jǐn)?shù)段的人數(shù)占總?cè)藬?shù)的比例：在單元格H29輸入以下公式：“=G29/$D$24”。圖89..精品精品.精品89、CUMPRINC函數(shù)：此函數(shù)用來計算貸款本金和利息。例如一位消費者獲得一項30年的住房貸款，金額為400000元，按月還款，年息是5%，請計算貸款的第五年應(yīng)該支付的本金和利息。具體的操作步驟如下：1）制作如圖90所示的表格；2）計算按月還款時第五年內(nèi)每月月末還款的本金的累計和，在單元格B6中輸入以下公式“=CUMPRINC(D3/12,C3*12,B3,49,60,0)”，在該公式中“D3/12”表示月利率，“C3*12”表示總的付款期數(shù)，“49”表示第五年的第一個月，“60”表示第五年的最后一個月，“0”表示付款時間類型是月末；2）計算第五年內(nèi)每月月末所付利累的累計和，在單元格C6中輸入以下公式：“=CUMIPMT(D3/12,C3*12,B3,49,60,0)”，注意這個公式與上面唯一不同的地方是所用的函數(shù)是不同的；3）計算本利之和，在單元格D6中輸入以下公式：“=SUM(B6:C6)”；4）計算按月付款時第五年內(nèi)共付多少，在單元格E6中輸入以下公式：“=PMT(D3/12,C3*12,B3,0,0)*12”，在該公式中，“PMT(D3/12,C3*12,B3,0,0)”表示使用PMT函數(shù)計算每月月末還款數(shù)，其中“D3/12”表示月利率，“C3*12”表示總的付款期數(shù)，所得結(jié)果再乘以12表示第五年內(nèi)總的還款數(shù)。圖90..精品精品.精品90、DOLLARDE函數(shù)：此函數(shù)用來將分?jǐn)?shù)形式轉(zhuǎn)換為小數(shù)形式。該函數(shù)的用法如圖91所示。圖9191、DOLLARFR函數(shù)：此函數(shù)用來將小數(shù)形式轉(zhuǎn)換為分?jǐn)?shù)形式。該函數(shù)的用法如圖92所示。圖9292、FV函數(shù)：此函數(shù)用來計算投資未來值。1）計算本利和：例如某公司為某項投資存款，銀行已有的存款是50000元，以后每年存款20000元，年利率是8%，試計算10年后的本息和為多少？如果每月存入2000元，那么10年后的本利和又是多少？具體的操作步驟如下：制作如圖93所示的表格，計算按存款時10年后的本金和利息之和，在單元格D5中輸入以下公式：“=FV(C3,D3,E3,B3,0)”；計算按月存款時10年后的本金和利息之和，在單元格D6中輸入以下公式：“=FV(C3/12,D3*12,F3,B3,0)”，在該公式中“C3/12”表示月利率，“D3*12”表示總的月份數(shù)，每月的存款和先期的存款都是負(fù)值，這樣計算出來的結(jié)果就是正值，“0”表示每次月末存款；2）計算累計金額：例如如果年息為6%，那么5年之后2000元的累計金額是多少？具體的操作步驟如下：將已知數(shù)據(jù)和相關(guān)項目輸入到工作表中建立數(shù)據(jù)模型，如圖94所示，在單元格B6中輸入以下公式：“=FV(B3,B4,0,B5,0)”；3）計算到期還款額：例如用戶向銀行借款5000元，期限6年，利息6%，請問到期還款額為多少？具體的操作步驟如下：將已知數(shù)據(jù)和相關(guān)項目輸入到工作表中建立數(shù)據(jù)模型，如圖95所示，在單元格B6中輸入以下公式：“=FV(B3,B4,0,B5,0)”；4）計算帳戶總額：如何期初余額為5000元，每月的月末存入600元，如果月息為0.75%，請問三年后此帳戶中的總額是多少錢？具體的操作步驟如下：將已知數(shù)據(jù)和相關(guān)項目輸入到工作表中建立數(shù)據(jù)模型，如圖96所示，在單元格B7中輸入以下公式：“=FV(B3,B4,B5,B6,0)”，即可求出三年后的賬戶總額。..精品精品.精品

圖93

圖94[/td]..精品精品.精品

圖95

圖9693、FVSCHEDULE函數(shù)：此函數(shù)用來計算本金未來值。例如某人存入銀行150000元，一年內(nèi)年利率在不斷地變化，請計算一年后的存款額。具體的操作步驟如下：1）制作如圖97所示的表格，其中包括一年內(nèi)不同的年利率以及由此得出的月利率；2）計算在各種利率條件下一年后的總存款數(shù)，在單元格D15中輸入以下公式：“=FVSCHEDULE(C2,F3:F14)”，即計算不同的利率條件下150000元的存款1年后的存款額是多少。..精品精品.精品圖9794、IPMT函數(shù)：此函數(shù)用來返回利息償還額。例如計算貸款利息，假設(shè)有一位消費者為買房而向銀行貸款200000元，貸款期限為10年，年息為4.5%，按月償還，請計算付款中的利息，具體的操作步驟如下：1）制作如圖98所示的表格，其中包括貸款金額、貸款時間和年利息；2）計算第一個月應(yīng)付的利息：在單元格D5中輸入以下公式：“=IPMT(D3/12,1,C3*12,B3)”；3）計算最后一個月應(yīng)付的利息：在單元格D6中輸入以下公式：“=IPMT(D3/12,120,C3*12,B3)”。圖9895、NPER函數(shù)：該函數(shù)用來返回投資總期數(shù)。該函數(shù)的用法如圖99所示。..精品精品.精品圖9996、PMT函數(shù)：該函數(shù)用來返回每期付款額。1）計算償還額：某公司從銀行貸款200000元，分6年償還，年利率為8%，現(xiàn)需計算按年償還和按月償還的還款額，條件為等額償還，具體的操作步驟如下：制作如圖100所示的表格，計算按年還款時的年初償還額，在單元格E4中輸入以下公式：“=PMT(C7,C5,C3,0,1)”；計算年末償還額，在單元格F4中輸入以下公式：“=PMT(C7,C5,C3)”；計算按月償還時的月初償還額，在單元格E8中輸入以下公式：“=PMT(C7/12,C5*12,C3,0,1)”，在該公式中“C7/12”表示月利率，“C5*12”表示還款總時間，因為是按月計算，所以是6*12=72個月，“1”仍表示是月初還款；計算按月還款時的月末償還額，在單元格F8中輸入以下公式：“=PMT(C7/12,C5*12,C3)”；2）計算存款金額：例如如果某公司需要為某個項目準(zhǔn)備資金，該項目在兩年后預(yù)計需要100000元，假設(shè)銀行的存款年利率為10%，那么從現(xiàn)在起公司每月或者每年應(yīng)當(dāng)存入的金額是多少？具體的操作步驟如下：制作如圖101所示的表格，計算按年存款時的年初存款額，在單元格E4中輸入以下公式：“=PMT(C7,C5,0,C3,1)”；計算按年存款時的年末存款額，在單元格F4中輸入以下公式：“=PMT(C7,C5,0,C3)”；計算按月存款時的月初存款額，在單元格E8中輸入以下公式：“=PMT(C7/12,C5*12,0,C3,1)”，在該公式中使用PMT函數(shù)計算按月存款時的月初存款額，公式中各項參數(shù)的意義依次為：“C7/12”為月利率，“C5*12”為存款總期數(shù)，“0”為本金，“100000”為未來值，“1”表示存款方式是期初；計算按月末存款額，在單元格F8中輸入以下公式：“=PMT(C7/12,C5*12,0,C3)”；3）計算利潤租金：假設(shè)一房產(chǎn)的租賃利益當(dāng)前可以以230000元出售，租期4年，預(yù)付每月的租金6000元，不得重新定價或者漲價，如果接受了0.75%的收益，請問可以得到多少利潤租金？具體的操作步驟如下：制作如圖102所示的表格，計算利潤租金，在單元格B7中輸入以下公式：“=PMT(B6,B4*12,-230000,0,1)+B5”；4）計算貸款償還額：假設(shè)有一筆期限為15年，月利息為0.65%的30000元的貸款，請計算月償還額為多少？具體的操作步驟如下：制作如圖103所示的表格，計算月償還額，在單元格B6中輸入以下公式：“=PMT(B4,B5*12,-B3,0,0)”。..精品精品.精品圖100圖101..精品精品.精品

圖102

圖10397、PPMT函數(shù)：該函數(shù)用來返回本金償還額。例如計算償還的本息，某公司從銀行貸款500000元，年利率為5%，還款期為10年，條件是等額償還，按年或者按月還款，請計算付款中的本金和利息。具體的操作步驟如下：1）制作如圖104所示的表格；2）計算按年還款時的年初和年末應(yīng)付本金：分別在單元格D6、D7、D8和D9中輸入以下公式：“=PPMT(E3,1,D3,C3,0,1)”、“=PPMT(E3,1,D3,C3,0)”、“=PPMT(E3,10,D3,C3,0,1)”、“=PPMT(E3,10,D3,C3,0,0)”，這4個公式分別是用來計算第一年的期初、期末和第十年的期初及期末的應(yīng)付本金的。公式的形式基本相同，只是函數(shù)PPMT中的參數(shù)per和type發(fā)生了改變，分別表示哪一年以及是期初還是期末的應(yīng)付本金；3）計算按月還款時的年初和年末應(yīng)付本金：分別在單元格D10、D11、D12和D13中輸入以下公式：“=PPMT(E3/12,1,D3*12,C3,0,1)”、“=PPMT(E3/12,1,D3*12,C3,0)”、“=PPMT(E3/12,120,D3*12,C3,0,1)”、“=PPMT(E3/12,120,D3*12,C3,0)”，在這4個公式中“E3/12”表示月利率，“D3*12”表示還款總月份數(shù)，“120”表示還款總期數(shù)中的最后一個月，由于是還款，因此未來值都是0；4）計算按年還款時的年初和年末應(yīng)付利息：分別在單元格E6、E7、E8和E9中輸入以下公式：“=IPMT(E3,1,D3,C3,0,1)”、“=IPMT(E3,1,D3,C3,0)”、“=IPMT(E3,10,D3,C3,0,1)”、“=IPMT(E3,10,D3,C3,0)”，與計算本金不同的地方就是所用的函數(shù)不一樣，其參數(shù)的意義都是一樣的；5）計算按月還款時的年初和年末應(yīng)付利息：分別在單元格E10、E11、E12和E13中輸入以下公式：“=IPMT(E3/12,1,D3*12,C3,0,1)”、“=IPMT(E3/12,1,D3*12,C3,0)”、“=IPMT(E3/12,120,D3*12,C3,0,1)”、“=IPMT(E3/12,120,D3*12,C3,0)”；6）計算各種情況下的本金和利息之和：在單元格F6中輸入以下公式：“=SUM(D6:E6)”。從上面的計算結(jié)果中可以看出：每年的年初所付款都是一樣的，雖然第一年和最后一年的本金和利息的值不同，但總和是相等的；同樣每年年末的本金和利息之和也是相等的，每月月初以及每月月末的本金和利息之和也是相等的。..精品精品.精品圖10498、PV函數(shù)：該函數(shù)用來返回投資現(xiàn)值。例如：1）計算貸款額：某公司想貸款進(jìn)行投資，其能承受的能力為每月支付10000元，以年利息5%進(jìn)行10年貸款，請計算該公司能承受的最多貸款額是多少？具體的操作步驟如下：制作如圖105所示的表格，在單元格E3中輸入以下公式：“=PV(C3/12,D3*12,-B3,0,0)”，即按月計算付款數(shù)；2）計算投資值：現(xiàn)有一項保險年金，購買該保險后可以在今后25年內(nèi)每月末領(lǐng)回1000元，假設(shè)購買時需先繳付15000元，投資回報率為4.5%，請計算這筆投資是否值得。具體的操作步驟如下：制作如圖106所示的表格，在單元格E3中輸入以下公式：“=PV(C3/12,D3*12,-B3,0,0)”，即可得到需要投資的現(xiàn)值。從上面的計算的結(jié)果中可以看出，計算出來的投資現(xiàn)值“179910.32”大于預(yù)交付的購買保險的費用“150000”，所以購買此保險還是值得的。..精品精品.精品圖105圖10699、RATE函數(shù)：該函數(shù)返回年金的各期利率。該函數(shù)的用法如圖107所示。..精品精品.精品圖107Excel常用電子表格公式大全2011-04-2408:53:41..精品精品.精品1、查找重復(fù)內(nèi)容公式：=IF(COUNTIF(A:A,A2)>1,"重復(fù)","")。

2、用出生年月來計算年齡公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。

3、從輸入的18位身份證號的出生年月計算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。

4、從輸入的身份證號碼內(nèi)讓系統(tǒng)自動提取性別，可以輸入以下公式：

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式內(nèi)的“C2”代表的是輸入身份證號碼的單元格。

1、求和：=SUM(K2:K56)——對K2到K56這一區(qū)域進(jìn)行求和；

2、平均數(shù)：=AVERAGE(K2:K56)——對K2K56這一區(qū)域求平均數(shù)；

3、排名：=RANK(K2，K$2:K$56)——對55名學(xué)生的成績進(jìn)行排名；

4、等級：=IF(K2>=85,"優(yōu)",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))

5、學(xué)期總評：=K2*0.3+M2*0.3+N2*0.4——假設(shè)K列、M列和N列分別存放著學(xué)生的“平時總評”、“期中”、“期末”三項成績；

6、最高分：=MAX(K2:K56)——求K2到K56區(qū)域（55名學(xué)生）的最高分；

7、最低分：=MIN(K2:K56)——求K2到K56區(qū)域（55名學(xué)生）的最低分；

8、分?jǐn)?shù)段人數(shù)統(tǒng)計：

（1）=COUNTIF(K2:K56,"100")——求K2到K56區(qū)域100分的人數(shù)；假設(shè)把結(jié)果存放于K57單元格；

（2）=COUNTIF(K2:K56,">=95")－K57——求K2到K56區(qū)域95～99.5分的人數(shù)；假設(shè)把結(jié)果存放于K58單元格；

（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58)——求K2到K56區(qū)域90～94.5分的人數(shù)；假設(shè)把結(jié)果存放于K59單元格；

（4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59)——求K2到K56區(qū)域85～89.5分的人數(shù)；假設(shè)把結(jié)果存放于K60單元格；

（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60)——求K2到K56區(qū)域70～84.5分的人數(shù)；假設(shè)把結(jié)果存放于K61單元格；

（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61)——求K2到K56區(qū)域60～69.5分的人數(shù)；假設(shè)把結(jié)果存放于K62單元格；

（7）=COUNTIF(K2:K56,"<60")——求K2到K56區(qū)域60分以下的人數(shù)；假設(shè)把結(jié)果存放于K63單元格；

說