Excel在統計方面的應用技巧

唐義勇 永州市統計局

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

一、Excel的常用統計函數介紹

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

二、Excel進行統計分析的技巧說明

(一)用Excel整理統計數據

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

(二)用Excel計算描述統計量

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

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

(三)用Excel進行區間估計

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

(四)用Excel進行指數分析

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

表2 某企業三種產品的生產情況表

三、以學生成績為例進行Excel使用技巧的說明

(一)VLOOKUP函數查找對應班級成績

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

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

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

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

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

(三)預測成績趨勢變化

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

四、結語

本文基于實際案例，具體介紹了Excel在社會學統計分析、學生成績分析等方面的應用優勢，也論述了其具體的應用思路和應用技巧，對于利用Excel工具進行數據統計分析與應用具有指導意義。Excel版本較多，Excel在實際的應用中應結合具體情況進行數據的統計與處理，使其強大的統計功能的發揮，以帶來數據統計的高效與高質。■