函數嵌套在計算機財務管理中的應用

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

[關鍵詞] Excel;IF函數;函數嵌套;財務管理

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

一#65380;IF函數說明

IF函數用于執行真假值判斷后，根據邏輯測試的真假值返回不同的結果，因此也被稱為條件函數#65377;它的應用很廣泛，可以使用 IF 函數對數值和公式進行條件檢測#65377;

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

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

簡言之，如果第一個參數logical_test返回的結果為真的話，則執行第二個參數Value_if_true的結果，否則執行第三個參數Value_if_1的結果#65377;

二#65380;IF函數的基本應用

1. 不同的條件返回不同的結果

從上述函數說明中不難看出，IF函數可以應用到非常廣泛的領域，所以它的使用頻率相當高#65377;比如，在成績表中根據不同的成績區分合格與不合格#65377;現在我們就以某班級的課程成績為例具體說明用法#65377;

某班級的成績如圖1所示，為了做出最終的綜合評定，我們設定按照平均分判斷該學生成績是否合格的規則#65377;如果各科平均分超過60分則認為是合格的，否則記作不合格#65377;

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

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

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

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

2. IF函數嵌套的應用

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

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

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

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

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

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

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

三#65380;IF函數嵌套在計算機財務管理中的應用

1. 函數嵌套在在籌資決策模型中的應用

在借款或融資租賃中，籌資方經常遇到等額分期付款問題#65377;在借款分析表或融資租賃分析表中有兩個問題需要解決:一是表格行標題區域的行數設定，二是表格內有數據行與無數據行的自動區分問題#65377;

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

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

現假設編制某公司租賃現金流量分析表，行標題區域位于A列，每期租金支付位于B列#65377;當前行為第23行，同時該工作表內存在已定義的名稱“支付租金方法#65380;租賃年利率#65380;每年付款次數#65380;總付款次數#65380;租金”#65377;

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

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

A17單元格位于表體的第二行，因為分期付款的特性決定了無論何種情況均須計算該期支付的租金，所以直接輸入常數1作為期次即可#65377;A17以下的單元格公式相似，利用填充柄在該區域復制公式即可#65377;如A23單元格的公式為

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

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

(2)“租金支付”一欄的編制#65377;該欄所用IF函數的Logical_test參數需要包含較為復雜的表達式，其中既要考慮付款是在期初還是期末，又要考慮在前述付款方式下本行是否要執行并顯示計算結果，具備這樣功能的函數就是另外一個邏輯函數AND函數，它能夠同時考慮這兩層要求#65377;因為其主要功能就是返回邏輯值，如果所有參數值均為邏輯“真(TRUE)”，則返回邏輯“真(TRUE)”，反之返回邏輯“假(FALSE)”#65377;則表內該行“每期租金支付”對應的單元格B23的公式為

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

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

2. IF函數嵌套在投資決策模型中的應用

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

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

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

至此，在用戶在窗體內選擇的折舊方法與折舊函數之間建立了動態鏈接，實現了現金流量表中折舊函數的自動切換#65377;

3. 多層嵌套函數在固定資產折舊額計算中的應用

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

這里假設固定資產原始價值為20 000元，其使用年限為5年，當利用滾動條調整其殘值大小至3 000元時，僅僅依靠VDB函數就會導致第5年無折舊可提，原因是VDB函數在第4年就把剩余可提取的1 320元全部提取了#65377;但是根據現行會計制度的規定，不允許出現此種提前提完折舊的情況，合理的做法是把1 320元平均分攤到剩余2年內，即第4年和第5年分別提取1 320/2=660元#65377;這里暫將考慮提前提完成折舊因素的VDB函數為修正VDB函數#65377;

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

=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，則執行第2個IF函數，然后判斷滿足條件A6<使用年限，執行第3個IF函數，再判斷仍然滿足條件VDB(原始成本，殘值，使用年限，A6，A6+1)>0，所以最終執行VDB(原始成本，殘值，使用年限，A6-1，A6)，即在期滿前的非零單元格中，由于其下一年的單元格仍然非零，所以仍然使用原始的VDB函數，結果是8 000元#65377;

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

=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，則執行第2個IF函數，然后判斷滿足條件A6<使用年限，執行第3個IF函數，再判斷不再滿足條件VDB(原始成本，殘值，使用年限，A6，A6+1)>0，所以最終執行SLN(VDB(原始成本，殘值，使用年限，A9-1，A9)，0，使用年限-A9+1)，即在期滿前的非零單元格中，由于其下一年的單元格已經是零，所以轉換使用SLN函數，結果是660元#65377;

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

=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，即期滿時的單元格已經是零，所以必然在期滿之前就已經轉換使用直線法了，結果是660元#65377;

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

VDB2 (cost， salvage， life， period)

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

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

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

= VDB2(cost， salvage， life， period)

四#65380;注意事項

1. 善于查找公式錯誤

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

2. 增強函數嵌套的可讀性和簡潔性

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

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

但是這樣做可能導致運算速度較慢，可設法更換為簡便的計算方法，將SUMSQ函數作為被嵌套函數，重新構造公式為:

Z=SQRT(SUMSQ(X，Y))

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