做數(shù)據(jù)分析時(shí)經(jīng)常會(huì)用到Excel，其中Excel的數(shù)據(jù)模擬分析模擬運(yùn)算表是一個(gè)非常好的數(shù)據(jù)預(yù)測(cè)和計(jì)算模塊。模擬運(yùn)算表是最強(qiáng)大的分析工具——初次敏感性分析。

例如，當(dāng)公司制定下一期的營(yíng)業(yè)計(jì)劃或者對(duì)引進(jìn)辦公室的機(jī)器的租金進(jìn)行比較性商討時(shí)，我們都會(huì)講求貨比三家，要求高性價(jià)比，追求經(jīng)濟(jì)效益的最大化，這個(gè)時(shí)候有一件事情是非常重要的， 那就是要多方更改所涉及的多種條件進(jìn)行充分的試算。

這種情況下，就該“模擬運(yùn)算表”大顯神威了。使用模擬運(yùn)算表，通過(guò)簡(jiǎn)單的操作就能夠?qū)崿F(xiàn)敏感度分析（一種分析手法，眾多條件發(fā)生變動(dòng)時(shí)會(huì)導(dǎo)致結(jié)果發(fā)生什么樣的變化）。

指定多個(gè)條件分析數(shù)據(jù)

為了說(shuō)明模擬運(yùn)算表的基本操作方法，對(duì)得到銀行融資時(shí)每月的還款金額進(jìn)行試算。條件有如下3個(gè)。

1.還款期限為3、4、5年中的任意一個(gè)。

2.借款金額為1000萬(wàn)元、1300萬(wàn)元、1500萬(wàn)元中的任意一個(gè)。

3.利息為年息2%，每月還款金額控制在30萬(wàn)元以內(nèi)。

由于還款期限有3種，借款金額也有3種，因此共計(jì)將有9種還款計(jì)劃。其中， 將每月還款金額控制在30萬(wàn)元以內(nèi)的同時(shí)將借款金額最大化，選擇哪種計(jì)劃更好呢？

借款計(jì)劃模式

對(duì)上述內(nèi)容進(jìn)行分析研究就需要像下圖中那樣，挨個(gè)輸入各公司的不同條件，對(duì)比結(jié)果，否則就無(wú)法選出最合適的。

還款期限為4年，借款金額為1000萬(wàn)元時(shí)

以還款期限為4年，借款金額為1000萬(wàn)元進(jìn)行試算。試算其他模式時(shí)，就不得不改寫(xiě)“借款金額”和“還款期限”。

但是，這種方法1次計(jì)算就只能確認(rèn)1種模式的還款計(jì)劃，要計(jì)算其他還款計(jì)劃就必須多次改寫(xiě)單元格的數(shù)據(jù)。此外，要比較各還款計(jì)劃的不同還需要把各自的結(jié)果寫(xiě)在其他的地方，非常不方便，不能說(shuō)它是一個(gè)高效的方法。 Excel中預(yù)備有可以進(jìn)行高效試算的便捷功能，那就是“模擬運(yùn)算表”。

一覽顯示多個(gè)條件計(jì)算得出的結(jié)果

使用模擬運(yùn)算表， 當(dāng)包含在公式中的一個(gè)或者兩個(gè)值發(fā)生變動(dòng)時(shí)，可以通過(guò)一覽表的形式確認(rèn)其計(jì)算結(jié)果發(fā)生了什么樣的變化。拿本次的示例來(lái)說(shuō)，當(dāng)2個(gè)值（還款期限和借款金額）發(fā)生變動(dòng)時(shí)，由此而發(fā)生的每個(gè)月的還款計(jì)劃共計(jì)9種模式，都可以一并在一張表中進(jìn)行確認(rèn)（具體的設(shè)置方法在下頁(yè)中進(jìn)行說(shuō)明）。

用模擬運(yùn)算表所進(jìn)行的還款計(jì)劃的試算

使用模擬運(yùn)算表，9種模式的試算結(jié)果可以一并在一張表中進(jìn)行最終確認(rèn)。

模擬運(yùn)算表的創(chuàng)建方法

來(lái)實(shí)際創(chuàng)建一下模擬運(yùn)算表吧。

創(chuàng)建模擬運(yùn)算表時(shí)，首先創(chuàng)建一個(gè)表格，填入“某條件下的試算結(jié)果”，然后從中選擇“試算時(shí)值發(fā)生更改的單元格”。本次我們來(lái)更改單元格F5和單元格F7的數(shù)值。

1.更改單元格F5的“借款金額”和單元格F7的“還款期限”的數(shù)值，求單元格F4的“每月還款金額”。

1

2.創(chuàng)建模擬運(yùn)算表所用表格，在標(biāo)題列中輸入還款期限，標(biāo)題行中輸入借款金額。

2

3.在標(biāo)題行左端的單元格中指定填寫(xiě)算式的單元格，該算式為模擬運(yùn)算表中欲進(jìn)行計(jì)算的算式。

3

Tip：每月還款金額的計(jì)算方法

PMT函數(shù)可以計(jì)算出每次的支付金額，其中有三個(gè)參數(shù)，貸款利率（第一參數(shù)）和付款期數(shù)（第二參數(shù)），當(dāng)前的借款金額（第三參數(shù)），為它們賦值即可求出付款金額。由于PMT函數(shù) 的結(jié)果顯示為負(fù)值，因此如果想顯示正值，須在“=”的后面添加“-”符號(hào).

4.選擇表格，包括標(biāo)題行和標(biāo)題列，點(diǎn)擊[數(shù)據(jù)]選項(xiàng)卡中的[模擬分析]→[模擬運(yùn)算表]。

4

5.分別以絕對(duì)引用的形式在[輸入引用行的單元格]中輸入標(biāo)題行中數(shù)值所對(duì)應(yīng)的單元格地址，在[輸入引用列的單元格]中輸入標(biāo)題列所對(duì)應(yīng)的單元格地址，按下[確定]按鈕。

5

6.顯示出模擬運(yùn)算表的計(jì)算結(jié)果?？梢钥闯?，本次的組合為每月的還款金額在30萬(wàn)元以內(nèi)，借款金額最大可達(dá)1500萬(wàn)元，還款金額為5年。

6

Tip:如果不想顯示左端的數(shù)值

如果不想顯示步驟 3 中輸入的位于標(biāo)題行左端的數(shù)值，可以將文字顏色設(shè)為白色，這樣就看不見(jiàn)了。單元格中的值不能刪除，刪除后，模擬運(yùn)算表的計(jì)算結(jié)果就會(huì)發(fā)生改變。

使用模擬運(yùn)算表時(shí)的注意事項(xiàng)

模擬運(yùn)算表中，通過(guò)改變表中的兩個(gè)數(shù)值來(lái)進(jìn)行多種情況的試算。需要改變哪個(gè)數(shù)值可以在“模擬運(yùn)算表”對(duì)話框中進(jìn)行指定，此時(shí)注意不要指定輸入有公式的單元格?？梢宰鳛槟M運(yùn)算表的變量進(jìn)行指定的只能是直接輸入了數(shù)值的單元格。

另外， 模擬運(yùn)算表和原表必須放在同一工作表中，而且不能引用別的工作表中的值。

這里比較令人苦惱的是模擬運(yùn)算表的放置位置。如果放置在原表的右側(cè)，那么當(dāng)原表的行發(fā)生增減時(shí)容易發(fā)生問(wèn)題。但是，要是放在原表的下方，又得擔(dān)心列寬的調(diào)整。

考慮到這些，模擬運(yùn)算表的最佳擺放位置就是原表的斜右下方（見(jiàn)下圖）。放在斜右下方，即便原表的行和列發(fā)生增減，也不會(huì)影響到模擬運(yùn)算表。此外，模擬運(yùn)算表的列寬是可以靈活設(shè)置的。使用Excel進(jìn)行數(shù)據(jù)分析時(shí)，對(duì)便于維護(hù)這一點(diǎn)的考量也是非常重要的。

創(chuàng)建模擬運(yùn)算表時(shí)將其放置在原表的斜右下方

大家都看明白了嗎？進(jìn)一步關(guān)注——

《Excel最強(qiáng)教科書(shū)（完全版）》

《孫子兵法》有云：將莫不聞，知之者勝，不知者不勝。這同樣也適用于職場(chǎng)成功法則，熟練使用Excel的職員和普通職員在工作效率和工作質(zhì)量上可謂天壤之別。做同樣的工作，有的人用5個(gè)小時(shí)，而有的人10秒就完成了。這并不是夸大其詞，日本Excel研修講師藤井直彌的真《Excel最強(qiáng)教科書(shū)》中所講解的Excel商務(wù)實(shí)用辦公技巧，不僅能幾十倍地提高工作效率，還能大幅度減少輸入錯(cuò)誤和計(jì)算錯(cuò)誤，問(wèn)世兩年印刷21次！

您的關(guān)注是我們不斷努力的動(dòng)力