精通 Excel 中的目標搜尋 – 完整指南與範例

目標搜尋是 Excel 假設分析工具包的一部分，是一項用於反向計算的強大功能。基本上，如果您知道公式想要的結果，但不確定什麼樣的輸入值會產生這個結果，您可以使用目標搜尋來找到它。無論您是財務分析師、學生還是企業主，了解如何使用目標搜尋可以顯著簡化您的問題解決過程。在本指南中，我們將探討什麼是目標搜尋、如何訪問它，並通過各種範例展示其實際應用，從調整貸款還款計劃到計算考試最低分數等等。

• 範例 1：調整貸款還款計劃
• 範例 2：確定考試及格的最低分數
• 範例 3：計算選舉勝利所需的票數
• 範例 4：實現企業的目標利潤

Excel 中的目標搜尋是什麼？

目標搜尋是 Microsoft Excel 的一個重要功能，屬於其假設分析工具套件的一部分。它使用戶能夠進行反向計算——不是根據給定的輸入計算結果，而是指定期望的結果，Excel 調整輸入值以達到該目標。此功能在需要找到能產生特定結果的輸入值的情況下特別有用。

• 單變量調整：
  目標搜尋一次只能更改一個輸入單元格的值。
• 需要公式：
  應用目標搜尋功能時，目標單元格必須包含公式。
• 保持公式的完整性：
  目標搜尋不會改變目標單元格中的公式；它更改的是公式所依賴的輸入單元格的值。

使用 Kutools AI 解鎖 Excel 的魔法

• 智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
• 自訂公式：生成量身定制的公式，簡化您的工作流程。
• VBA 編碼：輕鬆編寫和實現 VBA 代碼。
• 公式解釋：輕鬆理解複雜的公式。
• 文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

在 Excel 中訪問目標搜尋

要訪問目標搜尋功能，請前往「數據」選項卡，點擊「假設分析」>「目標搜尋」。參見截圖：

然後彈出目標搜尋對話框。以下是 Excel 目標搜尋對話框中三個選項的解釋：

• 設定單元格：包含您希望達成的公式的目標單元格。此單元格必須包含公式，目標搜尋將調整另一個相關單元格中的值，使此單元格的值達到您設定的目標。
• 目標值：您希望「設定單元格」達到的目標值。
• 變更單元格：您希望目標搜尋調整的輸入單元格。此單元格中的值將被更改，以確保「設定單元格」中的值符合「目標值」目標。
  
  

在下一節中，我們將通過詳細的範例來探索如何利用 Excel 的目標搜尋功能。

使用目標搜尋的範例

在本節中，我們將通過四個常見範例來展示目標搜尋的實用性。這些範例涵蓋了從個人財務調整到戰略商業決策，提供了如何在各種現實場景中應用此工具的見解。每個範例旨在讓您更清楚地了解如何有效地使用目標搜尋來解決不同類型的問題。

範例 1：調整貸款還款計劃

情境：一個人計劃借貸 20,000 美元，並打算在 5 年內以每年 5% 的利率償還。每月需還款 377.42 美元。後來，他意識到自己可以將每月還款額增加到 500 美元。假設總貸款金額和年利率保持不變，現在需要多少年才能還清貸款？

如下表所示：

• B1 包含貸款金額 20000 美元。
• B2 包含貸款期限 5（年）。
• B3 包含年利率 5%。
• B5 包含由公式 =PMT(B3/12,B2*12,B1) 計算得出的每月還款額。
  
  

接下來我將展示如何使用目標搜尋功能完成此任務。

步驟 1：啟動目標搜尋功能

前往「數據」選項卡，點擊「假設分析」>「目標搜尋」。

步驟 2：配置目標搜尋設置

1. 在「設定單元格」框中，選擇公式 B5。
2. 在「目標值」框中，輸入每月還款金額 -500（負數表示支付）。
3. 在「變更單元格」框中，選擇您想調整的單元格 B2。
4. 點擊「確定」。
   
   

結果

隨後將出現目標搜尋狀態對話框，顯示已找到解決方案。同時，您在「變更單元格」框中指定的單元格 B2 將被替換為新值，並且公式單元格將根據變量變化獲得目標值。點擊「確定」以應用更改。

現在，他將需要約 3.7 年來還清貸款。

範例 2：確定考試及格的最低分數

情境：一名學生需要參加 5 次考試，每次考試權重相同。為了及格，學生必須在所有考試中平均得分達到 70%。已經完成了前 4 次考試並知道他們的分數，學生現在需要計算第五次考試所需的最低分數，以確保整體平均分達到或超過 70%。

如下截圖所示：

• B1 包含第一次考試的分數。
• B2 包含第二次考試的分數。
• B3 包含第三次考試的分數。
• B4 包含第四次考試的分數。
• B5 將包含第五次考試的分數。
• B7 是通過公式 =(B1+B2+B3+B4+B5)/5 計算得出的及格平均分。
  
  

要實現這一目標，您可以按照以下方式應用目標搜尋功能：

步驟 1：啟動目標搜尋功能

前往「數據」選項卡，點擊「假設分析」>「目標搜尋」。

步驟 2：配置目標搜尋設置

1. 在「設定單元格」框中，選擇公式單元格 B7。
2. 在「目標值」框中，輸入及格平均分 70%。
3. 在「變更單元格」框中，選擇您想調整的單元格 (B5)。
4. 點擊「確定」。
   
   

結果

隨後將出現目標搜尋狀態對話框，顯示已找到解決方案。同時，您在「變更單元格」框中指定的單元格 B7 將自動填入匹配的值，並且公式單元格將根據變量變化獲得目標值。點擊「確定」接受解決方案。

因此，為了達到所需的整體平均分並通過所有考試，學生在最後一次考試中至少需要得到 71 分。

範例 3：計算選舉勝利所需的票數

情境：在一場選舉中，候選人需要計算確保多數票所需的最低票數。已知投票總數，候選人必須獲得多少票才能超過 50% 並獲勝？

如下截圖所示：

• 單元格 B2 包含選舉中的總投票數。
• 單元格 B3 顯示候選人目前獲得的票數。
• 單元格 B4 包含候選人希望實現的多數票百分比 (%)，由公式 =B2/B1 計算得出。
  
  

要實現這一目標，您可以按照以下方式應用目標搜尋功能：

步驟 1：啟動目標搜尋功能

前往「數據」選項卡，點擊「假設分析」>「目標搜尋」。

步驟 2：配置目標搜尋設置

1. 在「設定單元格」框中，選擇公式單元格 B4。
2. 在「目標值」框中，輸入所需的多數票百分比。在此情況下，要贏得選舉，候選人必須獲得超過一半（即超過 50%）的有效票數，所以我輸入 51%。
3. 在「變更單元格」框中，選擇您想調整的單元格 (B2)。
4. 點擊「確定」。
   
   

結果

隨後將出現目標搜尋狀態對話框，顯示已找到解決方案。同時，您在「變更單元格」框中指定的單元格 B2 將自動替換為新值，並且公式單元格將根據變量變化獲得目標值。點擊「確定」以應用更改。

因此，候選人需要至少 5100 票才能贏得選舉。

範例 4：實現企業的目標利潤

情境：一家公司希望在當年實現 150,000 美元的特定利潤目標。考慮到固定成本和可變成本，他們需要確定必要的銷售收入。需要銷售多少單位才能達到這個利潤目標？

如下截圖所示：

• B1 包含固定成本。
• B2 包含每單位的可變成本。
• B3 包含每單位的價格。
• B4 包含售出的單位數。
• B6 是總收入，由公式 =B3*B4 計算得出。
• B7 是總成本，由公式 =B1+(B2*B4) 計算得出。
• B9 是當前銷售利潤，由公式 =B6-B7 計算得出。
  
  
  </li

要根據目標利潤獲得總銷售單位數，您可以按照以下方式應用目標搜尋功能。

步驟 1：啟動目標搜尋功能

前往「數據」選項卡，點擊「假設分析」>「目標搜尋」。

步驟 2：配置目標搜尋設置

1. 在「設定單元格」框中，選擇返回利潤的公式單元格。這裡是單元格 B9。
2. 在「目標值」框中，輸入目標利潤 150000。
3. 在「變更單元格」框中，選擇您想調整的單元格 (B4)。
4. 點擊「確定」。
   
   

結果

隨後將出現目標搜尋狀態對話框，顯示已找到解決方案。同時，您在「變更單元格」框中指定的單元格 B4 將自動替換為新值，並且公式單元格將根據變量變化獲得目標值。點擊「確定」接受更改。

因此，公司需要至少銷售 6666 單位才能實現其 150,000 美元的目標利潤。

目標搜尋的常見問題及解決方案

本節列出了目標搜尋的一些常見問題及其相應的解決方案。

1. 目標搜尋無法找到解決方案

• 原因：這通常發生在目標值無法通過任何可能的輸入值實現，或者初始猜測值與任何潛在解決方案相差太遠的情況下。
• 解決方案：調整目標值以確保其在可行範圍內。此外，嘗試不同的初始猜測值，使其更接近預期的解決方案。確保「設定單元格」中的公式正確且邏輯上能夠產生所需的「目標值」。

2. 計算速度慢

• 原因：對於大型數據集、複雜公式或當初始猜測值與解決方案相差較遠時，目標搜尋可能會很慢，需要更多迭代。
• 解決方案：盡可能簡化公式並減少數據規模。確保初始猜測值盡可能接近預期解決方案，以減少所需的迭代次數。

3. 精度問題

• 原因：使用目標搜尋時，您可能會注意到有時候 Excel 會說找到了解決方案，但它並不是您想要的——接近但不夠準確。由於 Excel 的計算精度和四捨五入，目標搜尋可能無法始終提供高度準確的結果。
  
  例如，計算某數字的特定乘方並使用目標搜尋來找到能達到所需結果的底數。這裡我將更改單元格 A1（底數）中的輸入值，以便單元格 A3（乘方結果）匹配目標結果 25。
  
  正如您在下面的截圖中看到的，目標搜尋提供了一個近似值而不是精確的根。
  
• 解決方案：在 Excel 的計算選項中增加小數位數以提高精度，然後重新運行目標搜尋功能。請按以下步驟操作。
  
  1. 點擊「文件」>「選項」打開Excel 選項窗口。
  2. 在左側窗格中選擇「公式」，在「計算選項」部分，增加「最大變化」框中的小數位數。這裡我將 0.001 改為 0.000000001，然後點擊「確定」。
     
  3. 重新運行目標搜尋，您將獲得如下面截圖所示的精確根。
     

4. 局限性和替代方法

• 原因：目標搜尋只能調整一個輸入值，可能不適合需要同時調整多個變量的方程。
• 解決方案：對於需要調整多個變量的情況，使用 Excel 的求解器，它功能更強大且允許設置約束條件。

目標搜尋是一個強大的工具，可用於執行反向計算，免去了手動測試不同值以逼近目標的繁瑣工作。有了本文提供的見解，您現在可以自信地應用目標搜尋，將您的數據分析和效率提升到新的高度。對於那些渴望深入了解 Excel 功能的人，我們的網站擁有豐富的教程。在這裡發現更多 Excel 技巧和竅門。