楊桂梅

摘要:在信息化的時(shí)代，人們?cè)絹?lái)越意識(shí)到利用信息化工具在提高工作效率、創(chuàng)造更大效益等方面所發(fā)揮的重要作用。本文寫(xiě)作的目的僅是拋磚引玉，希望越來(lái)越多的人們都能利用現(xiàn)代化的工具為其所用，更好地工作和生活。

關(guān)鍵詞:Excel的兩種“模擬運(yùn)算表”;長(zhǎng)期貸款分析;應(yīng)用

目前，無(wú)論是企業(yè)還是個(gè)人都有可能需要長(zhǎng)期貸款。進(jìn)行長(zhǎng)期貸款理性的分析是必要的。長(zhǎng)期貸款分析主要是對(duì)貸款的金額、貸款的利率、貸款的期限和歸還期限等因素進(jìn)行分析。我們經(jīng)常要觀察某一個(gè)因素變動(dòng)或某兩個(gè)因素的變動(dòng)對(duì)其他因素的影響，Excel就提供了“模擬運(yùn)算表”工具來(lái)解決這類(lèi)問(wèn)題。

“模擬運(yùn)算表”就是將工作表中的一個(gè)單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬計(jì)算，測(cè)試使用一個(gè)或兩個(gè)變量對(duì)運(yùn)算結(jié)果的影響。在Excel中，可以構(gòu)造兩種模擬運(yùn)算表：?jiǎn)巫兞磕M運(yùn)算表和多變量模擬運(yùn)算表。

一、單變量模擬運(yùn)算表

單變量模擬運(yùn)算表就是基于輸入一個(gè)變量，用它來(lái)測(cè)試對(duì)公式計(jì)算結(jié)果的影響。

創(chuàng)建單變量模擬運(yùn)算表，所設(shè)計(jì)的運(yùn)算表應(yīng)當(dāng)是：其輸入數(shù)值被排列在一列中或一行中。使用的公式必須引用輸入單元格（輸入單元格：在該單元格中，源于數(shù)據(jù)表的輸入值將被替換。工作表中的任何單元格都可用作輸入單元格。盡管輸入單元格不必是數(shù)據(jù)表的一部分，但是數(shù)據(jù)表中的公式必須引用輸入單元格）。如果數(shù)據(jù)表是列方向的，在第一個(gè)數(shù)值的上一行且位于數(shù)值列右邊的單元格中鍵入公式。如果數(shù)據(jù)表是行方向的，在第一個(gè)數(shù)值左邊一列且位于數(shù)值行下方的單元格中鍵入公式。

例1：某企業(yè)向銀行貸款10000元，期限5年，用“模擬運(yùn)算表”工具來(lái)測(cè)試不同的利率對(duì)月還款額的影響，步驟如下：

1.設(shè)計(jì)單變量模擬運(yùn)算表結(jié)構(gòu)，利率設(shè)置為列變量，所以設(shè)置的公式單元格B7是位于變量值所在列的右側(cè)，并且高于第一個(gè)變量單元格A8一行。左側(cè)單元格A7無(wú)任何作用。如圖1所示。

2.在公式單元格B7中計(jì)算利率為4%時(shí)月還款額，輸入公式“B7=PMT(B5/12,B4,B1)”。

3.選取包括公式和需要進(jìn)行模擬運(yùn)算的單元格區(qū)域A7:B16。

4.單擊“數(shù)據(jù)”菜單，選擇“模擬運(yùn)算表”項(xiàng)，彈出“模擬運(yùn)算表”對(duì)話框，如圖2所示。

5.由于本例中引用的是列數(shù)據(jù)，故在“輸入引用列的單元格”中輸入“$B$5”。單擊“確定”按鈕，即得到單變量模擬運(yùn)算結(jié)果表，如圖3所示。

二、雙變量模擬運(yùn)算表

雙變量模擬運(yùn)算表就是考慮兩個(gè)變量的變化對(duì)公式計(jì)算結(jié)果的影響。

用一個(gè)簡(jiǎn)單的算式z=3x+4y+1來(lái)看：要求當(dāng)x等于從1到4間的所有整數(shù)，而y為1到7間所有整數(shù)時(shí)所有z的值，用模擬運(yùn)算表做：首先排好x與y的位置，然后在下面的單元格中建立一個(gè)公式，在公式所在行的右邊和下面分別輸入兩個(gè)變量的變化值，這里我們?cè)谛猩蠟閤，列上為y，然后選中這個(gè)方形的區(qū)域，選擇“數(shù)據(jù)”菜單中的“模擬運(yùn)算表”命令，打開(kāi)“模擬運(yùn)算表”對(duì)話框，將“輸入引用行的單元格”選擇為公式中x的數(shù)值所在單元格，“輸入引用列的單元格”選擇為公式中y的數(shù)值所在的單元格。如圖4所示。

單擊“確定”按鈕，就可以看到運(yùn)算的結(jié)果了。如果公式發(fā)生了改變，只要修改公式單元格中的公式就可以了，模擬運(yùn)算表的排列位置及計(jì)算結(jié)果如圖5所示。

例2：某企業(yè)打算向銀行申請(qǐng)長(zhǎng)期借款進(jìn)行項(xiàng)目投資，財(cái)務(wù)人員考慮了兩種還款方式想從中選擇其一?；緱l件是：用6年時(shí)間，借款金額在100萬(wàn)元—500萬(wàn)元，利率在5%—12%之間，采用到期一次還本付息還款方式或采用每年等額還本付息方式。分析哪種還款方式最為有利？用“雙變量模擬運(yùn)算表”工具來(lái)測(cè)試不同還款方式對(duì)還款額的影響，步驟如下：

1. 設(shè)計(jì)雙變量模擬運(yùn)算表結(jié)構(gòu)，如圖6所示。

2.在單元格D4中計(jì)算借款金額為100萬(wàn)元，借款期限為6年，借款利率為5%的條件下到期一次還本付息的金額，輸入公式“D4=FV(C4,B4,,A4)”。

3.選取包括公式和需要進(jìn)行模擬運(yùn)算的單元格區(qū)域D4:I12。

4.單擊【數(shù)據(jù)】菜單，選擇【模擬運(yùn)算表】項(xiàng)，彈出【模擬運(yùn)算表】對(duì)話框，在【輸入引用行的單元格】中輸入“$A$4”，在【輸入引用列的單元格】中輸入“$C$4”，如圖7所示。

5.單擊【確定】按鈕，即得到到期一次還本付息雙變量模擬運(yùn)算表的結(jié)果，如圖8所示。

6.按照上述（2）到（5）的步驟進(jìn)行每年等額還本付息雙變量模擬的運(yùn)算，計(jì)算結(jié)果如圖9所示。

7.從圖9中可以看到，將借款200萬(wàn)元，利率為7%，還款期為6年的到期一次還本付息和每年等額還本付息還款方式相比較，前者6年還款總額為300萬(wàn)元，后者6年還款總額為252萬(wàn)元，由此得出每年等額還本付息比到期一次還本付息還款方式要少負(fù)債很多。

例3：林某準(zhǔn)備按揭貸款買(mǎi)房，他手中有資金150000元，其收入為每月3600元?，F(xiàn)在他想選擇一套125平方米，單價(jià)為4000元/平方米的房子，假設(shè)首付款為房款的30%，貸款年利率為5%，準(zhǔn)備20年還完。現(xiàn)在對(duì)以下兩種情況進(jìn)行購(gòu)房分析：

第一，如果房子的價(jià)格、還款年限不變，林某只能每月支付2000元的貸款，用單變量模擬運(yùn)算表確定林某能購(gòu)買(mǎi)的合適的住房面積。

第二，如果房子的價(jià)格不變，而面積和貸款的還款年限可以變動(dòng)，用雙變量模擬運(yùn)算表確定合適的面積和貸款還款年限。

分析如下：

（1）建立單變量模擬運(yùn)算表如圖10所示，在B7單元格中使用函數(shù)B7=PMT(B5/12,B6*12,B2*B3-B4)計(jì)算出每月應(yīng)付款數(shù)額。再選中A7：B21，使用“模擬運(yùn)算表”，計(jì)算出不同面積每月應(yīng)付款數(shù)額。從計(jì)算結(jié)果分析：林某每月支付2000元的貸款，只能選擇面積為105平方米及以下的房子。

（2）建立雙變量模擬運(yùn)算表如圖11所示，在B7單元格中使用函數(shù)B7=PMT(B5/12,B6*12,B2*B3-B4)計(jì)算出每月應(yīng)付款數(shù)額。再選中B7：M21，使用“雙變量模擬運(yùn)算表”，計(jì)算出不同面積、不同年限的每月應(yīng)付款數(shù)額。從計(jì)算結(jié)果分析：林某每月支付2000元的貸款，可以選擇面積和年限為表中有底色顯示的房子。