EXCEL2003公式·函數(shù)應(yīng)用大全(1)
1、SUMPRODUCT函數(shù)：該函數(shù)的功能是在給定的幾組數(shù)組中將數(shù)組間對(duì)應(yīng)的元素相乘并返回乘積之和。例如：如圖1，如果想計(jì)算B3：C6和C3：E6這兩組區(qū)域的值，可以用以下公式：“=Sumproduct(B3:C6,D3:E6)”。
圖1
2、ABS函數(shù)：如果在A1、B1單元格中分別輸入120、90，那么如果要求A1與B1之間的差的絕對(duì)值，可以在C1單元格中輸入以下公式：“=ABS(A1-B1)”。
3、IF函數(shù)：如圖2，如果C3單元格的數(shù)據(jù)大于D3單元格，則在E3單元格顯示“完成任務(wù)，超出：”，否則顯示“未完成任務(wù)，差額：”，可以在E3單元格中輸入以下公式：“=IF(C3>D3, “完成任務(wù)，超出：”,”未完成任務(wù)，差額：””。
圖2
4、Ceiling函數(shù)：該數(shù)值向上舍入基礎(chǔ)的倍數(shù)。如圖3，在C3單元格中輸入以下公式：“=CEILING(B3,C3)”；而“=FLOOR(B3,C3)”則是向下舍入。
圖3
5、GCD函數(shù)：該函數(shù)計(jì)算最大公約數(shù)。如圖4，如果要計(jì)算B3：D3這一區(qū)域中3個(gè)數(shù)字的最大公約數(shù)，可以在E3單元格中輸入以下公式：“=GCD(B3,C3,D3)”。
圖4
6、INT函數(shù)：該函數(shù)是向下舍入取整函數(shù)。如圖5，如果要計(jì)算顯示器和機(jī)箱的購買數(shù)量，可以在E3單元格中輸入以下公式：“=INT(D3/C3)”。
圖5
7、LCM函數(shù)：該函數(shù)是計(jì)算最小公倍數(shù)。如圖6，如果要計(jì)算B3：D3這一區(qū)域中3個(gè)數(shù)字的最小公倍數(shù)，可以在E3單元格中輸入以下公式：“=LCM(B3,C3,D3)”。
圖6
8、LN函數(shù)：該函數(shù)是計(jì)算自然對(duì)數(shù)，公式為：“=LN(B3)”。
9、LOG函數(shù)：該函數(shù)是計(jì)算指定底數(shù)的對(duì)數(shù)，公式為：“=LOG10(B3)”。
10、MOD函數(shù)：該函數(shù)是計(jì)算兩數(shù)相除的余數(shù)。如圖7，判斷C3能否被B3整除，可以在D4單元格中輸入以下公式：“=IF(MOD(B3,C3)=0,"是","否")”。
圖7
11、PI函數(shù)：使用此函數(shù)可以返回?cái)?shù)字3.14159265358979，即數(shù)學(xué)常量PI，可精確到小數(shù)點(diǎn)后14位。如圖8，計(jì)算球體的面積，可以在C4單元格中輸入以下公式：“=PI()*(B3^2)*4)”；計(jì)算球體的體積，可以在D4單元格中輸入以下公式：“= (B3^3)*（4* PI())）/3”。
圖8
12、POWER函數(shù)：此函數(shù)用來計(jì)算乘冪。如圖9，首先在單元中輸入底數(shù)和指數(shù)，然后在D3中輸入以下公式：“=POWER(B3,C3)”。
圖9
13、PRODUCT函數(shù)：此函數(shù)可以對(duì)所有的以參數(shù)形式給出的數(shù)字相乘，并返回乘積。例如：某企業(yè)2005年度貸款金額為100000元，利率為1.5%，貸款期限為12個(gè)月。如圖10所示，直接在單元格E4中輸入以下公式：“ =PRODUCT(B4,C4,D4)”。
圖10
14、RADIANS函數(shù)：此函數(shù)是用來將弧度轉(zhuǎn)換為角度的?？梢栽贑3單元格中輸入以下公式：“=RADIANS (B3)”。
15、RAND函數(shù)：此函數(shù)可以返回大于等于0及小于1的均勻分布隨機(jī)數(shù)，每次計(jì)算工作表時(shí)都將返回一個(gè)新的數(shù)值。如果要使用函數(shù)RAND生成一個(gè)隨機(jī)數(shù)，并且使之不隨單元格的計(jì)算而改變，可以在編輯欄中輸入“=RAND()”，保持編輯狀態(tài)，然后按[F9]鍵，將公式永久性地改為隨機(jī)數(shù)。例如：在全班50名同學(xué)中以隨機(jī)方式抽出20名進(jìn)行調(diào)查，如圖11，在單元格中輸入開始號(hào)碼以及結(jié)束號(hào)碼，然后在單元格B4中輸入以下公式：“=1+RAND()*49”。
圖11
16、ROUND函數(shù)：此函數(shù)為四舍五入函數(shù)。如圖12，例如：將數(shù)字“12.3456”按照指定的位數(shù)進(jìn)行四舍五入，可以在D3單元格中輸入以下公式：“=ROUND(B3,C3)”。
17、ROUNDDOWN函數(shù)：此函數(shù)為向下舍入函數(shù)。例如：出租車的計(jì)費(fèi)標(biāo)準(zhǔn)是：起步價(jià)為5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。輸入不同的公里數(shù)，如圖13所示，然后計(jì)算其費(fèi)用。可以在C3單元格中輸入以下公式：“=IF(B3<=10,5+ROUNDDOWN(B3,0)*2,20+ROUNDDOWN((B3-10)*2,0)*2)”。
圖13
18、ROUNDUP函數(shù)：此函數(shù)為向上舍入函數(shù)。例如：現(xiàn)在網(wǎng)吧的管理一般是采用向上舍入法，不滿一個(gè)單元按照一個(gè)單位計(jì)算?，F(xiàn)假設(shè)每30分鐘計(jì)價(jià)0.5元，請(qǐng)計(jì)算如圖14中所示的上網(wǎng)所花費(fèi)的費(fèi)用。1）計(jì)算上網(wǎng)天數(shù)：首先在單元格C3中輸入以下公式：“=B3-A3”；2）計(jì)算上網(wǎng)分鐘數(shù)：上網(wǎng)分鐘數(shù)實(shí)際上就等于上網(wǎng)天數(shù)乘以60再乘以24，所以應(yīng)在單元格D3中輸入以下公式：“=C3*60*24”；3）計(jì)算計(jì)費(fèi)時(shí)間：本例中規(guī)定每30分鐘計(jì)費(fèi)一次，不滿30分鐘以30分鐘計(jì)價(jià)，所以應(yīng)在單元格E3中輸入以下公式：“=ROUNDUP(D3/30,0)”；4）計(jì)算上網(wǎng)費(fèi)用：在單元格G3中輸入以下公式：“=E3*F3”。
圖14
19、SUBTOTAL函數(shù)：使用該函數(shù)可以返回列表或者數(shù)據(jù)庫中的分類匯總。通常利用[數(shù)據(jù)]—[分類匯總]菜單項(xiàng)可以很容易地創(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
4
max
8
Stdevp
例如某班部分同學(xué)的考試成績(jī)?nèi)鐖D15，1）顯示最低的語文成績(jī)：首先在單元格B9中輸入“顯示最低的語文成績(jī)”的字樣，然后在單元格E9中輸入以下公式：“=SUBTOTAL(5,C3:C7)”；2）顯示最高的數(shù)學(xué)成績(jī)：首先在單元格B10中輸入“顯示最高的數(shù)學(xué)成績(jī)”的字樣，然后在單元格E10中輸入以下公式：“=SUBTOTAL(4,D3:D7)”。
圖15
20、計(jì)算庫存量和獎(jiǎng)金：假設(shè)某公司在月底要根據(jù)員工的業(yè)績(jī)發(fā)放工資并進(jìn)行產(chǎn)品的庫存統(tǒng)計(jì)，本例中規(guī)定員工的基本工資為600元，獎(jiǎng)金按照銷售業(yè)績(jī)的8%提成，總工資等于基本工資與獎(jiǎng)金之和。如圖16，1）在工作表中輸入相應(yīng)的數(shù)據(jù)信息；2）計(jì)算“現(xiàn)存庫量”：在單元格C15中輸入以下公式：“=C14-SUM(C3:C9)”；3）計(jì)算“銷售業(yè)績(jī)”：在單元格G3中輸入以下公式：“=SUMPRODUCT(C3:F3,$C$13:$F$13)”,函數(shù)SUMPRODUCT是計(jì)算數(shù)組C3：F3與數(shù)組$C$13:$F$13乘積的和，用數(shù)學(xué)公式表示出來就是：“=10*3050.5+10*1560.99+5*4489.9+20*2119”；4）計(jì)算獎(jiǎng)金：獎(jiǎng)金是按照銷售業(yè)績(jī)的8%提成得到的，這樣計(jì)算出來的結(jié)果可能會(huì)是小數(shù)，不好找零錢，所以這里采用向上舍入的方式得到整數(shù)，在單元格H3中輸入以下公式：“=ROUNDUP(G3*8%,0)”；5）計(jì)算總工資：由于總工資=基本工資+獎(jiǎng)金，所以在單元格J3中輸入以下公式：“=SUM(H3:I3)”。
圖16
21、計(jì)算工資和票面金額：假設(shè)某公司的銷售人員的銷售情況如圖17所示，按照銷售業(yè)績(jī)的5%計(jì)算銷售提成，下面需要結(jié)合上例中的函數(shù)來計(jì)算銷售人員的銷售業(yè)績(jī)以及獎(jiǎng)金工資，然后再計(jì)算出發(fā)放工資時(shí)需要準(zhǔn)備的票面數(shù)量。1）計(jì)算銷售業(yè)績(jī)：在單元格H13中輸入以下公式：“=SUMPRODUCT(C3:G3,$C$11:$G$11)”；2）計(jì)算提成：在本例中假設(shè)提成后出現(xiàn)小于1元的金額則舍入為1，所以需要使用ROUNDUP函數(shù)，在單元格I3中輸入以下公式：“=ROUNDUP(H3*5%,0)”；3）計(jì)算工資：在單元格K3中輸入以下公式：“=I3+J3”；4）計(jì)算100元的面值：在單元格L3中輸入以下公式：“=INT(K3/$L$2)”；5）計(jì)算50元的面值：在單元格M3中輸入以下公式：“=INT(MOD(K3,$L$2)/$M$2)”，此公式是使用MOD函數(shù)計(jì)算發(fā)放“MOD(K3,$L$2)”張100元后剩下的工資，然后利用取整函數(shù)INT得到50元票面的數(shù)量；6）計(jì)算10元的面值：在單元格N3中輸入以下公式：“=INT(MOD(K3,$M$2)/$N$2)”；7）計(jì)算5元的面值：在單元格O3中輸入以下公式：“=INT(MOD(K3,$N$2)/$O$2)”；8）計(jì)算1元的面值：在單元格P3中輸入以下公式：“=INT(MOD(K3,$O$2)/$P$2)”。
圖17
22、DATE函數(shù)：在實(shí)際工作中經(jīng)常會(huì)用到此函數(shù)來顯示日期。例如：如圖18，在單元格中輸入相應(yīng)的年、月和圖書館日等信息，然后在單元格E3中輸入以下公式：“=DATE(B3,C3,D3)”。
圖18
23、DATEIF函數(shù)：假設(shè)有兩個(gè)已知日期——開始日期和截止日期，那么可以利用DATEIF函數(shù)來計(jì)算它們之間相差的年數(shù)、月數(shù)或者天數(shù)等。如圖19，在單元格D3中輸入以下公式：“=DATEDIF(B3,C3,"y")”。
圖19
24、DAYS360函數(shù)：該函數(shù)計(jì)算兩個(gè)日期之間的天數(shù)，在財(cái)務(wù)中經(jīng)常會(huì)用到，如果財(cái)務(wù)系統(tǒng)是基于一年12個(gè)月并且每月30天，可以使用該函數(shù)幫助計(jì)算借款天數(shù)或者支付款項(xiàng)等。例如：某企業(yè)不同時(shí)間的貸款如圖20所示，然后利用DAYS360函數(shù)來計(jì)算其借款的時(shí)間，并且計(jì)算出還款利息。1）計(jì)算“借款天數(shù)”：在單元格D3中輸入以下公式：“=DAYS360(B3,C3)”；2）計(jì)算“還款利息”：在單元格G3中輸入以下公式：“=D3*E3*F”。
圖20
25、WEEKDAY函數(shù)：使用此函數(shù)可以返回某個(gè)日期為星期幾。語法：WEEKDAY（serial_number,return_type）：其中參數(shù)serial_number代表要查找的那一天的日期，參數(shù)return_type為確定返回值類型的數(shù)字，詳細(xì)內(nèi)容如下表：
參數(shù)值
函數(shù)返回值
1或者省略
返回?cái)?shù)字1（星期日）到數(shù)字7（星期六）之間的數(shù)字。
2
返回?cái)?shù)字1（星期一）到數(shù)字7（星期日）之間的數(shù)字。
3
返回?cái)?shù)字0（星期一）到數(shù)字6（星期日）之間的數(shù)字。
例如：計(jì)算當(dāng)前日期是星期幾：如圖21所示，在單元格B3中輸入計(jì)算當(dāng)前日期的公式：“=WEEKDAY(B3,2)”。
圖21
26、WEEKNUM函數(shù)：使用此函數(shù)可以計(jì)算一年中的第幾周。例如：已知2006年6月9日是星期五，下面利用WEEKNUM函數(shù)計(jì)算在參數(shù)不同的情況下返回的周數(shù)。如圖22所示，在單元格B3中輸入計(jì)算當(dāng)前日期的公式：“=WEEKNUM(B3,C3)”。
圖22
27、WORKDAY函數(shù)：使用此函數(shù)可以返回某個(gè)日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值，工作日不包括周末和專門指定的日期。假設(shè)某出版社要求某個(gè)編輯從2006年3月1日起開始寫稿，利用80天將其完成（其中不包括三天節(jié)假日），此時(shí)可以利用WORKDAY函數(shù)計(jì)算出完成日期。如圖23所示，在單元格中輸入上述信息，然后在單元格C7中輸入以下公式：“=WORKDAY(C2,C3,C4:C6)”。
圖23
28、計(jì)算年假天數(shù)和工齡補(bǔ)貼：假設(shè)某公司規(guī)定，員工任職滿1年的開始有年假，第1至5年每年7天，第6年開始每年10天。截止到2005年6月9日，以工齡計(jì)算每年補(bǔ)貼100元，任職不足一年的按每人50元計(jì)算。如圖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)))”，以此可以計(jì)算出員工的休假天數(shù)；2）在單元格G5中輸入以下公式：“=IF(DATEDIF($D5,DATE($C$2,6,9),"y")>=1,DATEDIF($D5,DATE($C$2,6,9),"y")*100,50)”，以此可計(jì)算出員工的工齡補(bǔ)貼。
圖24
29、計(jì)算火車站寄存包裹費(fèi)用：在火車站寄存包裹是按小時(shí)數(shù)收費(fèi)的。有些按整小時(shí)計(jì)數(shù)，有些按半小時(shí)計(jì)數(shù)，沒有超過半小時(shí)的以半小時(shí)計(jì)，半小時(shí)以上一小時(shí)以內(nèi)的按一小時(shí)計(jì)。同時(shí)包裹的大小不同收費(fèi)也不同，在本例中假設(shè)大的每小時(shí)6元，中型的每小時(shí)4元，小型的每小時(shí)2元，計(jì)算在火車站寄存包裹的費(fèi)用。如圖25所示：1）計(jì)算寄存天數(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í)可計(jì)算出所有型號(hào)的包裹寄存的天數(shù)，在此公式中用到了IF函數(shù)，函數(shù)中的條件為“TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”，它是用來判斷取走時(shí)間是否超過了寄存時(shí)間，如果條件為真則表示還沒有超過一天，那么寄存的天數(shù)就是“DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))-1”，即走取的日期減去寄存的日期再減1，如果時(shí)間超過了，那么寄存的天數(shù)就是“DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))”，即取走的日期與寄存時(shí)的日期之差；2)計(jì)算寄存小時(shí)數(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ù)中的條件與計(jì)算天數(shù)時(shí)的條件是一樣的，也是判斷取走時(shí)間是否超過了寄存時(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í)間的序列數(shù)，其中“TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”表示取走時(shí)間的序列數(shù)。再通過加減計(jì)算得到小時(shí)數(shù)，如果超過了小時(shí)數(shù)則為“HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4)))”，即直接用取走時(shí)間減去存在時(shí)間，取小時(shí)數(shù)；3)計(jì)算寄存分鐘數(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í)即可計(jì)算出所有型號(hào)的包裹寄存的分鐘數(shù)，其公式形式和計(jì)算小時(shí)數(shù)的公式相似，只是將HOUR換成了MINUTE，其判斷條件和前面的一樣，如果取走時(shí)間沒有超過寄存時(shí)間，分鐘數(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í)間減去寄存時(shí)間，取分鐘數(shù)；4)計(jì)算寄存的累計(jì)小時(shí)數(shù)：在單元格H4中輸入以下公式：“=E4*24+F4+IF(G4=0,0,IF(G4<=30,0.5,1))”，在該公式中，“E4*24”表示將天數(shù)轉(zhuǎn)換為小時(shí)數(shù)，在將分鐘轉(zhuǎn)換為小時(shí)數(shù)時(shí)，使用IF函數(shù)來判斷分鐘數(shù)的范圍，若分鐘數(shù)小于等于30則返回0.5小時(shí)，否則返回1小時(shí)，然后將所有的小時(shí)數(shù)相加即可得到累計(jì)小時(shí)數(shù)；5）計(jì)算寄存總費(fèi)用：在單元格J4中輸入以下公式：“=I4*H4”，此時(shí)即可計(jì)算出寄存包裹的費(fèi)用。
圖25
|
|
