當您有一個依賴多個變數的複雜公式並且想要了解更改這些輸入如何有效地影響結果時，Excel 中的假設分析資料表是一個強大的工具。它使您可以快速瀏覽所有可能的結果。以下是有關如何在 Excel 中建立資料表的逐步指南。此外，我們將討論有效使用資料表的一些關鍵點，並示範其他操作，例如刪除、編輯和重新計算資料表。

Excel中的資料表是什麼？

建立單變量資料表

• 面向列的資料表
• 面向行的資料表
• 一變量資料表的多個公式

建立二變量資料表

使用數據表的要點

使用資料表的其他操作

• 刪除資料表
• 編輯資料表結果
• 手動重新計算數據表

Excel中的資料表是什麼？

在 Excel 中，資料表是假設分析工具之一，可讓您試驗公式的不同輸入值並觀察公式輸出的變化。該工具對於探索各種場景和進行敏感度分析非常有用，特別是當公式依賴多個變數時。

Excel中有兩種類型的資料表：

一變量資料表：這使您可以分析一個變數的不同值將如何影響公式。您可以採用面向行或面向列的格式設定單變量資料表，這取決於您是要在行中還是在列中變更輸入。

二變量資料表：此類型可讓您查看更改兩個不同變數對公式結果的影響。在雙變量資料表中，您可以改變行和列上的值。

建立單變量資料表

在 Excel 中建立單變量資料表是一項寶貴的技能，可用於分析單一輸入的變化如何影響各種結果。本節將引導您完成建立面向列、面向行和多公式一變量資料表的過程。

驚人的！ 在 Excel 中使用高效的選項卡，如 Chrome、Edge、Firefox 和 Safari！
每天節省50％的時間，並減少數千次鼠標單擊！

---

面向列的資料表

當您想要測試單一變數的不同值如何影響公式的輸出（變數值在一列中列出）時，面向列的資料表非常有用。讓我們考慮一個簡單的財務範例：

假設您正在考慮一筆 50,000 美元的貸款，您計劃在 3 年（相當於 36 個月）內償還。為了根據您的薪資評估每月還款額是可以承受的，您有興趣探索不同的利率將如何影響您每月需要支付的金額。

第 1 步：設定基本公式

為了計算還款額，這裡我將利息設定為5%。在儲存格 B4 中，輸入 PMT 公式，它根據利率、期數和貸款金額計算每月還款。看截圖：

= -PMT($B$1/12, $B$2*12, $B$3)

步驟 2：在一欄列出利率

在一欄中列出您要測試的不同利率。例如，在一列中以 4% 為增量列出從 11% 到 1% 的值，並在右側至少留出一個空白列作為結果。看截圖：

第三步：建立資料表

1. 在儲存格 E2 中，輸入以下公式： = B4.
   
   備註: B4 是主公式所在的儲存格，您希望看到該公式的結果隨著利率的變化而變化。
   
2. 選擇包含公式、利率清單和結果相鄰儲存格的範圍（例如，選擇 D2 到 E10）。看截圖：
   
3. 轉到功能區，按一下 數據 標籤，然後單擊 假設分析 > 數據表，請參見屏幕截圖：
   
4. 在 數據表 對話框，點擊 列 輸入儲存格 方塊（因為我們使用一列作為輸入值），然後選擇公式中引用的變數儲存格。在此範例中，我們選擇包含利率的 B1。最後，點擊 OK 按鈕，請參見屏幕截圖：
   
5. Excel 會自動以對應的結果填入每個變數值（不同利率）旁邊的空白儲存格。看截圖：
   
6. 根據您的需求將所需的數位格式應用於結果（貨幣）。現在，列式資料表已成功創建，您可以快速查看結果來評估當利率變化時您可以承受哪些每月還款金額。看截圖：
   

面向行的資料表

在 Excel 中建立面向行的資料表需要對資料進行排列，以便變數值跨行列出，而不是沿著列列出。以上面的範例為參考，我們繼續依照步驟完成Excel中行式資料表的建立。

步驟 1：連續列出利率

將變數值（利率）放在一行中，確保左側至少有一個空白列用於公式，下方至少有一個空白行用於結果，請參閱螢幕截圖：

第三步：建立資料表

1. 在儲存格 A9 中，輸入以下公式： = B4.
   
2. 選擇包含公式、利率清單和結果相鄰儲存格的範圍（例如，選擇 A8 到 I9）。然後，點擊 數據 > 假設分析 > 數據表.
3. 在 數據表 對話框，點擊 行輸入單元格 框（因為我們使用一行作為輸入值），然後選擇公式中引用的變數儲存格。在此範例中，我們選擇包含利率的 B1。最後，點擊 OK 按鈕，請參見屏幕截圖：
   
4. 根據您的需求將所需的數位格式應用於結果（貨幣）。現在，面向行的資料表已創建，請參見螢幕截圖：
   

一變量資料表的多個公式

在 Excel 中建立包含多個公式的單變量資料表可讓您了解變更單一輸入如何同時影響多個不同的公式。在上面的範例中，如果您想查看還款利率和總利息的利率變化，該怎麼辦？設定方法如下：

第 1 步：新增公式來計算總利息

在儲存格 B5 中，輸入以下公式計算總利息：

=B4*B2*12-B3

步驟2：整理資料表的來源數據

在一列中，列出您要測試的不同利率，並在右側至少留出兩個空白列作為結果。看截圖：

第三步：建立資料表：

1. 在儲存格 E2 中，輸入以下公式： = B4 建立原始資料中還款計算的參考。
2. 在儲存格 F2 中，輸入以下公式： = B5 建立對原始資料的總興趣的參考。
   
3. 選擇包含公式、利率清單和結果相鄰儲存格的範圍（例如，選擇 D2 到 F10）。然後，點擊 數據 > 假設分析 > 數據表.
4. 在 數據表 對話框，點擊 列輸入單元格 方塊（因為我們使用一列作為輸入值），然後選擇公式中引用的變數儲存格。在此範例中，我們選擇包含利率的 B1。最後，點擊 OK 按鈕，請參見屏幕截圖：
   
5. 根據您的需求將所需的數位格式應用於結果（貨幣）。您可以根據不同的變數值查看每個公式的結果。
   

建立二變量資料表

Excel 中的雙變量資料表顯示兩組變數值的不同組合對公式結果的影響，演示公式的兩個輸入的變更如何同時影響其結果。

在這裡，我畫了一個簡單的草圖，以幫助您更好地理解二變量資料表的外觀和結構。

在建立一變量資料表的範例的基礎上，現在讓我們繼續學習如何在 Excel 中建立二變量資料表。

在下面的資料集中，我們有利率、貸款期限和貸款金額，並使用以下公式計算了每月還款額： =-PMT($B$1/12, $B$2*12, $B$3) 以及。在這裡，我們將重點關注數據中的兩個關鍵變數：利率和貸款金額，以觀察這兩個因素的變化如何同時影響還款金額。

第 1 步：設定兩個變化變數

1. 在一欄中列出您要測試的不同利率。看截圖：
   
2. 在一行中，在列值上方輸入不同的貸款金額值（從公式儲存格右側的一個儲存格開始），請參閱螢幕截圖：
   

第三步：建立資料表

1. 將公式放在列出變數值的行和列的交叉點。在這種情況下，我將輸入這個公式： = B4 進入單元格 E2。看截圖：
   
2. 選擇包含貸款金額、利率、公式儲存格以及將顯示結果的儲存格的範圍。
   
3. 然後，單擊 數據 > 假設分析 > 數據表。在資料表對話框中：
   • 在 行輸入單元格 在方塊中，選擇對行中變數值的輸入儲存格的儲存格參考（在此範例中，B3 包含貸款金額）。
   • 在 列輸入儲存格l 方塊中，選擇對列中變數值的輸入儲存格的儲存格參考（B1 包含利率）。
   • 然後，單擊 OK 按鈕。
     
4. 現在，Excel 將使用每種貸款金額和利率組合的結果填入資料表。它可以直接了解貸款金額和利率的不同組合如何影響每月還款額，使其成為財務規劃和分析的寶貴工具。
   
5. 最後，您應該根據需要將所需的數位格式應用於結果（貨幣）。
   

使用數據表的要點

• 新建立的資料表必須與原始資料位於同一工作表中。
• 資料表的輸出取決於來源資料集中的公式儲存格，對此公式儲存格的任何變更都會自動更新輸出。
• 一旦使用數據表計算出值，就無法撤銷 按Ctrl + Z。但是，您可以手動選擇所有值並將其刪除。
• 資料表產生數組公式，因此表中的各個單元格無法變更或刪除。

使用資料表的其他操作

建立資料表後，您可能需要執行其他操作來有效管理它，包括刪除資料表、修改其結果以及執行手動重新計算。

刪除資料表

由於資料表結果是使用陣列公式計算的，因此您無法從資料表中刪除單一值。但是，您只能刪除整個資料表。

選取所有資料表儲存格或僅選取包含結果的儲存格，然後按 刪除 鍵盤上的鍵。

編輯資料表結果

事實上，無法直接編輯資料表中的各個儲存格，因為它們包含 Excel 自動產生的陣列公式。

若要進行更改，您通常需要刪除現有資料表，然後建立一個包含所需修改的新資料表。這涉及調整基本公式或輸入值，然後再次設定資料表以反映這些變更。

手動重新計算數據表

通常，每次進行變更時，Excel 都會重新計算所有開啟的工作簿中的所有公式。如果包含大量變數值和複雜公式的大型資料表導致 Excel 工作簿速度變慢。

為了避免每次工作簿中發生變更時 Excel 會自動對所有資料表執行計算，您可以將計算模式從自動切換為手動。請依照以下步驟操作：

轉到 公式 選項卡，然後單擊 計算選項 > 自動（數據表除外），請參見屏幕截圖：

修改此設定後，當您重新計算整個工作簿時，Excel 將不再自動更新資料表中的計算。

如果您需要手動更新資料表，只需選擇顯示結果的儲存格（包含 TABLE() 公式的儲存格），然後按 F9 鍵。

透過遵循本指南中概述的步驟並記住要點，您可以有效地利用資料表來滿足您的資料分析需求。如果您有興趣探索更多 Excel 提示和技巧，我們的網站提供了數千個教程， 請點擊此處造訪它們。 感謝您的閱讀，我們期待在未來為您提供更多有用的信息！