資料表是一個儲存格範圍,顯示變更資料中的一或兩個 公式 如何影響這些公式的結果。 資料表提供一種快捷方式,可計算一個作業中的多個結果,並一起查看及比較工作表上所有不同變化的結果。

資料表是稱為分析工具之命令套件的一部分。 當您使用資料表時,您執行的就是分析。 如果分析會變更儲存格中的值,以查看這些變更會如何影響工作表上公式的結果。 例如,您可以使用資料表來調整貸款中使用的利率和期限長度,以判斷可能的每月付款金額。

在分析工具中,有三種假設分析工具:Excel、資料表和目標尋找。 分析藍本和運算列表能接受數組輸入值,並判斷可能的結果。 「目標搜尋」的運作方式與分析藍本和運算列表的不同之處,在於它能依據某個結果,判斷能產生該結果的可能輸入值。 與案例一樣,資料表可協助您探索一組可能的結果。 與案例不同,資料表會顯示一個工作表上一個資料表的所有結果。 使用運算列表能使某範圍內的所有可能結果變得一目了然。 由於您僅專注於一或兩個變數上,使得結果能輕鬆閱讀並以表格式表單進行共用。

運算列表無法容納兩個以上的變數。 如果您想要分析兩個以上變數,您應該改為流量分析案例。 雖然資料表只能包含一或兩個 (列輸入儲存格,一個變數用於欄輸入儲存格) ,但資料表可以包含許多不同的變數值。 分析藍本最多只能有 32 個不同的值,但您可以建立任意數量的分析藍本。

資料表基本功能

您可以根據要測試的變數和公式數目,建立單變數或雙變數資料表。 如果您想要查看一或多個公式中一個變數的不同值如何變更這些公式的結果,請使用單變數資料表。 例如,您可以使用單變數資料表,使用 PMT 函數來瞭解不同的利率如何影響每月貸款付款。 您可以在一欄或一列中輸入變數值,而結果會顯示在連續的欄或列中。

詳細資訊,請參閱 PMT 函數

儲存格 D2 包含付款公式 ,=PMT (B3/12,B4,-B5) ,這是參照輸入儲存格 B3。

單變數資料表

模擬分析 - 單一變數運算列表

圖說文字 1 輸入儲存格。

圖說文字 2 輸入儲存格中Excel值清單,B3。

使用雙變數資料表查看一個公式中兩個變數的不同值如何變更該公式的結果。 例如,您可以使用雙變數資料表來查看利率和貸款期限的不同組合如何影響每月的貸款付款。

儲存格 C2 包含付款公式 ,=PMT (B3/12,B4,-B5) ,使用兩個輸入儲存格 B3 和 B4。

雙變數資料表

模擬分析 - 雙變數運算列表

圖說文字 1 欄輸入儲存格。

圖說文字 2 列輸入儲存格 B4 中Excel取代的值清單。

圖說文字 3 列輸入儲存格。

圖說文字 4 欄輸入儲存格 B3 中Excel取代的值清單。

重新計算工作表時,即使資料表沒有變更,資料表都會重新計算。 若要加快計算包含資料表的工作表,您可以變更計算選項,以自動重新計算工作表,但不要重新計算資料表。

單變數資料表的輸入值會列在欄 (欄導向) 欄或列導向 (欄) 。 用於單變數資料表中的公式只能參照一個輸入儲存格。

  1. 在輸入儲存格下一欄或跨一列輸入您想要取代的值清單。 在值任一側保留數個空白列和欄。

  2. 執行下列其中一個動作:

如果資料表是

執行動作

以欄為導向 (變數值會位在欄)

在儲存格中輸入一列上方的公式,以及值欄右邊的一個儲存格。

概觀區段中顯示的單變數資料表圖例以欄為導向,且公式包含在儲存格 D2 中。

附註: 如果您想要檢查各種值對其他公式的影響,請于第一個公式右邊的儲存格中輸入其他公式。

以列為導向 (變數值是列式)

在儲存格中輸入公式:第一個值左邊一欄,值列下方一個儲存格

附註: 如果您想要檢查各種值對其他公式的影響,請于第一個公式下方的儲存格中輸入其他公式。

  1. 選取包含要取代之公式和值的儲存格範圍。 根據上一個概觀區段的第一個圖例,此範圍為 C2:D5

  2. 在 Mac 版 Excel 2016:按一下[資料>資料表的 [>分析> 。

    資料表選項

    在 Mac 版 Excel 2011:在[資料Mac 版 Excel索引鍵的 [分析中,按一下[What-if,,然後按一下[資料表> 。

    [運算列表] 對話方塊

  3. 執行下列其中一個動作:

如果資料表是

執行動作

欄導向

在資料行輸入儲存格方塊中輸入儲存格 的儲存格 參照。 使用第一個圖例中顯示的範例,輸入儲存格是 B3。

以列為導向

在列輸入儲存格方塊中輸入儲存格 的儲存格 參照。

附註: 建立資料表之後,您可能會想要變更結果儲存格的格式。 在圖例中,結果儲存格的格式會格式化為貨幣。

用於單變數資料表中的公式必須參照相同的輸入儲存格。

  1. 執行下列其中一個動作:

如果資料表是

執行動作

以欄為導向 (變數值會位在欄)

在資料表頂端列現有公式右側空白儲存格中輸入新公式。

以列為導向 (變數值是列式)

在資料表第一欄現有公式下方的空白儲存格中輸入新公式。

  1. 選取包含資料表和新公式的儲存格範圍。

  2. 在 Mac 版 Excel 2016:按一下[資料>資料表的 [>分析> 。

    資料表選項

    在 Mac 版 Excel 2011:在[資料Mac 版 Excel索引鍵的 [分析中,按一下[What-if,,然後按一下[資料表> 。

    [運算列表] 對話方塊

  3. 執行下列其中一個動作:

如果資料表是

執行動作

欄導向

在資料行輸入儲存格方塊中輸入儲存格 的儲存格 參照。

以列為導向

在列輸入儲存格方塊中輸入儲存格 的儲存格 參照。

雙變數資料表使用包含兩個輸入值清單的公式。 公式必須參照兩個不同的輸入儲存格。

  1. 在工作表上的儲存格中,輸入參照兩個輸入儲存格的公式。 在下列範例中,在儲存格 B3、B4 和 B5 中輸入公式的起始值,在儲存格 C2 中輸入公式 =PMT (B3/12,B4,-B5) 。

  2. 在公式下方,在同一欄中輸入一份輸入值清單。 在此案例中,在儲存格 C3、C4 和 C5 中輸入不同的利率。

  3. 在公式的右側,在同一列輸入第二個清單。 在儲存格 D2 和 E2 中 (月) 貸款條款。

  4. 選取包含公式 (C2) 的儲存格範圍、值 (C3:C5 和 D2:E2) 的列和欄,以及要計算值 (D3:E5) 的儲存格。 在這種情況下,請選取範圍 C2:E5。

  5. 在 Mac 版 Excel 2016:按一下[資料>資料表的 [>分析> 。

    資料表選項

    在 Mac 版 Excel 2011:在[資料Mac 版 Excel索引鍵的 [分析中,按一下[What-if,,然後按一下[資料表> 。

    [運算列表] 對話方塊

  6. 在列 輸入儲存格 方塊中,輸入資料列輸入值輸入儲存格的參照。 在列輸入儲存格方塊中輸入B4。

  7. 在欄 輸入儲存格 方塊中,輸入資料行中輸入值輸入儲存格的參照。 在欄輸入儲存格方塊中輸入B3。

雙變數資料表可以顯示利率和貸款期限的不同組合如何影響每月貸款付款。 在下列圖例中,儲存格 C2 包含使用兩個輸入儲存格 B3 和 B4 的付款公式 =PMT (B3/12,B4,-B5) 。

模擬分析 - 雙變數運算列表

圖說文字 1 欄輸入儲存格。

圖說文字 2 列輸入儲存格 B4 中Excel取代的值清單。

圖說文字 3 列輸入儲存格。

圖說文字 4 欄輸入儲存格 B3 中Excel取代的值清單。

重要: 當您選取此計算選項時,重新計算其他活頁簿時,資料表會略過。 若要手動重新計算資料表,請選取其公式,然後按F9。 若要在 Mac OS X 版本 10.3 或更新版本中使用此鍵盤快速鍵,您必須先關閉此按鍵的 Exposé 鍵盤快速鍵。 如要詳細資訊,請參閱 Excel中的鍵盤Windows。

  1. [Excel] 功能表中,按一下 [偏好設定]

  2. 在 [ 公式與清單> 區段,按一下 [ 計算,然後按一下 [除了資料表以外自動計算> 。

另請參閱

模擬分析簡介

預測資料趨勢

使用規劃求解定義和解決問題

使用目標尋找從公式尋找您想要的結果

需要更多協助?

擴展您的技能
探索訓練

這項資訊有幫助嗎?

您對語言品質的滿意度如何?
以下何者是您會在意的事項?

感謝您的意見反應!

×