使用試算表「目標搜尋」達到您想要的結果
「目標搜尋」是試算表中的實用功能,可以幫助您找到達成目標結果所需要的輸入值。無論您是在建立財務模型或是商務分析,「目標搜尋」可以節省您的時間,提高決策能力。
試算表中的「目標搜尋」是什麼?
如果您心中有一個目標值,並需要求出相應的「輸入值」以滿足該目標,可以使用試算表的「目標搜尋」工具。它會回推調整輸入值,以達到您指定的目標值。「目標搜尋」尤其適用於建立財務模型、商業分析或解決其他複雜問題。例如,「目標搜尋」可以告訴您達到利潤目標的銷售預測、求出考試及格分數、或者贏得選舉所需的選票數量等。
如何在試算表中使用「目標搜尋」?
在試算表中使用「目標搜尋」非常簡單直覺,您只需定義以下三種參數:
• 要運算的公式
• 目標值
• 要達成目標的輸入值變數
請注意:「目標搜尋」一次只能尋找一個輸入值。
我們將透過以下範例教您如何使用「目標搜尋」。
範例:貸款還款計算
假設您想要每個月支付1,500元,並想知道需要多少個月的貸款期限才能完全還清貸款。
1. 設定您需要的「公式」、「目標值」 和「輸入值變數」儲存格數據。
在這個例子中,公式儲存格(B5)使用PMT函數來計算每月支付金額。
=PMT(B3/12,B2,B1)
2. 到功能區的「資料」分頁,點擊「模擬分析」,然後選擇「目標搜尋」。
3.在「目標搜尋」彈窗中,選擇對應儲存格,並輸入您要尋找的目標值。
• 公式儲存格:B5(每月付款金額的計算公式)
• 要尋找的值:-1,500(您希望達到的每月付款金額)
備註:因為是支出,所以要用負數。
• 依變數儲存格:B2(月數期限輸入值)
4. 執行「目標搜尋」並找到運算結果。「依變數儲存格」中的值會被運算出來的新值直接取代。點擊「OK」接受新值,或點擊「取消」恢復原始值。
在此範例中,「目標搜尋」幫您找出了答案,您需要還款82個月(四捨五入到整數第一位)才能滿足想要每月支付1,500美元這個目標。
如何提高「目標搜尋」準確度
「目標搜尋」透過反覆運算逼近法來尋找所需的結果,因此運算結果不一定百分之百準確。您可以在試算表中調整反覆運算設定,以提升運算準確性。
您可以到功能區「檔案」分頁,點擊「選項」,然後選擇「公式」。勾選「反覆運算」並調整其設定,以取得更加準確的運算結果。
• 提高「最高次數」以增加反覆運算次數。
• 降低「最大變更」以減少反覆運算結果之間可接受的最大誤差。例如,您可以把預設的0.01改成0.0000001。
「目標搜尋」常見問題與解法
有時,「目標搜尋」可能無法產生準確的結果或無法收斂。以下是一些常見原因:
• 循環引用:請確保您的工作表中沒有循環引用,因為「目標搜尋」無法處理循環依賴關係。
• 公式複雜:簡化複雜公式或將其拆解為較小步驟,以提高目標搜尋的準確性。
• 數據限制:檢查您輸入的數據是否符合現實範圍,不實際或極端值可能導致無法收斂。