開(kāi)放大學(xué)統(tǒng)計(jì)課程融入Excel實(shí)例的教學(xué)實(shí)踐探析

吳集林，陳燕平，姚家育，謝凡

（佛山開(kāi)放大學(xué)，廣東 佛山 528000)

0 引言

統(tǒng)計(jì)基礎(chǔ)[1]或統(tǒng)計(jì)學(xué)概論[2]是開(kāi)放大學(xué)經(jīng)管類(lèi)專(zhuān)業(yè)的必修課，是一門(mén)闡述搜集、整理、分析統(tǒng)計(jì)數(shù)據(jù)的方法論的科學(xué)，目的是探索數(shù)據(jù)的內(nèi)在規(guī)律性，運(yùn)用統(tǒng)計(jì)的思維去發(fā)現(xiàn)數(shù)據(jù)、分析數(shù)據(jù)。通過(guò)本課程的學(xué)習(xí)，學(xué)生掌握統(tǒng)計(jì)學(xué)的有關(guān)理論和方法，具有基本的統(tǒng)計(jì)思維，掌握搜集數(shù)據(jù)、整理數(shù)據(jù)、分析數(shù)據(jù)的方法。教材各章節(jié)的例子，是基于手工或計(jì)算器的工具來(lái)進(jìn)行運(yùn)算的，如果基于手工計(jì)算或統(tǒng)計(jì)，要耗費(fèi)比較多的時(shí)間，并且容易出錯(cuò)。本來(lái)統(tǒng)計(jì)軟件有SPSS、R語(yǔ)言、SAS等軟件，但對(duì)于開(kāi)放大學(xué)經(jīng)管類(lèi)的學(xué)生來(lái)說(shuō)，并沒(méi)有開(kāi)設(shè)過(guò)統(tǒng)計(jì)軟件的課程，計(jì)算機(jī)知識(shí)主要是學(xué)了計(jì)算機(jī)應(yīng)用基礎(chǔ)（含Word、Excel、PPT 等模塊），沒(méi)有其他的統(tǒng)計(jì)軟件基礎(chǔ)。其實(shí)，對(duì)于統(tǒng)計(jì)基礎(chǔ)的實(shí)例，完全可以利用Excel實(shí)現(xiàn)。關(guān)于Excel在統(tǒng)計(jì)分析中的應(yīng)用已有文獻(xiàn)[3],但是文獻(xiàn)主要是針對(duì)統(tǒng)計(jì)專(zhuān)業(yè)的學(xué)生而編寫(xiě)，難度相對(duì)大一些，本文根據(jù)開(kāi)放大學(xué)經(jīng)管類(lèi)學(xué)生的特點(diǎn)，結(jié)合統(tǒng)計(jì)基礎(chǔ)課程內(nèi)容，有針對(duì)性地融入統(tǒng)計(jì)基礎(chǔ)課程的實(shí)例的Excel實(shí)現(xiàn)。

1 統(tǒng)計(jì)分布數(shù)列

統(tǒng)計(jì)分布數(shù)列，也稱(chēng)分配數(shù)列、次數(shù)分布，是在統(tǒng)計(jì)分組的基礎(chǔ)上，將總體的所有單位按組進(jìn)行歸類(lèi)整理，形成總體中各單位在各組間的分布。體現(xiàn)形式的這樣一個(gè)表格的內(nèi)容一般是三列，第一列指的是總體按某標(biāo)志值進(jìn)行分組，第二列是各組分配的單位數(shù)（也稱(chēng)次數(shù)或頻數(shù)），第三列是各組所占的比重（也稱(chēng)頻率）。根據(jù)教材中做統(tǒng)計(jì)分布數(shù)列的過(guò)程，各組的單位數(shù)是在排序的基礎(chǔ)上，一個(gè)一個(gè)手工統(tǒng)計(jì)的結(jié)果，其實(shí)在具體操作的時(shí)候，可用Excel實(shí)現(xiàn)。

首先介紹下面實(shí)例要使用的Excel函數(shù)countif的用法，

該函數(shù)的語(yǔ)法規(guī)則如下：countif(range，criteria)

第一個(gè)參數(shù)表示范圍，第二參數(shù)表示條件，函數(shù)的功能是對(duì)指定區(qū)域中符合指定條件的單元格進(jìn)行計(jì)數(shù)。

案例1：已知某班40 名學(xué)生的數(shù)學(xué)考試成績(jī)，要求完成成績(jī)的統(tǒng)計(jì)分布數(shù)列。

用Excel操作步驟如下：

1) 建一個(gè)Excel文件，在A列輸入40個(gè)學(xué)生的成績(jī)，輸入完成后將成績(jī)按升序排列，發(fā)現(xiàn)60分以下和90分以上的比較少，絕大部分在70至80之間，可將成績(jī)分成5個(gè)組：60分以下，60～70，70～80，80～90，90分以上；

2) 在E列至G 做一個(gè)表，內(nèi)容如下：

圖1 學(xué)生成績(jī)表的分布數(shù)列

3) 其中F 列和G 列的數(shù)據(jù)通過(guò)公式來(lái)實(shí)現(xiàn)，在單元格格F3 輸入函數(shù)=countif(A:A,"<60")；在單元格格F4 輸入函數(shù)=countif(A:A,"<70")-countif(A:A,"<60")；在單元格格F5 輸入函數(shù)=countif(A:A,"<80")-countif(A:A,"<70")；在單元格格F6 輸入函數(shù)=countif(A:A,"<90")-countif(A:A,"<80")；在單元格格F7 輸入函數(shù)=countif(A:A,"<100")-countif(A:A,"<80")。

4) 在G3 輸入公式=F3/40，并復(fù)制到G4、G5、G4、G7等 單元格格，G列單元格格格式為百分比。

本例的重點(diǎn)是掌握條件統(tǒng)計(jì)函數(shù)countif 統(tǒng)計(jì)某范圍中滿(mǎn)足某條件的數(shù)據(jù)的次數(shù)。

2 平均指標(biāo)與變異指標(biāo)

平均指標(biāo)，又稱(chēng)統(tǒng)計(jì)平均數(shù)，主要用于反映社會(huì)經(jīng)濟(jì)現(xiàn)象某一數(shù)量標(biāo)志在一定時(shí)間地點(diǎn)條件下達(dá)到的一般水平，平均指標(biāo)有算術(shù)平均數(shù)、眾數(shù)、中位數(shù)，其中算術(shù)平均數(shù)指的是總體標(biāo)志總量除以總體單位總量的結(jié)果。眾數(shù)是總體某一標(biāo)志值出現(xiàn)次數(shù)最多的那個(gè)標(biāo)志值。中位數(shù)指的是總體中各單位標(biāo)志值按大小順序排列，處于中點(diǎn)位置的標(biāo)志值。變異指標(biāo)反映總體某一標(biāo)志值的變化程度，標(biāo)準(zhǔn)差是常用的變異指標(biāo)，計(jì)算公式為，如果數(shù)據(jù)比較多，用這個(gè)公式來(lái)算，計(jì)算量也挺大的。

首先介紹下面實(shí)例要使用的幾個(gè)函數(shù)的用法：

第一個(gè)函數(shù)average，語(yǔ)法規(guī)則為average (range),得到某范圍中的數(shù)的平均數(shù)；

第二個(gè)函數(shù)mode，語(yǔ)法規(guī)則為mode(range),得到某范圍中的數(shù)的眾數(shù)；

第三個(gè)函數(shù)median，語(yǔ)法規(guī)則為median (range),得到某范圍中的數(shù)的中位數(shù)；

第四個(gè)函數(shù)stdevp，語(yǔ)法規(guī)則為stdevp (range),得到某范圍中的數(shù)的標(biāo)準(zhǔn)差。

案例2：已知某班40 名學(xué)生的數(shù)學(xué)考試成績(jī)，計(jì)算算術(shù)平均數(shù)、眾數(shù)、中位數(shù)、標(biāo)準(zhǔn)差等指標(biāo)。

用Excel解題操作步驟如下：

1) 在A列輸入40名學(xué)生的成績(jī)；

2) 在D2 輸入公式=average(A2:A41)得到平均成績(jī)，D3 輸入公式=mode(A2:A41)得到眾數(shù)，D4 輸入公式=median(A2:A41)得到中位數(shù)，D5 輸入公式=stdevp(A2:A41)得到標(biāo)準(zhǔn)差。

圖2 學(xué)生成績(jī)的平均指標(biāo)與標(biāo)準(zhǔn)差

本例的重點(diǎn)是掌握眾數(shù)函數(shù)mode、中位數(shù)函數(shù)median、平均數(shù)函數(shù)average及標(biāo)準(zhǔn)差函stdevp的使用。

3 相關(guān)系數(shù)與回歸分析方程

相關(guān)系數(shù)指的是在線性相關(guān)條件下，說(shuō)明兩個(gè)現(xiàn)象之間關(guān)系密切程度的統(tǒng)計(jì)分析指標(biāo)，通常用“r”表示，

回歸分析是在相關(guān)分析的基礎(chǔ)上，根據(jù)其變量之間的數(shù)量變化規(guī)律，運(yùn)用一個(gè)相關(guān)的數(shù)學(xué)模型近似地表示變量間的平均變化關(guān)系，并進(jìn)行推算和預(yù)測(cè)的一種統(tǒng)計(jì)分析方法。用直線方程來(lái)表示兩個(gè)變量之間的變動(dòng)關(guān)系，并進(jìn)行推算和預(yù)測(cè)。一元線性回歸方程：yc=a+bx，其中，不管是相關(guān)系數(shù)還是回歸直線方程，如果用手工計(jì)算，計(jì)算量都挺大。但是利用Excel中的相關(guān)系數(shù)的函數(shù)和散點(diǎn)圖表，可以很迅速地求得結(jié)果。

首先介紹下面實(shí)例要使用的Excel知識(shí)：

首先是理解函數(shù)correl，語(yǔ)法規(guī)則為correl (array1,array2)，得到兩個(gè)數(shù)組的相關(guān)系數(shù)；

第二是通過(guò)插入散點(diǎn)圖，并通過(guò)設(shè)置趨勢(shì)線來(lái)獲得回歸直線的圖像和直線方程。

案例3：有10 個(gè)家庭的月收入與消費(fèi)支出如下，求月收入與消費(fèi)支出的相關(guān)系數(shù)，并擬合消費(fèi)支出對(duì)家庭月收入的回歸直線方程。

圖3 月收入與消費(fèi)支出的相關(guān)系數(shù)與回歸直線圖

用Excel解題操作步驟如下：

1) 在B 至D 列輸入家庭月收入和消費(fèi)支出的數(shù)據(jù)；

2) 在D15輸入相關(guān)系數(shù)的公式=correl(C4:C13,D4:D13)，可迅速求得相關(guān)系數(shù)99.06%；

3) 先選擇區(qū)域C4:D13,插入散點(diǎn)圖，圖表布局選擇“布局3”，選擇其中一個(gè)具體的點(diǎn)，右擊，設(shè)置趨勢(shì)線的格式，勾選“顯示公式”和“R平方值”。

圖4 設(shè)置趨線格式

于是圖表區(qū)域中顯示了回歸直線方程和R 的平方，R的平方指的是相關(guān)系數(shù)的平方。

圖5 回歸直線與相關(guān)系數(shù)平方

本例的重點(diǎn)是掌握相關(guān)系數(shù)correl 以及利用散點(diǎn)圖中的趨勢(shì)線得到回歸方程。

4 動(dòng)態(tài)數(shù)列

教材的第八章是動(dòng)態(tài)數(shù)列，講一系列動(dòng)態(tài)指標(biāo)的計(jì)算，比如發(fā)展水平、平均發(fā)展水平、逐期增長(zhǎng)量、累計(jì)增長(zhǎng)量、環(huán)比發(fā)展速度、定基發(fā)展速度、環(huán)比增長(zhǎng)速度、定基增長(zhǎng)速度、平均發(fā)展速度。我們根據(jù)一個(gè)經(jīng)濟(jì)量的動(dòng)態(tài)數(shù)列，利用Excel公式，可以迅速地計(jì)算一系列指標(biāo)的數(shù)據(jù)。

案例4：根據(jù)我國(guó)1992年至2010年的國(guó)內(nèi)生產(chǎn)總值，計(jì)算一系列的統(tǒng)計(jì)動(dòng)態(tài)數(shù)列的指標(biāo)，年份和產(chǎn)值為下表中的A列和B列的數(shù)據(jù)。

圖6 統(tǒng)計(jì)指標(biāo)動(dòng)態(tài)數(shù)列

用Excel解題操作步驟如下：

1) 先從A列和B列輸入年份和產(chǎn)值；

2) 求平均發(fā)展水平，在C21輸入公式=average(B3:B21)；

3) 求逐期增長(zhǎng)量 在D4 單元格格輸入=B4-B3 ，并復(fù)制到本列的其他單元格格，因?yàn)槭窍鄬?duì)引用，被減數(shù)與減數(shù)引用的單元格會(huì)發(fā)生相應(yīng)的變化；

4) 求累積增長(zhǎng)量 在E4輸入公式=B4-$B$3，并復(fù)制到本列的其他單元格格，因?yàn)楸粶p數(shù)是固定的，所以要用絕對(duì)引用；

5) 求環(huán)比發(fā)展速度，在F4輸入公式=B4/B3，并復(fù)制到本列的其他單元格。因?yàn)槭窍鄬?duì)引用，被除數(shù)與除數(shù)引用的單元格會(huì)發(fā)生相應(yīng)的變化；

6) 求定基發(fā)展速度，在G4輸入公式=B4/$B$3，并復(fù)制到本列的其他單元格，因?yàn)楸怀龜?shù)固定，這里使用絕對(duì)引用；

7) 求環(huán)比增長(zhǎng)速度，在H4輸入公式=B4/B3-1，并復(fù)制到本列的其他單元格；

8) 求定基增長(zhǎng)速度，在I4 輸入公式=B4/$B$3-1，并復(fù)制到本列的其他單元格；

9) 求平均發(fā)展速度，在J21輸入公式=(B21/$B$3)^(1/18)；

10) 求平均增長(zhǎng)速度，在K21輸入公式=J21-1。

本例的重點(diǎn)是在理解這一系列指標(biāo)的概念，設(shè)置公式時(shí)先設(shè)置好最上的一個(gè)，注意公式引用的單元格是絕對(duì)引用還是相對(duì)引用，然后將公式復(fù)制得到一系列指標(biāo)的結(jié)果，注意公式中哪些地方是絕對(duì)引用，哪些地方是相對(duì)引用，掌握絕對(duì)引用和相對(duì)引用的技巧[4]。

5 結(jié)論

本文根據(jù)廣東開(kāi)放大學(xué)專(zhuān)科統(tǒng)計(jì)基礎(chǔ)或國(guó)家開(kāi)放大學(xué)專(zhuān)科統(tǒng)計(jì)學(xué)概論中的章節(jié)內(nèi)容，對(duì)于統(tǒng)計(jì)分布數(shù)列、綜合指標(biāo)、相關(guān)和回歸分析、動(dòng)態(tài)數(shù)列中的案例，用Excel中的公式或圖表來(lái)實(shí)現(xiàn)，使得教學(xué)的重點(diǎn)放到主要理解概念和公式，而不是將大量的時(shí)間用在統(tǒng)計(jì)實(shí)例的演算過(guò)程，計(jì)算結(jié)果的求得用Excel 來(lái)實(shí)現(xiàn)，能大大簡(jiǎn)化計(jì)算過(guò)程的煩瑣教學(xué)演示，這樣既加深了對(duì)概念的理解，又提高了同學(xué)們利用Excel 解決實(shí)際問(wèn)題的能力。