學(xué)習(xí)教程：常用的數(shù)據(jù)分析工具

一、模擬運算表概述

模擬運算表是一個單元格區(qū)域，它可顯示一個或多個公式中替換不同值時的結(jié)果。運算表根據(jù)需要觀察的數(shù)據(jù)變量多少，可以分為單變量數(shù)據(jù)表和多變量數(shù)據(jù)表兩種形式。單變量數(shù)據(jù)表中，用戶可以對一個變量鍵入不同的值從而查看它對一個或多個公式的影響。多變量數(shù)據(jù)表中，用戶對多個變量輸入不同值，而查看它對一個公式的影響。

二、單變量數(shù)據(jù)表運算

在單變量數(shù)據(jù)運算表中，可以對一個單變量輸入不同的值來查看它對一個或多個公式的影響。例如根據(jù)產(chǎn)品的不同銷售量計算產(chǎn)品所取得的純利潤。具體操作如下。

步驟1：在工作表中輸入總成本、單位售價、預(yù)計銷售量、銷售總收入等數(shù)據(jù)，在單元格B5中輸入公式“=B3*B4”，按回車鍵，然后在單元格B6中輸入公式“=B5-B2”，按回車鍵，計算出預(yù)計銷售量為20000時的純利潤，如圖1所示。

圖1　目標(biāo)數(shù)據(jù)

步驟2：在單元格A8和B8中分別輸入“實際銷售量”和“純利潤”。在單元格區(qū)域A10:A13中分別輸入如圖2所示的銷售量，并在單元格B9中輸入公式“=B5-B2”，按回車鍵即可。

圖2  輸入銷售量和公式

步驟3：選擇單元格區(qū)域A9:B13，選擇“數(shù)據(jù)”選項卡“數(shù)據(jù)工具”單元組中的“模擬分析”按鈕，從彈出的菜單中單擊“模擬運算表”選項，如圖3所示。

圖3　單擊“模擬運算表”選項

步驟4：彈出“模擬運算表”對話框，在“輸入引用列的單元格”中輸入單元格的引用地址“$B$4”，表示不同的銷售量，單擊“確定”按鈕，如圖4所示。

圖4 “模擬運算表”對話框

步驟5：查看利潤運算結(jié)果，如圖5所示。

圖5　完成運算

三、雙變量數(shù)據(jù)表運算

在雙變量數(shù)據(jù)表運算中，在為兩個變量輸入不同的值時查看它對一個公式值的影響變化。下面以創(chuàng)建多變量數(shù)據(jù)表為例介紹在工作表中使用模擬運算表的方法，本例數(shù)據(jù)表用于預(yù)測不同銷售金額和不同提成比率所對應(yīng)的提成金額，創(chuàng)建的是一個有兩個變量的模擬運算表。

步驟1：創(chuàng)建一個新的工作表，在工作表中輸入數(shù)據(jù)。在工作表的B9單元格中輸入提成額的計算公式“=$B$2*$B$3”，如圖6所示。

圖6　創(chuàng)建工作表并輸入公式

步驟2：選中創(chuàng)建運算表的單元格區(qū)域，在“數(shù)據(jù)”選項卡的“數(shù)據(jù)工具”組中單擊“模擬分析”按鈕，在下拉列表中選擇“模擬運算表”選項，如圖7所示。

圖7　選擇“模擬運算表”選項

步驟3：打開“模擬運算表”對話框，在“輸入引用行的單元格”文本框中單擊銷售金額值所在單元格生成地址“$B$2”，在“輸入引用列的單元格”文本框中單擊提成比率值所在單元格生成地址“$B$3”;完成單元格的引用后，單擊“確定”按鈕關(guān)閉對話框，如圖8所示。

圖8　指定引用單元格

步驟4：工作表中插入了數(shù)據(jù)表，通過該數(shù)據(jù)表將能查看不同的銷售金額和不同提成比率下對應(yīng)的提成金額，如圖9所示。

圖9　創(chuàng)建模擬運算表

技巧點撥

模擬運算表中的數(shù)據(jù)是存放在數(shù)組中的，表中的單個或部分?jǐn)?shù)據(jù)是無法刪除的。要想刪除數(shù)據(jù)表中的數(shù)據(jù)，只能選擇所有數(shù)據(jù)后再按Delete鍵。