Excel與數(shù)據(jù)統(tǒng)計分析

1、excel與數(shù)據(jù)統(tǒng)計分析 統(tǒng)計計算與統(tǒng)計分析強調與計算機密切結合，excel與數(shù)據(jù)統(tǒng)計分析旨在提高學生計算機的綜合運用能力，用統(tǒng)計方法分析問題、解決問題而編寫的。根據(jù)教材內容，也可以選擇使用spss、qstat、evievs、sas、minitab等統(tǒng)計軟件。第三章 統(tǒng)計整理3.1 計量數(shù)據(jù)的頻數(shù)表與直方圖例3.1 (3-1) 一、指定接受區(qū)域直方圖 在應用此工具前，用戶應先決定分布區(qū)間。否則，excel將用一個大約等于數(shù)據(jù)集中某數(shù)值的平方根作區(qū)間，在數(shù)據(jù)集的最大值與最小值之間用等寬間隔。如果用戶自己定義區(qū)間，可用2、5或10的倍數(shù)，這樣易于分析。 對于工資數(shù)據(jù)，最小值是100，最大值是298

2、。一個緊湊的直方圖可從區(qū)間100開始，區(qū)間寬度用10，最后一區(qū)間為300結束，需要21個區(qū)間。這里所用的方法在兩端加了一個空區(qū)間，在低端是區(qū)間“100或小于100”，高端是區(qū)間“大于300”。 參考圖3.3，利用下面這些步驟可得到頻率分布和直方圖： 1為了方便，將原始數(shù)據(jù)拷貝到新工作表“指定頻數(shù)直方圖”中。2在b1單元中輸入“組距”作為一標記，在b2單元中輸入100，b3單元中輸入110，選取b2:b3，向下拖動所選區(qū)域右下角的+到b22單元。3按下列步驟使用“直方圖”分析工具：（1）選擇工具菜單之數(shù)據(jù)分析選項, 在分析工具框中“直方圖”。如圖4所示。圖3.1 數(shù)據(jù)分析工具之直方圖對話框1)

3、輸入輸入?yún)^(qū)域：a1:a51 接受區(qū)域：b1:b22 (這些區(qū)間斷點或界限必須按升序排列)選擇標志 2) 輸出選項 輸出區(qū)域: c1 選定圖表輸出(2)單擊確定，excel將計算出結果顯示在輸出區(qū)域中。圖3.2 數(shù)據(jù)分析工具之直方圖對話框excel將把頻率分布和直方圖放在工作表中，如圖3.3所示，輸出表的c和d列中包括開始指定的界限。這些界限實際上是每一區(qū)間的上限，也就是說，界限實際上是邊界。圖3.3 頻數(shù)分布與直方圖為了使圖表更像傳統(tǒng)的直方圖和更易于理解，可雙擊圖表并對它做如下修改：1圖例：因為只有一個系列的數(shù)據(jù)顯示在圖表中，所以不需要圖例。單擊圖例(位于圖表右側的“頻率”)并按delete鍵

4、。2圖表區(qū)：繪圖區(qū)是以x和y軸為邊界的矩形區(qū)域。通過在柱形上面單擊可選取繪圖區(qū)，單擊鼠標右鍵并選擇繪圖區(qū)格式，將邊框改為無并將區(qū)域改為無，單擊確定。3條寬：在傳統(tǒng)的直方圖中，柱形是彼此相連接而不是分開的。選擇某個柱形，單擊鼠標右鍵，選擇數(shù)據(jù)系列格式，并單擊選擇標簽，將間距寬度從150改為0，單擊確定。4x軸標志：選取x軸，單擊鼠標右鍵，選擇坐標軸格式，單擊對齊標簽，將方向從自動改為水平文本，在這種設置下，即使圖表已重置尺寸，x軸標記也會變?yōu)樗降模瑔螕舸_定。最后的直方圖4.6圖3.4 修改后的直方圖二、不指定接受區(qū)域直方圖 在進行探索性分析時，為了方便，通常不指定接受區(qū)域作直方圖，步驟如下：（

5、1）選擇工具菜單之數(shù)據(jù)分析選項, 在分析工具框中“直方圖”。如圖4所示。1) 輸入輸入?yún)^(qū)域：a1:a51 接受區(qū)域：(該處為空)選擇標志 2) 輸出選項 輸出區(qū)域: b1 選定圖表輸出(2)單擊確定，得結果。(3)按前面方法對直方圖進行進一步修飾即得圖3.5圖3.5 修改后的直方圖3.2 計數(shù)數(shù)據(jù)的透視表與條圖例3.2（3-3）數(shù)據(jù)見圖步驟如下：（1）選擇數(shù)據(jù)菜單之數(shù)據(jù)透視表和圖表報告選項, 如圖4所示。(2)選擇數(shù)據(jù)源區(qū)域（3）選定數(shù)據(jù)透視表位置，完成（4）將“性別”作為行字段拖至g列，并將“性別”作為數(shù)據(jù)拖至數(shù)據(jù)項處，得下表結果同理可得“文化程度”的透視表此時如點擊圖形按鈕，立即得到如下的

6、透視圖（5）將“性別”作為行字段拖至行字段處，并將“文化程度”作為列字段拖至列字段處，將“性別”或“文化程度”作為列字段拖至數(shù)據(jù)字段處得下表結果第四章 總量指標和相對指標例4.1 (4-13) 計算步驟：（1）計算各廠計劃完成% e3=d3/c3*100, （2）2000年實際產量為1999年的% f3=d3/b3*100, 第五章 平均指標 5.1 簡單平均數(shù)例5.1某組有學生10人統(tǒng)計課考試成績?yōu)?5，82，76，80，82，86，84，88，95，98分，試求其平均指標。平均數(shù)的計算步驟如下：（1）將數(shù)據(jù)輸入到a列，根據(jù)excel提供的公式計算各種平均數(shù)（2）用ctrl+ 可切換到下面的

7、結果：5.2 加權平均數(shù)例5.2（5-1）原始數(shù)據(jù)見下圖a-d列，其中a、b列放日產量的下限和上限平均數(shù)的計算步驟如下：（1）計算日產量的組中值 e3=(a3+b3)/2, （2）計算每個組段的總產量 f3=c3*e3, g3=d3*e3, （3）計算每月的總產量 f8=sum(f3:f7), g8=sum(g3:g7), （4）計算平均數(shù)公式如下：均數(shù)f9=f8/c8g9=g8/d8眾數(shù)f10=a4+(c4-c3)/(c4-c3+c4-c5)*10g10=a6+(d6-d5)/(d6-d5+d6-d7)*10中位數(shù)f11=a4+(c8/2-a4)/c4*10g11=a5+(d8/2-c5)/

8、d5*10第六章 變異度指標 6.1 簡單變異度指標例6.1（6-1）變異度指標的計算步驟如下：（1）將甲乙兩組數(shù)據(jù)輸入到a, b列，根據(jù)excel提供的公式計算各種變異度指標（2）用ctrl+ 可切換到下面的公式：6.2 加權變異度指標例6.2（6-2）甲品種的原始數(shù)據(jù)見下圖b-c列，乙品種的原始數(shù)據(jù)見下圖g-h列下面以甲品種的數(shù)據(jù)計算為例：（1）計算單產值 d4=c4/b4, （2）計算單產均值 d9=c9/b9（3）計算次數(shù)x離差平方 e4=b4*(d4-$d$9)2 , 并求和 e9=sum(e4:e8)（4）計算標準差：d11=sqrt(e9/b9)=68.91（5）計算變異系數(shù)：d

9、12=d11/d9*100=6.9%同理可得乙品種的標準差為162.71, 變異系數(shù)為16.30%第七章 抽樣調查例7.1 (7-5) 期望求 e(x)的公式 b4=sum(b1:f1)*(b2:f2), 由于此處用到數(shù)組乘積求和，所以要得到結果，需用ctrl+shift+enter組合鍵。例7.2 (7-6) 二項分布 計算公式：p(5=x=10)=p(x=10)-p(x5)=p(x=10)-p(x=9)=1-p(x9)=1-p(x=5)=1-p(x=4)excel 計算結果：excel 計算公式： （ctrl+ 互換）例7.4 (7-9) 超幾何分布例7.5 (7-10) 正態(tài)分布 其中

10、f2=1-d2, d4=d3-d2 第八章 假設檢驗8.1 大樣本使用正態(tài)分布的假設檢驗例8.1 商店經理想為商店的持信用卡的顧客建一新的付款系統(tǒng)，經過詳細的經濟分析，她判定如果新系統(tǒng)每月平均利潤低于70元的話就不能有效地使用資金。于是隨機抽取了200個月的利潤，其平均月利潤為66元。如果a0.05，有無充分的證據(jù)說明新系統(tǒng)不是一項節(jié)省資金的系統(tǒng)?假設總體的標準偏差為30元。圖8.1 正態(tài)假設檢驗的標記和公式-已知均值標準差計算公式 上圖所示的工作表可用于正態(tài)分布平均值的左尾、右尾和雙尾假設檢驗。檢驗結果包括基于a判決法和p值報告法。輸入樣本大小、樣本平均值和標準偏差作為值、公式或引用，指定假

11、設的平均值(mean)和顯著水平a作為值。下面各步驟描述了如何建立該工作表： （1）打開一新工作表并輸入b列所示標記。 （2）要在c列的公式使用b列中的名稱，選取單元b4:c12，從插入菜單中選擇名稱4指定，在指定名稱對話框中復選名稱創(chuàng)建于最左列，單擊確定。 （3）輸入c列所示的公式(按圖所示鍵入公式或通過單擊適當?shù)囊衙膯卧迦牒瘮?shù)來建立公式)。（4）要得圖8.1的a列所示的外觀，按ctrl+。因為經理想知道平均月利潤是否小于70元，所以備擇假設為hd：mean70，零假設為ho：mean70或簡單地為ho：mean70。由于數(shù)據(jù)已經總結過了，可直接在工作表單元中輸入樣本大小n、樣本平均值

12、、總體的標準偏差、假設總體平均值和顯著水平。圖8.2 正態(tài)假設檢驗結論：得到z小于-1.886的概率是0.0297。如果零假設為真(每月平均利潤為70元)，得到樣本平均值為66元或小于它的概率約為3，即有充分的證據(jù)說明新系統(tǒng)是一項節(jié)省資金的系統(tǒng)。8.2 小樣本使t分布的假設檢驗家保險公司用代理的方式支付其客戶，賠償假定每年的平均代理賠償費用為32000元，如果平均支付費用與計劃不同，就需要對計劃進行修改。對一個有36個代理的樣本，上一年的平均支付費用為27500元，標準偏差為8400元，如果整個公司的平均支付變化與該樣本的情況不同，那么可用管理計劃來修改賠償計劃。根據(jù)這一結果的p值，這一樣本能

13、充分說明平均值變化了嗎？本例已知均值標準差，下面各步驟描述了如何建立計算工作表：1打開一新工作表，輸入a列所示標記。2要在c列的公式使用b列中的名稱，選取單元b4:c13，從插入菜單中選擇名稱4指定，在指定名稱對話框中復選名稱創(chuàng)建于最左列，單擊確定。圖8.3 已知均值標準差計算公式-t假設檢驗的標記和公式因為經理想知道平均支付是否發(fā)生了變化(不用指出變化的方向)，所以備擇假設為h1：mean32000，零假設為ho：mean32000。由于數(shù)據(jù)已經總結過了，可以直接向工作表的單元中輸入樣本大小n、樣本平均值、總體的標準偏差和假設總體的平均值。盡管例中未指明顯著水平可輸入為0.05。 結論：由于

14、p=0.00280.05，所以，有足夠的理由拒絕在顯著水平為5(雙尾檢驗)時的零假設，可得出如下結論：平均支付值不等于32000，明確說明平均值改變了。圖8.4 t假設檢驗例8.3（已知原始數(shù)據(jù)）一家制造商生產鋼棒，為了提高質量，如果某新的生產工藝生產出的鋼棒的斷裂強度大于現(xiàn)有平均斷裂強度標準的話，公司將采用該工藝。當肪鋼棒的平均斷裂強度標準是500公斤。對新工藝生產的鋼捧進行抽樣，12件棒材的斷裂強度如下：502，496，510，508，506，498，512，497，515，503，510和506，假設斷裂強度的分布比較近似于正態(tài)分布，將樣本數(shù)據(jù)畫圖，所畫圖形能表明平均斷裂強度有所提高嗎?

15、圖8.5顯示了假設檢驗所需的數(shù)據(jù)。 因為經理想檢查是否有提高，備擇假設為h1：mean500，所以用右尾檢驗比較合適。零假設為ho：mean500，或簡單地ho：mean=500。如例8.2所述，d2：d13單元已命名為data，單元b5:b7包含了公式count(data), average(data)和stdev(data)。盡管例5.5未指定一顯著水平, 在b10單元中入了顯著水平為0.05。包含了左尾檢驗結果的15到18行被隱藏。圖8.5 小樣本t假設檢驗公式 結論：p=0.01310.05，說明有充分證據(jù)來拒絕零假設?？傻贸鋈缦陆Y論：新工藝在統(tǒng)計上可帶來平均斷裂強度的顯著提高。圖8.

16、6 小樣本t假設檢驗結果第九章 相關與回歸簡單線性相關分析例9.1 （9-1）1excel進行相關分析： （1）輸數(shù)據(jù): 將數(shù)據(jù)輸入a1:c9單元格。（2）繪制散點圖: 圖9.1 簡單相關系數(shù)及散點圖3. 計算相關系數(shù)(1) 選擇工具菜單之數(shù)據(jù)分析選項, 在分析工具框中“相關系數(shù)”。相關系數(shù)對話框將顯示為圖9.2所示，它帶輸入輸出的提示。圖9.2 相關系數(shù)對話框1) 輸入輸入?yún)^(qū)域：b1:c9 分組方式：逐列選擇標志位于第一行 2) 輸出選項 輸出區(qū)域: a13(2)單擊確定，excel將計算出結果顯示在輸出區(qū)域中。4. 相關系數(shù)假設檢驗(1) 在單元格f14中輸入公式 =b15/sqrt(1-

17、b152)/(8-2) 計算得相關系數(shù)的t值為49.46(2) 在單元格f15中輸入公式 =tdist(abs(f14),b-2,2) 計算得 p=0.0001(3) 結論: 由于r=-0.9689, 且p0.05, 所以, 在0.05水平上拒絕原假設, 認為產品產量與單位成本間有負的線性相關關系9.2 簡單回歸分析上面的簡單相關分析只是說明兩變量之間的線性關系密切的程度，如果要建立它們之間線性依存的關系式，就需用回歸分析?？砂聪铝胁襟E使用“回歸”分析工具：1. 輸數(shù)據(jù): 將數(shù)據(jù)輸入a1:c9單元格。2. 回歸分析: (1) 選擇工具菜單之數(shù)據(jù)分析選項, 在分析工具框中“回歸”?；貧w對話框將顯

18、示為圖9.3所示， 圖9.3 回歸分析對話框1) 輸入y值輸入?yún)^(qū)域：c1:c9 x值輸入?yún)^(qū)域：b1:b9 標志: 選擇常數(shù)為零: 只有當用戶想強制使回歸線通過原點(0,0)時才選此框置信度: excel自動包括了回歸系數(shù)的95置信區(qū)間。要使用其他置信區(qū)間, 選擇該框并在confidence levet框中輸入置信水平2) 輸出選項 輸出區(qū)域: d1 3) 殘差殘差(r)：選擇此框可得到預測值和殘差(residual)。殘差圖(d): 選擇此框可得到殘差和每一x值的圖表。標準殘差(t)：選擇此框可得到標準化的殘差,每一殘差被估計標準誤差除)。這一輸出可使曲線較容易分層。線性擬合圖(i)：選擇此框

19、可得到一含有y輸入數(shù)據(jù)和擬合的y值的散點圖。4) 正態(tài)概率圖: 繪制因變量的正態(tài)概率圖(2)單擊確定，excel將計算出結果顯示在輸出區(qū)域中。圖9.4 回歸分析結果3. 回歸解釋 擬合回歸線的截距和斜率放在圖9.4的總結輸出中標記有“coeffients的左下部。截距系數(shù)77.30769是線性回歸方程中的常數(shù)項，x系數(shù)-0.80769是斜率?；貧w方程是：y77.30769-0.80769 * x圖9.5 殘差及擬合線在圖9.5所示的殘差輸出中，預測 y，有時又稱擬合值，是用這個回歸方程計算的單位成本的估計值。殘差是實際值和擬合值之間的差值?；卮稹皵M合關系怎么樣”問題的最通用的四個方法是標準誤差

20、，r2，t統(tǒng)計值和方差分析。標準誤差0.83205顯示在圖9.4的單元e7中。作為殘數(shù)的標準偏差，它衡量單位成本在回歸線周圍的分散情況，標準誤差通常稱為估計標準誤差。 r2（r square），如圖9.4的單元e5所示，衡量用回歸線解釋的因變量變化的比例。這一比例必擊是0和1之間的一個數(shù)據(jù)，經常以百分數(shù)表示。這里，約有的94的單位成本的變化是在線性方程中用產品產量做為預測因子來解釋的。單元e6顯示的adjusted r square在用附加解釋變量把此模型和其他模型比較時很有用。第十章 時間序列分析指標例10.1 (10-2) 1995-2000的銷售額見下圖b列，則其速度分析指標計算如下：e

21、xcel計算公式如下:(ctrl+ 切換)例10.2 (10-3) 計算步驟：（1）各季平均每月總產值計算公式 說明 單元格 公式一季 b16 = average(b2:b4)二季 b17 = average(b5:b7)三季 b18 = average(b8:b10)四季 b19 = average(b11:b13)全年 b20 = average(b2:b13)（2）全年平均職工人數(shù)： c16 = (c2/2+c3+c4+c5+c6+c7+c8+c9+c10+c11+c12+c13+c14/2)/13（3）月平均勞動生產率： c17 =b20/c16*10000 年平均勞動生產率： c18

22、 =sum(b2:b13)/c16*10000（4）全年職工構成指標： c19 = (d2/2+d3+d4+d5+d6+d7+d8+d9+d10+d11+d12+d13+d14/2)/(c2/2+c3+c4+c5+c6+c7+c8+c9+c10+c11+c12+c13+c14/2)*100例10.3 (10-5) 計算步驟：（1）計算每年的增長速度 a2=1+a1/100, （2）5年平均增長速度 f3 = (product(a2:e2)(1/5)-1)*100（3）國民生產總值翻兩翻需要時間 f4 = log(4,10)/log(1+f3/100,10)第十一章 時間數(shù)列預測方法例11.1

23、(11-1)（1）計算按5日擴大時距的時間數(shù)列和計算按5日平均日產量的時間數(shù)列，結果如下表excel計算公式（3）5日移動平均數(shù)。按下列步驟使用“移動平均”分析工具：1. 選擇工具菜單之數(shù)據(jù)分析選項, 在分析工具框中“移動平均”。移動平均對話框將顯示為下圖所示，它帶輸入輸出的提示。1) 輸入輸入?yún)^(qū)域：b1:b31 a標志位于第一行間隔：5 2) 輸出選項 輸出區(qū)域: c1 2. 單擊確定，excel將計算出結果顯示在輸出區(qū)域中。 例11.2 (11-2)(2) 選擇工具菜單之數(shù)據(jù)分析選項, 在分析工具框中“指數(shù)平滑”。指數(shù)平滑對話框將顯示為圖所示，它帶輸入輸出的提示。1) 輸入輸入?yún)^(qū)域：b1:b13 阻尼系樹：0.2 a標志 2) 輸出選項 輸出區(qū)域: c2 a 圖表輸出 (2)單擊確定，excel將計算出結果顯示在輸出區(qū)域中。例11.3 (11-6)1 輸入數(shù)據(jù)，繪制散點圖2 點擊圖中數(shù)據(jù)點