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

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

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

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

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

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