[摘 要] Excel提供了強(qiáng)大的數(shù)據(jù)處理功能，特別是內(nèi)置的工作表函數(shù)，在財(cái)務(wù)模型中巧妙靈活地運(yùn)用這些函數(shù)，會大大提高財(cái)務(wù)人員的工作效率#65377;在眾多的工作表函數(shù)當(dāng)中，某些工作表函數(shù)如IF函數(shù)的使用頻率是相當(dāng)高的，財(cái)務(wù)人員若能在掌握其他工作表函數(shù)之前熟練運(yùn)用IF函數(shù)，將有利于快速掌握更多的工作表函數(shù)#65377;本文主要圍繞IF函數(shù)探討財(cái)務(wù)人員應(yīng)該如何在財(cái)務(wù)模型中使用函數(shù)#65377;

[關(guān)鍵詞] Excel;IF函數(shù);函數(shù)嵌套;財(cái)務(wù)管理

[中圖分類號]F232;F275[文獻(xiàn)標(biāo)識碼]A[文章編號]1673-0194(2007)11-0068-05

一#65380;IF函數(shù)說明

IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測試的真假值返回不同的結(jié)果，因此也被稱為條件函數(shù)#65377;它的應(yīng)用很廣泛，可以使用 IF 函數(shù)對數(shù)值和公式進(jìn)行條件檢測#65377;

它的語法為IF(logical_test，value_if_true，value_if_

1)#65377;其中Logical_test表示計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式#65377;本參數(shù)可使用任何比較運(yùn)算符#65377;Value_if_true顯示在logical_test 為TRUE時(shí)返回的值，Value_if_true也可以是其他公式#65377;Value_if_1顯示在logical_test為FALSE時(shí)返回的值，Value_if_1也可以是其他公式#65377;

簡言之，如果第一個(gè)參數(shù)logical_test返回的結(jié)果為真的話，則執(zhí)行第二個(gè)參數(shù)Value_if_true的結(jié)果，否則執(zhí)行第三個(gè)參數(shù)Value_if_1的結(jié)果#65377;

二#65380;IF函數(shù)的基本應(yīng)用

1. 不同的條件返回不同的結(jié)果

從上述函數(shù)說明中不難看出，IF函數(shù)可以應(yīng)用到非常廣泛的領(lǐng)域，所以它的使用頻率相當(dāng)高#65377;比如，在成績表中根據(jù)不同的成績區(qū)分合格與不合格#65377;現(xiàn)在我們就以某班級的課程成績?yōu)槔唧w說明用法#65377;

某班級的成績?nèi)鐖D1所示，為了做出最終的綜合評定，我們設(shè)定按照平均分判斷該學(xué)生成績是否合格的規(guī)則#65377;如果各科平均分超過60分則認(rèn)為是合格的，否則記作不合格#65377;

根據(jù)這一規(guī)則，我們在綜合評定中寫公式(以單元格B12為例):

=IF(B11>60，“合格”，“不合格”)

語法解釋為，如果單元格B11的值大于60，則執(zhí)行第二個(gè)參數(shù)，即在單元格B12中顯示合格字樣;否則執(zhí)行第三個(gè)參數(shù)，即在單元格B12中顯示不合格字樣#65377;

在綜合評定欄中可以看到由于C列的同學(xué)各科平均分為54分，綜合評定為不合格#65377;其余均為合格#65377;

2. IF函數(shù)嵌套的應(yīng)用

在引用函數(shù)時(shí)，函數(shù)的參數(shù)又引用了函數(shù)，稱為“函數(shù)的嵌套”#65377;IF函數(shù)可以嵌套7層，用 value_if_1 及 value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測條件#65377;

在上述的例子中，我們只是將成績簡單區(qū)分為合格與不合格，在實(shí)際應(yīng)用中，成績通常是有多個(gè)等級的，比如優(yōu)#65380;良#65380;中#65380;及格#65380;不及格等#65377;有辦法一次性區(qū)分嗎?可以使用多層嵌套的辦法來實(shí)現(xiàn)#65377;仍以上例為例，我們設(shè)定綜合評定的規(guī)則為當(dāng)各科平均分超過90時(shí)，評定為優(yōu)秀#65377;如圖2所示#65377;

說明:為了解釋起來比較方便，我們在這里僅做兩重嵌套的示例，用戶可以按照實(shí)際情況進(jìn)行更多重的嵌套，但需要注意Excel的IF函數(shù)最多允許七重嵌套#65377;

根據(jù)這一規(guī)則，我們在綜合評定中寫公式(以單元格F12為例):

=IF(F11>60，IF(F11>90，“優(yōu)秀”，“合格”)，“不合格”)

語法解釋為，如果單元格F11的值大于60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格F11的值是否大于90，如果滿足在單元格F12中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執(zhí)行第三個(gè)參數(shù)即在單元格F12中顯示不合格字樣#65377;

在綜合評定欄中可以看到由于F列的同學(xué)各科平均分為92分，綜合評定為優(yōu)秀#65377;

三#65380;IF函數(shù)嵌套在計(jì)算機(jī)財(cái)務(wù)管理中的應(yīng)用

1. 函數(shù)嵌套在在籌資決策模型中的應(yīng)用

在借款或融資租賃中，籌資方經(jīng)常遇到等額分期付款問題#65377;在借款分析表或融資租賃分析表中有兩個(gè)問題需要解決:一是表格行標(biāo)題區(qū)域的行數(shù)設(shè)定，二是表格內(nèi)有數(shù)據(jù)行與無數(shù)據(jù)行的自動區(qū)分問題#65377;

對于第一個(gè)問題，由于一年內(nèi)的計(jì)息次數(shù)往往不止一次，這樣以來，在編制借款分析表時(shí)，要考慮在整個(gè)借貸期內(nèi)總的計(jì)息次數(shù)問題，對Excel來講就是表格行標(biāo)題區(qū)域的行數(shù)設(shè)定問題#65377;由于總付款期數(shù)=年數(shù)×一年內(nèi)付款次數(shù)，并且我們注意到，用戶往往偏好于通過微調(diào)按鈕或滾動條為借款設(shè)置年數(shù)與一年內(nèi)付款次數(shù)#65377;所以，根據(jù)上述窗體中所設(shè)置的最大值就能確定最大行數(shù)#65377;具體做法就是在表格的行標(biāo)題區(qū)域填入付款期次0，1，2，3，…，n(n為總付款期數(shù))#65377;其中由于每次可能在期初付款，因此務(wù)必考慮0期，即第一期的期初，表內(nèi)的期次應(yīng)該從0開始而不是從1開始#65377;

雖然解決了行數(shù)不足的問題，但是又帶來另外一個(gè)新問題，即某些期次對應(yīng)行應(yīng)該執(zhí)行相關(guān)的數(shù)據(jù)計(jì)算，其余期次對應(yīng)的行不執(zhí)行任何計(jì)算#65377;這里仍可用IF函數(shù)的嵌套來處理#65377;由于存在付款在期末或期初的問題，所以該函數(shù)的條件參數(shù)不得不把該問題考慮在內(nèi)#65377;如果是在期初付款，那么必然從期次為0對應(yīng)的行開始執(zhí)行計(jì)算，同時(shí)最后付款的期次=總付款期數(shù)-1，只有這樣的行次才能夠自動執(zhí)行相關(guān)計(jì)算#65377;同理，如果是在期末付款，那么必然從期次為1對應(yīng)的行開始執(zhí)行計(jì)算，最后付款期次=總付款期數(shù)，只有這樣的行次才能執(zhí)行相關(guān)計(jì)算#65377;除此之外的行次均不執(zhí)行任何計(jì)算#65377;具體實(shí)例見圖3#65377;

現(xiàn)假設(shè)編制某公司租賃現(xiàn)金流量分析表，行標(biāo)題區(qū)域位于A列，每期租金支付位于B列#65377;當(dāng)前行為第23行，同時(shí)該工作表內(nèi)存在已定義的名稱“支付租金方法#65380;租賃年利率#65380;每年付款次數(shù)#65380;總付款次數(shù)#65380;租金”#65377;

(1)行標(biāo)題區(qū)域的編制#65377;簡單的做法是利用數(shù)據(jù)填充，將全部可能的期次一次性填入，但在大多數(shù)情況下許多期次始終沒必要顯示，應(yīng)該暫時(shí)隱藏這些多余的期次#65377;而必須顯示的期次受到付款方式與總付款期數(shù)兩個(gè)因素的影響，因此必須把這種影響體現(xiàn)在公式當(dāng)中，有選擇地顯示行標(biāo)題區(qū)域的期次#65377;具體來講，有兩個(gè)方面要考慮，一是為避免可能出現(xiàn)的系統(tǒng)錯(cuò)誤提示，期次超越總付款期數(shù)范圍的單元格要顯示空白;二是根據(jù)先付或后付的付款方式確定允許顯示期次的單元格，若是先付則是期次小于總付款期數(shù)的單元格，若是后付則是期次不超過總付款期數(shù)的單元格，其余單元格全部顯示空白#65377;如A16單元格位于表體的第一行，期次為0，為保證付款方式為后付時(shí)不顯示期次，其公式應(yīng)為

=IF(支付租金方法=“先付”，0，“”)

A17單元格位于表體的第二行，因?yàn)榉制诟犊畹奶匦詻Q定了無論何種情況均須計(jì)算該期支付的租金，所以直接輸入常數(shù)1作為期次即可#65377;A17以下的單元格公式相似，利用填充柄在該區(qū)域復(fù)制公式即可#65377;如A23單元格的公式為

=IF(A22=“”，“”，IF(支付租金方法=“先付”，IF(A22+1<每年付款次數(shù)*租賃年限，A22+1，“”)，IF(A22+1<=每年付款次數(shù)*租賃年限，A22+1，“”)))

該公式首先判斷當(dāng)前單元格的期次是否超出了總付款期數(shù)，由于單元格A23對應(yīng)期次為7，總付款期數(shù)為2*4=8，又知付款方式為先付，這里期次7<總付款期數(shù)8，滿足顯示期次的條件，因此執(zhí)行A22+1的命令，在A23單元格內(nèi)顯示7#65377;

(2)“租金支付”一欄的編制#65377;該欄所用IF函數(shù)的Logical_test參數(shù)需要包含較為復(fù)雜的表達(dá)式，其中既要考慮付款是在期初還是期末，又要考慮在前述付款方式下本行是否要執(zhí)行并顯示計(jì)算結(jié)果，具備這樣功能的函數(shù)就是另外一個(gè)邏輯函數(shù)AND函數(shù)，它能夠同時(shí)考慮這兩層要求#65377;因?yàn)槠渲饕δ芫褪欠祷剡壿嬛担绻袇?shù)值均為邏輯“真(TRUE)”，則返回邏輯“真(TRUE)”，反之返回邏輯“假(FALSE)”#65377;則表內(nèi)該行“每期租金支付”對應(yīng)的單元格B23的公式為

=IF(and(支付租金方法=“先付”，A23<總付款次數(shù))，ABS(PMT(租賃年利率/每年付款次數(shù)，總付款次數(shù)，租金，0，1))，IF(AND(支付租金方法=“后付”，A23>=1，A23<=總付款次數(shù))，ABS(PMT(租賃年利率/每年付款次數(shù)，總付款次數(shù)，租金))，0))

在本例中，第23行對應(yīng)第7期，總付款期數(shù)=2*5=10，租金支付方法是后付，由于B23同時(shí)滿足支付租金方法=“后付”與1≤A23≤10兩個(gè)條件，因此B23就執(zhí)行命令A(yù)BS(PMT(租賃年利率/每年付款次數(shù)，總付款次數(shù)，租金))#65377;

2. IF函數(shù)嵌套在投資決策模型中的應(yīng)用

在固定資產(chǎn)投資決策模型當(dāng)中，用戶需要獲取不同的折舊方法(雙倍余額遞減法#65380;直線法以及年數(shù)總和法)對現(xiàn)金流量影響的信息#65377;按照不同的折舊方法算出的折舊額全部顯示在固定資產(chǎn)現(xiàn)金流量表中固然可行，但此舉必然導(dǎo)致表格體積增大，而且更為重要的是表格內(nèi)容的可讀性大大降低#65377;根據(jù)IF函數(shù)的特點(diǎn)，恰當(dāng)?shù)厥褂迷摵瘮?shù)既可保持原表格結(jié)構(gòu)不變，又可擴(kuò)充表格內(nèi)容#65377;例如在第24行中，B24∶F24區(qū)域顯示各期所提取的折舊額#65377;現(xiàn)在考慮如何實(shí)現(xiàn)以下目標(biāo):用戶能夠快捷地指定任一折舊方法，現(xiàn)金流量表在結(jié)構(gòu)保持不變的情況下立即顯示新的折舊額#65377;

(1)控制面板的制作#65377;首先為折舊方法的選擇創(chuàng)建人機(jī)交互的圖形界面#65377;在該區(qū)域右方如I24#65380;I25和I26三個(gè)單元格分別輸入“直線法”#65380;“雙倍余額遞減法”以及“年數(shù)總和法”#65377;然后在該區(qū)域右邊臨近位置如H23建立組合框窗體，其指定數(shù)據(jù)源區(qū)域指向I24∶I26;將單元格鏈接指向窗體附近的單元格如H24，用來儲存用戶在組合框窗體中所選項(xiàng)目的位置#65377;這樣以來，就在用戶選擇的折舊方法與單元格H24之間建立了動態(tài)鏈接#65377;

(2)折舊函數(shù)的自動切換#65377;在現(xiàn)金流量表內(nèi)的B24∶F24區(qū)域中輸入公式，該公式包含著SLN函數(shù)#65380;VDB函數(shù)和SYD函數(shù)#65377;若這3個(gè)折舊函數(shù)直接和用戶選擇的折舊方法建立動態(tài)鏈接，難度相當(dāng)大，為了降低難度，我們應(yīng)該利用用戶選擇的折舊方法與單元格H24之間的動態(tài)鏈接，如果將單元格H24作為橋梁，能夠在單元格H24與折舊函數(shù)之間建立起動態(tài)鏈接，就可以間接實(shí)現(xiàn)我們的目的了#65377;由于單元格H24可能的結(jié)果只有1#65380;2和3，而折舊方法也是3種，二者是一一對應(yīng)的，仍然可以通過IF函數(shù)來實(shí)現(xiàn)，只需要合理設(shè)計(jì)IF函數(shù)的Logical_test參數(shù)即可#65377;需要注意的是，由于H24在現(xiàn)金流量表外，其引用方式應(yīng)該是$H$24，因?yàn)橹挥羞@樣才能保證其地址不隨公式的復(fù)制而改變#65377;至于Value_if_true與Value_if_1參數(shù)，僅僅是3個(gè)折舊函數(shù)#65377;詳見圖4 ~ 圖6#65377;

至此，在用戶在窗體內(nèi)選擇的折舊方法與折舊函數(shù)之間建立了動態(tài)鏈接，實(shí)現(xiàn)了現(xiàn)金流量表中折舊函數(shù)的自動切換#65377;

3. 多層嵌套函數(shù)在固定資產(chǎn)折舊額計(jì)算中的應(yīng)用

Excel提供了DDB與VDB函數(shù)來計(jì)算雙倍余額遞減法下的折舊額，尤其是VDB函數(shù)，能夠?qū)崿F(xiàn)自動向直線法轉(zhuǎn)換的功能，因此，該函數(shù)更適合用來計(jì)算折舊額#65377;但是隨著固定資產(chǎn)原始價(jià)值#65380;殘值與使用年限的變動，當(dāng)三者之間的關(guān)系滿足一定條件時(shí)，如果仍然使用VDB函數(shù)，就會遇到提前折舊的反常現(xiàn)象#65377;而按照現(xiàn)行會計(jì)準(zhǔn)則的規(guī)定，正常情況下是不可能在固定資產(chǎn)使用期滿前提完成折舊的#65377;這實(shí)際上是VDB函數(shù)未能全面考慮我國會計(jì)制度的緣故#65377;為了避免這種情況的發(fā)生，離開VDB函數(shù)是不可行的，因此必須既要繼續(xù)使用該函數(shù)，又要排除提前提完成折舊的情形#65377;IF函數(shù)的嵌套就可以滿足要求，見圖7#65377;

這里假設(shè)固定資產(chǎn)原始價(jià)值為20 000元，其使用年限為5年，當(dāng)利用滾動條調(diào)整其殘值大小至3 000元時(shí)，僅僅依靠VDB函數(shù)就會導(dǎo)致第5年無折舊可提，原因是VDB函數(shù)在第4年就把剩余可提取的1 320元全部提取了#65377;但是根據(jù)現(xiàn)行會計(jì)制度的規(guī)定，不允許出現(xiàn)此種提前提完折舊的情況，合理的做法是把1 320元平均分?jǐn)偟绞Ｓ?年內(nèi)，即第4年和第5年分別提取1 320/2=660元#65377;這里暫將考慮提前提完成折舊因素的VDB函數(shù)為修正VDB函數(shù)#65377;

如第1年在第6行，該年的修正VDB函數(shù)公式為

=IF(VDB(原始成本，殘值，使用年限，A6-1，A6)=0，D5，IF(A6<使用年限，IF(VDB(原始成本，殘值，使用年限，A6，A6+1)>0，VDB(原始成本，殘值，使用年限，A6-1，A6)，SLN(VDB(原始成本，殘值，使用年限，A6-1，A6)，0，使用年限-A6+1))，VDB(原始成本，殘值，使用年限，A6-1，A6)))

從左至右解釋該公式，首先判斷滿足條件VDB(原始成本，殘值，使用年限，A6-1，A6)>0，則執(zhí)行第2個(gè)IF函數(shù)，然后判斷滿足條件A6<使用年限，執(zhí)行第3個(gè)IF函數(shù)，再判斷仍然滿足條件VDB(原始成本，殘值，使用年限，A6，A6+1)>0，所以最終執(zhí)行VDB(原始成本，殘值，使用年限，A6-1，A6)，即在期滿前的非零單元格中，由于其下一年的單元格仍然非零，所以仍然使用原始的VDB函數(shù)，結(jié)果是8 000元#65377;

如第4年在第9行，該年的修正VDB函數(shù)公式為

=IF(VDB(原始成本，殘值，使用年限，A9-1，A9)=0，D8，IF(A9<使用年限，IF(VDB(原始成本，殘值，使用年限，A9，A9+1)>0，VDB(原始成本，殘值，使用年限，A9-1，A9)，SLN(VDB(原始成本，殘值，使用年限，A9-1，A9)，0，使用年限-A9+1))，VDB(原始成本，殘值，使用年限，A9-1，A9)))

從左至右解釋該公式，首先判斷滿足條件VDB(原始成本，殘值，使用年限，A6-1，A6)>0，則執(zhí)行第2個(gè)IF函數(shù)，然后判斷滿足條件A6<使用年限，執(zhí)行第3個(gè)IF函數(shù)，再判斷不再滿足條件VDB(原始成本，殘值，使用年限，A6，A6+1)>0，所以最終執(zhí)行SLN(VDB(原始成本，殘值，使用年限，A9-1，A9)，0，使用年限-A9+1)，即在期滿前的非零單元格中，由于其下一年的單元格已經(jīng)是零，所以轉(zhuǎn)換使用SLN函數(shù)，結(jié)果是660元#65377;

如第5年在第10行，該年的修正VDB函數(shù)公式為

=IF(VDB(原始成本，殘值，使用年限，A10-1，A10)=0，D9，IF(A10<使用年限，IF(VDB(原始成本，殘值，使用年限，A10，A10+1)>0，VDB(原始成本，殘值，使用年限，A10-1，A10)，SLN(VDB(原始成本，殘值，使用年限，A10-1，A10)，0，使用年限-A10+1))，VDB(原始成本，殘值，使用年限，A10-1，A10)))

從左至右解釋該公式，首先判斷不滿足條件VDB(原始成本，殘值，使用年限，A6-1，A6)>0，則顯示上一年的折舊額即D9，即期滿時(shí)的單元格已經(jīng)是零，所以必然在期滿之前就已經(jīng)轉(zhuǎn)換使用直線法了，結(jié)果是660元#65377;

上述方法的缺點(diǎn)是包含工作表函數(shù)的公式比較復(fù)雜，且容易出錯(cuò)，所以另一個(gè)方法就是使用自定義函數(shù)#65377;具體來講就是利用VB語言構(gòu)造自定義函數(shù)，如果函數(shù)名定為VDB2，那么函數(shù)結(jié)構(gòu)如下:

VDB2 (cost， salvage， life， period)

該函數(shù)的倍率已固定為2，自動轉(zhuǎn)換功能始終是開啟的，其中period參數(shù)表示某一期折舊額的期次，其他參數(shù)的含義同VDB函數(shù)#65377;函數(shù)代碼如下:

Function VDB2(cost， salvage， life， period)

With Application.WorksheetFunction

If .Vdb(cost， salvage， life， period - 1， period) = 0 Then

VDB2 = VDB2(cost， salvage， life， period - 1)

ElseIf period < life Then

If .Vdb(cost， salvage， life， period - 1 + 1， period + 1) > 0 Then

VDB2 = .Vdb(cost， salvage， life， period - 1， period)

Else

VDB2 = .SLN(.Vdb(cost， salvage， life， period - 1， period)， 0， life - (period - 1))

End If

Else

VDB2 = .Vdb(cost， salvage， life， period - 1， period)

End If

End With

End Function

用戶只需在相應(yīng)單元格輸入下面的簡單公式即可得到各期的折舊額:

= VDB2(cost， salvage， life， period)

四#65380;注意事項(xiàng)

1. 善于查找公式錯(cuò)誤

對于帶有嵌套函數(shù)的復(fù)雜公式來說，一旦出現(xiàn)錯(cuò)誤往往很難追蹤#65377;Excel XP專門為此增加了一個(gè)“公式求值”功能，只要選中公式所在的單元格，單擊工具欄中的“公式求值”按鈕(沒有的話可以打開“自定義”對話框中的“命令”選項(xiàng)卡，選擇“類別”列表中的“工具”項(xiàng)，將右邊的“公式求值”按鈕拖到工具欄)，就可以單擊對話框中的“求值”按鈕，按照公式的執(zhí)行順序一步步觀察帶下劃線的表達(dá)式的運(yùn)算結(jié)果，從而找到錯(cuò)誤的根源#65377;這個(gè)功能有點(diǎn)像程序調(diào)試時(shí)的“單步執(zhí)行”，是查找公式錯(cuò)誤的有力武器#65377;

2. 增強(qiáng)函數(shù)嵌套的可讀性和簡潔性

函數(shù)嵌套雖然能解決許多復(fù)雜的財(cái)務(wù)管理問題，但是不能濫用，否則就會使公式越來越復(fù)雜，降低財(cái)務(wù)人員的工作效率，而且財(cái)務(wù)人員很難快速看出公式的意圖，更不便于公式的排錯(cuò)，所以要盡量增強(qiáng)嵌套函數(shù)的可讀性和簡潔性#65377;例如設(shè)矩形長為X，寬為Y，對角線長度為Z，可以看出，要求出Z，可在SQRT 函數(shù)內(nèi)嵌套POWER函數(shù)，構(gòu)造的公式為:

Z=SQRT(POWER(X，2)+ POWER(Y，2))

但是這樣做可能導(dǎo)致運(yùn)算速度較慢，可設(shè)法更換為簡便的計(jì)算方法，將SUMSQ函數(shù)作為被嵌套函數(shù)，重新構(gòu)造公式為:

Z=SQRT(SUMSQ(X，Y))

從而更加快速地計(jì)算出矩形對角線長度#65377;