Excel模擬分析和運(yùn)算(Excel 2010的功能和使用)

模擬分析和運(yùn)算Excel2010的功能和使用目錄一．手動(dòng)模擬運(yùn)算 1二．單變量求解 2三．模擬運(yùn)算表 31.單變量模擬運(yùn)算表 32.雙變量模擬運(yùn)算表 3四方案管理器 41.建立分析方案 42.顯示方案 63.修改或刪除方案 6模擬分析是在單元格中更改值以查看這些更改將如何影響工作表中公式結(jié)果的過(guò)程。在Excel中使用模擬分析工具，可以在一個(gè)或多個(gè)公式中試用幾組不同的數(shù)據(jù)來(lái)分析所有不同的結(jié)果。例如在個(gè)人住房貸款時(shí)，可以用模擬分析根據(jù)不同的貸款年利率和貸款償還年份，計(jì)算出每月的貸款償還額。Excel附帶了三種模擬分析工具:單變量求解、模擬運(yùn)算表和方案管理器。單變量求解是根據(jù)希望獲取的結(jié)果來(lái)確定生成該結(jié)果的可能的輸入值。模擬運(yùn)算表和方案管理器可獲取一組輸入值并確定可能的結(jié)果。一．手動(dòng)模擬運(yùn)算在不用Excel工具進(jìn)行模擬分析時(shí)，可以手動(dòng)進(jìn)行模擬運(yùn)算。打開(kāi)素材文件夾/第3章中的“模擬運(yùn)算.xlsx”工作簿，在“手動(dòng)模擬運(yùn)算”工作表中展示了某公司產(chǎn)品交易情況的試算表格，如圖所示。表格的上半部分是交易中各相關(guān)指數(shù)的數(shù)值，下半部分則根據(jù)這些數(shù)值用公式統(tǒng)計(jì)出的交易數(shù)量與交易額。在這個(gè)試算模型中，單價(jià)、每次交易數(shù)量、每月交易次數(shù)和歐元匯率都直接影響著月交易額。相關(guān)的模擬分析需求可能如下：1.如果單價(jià)增加1元會(huì)增加多少交易額？2.如果每次交易數(shù)量提高100會(huì)增加多少交易額？3.如果歐元匯率上漲會(huì)怎么樣？面對(duì)這些分析需求，最簡(jiǎn)單的處理方法是直接將假設(shè)的值填入表格上半部分的單元格里，然后利用公式自動(dòng)重算的特性，觀察表格下半年部分的結(jié)果變化。二．單變量求解打開(kāi)素材文件夾/第3章中的“模擬運(yùn)算.xlsx”工作簿，在“單變量求解”工作表中展示了某公司產(chǎn)品交易情況的試算表格，其中銷售單價(jià)、交易數(shù)量、交易次數(shù)和歐元匯率都會(huì)直接影響年交易額，如果希望根據(jù)某個(gè)年交易額快速倒推，計(jì)算出銷售單價(jià)、交易數(shù)量、交易次數(shù)和歐元匯率的具體狀況，具體操作步驟如下：步驟1：選擇年交易額所在的單元格B13，在【數(shù)據(jù)】|【數(shù)據(jù)工具】組中，單擊【模擬分析】按鈕，在彈出的下拉列表中選擇“單變量求解”命令，彈出“單變量求解”對(duì)話框。步驟2：在“目標(biāo)單元格”中顯示目標(biāo)值的單元格地址，此處為B13單元格。在“目標(biāo)值”輸入希望達(dá)成的交易額，此處輸入350000。單擊激活“可變單元格”編輯框，在工作表中單擊B4單元格。結(jié)果如圖所示。步驟3：?jiǎn)螕簟按_定”按鈕，彈出“單變量求解狀態(tài)”對(duì)話框，對(duì)單元格B13進(jìn)行單變量求解求得一個(gè)解，同時(shí)工作表中的“交易數(shù)量”和“年交易額”值發(fā)生了變化。單擊“確定”按鈕，接受計(jì)算結(jié)果，如圖所示。說(shuō)明：計(jì)算結(jié)果表明，在其他條件不變的情況下，要使交易額達(dá)到35萬(wàn)，可以提高銷售單價(jià)到230以上。步驟4：重復(fù)步驟1～3，可以重新測(cè)試銷售單價(jià)、交易次數(shù)等。三．模擬運(yùn)算表模擬運(yùn)算表實(shí)際上是一個(gè)單元格區(qū)域，它可以用列表的形式顯示計(jì)算模型中某些參數(shù)的變化對(duì)公式計(jì)算結(jié)果的影響。在這個(gè)區(qū)域中，生成的值所需要的若干個(gè)相同公式被簡(jiǎn)化成一個(gè)公式，從而簡(jiǎn)化了公式的輸入。根據(jù)模擬運(yùn)算行、列個(gè)數(shù)的不同，可分為單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表兩種類型。1.單變量模擬運(yùn)算表在素材文件夾/第3章中的“模擬運(yùn)算.xlsx”工作簿的“單變量模擬運(yùn)算表”工作表中，借助模擬運(yùn)算表分析歐元匯率變化對(duì)月交易額的影響，具體操作步驟如下：步驟1：在D4：D13單元格區(qū)域中，輸入可能的歐元匯率，在單元格E3中公式“=B11”。步驟2：選擇要?jiǎng)?chuàng)建模擬運(yùn)算表的單元格區(qū)域D3：E13，在【數(shù)據(jù)】|【數(shù)據(jù)工具】組中，單擊“模擬分析”按鈕，在彈出的下拉列表中選擇“模擬運(yùn)算表”命令，彈出“模擬運(yùn)算表”對(duì)話框。步驟3：?jiǎn)螕艏せ睢拜斎胍昧械膯卧瘛本庉嬁?，在工作表中單擊B6單元格，將自動(dòng)輸入“$B$6”，單擊“確定”按鈕，選定區(qū)域中自動(dòng)生成模擬運(yùn)算表，如圖所示。說(shuō)明：計(jì)算結(jié)果展示了在不同的歐元匯率下月交易額的變化。如果模擬運(yùn)算表變量值輸入在一行中，應(yīng)在“輸入引用行的單元格”框中選擇變量值所在的位置。2.雙變量模擬運(yùn)算表雙變量模擬運(yùn)算可以幫助用戶分析兩個(gè)因素對(duì)最終結(jié)果的影響。在素材文件夾/第3章中的“模擬運(yùn)算.xlsx”工作簿的“雙變量模擬運(yùn)算表”工作表中，分析銷售單價(jià)和歐元匯率同時(shí)變化對(duì)月交易額的影響，具體操作步驟如下：步驟1：在D4：D13單元格區(qū)域中，輸入不同的銷售單價(jià)，在E3：J3單元格區(qū)域中輸入可能的歐元匯率，在單元格E3中公式“=B11”。步驟2：選擇要?jiǎng)?chuàng)建模擬運(yùn)算表的單元格區(qū)域D3：J13，在【數(shù)據(jù)】|【數(shù)據(jù)工具】組中，單擊“模擬分析”按鈕，在彈出的下拉列表中選擇“模擬運(yùn)算表”命令，彈出“模擬運(yùn)算表”對(duì)話框。步驟3：?jiǎn)螕艏せ睢拜斎胍眯械膯卧瘛本庉嬁?，在工作表中單擊B6單元格，將自動(dòng)輸入“$B$6”；單擊激活“輸入引用列的單元格”編輯框，在工作表中單擊B3單元格，將自動(dòng)輸入“$B$3”；單擊“確定”按鈕，選定區(qū)域中自動(dòng)生成模擬運(yùn)算表，如圖所示。說(shuō)明：計(jì)算結(jié)果展示了在不同的銷售單價(jià)和歐元匯率下月交易額的變化。此處也可根據(jù)需要模擬運(yùn)算其他結(jié)果，如將D11中的公式改為“=B13“，將會(huì)計(jì)算在不同的銷售單價(jià)和歐元匯率下的年交易額。四方案管理器模擬運(yùn)算表無(wú)法容納兩個(gè)以上的變量，如果要同時(shí)考慮更多的因素來(lái)進(jìn)行分析，可以使用方案管理器。1.建立分析方案在素材文件夾/第3章中的“模擬運(yùn)算.xlsx”工作簿的“方案管理器”工作表中，可以為銷售單價(jià)、交易數(shù)量、歐元匯率等因素設(shè)置不同值的組合。例如要試算多種目標(biāo)下的交易額情況，如最好狀態(tài)、平均狀態(tài)、最差狀態(tài)3種，可以定義3個(gè)方案與之對(duì)應(yīng)，每個(gè)方案中都為這些因素設(shè)定不同的值，具體操作步驟如下：步驟1：選擇可變單元格區(qū)域B3:B6，在【數(shù)據(jù)】|【數(shù)據(jù)工具】組中，單擊“模擬分析”按鈕，在彈出的下拉列表中選擇“方案管理器”命令，彈出“方案管理器”對(duì)話框，如圖所示。步驟4：?jiǎn)螕粲疑戏降摹疤砑印卑粹o，彈出“添加方案”對(duì)話框。在“方案名”文本框中輸入方案名稱“最好狀態(tài)”，在“可變單元格”框中可重新指定變量單元格，如圖所示。步驟5：?jiǎn)螕簟按_定”按鈕，繼續(xù)彈出“方案變量值”對(duì)話框，依次輸入最好情況下的變量值，如圖所示。步驟6：?jiǎn)螕簟按_定”按鈕，返回到“方案管理器”對(duì)話框。步驟7：重復(fù)步驟4～6，繼續(xù)添加平均狀態(tài)、最差狀態(tài)下的方案，添加完成如圖所示。步驟8：操作過(guò)程中引用的可變單元格區(qū)域始終保持不變，所有方案添加完畢后，單擊“方案管理器”對(duì)話框中的“關(guān)閉”按鈕。2.顯示方案分析方案制定好后，任何時(shí)候都可以執(zhí)行方案，以查看不同的執(zhí)行結(jié)果，具體操作步驟如下：步驟1：在【數(shù)據(jù)】|【數(shù)據(jù)工具】組中，單擊“模擬分析”按鈕，在彈出的下拉列表中選擇“方案管理器”命令，彈出“方案管理器”對(duì)話框。步驟2：在“方案”列表框中選中一個(gè)方案后，單擊下方的“顯示”按鈕，或者直接雙擊某個(gè)方案，Excel將用該方案中設(shè)定的變量值替換掉工作表中相應(yīng)單元格原來(lái)的值，同時(shí)公式中顯示方案執(zhí)行結(jié)果。3.修改或刪除方案打開(kāi)“方案管理器”對(duì)話框，在“方案”列表中選擇要修改的方案，單擊“編輯”按鈕，在隨后彈出的對(duì)話框中可修改名稱、變量值等。單擊“刪除”按鈕，可以刪除方案。4.生成方案報(bào)告如果每次查看一個(gè)方案所生成的結(jié)果，顯示不便于對(duì)比分析，Excel的方案功能允許用戶生成報(bào)告，將所有方案的執(zhí)行結(jié)果都顯示出來(lái)并進(jìn)行比較，具體操作步驟如下：步驟1：在“方案管理器”工作表中，單擊【數(shù)據(jù)】|【數(shù)據(jù)工具】組中的“模擬分析”按鈕，從下拉列表中選擇“方案管理器”命令，彈出“方案管理器”對(duì)話框。步驟2：?jiǎn)螕粲覀?cè)的“摘要”按鈕，彈出“方案摘要”對(duì)話框，如圖所示。步驟3：在該對(duì)話框中選擇報(bào)表類型，其中“方案摘要”是以大綱形式展示報(bào)