Skip to main content

Excel中的數據表：創建一維和二維數據表

Author: Xiaoyang Last Modified: 2025-05-12

當您有一個依賴於多個變量的複雜公式，並希望了解改變這些輸入如何有效影響結果時，Excel中的假設分析數據表是一個強大的工具。它讓您能夠快速查看所有可能的結果。以下是創建Excel數據表的逐步指南。此外，我們還將討論一些有效使用數據表的關鍵點，並演示其他操作，例如刪除、編輯和重新計算數據表。

create Data table

什麼是Excel中的數據表？

創建一維數據表

創建二維數據表

使用數據表的關鍵點

使用數據表的其他操作

什麼是Excel中的數據表？

在Excel中，數據表是一種假設分析工具，允許您對公式嘗試不同的輸入值並觀察公式輸出的變化。這個工具對於探索各種場景和進行敏感性分析非常有用，特別是在公式依賴於多個變量的情況下。

備註：數據表與普通的Excel表格不同。
  • 數據表允許您測試公式中的不同輸入值，並查看這些值的變化如何影響輸出。這對於敏感性分析、情景規劃和財務建模尤其有用。
  • Excel表格用於管理和分析相關數據。它是一個結構化的數據範圍，您可以輕鬆地進行排序、篩選和其他操作。數據表更多是基於不同輸入探索各種結果，而Excel表格則是高效管理和分析數據集。

Excel中有兩種類型的數據表：

一維數據表：這允許您分析一個變量的不同值如何影響公式。您可以根據是否要在一行或一列中改變輸入來以行向或列向格式設置一維數據表。
One-Variable Data Table

二維數據表：這種類型允許您查看改變兩個不同變量對公式結果的影響。在二維數據表中，您沿著行和列改變值。
Two-Variable Data Table

創建一維數據表

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

列向數據表

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

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

步驟1：設置基本公式

為了計算付款額，這裡我將利率設為5%。在單元格B4中輸入PMT公式，該公式根據利率、期數和貸款金額計算每月付款額。請參見截圖：

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

use a formula to calculate the payment

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

在一列中列出您想測試的不同利率。例如，在一列中以1%的增量從4%到11%列出值，並在右側留出至少一列空白列作為結果。請參見截圖：
List the interest rates in a column

步驟3：創建數據表

  1. 在單元格E2中輸入以下公式： =B4.
    注意：B4是包含主公式的單元格，這是您希望在改變利率時看到其結果變化的公式。
    enter a formula to link the payment cell
  2. 選擇包含公式、利率列表和相鄰結果單元格的範圍（例如，選擇D2到E10）。請參見截圖：
    Select the range includes formula, list of interest rates and adjacent cells
  3. 轉到功能區，點擊 數據 選項卡，然後點擊 假設分析 > 數據表，請參見截圖：
    "click 數據表"/>
  4. 在數據表對話框中，點擊列輸入單元格框（因為我們使用列作為輸入值），並選擇公式中引用的變量單元格。在此示例中，我們選擇包含利率的B1。最後，點擊確定按鈕，請參見截圖：
    set options in the dialog box
  5. Excel將自動填充每個變量值（不同的利率）旁邊的空單元格，顯示相應的結果。請參見截圖：
    fill the empty cells adjacent to each of your variable values
  6. 根據需要將結果應用所需的數字格式（貨幣）。現在，列向數據表已成功創建，您可以快速瀏覽結果，評估利率變化時哪些每月付款額對您來說是可負擔的。請參見截圖：
    format to the results

行向數據表

在Excel中創建行向數據表涉及安排您的數據，使變量值列在一行中而不是在一列中。以上述示例為參考，讓我們繼續完成創建行向數據表的步驟。

步驟1：在一列中列出利率

將變量值（利率）放在一行中，確保左側至少有一列空白列用於公式，下方至少有一行空白行用於結果，請參見截圖：
List the interest rates in a row

步驟2：創建數據表

  1. 在單元格A9中輸入此公式：=B4。
    enter a formula to link to payment cell
  2. 選擇包含公式、利率列表和相鄰結果單元格的範圍（例如，選擇A8到I9）。然後，點擊數據 > 假設分析 > 數據表。
  3. 在數據表對話框中，點擊行輸入單元格框（因為我們使用行作為輸入值），並選擇公式中引用的變量單元格。在此示例中，我們選擇包含利率的B1。最後，點擊確定按鈕，請參見截圖：
    set options in the dialog box
  4. 根據需要將結果應用所需的數字格式（貨幣）。現在，行向數據表已創建，請參見截圖：
    the row-oriented data table is created

一維數據表的多個公式

在Excel中創建帶有多個公式的一維數據表，可以讓您看到改變單一輸入如何同時影響多個不同的公式。在上述示例中，如果您想看到利率變化對還款和總利息的影響該怎麼辦？以下是設置方法：

步驟1：添加新公式以計算總利息

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

=B4*B2*12-B3

apply a formula to calculate the total interest

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

在一列中，列出您想測試的不同利率，並在右側留出至少兩列空白列作為結果。請參見截圖：
Arrange the data table's source data

步驟3：創建數據表：

  1. 在單元格E2中輸入此公式：=B4，創建對原始數據中還款計算的引用。
  2. 在單元格F2中輸入此公式：=B5，創建對原始數據中總利息的引用。
    enter formulas to link to two cells
  3. 選擇包含公式、利率列表和相鄰結果單元格的範圍（例如，選擇D2到F10）。然後，點擊數據 > 假設分析 > 數據表。
  4. 在數據表對話框中，點擊列輸入單元格框（因為我們使用列作為輸入值），並選擇公式中引用的變量單元格。在此示例中，我們選擇包含利率的B1。最後，點擊確定按鈕，請參見截圖：
    set options in the dialog box
  5. 根據需要將結果應用所需的數字格式（貨幣）。並且您可以根據變量值的變化查看每個公式的結果。
    the results for each formula based on the varying variable values
a screenshot of kutools for excel ai

使用 Kutools AI 解鎖 Excel 的魔法

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

創建二維數據表

Excel中的二維數據表顯示了兩組變量值的不同組合對公式結果的影響，展示了公式中兩個輸入的同時變化如何影響其結果。

這裡，我畫了一個簡單的草圖，幫助您更好地理解二維數據表的外觀和結構。
two-variable data table sketch

基於創建一維數據表的示例，讓我們繼續學習如何在Excel中創建二維數據表。

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

步驟1：設置兩個變化的變量

  1. 在一列中，列出您想測試的不同利率。請參見截圖：
    list the different interest rates
  2. 在一行中，輸入不同的貸款金額值，位於列值上方（從公式單元格右側的一個單元格開始），請參見截圖：
    enter the different loan amount values

步驟2：創建數據表

  1. 將您的公式放在列出變量值的行和列的交匯處。在這種情況下，我將在單元格E2中輸入此公式：=B4。請參見截圖：
    enter a formula to link the payment cell
  2. 選擇包含貸款金額、利率、公式單元格和顯示結果的單元格的範圍。
    Select the range
  3. 然後，點擊 數據 > 假設分析 > 數據表。在數據表對話框中：
    • 在行輸入單元格框中，選擇行中變量值的輸入單元格引用（在此示例中，B3包含貸款金額）。
    • 在列輸入單元格框中，選擇列中變量值的輸入單元格引用（B1包含利率）。
    • 然後，點擊確定按鈕。
      set options in the dialog box
  4. 現在，Excel將填寫數據表，顯示每種貸款金額和利率組合的結果。它直接展示了不同貸款金額和利率組合如何影響每月付款額，使其成為財務規劃和分析的寶貴工具。
    two-variable data table is created
  5. 最後，您應該根據需要將結果應用所需的數字格式（貨幣）。
    apply the desired number format to the results

使用數據表的關鍵點

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

使用數據表的其他操作

一旦創建了數據表，您可能需要額外的操作來有效地管理它，包括刪除數據表、修改其結果以及執行手動重新計算。

刪除數據表

由於數據表結果是通過數組公式計算的，您無法從數據表中刪除單個值。然而，您只能刪除整個數據表。

選擇所有數據表單元格或僅選擇包含結果的單元格，然後按鍵盤上的Delete鍵。

編輯數據表結果

確實，無法直接編輯數據表內的單個單元格，因為它們包含Excel自動生成的數組公式。

要進行更改，通常需要刪除現有的數據表，然後創建一個包含所需修改的新數據表。這涉及調整您的基礎公式或輸入值，然後重新設置數據表以反映這些更改。

手動重新計算數據表

通常，每次進行更改時，Excel會重新計算所有打開的工作簿中的所有公式。如果一個包含大量變量值和複雜公式的大型數據表導致您的Excel工作簿變慢。

為避免Excel在每次工作簿中進行更改時自動為所有數據表執行計算，您可以將計算模式從自動切換為手動。請按照以下步驟操作：

轉到公式選項卡，然後點擊計算選項 > 除數據表外自動，請參見截圖：
click Automatic Except for Data Tables to disable the auto calculation

一旦修改此設置，當您重新計算整個工作簿時，Excel將不再自動更新數據表中的計算。

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

通過遵循本指南中概述的步驟並牢記關鍵點，您可以高效地利用數據表滿足您的數據分析需求。如果您對探索更多Excel技巧感興趣，我們的網站提供了數千個教程，請點擊這裡訪問它們。感謝閱讀，我們期待未來為您提供更多有用的信息！