使用運算列表計算多重結果

資料表格是一個儲存格範圍,您可以在其中變更部分儲存格中的值,並產生問題的不同答案。 資料表格的一個不錯範例使用了不同貸款金額和利率的 PMT 函式,在家用貸款貸款上計算價格合理的金額。 試驗不同的值,以觀察結果中的對應變化是 資料分析的一般工作。

在 Microsoft Excel 中,資料表格是一組稱為 What-If 分析工具的命令組成。 當您建立並分析資料表格時,您會進行模擬分析。

模擬分析能夠透過變更儲存格中的值,來查看那些變更會如何影響工作表上的公式結果。 例如,您可以使用資料表格來改變貸款的利率和字詞長度,以評估潛在的每月付款金額。

附註: 您可以使用資料表格和 Visual Basic for Applications (VBA) 來執行更快速的計算。 如需詳細資訊,請參閱Excel What-If 資料表:使用 VBA 進行計算的速度更快

模擬分析類型    

Excel 中有三種類型的模擬分析工具: 案例、資料表格目標搜尋。 案例和資料表格使用輸入值的集合來計算可能的結果。 目標-尋道差別不一樣,它會使用單一結果,並計算產生該結果的可能輸入值。

就像案例一樣,資料表格可協助您探索一組可能的結果。 與案例不同的是,資料表格會將所有的結果顯示在一個工作表上的一個表格中。 使用運算列表能使某範圍內的所有可能結果變得一目了然。 由於您僅專注於一或兩個變數上,使得結果能輕鬆閱讀並以表格式表單進行共用。

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

如需深入瞭解,請參閱What-If 分析簡介

視您需要測試的變數和公式數量而定,建立一個變數或兩個變數的資料表。

單變數資料表格    

如果您想要查看一或多個公式中某個變數的不同值如何變更這些公式的結果,請使用單變數運算清單。 例如,您可以使用一個單變數的資料表格,查看不同利率使用 PMT 函數會如何影響每月抵押款項。 您在一欄或一列中輸入變數值,而結果則會顯示于連續的欄或列中。

在下圖中,儲存格 D2 包含付款公式, = PMT (B3/12,B4,B4,B5) ,參照輸入儲存格 B3。

具有一個變數的運算列表

雙變數資料表格    

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

在下圖中,儲存格 C2 包含付款公式,也就是使用兩個輸入儲存格、B4 和 B4 的 (B3/12,B4,B5)

有兩個變數的運算列表
 

資料表格計算    

每當工作表重新計算時,任何資料表格也會重新計算,即使資料沒有變更也一樣。 若要加快包含資料表的工作表的計算速度,您可以變更 計算 選項,自動重新計算工作表,而不是資料表。 若要深入瞭解,請參閱 在包含資料表格的工作表中,加速計算

單變數資料表格在單一 (欄中包含其輸入值,不論是以資料行方向的) ,或是跨資料列 (以資料列為導向的) 。 單變數資料表格中的任何公式必須只參照一個 變數儲存格。

請遵循下列步驟:

  1. 在輸入儲存格中輸入您想要取代的值清單(在一欄中,或是在一列中。 在值的任一側留出一些空白列和欄。

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

    • 如果資料表格是 欄方向 (您的變數值位於 [欄) ] 中,請在其中一列的儲存格中輸入公式,並在值欄右側的一個儲存格中輸入公式。 這個單變數運算清單是以欄為導向,且公式包含在儲存格 D2 中。

      具有一個變數的運算列表

      如果您想要檢查其他公式的不同值的效果,請在第一個公式右側的儲存格中輸入額外的公式。

    • 如果資料表格是 列方向 (您的變數值是列) ,請在第一個值左邊的儲存格 a 欄中輸入公式,並在值列的下一個儲存格。

      如果您想要檢查其他公式的不同值的效果,請在第一個公式的 一個儲存格中輸入其他公式。

  3. 選取包含您要取代之公式和值的儲存格範圍。 在上圖中,此範圍是 C2: D5。

  4. 在 [資料] 索引標籤上,按一下 [資料工具] 群組中的 [模擬分析] >資料表 (,或 Excel 2016) 的 [預測] 群組。 

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

    • 如果資料表格是以欄為導向,請在 [ 欄輸入儲存格 ] 欄位中,輸入輸入儲存格的 儲存格參照 。 在上圖中,輸入儲存格為 B3。

    • 如果資料表格是列方向,請在 [ 列輸入儲存格 ] 欄位中,輸入輸入儲存格的儲存格參照。

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

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

請依照這些步驟操作

  1. 請執行下列其中一項操作:

    • 如果資料表格是以欄為導向,請在資料表格頂端列中現有公式右側的空白儲存格中,輸入新的公式。

    • 如果資料表格是列方向,請在資料表格第一欄中現有公式的空白儲存格中,輸入新的公式。

  2. 選取包含資料表格的儲存格範圍以及新的公式。

  3. 在 [資料] 索引標籤上,按一下 [資料工具] 群組中的 [模擬分析]> 資料表 (,或 Excel 2016) 的 [預測] 群組。

  4. 請執行下列其中一項操作︰

    • 如果運算清單是以欄為導向,請在 [ 欄輸入儲存格 ] 方塊中,輸入輸入儲存格的儲存格參照。

    • 如果資料表格是列方向,請在 [ 列輸入儲存格 ] 方塊中,輸入輸入儲存格的儲存格參照。

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

請遵循下列步驟:

  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. 在 [資料] 索引標籤上,于 [資料工具] 群組或 [預測群組] 中 ( Excel 2016) 中,按一下 [資料工具] 群組中的 [模擬>分析] ,或 (Excel 2016的 [預測] 群組 ) 。 

  6. 在 [ 列輸入儲存格 ] 欄位中,針對資料列中輸入值,輸入輸入儲存格的參照。
    在 [列輸入儲存格] 方塊中,輸入儲存格 B4

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

  8. 按一下 [確定]。

雙變數運算清單範例

兩個變數的資料表可以顯示不同的利率與貸款期限組合會如何影響每月抵押款項。 在此圖中,儲存格 C2 中包含付款公式,也就是使用兩個輸入儲存格、 b4、b4) 的 PMT (的 [b3] 或 [B5]。

有兩個變數的運算列表

當您設定此計算選項時,在整個活頁簿上完成重新計算後,就不會進行資料表格計算。 若要手動重新計算您的資料表格,請選取其公式,然後按 F9 鍵。

請依照下列步驟來改善計算效能:

  1. 按一下 [ 檔案] > [ 選項 ] > [ 公式]。

  2. 在 [ 計算選項 ] 區段的 [ 計算] 底下,按一下 [ 除資料表格以外的自動]

    提示: 您也可以選擇在 [公式] 索引標籤上,按一下 [計算選項] 上的箭號,然後按一下 [計算]) 群組中的 [資料工作表] (。

如果您有特定目標或較大的可變資料集,您可以使用幾個其他的 Excel 工具來執行模擬分析。

目標搜尋

如果您知道要從公式得到的結果,但不知道公式要取得該結果所需的輸入值,請使用 Goal-Seek 功能。 請參閱 使用 [目標搜尋],透過調整輸入值來找出您想要的結果

Excel 規劃求解

您可以使用 Excel 規劃求解增益集,找出一組輸入變數的最佳值。 規劃求解可與一組儲存格搭配使用, (稱為決策變數,或只是在計算目標與限制儲存格中的公式時所用的變數儲存格) 。 [規劃求解] 會調整決策變數儲存格中的值,以符合限制式儲存格的限制並產生您期望的目標儲存格結果。 如需深入瞭解,請參閱以下文章: 使用 [規劃求解] 定義和解決問題

在儲存格中插入不同的數位,就可以快速產生問題的不同答案。 很好的範例是使用 PMT 函數,以不同的利率與貸款期 (的月份) ,找出您可以為家用或汽車支付多少貸款。 您可以在稱為 [資料表格] 的儲存格範圍中輸入數位。

在這裡,資料表格是儲存格 B2: D8 的範圍。 您可以變更 B4 中的值、貸款金額,以及在 D 欄自動更新的每月付款。 使用3.75% 的利率,D2 會使用此公式傳回 $1042.01 的每月付款: = PMT (C2/12、$B $3、$B $4) 。

這個儲存格範圍 (B2:D8) 就是一個運算列表

您可以使用一或兩個變數,視您想要測試的變數和公式數量而定。

使用單變數測試,查看公式中一個變數的不同值如何變更結果。 例如,您可以使用 PMT 函數變更每月抵押付款的利率。 您在一欄或一列中輸入的變數值 () ,而結果則會顯示在附近的欄或列中。

在這個即時活頁簿中,儲存格 D2 包含付款公式 =PMT (C2/12,$B $3,$B $4) 。 儲存格 B3 是 可變 儲存格,您可以在其中插入不同字詞長度, (每月付款期數) 。 在儲存格 D2 中,PMT 函數會在利率 3.75 = 12、360月和 $225000 貸款中插入,並計算 $1042.01 每月付款。

使用雙變數測試,查看公式中兩個變數的不同值會如何變更結果。 例如,您可以測試利率及每月付款期數的不同組合,以計算抵押付款。

在這個即時活頁簿中,儲存格 C3 包含付款公式, =PMT ($B $ 3/12,$B $ 2,B4) ,使用兩個變數儲存格(B2 和 B3)。 在儲存格 C2 中,PMT 函數會在利率 3.875%/12、360月和 $225000 貸款中插入,並計算 $1058.03 月付款。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

需要更多協助?

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×