Excel 2016數(shù)據(jù)處理與分析應(yīng)用教程 課件 第10章 模擬分析

第10章模擬分析主要內(nèi)容10.1單變量求解10.2模擬運算表10.3方案管理器10.1單變量求解單變量求解是對函數(shù)公式的逆運算，主要解決假定一個公式要取得某一結(jié)果值，公式中的某個變量應(yīng)取值為多少的問題。10.1單變量求解【例10-1】簡單函數(shù)y=2x+10的單變量求解。分析：在B2輸入x的值5；B3輸入函數(shù)的截距10；B4中是y值的計算公式“=2*B2+B3”，計算結(jié)果為20。如果想讓B4（y）為某個特定值，那么B2（x）值應(yīng)該是多少呢？即如何根據(jù)y值求出x的值呢？這是典型的逆運算問題。10.1單變量求解【例10-1】簡單函數(shù)y=2x+10的單變量求解。假設(shè)y的目標(biāo)值為100，通過單變量求解出x值：①單擊“數(shù)據(jù)”選項卡“預(yù)測”選項組中的“模擬分析”按鈕，選擇“單變量求解”命令，彈出“單變量求解”對話框。②將目標(biāo)單元格設(shè)置為$B$4，目標(biāo)值設(shè)置為100，可變單元格設(shè)置為$B$2。10.1單變量求解【例10-1】簡單函數(shù)y=2x+10的單變量求解。求解結(jié)果：10.1單變量求解【例10-2】貸款問題的單變量求解。某人計劃貸款100萬元，10年，采取每月等額償還本息的方法，按年利率5.5%的方案，利用財務(wù)函數(shù)PMT可以計算出每月需還款額。但目前每月可用于還貸的資金只有8000元，可申請貸款的最大額度是多少？分析：B5的計算公式“=PMT(B4/12,B3*12,B2)”

B2是可變單元格，B5是目標(biāo)單元格，目標(biāo)值是-8000。10.1單變量求解【例10-2】貸款問題的單變量求解。求解結(jié)果：在年利率和貸款年限不變的條件下，可以申請貸款的最大額度。10.2模擬運算表10.2.1單變量模擬運算表

分析一個變量值的變化對公式運算結(jié)果的影響。10.2.2雙變量模擬運算表

分析兩個變量值同時變化對公式運算結(jié)果的影響。10.2.1單變量模擬運算表當(dāng)需要分析單個決策變量變化對某個計算公式的影響時，可以使用單變量模擬運算表實現(xiàn)。例如，不同的年化收益率對理財產(chǎn)品收益的影響，不同的貸款年利率對還款額度的影響等。10.2.1單變量模擬運算表【例10-3】某公司計劃貸款1000萬元，年限為10年，采取每月等額償還本息的方法歸還貸款本金并支付利息，目前的年利率為4%，每月的償還額為101245.14元。但根據(jù)宏觀經(jīng)濟的發(fā)展情況，國家會通過調(diào)整利率對經(jīng)濟發(fā)展進(jìn)行宏觀調(diào)控。投資人為了更好地進(jìn)行決策，需要全面了解利率變動對償貸能力的影響。10.2.1單變量模擬運算表【例10-3】貸款問題的單變量模擬運算表。分析：B2貸款金額1000萬元；B3貸款年限10年；B4年利率4%；B5的計算公式“=PMT(B4/12,B3*12,B2)”。使用單變量模擬運算表可以很直觀地以表格的形式，將償還貸款的能力與利率變化的關(guān)系列出來。10.2.1單變量模擬運算表【例10-3】貸款問題的單變量模擬運算表。操作步驟①選擇模擬運算表存放區(qū)域。本例選擇D1:E13。其中D2:D13列出了利率的所有取值，E1是計算每月償還額的公式“=PMT(B4/12,B3*12,B2)”10.2.1單變量模擬運算表【例10-3】貸款問題的單變量模擬運算表。操作步驟①選擇模擬運算表存放區(qū)域。本例選擇D1:E13。其中D2:D13列出了利率的所有取值，E1是計算每月償還額的公式“=PMT(B4/12,B3*12,B2)”10.2.1單變量模擬運算表【例10-3】貸款問題的單變量模擬運算表。操作步驟②選定D1:E13，單擊“數(shù)據(jù)”選項卡“預(yù)測”選項組中的“模擬分析”按鈕，選擇“模擬運算表”選項，彈出“模擬運算表”對話框。③在該對話框的“輸入引用列的單元格”框中輸入“$B$4”。10.2.1單變量模擬運算表【例10-3】貸款問題的單變量模擬運算表。計算結(jié)果10.2.1單變量模擬運算表【例10-3】貸款問題的單變量模擬運算表。將利率值按行存放，A8中輸入計算公式，$B$4為引用行的單元格，則計算結(jié)果：10.2.2雙變量模擬運算表

單變量模擬運算表只能解決一個變量值變化對公式計算結(jié)果的影響，如果想查看兩個變量值變化對公式計算結(jié)果的影響就需要用到雙變量模擬運算表。10.2.2雙變量模擬運算表【例10-4】基于例10-3，除了年利率的變化，還需要同時分析不同貸款年限對償還額的影響。分析：這里涉及到年利率和貸款年限兩個變量，需要使用雙變量模擬運算表進(jìn)行計算。10.2.2雙變量模擬運算表【例10-4】基于例10-3，除了年利率的變化，還需要同時分析不同貸款年限對償還額的影響。操作步驟①選擇模擬運算表存放區(qū)域A7:M13。B7:M7列出年利率；A8:A13列出貸款年限。A7是每月償還額的計算公式“=PMT(B4/12,B3*12,B2)”10.2.2雙變量模擬運算表【例10-4】基于例10-3，除了年利率的變化，還需要同時分析不同貸款年限對償還額的影響。操作步驟②選定整個模擬運算表區(qū)域A7:M13，單擊“數(shù)據(jù)”選項卡“預(yù)測”選項組……，打開“模擬運算表”對話框。③“輸入引用行的單元格”為“$B$4”，“輸入引用列的單元格”為“$B$3”，即行變量是年利率，列變量是貸款年限。10.2.2雙變量模擬運算表【例10-4】基于例10-3，除了年利率的變化，還需要同時分析不同貸款年限對償還額的影響。計算結(jié)果10.3方案管理器如果要解決包括更多可變因素的問題，或是要在多種假設(shè)分析中找出最佳執(zhí)行方案，單變量模擬運算表和雙變量模擬運算表就無法實現(xiàn)了。方案管理器主要用于解決多方案求解問題，尋求最佳的解決方案。10.3方案管理器【例10-5】基于例10-4雙變量模擬運算表中的貸款問題，要求同時分析不同貸款年利率、貸款年限和貸款金額對每月償還額的影響。分析：這里涉及到年利率、貸款年限和貸款金額共三個變量，即變量超過了兩個，使用方案管理器。方案管理器是在雙變量模擬運算表的基礎(chǔ)上創(chuàng)建的。10.3方案管理器【例10-5】方案管理器。操作步驟①單擊“數(shù)據(jù)”選項卡“預(yù)測”選項組中的“模擬分析”按鈕，選擇“方案管理器”選項，彈出“方案管理器”對話框。②在“方案管理器”對話框中單擊“添加”按鈕，彈出“添加方案”對話框。10.3方案管理器【例10-5】方案管理器。操作步驟③在“添加方案”對話框的“方案名”中輸入“貸款金額-800”，然后指定“貸款金額”所在的B2單元格為可變單元格。④單擊“確定”按鈕。出現(xiàn)“方案變量值”對話框，將文本框中顯示的可變單元格原始數(shù)據(jù)修改為方案模擬數(shù)值800萬元。10.3方案管理器【例10-5】方案管理器。操作步驟⑤“貸款金額-800”方案創(chuàng)建完畢后，相應(yīng)的方案自動添加到“方案管理器”的方案列表中。⑥同樣的方法，建立“貸款金額-900”“貸款金額-1000”“貸款金額-1100”和“貸款金額-1200”等方案。10.3方案管理器【例10-5】方案管理器。查看方案“貸款金額-800”選中方案，單擊“顯示”。10.3方案管理器【例10-5】方案管理器。查看方案“貸款金額-1100”10.3