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

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

運算列表是一個儲存格範圍,您可以在某些儲存格中變更值以針對問題產生不同的解答。使用 PMT 函數是個很好的範例,可透過不同的貸款金額與利率來算出您可以負擔多少房屋或汽車貸款。而嘗試各種可變更的值以產生不同的結果,是資料分析領域的一部分。

運算列表是一組稱為模擬分析工具命令的一部分。當您使用運算列表時,就是在進行模擬分析。

模擬分析是變更儲存格中的值,以查看這些變更如何影響工作表上公式結果的處理程序。例如,您可以使用運算列表來變更貸款中使用的利率和貸款期數,以決定可能的每月支付款項。

假設狀況分析的種類    Excel 內有三種假設狀況分析工具:分析藍本、資料表和目標搜尋。分析藍本和資料表會使用一組輸入值並決定可能的結果。「目標搜尋」的運作方式和與分析藍本及資料表不同,其取得結果並藉以判定產生該結果的可能輸入值。

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

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

模擬分析的相關資訊,請參閱模擬分析的簡介

運算列表基礎

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

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

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

具有一個變數的運算列表

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

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

有兩個變數的運算列表

資料表格的計算   運算列表重新計算工作表重新計算時, 即使他們沒有變更。若要加速包含運算列表的工作表中的計算,您可以變更自動重新計算工作表,但不是運算列表計算選項]。請參閱加速包含運算列表的工作表中的計算

建立單變數資料表

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

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

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

    • 如果運算列表是以循欄的方式 (您變數中值的資料行),輸入上方的儲存格一列中的公式和數值欄右邊的一個儲存格。單變數運算列表圖例概觀一節中顯示欄方向,而儲存格 D2 中包含公式。
      如果您想要查看其他公式各種值的效果,請右邊的第一個公式的儲存格內輸入其他的公式。

    • 如果運算列表是列方向 (您的變數值位於列中),請在第一個數值左邊那一欄和數值列正下方的儲存格中輸入公式。
      如果您想要檢查各種數值對其他公式的影響,請在第一個公式下方的儲存格中輸入其他公式。

  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),按一下 [模擬分析,,然後按一下運算列表

  6. [列變數儲存格] 方塊中輸入資料列中輸入值之變數儲存格的參照。
    列變數儲存格] 方塊中輸入儲存格 B4

  7. 在 [欄變數儲存格] 方塊中輸入資料行中輸入值之變數儲存格的參照。
    欄變數儲存格] 方塊中輸入B3

  8. 按一下 [確定]

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

有兩個變數的運算列表

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

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

    • 在Excel 2007,按一下 [ Microsoft Office 按鈕] Office 按鈕影像 、 按一下 [ Excel 選項],然後按一下 [公式] 類別。

    • 在所有其他版本中,按一下 [檔案>選項>公式

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

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

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

下一步是什麼?

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

目標搜尋

如果您知道您想要從公式,,但不確定哪些輸入值,公式的結果所需的結果,請使用 [目標搜尋] 功能。請參閱使用目標搜尋來尋找您想要調整輸入的值的結果

Excel 規劃求解

您可以使用 Excel 規劃求解增益集來尋找最佳根據變數的數字的值。規劃求解搭配 (稱為決策變數或只是變數儲存格) 儲存格,用於計算的公式中的目標和限制的儲存格群組。規劃求解調整決策變數儲存格符合限制式的儲存格的限制,並產生您想要的目標儲存格的結果中的值。請參閱定義和解決問題使用規劃求解

頁面頂端

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

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×