《統(tǒng)計學(xué)原理》實驗指導(dǎo)書

一、實驗?zāi)康模?/p>

1、通過實驗讓學(xué)生了解如何在詳細(xì)占有有關(guān)材料的基礎(chǔ)上，靈活應(yīng)用各種科學(xué)

的統(tǒng)計分析方法，揭示事物的總體特征、總體變化及其原因，以及揭示事物之間

的內(nèi)在聯(lián)系及對比關(guān)系；從數(shù)量的內(nèi)在聯(lián)系與對比關(guān)系中發(fā)現(xiàn)問題、提出問題，

然后進行分析研究，從而認(rèn)識事物的本質(zhì)和發(fā)展規(guī)律。

2、掌握用EXCEL進行數(shù)據(jù)的搜集整理和顯示。

二、指導(dǎo)說明

（-）根據(jù)調(diào)查目的確定調(diào)查對象

（二）調(diào)查目的及對象確定情況下，該如何設(shè)計調(diào)查問卷。

（三）操作步驟

1、每位同學(xué)根據(jù)調(diào)查目的先自己設(shè)計一份調(diào)查問卷。

2、學(xué)習(xí)委員將該班學(xué)生進行分組，選出組長。

3、每一組的組長和小組成員協(xié)商，選出一份優(yōu)秀的調(diào)查問卷或?qū)⒃摻M所有成員

的問卷進行補充，設(shè)計出該組的調(diào)查問卷。

4、從各組中選出優(yōu)秀的調(diào)查問卷進行比較補充，設(shè)計出全班統(tǒng)?的調(diào)查問卷。

5、根據(jù)調(diào)查問卷設(shè)計出統(tǒng)一的調(diào)查表。

6、各組分工進行資料的收集。

7、資料加工整理。

（1）各組的每個同學(xué)先對自己搜集的資料進行加工整理；

（2）小組組長匯總該組資料；

（3）學(xué)習(xí)委員匯總各組資料”

8、按照要求運用EXCEL分析資料。即，根據(jù)調(diào)查表的各項指標(biāo)或主要指標(biāo)分

析資料。（見以下EXCEL的操作文件）

9、根據(jù)分析資料撰寫實驗報告（或?qū)嶒灴偨Y(jié)），字?jǐn)?shù)在1500字左右。

10、將所有書面資料按照實驗過程裝訂。

實驗一用Excel搜集與整理數(shù)據(jù)

—?、用Excel搜集數(shù)據(jù)

二、用Excel進行統(tǒng)計分組

三、用Excel作統(tǒng)計圖

實驗二用EXCEL計算描述統(tǒng)計量

一、用函數(shù)計算描述統(tǒng)計量

二、描述統(tǒng)計工具量的使用

實驗三用EXCEL進行時間序列分析

一、測定增長量和平均增長量

二、測定發(fā)展速度和平均發(fā)展速度

三、計算長期趨勢

四、計算季節(jié)變動

實驗四用EXCEL進行指數(shù)分析

一、用Excel計算總指數(shù)

二、用Excel計算平均指數(shù)

三、用Excel進行因素分析

實驗五用EXCEL進行相關(guān)與回歸分析

一、用EXCEL進行相關(guān)分析

二、用EXCEL進行回歸分析

實驗六用統(tǒng)計軟件進行預(yù)測

實驗七用EXCEL進行參數(shù)估計和假設(shè)檢驗

一、用EXCEL進行區(qū)間估計

二、用EXCEL進行假設(shè)檢驗

三、實驗要求和注意事項

1、預(yù)習(xí)實驗指導(dǎo)書的內(nèi)容，結(jié)合課程所講的理論，理解實驗的目的、內(nèi)容和操

作步驟。

2、上機前準(zhǔn)備所需的資料及數(shù)據(jù)。

3、認(rèn)真觀察實驗結(jié)果、記錄實驗結(jié)果。

4、對實驗結(jié)果做簡要分析總結(jié)。

四、實驗成績評價標(biāo)準(zhǔn)

1、優(yōu)秀：獨立完成實驗報告，實驗步驟、實驗結(jié)論正確。

2、良好：實驗步驟、結(jié)論基本正確。

3、中等：實驗步驟、結(jié)論出現(xiàn)錯誤較多。

4、及格：不能獨立完成實驗報告或步驟、結(jié)論出現(xiàn)重大錯誤，態(tài)度不認(rèn)真。

5、不及格：態(tài)度很不認(rèn)真。

實驗一用Excel搜集與整理數(shù)據(jù)

實驗?zāi)康模赫莆沼肊XCEL進行數(shù)據(jù)的搜集整理和顯示

實驗步驟：

一、用Excel搜集數(shù)據(jù)

搜集數(shù)據(jù)的方法有多種，可以采用統(tǒng)計報表、典型調(diào)查、重點調(diào)查或抽樣調(diào)

查，以后我國的統(tǒng)計調(diào)查將以抽樣為主，所以我們在這里介紹一下如何用Excel

進行抽樣。

使用Excel進行抽樣，首先要對各個總體單位進行編號，編號可以按隨機原

則，也可以按有關(guān)標(biāo)志或無關(guān)標(biāo)志，具體可參見本書有關(guān)抽樣的章節(jié)，編號后，

將編號輸入工作表。

【例13-1】假定有100個總體單位，每個總體單位給一個編號，共有從1

至U到0個編號，輸入工作表后如圖13-1所示：

i（＞--■?='?Iw4UI..NES996?太啟、＜*EM.0.d-_

—1

11?.ZIT81

.22

2222二278

33323m3738393

4442;4478494

5K35K748590

6-666-78e

G7:7G79

T70n777H8TQ175

80-

80:087878838

999-89g

?V9

00oo0

yC7?、；」」_■L!/t：t＞TTt3/1?I

圖：13-1總體各單位編號表

輸入各總體單位的編號后，可按以下步驟操作：

第?步：單擊工具菜單，選擇數(shù)據(jù)分析選項（若無數(shù)據(jù)分析選項，可在工具

菜單下選擇加載宏，在彈出的對話框中選擇分析工具庫，便可出現(xiàn)數(shù)據(jù)分析選

項），打開數(shù)據(jù)分析對話框，從中選擇抽樣。如圖13-2所示：

數(shù)據(jù)分析JJ2SJ

圖13-2數(shù)據(jù)分析對話框

第二步：單擊抽樣選項，彈出抽樣對話框。如圖13-3

圖13-3抽樣對話框

第三步：在輸入?yún)^(qū)域框中輸入總體單位編號所在的單元格區(qū)域，在本例是

$A$1:$J$1O,系統(tǒng)將從A列開始抽取樣本，然后按順序抽取B列至J歹!L如果

輸入?yún)^(qū)域的第一行或第一列為標(biāo)志項（橫行標(biāo)題或縱列標(biāo)題），可單擊標(biāo)志復(fù)選

框。

第四步：在抽樣方法項下，有周期和隨機兩種抽樣模式：

“周期”模式即所謂的等距抽樣，采用這種抽樣方法，需將總體單位數(shù)除以

要抽取的樣本單位數(shù)，求得取樣的周期間隔。如我們要在100個總體單位中抽取

12個，則在“間隔”框中輸入8。

“隨機模式”適用于純隨機抽樣、分類抽樣、整群抽樣和階段抽樣。采用純

隨機抽樣，只需在“樣本數(shù)”框中輸入要抽取的樣本單位數(shù)即可；若采用分類抽

樣，必須先將總體單位按某?標(biāo)志分類編號，然后在每一類中隨機抽取若干單位,

這種抽樣方法實際是分組法與隨機抽樣的結(jié)合；整群抽樣也要先將總體單位分類

編號，然后按隨機原則抽取若干類作為樣本，對抽中的類的所有單位全部進行調(diào)

查?？梢钥闯?，此例的編號輸入方法，只適用于等距抽樣和純隨機抽樣。

第四步：指定輸出區(qū)域，在這里我們輸入$人$14,單擊確定后，即可得到抽

樣結(jié)果，如圖13-4

圖13-4等距抽樣結(jié)果

二、用Excel進行統(tǒng)計分組

用Excel進行統(tǒng)計分組有兩種方法，一是利用FREQUENCY函數(shù)；二是利用

數(shù)據(jù)分析中的“直方圖”工具。我們介紹一下后者的操作方法。

【例13-2］我們采用第三章例3-1,把50名工人的月產(chǎn)量資料輸入工作表，

如圖13-5所小:

然后按以下步驟操作。

第一步：在工具菜單中單擊數(shù)據(jù)分析選項，從其對話框的分析工具列表中選

擇直方圖，打開直方圖對話框。如圖13-6所示:

直方圖

圖13-6直方圖對話框

第二步：在輸入?yún)^(qū)域輸入$人$1:$畤5,在接收區(qū)域輸入$人$9:$人$15。接收區(qū)

域指的是分組標(biāo)志所在的區(qū)域，假定我們把分組標(biāo)志輸入到A9:A15單元格，注

意這里只能輸入每一組的上限值，即130,160,190,220,250,280,310。

第三步：選擇輸出選項，可選擇輸入?yún)^(qū)域、新工作表組或新工作薄。我們在

這里選擇輸入?yún)^(qū)域，可以直接選擇一個區(qū)域，也可以直接輸入一個單元格（代表

輸出區(qū)域的左上角），這里我們推薦只輸入一個單元格（本例為A7）,因為我們

往往事先并不知道具體的輸出區(qū)域有多大。

第四步：選擇圖表輸出，可以得到直方圖；選擇累計百分率，系統(tǒng)將在直方

圖上添加累積頻率折線；選擇柏拉圖，可得到按降序排列的直方圖。

第五步：按確定按鈕，可得輸出結(jié)果如圖13-7：

i*1）srwqr?CB>urncy>MB入xxci?*u>ar，?w??>???.

I*伊二*|w*04OPtx；.ZaS^3

\ij-J-1?rrqv

圖13-7頻數(shù)分布和直方圖

應(yīng)當(dāng)注意，上圖實際上是一個條形圖，而不是直方圖，若要把它變成直方圖,

可按如下操作：

用鼠標(biāo)左鍵單擊任一直條，然后右鍵單擊，在彈出的快捷菜單中選取數(shù)據(jù)系

列格式，彈出數(shù)據(jù)系列格式對話框，如圖13-8所示：

圖13-8數(shù)據(jù)系列格式對話框

在對話框中選擇選項標(biāo)簽，把間距寬度改為0,按確定后即可得到直方圖,

如圖13-9所示:

1

1

1

11001a0

0108N0

B2.

-1o06.0

274

^O7.0

2207R凡0

a0U明0

3

O。060

.：n

圖13-9調(diào)整后的直方圖

三、用Excel作統(tǒng)計圖

Excel提供的統(tǒng)計圖有多種，包括柱形圖、條形圖、折線圖、餅圖、散點圖、

面積圖、環(huán)形圖、雷達(dá)圖、曲面圖、氣泡圖、股價圖、圓柱圖、圓錐圖等，各種

圖的作法大同小異。

【例13-3】我們這里采用第三章表3-13的資料，看一下如何作餅圖。首先

把數(shù)據(jù)輸入到工作表中，如圖13-10所示：

展*

f二

?

山

奈

三

三

m；

*°7

>11rtl

圖13-10河北省主要市2001年人口情況

按以下步驟可作出餅圖：

第一步：選中某一單元格，單擊插入菜單，選擇圖表選項，彈出圖表向?qū)?/p>

話框。如圖13-11：

圖13-11圖表向?qū)υ捒?/p>

第二步：在圖表類型中選擇餅圖，然后在子圖表類型中選擇一種類型，這里

我們選用系統(tǒng)默認(rèn)的方式。然后單擊下一步按鈕，打開源數(shù)據(jù)對話框。如圖13-12

所示：

*君￡工：I—Li，:FC7室￡J

K加L主在.r-萬QBJ>

e加<XJ>

士」|0E|<上一RCBJ>。下H>■gRgttQD]

圖13-12源數(shù)據(jù)對話框

第三步：在源數(shù)據(jù)對話框中填入數(shù)據(jù)所在區(qū)域，單擊完成按鈕，即可得如

13-13所示的餅圖：

□石家莊市

■唐山市

□秦皇島市

口保定市

■邯鄲市

□邢臺市

■滄州|仃

□張家口市

圖13-13餅圖

實驗二用EXCEL計算描述統(tǒng)計量

實驗?zāi)康模河肊XCEL計算描述統(tǒng)計量

實驗步驟：

EXCEL中用于計算描述統(tǒng)計量的方法有兩種，函數(shù)方法和描述統(tǒng)計工具的

方法。

一、用函數(shù)計算描述統(tǒng)計量

常用的描述統(tǒng)計量有眾數(shù)、中位數(shù)、算術(shù)平均數(shù)、調(diào)和平均數(shù)、幾何平均數(shù)、

極差、四分位差、標(biāo)準(zhǔn)差、方差、標(biāo)準(zhǔn)差系數(shù)等。一般來說，在Excel中求這些

統(tǒng)計量，未分組資料可用函數(shù)計算，已分組資料可用公式計算。這里我們僅介紹

如何用函數(shù)計算。

（一）眾數(shù)

【例13-4]：為了解某經(jīng)濟學(xué)院新畢業(yè)大學(xué)生的工資情況，隨機抽取30人，

月工資如下：

15601340160014101590141016101570171015501490

16901380168014701530156012501560135015601510

15501460155015701980161015101440

用函數(shù)方法求眾數(shù)，應(yīng)先將30個人的工資數(shù)據(jù)輸入Al：A30單元格，

然后單擊任一空單元格，輸入“=MODE（Al：A30）”，回車后即可得眾

數(shù)為1560

（二）中位數(shù)

仍米用上面的例子，單擊任一空單元格，輸入“=MEDIAN（Al：A30）”,

回車后得中位數(shù)為1550。

（三）算術(shù)平均數(shù)

單擊任一單元格，輸入“=AVERAGE（Al：A30）”，回車后得算術(shù)平

均數(shù)為1535。

（四）標(biāo)準(zhǔn)差

單擊任一單元格，輸入“=STDEV（Al：A30）”，回車后得標(biāo)準(zhǔn)差為

135.0287。

二、描述統(tǒng)計工具量的使用

仍使用上面的例子，我們已經(jīng)把數(shù)據(jù)輸入到Al：A30單元格，然后按以下步驟操

作：

第一步：在工具菜單中選擇數(shù)據(jù)分析選項，從其對話框中選擇描述統(tǒng)計,

按確定后打開描述統(tǒng)計對話框，如圖13-14所示：

圖13-14描述統(tǒng)計對話框

第二步：在輸入?yún)^(qū)域中輸入$A$l:$A$30,在輸出區(qū)域中選擇$C$1,其他復(fù)選

框可根據(jù)需要選定，選擇匯總統(tǒng)計，可給出一系列描述統(tǒng)計量；選擇平均數(shù)置信

度，會給出用樣本平均數(shù)估計總體平均數(shù)的置信區(qū)間；第K大值和第K小值會給

出樣本中第K個大值和第K個小值。

第三步：單擊確定，可得輸出結(jié)果，如圖13T5所示：

?UMNlWri?TMlTlWBOV4mJM

圖13-15描述統(tǒng)計輸出結(jié)果

上面的結(jié)果中，平均指樣本均值；標(biāo)準(zhǔn)誤差指樣本平均數(shù)的標(biāo)準(zhǔn)差；中值即

中位數(shù)；模式指眾數(shù)；標(biāo)準(zhǔn)偏差指樣本標(biāo)準(zhǔn)差，自由度為n-1；峰值即峰度系數(shù);

偏斜度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差，或全距。（可見中文版的翻譯有問題）

實驗三用EXCEL進行時間序列分析

實驗?zāi)康模河肊XCEL進行時間序列分析

實驗步驟：

一、測定增長量和平均增長量

【例13-5］：根據(jù)1995-2001年河北省國內(nèi)生產(chǎn)總值，計算逐期增長量、累

圖13-16用EXCEL計算增長量和平均增長量資料及結(jié)果

計算步驟如下：

第一步：在A列輸入年份，在B列輸入國內(nèi)生產(chǎn)總值。

第二步：計算逐期增長量：在C3中輸入公式：=B3-B2,并用鼠標(biāo)拖曳將公

式復(fù)制到C3：C8區(qū)域。

第三步：計算累計增長量：在D3中輸入公式：=B3-$B$2,并用鼠標(biāo)拖曳公

式復(fù)制到D3：D8區(qū)域。

第四步：計算平均增長量(水平法)：在CIO中輸入公式：=(B8-B2)/5,

按回車鍵，即可得到平均增長量。

二、測定發(fā)展速度和平均發(fā)展速度

【例13-6］：以1995-2001年河北省國內(nèi)生產(chǎn)總值為例，說明如何計算定基

發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速度。如圖13-17

YJtrrtir?，，工—，?*???>?M>

|T.m.?N-R■_?5?<.u_tmhcry>?im?-.a.?”ws，vcu“，<■x>*N*>■t%u.Wwrcix<w?.J*eIh—-4-d-C2i?__|gi

itriniri"■iit?.omH,：rin)

?KMIMVM(I

圖13-17用EXCEL計算發(fā)展速度和平均發(fā)展速度資料及結(jié)果

第一步：在A列輸入年份，在B列輸入國內(nèi)生產(chǎn)總值。

第二步：計算定基發(fā)展速度：在C3中輸入公式：=B3/$B$2,并用鼠標(biāo)拖曳

將公式復(fù)制到C3：C8區(qū)域。

第三步：計算環(huán)比發(fā)展速度：在D3中輸入公式：=B3/B2,并用鼠標(biāo)拖曳將

公式復(fù)制到D3：D8區(qū)域。

第四步：計算平均發(fā)展速度（水平法）：選中CIO單元格，單擊插入菜單,

選擇函數(shù)選項，出現(xiàn)插入函數(shù)對話框后，選擇GEOMEAN（返回兒何平均值）

函數(shù)，在數(shù)值區(qū)域中輸入D3：D8即可。

三、計算長期趨勢

【例13-7］：我們用某企業(yè)某年12個的總產(chǎn)值資料來說明如何用移動平均法

計算長期趨勢。如圖13-18

圖13-18用EXCEL計算長期趨勢資料及結(jié)果

第一步：在A列輸入月份，在B列輸入總產(chǎn)值。

第二步：計算三項移動平均：在C3中輸入“=（B2+B3+B4）/3"，并用鼠

標(biāo)拖曳將公式復(fù)制到C3：C12區(qū)域。

第三步：計算四項移動平均：在D4中輸入“=SUM（B2：B5）/4"，并用鼠

標(biāo)拖曳將公式復(fù)制到D4：D12區(qū)域。

第四步：計算二項移正平均數(shù)：在E4中輸入“=（D4+D5）/2"，并用公式

拖曳將公式復(fù)制到E4：E11區(qū)域。

四、計算季節(jié)變動

【例13-8］：利用某種商品五年分季度的銷售額資料，說明如何用移動平均

趨勢剔除法測定季節(jié)變動。如圖13-19

CAPSNUM

圖13-19用EXCEL計算季節(jié)變動資料

第一?步：按圖上的格式在A列輸入年份，在B列輸入季別，在C列輸入銷

售收入。

第二步：計算四項移動平均：在D3中輸入“=SUM(C2：C4)/4"，并用鼠

標(biāo)拖曳將公式復(fù)制到D3：D19區(qū)域。

第三步：計算趨勢值(即二項移動平均)T：在E4中輸入“=(D3+D4)/2”,

并用鼠標(biāo)拖曳將公式復(fù)制到E4：E19區(qū)域。

第四步：剔除長期趨勢，即計算Y/T：在F4中輸入“=C4/E4”，并用鼠標(biāo)拖

曳將公式復(fù)制到F4：F19區(qū)域。

第五步：重新排列F4：F19區(qū)域中的數(shù)字，使同季的數(shù)字位于一列，共排

成四列。

第六步：計算各年同季平均數(shù)：在B29單元格中輸入公式：

=average(B25:B28)；在C29中輸入公式=average(C25:C28)；在D29中輸入公式

=average(D24:27)；在E29中輸入公式=average(E24:E27)。

第七步：計算調(diào)整系數(shù)：在B31中輸入公式：=4/sum(B29:E29)

第八步：計算季節(jié)比率：在B30中輸入公式：=B29*$B$31,并用鼠標(biāo)拖曳

將公式復(fù)制到單元格區(qū)域B30：E30,就可以得到季節(jié)比率的值，具體結(jié)果見圖

13-20：

A

粵1aLq11KlHIJ三

年孑第二季郎一手笫四￥

24年

三

26年OO1.147677320.89609

O.8897121.07415471.153144888O.925504

06=年

7O.RS59-?1.07990221.O64O9687U0.919793

-四

8

2輩O.9346221.OO64G121.11722633O.918063

29五O.9451191.0982712O0

0均

2案,0.9138611.OG4G0731.1205333534888.

^1差

31

32

33

34

35

36

37

38

39

40

41

4N

圖13-20用EXCEL計算季節(jié)變動結(jié)果

實驗四用EXCEL進行指數(shù)分析

實驗?zāi)康模河肊XCEL進行指數(shù)分析

實驗步驟：

指數(shù)分析法是研究社會經(jīng)濟現(xiàn)象數(shù)量變動情況的--種統(tǒng)計分析法。指數(shù)有總

指數(shù)與平均指數(shù)之分，在這一節(jié)我們介紹如何用Excel進行指數(shù)分析與因素分析。

一、用Excel計算總指數(shù)

【例13-9］：圖中是某企業(yè)甲、乙、丙三種產(chǎn)品的生產(chǎn)情況，以基期價格p

作為同度量因素，計算生產(chǎn)量指數(shù)。如圖13-21

圖13-21用EXCEL計算總指數(shù)資料及結(jié)果

計算步驟：

第一步：計算各個pOqO：在G2中輸入“=C2*D2"，并用鼠標(biāo)拖曳將公式復(fù)

制到G2：G4區(qū)域。

第二步：計算各個pO*ql：有H2中輸入“=C2*F2"，并用鼠標(biāo)拖曳將公式

復(fù)制到H2：H4區(qū)域。

第三步：計算ZpOqO和ZpOql：選定G2：G4區(qū)域，單擊工具欄上的“2”

按鈕，在G5出現(xiàn)該列的求和值。選定H2：H4區(qū)域，單擊工具欄上的“Z”按鈕,

在H5出現(xiàn)該列的求和值。

第四步：計算生產(chǎn)量綜合指數(shù)Iq=ZpOql/2pOqO：在C6中輸入“=H5/G5”

便可得到生產(chǎn)量綜合指數(shù)

注意：在輸入公式的時候，不要忘記等號，否則就不會出現(xiàn)數(shù)值。

二、用Excel計算平均指數(shù)

現(xiàn)以生產(chǎn)量平均指數(shù)為例，說明加權(quán)算術(shù)平均法的計算方法。

【例13-10］：圖中的Al：A4區(qū)域內(nèi)是某企業(yè)生產(chǎn)情況的統(tǒng)計資料，我們要

以基期總成本為同度量因素，計算生產(chǎn)量平均指數(shù)。如圖13-22

L0

鬻

生

Il

圖13-22用EXCEL計算平均指數(shù)資料及結(jié)果

計算步驟:

第一步:計算個體指數(shù)k=ql/qO：在F2中輸入“=D2/C2”。并用鼠標(biāo)拖曳

將公式復(fù)制到F2：F4區(qū)域。

第二步：計算k*pOqO并求和。在G2中輸入“=F2*E2”并用鼠標(biāo)拖曳將公

式復(fù)制到G2：G4區(qū)域。選定G2：G4區(qū)域，單擊工具欄上的：“Z”按鈕，在

G5列出現(xiàn)該列的求和值。

第三步：計算生產(chǎn)量平均指數(shù)：在C7中輸入“=G5/E5”即得到所求的值。

三、用Excel進行因素分析

【例13-11］：我們還用上面的例子，有關(guān)資料如圖13-23

i

n

w

￡

i?

i§*

!?

!:

同

一

圖13-23用EXCEL進行因素分析資料及結(jié)果

進行因素分析的計算步驟如下：

第一步：計算各個pO*qO和EpOqO：在G2中輸入“C2*D2"，并用鼠標(biāo)拖曳

將公式復(fù)制到G2：G4區(qū)域。選定G2：G4區(qū)域，單擊工具欄上的按鈕，在

G5出現(xiàn)該列的求和值。

第二步:計算各個pO*ql和￡pO*ql：在H2中輸入“=C2*F2”,并用鼠標(biāo)拖

曳將公式復(fù)制到H2：H4區(qū)域。選定H2：H6區(qū)域，單擊工具欄上的按鈕，

在H5出現(xiàn)該列的求和值。

第三步：計算各個pl*ql和￡pl*ql：在12中輸入“=E2*F2"，并用鼠標(biāo)拖

曳將公式復(fù)制到12：14區(qū)域。選定12：14區(qū)域，單擊工具欄上的按鈕，

在15出現(xiàn)該列的求和值。

第四步：計算總成本指數(shù)：在C6中輸入“=I5/G5”，即求得總成本指數(shù)。

第五步：計算產(chǎn)量指數(shù)：在C7中輸入“=H5/G5”，即得產(chǎn)量指數(shù)

第六步：計算單位成本指數(shù)：在C8中輸入“=I5/H5”即求得單位成本指數(shù)

實驗五用EXCEL進行相關(guān)與回歸分析

實驗?zāi)康模河肊XCEL進行相關(guān)與回歸分析

實驗步驟：

我們用下面的例子進行相關(guān)和回歸分析：

【例13-12］：10個學(xué)生身高和體重的情況如下：

學(xué)生身高（公分）體重（公斤）

117153

216756

317764

415449

516955

617566

716352

815247

917258

1016050

要求對身高和體重作相關(guān)和回歸分析。

一、用EXCEL進行相關(guān)分析

首先把有關(guān)數(shù)據(jù)輸入EXCEL的單元格中，如圖13-24

用EXCEL進行相關(guān)分析有兩種方法，一是利用相關(guān)系數(shù)函數(shù)，另一種是利用相

關(guān)分析宏。

1.利用函數(shù)計算相關(guān)系數(shù)

在EXCEL中，提供了兩個計算兩個變量之間相關(guān)系數(shù)的方法，CORREL函

數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價的，這里我們介紹用CORREL函數(shù)計算

相關(guān)系數(shù)：

第一步：單擊任一個空白單元格，單擊插入菜單，選擇函數(shù)選項，打開粘貼

函數(shù)對話框，在函數(shù)分類中選擇統(tǒng)計，在函數(shù)名中選擇CORREL,單擊確定后，

出現(xiàn)CORREL對話框。

第二步：在arrayl中輸入B2：B11,在array2中輸入C2：C11,即可在對

話框下方顯示出計算結(jié)果為0896。如圖13-25所示：

Arrayl|B2EllK]={171；167：177：154

Array2|C2:C11司={53;56;64;49;55;F

=0.896027805

返回兩組數(shù)值的相關(guān)系數(shù).

Array2第二狙數(shù)值單元格區(qū)域.

回|計宜結(jié)果=0.89602T805|確定|取消|

圖13-25CORREL對話框及輸入結(jié)果

2.用相關(guān)系數(shù)宏計算相關(guān)系數(shù)

第一步：單擊工具菜單，選擇數(shù)據(jù)分析選項，在數(shù)據(jù)分析選項中選擇相關(guān)系

數(shù)，彈出相關(guān)系數(shù)對話框，如圖13-26所示：

圖13-26相關(guān)系數(shù)對話框

第二步：在輸入?yún)^(qū)域輸入$B$1:$C$1,分組方式選擇逐列，選擇標(biāo)志位于第

一行，在輸出區(qū)域中輸入$E$1,單擊確定，得輸出結(jié)果如圖13-27

I」，1F

圖13-27相關(guān)分析輸出結(jié)果

在上面的輸出結(jié)果中，身高和體重的自相關(guān)系數(shù)均為1,身高和體重的相關(guān)系數(shù)

為0.896,和用函數(shù)計算的結(jié)果完全相同。

二、用EXCEL進行回歸分析

EXCEL進行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個函

數(shù)用于建立回歸模型和預(yù)測。這9個函數(shù)分別是：

INTERCEPT返回線性回歸模型的截距

SLOPE返回線性回歸模型的斜率

RSQ返回線性回歸模型的判定系數(shù)

FORECAST返回一元線性回歸模型的預(yù)測值

STEYX計算估計的標(biāo)準(zhǔn)誤

TREND計算線性回歸線的趨勢值

GROWTH返回指數(shù)曲線的趨勢值

LINEST返回線性回歸模型的參數(shù)

LOGEST返回指數(shù)曲線模型的參數(shù)

用函數(shù)進行回歸分析比較麻煩，我們這里介紹使用回歸分析宏進行回歸分

析。

第一步：單擊工具菜單，選擇數(shù)據(jù)分析選項，出現(xiàn)數(shù)據(jù)分析對話框，在分析

工具中選擇回歸，如圖13-28

圖13-28數(shù)據(jù)分析對話框

第二步:單擊確定按鈕,彈出回歸對話框，在Y值輸入?yún)^(qū)域輸入$8$2：$B$11,

在X值輸入?yún)^(qū)域輸入$?$2：$C$11,在輸出選項選擇新工作表組，如圖13-29所

示：

圖13-29回歸對話框

第四步：單擊確定按鈕，得回歸分析結(jié)果如圖13-30所示

圖13-30EXCEL回歸分析結(jié)果

在上面的輸出結(jié)果中，第一部分為匯總統(tǒng)計，MultipleR指復(fù)相關(guān)系數(shù)，RSquare

指判定系數(shù)，Adjusted指調(diào)整的判定系數(shù)，標(biāo)準(zhǔn)誤差指估計的標(biāo)準(zhǔn)誤，觀測值指

樣本容量；第二部分為方差分析，df指自由度，SS指平方和，MS指均方，F(xiàn)指

F統(tǒng)計量，SignificanceofF指p值；第三部分包括：Intercept指截距，Coefficient

指系數(shù)，tstat指t統(tǒng)計量。

實驗六用統(tǒng)計軟件進行預(yù)測

實驗?zāi)康模河肊XCEL進行預(yù)測

實驗步驟：

【例13-13］：某煤礦某年1-11月份5K煤量如下表：

月份產(chǎn)量月份產(chǎn)量

19.0379.15

29.0689.36

39.1299.45

48.73109.30

58.94119.24

69.3012

一、用移動平均法進行預(yù)測

具體步驟:

第一步：將原始數(shù)據(jù)錄入到單元格區(qū)域A2：A12,如圖13-31所示:

W—

6

7

w

i?

ll

14

15

i?

il

2no

圖13-32數(shù)據(jù)分析對話框

第三步：在"分析工具”框中選擇“移動平均"，單擊“確定”按鈕，彈出

移動平均對話框，相應(yīng)作如下輸入，即可得到如圖13-33所示的對話框：

（1）在“輸出區(qū)域”內(nèi)輸入：$A$2：$A$12,即原始數(shù)據(jù)所在的單元格區(qū)域。

（2）在“間隔”內(nèi)輸入：3,表示使用三步移動平均法。

（3）在“輸出區(qū)域”內(nèi)輸入：B2,即將輸出區(qū)域的左上角單元格定義為B2。

（4）選擇“圖表輸出”復(fù)選框和“標(biāo)準(zhǔn)誤差”復(fù)選框。

13-33移動平均對話框

第四步：單擊“確定”按鈕，便可得到移動平均結(jié)果，如圖13-34所示：

分析：在圖中，B4：B12對應(yīng)的數(shù)據(jù)即為三步移動平均的預(yù)測值；單元格區(qū)域

C6：C12即為標(biāo)準(zhǔn)誤差。

13-34移動平均分析結(jié)果

二、用指數(shù)平滑法進行預(yù)測:

第一步：將原始數(shù)據(jù)輸入到單元格B2：B12；

第二步：選擇菜單條上的“工具”——“數(shù)據(jù)分析”命令，彈出如圖13-35

所示的對話框：

圖13-35數(shù)據(jù)分析對話框

第三步：在“分析工具”中選擇“指數(shù)平滑”，單擊“確定”按鈕，彈出一

個對話框，作相應(yīng)輸入，即可得到如圖13^6所示的對話框;

13-36指數(shù)平滑對話框

第四步：單擊“確定”按鈕，即可得到指數(shù)平滑結(jié)果，如圖13-37所示:

903

-

2S

?0

9085

0250

?6

93125U2O875?l

8江.116630208982

江18102986G9

29102196B4

d.

39B02BB639

Qa.

32Rn178777

圖13-37指數(shù)平滑結(jié)果

三、趨勢預(yù)測法進行預(yù)測

第一步：把相關(guān)數(shù)據(jù)輸入到EXCEL中，其中月份輸入A1-A11單元格,

第二步：在工作表中選擇一個空的單元格。在這里我們選擇D2單元格。

第三步：選擇插入下拉菜單。

第四步：選擇函數(shù)選項。

第五步：當(dāng)函數(shù)對話框出現(xiàn)時：在函數(shù)類別框中選擇統(tǒng)計，在函數(shù)名字中選

圖13-39粘貼函數(shù)對話框

第六步：單擊確定按鈕，出現(xiàn)預(yù)測對話框，在x中輸入12,在know-y，s中

輸入Bl：B11,在know-x，s中輸入Al：A1L如圖13-40所示:

=9.396545455

通過一條線性回歸擬合線返回一個預(yù)測值-

Known_/零從滿足線性擬合直線y=mx+b的點集中選出的一組已知的y值。

|回|計算結(jié)果:9.396545455確定|取消

圖13-40FORCAST對話框

第七步：單擊確定按鈕，預(yù)測結(jié)果出現(xiàn)在D2單元格中，如圖13-41所示:

圖13-41趨勢預(yù)測法預(yù)測結(jié)果

實驗七用EXCEL進行參數(shù)估計和假設(shè)檢驗

實驗?zāi)康模河肊XCEL進行參數(shù)估計和假設(shè)檢驗

實驗步驟：

在EXCEL中，進行參數(shù)估計只能使用公式和函數(shù)的方法，而假設(shè)檢驗除以

上兩種方法外，還可以使用假設(shè)檢驗工具。

一、用EXCEL進行區(qū)間估計

【例13-14］：某飯店在7星期內(nèi)抽查49位顧客的消費額（元）如下：1524

3826304218302526344420352426344818

2846193036422432453621472628314245

36242827323647532224324626

求在概率90%的保證下，顧客平均消費額的估計區(qū)間。如圖13-42：

計算方法如下：

第一步：把數(shù)據(jù)輸入到A2：A5O單元格。

第二步：在C2中輸入公式“=COUNT（A2：A5O）”，C3中輸入“=AVERAGE

（A2：A5O）”，在C4中輸入“STDEV（A2：A5O）”，在C5中輸入“=C4/SQRT

（C2）"，在C6中輸入0.90,在C7中輸入“=C2-1"，在C8