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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

表3 年級1到8班全體學(xué)生模考成績總表截取

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

表4 年級1班全體學(xué)生模考四成績直方圖

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

(三)預(yù)測成績趨勢變化

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

四、結(jié)語

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