掌握 Excel 中的目標尋求 – 帶有範例的完整指南
Goal Seek 是 Excel 假設分析工具包的一部分,是用於向後計算的強大功能。本質上,如果您知道公式所需的結果,但不確定什麼輸入值會產生此結果,則可以使用 Goal Seek 來查找它。無論您是財務分析師、學生還是企業主,了解如何使用 Goal Seek 都可以顯著簡化您的問題解決流程。在本指南中,我們將探討 Goal Seek 是什麼、如何存取它,並透過各種範例來展示其實際應用,從調整貸款支付計劃到計算考試最低分數等。
Excel 中的目標搜尋是什麼?
目標尋求 是 Microsoft Excel 的一個不可或缺的功能,也是其假設分析工具套件的一部分。它使用戶能夠執行反向計算 - 您指定所需的結果,而不是計算給定輸入的結果,Excel 會調整輸入值以實現該目標。在您需要尋找將產生特定結果的輸入值的情況下,此功能特別有用。
- 單變量調節:
Goal Seek 一次只能變更一個輸入儲存格值。 - 需要一個公式:
應用目標搜尋功能時,目標儲存格必須包含公式。 - 保持公式的完整性:
Goal Seek 不會改變目標儲存格中的公式;它會更改公式所依賴的輸入儲存格中的值。
在 Excel 中存取目標搜尋
若要存取「目標尋求」功能,請前往 數據 標籤,點擊 假設分析 > 目標尋求。 看截圖:
然後, 目標尋求 彈出對話框。以下是 Excel 的「目標尋求」對話方塊中三個選項的說明:
- 設置單元格:包含您要實現的公式的目標儲存格。該儲存格必須包含公式,Goal Seek 將調整另一個相關儲存格中的值,以使該儲存格的值達到您設定的目標。
- 重視價值:您想要的目標值“設置單元格” 來達到。
- 透過更換電池:您希望 Goal Seek 調整的輸入儲存格。該單元格中的值將被更改以確保“設置單元格“滿足”重視價值“ 目標。
在下一節中,我們將透過詳細範例探討如何利用 Excel 的 Goal Seek 功能。
使用目標尋求範例
在本節中,我們將透過四個常見範例來展示 Goal Seek 的實用性。這些範例涵蓋從個人財務調整到策略性業務決策,提供有關如何將該工具應用於各種現實場景的見解。每個範例旨在讓您更清楚地了解如何有效地使用 Goal Seek 來解決不同類型的問題。
範例 1:調整貸款還款計劃
<span class="notranslate">EventXtra 6大解決方案</span>:某人計劃申請一筆 20,000 美元的貸款,並計劃在 5 年內以 5% 的年利率償還。每月所需還款額為 377.42 美元。後來,他意識到他可以將每月還款額增加 500 美元。在貸款總額和年利率不變的情況下,現在需要幾年才能還清貸款?
如下表所示:
- B1 包含貸款金額 $20000。
- B2包含貸款期限5(年)。
- B3包含年利率5%。
- B5包含以公式=PMT(B3/12,B2*12,B1)計算的每月付款。
這裡我將向您展示如何使用Goal Seek 功能來完成此任務。
第 1 步:啟動目標尋求功能
轉到 數據 標籤,點擊 假設分析 > 目標尋求.
第 2 步:配置目標尋求設定
- 在 設置單元格 框中,選擇公式 B5。
- 在 重視 框中輸入每月計畫還款金額-500(負數代表還款)。
- 在 通過改變細胞 在方塊中,選取要調整的儲存格 B2。
- 點擊 OK.
結果
目標尋求狀態 然後會出現對話框,顯示已找到解決方案。同時,您在「透過變更儲存格」方塊中指定的儲存格 B2 中的值將被替換為新值,並且公式儲存格將根據變數變更取得目標值。點選 OK 應用更改
現在,他大約需要3.7年才能還清貸款。
範例 2:確定通過考試的最低分數
<span class="notranslate">EventXtra 6大解決方案</span>:一名學生需要參加 5 場考試,每場考試的權重相等。要通過考試,學生所有考試的平均成績必須達到 70%。完成前 4 次考試並了解分數後,學生現在需要計算第五次考試所需的最低分數,以確保總體平均分數達到或超過 70%。
如下截圖所示:
- B1包含第一次考試的分數。
- B2包含第二次考試的分數。
- B3包含第三次考試的分數。
- B4包含第四次考試的分數。
- B5將包含第五次考試的分數。
- B7為及格平均分數,計算公式=(B1+B2+B3+B4+B5)/5。
為了實現這一目標,您可以如下套用目標搜尋功能:
第 1 步:啟動目標尋求功能
轉到 數據 標籤,點擊 假設分析 > 目標尋求.
第 2 步:配置目標尋求設定
- 在 設置單元格 方塊中,選擇公式儲存格 B7。
- 在 重視 框中,輸入及格平均分數 70%。
- 在 通過改變細胞 在方塊中,選取要調整的儲存格 (B5)。
- 點擊 OK.
結果
目標尋求狀態 然後會出現對話框,顯示已找到解決方案。同時,您在「透過變更儲存格」方塊中指定的儲存格 B7 將自動輸入符合的值,且公式儲存格將根據變數變更取得目標值。點選 OK 接受解決方案。
因此,要達到所需的總平均分數並通過所有考試,學生需要在最後一次考試中獲得至少 71% 的分數。
範例 3:計算選舉勝利所需的票數
<span class="notranslate">EventXtra 6大解決方案</span>:在選舉中,候選人需要計算獲得多數票所需的最低票數。在已知總票數的情況下,候選人最少必須獲得多少票才能獲得 50% 以上的票數並獲勝?
如下截圖所示:
- 儲存格 B2 包含選舉中投票的總數。
- 儲存格 B3 顯示候選人目前收到的票數。
- 儲存格 B4 包含候選人希望獲得的所需多數票 (%),其計算公式為 =B2/B1。
為了實現這一目標,您可以如下套用目標搜尋功能:
第 1 步:啟動目標尋求功能
轉到 數據 標籤,點擊 假設分析 > 目標尋求.
第 2 步:配置目標尋求設定
- 在 設置單元格 方塊中,選擇公式儲存格 B4。
- 在 重視 框中,輸入所需的多數票百分比。在這種情況下,候選人要贏得選舉,必須獲得有效票總數的一半以上(即50%以上),所以我輸入51%。
- 在 通過改變細胞 在方塊中,選取要調整的儲存格 (B2)。
- 點擊 OK.
結果
目標尋求狀態 然後會出現對話框,顯示已找到解決方案。同時,您在「透過變更儲存格」方塊中指定的儲存格 B2 將自動替換為新值,且公式儲存格將根據變數變更取得目標值。點選 OK 應用更改。
因此,候選人至少需要 5100 票才能贏得選舉。
範例 4:實現企業目標利潤
<span class="notranslate">EventXtra 6大解決方案</span>:某公司本年度的具體利潤目標為 150,000 美元。考慮到固定成本和變動成本,他們需要確定必要的銷售收入。需要多少銷售量才能達到這個利潤目標?
如下截圖所示:
- B1包含固定成本。
- B2 包含每單位的變動成本。
- B3 包含每單位的價格。
- B4 包含已售出的單位。
- B6為總收入,計算公式=B3*B4。
- B7為總成本,計算公式=B1+(B2*B4)。
- B9為當期銷售利潤,計算公式=B6-B7。
若要根據目標利潤取得總銷售單位,您可以套用「目標搜尋」功能,如下所示。
第 1 步:啟動目標尋求功能
轉到 數據 標籤,點擊 假設分析 > 目標尋求.
第 2 步:配置目標尋求設定
- 在 設置單元格 在方塊中,選擇返回利潤的公式儲存格。這是儲存格 B9。
- 在 重視 框中,輸入目標利潤 150000。
- 在 通過改變細胞 在方塊中,選取要調整的儲存格 (B4)。
- 點擊 OK.
結果
然後將出現「目標尋求狀態」對話框,顯示已找到解決方案。同時,您在「透過變更儲存格」方塊中指定的儲存格 B4 將自動替換為新值,且公式儲存格將根據變數變更取得目標值。點選 OK 接受更改。
因此,該公司需要至少銷售 6666 台才能實現 150,000 萬美元的目標利潤。
Goal Seek 的常見問題和解決方案
本節列出了 Goal Seek 的一些常見問題和相應的解決方案。
1. 目標尋求找不到解決方案
- 原因:當使用任何可能的輸入值都無法實現目標值,或者初始猜測與任何潛在解決方案相差太遠時,通常會發生這種情況。
- 解決方案:調整目標值,確保其在可行範圍內。另外,嘗試更接近預期解決方案的不同初始猜測。確保“設定單元格”中的公式正確並且可以邏輯地產生所需的“目標值”。
2.計算速度慢
- 原因:對於大型資料集、複雜的公式,或當初始猜測與解決方案相差甚遠時,目標搜尋可能會很慢,需要更多迭代。
- 解決方案:盡可能簡化公式並減少資料量。確保初始猜測盡可能接近預期解,以減少所需的迭代次數。
3. 準確性問題
- 原因:使用目標查找時,您可能會注意到有時 Excel 會說它找到了解決方案,但這並不完全是您想要的 - 接近但還不夠接近。由於 Excel 的計算精度和舍入,Goal Seek 可能並不總是提供高度準確的結果。
例如,計算某個數字的特定冪並使用 Goal Seek 找出實現所需結果的底數。在這裡,我將更改單元格 A1(基數)中的輸入值,以便單元格 A3(求冪結果)與目標結果 25 相符。正如您在下面的螢幕截圖中看到的,Goal Seek 提供了一個近似值而不是精確的根。 - 解決方案:增加 Excel 計算選項中顯示的小數位數以獲得更高的準確性,然後重新執行「目標搜尋」功能。請按以下步驟操作。
- 點擊 文件 > 選項 打開 Excel選項 窗口。
- 選擇 公式 在左窗格中,然後在 計算選項 部分,增加小數位數 最大變化 盒子。這裡我改變一下 0.001 至 0.000000001 並點擊 OK.
- 重新運行 Goal Seek,您將獲得確切的根,如下面的螢幕截圖所示。
4. 限制和替代方法
- 原因:Goal Seek 只能調整一個輸入值,可能不適用於需要同時調整多個變數的方程式。
- 解決方案:對於需要調整多個變數的場景,可以使用Excel的Solver,功能更強大,可以設定約束。
Goal Seek 是執行反向運算的強大工具,可讓您免於手動測試不同值以近似目標的繁瑣任務。有了本文的見解,您現在可以自信地應用 Goal Seek,將您的數據分析和效率推向新的高度。對於那些渴望深入研究 Excel 功能的人,我們的網站擁有豐富的教學。 在這裡了解更多 Excel 提示和技巧.
最佳辦公生產力工具
🤖 | Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行 | 生成代碼 | 建立自訂公式 | 分析數據並產生圖表 | 呼叫 Kutools 函數... |
熱門特色: 尋找、突出顯示或識別重複項 | 刪除空白行 | 合併列或儲存格而不遺失數據 | 沒有公式的回合 ... | |
超級查詢: 多條件VLookup | 多值VLookup | 跨多個工作表的 VLookup | 模糊查詢 .... | |
高級下拉列表: 快速建立下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
欄目經理: 新增特定數量的列 | 移動列 | 切換隱藏列的可見性狀態 | 比較範圍和列 ... | |
特色功能: 網格焦點 | 設計圖 | 大方程式酒吧 | 工作簿和工作表管理器 | 資源庫 (自動文字) | 日期選擇器 | 合併工作表 | 加密/解密單元格 | 按清單發送電子郵件 | 超級濾鏡 | 特殊過濾器 (過濾粗體/斜體/刪除線...)... | |
前 15 個工具集: 12 文本 工具 (添加文本, 刪除字符,...) | 50+ 圖表 類型 (甘特圖,...) | 40+ 實用 公式 (根據生日計算年齡,...) | 19 插入 工具 (插入二維碼, 從路徑插入圖片,...) | 12 轉化 工具 (數字到單詞, 貨幣兌換,...) | 7 合併與拆分 工具 (高級合併行, 分裂細胞,...) | ... 和更多 |
使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。 點擊此處獲取您最需要的功能...
Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆
- 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!