Excel2016中模擬運(yùn)算表的應(yīng)用

王侃

摘要：Excel2016是微軟辦公軟件OFFICE的重要組成部分，它具有強(qiáng)大的數(shù)據(jù)計(jì)算、處理和統(tǒng)計(jì)分析功能，并能輔助決策，廣泛應(yīng)用于財(cái)務(wù)、金融和管理等多個(gè)領(lǐng)域。模擬運(yùn)算表是進(jìn)行預(yù)測(cè)分析的一種工具，它可以用來(lái)預(yù)測(cè)公式中一個(gè)或多個(gè)數(shù)據(jù)變化時(shí)對(duì)計(jì)算結(jié)果產(chǎn)生的影響。通過(guò)貸款還款表，數(shù)學(xué)方程計(jì)算等實(shí)例中來(lái)淺談模擬運(yùn)算表的應(yīng)用。

關(guān)鍵詞：Excel2016；模擬運(yùn)算表；應(yīng)用

中圖分類(lèi)號(hào)：TP393 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2017）01-0270-02

Excel2016表處理軟件是美國(guó)微軟公司研制的辦公自動(dòng)化軟件OFFICE 中的重要成員，具有強(qiáng)大的數(shù)據(jù)計(jì)算及處理分析功能。模擬運(yùn)算表即是其中的應(yīng)用之一，模擬運(yùn)算表是進(jìn)行預(yù)測(cè)分析的一種工具，是Excel2016工作表的一個(gè)單元格區(qū)域，用于顯示的是公式中一個(gè)或多個(gè)數(shù)據(jù)變量對(duì)計(jì)算結(jié)果的影響。模擬運(yùn)算表提供了一種捷徑，它可以同時(shí)求解運(yùn)算中所有可能的變化值的組合，也是一種有效的方法，可以將不同的計(jì)算結(jié)果同時(shí)顯示在工作表中，可以查看和比較由工作表中不同變化所引起的各種結(jié)果。

1 概述

1.1 模擬分析工具

模擬分析是指通過(guò)更改單元格中的值來(lái)查看這些更改對(duì)工作表中公式結(jié)果的影響的過(guò)程。在Excel2016中模擬分析工具有三種，分別是方案管理器、單變量求解和模擬運(yùn)算表。模擬運(yùn)算表是這一組命令的組成部分，這組命令便被稱(chēng)為模擬分析工具。

方案管理器和模擬運(yùn)算表根據(jù)各組輸入值來(lái)確定可能的結(jié)果，使用方案管理器可以方便進(jìn)行假設(shè)，可以為多變量存儲(chǔ)輸入值的不同組合，并且可以為每個(gè)組合命名。與方案管理器類(lèi)似的是，模擬運(yùn)算表有助于尋找一組可能的結(jié)果。不同于方案管理器的是，模擬運(yùn)算表會(huì)在工作表中的一個(gè)表中顯示所有結(jié)果。使用模擬運(yùn)算表可以輕松查看一系列可能性。由于只關(guān)注一個(gè)或兩個(gè)變量，表格形式的結(jié)果易于閱讀和共享。模擬運(yùn)算表無(wú)法容納兩個(gè)以上的變量。如果要分析兩個(gè)以上的變量，則應(yīng)改用方案管理器。盡管只能使用一個(gè)或兩個(gè)變量，但模擬運(yùn)算表可以包括任意數(shù)量的不同變量值。方案管理器可擁有最多32個(gè)不同的值，但可以創(chuàng)建任意數(shù)量的方案。

單變量求解與方案管理器和模擬運(yùn)算表的工作方式不同，它是假設(shè)一個(gè)公式要取得的某一個(gè)結(jié)果值，并確定生成該結(jié)果的可能的輸入值。Excel會(huì)根據(jù)用戶提供的目標(biāo)值對(duì)引用單元格的值不斷地進(jìn)行調(diào)整，直至達(dá)到所要求的公式的目標(biāo)值時(shí)，變量單元格中的值才能確定。

1.2 模擬運(yùn)算表的類(lèi)型

根據(jù)需要觀察的數(shù)據(jù)變量的多少可以將模擬運(yùn)算表分為兩種類(lèi)型：?jiǎn)巫兞磕M運(yùn)算表和雙變量模擬運(yùn)算表。其中，在單變量模擬運(yùn)算表中，輸入一個(gè)變量的不同替換值，并顯示此變量對(duì)一個(gè)或多個(gè)公式的影響；在雙變量模擬運(yùn)算表中，輸入兩個(gè)變量的不同替換值，從而來(lái)顯示這兩個(gè)變量對(duì)一個(gè)公式的影響。創(chuàng)建單變量模擬運(yùn)算表還是雙變量模擬運(yùn)算表，取決于需要測(cè)試的變量和公式數(shù)。

2 模擬運(yùn)算表的應(yīng)用

2.1 貸款計(jì)算表

如今越來(lái)越多的人利用貸款來(lái)購(gòu)置房產(chǎn)，在銀行貸款時(shí)貸款的年限是可以選擇的，利率也是會(huì)有波動(dòng)的，而用手工來(lái)計(jì)算每月需還多少金額相對(duì)比較復(fù)雜。這時(shí)就可以利用模擬運(yùn)算表來(lái)顯示在年限與利率變化之下的月還款金額。

（1）單變量模擬運(yùn)算表

若貸款總額和利率不變而只有貸款年限變動(dòng)，則可以采用單變量模擬運(yùn)算表來(lái)計(jì)算每月還款金額。在計(jì)算月還款金額時(shí)需要用到PMT函數(shù)，然后再單列或單行中輸入變量值后，結(jié)果便會(huì)在相鄰的列或行中顯示。

首先創(chuàng)建如圖1所示工作表，在B6單元格內(nèi)輸入公式“=PMT（B3/12，B5*12，A3）”，即可得到當(dāng)貸款年限為5年時(shí)每月需還款的金額￥-11599.68。然后選擇區(qū)域B5：F6，單擊【數(shù)據(jù)】選項(xiàng)卡下的【模擬分析】中的【模擬運(yùn)算表】，在彈出的對(duì)話框中輸入引用行的單元格$B$5（因?yàn)樵诒纠袝?huì)發(fā)生變動(dòng)的年限數(shù)值在放在一行中的，也即模擬運(yùn)算表是行方向的，所以選擇輸入引用行），如圖2所示。單擊確定后便可創(chuàng)建如圖3所示的模擬運(yùn)算表，從該表中可以看到還款年限對(duì)月還款金額的影響。

（2）雙變量模擬運(yùn)算表

若貸款年限和貸款利率同時(shí)變化，則需要?jiǎng)?chuàng)建雙變量模擬運(yùn)算表來(lái)計(jì)算每月的還款金額。首先在A10單元格中輸入和B6中同樣的公式“=PMT（B3/12，B5*12，A3）”，得到當(dāng)利率為6%，貸款年限為5年時(shí)每月需還款的金額。然后選擇區(qū)域A10：F16，單擊【數(shù)據(jù)】選項(xiàng)卡下的【模擬分析】中的【模擬運(yùn)算表】，在彈出的對(duì)話框中輸入引用行的單元格$B$5，輸入引用列的單元格$B$3，便可得到如圖4所示的雙變量模擬運(yùn)算表，在年限不同且利率發(fā)生變化的情況下，每月需還款的金額一目了然。

（3）加快模擬運(yùn)算表的計(jì)算

如果工作表發(fā)生了變動(dòng)，則需對(duì)表數(shù)據(jù)重新進(jìn)行計(jì)算，那工作表中包含的模擬數(shù)據(jù)表也得重新計(jì)算，可以通過(guò)設(shè)置提高模擬運(yùn)算表的運(yùn)算速度。打開(kāi)【文件】選項(xiàng)卡中的【選項(xiàng)】，點(diǎn)擊【公式】，在計(jì)算選項(xiàng)中，選中【除模擬運(yùn)算表外，自動(dòng)重算】，然后單擊【確定】即可。

2.2 數(shù)學(xué)方程式計(jì)算

若一個(gè)數(shù)學(xué)方程式里含有一個(gè)變量x，則可以利用單變量模擬運(yùn)算表代入若干個(gè)x的值，很快便可得到方程的結(jié)果。若含有兩個(gè)變量x和y，則可以利用雙變量模擬運(yùn)算表來(lái)計(jì)算方程的結(jié)果。

例如f（x）=5x4-2y3+4x2y-6x-8y+16，代入各種x和y的數(shù)值，求f（x）的值。在Excel中創(chuàng)建如圖5數(shù)據(jù)表。將需要代入的變量x的值輸入在C4：H4單元格區(qū)域中，變量y的值輸入在B5：B10單元格區(qū)域中，把變量x的可變單元格指定為A2，變量y的可變單元格指定為B2，則在B4單元格內(nèi)輸入公式：=5*A2^4-2*B2^3+4*A2^2*B2-6*A2-8*B2+16，可以得到x，y取值均為0時(shí)方程的結(jié)果，然后選擇區(qū)域B4：H10，單擊【數(shù)據(jù)】選項(xiàng)卡下的【模擬分析】中的【模擬運(yùn)算表】，在彈出的對(duì)話框中輸入引用行的單元格$A$2，輸入引用列的單元格$B$2，如圖6所示，單擊【確定】后便可得出在不同的x和y的數(shù)值下，方程f（x）的結(jié)果，如圖7所示。

3 總結(jié)

利用Excel模擬運(yùn)算表只需一步操作便能求出所有變化，可以顯示公式中某些值的變化對(duì)計(jì)算結(jié)果的影響。通過(guò)以上實(shí)例我們可以看出，模擬運(yùn)算表就是在單元格內(nèi)建立一個(gè)含有變量的公式，再將需要代入運(yùn)算的各個(gè)數(shù)值輸入在工作表上，利用【模擬運(yùn)算表】命令即可計(jì)算數(shù)值代入公式計(jì)算后的各個(gè)結(jié)果。

參考文獻(xiàn)：

[1]谷增軍.Excel模擬運(yùn)算表在財(cái)務(wù)分析中的應(yīng)用[J].財(cái)會(huì)月刊，2010（1）：22-23.

[2]Excel在中小企業(yè)財(cái)務(wù)管理中的應(yīng)用[J].商，2015（9）：143-143.

[3]Excel單變量模擬運(yùn)算表在投資收益運(yùn)算中的應(yīng)用[J].軍民兩用技術(shù)與產(chǎn)品，2015（4）.