項(xiàng)目九 Excel 在統(tǒng)計(jì)分析中的應(yīng)用

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

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

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

第2步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項(xiàng)。第3步：在分析工具中選擇“描述統(tǒng)計(jì)”。第4步：當(dāng)出現(xiàn)對(duì)話(huà)框時(shí)，在“輸入?yún)^(qū)域”方框內(nèi)鍵入A1：A51(或用鼠標(biāo)選擇這區(qū)域)，“分組方式”選擇“逐列”單選框，因?yàn)椤拜斎雲(yún)^(qū)域”包含了“成績(jī)”這個(gè)標(biāo)志，所以勾選“標(biāo)志位于第一行”復(fù)選框，在“輸出選項(xiàng)”中選擇輸出區(qū)域(在此選擇$D$1)，再選擇“匯總統(tǒng)計(jì)”(該選項(xiàng)給出全部描述統(tǒng)計(jì)量)，如圖9－9。最后，選擇確定。

Excel在統(tǒng)計(jì)分析中的應(yīng)用9.4.1用Excel進(jìn)行相關(guān)分析第1步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項(xiàng)。

第2步：在分析工具中選擇“相關(guān)系數(shù)”。

第3步：當(dāng)出現(xiàn)對(duì)話(huà)框時(shí)，在“輸入?yún)^(qū)域”方框內(nèi)鍵入A1：B11，勾選“標(biāo)志位于第一行”復(fù)選框，在“輸出選項(xiàng)”中選擇輸出區(qū)域選擇“新工作表組”。得圖9－12，點(diǎn)擊“確定”。Excel在統(tǒng)計(jì)分析中的應(yīng)用9.4.2用Excel進(jìn)行回歸分析第1步：選擇“工具”下拉菜單，選擇“數(shù)據(jù)分析”選項(xiàng)。

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

第3步：當(dāng)出現(xiàn)對(duì)話(huà)框時(shí)，在“輸入Y的區(qū)域”方框內(nèi)鍵入B2：B11，在“輸入X的區(qū)域”方框內(nèi)鍵入A2：A11，在“輸出選項(xiàng)”中選擇“新工作表”，如圖9－14。點(diǎn)擊“確定”。得到圖9－15所示的結(jié)果。Excel在統(tǒng)計(jì)分析中的應(yīng)用所以，該例題中得到的一元線(xiàn)性回歸方程為：

Excel在統(tǒng)計(jì)分析中的應(yīng)用9.5.1用移動(dòng)平均法進(jìn)行預(yù)測(cè)根據(jù)9.4.1中的表9－3中的資料，利用Excel對(duì)其進(jìn)行三項(xiàng)移動(dòng)平均。操作步驟：第1步：將原始數(shù)據(jù)錄入Excel表格中，如圖9－16所示。Excel在統(tǒng)計(jì)分析中的應(yīng)用第2步：選擇“工具”下拉菜單，選擇“數(shù)據(jù)分析”選項(xiàng)。

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