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

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