Excel在統(tǒng)計(jì)方面的應(yīng)用技巧

唐義勇 永州市統(tǒng)計(jì)局

在實(shí)際的數(shù)據(jù)處理中，往往涉及到數(shù)據(jù)的統(tǒng)計(jì)，數(shù)據(jù)統(tǒng)計(jì)時(shí)常常對(duì)應(yīng)線性回歸與相關(guān)性分析，當(dāng)前人們也更傾向于使用相應(yīng)的工具軟件或編程系統(tǒng)進(jìn)行數(shù)據(jù)的大規(guī)模高效率統(tǒng)計(jì)，既追求數(shù)據(jù)統(tǒng)計(jì)的速度，也追求統(tǒng)計(jì)的質(zhì)量。但無論是使用工具軟件還是編程進(jìn)行數(shù)據(jù)統(tǒng)計(jì)，不僅操作繁瑣，且對(duì)操作人員技能要求較高，應(yīng)用效果也不甚理想。而Excel作為一款優(yōu)秀的數(shù)據(jù)分析軟件，支持?jǐn)?shù)據(jù)的統(tǒng)計(jì)分析與處理，因此，總結(jié)其在數(shù)據(jù)統(tǒng)計(jì)分析方面的應(yīng)用技巧非常必要。

一、Excel的常用統(tǒng)計(jì)函數(shù)介紹

AVERAGE工作表函數(shù)是返回參數(shù)的算術(shù)平均值，對(duì)應(yīng)的語法格式為AV-ERAGE(n1，n2，…，n30)；CORREL工作表函數(shù)，其對(duì)應(yīng)的是返回兩個(gè)數(shù)值單元格區(qū)域之間的相關(guān)關(guān)系，對(duì)應(yīng)的語法格式為CORREL(array1，array2)，其中Array1和Array2參數(shù)分別表示第一和第二單元格區(qū)域；COUNTIF工作表函數(shù)，對(duì)應(yīng)的是給定區(qū)域內(nèi)符合條件的單元格的數(shù)目，對(duì)應(yīng)的語法格式為COUNTIF(range，criteria)，Range參數(shù)代表單元格區(qū)域[1]，而Criteria是給定條件，工作表函數(shù)主要是使用最小二乘法進(jìn)行最佳直線擬合，返回直線數(shù)組的描述，也有一定的語法格式為LINEST(y's，x's，const，stats)，對(duì)應(yīng)的參數(shù)是y's和x's。MAX、MIN工作表函數(shù)，分別對(duì)應(yīng)數(shù)據(jù)集中的最大值、最小值，對(duì)應(yīng)的語法格式為MAX(n1，n2，…，n30)和MIN(n1，n2，…，n30)，RANK工作表函數(shù)反映了數(shù)值在一組數(shù)值中的排名，對(duì)應(yīng)的語法格式為RANK(number，ref，order)。STDEV、STDEVP工作表函數(shù)，前者對(duì)應(yīng)的是樣本的標(biāo)準(zhǔn)偏差，而后者對(duì)應(yīng)的是樣本總體標(biāo)準(zhǔn)偏差，對(duì)應(yīng)的語法格式為STDEV(n1，n2，…)和STDEVP(n1，n2，…)，Number1，Number2，…為1到30個(gè)樣本值[2]。Excel作為友好的統(tǒng)計(jì)軟件，操作界面友好，操作簡(jiǎn)單，比較容易上手，其數(shù)據(jù)處理功能十分強(qiáng)大，即使面對(duì)復(fù)雜的數(shù)據(jù)多重檢索標(biāo)準(zhǔn)，也能進(jìn)行數(shù)據(jù)的引用應(yīng)用，并進(jìn)行數(shù)據(jù)的高效高質(zhì)處理。

二、Excel進(jìn)行統(tǒng)計(jì)分析的技巧說明

(一)用Excel整理統(tǒng)計(jì)數(shù)據(jù)

使用Excel統(tǒng)計(jì)分組，一種對(duì)應(yīng)是FREQUENCY函數(shù)，另一種是利用數(shù)據(jù)分析中的“直方圖”工具，用函數(shù)編制頻數(shù)表，先明確每組的上限值，這是編制頻數(shù)表的關(guān)鍵一步，明確了上限值就相當(dāng)于明確了組的組距和組線[3]，如果將上限值對(duì)應(yīng)輸入為900/1000/1100/1300/1500，如果將上線值輸入到A7：A11，選取結(jié)果存放的單元格區(qū)域B7：B11，于編輯欄輸入公式=frequency(A1:J5，A7:A11)就可獲得各組相應(yīng)的頻數(shù)。采用數(shù)據(jù)分析工具進(jìn)行頻數(shù)分布表的制作，需要于工具菜單中選擇數(shù)據(jù)分析選項(xiàng)，找到對(duì)話框中的直方圖工具，打開對(duì)話框輸入，在接收區(qū)域輸入$A$1:$J$5，接收區(qū)域是分組標(biāo)志所在的區(qū)域。選擇輸入項(xiàng)，可以自主選擇輸出區(qū)域，可以是新工作表或工作簿或者圖表輸出，從而得到直方圖。選擇累計(jì)百分率，則直方圖上伴有累計(jì)頻率折線，若選擇“柏拉圖”，可以獲得按降序排列的直方圖，按確定按鈕能得到“草圖”，我們可以在“草圖”上作進(jìn)一步的修改[4]，如用鼠標(biāo)單擊任意直線，右鍵單擊在彈出的快捷菜單中選數(shù)據(jù)格式，根據(jù)對(duì)話框的內(nèi)容選擇相對(duì)應(yīng)的標(biāo)簽，進(jìn)行直方圖顏色的修改、標(biāo)題的清除。

(二)用Excel計(jì)算描述統(tǒng)計(jì)量

眾數(shù)、中位數(shù)、算術(shù)平均數(shù)、調(diào)和平均數(shù)、標(biāo)準(zhǔn)差等都屬于描述統(tǒng)計(jì)量，使用Excel進(jìn)行這些統(tǒng)計(jì)量的統(tǒng)計(jì)時(shí)分組的資料用函數(shù)計(jì)算，未分組的材料用公式計(jì)算。以某城市居民家庭收入表為例，見表1，求眾數(shù)[5]。先創(chuàng)建Excel文件，在B11中輸入數(shù)據(jù)，輸入公式=20000+((260-240)/((260-240)+(260-140)))×5000計(jì)算即可得出結(jié)果。在計(jì)算中位數(shù)時(shí)則利用上限公式，于B11單元格中輸入公式=20000-(500-488)/240×5000即可得出結(jié)論。用數(shù)據(jù)分析工具描述統(tǒng)計(jì)量，先要把數(shù)據(jù)輸入到單元格中，于工具菜單欄中選擇數(shù)據(jù)分析這一選項(xiàng)，于對(duì)話框中選擇描述統(tǒng)計(jì)，打開統(tǒng)計(jì)對(duì)話框于需要輸入的區(qū)域中輸入，選擇匯總統(tǒng)計(jì)就可獲得統(tǒng)計(jì)量。

表1 某城市居民家庭收入表

(三)用Excel進(jìn)行區(qū)間估計(jì)

使用Excel進(jìn)行區(qū)間估計(jì)，主要是根據(jù)區(qū)間估計(jì)的要求逐步進(jìn)行，先計(jì)算樣本均值，也就是點(diǎn)估計(jì)值，接著計(jì)算樣本標(biāo)差，結(jié)合樣本量構(gòu)造抽樣誤差，再結(jié)合置信度構(gòu)造極限誤差，樣本均值加上減去極限誤差能得到區(qū)間估計(jì)上下限。

(四)用Excel進(jìn)行指數(shù)分析

首先是計(jì)算總指數(shù)，這是研究經(jīng)濟(jì)現(xiàn)象數(shù)量變動(dòng)情況的常用統(tǒng)計(jì)分析方法。指數(shù)分析有綜合指數(shù)分析和平均指數(shù)分析的區(qū)別，以某企業(yè)三種產(chǎn)品的生產(chǎn)情況為例，見表2，以基期價(jià)格p0作為同度量因素來進(jìn)行生產(chǎn)指數(shù)的計(jì)算。先進(jìn)行p0q0的計(jì)算，具體是在G2區(qū)域中輸入并復(fù)制公式到區(qū)域G3：G4計(jì)算，然后計(jì)算各個(gè)p0q1，在H2中輸入：“=E2*D2”，并用鼠標(biāo)拖拽將公式復(fù)制到H3：H4區(qū)域。接下來計(jì)算∑p0q0 和∑p0q1，選定G2：G4區(qū)域，單擊工具欄上的“∑”按鈕，在H5出現(xiàn)該列的求和值。最后計(jì)算生產(chǎn)量綜合指數(shù)Iq=p0q1/p0q0，在C6中輸入“=H5/G5”便可得到生產(chǎn)量綜合指數(shù)。

表2 某企業(yè)三種產(chǎn)品的生產(chǎn)情況表

三、以學(xué)生成績?yōu)槔M(jìn)行Excel使用技巧的說明

(一)VLOOKUP函數(shù)查找對(duì)應(yīng)班級(jí)成績

在教學(xué)中，教師需要統(tǒng)計(jì)自己所教班級(jí)學(xué)生的成績，并將成績與全院所有班級(jí)做對(duì)比，明確自身與其他院系教師、學(xué)生的差異。例如，在某場(chǎng)模擬考試成績之后，某教師收到了該年級(jí)1到8班全體學(xué)生?？汲煽兛偙?，數(shù)據(jù)表結(jié)構(gòu)和記錄如下頁表3所示。學(xué)生模考成績表可以顯示學(xué)生學(xué)號(hào)、對(duì)應(yīng)分值、總成績，但沒有班級(jí)，無法直觀看出學(xué)生的成績變化，可以將四個(gè)工作表的?？紨?shù)據(jù)整合在一個(gè)匯總表中，于匯總表中，得出教師所在的班級(jí)1班所有學(xué)生每次考試對(duì)應(yīng)的成績。具體操作是找到工作表共同的字段“學(xué)號(hào)”，使用Excel表中的VLOOKUP函數(shù)在E3單元格中輸入公式：=VLOOKUP(A3，’?？?’!$B$2：$P$277，15，F(xiàn)ALSE)，計(jì)算出第一位學(xué)生?？家坏某煽?，逐漸下拉填充，計(jì)算出其他所有學(xué)生?？家坏某煽?。同樣完成學(xué)生其余三次模考的成績，使用平均函數(shù)進(jìn)行1班和所有班級(jí)學(xué)院學(xué)生四次模考的平均成績的計(jì)算，進(jìn)行數(shù)據(jù)的對(duì)比，從而探明教師的教學(xué)水平。

表3 年級(jí)1到8班全體學(xué)生?？汲煽兛偙斫厝?/p>

(二)直方圖顯示學(xué)生成績

表4 年級(jí)1班全體學(xué)生?？妓某煽冎狈綀D

通過直方圖可以準(zhǔn)確了解一組數(shù)據(jù)的分布特征，了解數(shù)據(jù)是否存在對(duì)稱分布的屬性，查明數(shù)據(jù)異常，從而評(píng)估學(xué)生整體學(xué)習(xí)情況。例如，基于匯總表可以了解1班所有學(xué)生?？妓牡某煽?，教師創(chuàng)建直方圖，進(jìn)行直方圖分析，明確該班此次考試成績整體情況。具體操作是選取模考四成績列區(qū)域，繪制圖表類型中的直方圖，并設(shè)置好圖表標(biāo)題、坐標(biāo)軸標(biāo)題、圖例、箱寬度等，將箱寬度設(shè)置為5，由此看到1班?？妓牡某煽兎险龖B(tài)分布，且主要集中在34到44分之間，而及格率按45分及以上來推算的話，僅有7人合格，因此教師接下來的教學(xué)重點(diǎn)是重點(diǎn)關(guān)注34到44這部分分?jǐn)?shù)段的學(xué)生。

(三)預(yù)測(cè)成績趨勢(shì)變化

使用Excel進(jìn)行數(shù)據(jù)的統(tǒng)計(jì)分析，也可以用于預(yù)測(cè)學(xué)生的成績變化趨勢(shì)，可以根據(jù)學(xué)生歷史考試成績創(chuàng)建預(yù)測(cè)，去推斷學(xué)生下幾次考試的通過情況、波動(dòng)幅度，以指導(dǎo)教師做好教學(xué)調(diào)整，具體來說，可以選取“日期”和“通過率”兩列數(shù)據(jù)來創(chuàng)建預(yù)測(cè)工作表，生成預(yù)測(cè)數(shù)據(jù)和圖表，其不僅有歷史數(shù)據(jù)，且有預(yù)測(cè)數(shù)據(jù)，還伴有預(yù)測(cè)圖。教師可以一目了然地了解到學(xué)生接下來考試的通過率走勢(shì)，并有上限和下限范圍的標(biāo)注。一般來說，歷史數(shù)據(jù)越多，預(yù)測(cè)趨勢(shì)結(jié)果就越準(zhǔn)確。

四、結(jié)語

本文基于實(shí)際案例，具體介紹了Excel在社會(huì)學(xué)統(tǒng)計(jì)分析、學(xué)生成績分析等方面的應(yīng)用優(yōu)勢(shì)，也論述了其具體的應(yīng)用思路和應(yīng)用技巧，對(duì)于利用Excel工具進(jìn)行數(shù)據(jù)統(tǒng)計(jì)分析與應(yīng)用具有指導(dǎo)意義。Excel版本較多，Excel在實(shí)際的應(yīng)用中應(yīng)結(jié)合具體情況進(jìn)行數(shù)據(jù)的統(tǒng)計(jì)與處理，使其強(qiáng)大的統(tǒng)計(jì)功能的發(fā)揮，以帶來數(shù)據(jù)統(tǒng)計(jì)的高效與高質(zhì)?！?/p>