應(yīng)用統(tǒng)計分析上機實驗指導(dǎo)

1、應(yīng)用統(tǒng)計分析上機實驗指導(dǎo)目 錄實驗1 用Excel搜集與整理數(shù)據(jù)11.1 借助Excel搜集數(shù)據(jù)11.2 用Excel進行統(tǒng)計分組21.3 用Excel作統(tǒng)計圖5實驗2 用Excel計算描述統(tǒng)計量52.1 用函數(shù)計算描述統(tǒng)計量62.2 描述統(tǒng)計工具的使用6實驗3 用Excel進行參數(shù)估計和假設(shè)檢驗73.1 用Excel進行區(qū)間估計83.2 用Excel進行假設(shè)檢驗10實驗4 用Excel進行方差分析124.1 單因素方差分析124.2 無交互作用的雙因素方差分析134.3 有交互作用的雙因素方差分析14實驗5 用Excel進行相關(guān)與回歸分析145.1 用Excel進行相關(guān)分析155.1.1 利

2、用函數(shù)計算相關(guān)系數(shù)155.1.2 用相關(guān)系數(shù)工具計算155.2 用Excel進行回歸分析165.2.1 一元線性回歸165.2.2 多元線性回歸17實驗6 時間序列分析和預(yù)測186.1 增減速度分析18*6.2 平穩(wěn)序列預(yù)測186.2.1 移動平均法186.2.2 指數(shù)平滑法196.3 趨勢型序列的預(yù)測196.4 季節(jié)型序列的預(yù)測196.4.1 季節(jié)型多元回歸預(yù)測196.4.2 復(fù)合型序列的分解預(yù)測20進度安排實驗項目學(xué)時實驗1實驗34實驗4實驗64社會調(diào)查數(shù)據(jù)搜集、分析、撰寫報告71 / 32實驗1 用Excel搜集與整理數(shù)據(jù)【實驗?zāi)康摹空莆沼肊xcel進行數(shù)據(jù)的搜集、整理和顯示?！緦嶒炓?/p>

3、】1、驗證本指導(dǎo)手冊中的操作；2、在Excel中完成教材P25例題的操作；3、在Excel中完成應(yīng)用統(tǒng)計分析技能訓(xùn)練P12分析題。1.1 借助Excel搜集數(shù)據(jù)搜集數(shù)據(jù)的方法有多種，可以采用統(tǒng)計報表、抽樣調(diào)查或普查，尤以抽樣調(diào)查較為常見，所以我們在這里介紹一下如何用Excel進行抽樣。使用Excel進行抽樣，首先要對各個總體單位進行編號，編號可以按隨機原則，也可以按有關(guān)標志或無關(guān)標志。【例】假定有100個總體單位，從1到100編號。需要在這100個單位中抽取20個單位組成樣本?！静僮鞑襟E】【方式一：產(chǎn)生隨機數(shù)方式】（1）使用函數(shù)產(chǎn)生隨機數(shù)。使用Excel函數(shù)“RANDBETWEEN(a,b)”

4、生成a,b區(qū)間范圍內(nèi)的隨機整數(shù)。在Excel單元格中輸入=RANDBETWEEN(1,100)然后通過填充（使用單元格填充柄）得到等于樣本容量個數(shù)的隨機數(shù)，這些隨機數(shù)對應(yīng)編號的個體即被抽出。（注：如果該函數(shù)不可用，并返回錯誤值 #NAME?，請加載“分析工具庫”宏。加載方法是：在“工具”菜單下選擇“加載宏”，在彈出的對話框中勾選“分析工具庫”，然后確定加載。如果您的Excel是不完整安裝的，可能會提示插入Office安裝光盤。）（2）使用宏工具產(chǎn)生隨機數(shù)。單擊“工具”菜單，選擇“數(shù)據(jù)分析”（注：如果“工具”菜單中沒有該項，也需如前加載“分析工具庫”），從中選擇“隨機數(shù)發(fā)生器”。在彈出的對話框中

5、如下圖輸入和選擇，點確定后即可獲得1100范圍的隨機實數(shù)，再四舍五入獲得隨機整數(shù)（方法：重復(fù)點擊工具欄“減少小數(shù)位數(shù)”按鈕“”直至顯示整數(shù)，或使用函數(shù)ROUND）。【方式二：隨機抽樣方式】1、將各總體單位的編號輸入工作表（無規(guī)律的非連續(xù)編號需手工逐個輸入。連續(xù)編號可使用“填充”功能快捷輸入）；2、單擊“工具”菜單，選擇“數(shù)據(jù)分析”，在彈出的對話框中選擇“抽樣”。3、彈出“抽樣”對話框。如圖11圖11 抽樣對話框1）在“輸入?yún)^(qū)域”框中輸入總體單位編號所在的單元格區(qū)域，本例是A1:A100。如果輸入?yún)^(qū)域的第一行或第一列為標志項（橫行標題或縱列標題），注意點擊“標志”復(fù)選框。2）在“抽樣方法”中，有

6、周期和隨機兩種抽樣模式：“周期”模式即所謂的等距抽樣，采用這種抽樣方法，需將總體單位數(shù)除以要抽取的樣本單位數(shù)，求得取樣的周期間隔。比如要在100個單位中抽取12個，則在“間隔”框中輸入8?！半S機”模式適用于簡單隨機抽樣，只需在“樣本數(shù)”框中輸入樣本容量即可。3）指定輸出選項，這里我們指定“輸出區(qū)域”為C1，單擊確定后，即得到抽樣結(jié)果。1.2 用Excel進行統(tǒng)計分組用Excel統(tǒng)計分組的各組的頻數(shù)，有兩種方法：一是利用數(shù)組函數(shù)FREQUENCY；二是利用數(shù)據(jù)分析中的“直方圖”工具?！纠拷滩腜25例：某企業(yè)100名職工工資資料：480250440220350350420380360350350

7、280340320520430500440480460580470330410420350380470610390400450390460290510320380410530440380470570260400470560430600580450420560520410520390480230320370550420370290310400300520360510470430540570460510480340550510450500470440370420480540300400530550530200650630540590這里采用的分組組限是（對一組數(shù)據(jù)如何分組和確定組限，請參見統(tǒng)計學(xué)教

8、材）：200-260,260-320,320-380,380-440,440-500,500-560,560-620,620-680?！静僮鞑襟E】【方法一】利用FREQUENCY函數(shù)：1、將原始數(shù)據(jù)錄入在A1:J10單元格；將分組上限輸入A13:A20單元格（注意：分組的上限值應(yīng)當按升序排列。對于數(shù)值型數(shù)據(jù)，Excel在統(tǒng)計時把“上限值”包含在了該組內(nèi)，這與我們統(tǒng)計學(xué)上的習(xí)慣規(guī)定“上組限不在內(nèi)”不同，因此，建議輸入的分組上限值是：259、319、379、439、499、559、619、679。Excel將按(a,b區(qū)間模式計數(shù)）。2、選中B13:B21單元格后，輸入“=FREQUENCY(A1

9、:J10,A13:A20)”，按組合鍵“Ctrl+Shift+Enter”，Excel即返回頻數(shù)統(tǒng)計結(jié)果。如下圖：3、在單元格C13編寫公式計算頻率“=B13/sum(B$13:B$20)”（注意恰當使用單元格的相對引用與絕對引用$），并向下填充得到各組頻率；在單元格D13編寫公式計算向上累計頻數(shù)“=sum(B$13:B13)”，并向下填充得到各組累計頻數(shù)。4、根據(jù)各組頻數(shù)（或頻率）繪制直方圖，參見1.3節(jié)?！痉椒ǘ坷谩爸狈綀D”工具：1、在“工具”菜單中單擊“數(shù)據(jù)分析”選項，從其對話框的分析工具列表中選擇“直方圖”，打開直方圖對話框。如圖12所示：圖12 直方圖對話框2、在“輸入?yún)^(qū)域”輸入

10、需要分組的原始數(shù)據(jù)所在的單元格區(qū)域，本例為A1:J10。在“接收區(qū)域”輸入定義接收區(qū)域邊界值的單元格引用，本例已在A13:A20輸入數(shù)值“259、319、379、439、499、559、619、679”，因而，接收區(qū)域中輸入“A13:A20”。3、選擇輸出選項。這里選擇B12為起始的“輸出區(qū)域”。另外，如果在如圖1-2的對話框中勾選“柏拉圖”，可得到按降序排列的柱形圖（帕累托圖）；勾選“累計百分率”，Excel將在直方圖上添加累積頻率折線；勾選“圖表輸出”，可以得到直方圖。圖13 頻數(shù)分布和直方圖應(yīng)當注意，上圖的輸出實際上是一個“條形圖”的形式，若要把它變成“直方圖”形式，可按如下操作：用鼠標

11、右鍵單擊任一長方形柱條，在彈出的快捷菜單中選擇“數(shù)據(jù)系列格式”，在之后彈出的對話框中點擊“選項”標簽，把“分類間距”寬度改為“0”，點擊確定即可。如圖14所示：圖14 數(shù)據(jù)系列格式對話框【練習(xí)】用Excel“工具”-“數(shù)據(jù)分析”中的“隨機數(shù)發(fā)生器”產(chǎn)生300個服從正態(tài)分布（均值為1500，標準差為260）的數(shù)據(jù)，然后對其進行適當?shù)慕y(tǒng)計分組，統(tǒng)計頻數(shù)，并繪制出直方圖（注意觀察圖形是否呈對稱分布）。1.3 用Excel作統(tǒng)計圖Excel提供的統(tǒng)計圖有多種，包括柱形圖、條形圖、折線圖、餅圖、散點圖、面積圖、環(huán)形圖、雷達圖、曲面圖、氣泡圖、股價圖、圓柱圖、圓錐圖等，各種圖的作法大同小異?！纠窟@里采用

12、教材P22表2.2的資料，看一下如何作餅圖。按所有制分組企業(yè)數(shù)全民3204集體1286私營152中外合資212外商獨資102其它44合計5000【操作步驟】1、選中某一單元格，單擊“插入”菜單，選擇“圖表”選項（或直接點擊工具欄“”圖標），彈出圖表向?qū)υ捒颉?、在“圖表類型”中選擇“餅圖”，然后在“子圖表類型”中選擇一種類型，然后單擊下一步按鈕，打開圖表源數(shù)據(jù)對話框。3、在圖表源數(shù)據(jù)對話框中“數(shù)據(jù)區(qū)域”填入數(shù)據(jù)所在區(qū)域，單擊下一步按鈕，得到如15所示“圖表選項”對話框，根據(jù)需要勾選“百分比”，點擊完成即可。圖15 “圖表選項”對話框【“復(fù)合餅圖”的繪制】：第1步操作同前，在第2步操作中，在“

13、子圖表類型”中選擇“復(fù)合餅圖”，則可以將占比較小的后4類提取組合進第二個餅圖（在所得圖形中用鼠標右鍵點擊子圖彈出快捷菜單，選擇“數(shù)據(jù)系列格式”，然后在“選項”中輸入第二個繪圖區(qū)包含的類別數(shù)目）?！静僮骶毩?xí)】使用恰當?shù)膱D形表現(xiàn)下列數(shù)據(jù)：（1） 某城市家庭對住房狀況的評價情況調(diào)查結(jié)果：回答類別戶數(shù)(戶)百分比(%)非常不滿意248不滿意10836一般9331滿意4515非常滿意3010合計300100.0（2） 顯示糧食單位面積產(chǎn)量與降雨量、溫度的關(guān)系：溫度降雨量(mm)產(chǎn)量(kg/hm2)62522508403450105046001362570014957400161105900實驗2 用Ex

14、cel計算描述統(tǒng)計量【實驗?zāi)康摹坑肊xcel計算描述統(tǒng)計量【實驗要求】1、驗證本指導(dǎo)手冊中的操作；2、在Excel中完成應(yīng)用統(tǒng)計分析技能訓(xùn)練P82練習(xí)一。2.1 用函數(shù)計算描述統(tǒng)計量Excel中用于計算描述統(tǒng)計量的方法有兩種：函數(shù)方法和描述統(tǒng)計工具方法。未分組資料可用函數(shù)計算，已分組資料可借助于Excel工作表編寫相應(yīng)公式計算。常用于描述統(tǒng)計指標計算的Excel函數(shù)如下表：指標名稱Excel函數(shù)說 明平均指標算術(shù)平均數(shù)AVERAGE返回一組數(shù)據(jù)的算術(shù)平均值調(diào)和平均數(shù)HARMEAN返回數(shù)據(jù)集合的調(diào)和平均值幾何平均數(shù)GEOMEAN返回正數(shù)數(shù)組或區(qū)域的幾何平均值切尾平均數(shù)TRIMMEAN返回數(shù)據(jù)集的

15、內(nèi)部平均值。兩頭對稱的一共去掉的數(shù)據(jù)個數(shù)，用參數(shù)以占數(shù)據(jù)總數(shù)的百分比給出眾數(shù)MODE求一組數(shù)據(jù)的眾數(shù)中位數(shù)MEDIAN返回給定數(shù)值集合的中位數(shù)四分位數(shù)QUARTILE參數(shù)取值與含義：0最小值；1下四分位數(shù)；2中位數(shù)；3上四分位數(shù)；4最大值變異指標平均差A(yù)VEDEV求一組數(shù)據(jù)與其均值的平均離差樣本標準差STDEV計算基于給定樣本的標準差總體標準差STDEVP計算總體的標準差樣本方差VAR計算基于給定樣本的方差總體方差VARP計算總體的方差形態(tài)指標峰度系數(shù)KURT返回數(shù)據(jù)集的峰度值偏態(tài)系數(shù)SKEW返回分布的偏斜度【例2.1.1】為了解某地畢業(yè)大學(xué)生的工資情況，隨機抽取30人，月工資（元）如下：15

16、6013401600141015901410161015701710155014901690138016801470153015601250156013501560151015501460155015701980161015101440以計算“切尾平均數(shù)”為例：【方法一】直接輸入函數(shù)名。假設(shè)已經(jīng)將這30個人的工資數(shù)據(jù)輸入A1:J3單元格，選中任一空白單元格，輸入“=TRIMMEAN(A1:J3,0.1)”（函數(shù)TRIMMEAN將去除的數(shù)據(jù)點數(shù)目向下舍入為最接近的2的倍數(shù)。這里參數(shù)=0.1，30個數(shù)據(jù)點的10%等于3個數(shù)據(jù)點，所以函數(shù)TRIMMEAN將對稱地在數(shù)據(jù)集的頭部和尾部各去除一個數(shù)據(jù)），

17、回車后可得去掉一個最大值和去掉一個最小值后的平均數(shù)，為1529.286.【方法二】插入函數(shù)方式。點擊“插入”菜單，選擇“函數(shù)”，從彈出的對話框中，“選擇類別”為“統(tǒng)計”，然后在“選擇函數(shù)”框中找到相應(yīng)的函數(shù)命令，確定后再在彈出的對話框中輸入函數(shù)所需參數(shù)即可。其余函數(shù)的操作與此相似，注意練習(xí)。對組距分組資料計算描述統(tǒng)計分析指標，只能借助Excel工作表自己編寫公式完成?！纠?.1.2】某班同學(xué)成績分組統(tǒng)計資料如下表。計算平均數(shù)和標準差。成績(分)組中值(x)學(xué)生數(shù)(f )60以下55460-70651270-80752480-9085690以上954合計50該例需要計算加權(quán)算術(shù)平均數(shù)和按加權(quán)形式

18、計算標準差?！静僮鞑襟E】1、加權(quán)算術(shù)平均數(shù)的計算公式為：。（假定原始數(shù)據(jù)分組表在A1:C7單元格，如圖2-1所示）在D2單元格輸入計算公式“=B2*C2”，然后向下填充至D6，在D7單元格對D2:D6求和得到Sxf，在D8計算平均數(shù)“=D7/C7”。2、這里使用的標準差計算公式為：。在E2輸入“=(B2-D$8)2*C2”（注意單元格D8對“行”的絕對引用），然后向下填充至E6，在E7單元格對E2:E6求和，在D9計算標準差“=SQRT(E7/C7)”。圖2-12.2 描述統(tǒng)計工具的使用仍使用【例2.1.1】的數(shù)據(jù)，我們需要把數(shù)據(jù)調(diào)整到A1:A30單元格（調(diào)整到一列的原因是Excel會把每一列

19、（或行）作為一個樣本進行計算），然后按以下步驟操作：1、在“工具”菜單中選擇“數(shù)據(jù)分析”選項，從彈出的對話框中選擇“描述統(tǒng)計”，點擊確定后打開描述統(tǒng)計對話框，如圖22所示：圖2-2 描述統(tǒng)計對話框2、在“輸入?yún)^(qū)域”中輸入數(shù)據(jù)所在的區(qū)域“A1:A30”，在“輸出區(qū)域”中輸入“C1”（指的是從這個位置開始放置輸出結(jié)果），其他復(fù)選框可根據(jù)需要選定：勾選“匯總統(tǒng)計”，可給出一系列描述統(tǒng)計量；勾選“平均數(shù)置信度”，會給出用樣本平均數(shù)估計總體平均數(shù)的置信區(qū)間；“第K大值”和“第K小值”會給出樣本中第k個大值和第k個小值。單擊確定，可得如圖23所示輸出結(jié)果。圖2-3 描述統(tǒng)計輸出結(jié)果上面的結(jié)果中，平均是指樣

20、本均值；標準誤差是指樣本平均數(shù)的標準差；中位數(shù)、眾數(shù)、標準差分別指樣本中位數(shù)、眾數(shù)、標準差（自由度為n-1）；峰度即峰度系數(shù)；偏度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差（全距）。實驗3 用Excel進行參數(shù)估計和假設(shè)檢驗【實驗?zāi)康摹坑肊xcel進行參數(shù)估計和假設(shè)檢驗.【實驗要求】1、驗證本指導(dǎo)手冊中的操作；2、驗證應(yīng)用統(tǒng)計分析技能訓(xùn)練中相關(guān)操作；3、在Excel中完成應(yīng)用統(tǒng)計分析技能訓(xùn)練P96練習(xí)四。在Excel中，進行參數(shù)估計只能使用公式和函數(shù)的方法，而假設(shè)檢驗除以上兩種方法外，還可以使用假設(shè)檢驗工具。3.1 用Excel進行區(qū)間估計3.1.1 使用函數(shù)CONFIDENCE總體標準差已知時，對總體均值

21、的區(qū)間估計為，可使用函數(shù)CONFIDENCE計算抽樣的極限誤差，調(diào)用格式為：CONFIDENCE(alpha,standard_dev,size)。其中，Alpha 對應(yīng)于置信度等于 100*(1 - alpha)%，Standard_dev為已知的總體標準差，Size 為樣本容量?！纠拷滩腜78例5.1（應(yīng)用統(tǒng)計分析技能訓(xùn)練P92例4.1）。如下圖，在B2輸入的值0.05，B3中輸入標準差200，B4中輸入樣本容量16后，在B5編寫公式 =CONFIDENCE(B2,B3,B4)，在B6輸入均值6200，然后在B7計算下限 =B6-B5，在B8計算上限 =B6+B5。3.1.2 編寫公式當

22、總體標準差未知時，對總體均值的區(qū)間估計為，可以在Excel中調(diào)用函數(shù)并編寫公式計算該置信區(qū)間。函數(shù)STDEV計算樣本標準差，TINV(alpha,df) 返回雙尾面積為alpha、自由度為df的t分布的臨界值。Excel中的統(tǒng)計函數(shù)：NORMSDIST, TDIST, FDIST, CHIDIST分別計算標準正態(tài)分布、t分布、F分布、分布的概率P()；NORMSINV, TINV, FINV, CHIINV分別計算標準正態(tài)分布、t分布、F分布、分布的臨界值（分布函數(shù)的逆函數(shù)）?！纠恳槐ｋU公司收集到由36位投保人組成的隨機樣本，他們的年齡如下：2335392736443642464331334

23、25345544724342839364440394938344850343945484532求投保人年齡90%的置信區(qū)間。【用函數(shù)計算】1、把數(shù)據(jù)輸入（復(fù)制）到A1:F6單元格；2、在H1計算樣本平均值，輸入“=AVERAGE(A1:F6)”；3、在H2計算樣本標準差s，輸入“=STDEV(A1:F6)”；4、在H3計算估計精度，輸入“=TINV(0.1,36-1)*H2/SQRT(36)”。其中，0.1是對應(yīng)于90%置信度的，36是樣本容量。5、最后，在H4計算置信下限：=H1-H3在H5計算置信上限：=H1+H3關(guān)于總體方差的估計、總體比例的估計等可按類似方法進行?！窘柚枋鼋y(tǒng)計工具】（

24、總體標準差未知情形）將數(shù)據(jù)調(diào)整為一列，比如放置于A1:A36，然后點擊“工具”“數(shù)據(jù)分析”“描述統(tǒng)計”，確定后可得下列結(jié)果：由上表輸出結(jié)果可得投保人年齡90%的置信區(qū)間為：(39.5-2.19, 39.5+2.19)=(37.31,41.69).3.2 用Excel進行假設(shè)檢驗3.2.1 直接計算檢驗統(tǒng)計量的值用Excel函數(shù)和公式計算假設(shè)檢驗統(tǒng)計量的值，然后與檢驗的臨界值比較作出結(jié)論。檢驗統(tǒng)計量值的計算與參數(shù)估計類似。如，下面的命令是針對3.2.2節(jié)例題數(shù)據(jù)計算檢驗統(tǒng)計量的值的：=(AVERAGE(A1:E10)-1.35)/STDEV(A1:E10)*SQRT(50)3.2.2 使用檢驗函

25、數(shù)通過檢驗函數(shù)得到檢驗的P值，與顯著性水平a比較，從而得出結(jié)論（若P值a，則否定原假設(shè)；否則不能否定原假設(shè)）。ZTEST, TTEST, FTEST, CHITEST計算Z檢驗、t檢驗、F檢驗、檢驗的P值.對ZTEST，下面的Excel公式可直接返回單尾P值：=MIN(ZTEST(array,0,sigma),1-ZTEST(array,0,sigma)計算雙尾P值的公式：=2*MIN(ZTEST(array,0,sigma), 1-ZTEST(array,0,sigma)在“array”位置輸入原始數(shù)據(jù)所在區(qū)域；在“0”位置輸入?yún)?shù)的某一假定值；在“Sigma”后輸入已知的總體標準差（若總體

26、標準差未知則可忽略不填，系統(tǒng)將自動使用樣本標準差代替）?！纠恳环N機床加工的零件尺寸絕對平均誤差為1.35mm。生產(chǎn)廠家現(xiàn)采用一種新的機床進行加工以期進一步降低誤差。為檢驗新機床加工的零件平均誤差與舊機床相比是否有顯著降低，從某天生產(chǎn)的零件中隨機抽取50個進行檢驗。利用這些樣本數(shù)據(jù)，檢驗新機床加工的零件尺寸的平均誤差與舊機床相比是否有顯著降低？ (a=0.01) 1.261.191.310.971.811.130.961.061.000.940.981.101.121.031.161.121.120.951.021.131.230.741.500.500.590.991.451.241.012

27、.031.981.970.911.221.061.111.541.081.101.641.702.371.381.601.261.171.121.230.820.86提出原假設(shè)H0：m 1.35，H1：m 1.35【操作步驟】本例為單邊Z檢驗，故只需在某單元格輸入=MIN(ZTEST(A1:E10,1.35),1-ZTEST(A1:E10,1.35)即可得出 P值=0.004579。由于P值a=0.01，故否定H0。3.2.3 假設(shè)檢驗工具的使用在Excel中，假設(shè)檢驗工具主要有四個，主要是針對雙總體的，如圖31所示：圖31 數(shù)據(jù)分析對話框“平均值的成對二樣本分析”實際上指的是在總體方差已知的

28、條件下，兩個樣本均值之差的檢驗，準確的說應(yīng)該是Z檢驗；“雙樣本等方差檢驗”是總體方差未知，但假定其相等的條件下進行的t檢驗；“雙樣本異方差檢驗”指的是總體方差未知，但假定其不等的條件下進行的t檢驗；“雙樣本平均差檢驗”指的是配對樣本的t檢驗。【例】某飲料公司開發(fā)研制出一新產(chǎn)品，為比較消費者對新老產(chǎn)品口感的滿意程度，該公司隨機抽選一組消費者(8人)，每個消費者先品嘗一種飲料，然后再品嘗另一種飲料，兩種飲料的品嘗順序是隨機的，然后每個消費者對兩種飲料分別進行評分(0分10分)，評分結(jié)果如下表。取顯著性水平a =0.05，該公司是否有證據(jù)認為消費者對兩種飲料的評分存在顯著差異？舊飲料54735856

29、新飲料66743976【操作步驟】1、輸入數(shù)據(jù)到工作表。2、選擇“工具”下拉菜單，并選擇“數(shù)據(jù)分析”選項；在分析工具中選擇“t檢驗：平均值的成對二樣本分析”；3、當出現(xiàn)對話框后，在“變量1的區(qū)域”方框內(nèi)輸入變量1的數(shù)據(jù)區(qū)域；在“變量2的區(qū)域”方框內(nèi)輸入變量2的數(shù)據(jù)區(qū)域；在“假設(shè)平均差”方框內(nèi)輸入假設(shè)的差值(這里為0)；在“”框內(nèi)輸入給定的顯著性水平，然后確定?？傻萌缦陆Y(jié)果：圖32 平均值的成對二樣本分析結(jié)果在上面的結(jié)果中，我們可以根據(jù)P值進行判斷，也可以根據(jù)統(tǒng)計量和臨界值比較進行判斷。本例采用的是雙側(cè)檢驗，P值為0.217，大于給定的顯著性水平0.05，所以不能拒絕原假設(shè)，即不能認為消費者對兩

30、種飲料的評分存在顯著差異；若用臨界值判斷，得出的結(jié)論是一樣的，如本例t值為-1.357，|t| F=3.1274（也可以直接看檢驗的P值，若小于顯著性水平，則拒絕原假設(shè)），故拒絕H0，即認為行業(yè)之間的服務(wù)質(zhì)量存在顯著差異。4.2 無交互作用的雙因素方差分析【例】為了研究不同地點、不同季節(jié)大氣飄塵含量的差異性，對地點（A）取三個不同水平，對季節(jié)（B）取四個不同水平，在不同組合（Ai,Bj）下各測得一次大氣飄塵含量（mg/m2），結(jié)果見下表，試研究地點間的差異及季節(jié)間的差異對大氣飄塵含量有無影響？(0.01)因素B因素A冬季春季夏季秋季11.1500.6140.4750.66721.2000.62

31、00.4200.88030.9400.3790.2000.540解 建立假設(shè)H0A: 不同地點對大氣飄塵含量無影響，H0B: 不同季節(jié)對大氣飄塵含量無影響。【Excel操作步驟】1、將觀測數(shù)據(jù)復(fù)制到Excel工作表；2、點擊“工具”菜單，選擇“數(shù)據(jù)分析”，在分析工具中選擇“方差分析：無重復(fù)雙因素分析”，然后點擊確定。3、當對話框出現(xiàn)時，在“輸入?yún)^(qū)域”方框內(nèi)鍵入數(shù)據(jù)單元格區(qū)域B2:E4；將“”方框內(nèi)默認的0.05改為0.01；在“輸出選項”中選擇輸出區(qū)域A6。結(jié)果如下：由于FA=23.83 F=10.92, FB=88.76 F=9.78，故分別拒絕原假設(shè)H0A、H0B，即認為地點的不同、季節(jié)的

32、不同分別都對大氣飄塵含量有影響。4.3 有交互作用的雙因素方差分析【例】 為分析4種化肥和3種小麥品種對小麥產(chǎn)量的影響，把一塊試驗田等分成24小塊，對種子和化肥的每一組合種植2小塊田，產(chǎn)量如下表，問品種、化肥及兩者的交互作用對小麥產(chǎn)量有無顯著影響？表 小麥產(chǎn)量試驗數(shù)據(jù)（單位：kg）化肥1化肥2化肥3化肥4品種1173174177172172176179173品種2175178174170173177175171品種3177174174169175174173169（考慮交互作用，每一組合至少要作2次試驗）【Excel實現(xiàn)操作】1、將觀測數(shù)據(jù)復(fù)制到Excel工作表A1:E7（包括行標題和列標題）

33、；2、點擊“工具”菜單，選擇“數(shù)據(jù)分析”，在分析工具中選擇“方差分析：可重復(fù)雙因素分析”，然后點擊確定。3、當對話框出現(xiàn)時，在“輸入?yún)^(qū)域”方框內(nèi)鍵入數(shù)據(jù)單元格區(qū)域A1:E7；在“每一樣本的行數(shù)”方框內(nèi)鍵入重復(fù)試驗次數(shù)(2)；在“輸出選項”中選擇輸出區(qū)域A9。結(jié)果如下（最后部分）：方差分析差異源SSdfMSFP-valueF crit樣本(行因素_品種)8.083324.04174.40910.03673.8853列(列因素_化肥)90.8333330.277833.03030.00003.4903交互(交互作用)51.916768.65289.43940.00062.9961內(nèi)部(誤差)111

34、20.9167總計161.833323可見，化肥因素和交互因素對產(chǎn)量的影響非常顯著，品種因素在顯著性水平0.05下也是顯著的。因而，應(yīng)注意化肥與品種的交互作用，從試驗數(shù)據(jù)看，應(yīng)把化肥3與品種1搭配起來安排生產(chǎn)。實驗5 用Excel進行相關(guān)與回歸分析【實驗?zāi)康摹坑肊xcel進行相關(guān)與回歸分析【實驗要求】1、驗證本指導(dǎo)手冊中的操作；2、驗證教材中相關(guān)例題的數(shù)據(jù)；3、在Excel中完成應(yīng)用統(tǒng)計分析技能訓(xùn)練P106練習(xí)六。【例5.1】某商業(yè)銀行某年的主要業(yè)務(wù)數(shù)據(jù)如下：表111 某商業(yè)銀行某年的主要業(yè)務(wù)數(shù)據(jù)分行編號不良貸款(億元）各項貸款余額（億元）本年累計應(yīng)收貸款（億元）貸款項目個數(shù)（個）本年固定資產(chǎn)

35、投資額（億元）10.967.36.8551.921.1111.319.81690.934.8173.07.71773.743.280.87.21014.557.8199.716.51963.262.716.22.212.271.6107.410.71720.2812.5185.427.11843.891.096.11.71055.9102.672.89.11464.3110.364.22.11142.7124.0132.211.22376.7130.858.66.01422.8143.5174.612.726117.11510.2263.515.634146.7163.079.38.91529

36、.9170.214.80.6242.1180.473.55.91125.3191.024.75.0413.4206.8139.47.22864.32111.6368.216.832163.9221.695.73.81044.5231.2109.610.31467.9247.2196.215.81639.7253.2102.212.01097.15.1 用Excel進行相關(guān)分析用Excel進行相關(guān)分析有兩種方法，一是利用相關(guān)系數(shù)函數(shù)，另一種是利用相關(guān)分析工具。5.1.1 利用函數(shù)計算相關(guān)系數(shù)在Excel中，提供了兩個計算兩個變量之間相關(guān)系數(shù)的函數(shù)：CORREL和PEARSON，這兩個函數(shù)是等價的

37、。如計算“不良貸款”與“貸款項目個數(shù)”的相關(guān)系數(shù)，下述兩種方式均可（此處假設(shè)例題5.1的數(shù)據(jù)位于Excel工作表的B2:F26）：=CORREL(B2:B26,E2:E26)或=PEARSON(B2:B26,E2:E26)5.1.2 用相關(guān)系數(shù)工具計算【操作步驟】1、單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項，在數(shù)據(jù)分析選項中選擇“相關(guān)系數(shù)”；2、在彈出的相關(guān)系數(shù)對話框中（如圖51）：在“輸入?yún)^(qū)域”輸入數(shù)據(jù)所在區(qū)域B2:F26，注意必須為相鄰的單元格引用；再選擇“分組方式”為“逐列”；在“輸出區(qū)域”中輸入H2，單擊確定，即輸出5個變量的相關(guān)系數(shù)矩陣結(jié)果。圖51 相關(guān)系數(shù)對話框5.2 用Excel進

38、行回歸分析Excel進行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個函數(shù)用于建立回歸模型和預(yù)測。這9個函數(shù)分別是：INTERCEPT 返回線性回歸模型的截距SLOPE 返回線性回歸模型的斜率RSQ 返回線性回歸模型的判定系數(shù)FORECAST 返回一元線性回歸模型的預(yù)測值STEYX 計算估計的標準誤TREND 計算線性回歸線的趨勢值GROWTH 返回指數(shù)曲線的趨勢值LINEST 返回線性回歸模型的參數(shù)LOGEST 返回指數(shù)曲線模型的參數(shù)但是，用函數(shù)進行回歸分析比較麻煩，故一般使用回歸分析工具。5.2.1 一元線性回歸仍以前例數(shù)據(jù)為例。對“不良貸款（Y）”與“各項貸款余額（X）”進行回歸分

39、析：【操作步驟】1、單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項，在數(shù)據(jù)分析選項中選擇“回歸”；2、在彈出的回歸對話框中：在“Y值輸入?yún)^(qū)域”輸入不良貸款數(shù)據(jù)所在區(qū)域“B2:B26”，在“X值輸入?yún)^(qū)域”輸入各項貸款余額數(shù)據(jù)所在區(qū)域“C2:C26”；3、若需殘差圖等，可勾選相應(yīng)復(fù)選框；單擊確定，即可輸出如下分析結(jié)果。在上面的輸出結(jié)果中，第一部分為匯總統(tǒng)計，Multiple R指復(fù)相關(guān)系數(shù)，R Square指判定系數(shù)（可決系數(shù)），Adjusted R Square指調(diào)整的判定系數(shù)，標準誤差指估計的標準誤，觀測值指樣本容量；第二部分為方差分析，df指自由度，SS指平方和，MS指均方，F(xiàn)指用于回歸方程顯著性檢

40、驗的F統(tǒng)計量，Significance F是F統(tǒng)計量對應(yīng)的P值；第三部分包括：Intercept指截距，X Variable為自變量，Coefficient指系數(shù)，t Stat指用于系數(shù)顯著性檢驗的t統(tǒng)計量，P-value指t統(tǒng)計量對應(yīng)的P值。由上述Excel輸出結(jié)果得到本例的回歸分析結(jié)果：（1）回歸方程為：=-0.83+0.0379x（2）判定系數(shù)R2=0.712，說明各項貸款余額解釋了不良貸款取值變動的71.2%。（3）不良貸款與貸款余額之間的線性關(guān)系顯著性的F檢驗：對應(yīng)的P值Significance F= 1.18E-07 F=4.28判斷），說明不良貸款與貸款余額之間存在顯著的線性關(guān)系

41、。（4）自變量x系數(shù)顯著性的t檢驗：對應(yīng)的P值為1.18E-07，說明x對y的影響是顯著的。另外，利用回歸方程進行預(yù)測：可在Excel中編寫公式直接計算。如當“各項貸款余額”為400億元時，“不良貸款”的預(yù)測值為“=-0.83+0.0379*400”。或參見6.3節(jié)，使用函數(shù)進行預(yù)測。5.2.2 多元線性回歸Excel操作基本同于一元線性回歸。只是注意要將所有的自變量數(shù)據(jù)放置在Excel的相鄰區(qū)域。實驗6 時間序列分析和預(yù)測【實驗?zāi)康摹坑肊xcel進行時間序列分析和預(yù)測【實驗要求】1、驗證本指導(dǎo)手冊中的操作；2、驗證教材中相關(guān)例題的數(shù)據(jù)；3、在Excel中完成應(yīng)用統(tǒng)計分析技能訓(xùn)練P89練習(xí)二。

42、6.1 增減速度分析【例】下表是2000-2009年的四川省第三產(chǎn)業(yè)的地區(qū)生產(chǎn)總值（單位：億元），計算增減指標和速度指標。時間2000200120022003200420052006200720082009第三產(chǎn)業(yè)的GDP1549.511739.811943.682189.682510.32836.733319.623881.64561.695198.8逐期增減量-累計增減量-環(huán)比發(fā)展速度%-定基發(fā)展速度%-環(huán)比增減速度%-定基增減速度%-增長1%的絕對額-【Excel操作】（假設(shè)本表處于Excel工作表的A1:K9區(qū)域。）1、計算逐期增減量：在C3中輸入公式 =C2-B2，并向右填充；2、計

43、算累計增減量：在C4中輸入公式 =C2-$B2，并向右填充；3、計算環(huán)比發(fā)展速度：在C5中輸入公式 =C2/B2*100，并向右填充；4、計算定基發(fā)展速度：在C6中輸入公式 =C2/$B2*100，并向右填充；5、計算環(huán)比增減速度：在C7中輸入公式 =(C2-B2)/B2*100 或 =C3/B2*100 或 =C5-100，并向右填充；6、計算定基增減速度：在C8中輸入公式 =(C2-$B2)/$B2*100 或 =C4/$B2*100 或 =C6-100，并向右填充；7、計算增長1%的絕對額：在C9中輸入公式 = B2/100，并向右填充；8、計算平均發(fā)展速度：（1）通過計算環(huán)比發(fā)展速度的

44、幾何平均數(shù)獲得：=GEOMEAN(C5:K5)（2）通過初期水平與末期水平之比開方獲得：=(K2/B2)(1/9)*100%計算平均增減速度：平均增減速度=平均發(fā)展速度-100%*6.2 平穩(wěn)序列預(yù)測6.2.1 移動平均法【例】對下表居民消費價格指數(shù)進行分析。年份CPI3期移動平均預(yù)測預(yù)測誤差誤差平方5期移動平均預(yù)測預(yù)測誤差誤差平方1990103.1 1991103.4 1992106.4 1993114.7 104.3 10.4 107.9 1994124.1 1995117.1 110.3 6.8 45.8 1996108.3 1997102.8 199899.2 199998.6 2000100.4 2001100.7 200299.2 2003101.2 2004103.9 2005合計【Excel操作】1、在A2:A17輸入年份，在B2: