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

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

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

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

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

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

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

7、結(jié)果：5.2 加權(quán)平均數(shù)例5.2（5-1）原始數(shù)據(jù)見下圖a-d列，其中a、b列放日產(chǎn)量的下限和上限平均數(shù)的計(jì)算步驟如下：（1）計(jì)算日產(chǎn)量的組中值 e3=(a3+b3)/2, （2）計(jì)算每個(gè)組段的總產(chǎn)量 f3=c3*e3, g3=d3*e3, （3）計(jì)算每月的總產(chǎn)量 f8=sum(f3:f7), g8=sum(g3:g7), （4）計(jì)算平均數(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第六章 變異度指標(biāo) 6.1 簡單變異度指標(biāo)例6.1（6-1）變異度指標(biāo)的計(jì)算步驟如下：（1）將甲乙兩組數(shù)據(jù)輸入到a, b列，根據(jù)excel提供的公式計(jì)算各種變異度指標(biāo)（2）用ctrl+ 可切換到下面的公式：6.2 加權(quán)變異度指標(biāo)例6.2（6-2）甲品種的原始數(shù)據(jù)見下圖b-c列，乙品種的原始數(shù)據(jù)見下圖g-h列下面以甲品種的數(shù)據(jù)計(jì)算為例：（1）計(jì)算單產(chǎn)值 d4=c4/b4, （2）計(jì)算單產(chǎn)均值 d9=c9/b9（3）計(jì)算次數(shù)x離差平方 e4=b4*(d4-$d$9)2 , 并求和 e9=sum(e4:e8)（4）計(jì)算標(biāo)準(zhǔn)差：d11=sqrt(e9/b9)=68.91（5）計(jì)算變異系數(shù)：d

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

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

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

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

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

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

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

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

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

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

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

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

21、xcel計(jì)算公式如下:(ctrl+ 切換)例10.2 (10-3) 計(jì)算步驟：（1）各季平均每月總產(chǎn)值計(jì)算公式 說明 單元格 公式一季 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）月平均勞動(dòng)生產(chǎn)率： c17 =b20/c16*10000 年平均勞動(dòng)生產(chǎn)率： c18

22、 =sum(b2:b13)/c16*10000（4）全年職工構(gòu)成指標(biāo)： 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) 計(jì)算步驟：（1）計(jì)算每年的增長速度 a2=1+a1/100, （2）5年平均增長速度 f3 = (product(a2:e2)(1/5)-1)*100（3）國民生產(chǎn)總值翻兩翻需要時(shí)間 f4 = log(4,10)/log(1+f3/100,10)第十一章 時(shí)間數(shù)列預(yù)測(cè)方法例11.1

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