exc的el-ch17 目标搜寻与分析蓝本.ppt

第17章 目標搜尋與分析藍本 目標搜尋 我們曾在第6章及第9章學過公式及函數，基本上他們就是填入運算元或參數等來源變數，然後利用運算子或函數來計算出結果，而目標搜尋則是反其道而行，是由結果去反推來源變數，例如：A1+B1=C1是我們的公式，A1與B1儲存格填入資料便可在C1得到結果，而目標搜尋則是固定目標儲存格C1的結果，反推A1或B1儲存格，目標搜尋只能反推計算過程中的一個變數。或許你會覺得直接利用其他的儲存格來計算，例如A1=C1-B1不是更快？那是因為這是太簡單的例子，想想如果公式像((A1^2+B1^1/2)*0.5)/0.33=C1這樣子，要算出A1或B1的反推方式是不是要花點腦筋跟時間，用目標搜尋就好了，尤其是函數，像PMT或FT的財務函數，要反推期數，或利率的公式，就沒那麼簡單了。 17-1 利用目標搜尋預測的例子 利用目標搜尋的例子很多，例如每月固定儲蓄一金額，每年存款利率為5%，經過多久可以存到100萬元呢？或如下例，有人只求總成績60及格就好，有人想進軍第1名，那期末到底要考多少呢？ Step1：我們先選取多媒體成績表如下，有10名同學的平時成績及期中、期末成績，而總成績的方式=平時成績*40%+期中考*30%+期末考*30%。 Step2：再選取成績目標搜尋工作表。 Step3：為了工作表美觀，我們透過 [工具/選項/檢視] 將格線拿掉。 Step4：並利用註解將公式秀出來，原來這是透過參照到多媒體成績表得到的結果。 Step5：試試將C3的學號果然公式都自動幫我們取得了該生的資料。 Step6：假設道明四同學想在總成績逹到60分，而只剩期末考資料可改變，讓我們算算看(你自己去用公式反推吧)！我們還是用目標搜尋吧！ Step7：執行 [資料/目標搜尋] 。 Step8：開啟 [目標搜尋] 交談窗，並選取目標儲存格為C7，目標值設為60，而變數儲存格就是期末考成績C6。 Step9：喔哦！出現下列錯誤訊息，原來是變數儲存格必須是數值，而這裏是參照的公式是期末考的30%。 Step10：利用摺疊鈕且到多媒體成績表，選取J5儲存格，才真正是期末考的分數，30分會使總成績不及格，該考多少呢？ Step11：按摺疊鈕回來，再按 [確定]。 Step12：出現 [目標搜尋狀態] 之視窗，告訴我們，己求得解答，按﹝確定﹞。 Step13：看到結果，總成績60。 Step14：再到多媒體成績表的J5去瞧瞧，己經變成60.24，也就是必須由原來的30分加油考到60分才可過關。 17-2 建立分析藍本 分析藍本是儲存在工作表中可進行自動置換的一群組值。您可以使用分析藍本來預測工作表模型的輸出結果。 同時還可以在工作表中建立並儲存不同的數值群組，然後切換至任意新分析藍本以檢視不同結果。 Step1：開ch17.xls活頁簿，選取「公司求才比較表」，假設同學們要畢業了，老師給了一組期望待遇，供同學以後求職的參考。資料如下，每月的月薪如果有3萬元，一年可得36萬元，假設每年調薪4%，10年共可得4,322,198.56元！ Step2：執行 [工具/分析藍本] 。 Step3：開啟 [分析藍本管理員]之視窗，[新增]一個藍本。 Step4：假設同學與「台雞店」公司面談有結果，我們就建立一個台雞店藍本，變數儲存格右邊的折疊按紐一下 Step5：選取B2及B4不相鄰的儲存格，按折疊紐回上畫面。 Step6：參數無誤後，按[確定] 。 Step7：輸入每一個變數儲存格的值，假設台雞店提供25000的月薪及每年7%的調薪幅度 Step8：重複Step3到Step7步驟，加入2筆公司的資料，分別為連墊公司，薪資32000，調薪幅度為2%而紅姬公司薪資28000，調薪幅度為6%，新增完畢後可在 [分析藍本管理員]視窗環境下操作，例如選「紅姬」按 [顯示] 就會將原始期望公司待遇的資料蓋掉，出現該筆重新計算的資訊，例如若薪資32000，年薪為 32000*12 = 384000，但調幅度只有2%，所以十年總所得如下。 17-3 編輯分析藍本 Step9：除了[新增]及[顯示]藍本外，尚可[刪除]或[編輯]此藍本，選取「台雞店」，按[編輯]鈕。 Step10：出現[編輯分析藍本]交談窗，按[確定]。 Step11：出現[分析藍本數值]交談窗，可修改數值後，按[確定]鈕即完成編輯藍本功能。 17-4 顯示分析藍本摘要 要比較多種分析藍本，可在同一頁面上建立對這些分析藍本進行摘要的報表。該報表可對比列出各個分析藍本，或在樞紐分析表報表中進行摘要。 Step1：在 [分析藍本管理員] 交談窗中，按一下[摘要]按鈕。 Step2：出現[分析藍本摘要]交談窗，點選[分析藍本摘要]，按[確定]。 Step3：