Ecel 數(shù)據(jù)統(tǒng)計(jì)技巧

Excel與數(shù)據(jù)統(tǒng)計(jì)分析3.1計(jì)量數(shù)據(jù)的頻數(shù)表與直方圖例3.1(3-1)一、指定接受區(qū)域直方圖在應(yīng)用此工具前，用戶(hù)應(yīng)先決定分布區(qū)間。否則，Excel將用一個(gè)大約等于數(shù)據(jù)集中某數(shù)值的平方根作區(qū)間，在數(shù)據(jù)集的最大值與最小值之間用等寬間隔。如果用戶(hù)自己定義區(qū)間，可用2、5或10的倍數(shù)，這樣易于分析。對(duì)于工資數(shù)據(jù)，最小值是100，最大值是298。一個(gè)緊湊的直方圖可從區(qū)間100開(kāi)始，區(qū)間寬度用10，最后一區(qū)間為300結(jié)束，需要21個(gè)區(qū)間。這里所用的方法在兩端加了一個(gè)空區(qū)間，在低端是區(qū)間“100或小于100”，高端是區(qū)間“大于300”。參考圖3.3，利用下面這些步驟可得到頻率分布和直方圖：1．為了方便，將原始數(shù)據(jù)拷貝到新工作表“指定頻數(shù)直方圖”中。在B1單元中輸入“組距”作為一標(biāo)記，在B2單元中輸入100,B3單元中輸入110,選取B2:B3，向下拖動(dòng)所選區(qū)域右下角的+到B22單元。按下列步驟使用“直方圖”分析工具：選擇工具菜單之?dāng)?shù)據(jù)分析選項(xiàng),在分析工具框中“直方圖”。如圖4所示。圖3.1數(shù)據(jù)分析工具之直方圖對(duì)話(huà)框輸入輸入?yún)^(qū)域：A1:A51接受區(qū)域：B1:B22(這些區(qū)間斷點(diǎn)或界限必須按升序排列)選擇標(biāo)志輸出選項(xiàng)輸出區(qū)域:C1選定圖表輸出.單擊確定,Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中。圖3.2數(shù)據(jù)分析工具之直方圖對(duì)話(huà)框Excel將把頻率分布和直方圖放在工作表中，如圖3.3所示，輸出表的C和D列中包括開(kāi)始指定的界限。這些界限實(shí)際上是每一區(qū)間的上限，也就是說(shuō)，界限實(shí)際上是邊界。圖3.3頻數(shù)分布與直方圖為了使圖表更像傳統(tǒng)的直方圖和更易于理解，可雙擊圖表并對(duì)它做如下修改：1．圖例：因?yàn)橹挥幸粋€(gè)系列的數(shù)據(jù)顯示在圖表中，所以不需要圖例。單擊圖例（位于圖表右側(cè)的“頻率”）并按Delete鍵。2.圖表區(qū)：繪圖區(qū)是以X和Y軸為邊界的矩形區(qū)域。通過(guò)在柱形上面單擊可選取繪圖區(qū)，單擊鼠標(biāo)右鍵并選擇繪圖區(qū)格式，將邊框改為無(wú)并彳將區(qū)域改為無(wú),單擊確定。3．條寬：在傳統(tǒng)的直方圖中，柱形是彼此相連接而不是分開(kāi)的。選擇某個(gè)柱形,單擊鼠標(biāo)右鍵，選擇數(shù)據(jù)系列格式并單擊選擇標(biāo)簽，將間距寬度從150%改為0%,單擊確定。X軸標(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ū)域作直方圖，步驟如下：選擇工具菜單之?dāng)?shù)據(jù)分析選項(xiàng),在分析工具框中“直方圖”。如圖4所示。1)輸入輸入?yún)^(qū)域：A1:A51接受區(qū)域：(該處為空)選擇標(biāo)志2)輸出選項(xiàng)輸出區(qū)域:B1

選定圖表輸出.單擊確定,得結(jié)果。按前面方法對(duì)直方圖進(jìn)行進(jìn)一步修飾即得圖3.5

ABC1工資接收頻率ABC1工資接收頻率214610013176128.344198156.665227184.9136298213.1127137241.478173269.759194其他2102221126712140131751419615224EFGH圖3.5修改后的直方圖3.2計(jì)數(shù)數(shù)據(jù)的透視表與條圖例3.2(3-3)數(shù)據(jù)見(jiàn)圖步驟如下：(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é)果

同理可得“文化程度”的透視表■ GIH丁IJ■K計(jì)數(shù)項(xiàng):性別計(jì)數(shù)項(xiàng):文化程度性別 〒匯總文化程度 -匯總男12初中8女3高中2總計(jì)15技工1文盲1小學(xué)2中專(zhuān)1總計(jì)15此時(shí)如點(diǎn)擊圖形按鈕，立即得到如下的透視圖

5）將“性別”作為行字段拖至行字段處，并將“文化程度”作為列字段拖至列字段處，將“性別”或“文化程度”作為列字段拖至數(shù)據(jù)字段處得下表結(jié)果18計(jì)數(shù)項(xiàng):性別墳化程度-19性別 -初中高中技工文盲小學(xué)中專(zhuān)總計(jì)20男721111221女111322總計(jì)82112115第四章總量指標(biāo)和相對(duì)指標(biāo)例4.1(4-13)ABCDEF11999年2000^匚產(chǎn)量計(jì)劃完成2000年實(shí)際產(chǎn)量2工廠(chǎng)實(shí)際產(chǎn)量計(jì)劃產(chǎn)量實(shí)際產(chǎn)量%為1999年的％3195020002200110112.82420202204：199890.6590.915295030103035100.83102.88計(jì)算步驟：計(jì)算各廠(chǎng)計(jì)劃完成%E3=D3/C3*100,…2000年實(shí)際產(chǎn)量為1999年的%F3=D3/B3*100,…第五章平均指標(biāo)5.1簡(jiǎn)單平均數(shù)例5.1．某組有學(xué)生10人統(tǒng)計(jì)課考試成績(jī)?yōu)?5，82，76，80，82，86，84，8895，98分，試求其平均指標(biāo)。平均數(shù)的計(jì)算步驟如下：(1)將數(shù)據(jù)輸入到A列，根據(jù)Excel提供的公式計(jì)算各種平均數(shù)AEC165282算術(shù)均數(shù)^AVERAGE（Al:A10）376調(diào)和均數(shù)=HARMEAN（Al:A10）480幾何均數(shù)=GEOMEAN（Al:A10）582眾數(shù)=MODE（Al:A10）686中位數(shù)^MEDIAN（Al:A10）7848889951098(2)用Ctrl+'可切換到下面的結(jié)果:ABCD1652S2算術(shù)均數(shù)S4376調(diào)和均數(shù)S2.604SO幾何均數(shù)S3.11582眾數(shù)82686中位數(shù)S378488899510985.2加權(quán)平均數(shù)■例5.2(5-1)原始數(shù)據(jù)見(jiàn)下圖A-D列，其中A、B列放日產(chǎn)量的下限和上限ABCDEFG1日產(chǎn)量(公斤)工人數(shù)f組中值總產(chǎn)量xf2L-U4月份fl陰份f2X4月份趕15月份xf23102020101530015042030352025S7550053040302535105007564050103045450135075060515552750258合計(jì)100100175295037009均數(shù)29.53710眾數(shù)27.542.511中位數(shù)28.5730.00平均數(shù)的計(jì)算步驟如下：計(jì)算日產(chǎn)量的組中值E3=(A3+B3)/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/C8 G9=G8/D8眾數(shù) F10=A4+(C4-C3)/(C4-C3+C4-C5)*10 G10=A6+(D6-D5)/(D6-D5+D6-D7)*10中位數(shù) F11=A4+(C8/2-A4)/C4*10 G11=A5+(D8/2-C5)/D5*10第六章變異度指標(biāo)6.1簡(jiǎn)單變異度指標(biāo)例6.1(6-1)變異度指標(biāo)的計(jì)算步驟如下：將甲乙兩組數(shù)據(jù)輸入到A,B列，根據(jù)Excel提供的公式計(jì)算各種變異度指標(biāo)

ABCDEF1甲組乙組 甲組 乙組22067全距100634068平均差30.002.0046069標(biāo)沽差34.162.165SO71610072712073用Ctrl+'可切換到下面的公式:ABCEF1甲組乙組甲組乙組22067全距=MAX(A2:A7)-MIN(A2:A7)=MAX(E2:B7)-MIN(E2:B7)3406S平均差=AVEDEV(A2:A7)=AVEDEV(E2:B7)46069標(biāo)準(zhǔn)差=STDEVP(A2:A7)=STDEVP(E2:B7)5S0716100727120736.2加權(quán)變異度指標(biāo)例6.2(6-2)甲品種的原始數(shù)據(jù)見(jiàn)下圖B-C列，乙品種的原始數(shù)據(jù)見(jiàn)下圖G-HABcDEFGHIJ1甲品種乙品種2田塊面積產(chǎn)量單產(chǎn)田塊面積產(chǎn)量單產(chǎn)3編號(hào)fxfX聰-mz)佗編號(hào)fxfX411.2120010001.211.51680112021961.5521.110459502641.121.3130010001.3631110011001020131.311709UU12741.3740.98109UU8820.9411208120843681850.884010502080.850.963070080460.99合計(jì)5499599923745合計(jì)6598899815884610均值ms均值mz11標(biāo)準(zhǔn)差68.91標(biāo)準(zhǔn)差162.7112變異系數(shù)％6.90變異系數(shù)％16.30下面以甲品種的數(shù)據(jù)計(jì)算為例:(1)計(jì)算單產(chǎn)值D4=C4/B4,…(2)計(jì)算單產(chǎn)均值D9=C9/B9計(jì)算次數(shù)X離差平方E4=B4*(D4-$D$9)‘2，…并求和E9=SUM(E4:E8)計(jì)算標(biāo)準(zhǔn)差:D11=SQRT(E9/B9)=68.91計(jì)算變異系數(shù)：D12=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(x<5)=P(x<=10)-P(x<=4)P(x>=9)=1-P(x<9)=1-P(x<=8)Excel計(jì)算結(jié)果:

■ABC|D1n152p0.25Ex3.753P(X<=10)0.99994P(X<5)0.6865P(5<=x<=10)0.31345P(X<^)0.995SP(5<=k<=10)0.004=2Excel計(jì)算公式：ABCD1n152p0.25Ex=E1*E23P(X<=10)^BINOMDISTtlO.Bl.B^1)4P(X<5)^BINOMDISTt^El.B^1)P(5<=x<=10)=E3-Bi5P(X<8)=EINOMDIST(8,61^2,1)P(5<=k<=10)=1-E5(Ctrl+'互換)例7.3(7-6)泊松分布計(jì)算公式：P(x>=5)=1-P(x<=4)Excel計(jì)算結(jié)果：ABCD1n1002p0.023lamda24P(X=4)0.09025P(X<=4)0.9473P(x>=5)0.0527Excel計(jì)算公式：

■ AB■c■ D1n1002p0.023lamda=E1+E24P(X=4)=POISSON(4：JB3J0)5Y>(X<=4)二FOI跖ON(￡E擊1)P(x>=5)=1-B5(Ctrl+'互換)例7.4(7-9)超幾何分布D2■=二HYPGEOMDIET(CNE$ZE$￡E$1)■A|B[CD1EF1N60XP(X=K)2M500.637013tl510.3122420.0480530.0027640.0001750.0000例7.5(7-10)正態(tài)分布其中F2=1-D2,D4=D3-D2第八章假設(shè)檢驗(yàn)8.1大樣本——使用正態(tài)分布的假設(shè)檢驗(yàn)例8.1商店經(jīng)理想為商店的持信用卡的顧客建一新的付款系統(tǒng)，經(jīng)過(guò)詳細(xì)的經(jīng)濟(jì)分析，她判定如果新系統(tǒng)每月平均利潤(rùn)低于70元的話(huà)就不能有效地使用資金。于是隨機(jī)抽取了200個(gè)月的利潤(rùn)，其平均月利潤(rùn)為66元。如果a=0.05,有無(wú)充分的證據(jù)說(shuō)明新系統(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ì)算公式ABc1丈樣本已知均值標(biāo)準(zhǔn)差23樣本値4例數(shù)n2005均值1応已a(bǔ)n666檢鸚假設(shè)7檢騎假設(shè)Hijkl已a(bǔ)n708檢驗(yàn)水平a0.059標(biāo)準(zhǔn)差Std3010統(tǒng)計(jì)量11標(biāo)準(zhǔn)誤Se=Std/SQRT(n)12z二(Me:diL-HuMe/Se13檢驗(yàn)14左尾檢驗(yàn)15P值p=NOEMSDIST(z)16左尾檢驗(yàn)17P值p=l-NOEMSDIST(z)18雙尾檢驗(yàn)19P值p=IF(2>0,2*(1-NOEMSDIST(z)),2*NOEMSDIST(z))上圖所示的工作表可用于正態(tài)分布平均值的左尾、右尾和雙尾假設(shè)檢驗(yàn)。檢驗(yàn)結(jié)果包括基于Q判決法和P值報(bào)告法。輸入樣本大小、樣本平均值和標(biāo)準(zhǔn)偏差作為值、公式或引用，指定假設(shè)的平均值（Mean）和顯著水平u作為值。下面各步驟描述了如何建立該工作表：（1） 打開(kāi)一新工作表并輸入B列所示標(biāo)記。（2） 要在C列的公式使用B列中的名稱(chēng)，選取單元B4:C12,從插入菜單中選擇罰指定，在指定名稱(chēng)對(duì)話(huà)框中復(fù)選名稱(chēng)創(chuàng)建于最左列，單擊確定。（3） 輸入C列所示的公式（按圖所示鍵入公式或通過(guò)單擊適當(dāng)?shù)囊衙膯卧迦牒瘮?shù)來(lái)建立公式）。（4） 要得圖8.1的A列所示的外觀，按Ctrl+'。

因?yàn)榻?jīng)理想知道平均月利潤(rùn)是否小于70元，所以備擇假設(shè)為Hd：MeanV70,零假設(shè)為Ho：Mean>70或簡(jiǎn)單地為Ho：Mean=70。由于數(shù)據(jù)已經(jīng)總結(jié)過(guò)了，可直接在工作表單元中輸入樣本大小n、樣本平均值、總體的標(biāo)準(zhǔn)偏差、假設(shè)總體平均值和顯著水平。ABCD1大樣本已知均値標(biāo)準(zhǔn)差23樣本值4例數(shù)n2005均值Mean666檢驗(yàn)假設(shè)7檢騎假設(shè)HoMean708檢騎水平a0.059標(biāo)準(zhǔn)差Std3010統(tǒng)計(jì)量11標(biāo)準(zhǔn)誤Se2.121312zlfz-1.885613檢驗(yàn)14左尾檢驗(yàn)15P值p0.029716左尾檢驗(yàn)17P值p0.970318雙尾檢驗(yàn)19圖8.2正態(tài)假設(shè)檢驗(yàn)0>0503結(jié)論：得到Z小于-1.886的概率是0.0297。如果零假設(shè)為真（每月平均利潤(rùn)為70元），得到樣本平均值為66元或小于它的概率約為3％，即有充分的證據(jù)說(shuō)明新系統(tǒng)是一項(xiàng)節(jié)省資金的系統(tǒng)。8.2小樣本——使t分布的假設(shè)檢驗(yàn)家保險(xiǎn)公司用代理的方式支付其客戶(hù)，賠償假定每年的平均代理賠償費(fèi)用為32000元，如果平均支付費(fèi)用與計(jì)劃不同，就需要對(duì)計(jì)劃進(jìn)行修改。對(duì)一個(gè)有36個(gè)代理的樣本，上一年的平均支付費(fèi)用為27500元，標(biāo)準(zhǔn)偏差為8400元，如果整個(gè)公司的平均支付變化與該樣本的情況不同，那么可用管理計(jì)劃來(lái)修改賠償計(jì)劃。根據(jù)這一結(jié)果的P值，這一樣本能充分說(shuō)明平均值變化了嗎?本例已知均值標(biāo)準(zhǔn)差，下面各步驟描述了如何建立計(jì)算工作表：打開(kāi)一新工作表，輸入A列所示標(biāo)記。要在C列的公式使用B列中的名稱(chēng)，選取單元B4:C13,從插入菜單中選擇|名稱(chēng)||指定|,在指定名稱(chēng)對(duì)話(huà)框中復(fù)選名稱(chēng)創(chuàng)建于最左列，單擊確定|。圖8.3已知均值標(biāo)準(zhǔn)差計(jì)算公式―t假設(shè)檢驗(yàn)的標(biāo)記和公式ABc1小樣本已知均值標(biāo)準(zhǔn)差tte驗(yàn)23樣本值4例數(shù)n=COUNT(Data)5均值Me；dii=AVERAGE(Data)6標(biāo)準(zhǔn)差Std=STDEV(Data)7檢驗(yàn)假設(shè)8檢驗(yàn)假設(shè)HoMe；dii9檢驗(yàn)水平Ct10統(tǒng)計(jì)量11標(biāo)準(zhǔn)誤Se=Std/SQRT(n)12df二門(mén)-113t二(.Mean-HoMean.)/Se14檢驗(yàn)15左尾檢驗(yàn)16P值p=IF(t<0,TDIST(ABS〔tdf,1),1-TDISTtt,df,1))17左尾檢驗(yàn)18P值p=IF(t>OJTDIST(t,df,1),1-TDIST(ABStt),df,1))19雙尾檢驗(yàn)20P值p=IF(AES(t)Jdf,2)因?yàn)榻?jīng)理想知道平均支付是否發(fā)生了變化（不用指出變化的方向），所以備擇假設(shè)為H1：MeanH32000,零假設(shè)為Ho：Mean=32000。由于數(shù)據(jù)已經(jīng)總結(jié)過(guò)了，可以直接向工作表的單元中輸入樣本大小n、樣本平均值、總體的標(biāo)準(zhǔn)偏差和假設(shè)總體的平均值。盡管例中未指明顯著水平可輸入為0.05。結(jié)論：由于P=0.0028〈0.05,所以，有足夠的理由拒絕在顯著水平為5%（雙尾檢驗(yàn)）時(shí)的零假設(shè)，可得出如下結(jié)論：平均支付值不等于32000，明確說(shuō)明平均值改變了。

ABcD1小樣本己知均值標(biāo)準(zhǔn)差t檢驗(yàn) 1I23樣本值4例數(shù)n365均值Mean275006標(biāo)準(zhǔn)差Std04007檢驗(yàn)假設(shè)8檢驗(yàn)假設(shè)HoMean320009檢驗(yàn)水平cc0.0510統(tǒng)計(jì)量11標(biāo)準(zhǔn)誤Se1400.000012df35.000013t-3.214314檢驗(yàn)15左尾檢驗(yàn)16P值p0.001417左尾檢驗(yàn)18P值p0.99B619雙尾檢驗(yàn)20P值EC■ .J3、” 0.002B—圖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)的話(huà)，公司將采用該工藝。當(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ù)畫(huà)圖，所畫(huà)圖形能表明平均斷裂強(qiáng)度有所提高嗎?圖8.5顯示了假設(shè)檢驗(yàn)所需的數(shù)據(jù)。因?yàn)榻?jīng)理想檢查是否有提高，備擇假設(shè)為Hl：Mean>500,所以用右尾檢驗(yàn)比較合適。零假設(shè)為Ho：MeanV500,或簡(jiǎn)單地Ho：Mean=500。如例8.2所述，D2：D13單元已命名為Data,單元B5:B7包含了公式C0UNT(Data),AVERAGE(Data)和STDEV(Data)。盡管例5.5未指定一顯著水平，在B10單元中入了顯著水平為0.05。包含了左尾檢驗(yàn)結(jié)果的15到l8行被隱藏。

ABcD1小樣本已知均值標(biāo)準(zhǔn)差tte驗(yàn)Data25023樣本值4964例數(shù)n=COUNT(D2:D13)5105均值=AVERAGE①2:DI3)5086標(biāo)準(zhǔn)差Std=STDEV(D2:D13)5067檢驗(yàn)假設(shè)4988檢驗(yàn)假設(shè)HoMe；dii5005129檢驗(yàn)水平Ct0.0549710統(tǒng)計(jì)量51511標(biāo)準(zhǔn)誤Se=Std/SQRT(n)50312df=n-l51013逋t=(Mean-HoMean)/Se50614檢驗(yàn)15左尾檢驗(yàn)16P值p=IF(t<0,TDIST(ABStt),df,1),l-TDISTtt,df,1))17左尾檢驗(yàn)18P值p=IF(t》6TDIST(t,df,1),1-TDIST(ABS(t),df,1))19雙尾檢驗(yàn)20P值p=TDIST(ABSCt),df,2)圖8.5小樣本t假設(shè)檢驗(yàn)公式結(jié)論：P=0.0131〈0.05,說(shuō)明有充分證據(jù)來(lái)拒絕零假設(shè)?？傻贸鋈缦陆Y(jié)論:新工藝在統(tǒng)計(jì)上可帶來(lái)平均斷裂強(qiáng)度的顯著提高。ABCDEF1小樣本已知均值標(biāo)準(zhǔn)差t.檢驗(yàn)Data25023樣本值4964例數(shù)n125105均値Mean5055086標(biāo)i隹差Std6.15155067檢驗(yàn)假設(shè)4988檢驗(yàn)假設(shè)HoMean5005129檢驗(yàn)水平0：0.0549710統(tǒng)計(jì)童51511標(biāo)準(zhǔn)誤Se1.775850312df11.000051013湄t2.956450614檢驗(yàn)15左尾檢驗(yàn)1&F值P0.993517左尾檢驗(yàn)18P值P0.006519雙尾檢驗(yàn)20F值P0.0131圖8.6小樣本t假設(shè)檢驗(yàn)結(jié)果第九章相關(guān)與回歸簡(jiǎn)單線(xiàn)性相關(guān)分析例9.1（9-1）Excel進(jìn)行相關(guān)分析：（1）輸數(shù)據(jù)：將數(shù)據(jù)輸入A1:C9單元格。2）繪制散點(diǎn)圖:ABCDEFGH1年份產(chǎn)品產(chǎn)量爲(wèi)單位成本y7473727170696867666564219932.0073++319943.0072419954.0071519963.0073619974.0069719905.0060819996.0066920007.006510110.00 2.00 4.00 6.00 8.001213言1=1廬民J口口」里單位成本14產(chǎn)品產(chǎn)量1t二-9.5915單位成本-0.96891771p=0.0001圖9.1簡(jiǎn)單相關(guān)系數(shù)及散點(diǎn)圖計(jì)算相關(guān)系數(shù)（1）選擇工具菜單之?dāng)?shù)據(jù)分析選項(xiàng),在分析工具框中“相關(guān)系數(shù)”。相關(guān)系數(shù)對(duì)話(huà)框?qū)@示為圖9.2所示，它帶輸入輸出的提示。圖9.2相關(guān)系數(shù)對(duì)話(huà)框1)輸入輸入?yún)^(qū)域：B1：C9分組方式：逐列選擇標(biāo)志位于第一行2)輸出選項(xiàng)輸出區(qū)域：A13.單擊確定,Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中。相關(guān)系數(shù)假設(shè)檢驗(yàn)在單元格F14中輸入公式=B15/SQRT((l-B15'2)/(8-2))計(jì)算得相關(guān)系數(shù)的t值為49.46在單元格F15中輸入公式=TDIST(ABS(F14),B-2,2)計(jì)算得p=0.0001結(jié)論：由于r=-0.9689,且p〈0.05,所以，在0.05水平上拒絕原假設(shè),認(rèn)為產(chǎn)品產(chǎn)量與單位成本間有負(fù)的線(xiàn)性相關(guān)關(guān)系9.2簡(jiǎn)單回歸分析上面的簡(jiǎn)單相關(guān)分析只是說(shuō)明兩變量之間的線(xiàn)性關(guān)系密切的程度，如果要建立它們之間線(xiàn)性依存的關(guān)系式，就需用回歸分析?？砂聪铝胁襟E使用“回歸”分析工具：輸數(shù)據(jù)：將數(shù)據(jù)輸入A1:C9單元格?；貧w分析:(1)選擇工具菜單之?dāng)?shù)據(jù)分析選項(xiàng),在分析工具框中“回歸”。回歸對(duì)話(huà)框?qū)@示為圖9.3所示，圖9.3回歸分析對(duì)話(huà)框1)輸入Y值輸入?yún)^(qū)域：C1:C9X值輸入?yún)^(qū)域：B1:B9標(biāo)志:選擇常數(shù)為零:只有當(dāng)用戶(hù)想強(qiáng)制使回歸線(xiàn)通過(guò)原點(diǎn)(0,0)時(shí)才選此框置信度：Excel自動(dòng)包括了回歸系數(shù)的95%置信區(qū)間。要使用其他置信區(qū)間，選擇該框并在ConfidenceLevet框中輸入置信水平輸出選項(xiàng)輸出區(qū)域：D1殘差殘差(R):選擇此框可得到預(yù)測(cè)值和殘差(Residual)。殘差圖(D):選擇此框可得到殘差和每一x值的圖表。標(biāo)準(zhǔn)殘差(T):選擇此框可得到標(biāo)準(zhǔn)化的殘差，每一殘差被估計(jì)標(biāo)準(zhǔn)誤差除)。這一輸出可使曲線(xiàn)較容易分層。線(xiàn)性擬合圖(I):選擇此框可得到一含有y輸入數(shù)據(jù)和擬合的y值的散點(diǎn)圖。正態(tài)概率圖:繪制因變量的正態(tài)概率圖(2).單擊確定,Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中。圖9.4回歸分析結(jié)果回歸解釋擬合回歸線(xiàn)的截距和斜率放在圖9.4的總結(jié)輸出中標(biāo)記有“Coeffients''的左下部。截距系數(shù)77.30769是線(xiàn)性回歸方程中的常數(shù)項(xiàng)，x系數(shù)-0.80769是斜率?；貧w方程是：率?；貧w方程是：y=77.30769-0.80769*x觀測(cè)值測(cè)單位成本殘差173.觀測(cè)值測(cè)單位成本殘差173.6923-0.6923271.SS460.1154370.07690.92314：71.SS461.1154570.0769-1.0769660.2692-0.2692766.4615-0.4615S64.653S0.346299.5997-0.12971011.5322-0.0522RESIDUALOUTFUT5432109-076540777777666666尸哥罡宰a-圖9.5殘差及擬合線(xiàn)在圖9.5所示的殘差輸出中，預(yù)測(cè)y,有時(shí)又稱(chēng)擬合值，是用這個(gè)回歸方程計(jì)算的單位成本的估計(jì)值。殘差是實(shí)際值和擬合值之間的差值?；卮稹皵M合關(guān)系怎么樣”問(wèn)題的最通用的四個(gè)方法是標(biāo)準(zhǔn)誤差，R2,t統(tǒng)計(jì)值和方差分析。標(biāo)準(zhǔn)誤差0.83205顯示在圖9.4的單元E7中。作為殘數(shù)的標(biāo)準(zhǔn)偏差，它衡量單位成本在回歸線(xiàn)周?chē)姆稚⑶闆r，標(biāo)準(zhǔn)誤差通常稱(chēng)為估計(jì)標(biāo)準(zhǔn)誤差。R2(RSquare)，如圖9.4的單元E5所示，衡量用回歸線(xiàn)解釋的因變量變化的比例。這一比例必?fù)羰?和1之間的一個(gè)數(shù)據(jù)，經(jīng)常以百分?jǐn)?shù)表示。這里約有的94％的單位成本的變化是在線(xiàn)性方程中用產(chǎn)品產(chǎn)量做為預(yù)測(cè)因子來(lái)解釋的。單元E6顯示的AdjustedRsquare在用附加解釋變量把此模型和其他模型比較時(shí)很有用。第十章時(shí)間序列分析指標(biāo)例10.1(10-2)1995-2000的銷(xiāo)售額見(jiàn)下圖B列，則其速度分析指標(biāo)計(jì)算如下：

ABCDEF1年份銷(xiāo)售額逐期增長(zhǎng)量環(huán)比發(fā)展速度環(huán)比發(fā)展速度增長(zhǎng)百分之一21995100.8031996111.0010.20110.1210.121.0141997122.6611.66110.5010.501.115199B136.5213.86111.3011.301.2361999146.009.4S106.946.941.3772000160.2014.20109.739.731.4=6Excel計(jì)算公式如下:AECDEF1年份銷(xiāo)售額逐期增長(zhǎng)量環(huán)比發(fā)展速度環(huán)比發(fā)展速度增長(zhǎng)百分之一21995100.831996111=B3-B2=B3/B2*100=D3-100=C3/E341997122.66=B4-B3=B4/B3*100=D4-100=C4/E451998136.52=E5-B4=B5/B4*100=D5-100=C5/E561999146=E6-B5=B6/B5*100=D6-100=C6/E672000160.2=E7-B6=B7/B6*100=D7-100=C7/E7(Ctrl+'切換)例10.2(10-3)ABCDE1月份總產(chǎn)值a期初職工人數(shù)b生產(chǎn)工人人數(shù)c2116S2001363220419612043SO200136541842061446518221015076IBS210150872002201569020222215S109200212154111019S2161521211201218153131220522915S1412301601516一季150.67196.46全年平均職工人數(shù)17季184.6793S2.67月平均全員勞動(dòng)生產(chǎn)率18三季200.67112592.01年平均全員勞動(dòng)生產(chǎn)率19四季201.3369.66全年職工構(gòu)成指標(biāo)20全年104.33

計(jì)算步驟：（1）各季平均每月總產(chǎn)值計(jì)算公式說(shuō)明單元格公式一季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)/133)月平均勞動(dòng)生產(chǎn)率：C17=B20/C16*10000年平均勞動(dòng)生產(chǎn)率：C18=SUM(B2:B13)/C16*100004)全年職工構(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)F3- ==(PRODUCT(A2:E2)71/5)-1)+100[AHBC[DIEF18.28.88.9810.510.8321.0821.0881.08981.1051.10833平均增長(zhǎng)速度11 9.46|4翻兩隹朋時(shí)間15.345計(jì)算步驟：1)計(jì)算每年的增長(zhǎng)速度A2=1+A1/100,…5年平均增長(zhǎng)速度 F3=(PRODUCT(A2:E2)人(1/5)-1)*100國(guó)民生產(chǎn)總值翻兩翻需要時(shí)間F4=LOG(4,10)/LOG(1+F3/100,10)第十一章時(shí)間數(shù)列預(yù)測(cè)方法例11.1(11-1)(1)計(jì)算按5日擴(kuò)大時(shí)距的時(shí)間數(shù)列和計(jì)算按5日平均日產(chǎn)量的時(shí)間數(shù)列結(jié)果如下表ABCDE1日期產(chǎn)量⑴⑵1213011^51496299.2323026^101552310.443304：11^151566313.25429116^2016153236529S21^2516S53377631026^30174=4340.0Excel計(jì)算公式AB1CDE1日期產(chǎn)量⑴⑵□213011"5=SUM（E2:R6）=AVERAGE（E2:E6）323026^10=SUM（E7:E11）=AVERAGE（E7:E11）4330411^15=SUM（E12:B16）=AVERAGE（E12:E16）5429116^20=SUM（E17:E21）=AVERAGE（E17:E21）6529021^25=SUM（E22:B26）=AVERAGE（B22:B26）7631026^30=SUM（E27:E31）=AVERAGE（E27:E31）3)5日移動(dòng)平均數(shù)。按下列步驟使用“移動(dòng)平均”分析工具：選擇工具菜單之?dāng)?shù)據(jù)分析選項(xiàng),在分析工具框中“移動(dòng)平均”。移動(dòng)平均對(duì)話(huà)框?qū)@示為下圖所示，它帶輸入輸出的提示。1)輸入輸入?yún)^(qū)域：B1:B31□標(biāo)志位于第一行間隔：2)輸出選項(xiàng)輸出區(qū)域：Cl單擊確定,Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中。ABC1日期產(chǎn)量5日移動(dòng)平均21301323024330454291299.26529830176310301.687305303.290312300109315310.411103103101211300312.81312319314.414133203161514：323313.2例11.2(11-2)(2)選擇工具菜單之?dāng)?shù)據(jù)分析選項(xiàng),在分析工具框中“指數(shù)平滑”。

指數(shù)平滑對(duì)話(huà)框?qū)@示為圖所示，它帶輸入輸出的提示。1)輸入阻尼系樹(shù)：0.22)輸出選項(xiàng)輸出區(qū)域：C2□圖表輸出(2).單擊確定,Excel將計(jì)算出結(jié)果顯示在輸出區(qū)域中。例11.3(11-6)1．輸入數(shù)據(jù)，繪制散點(diǎn)圖點(diǎn)擊圖中數(shù)據(jù)點(diǎn)，在菜單“圖表”中添加趨勢(shì)線(xiàn)，在對(duì)話(huà)框中選擇2階多項(xiàng)式，并在選項(xiàng)中選中“顯示公式”和“顯示R平方值”ABCDEFG1年份及資額投資頷21992124124LILI31993129199nn419941362c-c-UU5199514502000?6199615621800?7199716951600?81998184591999201814IJU++?10200022101200111LILILI112lggnl'd'dE1994 199619982LI0LI200213

例11.4(11-7)1．求年合計(jì)和年平均：M2=SUM(B2:M2)，。。。N2=AVERAGE(B2:M2)，。。。2．求月合計(jì)和月平均：B6=SUM(B2:B5)，。。。B7=AVERAGE(B2:B5)，。。。求季節(jié)指數(shù)：B8=B2/$O$2,。。。AECDEFGHIJKLMN01123456789101112合計(jì)平均219978621LL60.4LL81.2252125736.0S3199815942.510.81.223.58.53435116.59.714199924156斗21.13.2斗7154248171.314.2852814831.20.93.74.88.3144751183.915.3367544：2010.54.83.28.91220.842.514：4159544.745.39718.751152.6251.20.82.22535.210.633639.75136.211.358季節(jié)指竅1.320.990.330.160.100.070.130.200.330.823.454.11例11.5(11-7)計(jì)算線(xiàn)性趨勢(shì)值T,D3=FORECAST(B3,C$3:C$22,B$3:B$22),FORECAST函數(shù)直接給出以B$3:B$22為自變量，C$3:C$22為因變量的線(xiàn)性回歸的預(yù)測(cè)(估計(jì))值，將公式拷貝到D22即的結(jié)果。計(jì)算循環(huán)及不規(guī)則變動(dòng)。?1(%)E3=C3/D3*100,。。。用循環(huán)不規(guī)則變動(dòng)的3項(xiàng)移動(dòng)平均計(jì)算循環(huán)變動(dòng)C(%)：F4=AVERAGE(E3:E5)，。。。計(jì)算不規(guī)則變動(dòng)I(%)：G4=E4/F4*100,。。。

ABCDEFG1年份編號(hào)出生人數(shù)趨勢(shì)值循環(huán)及不規(guī)則變動(dòng)C?I（%）循環(huán)變動(dòng)不規(guī)則變動(dòng)2F（萬(wàn)人）T（萬(wàn)人）C(%)1(%)319811143.02130.10109.93419822140.93129.32108.98109.0499.94519B33139.11120.54100.22107.52100.65619B44134.63127.76105.37106.0199.40719S55132.61126.98104.43102.54101.B4819066123.45126.2097.8296.S1101.04919877110.61125.4388.1989.6798.341019888103.48124.6583.0284.4298.341119899101.65123.B7S2.0605.1596.3812199010111.23123.0990.3790.07100.3313199111119.6122.3197.7995.17102.7514199212113.31121.5397.35100.689fi.6915199313129.09120.75106.91102.53104.2716199414123.98119.97103.34：102.12101.2017199515114.55119.1996.1198.8297.2618199616114.86110.4197.0097.1699.0419199717115.7117.6398.36101.1397.2620199BIB126.23116.S510B.03105.55102.3421199919128