項目九 Excel 在統計分析中的應用

Excel在統計分析中的應用操作步驟：第1步：選擇“工具”下拉菜單，選擇“加載宏”命令。第2步：在彈出的“加載宏”的對話框中選擇“分析工具庫”復選框，單擊“確定”按鈕（如圖9-1），即可安裝好“分析工具庫”插件。安裝完成后，在“工具”菜單欄中就出現了一個“數據分析”的命令（如圖9-2）.第3步：選擇“數據分析”命令，打開“分析工具庫”對話框（圖9-3），就可以見到統計中常用的數據分析模塊，如相關系數、描述統計、回歸、抽樣等功能。9.1Excel數據分析程序的安裝圖9-1加載分析庫工具庫的界面

圖9-2加載分析工具庫后“工具”菜單

圖9-3數據分析對話框Excel在統計分析中的應用9.2用Excel整理統計數據第1步：打開Excel界面，在“A1”單元格輸入“業(yè)務收入計劃”，并從上到下依此在“A列”輸入40個分公司的數據。根據實際的需要可以將數據分成若干個組，可以將“B1”單元格輸入“各組上限”，請注意，Excel在統計各組頻數時，是按“各組上限在包含在本組內”的原則處理的。第2步：選擇“工具”下拉菜單，選擇“數據分析”。第3步：在分析工具中選擇“直方圖”，如圖9－4。Excel在統計分析中的應用第4步：當出現“直方圖”對話框時，在“輸入區(qū)域”方框內鍵入$A$1：$A$41（“$”符號起到固定單元格坐標的作用，表示絕對引用地址），40個數據已輸入該區(qū)域內（如果是分組排列的，就應選擇整個矩形區(qū)域）。在“接收區(qū)域”方框內鍵入$B$1：$B$9，因為在“輸入區(qū)域”和“接收區(qū)域”都包含了標志，所以勾選“標志”復選框。在“輸出區(qū)域”方框內鍵入$D$1。對話框中，勾選“累積百分率”、“圖表輸出”復選框（如圖9－5）。最后點擊“確定”，就可得到結果。Excel在統計分析中的應用4輸入區(qū)域：在此輸入待分析數據區(qū)域的單元格范圍。接收區(qū)域（可選）：在此輸入接收區(qū)域的單元格范圍，該區(qū)域應包含一組可選的用來計算頻數的邊界值。這些值應當按升序排列。標志：如果輸入區(qū)域的第一行或第一列中包含標志項，則選中此復選框；輸出區(qū)域：在此輸入計算結果顯示的單元格地址。柏拉圖：選中此復選框，可以在輸出表中同時顯示按升序、降序排列頻率數據。如果此復選框被清除，Excel將只按升序來排列數據。累積百分比：選中此復選框，可以在輸出結果中添加一列累積百分比數值，并同時在直方圖表中添加累積百分比折線。圖表輸出：選中此復選框，可以在輸出表中同時生成一個嵌入式直方圖表。有關結果如圖9－6。完整的結果通常包括三列和一個頻數分布圖，第一列是數值的區(qū)間范圍，第二列是數值分布的頻數（不是頻率），第三列是頻數分布的累積百分比。Excel在統計分析中的應用Excel在統計分析中的應用用鼠標左鍵單擊圖中任一直條形，然后右鍵單擊，在彈出的快捷菜單中選取“數據系列”格式，彈出“數據系列格式”對話框。在對話框中選擇“選項”標簽，把“分類間距”寬度改為0，如圖9－7所示。按確定后即可得到直方圖，見圖9－8所示。Excel在統計分析中的應用9.3用Excel計算描述統計量第1步：打開Excel界面，在“A1”單元格輸入“成績”，并從上到下依此在“A列”輸入50個學生的成績。

第2步：選擇“工具”下拉菜單，再選擇“數據分析”選項。第3步：在分析工具中選擇“描述統計”。第4步：當出現對話框時，在“輸入區(qū)域”方框內鍵入A1：A51(或用鼠標選擇這區(qū)域)，“分組方式”選擇“逐列”單選框，因為“輸入區(qū)域”包含了“成績”這個標志，所以勾選“標志位于第一行”復選框，在“輸出選項”中選擇輸出區(qū)域(在此選擇$D$1)，再選擇“匯總統計”(該選項給出全部描述統計量)，如圖9－9。最后，選擇確定。

Excel在統計分析中的應用9.4.1用Excel進行相關分析第1步：選擇“工具”下拉菜單，再選擇“數據分析”選項。

第2步：在分析工具中選擇“相關系數”。

第3步：當出現對話框時，在“輸入區(qū)域”方框內鍵入A1：B11，勾選“標志位于第一行”復選框，在“輸出選項”中選擇輸出區(qū)域選擇“新工作表組”。得圖9－12，點擊“確定”。Excel在統計分析中的應用9.4.2用Excel進行回歸分析第1步：選擇“工具”下拉菜單，選擇“數據分析”選項。

第2步：在分析工具中選擇“回歸”。

第3步：當出現對話框時，在“輸入Y的區(qū)域”方框內鍵入B2：B11，在“輸入X的區(qū)域”方框內鍵入A2：A11，在“輸出選項”中選擇“新工作表”，如圖9－14。點擊“確定”。得到圖9－15所示的結果。Excel在統計分析中的應用所以，該例題中得到的一元線性回歸方程為：

Excel在統計分析中的應用9.5.1用移動平均法進行預測根據9.4.1中的表9－3中的資料，利用Excel對其進行三項移動平均。操作步驟：第1步：將原始數據錄入Excel表格中，如圖9－16所示。Excel在統計分析中的應用第2步：選擇“工具”下拉菜單，選擇“數據分析”選項。

第3步：在分析工具中選擇“移動平均”。第4步：彈出移動平均對話框，相應作如下輸入，即可得到如附圖9－3所示的對話框。在“輸入區(qū)域”內輸入：“$B$2：$B$11”，即原始數據所在的單元格區(qū)域。在“間隔”內輸入：“3”，表示使用三步移動平均法。在“輸出區(qū)域”內輸入：“$C$1”，即將輸出區(qū)域的左上角單元格定義為$C$1。選擇“圖表輸出”復選框和“標準誤差”復選框。第5步：單擊“確定”按鈕。便可得到移動平均結果，如圖9－18所示。分析：在附圖9－18中，“C3：C10”對應的數據即為三步移動平均的預測值；單元格區(qū)域“D5：D10”即為標準誤差。Excel在統計分析中的應用圖9－17“移動平均”對話框“移動平均”輸出結果Excel在統計分析中的應用9.5.2用指數平滑法進行預測根據9.4.1中的表9－3中的資料，利用Excel對其進行指數平滑法進行預測。操作步驟：第1步：將原始數據錄入Excel表格中，如圖9－16所示。第2步：選擇“工具”下拉菜單。第3步：選擇“數據分析”選項。第4步：在分析工具中選擇“指數平滑”，單擊“確定”按鈕。第5步：在彈出“指數平滑”對話框，作相應輸入，即可得到如圖9－19所示的對話框。在“輸入區(qū)域”內輸入：“$B$2：$B$11”，即原始數據所在的單元格區(qū)域。在“阻尼系數”內輸入：“0.5”（阻尼系數=1-平滑系數=1-0.5=0.5）。在“輸出區(qū)域”內輸入：“$D$2”，即將輸出區(qū)域的左上角單元格定義為$D$2。選擇“圖表輸出”復選框和“標準誤差”復選框。Excel在統計分析中的應用Excel在統計分析中的應用第6步：單擊“確定”按鈕。經過以上各步驟操作，即可得到指數平滑結果，如圖9－20所示?！癈3：C12”對應的數據即為指數平滑的預測值；單元格區(qū)域“E6：E11”即為標準誤差。Excel在統計分析中的應用9.5.2用回歸法進行預測根據9.4.1中的表9－2中的資料，利用Excel對其進行指數平滑法進行預測。操作步驟：第1步：將原始數據錄入Excel表格中，為簡化計算，設置時間序號t列，如圖9－21所示。第2步：選擇“工具”下拉菜單。第3步：選擇“數據分析”選項。第4步：在分析工具中選擇“回歸”，單擊“確定”按鈕。第5步：當出現對話框時，在“Y值輸入區(qū)域”方框內輸入：“$B$2：$B$11”，在“X值輸入區(qū)域”方框內輸入：“$C$2：$C$11”，在“輸出選項”中選擇“輸出區(qū)域”，“輸出區(qū)域”方框中輸入：“$E$1”，勾選“線性擬合圖”復選框，如圖9－22。點擊“確定”，得到回歸分析結果和線性擬合圖，如圖9－23。Excel在統計分析中的應用圖9－21輸入原始數據圖9－22“回歸”對話框Excel在統計分析中的應用圖9－23回歸分析結果和線性擬合圖Excel在統計分析中的應用第6步：在線性擬合圖的任意數據點上右擊，在彈出的菜單中執(zhí)行“添加趨勢線”命令，彈出“添加趨勢線”對話框。在該對話框的“類型”選項卡中選擇“線性”趨勢預測/回歸分析類型，在“選擇數據系列”中