錦子老師

使用目標搜尋藉由調整輸入值來找出想要的結果

如果我們建立一個運算模型，並且利用公式計算出結果，可是結果並不一定是我們理想中的值，若希望結果值達到我們理想中的值，並想知道與結果有關聯性的某一公式所在儲存格中值會變成怎樣，這時可以藉由「目標搜尋」功能來進行，即俗稱的「反推運算」。

例如，假設我們需要向銀行借一筆錢購屋。我們知道自己想要借多少錢、需要多久的時間才能還清貸款，以及每個月可以支付的金額。此時可以使用「目標搜尋」來運算出符合貸款目標的利率。

附註：目標搜尋只可以使用運算一個變數。如果想要接受一個以上的輸入值，例如，貸款金額及每月支付金額，則可以使用「規劃求解」增益集。

由於想要計算符合我們期望的每月付款金額，故使用了PMT函數。PMT函數會計算每月支付金額。在此例中，每月支付金額(期付款)就是我們所尋找的目標。

建造運算模型

01    開啟一個新的空白工作表。

02    首先，在第一欄中加入一些標籤，使工作表的內容易於閱讀。

在儲存格A2中，輸入「貸款金額」。

在儲存格A3中，輸入「貸款年數」。

在儲存格A4中，輸入「貸款期數」。

在儲存格A5中，輸入「年利率」。

在儲存格A6中，輸入「月利率」。

在儲存格A7中，輸入「期付款」。

03    接下來，請加入您知道的數值。

在儲存格B2中，輸入「3000000」。這是我們向銀行借款金額。

在儲存格B3中，輸入「30」。這是預計要還款的年數。

在儲存格B4中，輸入「=B2*12」公式。這是預計要還款的期數。

在儲存格B5中，輸入「2.8%」。這是預計銀行要給的年利率。

在儲存格B6中，輸入「=B5*12」公式。這是預計要還款的年數。

在儲存格B7中，輸入目標值的公式「=PMT(B6,B4,B2)」，此公式會計算出每月付款金額，在此例中，每個月需要支付12326.83元。

因為想要使用「目標搜尋」來決定貨款年數，而「目標搜尋」需要以公式啟動。此公式參照儲存格B2、B4與B6，其中包含我們在先前步驟中指定的數值。

使用目標搜尋來決定利率

我們想要依照目前運算的結果，求出若我們每月付款9000元，付時才可付清貨款？

01    點取「資料Æ資料工具Æ模擬分析Æ目標搜尋」，開啟「目標搜尋」對話方塊。

02    點取「目標儲存格」欄位，輸入含有我們想要求解其公式(公式:儲存格中共同產生新值的一系列值、儲存格參照、名稱、函數或運算子。公式必定以等號(=)開頭。)的儲存格參照。就本範例而言，此儲存格參照為B7。

03    點取「目標值」欄位，輸入我們想要的公式結果。此例中為-9000。請注意，此數字為負數是因為代表付款。

04    點取「變數儲存格」欄位，輸入含有我們想要調整之數值的儲存格參照。就本範例而言，此儲存格參照為B3。

附註：在「目標儲存格」欄位中所指定之儲存格內的公式必須能夠參照到「目標搜尋」的「變數儲存格」欄位。

05    點取「確定」鈕，如果可以求得解答會出現如下圖對話方塊。

06    點取「確定」鈕，產生如下圖所示的結果。

哇！一個月少付3326元(約佔1/3)，卻要多奮鬥23.78年(1.8倍)左右，真是恐佈。