Excel 資料表：建立單變數與雙變數資料表

當您擁有依賴多個變數的複雜公式，並希望清楚掌握調整這些輸入值會如何影響結果時，Excel 的模擬分析資料表就是一項強大利器。它能讓您一眼綜覽所有可能的結果。以下將逐步說明如何在 Excel 中建立資料表，同時探討使用資料表的關鍵要點，並示範如何刪除、編輯與重新計算資料表等操作。

什麼是 Excel 中的資料表？

建立單變數資料表

• 以欄為主的資料表
• 以列為主的資料表
• 單變數資料表的多重公式

建立雙變數資料表

使用資料表的重點提示

資料表的其他操作

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

什麼是 Excel 中的資料表？

在 Excel 中，資料表是模擬分析的利器之一，能讓您針對公式測試不同的輸入值，並即時觀察輸出結果的變化。這項工具特別適合用於探索多種情境與執行敏感度分析，尤其當公式涉及多個變數時，更能展現其價值。

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

單變數資料表：此類型可讓您分析單一變數的不同數值如何影響公式結果。根據輸入值是橫向排列於一列，還是縱向排列於一欄，您可以選擇以列為主或以欄為主的格式來建立單變數資料表。

雙變數資料表：此類型可讓您觀察兩個不同變數的變動如何影響公式結果。在雙變數資料表中，您將同時沿著一列與一欄調整數值。

建立單變數資料表

在 Excel 中建立單變數資料表，是分析單一輸入值變動如何影響多種結果的實用技巧。本節將逐步引導您完成建立以欄為主、以列為主，以及包含多個公式的單變數資料表。

太棒了！在 Excel 中使用高效能的 Tabs，就像 Chrome、Edge、Firefox 和 Safari 一樣！
每天為您節省 50% 的時間，並減少數千次滑鼠點擊！

---

以欄為主的資料表

當您想測試單一變數的不同數值對公式結果的影響，且這些變數值是以欄列出時，以欄為主的資料表就非常實用。讓我們來看一個簡單的財務範例：

假設您考慮一筆 50,000 美元的貸款，並計劃在 3 年（即 36 個月）內償清。為根據您的薪資評估可負擔的每月還款金額，您希望了解不同利率將如何影響每月應付金額。

步驟 1：設定基礎公式

若要計算付款金額，我在此將利率設為 5%。請在儲存格 B4 中輸入 PMT 公式，該公式會根據利率、期數與貸款金額，自動計算出每月應付金額。請參閱以下截圖：

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

步驟 2：在一欄中列出利率

在一欄中列出您要測試的不同利率，例如以 1% 為間距，從 4% 到 11% 依序填入；並在右側至少保留一欄空白，用以顯示運算結果。請參閱截圖：

步驟 3：建立資料表

1. 在儲存格 E2 中輸入下列公式：=B4.
   
   注意：B4 是您主要公式的所在儲存格，此公式會隨著利率變化而顯示不同結果。
   
2. 選取包含公式、利率清單以及相鄰結果儲存格的範圍（例如，選取 D2 至 E10）。請參閱螢幕截圖：
   
3. 前往功能區，按一下資料索引標籤，然後按一下模擬分析＞ 資料表，請參閱截圖：
   資料表“/>
4. 在資料表對話方塊中，按一下欄變數儲存格方塊（因我們以欄作為輸入值），並選取公式中所引用的變數儲存格。在此範例中，我們選取包含利率的 B1 儲存格。最後，按一下確定按鈕，請參閱下方截圖：
   
5. Excel 會自動在每個變數值（不同利率）旁的空白儲存格中填入對應結果。請參閱截圖：
   
6. 根據您的需求，將所需的數字格式（貨幣）套用至結果。現在，您已成功建立以欄為主的資料表，可快速瀏覽結果，評估在利率變動時，哪些每月付款金額符合您的負擔能力。請參閱截圖：
   

以列為主的資料表

在 Excel 中建立以列為導向的資料表時，需將資料排列成變數值橫向置於同一列中，而非縱向填入欄位。以上述範例為參考，請依照以下步驟完成建立以列為導向的資料表。

步驟 1：將利率列於一列中

將變數值（利率）置於一列中，並確保左側至少保留一個空白欄位用於公式，下方至少保留一個空白列用於結果，請參見截圖：

步驟 2：建立資料表

1. 在儲存格 A9 中輸入此公式：=B4.
   
2. 選取包含公式、利率清單及相鄰結果儲存格的範圍（例如：A8 至 I9）。接著，點選資料＞ 模擬分析＞ 資料表。
3. 在資料表對話方塊中，按一下列變數儲存格方塊（因我們以列作為輸入值），並選取公式中所引用的變數儲存格。在此範例中，請選取包含利率的 B1 儲存格。最後，按一下確定按鈕，詳情請參閱下方截圖：
   
4. 根據需求，將所需的數字格式（例如貨幣）套用至結果。現已建立以列為主的資料表，請參閱下方截圖：
   

單變數資料表的多重公式

在 Excel 中建立包含多個公式的單變數資料表，能讓您同時觀察單一輸入值的變動對多個不同公式結果的影響。以上述範例為例，若您希望同時掌握利率變化對每月還款額與總利息的影響，可依照下列方式進行設定：

步驟 1：新增計算總利息的公式

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

=B4*B2*12-B3

步驟 2：安排資料表的源數據

在一欄中列出您要測試的各項利率，並在其右側至少保留兩欄空白，以便顯示結果。請參閱截圖：

步驟 3：建立資料表：

1. 在儲存格 E2 中輸入此公式：=B4，即可建立對原始資料中還款計算的參照。
2. 在儲存格 F2 中輸入此公式：=B5，即可建立對原始資料中總利息的參照。
   
3. 選取包含公式、利率清單及相鄰結果儲存格的範圍（例如：D2 至 F10）。接著，點選資料＞ 模擬分析＞ 資料表。
4. 在資料表對話方塊中，按一下欄變數儲存格方塊（因我們以欄作為輸入值），並選取公式中所引用的變數儲存格。在此範例中，我們選取包含利率的 B1 儲存格。最後，按一下確定按鈕。截圖如下：
   
5. 根據需求，將所需的貨幣格式套用至結果。您可清楚看到每個公式在不同變數值下所產生的結果。
   

透過 KUTOOLS AI 解鎖 Excel 魔法

• 智慧執行：只需簡單指令，即可執行儲存格操作、分析資料並建立圖表。
• 自訂公式：打造專屬於您的公式，輕鬆簡化工作流程！
• VBA 編碼：輕鬆撰寫並執行 VBA 程式碼！
• 公式解讀：輕鬆掌握複雜公式！
• 文字翻譯：輕鬆打破試算表中的語言障礙！

透過 AI 驅動工具提升您的 Excel 能力。立即下載，體驗前所未有的高效！

建立雙變數資料表

Excel 中的雙變數資料表能清楚呈現兩組變數值的不同組合對公式結果所產生的影響，讓您一目了然地掌握當公式中的兩個輸入值同時變動時，結果將如何隨之變化。

在此，我繪製了一張簡易示意圖，幫助您更清楚掌握雙變數資料表的外觀與結構。

延續先前建立單變數資料表的範例，現在讓我們進一步學習如何在 Excel 中建立雙變數資料表。

在下方資料集中，我們提供了利率、貸款年限與貸款金額，並已使用下列公式計算出每月還款額：=-PMT($B$1/12, $B$2*12, $B$3)。接下來，我們將聚焦於其中兩個關鍵變數——利率與貸款金額，觀察這兩者同時變動時對每月還款額的影響。

步驟 1：設定兩個變動變數

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

步驟 2：建立資料表

1. 將您的公式置於列出變數值的列與欄交會處。在此情況下，我會在儲存格 E2 輸入此公式：=B4. 請參閱下方截圖：
   
2. 選取包含貸款金額、利率、公式儲存格，以及用來顯示結果的儲存格範圍。
   
3. 接著，按一下資料＞ 模擬分析＞ 資料表。在「資料表」對話方塊中：
   • 在列變數儲存格方塊中，選取對應至該列變數值的儲存格參照（在此範例中，B3 包含貸款金額）。
   • 在欄輸入儲存格方塊中，選取對應該欄變數值的儲存格參照（例如：B1 包含利率）。
   • 接著，點擊確定按鈕。
     
4. 現在，Excel 會自動以每種貸款金額與利率組合的計算結果填滿資料表，清楚呈現不同貸款條件對每月還款金額的影響，是財務規劃與分析不可或缺的利器。
   
5. 最後，請根據您的需求，將所需的數字格式（例如貨幣）套用至結果。
   

使用資料表的重點提示

• 新建立的資料表必須與原始資料位於同一張工作表中。
• 資料表的輸出結果取決於來源資料設定中的公式儲存格，且該公式儲存格一旦變更，輸出結果將自動同步更新。
• 一旦使用資料表計算出數值，便無法透過 Ctrl + Z 復原，但您可以手動選取所有數值並刪除。
• 由於資料表會產生陣列公式，因此無法變更或刪除表格中的個別儲存格。

資料表的其他操作

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

刪除資料表

由於資料表結果是透過陣列公式計算得出，因此無法單獨刪除其中某個值，只能整體移除整個資料表。

選取所有資料表儲存格，或僅選取包含結果的儲存格，再按下 Delete 鍵即可清除內容！

編輯資料表結果

確實無法直接編輯資料表中的個別儲存格，因為這些儲存格包含 Excel 自動產生的陣列公式。

若需進行變更，通常必須先刪除現有的資料表，再根據調整需求重新建立一個新資料表，包括修改基礎公式或輸入值，並重新設定資料表以反映這些變更。

手動重新計算資料表

一般情況下，每次進行變更時，Excel 都會重新計算所有已開啟活頁簿中的公式。若您的資料表規模龐大，且包含大量變數與複雜公式，可能會導致 Excel 工作表運作速度變慢。

為避免每次在活頁簿中進行變更時，Excel 自動重新計算所有工作表，您可以將計算模式從「自動」切換為「手動」。請依照下列步驟操作：

前往公式索引標籤，然後按一下計算選項> 除了資料表以外皆自動，請參見截圖：

一旦變更此設定，當您在重新計算整個活頁簿時，Excel 將不再自動更新資料表中的計算結果。

若您需要手動更新資料表，只需選取顯示結果的儲存格（即包含 TABLE() 公式的儲存格），然後按下 F9 鍵即可立即刷新！

只要遵循本指南所述步驟並牢記關鍵要點，您就能高效運用資料表，輕鬆滿足數據分析需求！若您想探索更多 Excel 實用技巧，我們網站提供數千篇教學文章，請點此前往瀏覽。感謝您的閱讀，期待未來為您帶來更多實用資訊！