在Excel2016中常用的數(shù)據(jù)分析工具

一、模擬運(yùn)算表概述

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

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

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

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

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

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

圖2  輸入銷售量和公式

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

圖3　單擊“模擬運(yùn)算表”選項(xiàng)

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

圖4 “模擬運(yùn)算表”對話框

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

圖5　完成運(yùn)算

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

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

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

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

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

圖7　選擇“模擬運(yùn)算表”選項(xiàng)

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

圖8　指定引用單元格

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

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

技巧點(diǎn)撥

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