如何使用 Microsoft Excel 中的假設分析工具

當您在 Microsoft Excel 中分析數據時，您可能想要進行一些比較，例如“如果我選擇選項 A 而不是選項 B 會怎麼樣？” 使用 Excel 中內置的假設分析工具，您可以更輕鬆地比較數字和金額 – 例如，評估工作薪資、貸款選項或收入和支出方案。

Excel 中的假設分析工具包括場景管理器、目標搜索和數據表。為了更好地解釋這些工具的用途，讓我們看一下每個工具的示例。

場景經理

使用場景管理器，輸入可以更改的值以查看不同的結果。作為獎勵，創建一個場景摘要報告來並排比較金額或數字。

例如，假設您正在計劃一項活動並在幾個具有不同成本的主題之間做出決定。設置每個主題的價格，看看它們的成本是多少，然後進行比較。

針對不同情況創建各種場景以幫助您做出決策。

如何使用場景管理器

如果您準備好比較不同的情況（如上面的示例），請按照以下步驟使用 Excel 中的方案管理器。

• 在工作表中輸入第一個方案的數據。使用前面的示例，我們比較活動的主題成本，並在單元格 A2 到 A6 中輸入海灘主題的費用，並在單元格 B2 到 B6 中輸入其成本。我們將單元格 B7 中的價格相加，以查看總成本。

• 要將這些詳細信息添加到場景管理器，請轉到功能區的“數據”選項卡和“預測”部分。打開“假設分析”下拉菜單，然後選擇“場景管理器”。

• 單擊“添加”。

• 為您的場景命名（我們使用“海灘主題”），然後在“更改單元格”字段中輸入您要調整的單元格。或者，將光標拖過工作表中的單元格以填充該字段。或者，輸入默認值以外的註釋，然後單擊“確定”。

• “更改單元格”字段中的值應與工作表上的值匹配，但您可以在此處調整它們。單擊“確定”繼續。

• 現在您已經添加了第一個場景，您將看到它列在場景管理器中。選擇“添加”以設置您的下一個場景。

• 像第一個場景一樣輸入第二個場景的詳細信息。包括名稱、更改的單元格和可選註釋，然後單擊“確定”。在我們的示例中，我們輸入“Vegas Theme”和相同的單元格範圍（B2 到 B6），以便輕鬆查看就地比較。

• 在“方案值”窗口中輸入第二個方案的值。如果您使用與第一個單元格相同的單元格，您將看到這些單元格已填充。輸入您想要使用的內容，然後單擊“確定”。

• 從“場景管理器”窗口的列表中選擇要查看的場景，然後單擊“顯示”。

• 工作表中的值將更新以顯示所選方案。

• 繼續添加並顯示其他方案以查看工作表中的更新值。找到要保留在工作表中的內容後，選擇“關閉”退出場景管理器。

查看場景摘要

查看場景摘要以立即查看所有場景以進行並排比較。

• 返回“數據 -> 假設分析 -> 場景管理器”，然後單擊“摘要”。

• 選擇您要查看的報告類型：“方案摘要”或“方案數據透視表報告”。或者，如果您想顯示結果，請輸入包含該結果的單元格，然後單擊“確定”。

在我們的示例中，我們選擇“方案摘要”，將報告放置在新的工作表選項卡中。您還會注意到，報告可以選擇包含單元格分組以隱藏報告的某些部分。

請注意，如果您在方案管理器中調整詳細信息，報告不會自動更新，因此您必須生成新報告。

目標尋求

目標搜索工具的工作方式與場景管理器有些相反。使用此工具，您可以獲得已知的結果，並輸入不同的變量來查看如何達到該結果。

例如，也許您銷售產品並有年度利潤目標。您想知道需要出售多少單位或以什麼價格才能達到目標。Goal Seek 是尋找答案的理想工具。

對於 Goal Seek，只能使用一個變量或輸入值。對於您預先擁有剩餘值的情況，請使用此選項。

如何使用目標尋求

在 Goal Seek 工具的示例中，我們有 1,500 種產品要銷售，並且希望賺取 52,000 美元的利潤。我們使用 Goal Seek 來確定我們應該以什麼價格銷售我們的產品以實現該目標。

• 首先根據您的場景在工作表中輸入值和公式。在我們的示例中，我們在單元格 B2 中輸入當前數量，在單元格 B3 中輸入估計價格，並在單元格 B4 中輸入利潤公式，即=B2*B3。

• 轉到“數據”選項卡，打開“假設分析”下拉菜單，然後選擇“目標尋求”。

• 輸入以下值，然後單擊“確定”：
  • 設置單元格：您要更改值以達到所需結果的單元格引用（包含公式）。在我們的示例中，這是單元格 B4。
  • 價值：您想要的結果的價值。對於我們來說，這是 52000。
  • 通過更改單元格：要更改的單元格引用以達到結果。我們正在使用單元格 B3，因為我們想要更改價格。

• 單擊“確定”查看“目標尋求狀態”框更新以顯示解決方案，並且工作表更改為包含調整後的值。在我們的示例中，我們必須以 35 美元的價格出售產品才能達到 52,000 美元的目標。選擇“確定”以在工作表中保留新值。

您知道嗎：您可以在 Microsoft Excel 中執行很多操作，包括插入迷你圖和迷你圖表。

數據表

使用 Excel 中的數據表查看一系列可能的數字情況。

舉一個理想的例子，您可能正在審查貸款選項。通過輸入不同的利率，您可以查看每種利率的每月還款額。這可以幫助您確定購買的利率或與貸方討論的利率。

對於數據表，您最多只能使用兩個變量。如果您需要更多，請使用場景管理器。

如何使用數據表

請按照以下步驟使用數據表（第三個假設分析工具）。請注意數據設置。

例如，我們使用數據表通過以下數據查看不同利率下我們的貸款支付金額：

• 單元格 B3 至 B5 中的利率、還款次數和貸款金額。
• 利率列，其中包含單元格 C3 至 C5 中要探索的利率。
• 單元格 D2 中包含當前付款公式的付款列。
• 付款列中公式下方的結果單元格是使用數據表工具自動輸入的。這向我們顯示了每個利率的付款金額。

在工作表中輸入數據和公式時，請記住以下幾點：

• 使用面向行或列的佈局。它將決定您的公式的位置。
• 對於面向行的佈局，請將公式放置在初始值左側一列的單元格中以及包含值的行下方的一個單元格中。
• 對於面向列的佈局，請將公式放置在包含值的列上方一行的單元格和右側的一個單元格中。

在我們的示例中，我們在面向列的佈局中使用單變量（利率）。請注意我們的公式在單元格 D2 中的位置（我們的值的上方一行和右側的一個單元格）。

• 輸入您自己的數據並選擇包含公式、值和結果單元格的單元格。在我們的示例中，我們選擇單元格 C2 到 D5。

• 轉到“數據”選項卡，打開“假設分析”下拉菜單，然後選擇“數據表”。

• 在數據錶框中輸入包含數據變化變量的單元格。對於面向行的佈局，請使用“行輸入單元格”，對於面向列的佈局，請使用“列輸入單元格”。在我們的示例中，我們使用後者並輸入“B3”，這是包含利率的單元格。

• 在“數據表”框中單擊“確定”後，您應該看到結果單元格填充了您期望的數據。我們的示例包括每個不同利率的付款金額。

請注意，您可以在數據表中使用兩個變量而不是一個，嘗試面向行的佈局，或者在Microsoft 的該功能支持頁面上查看此假設分析工具的更多詳細信息和限制。

經常問的問題

如何在 Excel 中編輯現有方案？

您可以使用場景管理器更改場景的名稱和值。通過選擇“數據 -> 假設分析 -> 場景管理器”打開該工具。從列表中選擇場景，然後單擊右側的“編輯”。進行更改，然後選擇“確定”保存它們。

如果您最初創建了場景摘要報告，則需要重新生成報告才能查看更新的詳細信息。

我可以阻止 Excel 重新計算數據表嗎？

如果您的工作簿包含數據表，Excel 會自動重新計算該數據表，即使沒有任何更改。但是，如果您願意，可以關閉此選項。

轉到“公式”選項卡，打開“計算”組中的“計算選項”下拉菜單，然後選擇“除數據表外自動”。

要手動重新計算數據表，請選擇公式並按F9。

Excel 還提供哪些其他分析工具？

Excel 提供了許多不同類型的數據分析工具，具體取決於您的需要。僅舉幾例，您可以使用條件格式來突出顯示特定數據，使用快速分析來格式化、圖表和表格，以及使用Power Query來進行可靠的數據分析。

您還可以使用基本的Excel 功能，例如用於縮小數據范圍的過濾器、用於過濾表格和圖表的切片器以及用於獲取數據問題答案的分析數據工具。

如需有關這些功能及其他功能的更多幫助，請轉到 Windows 上 Excel 中的“幫助”選項卡，或使用 Mac 上 Excel 中的“告訴我”菜單選項。

圖片來源：Pixabay。所有屏幕截圖均由 Sandy Writtenhouse 製作。