資料透視表中的假設分析

使用Excel中的資料透視表，您可以輕鬆更改一個或兩個輸入值並執行假設分析。資料透視表是一個單元格區域，您可以更改其中某些單元格的值，從而得出問題的不同答案。

資料透視表有兩種型別：

單變數資料透視表
雙變數資料透視表

如果您的分析問題中有多於兩個變數，則需要使用Excel的場景管理器工具。詳情請參閱本教程中的章節 – 使用場景管理器進行假設分析。

單變數資料透視表

如果您想檢視一個或多個公式中一個變數的不同值將如何更改這些公式的結果，則可以使用單變數資料透視表。換句話說，使用單變數資料透視表，您可以確定更改一個輸入如何更改任意數量的輸出。我們將透過一個示例來理解這一點。

示例

有一筆為期30年的5,000,000貸款。您想知道不同利率下的月供（EMI）。您可能還想知道第二年支付的利息和本金金額。

使用單變數資料透視表進行分析

使用單變數資料透視表進行分析需要三個步驟：

步驟1 - 設定所需的背景。

步驟2 - 建立資料透視表。

步驟3 - 執行分析。

讓我們詳細瞭解這些步驟：

步驟1：設定所需的背景

假設利率為12%。
列出所有所需的值。
為包含值的單元格命名，以便公式使用名稱而不是單元格引用。
分別使用Excel函式–PMT、CUMIPMT和CUMPRINC設定EMI、累計利息和累計本金的計算。

您的工作表應如下所示：

您可以看到C列中的單元格名稱如D列中相應的單元格所示。

步驟2：建立資料透視表

鍵入您想要替換輸入單元格的值列表（即利率），如下所示，在E列下方：

如您所見，利率值上方有一行空行。此行用於您想要使用的公式。

在值列上方和右側的一個單元格中鍵入第一個函式（PMT）。在第一個函式的右側單元格中鍵入其他函式（CUMIPMT和CUMPRINC）。

現在，利率值上方的兩行如下所示：

資料透視表如下所示：

步驟3：使用假設分析資料透視表工具進行分析

選擇包含公式和要替換的值的單元格範圍，即選擇範圍 – E2:H13。
單擊功能區上的“資料”選項卡。
在“資料工具”組中單擊“假設分析”。
在下拉列表中選擇“資料透視表”。

資料透視表對話框出現。

單擊“列輸入單元格”框中的圖示。
單擊單元格Interest_Rate（C2）。

您可以看到列輸入單元格被設定為$C$2。單擊“確定”。

資料透視表將填充每個輸入值的計算結果，如下所示：

如果您能支付54,000的EMI，您可以觀察到12.6%的利率適合您。

雙變數資料透視表

如果您想檢視公式中兩個變數的不同值將如何更改該公式的結果，則可以使用雙變數資料透視表。換句話說，使用雙變數資料透視表，您可以確定更改兩個輸入如何更改單個輸出。我們將透過一個示例來理解這一點。

示例

有一筆50,000,000的貸款。您想知道利率和貸款期限的不同組合將如何影響月供（EMI）。

使用雙變數資料透視表進行分析

使用雙變數資料透視表進行分析需要三個步驟：

步驟1 - 設定所需的背景。

步驟2 - 建立資料透視表。

步驟3 - 執行分析。

步驟1：設定所需的背景

假設利率為12%。
列出所有所需的值。
為包含值的單元格命名，以便公式使用名稱而不是單元格引用。
使用Excel函式–PMT設定EMI的計算。

您的工作表應如下所示：

您可以看到C列中的單元格名稱如D列中相應的單元格所示。

步驟2：建立資料透視表

在單元格F2中鍵入=EMI。

鍵入第一個輸入值列表（即利率），在公式下方（即F3）的F列中向下鍵入。
鍵入第二個輸入值列表（即付款次數），在公式右側（即G2）的第2行中向右鍵入。

資料透視表如下所示：

使用假設分析工具資料透視表進行分析

選擇包含公式和要替換的兩組值的單元格範圍，即選擇範圍 – F2:L13。
單擊功能區上的“資料”選項卡。
在“資料工具”組中單擊“假設分析”。
從下拉列表中選擇資料透視表。

資料透視表對話框出現。

單擊“行輸入單元格”框中的圖示。
單擊單元格NPER（C3）。
再次單擊“行輸入單元格”框中的圖示。
接下來，單擊“列輸入單元格”框中的圖示。
單擊單元格Interest_Rate（C2）。
再次單擊“列輸入單元格”框中的圖示。

您將看到行輸入單元格被設定為$C$3，列輸入單元格被設定為$C$2。單擊“確定”。

資料透視表將填充每種組合的兩個輸入值的計算結果：

如果您能支付54,000的EMI，則12.2%的利率和288次EMI適合您。這意味著貸款期限為24年。

資料透視表計算

每次重新計算包含資料透視表的工作表時，都會重新計算資料透視表，即使它們沒有更改。為了加快包含資料透視表的工作表的計算速度，您需要將計算選項更改為自動重新計算工作表，但不包括資料透視表，如下一節所述。

加快工作表中的計算速度

您可以透過兩種方式加快包含資料透視表的工作表的計算速度：

從Excel選項。
從功能區。

從Excel選項

單擊功能區上的“檔案”選項卡。
從左側窗格中的列表中選擇“選項”。

Excel選項對話框出現。

從左側窗格中選擇公式。
在“計算選項”部分的“工作簿計算”下選擇選項“自動，但資料透視表除外”。單擊“確定”。

從功能區

單擊功能區上的“公式”選項卡。
單擊“計算”組中的計算選項。
在下拉列表中選擇“自動，但資料透視表除外”。

列印頁面