EXCEL函數(shù)培訓(xùn)課件.ppt

EXCEL函數(shù)學(xué)習(xí),2010年7月,1,單位：億元,常用函數(shù)-目錄,2,1、IF函數(shù),2、SUM函數(shù),3、SUMIF函數(shù),5、COUNT函數(shù),4、SUMPRODUCT函數(shù),6、COUNTA函數(shù),7、COUNTBLANK函數(shù),8、COUNTIF函數(shù),10、ROUND函數(shù),11、CONCATENATE函數(shù),13、LOOKUP函數(shù),14、VLOOKUP函數(shù),9、RANK函數(shù),12、TEXT函數(shù),單位：億元,常用函數(shù),3,1、IF函數(shù),IF函數(shù)是一個(gè)邏輯函數(shù)，當(dāng)達(dá)到某一個(gè)條件是，取某一個(gè)值，它經(jīng)常與其函數(shù)合并使用，在財(cái)務(wù)上典型應(yīng)用是個(gè)稅計(jì)算。,單位：億元,常用函數(shù),4,2、SUM求和函數(shù),SUM求和函數(shù)是最簡(jiǎn)單也是最常用的函數(shù)，函數(shù)表示方式為“=SUM（Number1,Number2,）最多可帶30個(gè)，Number1可發(fā)是數(shù)字，也可以是單元格，還可以是一個(gè)區(qū)域。,單位：億元,常用函數(shù),5,3、SUMIF條件求和函數(shù),公式：SUMIF(range,criteria,sum_range) Range ：為用于條件判斷的單元格區(qū)域。 Criteria ：為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。 Sum_range：是需要求和的實(shí)際單元格,單位：億元,常用函數(shù),6,4、SUMPRODUCT返回?cái)?shù)組元數(shù)對(duì)應(yīng)相乘的和,公式： SUMPRODUCT(array1,array2,array3) array ：為數(shù)組，至少為2個(gè)，最多為30個(gè)。,單位：億元,常用函數(shù),7,5、COUNT數(shù)值計(jì)數(shù)函數(shù),公式：COUNT(Value1, Value2, Value2) value：可以為數(shù)值，也可以為單元格，也可為空字符串，如果單元格為非數(shù)值類型的數(shù)據(jù)將不被統(tǒng)計(jì)，但日期型的數(shù)據(jù)可以被統(tǒng)計(jì)。,單位：億元,常用函數(shù),8,6、COUNTA非空單元格計(jì)數(shù)函數(shù),公式：COUNTA(Value1, Value2, Value2) value：可以為數(shù)值、文本，也可以為單元格引用，不包括空白單元格，但包括空字符（”)。,單位：億元,常用函數(shù),9,7、COUNTBLANK計(jì)數(shù)函數(shù),公式：COUNTBLANK(rang) rang：為一個(gè)區(qū)域，只能有一個(gè)參數(shù)，如果單元為0值將不被統(tǒng)計(jì)，如果單元包括（”）空字符的公式，也將被統(tǒng)計(jì)。,單位：億元,常用函數(shù),10,8、COUNTIF計(jì)數(shù)函數(shù),公式：COUNTIF(rang, creteria) rang：為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。 Criteria: 為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 32、“32“、“32“ 或 “apples“。,單位：億元,常用函數(shù),11,10、Round四舍五入按指定位數(shù)取整的函數(shù),公式：Round(number, num_digits) number：需要四舍五入的數(shù)字。 Num_digits：指定要四舍五入的位數(shù)， Num_digits=0,指定要四舍五入的小數(shù)， Num_digits0,在小數(shù)點(diǎn)左側(cè)指定要四舍五入的位數(shù)。,9、RANK排名函數(shù),公式：RANK(number, REF，ORDER) 表示數(shù)字number在ref中的排序，order等于零或省略表示按升序排序，order等于表示按降序排序,單位：億元,常用函數(shù),12,11、concatenate文本連接函數(shù),公式：concatenate(text1, text2) Text1, text2, . 為 1 到 30 個(gè)將要合并成單個(gè)文本項(xiàng)的文本項(xiàng)。這些文本項(xiàng)可以為文本字符串、數(shù)字或?qū)蝹€(gè)單元格的引用。 也可以用 &（和號(hào)）運(yùn)算符代替函數(shù) CONCATENATE 實(shí)現(xiàn)文本項(xiàng)的合并。,1、TEXT文本轉(zhuǎn)換函數(shù),語(yǔ)法:TEXT(value,format_text) Value 為數(shù)值、計(jì)算結(jié)果為數(shù)字值的公式，或?qū)Π瑪?shù)字值的單元格的引用。 Format_text 為“單元格格式”對(duì)話框中“數(shù)字”選項(xiàng)卡上“分類”框中的文本形式的數(shù)字格式。,單位：億元,常用函數(shù),13,13、lookup查找并返回指定值，,公式：lookup(lookup_value,lookup_vector,result_vector) 表示在LOOKUP_VECTOR中查找LOOKUP_VALUE的值，并返回RESULT_VECTOR中同一位置的值，要求LOOKUP_VECTOR必須按數(shù)字、字母從小到大排序，LOOKUP_VETOR與RESULT_VECTOR只能包含一列或一行的值，且必須大小一致，如果LOOKUP_VALUE少于LOOKUP_VECTOR最少值，則出錯(cuò)，如果LOOKUP_VALUE大于LOOKUP_VECTOR最少值，但找不到相等值，則返回最近值。,單位：億元,常用函數(shù),14,14、Vlookup查找并返回指定值，,公式：Vlookup(lookup_value,table_array,col_index_num,rang_lookup) 表示在table_array中首列查找LOOKUP_VALUE的值，并返回table_array中指定列號(hào)col_index_num中同一位置的值，rang_lookup為邏輯值，省略或?yàn)閠rue，1表示模糊匹配，要求table_array必須按數(shù)字、字母從小到大排序，如rang_lookup的值為0或FALSE則表示精確匹配， table_array無須排序，如找不到則返回#N/A。,15,單位：億元,16,1、FV年金終值,2、PV年金現(xiàn)值,3、PMT求年金,4、NPER年金中周期,7、RATE年金中利率,8、NPV非固定投資現(xiàn)值,9、IRR內(nèi)部收益率,與年金有關(guān)的函數(shù)-目錄,單位：億元,與年金有關(guān)的函數(shù),17,在介紹具體與年金有關(guān)的函數(shù)之前，我們首先來了解一下年金函數(shù)中常見的參數(shù)： 未來值 (fv)-在所有付款發(fā)生后的投資或貸款的價(jià)值。 投資期數(shù) (nper)-為總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)。 付款 (pmt)-對(duì)于一項(xiàng)投資或貸款的定期支付數(shù)額。其數(shù)值在整個(gè)年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費(fèi)用及稅款。 現(xiàn)值 (pv)-在投資期初的投資或貸款的價(jià)值。例如，貸款的現(xiàn)值為所借入的本金數(shù)額。 利率 (rate)-投資或貸款的利率或貼現(xiàn)率。 類型 (type)-付款期間內(nèi)進(jìn)行支付的間隔，如在月初或月末，用0或1表示。 從上面的參數(shù)，我們也引出了5個(gè)年金函數(shù)，分別為：FV、PV、NPER、PMT、RATE，他們分別對(duì)應(yīng)年金公式中的年金終值S，年金現(xiàn)值P，期數(shù)N，年金A，利率I,單位：億元,18,1、年金終值函數(shù)FV,語(yǔ)法形式為FV(rate,nper,pmt,pv,type)， S=A*(（1+i）n-1)/i Rate：利率，指年金公式的i Nper：年金期數(shù)，或總投資期，指年金公式的n Pmt：年金，或者每期投資金額，指年金公式的A，以負(fù)數(shù)表示； PV：為現(xiàn)值，即從該項(xiàng)投資開始計(jì)算時(shí)已經(jīng)入帳的款項(xiàng)，或一系列未來付款的當(dāng)前值的累積和，以負(fù)數(shù)表示。 Type：為數(shù)字0或1，省略為0，數(shù)字0表期末支付，數(shù)字1表示期初支付。,與年金有關(guān)的函數(shù),單位：億元,19,2、年金現(xiàn)值函數(shù)PV,語(yǔ)法形式為PV(rate,nper,pmt,fv,type) P=A*(1-（1+i）-n)/i Rate：利率，指年金公式的i Nper：年金期數(shù)，或總投資期，指年金公式的n Pmt：年金，或者每期投資金額，指年金公式的A，以負(fù)數(shù)表示； FV：為未來值，或最后一次支付后希望得到的現(xiàn)金余額。 Type：為數(shù)字0或1，省略為0，數(shù)字0表期末，數(shù)字1表示期初。,與年金有關(guān)的函數(shù),單位：億元,20,3、每期支付的金額(年金)函數(shù)PMT,語(yǔ)法形式為PMT(rate,nper,pv,fv,type) Rate：利率，指年金公式的i Nper：年金期數(shù)，或總投資期，指年金公式的n PV：為現(xiàn)值，或一系列未來付款的當(dāng)前值的累積和，也稱為本金。 ； FV：為未來值，或最后一次支付后希望得到的現(xiàn)金余額。 Type：為數(shù)字0或1，省略為0，數(shù)字0表期末，數(shù)字1表示期初。 典型應(yīng)用：融資租賃每期租金的確定,與年金有關(guān)的函數(shù),單位：億元,21,4、投資總期數(shù)NPER,語(yǔ)法形式為NPER(rate,nper,pv,fv,type)， Rate：利率。 Npmt：年金為各期所應(yīng)支付的金額，其數(shù)值在整個(gè)年金期間保持不變。 PV：為現(xiàn)值，或一系列未來付款的當(dāng)前值的累積和，也稱為本金。 FV：為未來值，或最后一次支付后希望得到的現(xiàn)金余額。 Type：為數(shù)字0或1，省略為0，數(shù)字0表期末，數(shù)字1表示期初。,與年金有關(guān)的函數(shù),單位：億元,財(cái)務(wù)函數(shù),22,5、年金利率RATE,語(yǔ)法形式為RATE(nper,npmt,pv,fv,type,guess)， Nper：總投資期，即該項(xiàng)目投資付款期總和。 Npmt：各期所支付的金額，其數(shù)值在整個(gè)期間保持不變。 PV：為現(xiàn)值，或一系列未來付款的當(dāng)前值的累積和，也稱為本金。 FV：為未來值，或最后一次支付后希望得到的現(xiàn)金余額。 Type：為數(shù)字0或1，省略為0，數(shù)字0表期末，數(shù)字1表示期初。,單位：億元,23,6、投資凈現(xiàn)值NPV,語(yǔ)法形式為NPV(rate,value1,value2,)， Rate：利率， value：未來一系列的收入（收入）與支出（負(fù)值），為1到29個(gè)參數(shù)。 注：函數(shù) NPV 假定投資開始于 value1 現(xiàn)金流所在日期的前一期，并結(jié)束于最后一筆現(xiàn)金流的當(dāng)期。函數(shù) NPV 依據(jù)未來的現(xiàn)金流來進(jìn)行計(jì)算。如果第一筆現(xiàn)金流發(fā)生在第一個(gè)周期的期初，則第一筆現(xiàn)金必須添加到 函數(shù) NPV 的結(jié)果中，而不應(yīng)包含在 values 參數(shù)中。,與年金有關(guān)的函數(shù),單位：億元,24,7、內(nèi)部收益率IRR和XIRR,IRR語(yǔ)法 ：(Values, guess) Values：為數(shù)組或單元格的引用，包含用來計(jì)算返回的內(nèi)部收益率的數(shù)字，必須包括一個(gè)負(fù)數(shù)和正數(shù)。 Guess: 為對(duì)函數(shù) IRR 計(jì)算結(jié)果的估計(jì)值。Microsoft Excel 使用迭代法計(jì)算函數(shù) IRR。,與年金有關(guān)的函數(shù),XIRR語(yǔ)法： (Values, dates，guess)，表示一組不固定日期收益的內(nèi)部收益率，與IRR的區(qū)別在于：IRR的收益均假設(shè)在期末，而XIRR的收益可以一年中的任何一天，收益的時(shí)間無規(guī)率性。,25,單位：億元,26,1、ACCRINTM到期債 應(yīng)計(jì)利息,2、CUMPRINC等額 償還貸款中本金,3、DISC貼現(xiàn)率,4、PRICEDISC貼現(xiàn)價(jià)格,5、EFFECT實(shí)際利率,6、MOMINAL名義利率,與債券、票據(jù)有關(guān)的函數(shù) 目錄,單位：億元,27,1、求到期付息有價(jià)證券的應(yīng)計(jì)利息的函數(shù)ACCRINTM,語(yǔ)法：ACCRINT(issue，maturity ，rate，par，basis) 其中issue為有價(jià)證券的發(fā)行日，maturity為有價(jià)證券的到期日，settlement為有價(jià)證券的成交日，即在發(fā)行日之后，有價(jià)證券賣給購(gòu)買者的日期，rate為有價(jià)證券的年息票利率，par為有價(jià)證券的票面價(jià)值，如果省略par，函數(shù)ACCRINTM就會(huì)自動(dòng)將par設(shè)置為￥1000，basis為日計(jì)數(shù)基準(zhǔn)類型。,與債券、票據(jù)有關(guān)的函數(shù),單位：億元,28,2、計(jì)算等額還本付中償還本金數(shù)額CUMPRINC,語(yǔ)法：CUMPRINC(rate，nper，pv,start_period，end_period，type) 。其中rate為利率，nper為總付款期數(shù)，pv為現(xiàn)值，start_period為計(jì)算中的首期，付款期數(shù)從1開始計(jì)數(shù)，end_period為計(jì)算中的末期，type為付款時(shí)間類型。 典型應(yīng)用：融資租賃未確認(rèn)融資收益的計(jì)算。 與此函數(shù)功能相同的函數(shù)為PPMT（RATE，PER，NPER，PV，F(xiàn)V，TYPE），求償還的利息函數(shù)為IPMT（ RATE，PER，NPER，PV，F(xiàn)V，TYPE ），區(qū)別就是PPMT只能計(jì)算某一期間償還的本金，而CUMPRNINC可以計(jì)算一連續(xù)期間償還本金之和。,與債券、票據(jù)有關(guān)的函數(shù),單位：億元,29,3、有價(jià)證券的貼現(xiàn)率DISC,其語(yǔ)法形式為： DISC(settlement,maturity,pr,redemption,basis) 其中settlement為有價(jià)證券的成交日，即在發(fā)行日之后，有價(jià)證券賣給購(gòu)買者的日期，maturity為有價(jià)證券的到日期，到期日是有價(jià)證券有效期截止時(shí)的日期，pr為面值為“￥100“的有價(jià)證券的價(jià)格，redemption為面值為“￥100“的有價(jià)證券的清償價(jià)格，basis為日計(jì)數(shù)基準(zhǔn)類型。,與債券、票據(jù)有關(guān)的函數(shù),單位：億元,30,4、有價(jià)證券的貼現(xiàn)價(jià)格PRICEDISC,其語(yǔ)法形式為： DISC(settlement,maturity,discount,redemption,basis) 其中settlement為有價(jià)證券的成交日，即在發(fā)行日之后，有價(jià)證券賣給購(gòu)買者的日期，maturity為有價(jià)證券的到日期，到期日是有價(jià)證券有效期截止時(shí)的日期，discount有價(jià)證券的貼現(xiàn)率，redemption為有價(jià)證券的清償價(jià)格，basis為日計(jì)數(shù)基準(zhǔn)類型。,與債券、票據(jù)有關(guān)的函數(shù),單位：億元,31,5、計(jì)算實(shí)際利率函數(shù)EFFECT,其語(yǔ)法形式為：EFFECT(nominal_rate,npery) 表示以名義利率為nominal_rate,復(fù)利npery期的實(shí)際利率，計(jì)算公式為（1+ nominal_rate/ npery） npery-1,與債券、票據(jù)有關(guān)的函數(shù)