使用資料表計算多重結果

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

運算列表是一個儲存格範圍,會顯示公式中某幾個變數的變化對公式結果的影響。運算列表提供了一種快捷方法,可透過一個步驟操作計算出多種情況下的值,而且還能用來檢視和比較由工作表中不同變化所引起的各種結果。

本文內容

概觀

建立單變數資料表

將公式新增到單變數資料表

建立雙變數資料表

加速包含資料表之工作表的計算

概觀

資料表是一組稱為假設狀況分析工具命令的一部分。

假設狀況分析是一種變更儲存格內的值,以查看這些變更將如何影響工作表上公式之結果的程序。例如,您可以使用資料表來變更貸款中使用的利率和貸款期數,以決定可能的每月支付款項。

模擬分析的類型   有三種在 Excel 中的模擬分析工具 ︰ 案例、 資料的資料表,與目標搜尋。案例運算列表採取的輸入值,並決定可能的結果。[目標搜尋層面的運作案例與資料表的結果並決定可能會產生結果的輸入的值。

和分析藍本一樣,資料表可協助您探索一組可能的結果。與分析藍本不同的是,資料表會在工作表的某個表格中顯示所有結果。使用資料表可以很容易地檢查一系列的可能性。由於您只專注在一個或兩個變數上,因此可以很容易閱讀結果,並以列表方式共用。

資料表無法容納超過兩個以上的變數。若您想要分析兩個以上的變數,應該改用分析藍本。儘管只能使用一個或兩個變數 (其中一個做為列變數儲存格,另一個則為欄變數儲存格),但您可以視需求在資料表中包含許多不同的變數值。分析藍本可以使用最多 32 個不同值,但您可以視需求建立所需的分析藍本數目。

如需假設狀況分析工具的詳細資訊,請參閱 [請參閱] 區段中的連結。

資料表基礎

根據您想測試的變數和公式數量而定,您可以建立單變數或雙變數資料表。

單變數運算列表   如果您想要查看如何不同的值,其中一種一個變數的或多個公式會變更這些公式的結果,請使用單變數運算列表。例如,您可以使用單變數運算列表若要查看不同利率影響抵押貸款月付款使用PMT函數。您在一欄或列中,輸入變數的值,結果會顯示在相鄰的欄或列。

如需詳細資訊,請參閱PMT 函數

下圖中,儲存格 D2 中含有償還公式 =PMT(B3/12,B4,-B5),其中參照了變數儲存格 B3。

具有一個變數的運算列表

單變數資料表

雙變數資料表    使用雙變數資料表來查看某個公式中兩個不同的變數如何影響該公式的結果。例如,您可以使用雙變數資料表來查看不同利率和貸款條款的組合對每月抵押償還的影響。

下圖中,儲存格 C2 中是償還公式 =PMT(B3/12,B4,-B5),其中使用了兩個變數儲存格 B3 和 B4。

有兩個變數的運算列表

雙變數資料表

運算列表計算    每當重新計算工作表時,即使運算列表沒有任何變更,也會一併重新計算。若要加快含運算列表之工作表的計算速度,可藉由變更 [計算] 選項以便自動重新計算工作表,而不是計算其中的運算列表。請參閱加速包含運算列表之工作表的計算一節。

頁面頂端​

建立單變數資料表

單變數資料表具有以欄 (欄方向) 或列 (列方向) 的方式列示輸入值。用於單變數資料表中的公式僅能參照到某個變數儲存格。

  1. 輸入您想要在變數儲存格中替代一欄或一列的數值清單。請在數值的任一端保留幾個空白列和欄。

  2. 請執行下列其中一項:

    • 如果運算列表是欄方向 (您的變數值位於欄中),請在數值欄上方一列和右邊一欄的儲存格中輸入公式。<概觀>一節顯示的單變數運算列表圖中即是採用欄方向,而公式包含在儲存格 D2 中。

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

    • 如果運算列表是列方向 (您的變數值位於列中),請在第一個數值左邊那一欄和數值列正下方的儲存格中輸入公式。

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

  3. 選取含有您想要替代之公式和數值的儲存格範圍。根據先前<概觀>一節中第一個圖所示,此範圍為 C2:D5。

  4. [資料] 索引標籤上,按一下 [資料工具] 群組中的 [模擬分析],然後按一下 [運算列表]

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

    • 如果該資料表格是欄方式,請在 [欄變數儲存格] 方塊內輸入變數儲存格的儲存格參照。使用第一個圖中所示的範例,其變數儲存格為 B3。

    • 如果資料表格是以循列的方式,請在 [列變數儲存格] 方塊內輸入變數儲存格的儲存格參照。

      附註: 在建立資料表後,您可能想要變更目標儲存格的格式。在圖中,目標儲存格已格式化為貨幣格式。

頁面頂端​

將公式新增到單變數資料表

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

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

    • 如果該資料表格是欄方式 (您的變數值位於欄中),請將新的公式輸入資料表第一列現有公式右邊的空白儲存格內。

    • 如果該資料表格是列方式 (您的變數值位於列中),請將新的公式輸入資料表第一欄現有公式底下的空白儲存格內。

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

  3. [資料] 索引標籤上,按一下 [資料工具] 群組中的 [模擬分析],然後按一下 [運算列表]

  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. [資料] 索引標籤上,按一下 [資料工具] 群組中的 [模擬分析],然後按一下 [運算列表]

  6. [列變數儲存格] 方塊中,輸入對某列輸入值之變數儲存格的參照。

    [列變數儲存格] 方塊中輸入 B4

  7. [欄變數儲存格] 方塊中,輸入對某欄輸入值之變數儲存格的參照。
    [欄變數儲存格] 方塊中輸入 B3

  8. 按一下 [確定]

範例    雙變數資料表可顯示不同利率和貸款條款組合對每月抵押償還的影響。下圖中,儲存格 C2 中含有償還公式 =PMT(B3/12,B4,-B5),其中使用了兩個變數儲存格 B3 和 B4。

有兩個變數的運算列表

頁面頂端​

加速包含運算列表之工作表的計算

  1. 按一下 [檔案] 索引標籤,然後按一下 [選項],再按一下 [公式] 類別。

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

    提示: 或者,按一下 [公式] 索引標籤上 [計算] 群組中 [重算選項] 上的箭號,然後按一下 [除資料表外,自動重算]

附註: 當您選取這個計算選項時,重新計算活頁簿的其他部分時將略過資料表格。如要手動重新計算您的資料表格,請選取公式並按下 F9 鍵。

頁面頂端​

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

這項資訊有幫助嗎?

太好了! 還有其他意見反應嗎?

我們應該如何改進?

感謝您的意見反應!

×