《統(tǒng)計(jì)學(xué)原理》實(shí)驗(yàn)指導(dǎo)書(shū)

《統(tǒng)計(jì)學(xué)原理》實(shí)驗(yàn)指導(dǎo)書(shū)

一、實(shí)驗(yàn)?zāi)康模?/p>

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

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

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

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

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

二、指導(dǎo)說(shuō)明

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

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

（三）操作步驟

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

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

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

的問(wèn)卷進(jìn)行補(bǔ)充，設(shè)計(jì)出該組的調(diào)查問(wèn)卷。

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

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

6、各組分工進(jìn)行資料的收集。

7、資料加工整理。

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

（2）小組組長(zhǎng)匯總該組資料；

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

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

析資料。（見(jiàn)以下EXCEL的操作文件）

9、根據(jù)分析資料撰寫實(shí)驗(yàn)報(bào)告（或?qū)嶒?yàn)總結(jié)），字?jǐn)?shù)在1500字左右。

10、將所有書(shū)面資料按照實(shí)驗(yàn)過(guò)程裝訂。

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

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

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

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

實(shí)驗(yàn)二用EXCEL計(jì)算描述統(tǒng)計(jì)量

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

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

實(shí)驗(yàn)三用EXCEL進(jìn)行時(shí)間序列分析

一、測(cè)定增長(zhǎng)量和平均增長(zhǎng)量

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

三、計(jì)算長(zhǎng)期趨勢(shì)

四、計(jì)算季節(jié)變動(dòng)

實(shí)驗(yàn)四用EXCEL進(jìn)行指數(shù)分析

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

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

三、用Excel進(jìn)行因素分析

實(shí)驗(yàn)五用EXCEL進(jìn)行相關(guān)與回歸分析

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

二、用EXCEL進(jìn)行回歸分析

實(shí)驗(yàn)六用統(tǒng)計(jì)軟件進(jìn)行預(yù)測(cè)

實(shí)驗(yàn)七用EXCEL進(jìn)行參數(shù)估計(jì)和假設(shè)檢驗(yàn)

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

二、用EXCEL進(jìn)行假設(shè)檢驗(yàn)

三、實(shí)驗(yàn)要求和注意事項(xiàng)

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

作步驟。

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

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

4、對(duì)實(shí)驗(yàn)結(jié)果做簡(jiǎn)要分析總結(jié)。

四、實(shí)驗(yàn)成績(jī)?cè)u(píng)價(jià)標(biāo)準(zhǔn)

1、優(yōu)秀：獨(dú)立完成實(shí)驗(yàn)報(bào)告，實(shí)驗(yàn)步驟、實(shí)驗(yàn)結(jié)論正確。

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

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

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

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

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

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

實(shí)驗(yàn)步驟：

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

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

查，以后我國(guó)的統(tǒng)計(jì)調(diào)查將以抽樣為主，所以我們?cè)谶@里介紹一下如何用Excel

進(jìn)行抽樣。

使用Excel進(jìn)行抽樣，首先要對(duì)各個(gè)總體單位進(jìn)行編號(hào)，編號(hào)可以按隨機(jī)原

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

將編號(hào)輸入工作表。

【例13-1】假定有100個(gè)總體單位，每個(gè)總體單位給一個(gè)編號(hào)，共有從1

至U到0個(gè)編號(hào)，輸入工作表后如圖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總體各單位編號(hào)表

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

第?步：?jiǎn)螕艄ぞ卟藛?，選擇數(shù)據(jù)分析選項(xiàng)（若無(wú)數(shù)據(jù)分析選項(xiàng)，可在工具

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

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

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

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

第二步：?jiǎn)螕舫闃舆x項(xiàng)，彈出抽樣對(duì)話框。如圖13-3

圖13-3抽樣對(duì)話框

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

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

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

框。

第四步：在抽樣方法項(xiàng)下，有周期和隨機(jī)兩種抽樣模式：

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

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

12個(gè)，則在“間隔”框中輸入8。

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

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

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

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

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

查?？梢钥闯觯死木幪?hào)輸入方法，只適用于等距抽樣和純隨機(jī)抽樣。

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

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

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

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

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

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

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

如圖13-5所小:

然后按以下步驟操作。

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

擇直方圖，打開(kāi)直方圖對(duì)話框。如圖13-6所示:

直方圖

圖13-6直方圖對(duì)話框

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

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

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

第三步：選擇輸出選項(xiàng)，可選擇輸入?yún)^(qū)域、新工作表組或新工作薄。我們?cè)?/p>

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

輸出區(qū)域的左上角），這里我們推薦只輸入一個(gè)單元格（本例為A7）,因?yàn)槲覀?/p>

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

第四步：選擇圖表輸出，可以得到直方圖；選擇累計(jì)百分率，系統(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)注意，上圖實(shí)際上是一個(gè)條形圖，而不是直方圖，若要把它變成直方圖,

可按如下操作：

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

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

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

在對(duì)話框中選擇選項(xiàng)標(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)計(jì)圖

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

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

圖的作法大同小異。

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

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

展*

f二

?

山

奈

三

三

m；

*°7

>11rtl

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

按以下步驟可作出餅圖：

第一步：選中某一單元格，單擊插入菜單，選擇圖表選項(xiàng)，彈出圖表向?qū)?duì)

話框。如圖13-11：

圖13-11圖表向?qū)?duì)話框

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

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

所示：

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

K加L主在.r-萬(wàn)QBJ>

e加<XJ>

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

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

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

13-13所示的餅圖：

□石家莊市

■唐山市

□秦皇島市

口保定市

■邯鄲市

□邢臺(tái)市

■滄州|仃

□張家口市

圖13-13餅圖

實(shí)驗(yàn)二用EXCEL計(jì)算描述統(tǒng)計(jì)量

實(shí)驗(yàn)?zāi)康模河肊XCEL計(jì)算描述統(tǒng)計(jì)量

實(shí)驗(yàn)步驟：

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

方法。

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

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

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

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

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

（一）眾數(shù)

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

月工資如下：

15601340160014101590141016101570171015501490

16901380168014701530156012501560135015601510

15501460155015701980161015101440

用函數(shù)方法求眾數(shù)，應(yīng)先將30個(gè)人的工資數(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ì)工具量的使用

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

作：

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

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

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

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

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

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

出樣本中第K個(gè)大值和第K個(gè)小值。

第三步：?jiǎn)螕舸_定，可得輸出結(jié)果，如圖13T5所示：

?UMNlWri?TMlTlWBOV4mJM

圖13-15描述統(tǒng)計(jì)輸出結(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上是極差，或全距。（可見(jiàn)中文版的翻譯有問(wèn)題）

實(shí)驗(yàn)三用EXCEL進(jìn)行時(shí)間序列分析

實(shí)驗(yàn)?zāi)康模河肊XCEL進(jìn)行時(shí)間序列分析

實(shí)驗(yàn)步驟：

一、測(cè)定增長(zhǎng)量和平均增長(zhǎng)量

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

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

計(jì)算步驟如下：

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

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

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

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

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

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

按回車鍵，即可得到平均增長(zhǎng)量。

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

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

發(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計(jì)算發(fā)展速度和平均發(fā)展速度資料及結(jié)果

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

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

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

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

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

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

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

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

三、計(jì)算長(zhǎng)期趨勢(shì)

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

計(jì)算長(zhǎng)期趨勢(shì)。如圖13-18

圖13-18用EXCEL計(jì)算長(zhǎng)期趨勢(shì)資料及結(jié)果

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

第二步：計(jì)算三項(xiàng)移動(dòng)平均：在C3中輸入“=（B2+B3+B4）/3"，并用鼠

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

第三步：計(jì)算四項(xiàng)移動(dòng)平均：在D4中輸入“=SUM（B2：B5）/4"，并用鼠

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

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

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

四、計(jì)算季節(jié)變動(dòng)

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

趨勢(shì)剔除法測(cè)定季節(jié)變動(dòng)。如圖13-19

CAPSNUM

圖13-19用EXCEL計(jì)算季節(jié)變動(dòng)資料

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

售收入。

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

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

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

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

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

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

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

成四列。

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

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

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

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

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

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

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計(jì)算季節(jié)變動(dòng)結(jié)果

實(shí)驗(yàn)四用EXCEL進(jìn)行指數(shù)分析

實(shí)驗(yàn)?zāi)康模河肊XCEL進(jìn)行指數(shù)分析

實(shí)驗(yàn)步驟：

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

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

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

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

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

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

計(jì)算步驟：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

L0

鬻

生

Il

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

計(jì)算步驟:

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

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

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

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

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

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

三、用Excel進(jìn)行因素分析

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

i

n

w

￡

i?

i§*

!?

!:

同

一

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

進(jìn)行因素分析的計(jì)算步驟如下：

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

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

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

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

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

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

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

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

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

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

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

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

實(shí)驗(yàn)五用EXCEL進(jìn)行相關(guān)與回歸分析

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

實(shí)驗(yàn)步驟：

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

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

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

117153

216756

317764

415449

516955

617566

716352

815247

917258

1016050

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

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

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

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

關(guān)分析宏。

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

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

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

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

第一步：?jiǎn)螕羧我粋€(gè)空白單元格，單擊插入菜單，選擇函數(shù)選項(xiàng)，打開(kāi)粘貼

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

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

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

話框下方顯示出計(jì)算結(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ū)域.

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

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

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

第一步：?jiǎn)螕艄ぞ卟藛?，選擇數(shù)據(jù)分析選項(xiàng)，在數(shù)據(jù)分析選項(xiàng)中選擇相關(guān)系

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

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

第二步：在輸入?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ù)計(jì)算的結(jié)果完全相同。

二、用EXCEL進(jìn)行回歸分析

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

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

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

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

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

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

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

TREND計(jì)算線性回歸線的趨勢(shì)值

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

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

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

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

析。

第一步：?jiǎn)螕艄ぞ卟藛?，選擇數(shù)據(jù)分析選項(xiàng)，出現(xiàn)數(shù)據(jù)分析對(duì)話框，在分析

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

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

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

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

示：

圖13-29回歸對(duì)話框

第四步：?jiǎn)螕舸_定按鈕，得回歸分析結(jié)果如圖13-30所示

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

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

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

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

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

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

實(shí)驗(yàn)六用統(tǒng)計(jì)軟件進(jìn)行預(yù)測(cè)

實(shí)驗(yàn)?zāi)康模河肊XCEL進(jìn)行預(yù)測(cè)

實(shí)驗(yàn)步驟：

【例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

一、用移動(dòng)平均法進(jìn)行預(yù)測(cè)

具體步驟:

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

W—

6

7

w

i?

ll

14

15

i?

il

2no

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

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

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

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

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

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

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

13-33移動(dòng)平均對(duì)話框

第四步：?jiǎn)螕簟按_定”按鈕，便可得到移動(dòng)平均結(jié)果，如圖13-34所示：

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

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

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

二、用指數(shù)平滑法進(jìn)行預(yù)測(cè):

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

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

所示的對(duì)話框：

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

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

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

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

第四步：?jiǎn)螕簟按_定”按鈕，即可得到指數(shù)平滑結(jié)果，如圖13-37所示:

903

-

2S

?0

9085

0250

?6

93125U2O875?l

8江.116630208982

江18102986G9

29102196B4

d.

39B02BB639

Qa.

32Rn178777

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

三、趨勢(shì)預(yù)測(cè)法進(jìn)行預(yù)測(cè)

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

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

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

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

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

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

第六步：?jiǎn)螕舸_定按鈕，出現(xiàn)預(yù)測(cè)對(duì)話框，在x中輸入12,在know-y，s中

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

=9.396545455

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

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

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

圖13-40FORCAST對(duì)話框

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

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

實(shí)驗(yàn)七用EXCEL進(jìn)行參數(shù)估計(jì)和假設(shè)檢驗(yàn)

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

實(shí)驗(yàn)步驟：

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

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

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

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

3826304218302526344420352426344818

2846193036422432453621472628314245

36242827323647532224324626

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

計(jì)算方法如下：

第一步：把數(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