實(shí)訓(xùn)八高級(jí)函數(shù)應(yīng)用

1、P96書例（自己操作）技巧：同時(shí)輸入相同數(shù)據(jù)，可先Ctrl選擇多個(gè)單元格后，輸入內(nèi)容后按“CTRL+enter”函數(shù)的概念 函數(shù)是能夠完成特定功能的程序。在Excel中，它是系統(tǒng)預(yù)定義的一些公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算,然后把計(jì)算的結(jié)果存放在某個(gè)單元格中。 在大多數(shù)情況下，函數(shù)的計(jì)算結(jié)果是數(shù)值。當(dāng)然，它也可以返回文本、引用、邏輯值、數(shù)組或工作表的信息 Excel函數(shù)分類分分 類類功功 能能 簡(jiǎn)簡(jiǎn) 介介數(shù)據(jù)庫函數(shù)數(shù)據(jù)庫函數(shù)對(duì)數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行分析、查找、計(jì)算等對(duì)數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行分析、查找、計(jì)算等日期與時(shí)間日期與時(shí)間對(duì)日期和時(shí)間進(jìn)行計(jì)算、設(shè)置及格式化處理對(duì)日

2、期和時(shí)間進(jìn)行計(jì)算、設(shè)置及格式化處理工程函數(shù)工程函數(shù)用于工程數(shù)據(jù)分析與處理用于工程數(shù)據(jù)分析與處理信息函數(shù)信息函數(shù)對(duì)單元格或公式中數(shù)據(jù)類型進(jìn)行判定對(duì)單元格或公式中數(shù)據(jù)類型進(jìn)行判定財(cái)務(wù)函數(shù)財(cái)務(wù)函數(shù)進(jìn)行財(cái)務(wù)分析及財(cái)務(wù)數(shù)據(jù)的計(jì)算進(jìn)行財(cái)務(wù)分析及財(cái)務(wù)數(shù)據(jù)的計(jì)算邏輯函數(shù)邏輯函數(shù)進(jìn)行邏輯判定、條件檢查進(jìn)行邏輯判定、條件檢查統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù)對(duì)工作表數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析對(duì)工作表數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析查找函數(shù)查找函數(shù)查找特定的數(shù)據(jù)或引用公式中的特定信息查找特定的數(shù)據(jù)或引用公式中的特定信息文本函數(shù)文本函數(shù)對(duì)公式、單格中的字符、文本進(jìn)行格式化或運(yùn)算對(duì)公式、單格中的字符、文本進(jìn)行格式化或運(yùn)算數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)進(jìn)行數(shù)學(xué)計(jì)算等進(jìn)行數(shù)學(xué)

3、計(jì)算等外部函數(shù)外部函數(shù)進(jìn)行外部函數(shù)調(diào)用及數(shù)據(jù)庫的鏈接查詢等功能進(jìn)行外部函數(shù)調(diào)用及數(shù)據(jù)庫的鏈接查詢等功能自定義函數(shù)自定義函數(shù)用戶用用戶用vba編寫，用于完成特定功能的函數(shù)編寫，用于完成特定功能的函數(shù)函數(shù)調(diào)用 函數(shù)的語法 函數(shù)名(參數(shù)1, 參數(shù)2, 參數(shù)3,) 在公式中調(diào)用函數(shù) =IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套函數(shù)嵌套函數(shù)函數(shù)輸入在單元格的公式中直接輸入函數(shù)調(diào)用函數(shù)調(diào)用向?qū)xcel的幫助系統(tǒng)1 1、案例、案例 某班學(xué)生成績(jī)表如圖所示，統(tǒng)計(jì)出其中的某班學(xué)生成績(jī)表如圖所示，統(tǒng)計(jì)出其中的平均分、總分、平均分、總分、參考人數(shù)、等級(jí)及缺考人數(shù)參考人數(shù)、等級(jí)及缺考人數(shù)

4、等數(shù)據(jù)。等數(shù)據(jù)。 該工作表要用到：該工作表要用到：if,average,sum,count,countif，max,min等函數(shù)等函數(shù)2、條件函數(shù)IF 用法 IF(條件, 表達(dá)式1, 表達(dá)式2)。 功能 當(dāng)條件成立時(shí)，計(jì)算出表達(dá)式1的值；當(dāng)條件不成立時(shí)，計(jì)算出表達(dá)式2的值 事例 If(A160,“及格了”,“不及格”)，若A1單元格的值是76，則該函數(shù)的結(jié)果是：及格了；若A1單元格的值是50，則該函數(shù)的結(jié)果是:不及格。 IF函數(shù)的嵌套調(diào)用If（B3:B12100000,”差”, If（B3:B12200000,”一般”, If（B3:B12300000,”較好”, If（B3:B12大于大于a

5、1b1小于小于a1=大于等于大于等于a1=60=小于等于小于等于a1=”dd”不等于不等于a102邏輯運(yùn)算 AND(l1, l2, ) OR(l1, l2, ) NOT(logical) TRUE( ) FALSE( ) 其中， l1, l2是關(guān)系式或邏輯值。案例 某單位有3000名職工，要按其工資繳納個(gè)人收入所得稅，稅率與工資的關(guān)系如表所示。 該單位的職工表如下所示，用IF函數(shù)求出各職工的稅率！ 工工 資資稅稅 率率3000以上以上稅率稅率=30%25003000稅率稅率=25%20002500稅率稅率=20%15002000稅率稅率=15%12001500稅率稅率=10%10001200稅

6、率稅率=8%8001000稅率稅率=5%800以下以下稅率稅率=0在在E4中輸入編輯欄中中輸入編輯欄中的公式！的公式！Excel提供了許多數(shù)學(xué)和三角函數(shù)，它們能夠完成大多數(shù)數(shù)學(xué)和三角運(yùn)算，這些函數(shù)可以在公式中直接引用，然后將公式的計(jì)算結(jié)果返回到輸入公式的單元格中。常見的數(shù)學(xué)函數(shù)MOD MOD 、TRUNC TRUNC 、ABS ABS 、SQRT SQRT 、SIN SIN 、ASIN(ASIN(n n1)1)，ACOS(ACOS(n n1)1)，ATAN(ATAN(n n2)2)、EXP(EXP(n n) )、POWER(POWER(x x, , n n) )、LN(LN(n n) )、FA

7、CT(FACT(n n) )、LOG(LOG(n n, base), base)、MINVERSE(array)MINVERSE(array)、MMULT(array1, array2) MMULT(array1, array2) 1、Excel處理日期的方式 Excel將日期存儲(chǔ)為一系列連續(xù)的序列數(shù)，將時(shí)間存儲(chǔ)為小數(shù)。 例如，37260.73表示2002-1-4 5:36 PM，在這個(gè)數(shù)中小數(shù)點(diǎn)前面的數(shù)字37260表示日期2002-1-4，小數(shù)點(diǎn)后的數(shù)字73表示時(shí)間，即下午5點(diǎn)36分。 日期和時(shí)間都是數(shù)值，因此它們也可以進(jìn)行各種運(yùn)算。如果要計(jì)算兩個(gè)日期之間的差值，可以用一個(gè)日期減去另一個(gè)日期

8、。 1DATE函數(shù) 用法 DATE(year, month, day) 功能 DATE函數(shù)利用所給的參數(shù)，構(gòu)造一個(gè)日期序列數(shù) 例如 DATE(2005,3,21)的結(jié)果是2005-3-212YEAR，MONTH，DAY函數(shù) 用法 YEAR(serial_number) MONTH(serial_number) DAY(serial_number) 其中的serial_number是一個(gè)日期或數(shù)字。 功能 YEAR函數(shù)返回某日期的年份。 MONTH函數(shù)返回以系列數(shù)表示的日期中的月份。 DAY函數(shù)返回以系列數(shù)表示的某日期的天數(shù)，用整數(shù)131表示。 3TODAY、NOW函數(shù) 用法 TODAY( )

9、NOW( ) 功能 TODAY函數(shù)返回系統(tǒng)的當(dāng)前日期。 NOW函數(shù)計(jì)算當(dāng)前日期和時(shí)間。 4.時(shí)間函數(shù)常結(jié)合一起來用：如year(today()5WEEKDAY 用法 WEEKDAY(serial_number, return_type) 其中： serial_number代表要查找的日期，或日期的系列數(shù)，以了解該日期為星期幾；return_type確定返回值類型的數(shù)字，。 功能 WEEKDAY計(jì)算給定的日期是星期幾 NETWORKDAYS函數(shù)（有些版本無此函數(shù)）用法 NETWORKDAYS(start_date, end_date, holidays) 其中 start_date表示代表開始日

10、期，end_date為終止日期， holidays表示不在工作日歷中的一個(gè)或多個(gè)日期所構(gòu)成的可選區(qū)域，如元旦節(jié)、五一節(jié)、春節(jié)。 案例案例 某建筑公司按天數(shù)計(jì)算每個(gè)民工的報(bào)酬，民工每個(gè)星期工作某建筑公司按天數(shù)計(jì)算每個(gè)民工的報(bào)酬，民工每個(gè)星期工作5 5天，工作天，工作時(shí)間和請(qǐng)假時(shí)間如圖的時(shí)間和請(qǐng)假時(shí)間如圖的AHAH列所示，列所示，I I列是每天的報(bào)酬，列是每天的報(bào)酬，用日期函數(shù)計(jì)用日期函數(shù)計(jì)算每個(gè)民工的工作天數(shù)和總工資算每個(gè)民工的工作天數(shù)和總工資。 3 3、用、用IFIF和和SUMSUM函數(shù)進(jìn)行條件匯總。函數(shù)進(jìn)行條件匯總。 某電腦公司主要經(jīng)營(yíng)計(jì)算機(jī)的CPU、主板、內(nèi)存條、硬盤、顯卡和聲卡等電腦配件

11、。該公司將每天的銷售數(shù)據(jù)記錄在Excel的數(shù)據(jù)表中，如下圖的表一所示。該公司希望隨時(shí)統(tǒng)計(jì)截止某一月份時(shí)，各種零配件的銷售總數(shù)和銷售總金額。統(tǒng)計(jì)的結(jié)果如下圖表二所示。 表二中顯示的是截止到2004年4月份時(shí)，各種電腦配件的銷售總量和銷售總金額，即該公司1、2、3、4共4個(gè)月的銷售匯總數(shù)據(jù)。如果在表二的截止月份中輸入3，則將1、2、3這3個(gè)月的銷售數(shù)據(jù)統(tǒng)計(jì)在表二之中。 概述 查找引用函數(shù)能通過地址、行、列對(duì)工作表的單元格進(jìn)行訪問，也可通過這些函數(shù)從單元格的地址中求出其所在的行或列，進(jìn)而查獲更多的信息。當(dāng)需要從一個(gè)工作表查詢特定的值、單元格內(nèi)容、格式或選擇單元格區(qū)域時(shí)，這類函數(shù)特別有用。 1 1AD

12、DRESS ADDRESS 函數(shù)函數(shù) 用法用法 ADDRESS(row, col, abs_num, a1, sheet_text)ADDRESS(row, col, abs_num, a1, sheet_text) 其中其中 row_numrow_num是引用中使用的行號(hào)；是引用中使用的行號(hào)；column_numcolumn_num是引用中使用的列標(biāo)；是引用中使用的列標(biāo)；abs_numabs_num指明返回的單元格引用類型指明返回的單元格引用類型 sheet_textsheet_text為一文本，指明作為外部引用的工作表的名稱，如果省為一文本，指明作為外部引用的工作表的名稱，如果省略略she

13、et_textsheet_text，則不使用任何工作表名。，則不使用任何工作表名。 功能功能 用指定的行號(hào)和列標(biāo)，建立文本類型的單元格地址用指定的行號(hào)和列標(biāo)，建立文本類型的單元格地址 例如：例如： ADDRESS(6,3) = ADDRESS(6,3) = “$C$6$C$6” ADDRESS(6,1,2) =A$6ADDRESS(6,1,2) =A$6 ADDRESS(2,3,1,false,book1 sheet1)=book1sheet1!r2c3ADDRESS(2,3,1,false,book1 sheet1)=book1sheet1!r2c3 2 2choosechoose函數(shù)函數(shù)

14、用法用法 CHOOSE(N, V1, V2, .)CHOOSE(N, V1, V2, .) 功能功能 利用索引利用索引N N從參數(shù)清單從參數(shù)清單V1, V2, .V1, V2, .中選擇需要的數(shù)值中選擇需要的數(shù)值 。 例如：例如： CHOOSE(5,“一月”,“二月”,“三月”,“四月”,“五月”,“六月”)=“五月”； SUM(A1:CHOOSE(3,A10,A20,A30)=SUM(A1:A30) CHOOSE(B5, nails,screws,nuts,bolts)=nuts 4 4LOOKUPLOOKUP函數(shù)函數(shù) 用法用法 LOOKUP(value, r1, r2)LOOKUP(val

15、ue, r1, r2) 其中其中 valuevalue是要查找的數(shù)值，可以為數(shù)字、文本、邏輯值或包含數(shù)是要查找的數(shù)值，可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。值的名稱或引用。 r1r1為只包含一行或一列的區(qū)域，其值可以為文本、數(shù)字或邏為只包含一行或一列的區(qū)域，其值可以為文本、數(shù)字或邏輯值。輯值。 r2r2為只包含一行或一列的區(qū)域，其大小必須與為只包含一行或一列的區(qū)域，其大小必須與r1r1相同相同 功能功能 該函數(shù)在該函數(shù)在r1r1所在的行或列中查找值為所在的行或列中查找值為valuevalue的單元格，然后返的單元格，然后返回回r2r2中與中與r1r1同行或同列的單元格中的值。同行或同

16、列的單元格中的值。缺點(diǎn)：lookup只能模糊查找，所以查找中經(jīng)常會(huì)出現(xiàn)問題，而vlookup和hlookup可以精確查找，更常用5 5、HLOOKUPHLOOKUP函數(shù)函數(shù)用法用法HLOOKUP(value, table, n, range_lookup)HLOOKUP(value, table, n, range_lookup)說明說明valuevalue為需要在數(shù)據(jù)表第一行中進(jìn)行的查找的數(shù)值，它可以是數(shù)為需要在數(shù)據(jù)表第一行中進(jìn)行的查找的數(shù)值，它可以是數(shù)值、引用或文字串。值、引用或文字串。tabletable是需要在其中查找數(shù)據(jù)的數(shù)據(jù)表、可以為對(duì)區(qū)域或區(qū)域名是需要在其中查找數(shù)據(jù)的數(shù)據(jù)表、可以

17、為對(duì)區(qū)域或區(qū)域名稱的引用。它的第一行的數(shù)值可以為文本、數(shù)字或邏輯值。稱的引用。它的第一行的數(shù)值可以為文本、數(shù)字或邏輯值。如果如果range_lookuprange_lookup為為1 1，則進(jìn)行近似值查找，且，則進(jìn)行近似值查找，且tabletable第一行的第一行的數(shù)值必須按升序排列；如果數(shù)值必須按升序排列；如果 range_lookuprange_lookup為為0 0，則進(jìn)行精確查，則進(jìn)行精確查找，且找，且tabletable不必進(jìn)行排序。不必進(jìn)行排序。功能功能在數(shù)據(jù)清單（或區(qū)域）在數(shù)據(jù)清單（或區(qū)域）tabletable的第一行中查找值為的第一行中查找值為valuevalue的單元的單元格

18、，如果找到，則返回該列第格，如果找到，則返回該列第n n行單元格的值。行單元格的值。 案例案例4、Vlookup函數(shù)查找 Vlookup函數(shù)能夠從其它工作表（或本工作表）將某數(shù)據(jù)列中的數(shù)據(jù)查找出需要的數(shù)據(jù)。 VLOOKUP函數(shù)的用法如下： VLOOKUP（x, table, n, r） 其中，x是要查找的值，table是一個(gè)單元格區(qū)域（即由工作表的1列或多列組成）。Excel將在table的第一列查找有無x這樣的值（只在table的第1列查找有無x） n是table中待返回的匹配值的列序號(hào)。 r可以取0或1，當(dāng)r為0時(shí)，該函數(shù)將進(jìn)行精確匹配查找； 當(dāng)r=1時(shí)，table的第一列應(yīng)按升序排列，否

19、則找到的結(jié)果可能不正確，r=1表示Excel實(shí)行的是近似值查找。6 6MATCHMATCH函數(shù)函數(shù) 用法用法 MATCH(value, array, type)MATCH(value, array, type) 其中其中 valuevalue是在是在arrayarray中查找的數(shù)中查找的數(shù)值。值。 arrayarray是包含要查找數(shù)據(jù)的區(qū)是包含要查找數(shù)據(jù)的區(qū)域，數(shù)組域，數(shù)組 typetype為數(shù)字為數(shù)字-1-1、0 0或或1 1 功能 在指定方式下與指定數(shù)值匹在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置配的數(shù)組中元素的相應(yīng)位置 取值取值函函 數(shù)數(shù) 功功 能能-1array 必須按降序排列，

20、必須按降序排列，查找大于或等于查找大于或等于 value的最小數(shù)值的最小數(shù)值0array 不必排序，查找不必排序，查找等于等于value的第一個(gè)的第一個(gè)數(shù)值，精確查找數(shù)值，精確查找1array 必須按升序排列，必須按升序排列，查找小于或等于查找小于或等于value的最大數(shù)值的最大數(shù)值6 6、INDEXINDEX函數(shù)函數(shù) 用法用法 INDEX(array,row_num,column_num)INDEX(array,row_num,column_num) 其中其中 arrayarray 是單元格區(qū)域或數(shù)組常量。是單元格區(qū)域或數(shù)組常量。row_numrow_num 是是arrayarray中某行中

21、某行的行號(hào)，的行號(hào)，column_numcolumn_num是是arrayarray中某列的列號(hào)。中某列的列號(hào)。 功能 indexindex函數(shù)返回函數(shù)返回arrayarray中中row_numrow_num和和column_numcolumn_num交叉處的：交叉處的：?jiǎn)卧褚?。單元格引用?例：例：p103p103MOD（number,divisor），相除的余數(shù)，結(jié)果的的正負(fù)號(hào)與除數(shù)相同，除數(shù)不能為0，例mod(51,4)=3 余數(shù)為3，mod(-101,-2)余數(shù)為1，但正負(fù)號(hào)要與除數(shù)相同，所以結(jié)果為-1Row()自動(dòng)算行數(shù)=IF(MOD(ROW(),3)=0,IF(MOD(ROW(

22、),3)=1,Sheet8!A$1,INDEX(Sheet8!$A:$L,INT(ROW()+4)/3,COLUMN()1 1、用、用INDEXINDEX和和MATCHMATCH進(jìn)行模糊查找進(jìn)行模糊查找 已知個(gè)人所得稅稅率的計(jì)算方法如右表所示 某單位的職工收入表如下圖的DF列所示，現(xiàn)要計(jì)算每個(gè)職工應(yīng)納的個(gè)人所得稅稅率。 為了便于數(shù)據(jù)對(duì)比，將各種收入所對(duì)應(yīng)的個(gè)人所得稅稅率列于下圖的A2:B10單元格區(qū)域內(nèi)。 收入收入稅率稅率說明說明1000以下以下0%不含不含10001000100012005%含含10001000120015008%含含120012001500200010%含含15001500

23、2000300012%含含200020003000400015%含含300030004000500020%含含400040005000以上以上25%含含50005000Index&Match的模糊查找2 2、用、用INDEXINDEX和和MATCHMATCH進(jìn)行精確查找進(jìn)行精確查找 某水果批發(fā)商將其水果銷往上海、廣西、四川等地，他將各地的水果單價(jià)保存在一個(gè)單獨(dú)的工作表中，如下圖（a）所示。同時(shí)在另一個(gè)工作表中保存每周的銷售數(shù)據(jù)，如圖（b）所示 用Index和Match函數(shù)將圖（a）中的水果單價(jià)查找到圖(b)的E列。 4、Vlookup函數(shù)查找 Vlookup函數(shù)能夠從其它工作表（或本工

24、作表）將某數(shù)據(jù)列中的數(shù)據(jù)查找出需要的數(shù)據(jù)。 VLOOKUP函數(shù)的用法如下： VLOOKUP（x, table, n, r） 其中，x是要查找的值，table是一個(gè)單元格區(qū)域（即由工作表的1列或多列組成）。Excel將在table的第一列查找有無x這樣的值（只在table的第1列查找有無x） n是table中待返回的匹配值的列序號(hào)。 r可以取0或1，當(dāng)r為0時(shí)，該函數(shù)將進(jìn)行精確匹配查找； 當(dāng)r=1時(shí)，table的第一列應(yīng)按升序排列，否則找到的結(jié)果可能不正確，r=1表示Excel實(shí)行的是近似值查找。小結(jié)小結(jié)數(shù)據(jù)查找是數(shù)據(jù)查找是ExcelExcel辦公應(yīng)用中最常見、最重要的問辦公應(yīng)用中最常見、最重要

25、的問題，方法不當(dāng)，本來幾分種就能解決的問題，可能題，方法不當(dāng)，本來幾分種就能解決的問題，可能要弄幾小時(shí)，甚至幾天。要弄幾小時(shí)，甚至幾天?？偟恼f來，在工作中應(yīng)用最廣、作用最大的查找函總的說來，在工作中應(yīng)用最廣、作用最大的查找函數(shù)是數(shù)是VLOOKUPVLOOKUP。在在查找數(shù)據(jù)的案例集合查找數(shù)據(jù)的案例集合.xls.xls工作薄工作薄中包括了近年在中包括了近年在實(shí)際辦公應(yīng)用中收集到的數(shù)據(jù)查找案例。實(shí)際辦公應(yīng)用中收集到的數(shù)據(jù)查找案例。ExcelExcel提供了許多有關(guān)財(cái)務(wù)、投資、償還、利息及提供了許多有關(guān)財(cái)務(wù)、投資、償還、利息及折舊方面的函數(shù)，在工作表中運(yùn)用這些函數(shù)可以較折舊方面的函數(shù)，在工作表中運(yùn)用

26、這些函數(shù)可以較松地完成相關(guān)的財(cái)務(wù)運(yùn)算，或者對(duì)其他財(cái)務(wù)管理軟松地完成相關(guān)的財(cái)務(wù)運(yùn)算，或者對(duì)其他財(cái)務(wù)管理軟件的運(yùn)算輸出數(shù)據(jù)進(jìn)行驗(yàn)證。件的運(yùn)算輸出數(shù)據(jù)進(jìn)行驗(yàn)證。1 1、折舊函數(shù)、折舊函數(shù) ExcelExcel折舊函數(shù)有折舊函數(shù)有DBDB、DDBDDB、SLNSLN、YDBYDB及及VDBVDB。運(yùn)用這。運(yùn)用這5 5個(gè)折舊函數(shù)可以確定指定時(shí)期內(nèi)資產(chǎn)的折舊值。個(gè)折舊函數(shù)可以確定指定時(shí)期內(nèi)資產(chǎn)的折舊值。 這這5 5個(gè)折舊函數(shù)有個(gè)折舊函數(shù)有4 4個(gè)參數(shù)是共有的，如表所示。個(gè)參數(shù)是共有的，如表所示。cost為資產(chǎn)原值為資產(chǎn)原值salvage為資產(chǎn)在折舊期末的價(jià)值（也稱為資產(chǎn)殘值）為資產(chǎn)在折舊期末的價(jià)值（也稱為

27、資產(chǎn)殘值）life為折舊期限（有時(shí)也可稱作資產(chǎn)的生命周期）為折舊期限（有時(shí)也可稱作資產(chǎn)的生命周期）period必須使用與必須使用與lIFe相同的單位相同的單位DBDB函數(shù)函數(shù) 用法用法 DB(cost, salvage, life, period, month)DB(cost, salvage, life, period, month) 其中：其中：lIFelIFe和和periodperiod須使用相同的時(shí)間單位；須使用相同的時(shí)間單位；monthmonth為第一年的為第一年的月份數(shù)，如果省略，則默認(rèn)為月份數(shù)，如果省略，則默認(rèn)為1212。 功能功能 DBDB函數(shù)使用固定余額遞減法，計(jì)算一筆資產(chǎn)在

28、給定期限的折舊函數(shù)使用固定余額遞減法，計(jì)算一筆資產(chǎn)在給定期限的折舊值值 例如例如 某學(xué)校購買了一批計(jì)算機(jī)，價(jià)值為某學(xué)校購買了一批計(jì)算機(jī)，價(jià)值為500 000500 000元使用期限為元使用期限為3 3年，年，報(bào)廢價(jià)值為報(bào)廢價(jià)值為100 000100 000元。每年的折舊公式及結(jié)果值如下所示：元。每年的折舊公式及結(jié)果值如下所示： DB(500000,100000,3,1,6) =DB(500000,100000,3,1,6) =￥103 750.00103 750.00 DB(500000,100000,3,2,6) =DB(500000,100000,3,2,6) =￥164 443.7516

29、4 443.75 DB(500000,100000,3,3,6) =DB(500000,100000,3,3,6) =￥96 199.59 96 199.59 DDBDDB函數(shù)函數(shù) 用法：用法：DDB(cost, salvage, life, period, factorDDB(cost, salvage, life, period, factor) ) 其中，其中，factorfactor為余額遞減速率。如為余額遞減速率。如factorfactor省略，則默認(rèn)為省略，則默認(rèn)為2 2（雙倍余額（雙倍余額遞減法）。遞減法）。5 5個(gè)參數(shù)都必須為正數(shù)個(gè)參數(shù)都必須為正數(shù) 功能功能 DDBDDB函數(shù)使

30、用雙倍余額遞減法或其他指定方法，計(jì)算一筆資產(chǎn)在給定期函數(shù)使用雙倍余額遞減法或其他指定方法，計(jì)算一筆資產(chǎn)在給定期限內(nèi)的折舊值限內(nèi)的折舊值 例如：例如： 學(xué)?；▽W(xué)校花1010萬元購買了一臺(tái)新設(shè)備，使用期限為萬元購買了一臺(tái)新設(shè)備，使用期限為1010年，報(bào)廢價(jià)值為年，報(bào)廢價(jià)值為1 1萬。萬。下面的例子給出幾個(gè)期限內(nèi)的折舊值（結(jié)果保留兩位小數(shù)）。下面的例子給出幾個(gè)期限內(nèi)的折舊值（結(jié)果保留兩位小數(shù)）。 DDB(100000,10000,3650,1) =DDB(100000,10000,3650,1) =￥54.7954.79，第一天的折舊值。，第一天的折舊值。factorfactor被被ExcelExc

31、el默認(rèn)設(shè)置設(shè)為默認(rèn)設(shè)置設(shè)為2 2。 DDB(100000,10000,120,1) =DDB(100000,10000,120,1) =￥1 666.671 666.67，第一個(gè)月折舊值。，第一個(gè)月折舊值。 DDB(100000,10000,10,1) =DDB(100000,10000,10,1) =￥20 000.0020 000.00，第一年的折舊值。，第一年的折舊值。 DDB(100000,10000,10,3,1.5) =DDB(100000,10000,10,3,1.5) =￥10 837.5010 837.50，第三年的折舊。，第三年的折舊。 這里沒有使用雙倍余額遞減法，這里沒

32、有使用雙倍余額遞減法，factor=1.5factor=1.5。 SYDSYD、SLNSLN、VDBVDB函數(shù)函數(shù) 用法 SYD(cost, salvage,life, per) SLN(cost, salvage, life) VDB(cost, salvage, lIFe, start_period, end_period, factor, no_switch) 功能 SYDSYD函數(shù)計(jì)算某項(xiàng)資產(chǎn)按年限總和折舊法計(jì)算的某期限內(nèi)的函數(shù)計(jì)算某項(xiàng)資產(chǎn)按年限總和折舊法計(jì)算的某期限內(nèi)的折舊值折舊值 SLNSLN函數(shù)計(jì)算一項(xiàng)資產(chǎn)每期的直線折舊值函數(shù)計(jì)算一項(xiàng)資產(chǎn)每期的直線折舊值 VDBVDB函數(shù)代表可變

33、余額遞減法，可使用雙倍遞減余額法或其函數(shù)代表可變余額遞減法，可使用雙倍遞減余額法或其他指定的方法，計(jì)算指定期間內(nèi)或某一時(shí)間段內(nèi)的資產(chǎn)折舊他指定的方法，計(jì)算指定期間內(nèi)或某一時(shí)間段內(nèi)的資產(chǎn)折舊額。額。 2 2、 投資函數(shù)投資函數(shù) 投資函數(shù)的參數(shù) Excel投資分析方面的函數(shù)，這些函數(shù)使用的參數(shù)大致相同，意義相近 ，如下表所示。參參 數(shù)數(shù)說說 明明rate為各期利率，為固定值為各期利率，為固定值per用于計(jì)算其本金數(shù)額的期次，必須在用于計(jì)算其本金數(shù)額的期次，必須在 1nper 之間之間nper為總投資（或貸款）期次，即該項(xiàng)投資（或貸款）的付款期總數(shù)為總投資（或貸款）期次，即該項(xiàng)投資（或貸款）的付款期

34、總數(shù)pv為現(xiàn)值，即從該項(xiàng)投資（或貸款）開始計(jì)算時(shí)已經(jīng)入賬的款項(xiàng)，或一系列未來為現(xiàn)值，即從該項(xiàng)投資（或貸款）開始計(jì)算時(shí)已經(jīng)入賬的款項(xiàng)，或一系列未來付款當(dāng)前值的累積和，也稱為本金付款當(dāng)前值的累積和，也稱為本金fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè)其，則假設(shè)其值為零（如一筆貸款的未來值即為零）值為零（如一筆貸款的未來值即為零）type數(shù)字?jǐn)?shù)字 0 或或 1，用以指定各期的付款時(shí)間是在期初還是期末，用以指定各期的付款時(shí)間是在期初還是期末，0：期末；：期末；1：期初：期初value1,value2value1,v

35、alue29代表代表129個(gè)償還金額不相等時(shí)的分期償還額個(gè)償還金額不相等時(shí)的分期償還額PMTPMT函數(shù)函數(shù) (貸款還款) 用法 PMT(rate, nper, pv, fv, type) 說明，PMT返回的支付款項(xiàng)包括本金和利息，但不包括稅款。rate和nper單位要一致 。 功能 PMT基于固定利率及等額分期付款方式，計(jì)算投資或貸款的每期付款額 。 例如：某人買房了，貸款200 000元，利率為7%，分10個(gè)月付清，則他的月支付額為： PMT(7%/12, 10, 200000) =￥-20 647.26 P105操作題（注意現(xiàn)值、未來值）哪個(gè)貸款方案比較好？PVPV函數(shù)函數(shù) 用法用法 PV

36、(rate, nper, PMT, fv, type)PV(rate, nper, PMT, fv, type) 功能功能 PVPV函數(shù)可以計(jì)算投資的現(xiàn)值?，F(xiàn)值是一系列未來付款當(dāng)前值的函數(shù)可以計(jì)算投資的現(xiàn)值。現(xiàn)值是一系列未來付款當(dāng)前值的累積和，如借入方的借入款即為貸出方貸款的現(xiàn)值。累積和，如借入方的借入款即為貸出方貸款的現(xiàn)值。 例如：例如： 某人想買一筆養(yǎng)老保險(xiǎn)，該保險(xiǎn)可以在今后某人想買一筆養(yǎng)老保險(xiǎn)，該保險(xiǎn)可以在今后2525年內(nèi)于每月末回年內(nèi)于每月末回報(bào)報(bào)500500元。該保險(xiǎn)的購買成本為元。該保險(xiǎn)的購買成本為75 00075 000，假定投資回報(bào)率為，假定投資回報(bào)率為6.7%6.7%。該保險(xiǎn)

37、是否合算？。該保險(xiǎn)是否合算？ 現(xiàn)在可以通過函數(shù)現(xiàn)在可以通過函數(shù)PVPV計(jì)算一下這筆投資是否值得。該項(xiàng)投資的計(jì)算一下這筆投資是否值得。該項(xiàng)投資的年金現(xiàn)值為：年金現(xiàn)值為： PV(0.067/12,12PV(0.067/12,12* *25,500,0)=-72 700.0525,500,0)=-72 700.05小強(qiáng)存了些錢，準(zhǔn)備參加“好極有限公司的投資項(xiàng)目”，此項(xiàng)目需交40萬元，未來4年內(nèi)每年可領(lǐng)回11萬元作為收入，銀行年利率為4%,此項(xiàng)投資可行否？FVFV函數(shù)函數(shù) 用法 FV(rate, nper, PMT, pv, type) rate為存款利率；nper為總的存款時(shí)間，對(duì)于三年期零存整取存

38、款來說共有3*12=36個(gè)月；pmt為每月存款金額，如果忽略pmt則公式必須包含參數(shù)fv；fv為最后一次存款后希望得到的現(xiàn)金總額，如果省略了fv則公式中必須包含pmt參數(shù)；type為數(shù)字0或1，它指定存款時(shí)間是月初還是月末 功能 FV函數(shù)計(jì)算投資在將來某個(gè)日期的價(jià)值，它可以計(jì)算出投資的一次性償還金額，也可以計(jì)算出一系列數(shù)額相等的分期償還金額。 例如 某人為保證退休后的生活，打算每年年初存入3 000元，在整個(gè)投資期間，平均投資回報(bào)率為7%。此人今年30歲，到他60歲時(shí)，有多少存款？計(jì)算方法如下。 FV(7%, 30,-3000,1)=￥283374.74P104頁題小強(qiáng)總算找了份工，他每月存1

39、500，選擇了工行的“零存整付”業(yè)務(wù)，年利率3%，合約期2年，那2年后他能拿多少錢呢？XNPVXNPV函數(shù)函數(shù)( (不是系統(tǒng)默認(rèn)函數(shù)，需在工具加載宏加載不是系統(tǒng)默認(rèn)函數(shù)，需在工具加載宏加載工具分析箱工具分析箱) ) 用法用法 XNPV(rate, values, dates)XNPV(rate, values, dates) 功能功能 XNPVXNPV函數(shù)計(jì)算一組現(xiàn)金流的凈現(xiàn)值，這些現(xiàn)金流函數(shù)計(jì)算一組現(xiàn)金流的凈現(xiàn)值，這些現(xiàn)金流不一定定期不一定定期發(fā)生發(fā)生 若要計(jì)算一組若要計(jì)算一組定期定期現(xiàn)金流的凈現(xiàn)值，使用函數(shù)現(xiàn)金流的凈現(xiàn)值，使用函數(shù) NPVNPV 例如例如 假定某項(xiàng)投資需要在假定某項(xiàng)投資需要

40、在20022002年年3 3月月2 2日支付現(xiàn)金日支付現(xiàn)金30 00030 000元，并于下述時(shí)元，并于下述時(shí)間獲取以下金額的返回資金：間獲取以下金額的返回資金：20022002年年4 4月月2121日返回日返回8 7508 750元；元；20022002年年6 6月月1010日返回日返回7 2507 250元；元；20022002年年7 7月月3030日返回日返回16 25016 250元；元；20022002年年9 9月月1818日返回日返回9 7509 750元。假設(shè)資金流轉(zhuǎn)折扣為元。假設(shè)資金流轉(zhuǎn)折扣為7%7%，則凈現(xiàn)值為。，則凈現(xiàn)值為。 XNPV(0.07,-30000,8750,72

41、50,16250,9750,XNPV(0.07,-30000,8750,7250,16250,9750,37317,37667,38017,38367,38717)37317,37667,38017,38367,38717)= =￥5 465.45 5 465.45 其它投資函數(shù)其它投資函數(shù) NPV(NPV(rate, value1, value2, .)rate, value1, value2, .) IPMT(rate, per, nper, pv, fv, type)IPMT(rate, per, nper, pv, fv, type) PPMT(rate, per, nper, pv,

42、 fv, type)PPMT(rate, per, nper, pv, fv, type) NPER(rate, PMT, pv, fv, type)NPER(rate, PMT, pv, fv, type)小明想買保險(xiǎn)，昆人人壽有份15年的保險(xiǎn)計(jì)劃，前5年每年交3萬元保費(fèi)，同時(shí)第5-10年可以每年鄰回2萬元紅利，第11-15年每年可以領(lǐng)回2萬3千元。年通貨膨脹5%(或年折扣率)，些保險(xiǎn)計(jì)劃是否值 得購買？(定期發(fā)生用NPV)3 3、計(jì)算償還率的函數(shù)、計(jì)算償還率的函數(shù) RATERATE函數(shù)函數(shù) 用法用法 RATE(nper, PMT, pv, fv, type, guess)RATE(nper, PMT, pv, fv, type, guess) 功能功能 RATERATE函數(shù)用于計(jì)算投資的各期利率?？梢杂?jì)算連續(xù)分期等額投函數(shù)用于計(jì)算投資的各期利率?？梢杂?jì)算連續(xù)分期等額投資的償還率，也可以計(jì)算一次性償還的投資利率資的償還率，也可以計(jì)算一次性償還的投資利率 例如例如 某人修房貸款某人修房貸款100 000100 000元的元的6 6年期貸款，月支付額為年期貸款，月支付額為2 2002 200元，元，該筆貸款的利率為。該筆貸款的利率為。 =RATE(12=RATE(12* *6, -