基于Excel數據分析處理功能統計學生成績

　　摘要： 每次考試結束需對學生成績進行統計分析，合理巧妙運用Excel數據分析處理功能，能使許多復雜的問題簡單化，并可以大大提高工作效率。本文針對學生成績表的多條件統計平均分、及格人數和及格率等需求，給出公式或函數法和數據透視表法兩種解決方案，供教學和教師統計分析成績時參考。
　　關鍵詞： 公式或函數數據透視表多條件及格率平均分
　　
　　引言
　　每次全國普通高校非計算機專業計算機聯合考試（一級）考試閱卷結束后，由自治區將成績以Excel電子表格形式下發到各學校（以下簡稱成績表）。成績表的表頭各字段名如下：
　　
　　
　　教師需對成績表進行匯總統計與分析，了解全校各系、各年級、各專業的平均分、及格人數和及格率等情況，便于修訂教學計劃。
　　針對學生成績表的多條件統計分析需求給出公式或函數法和數據透視表法兩種解決方案。
　　1.公式或函數法
　　1.1 SUMPRODUCT函數
　　SUMPRODUCT函數是在給定的幾組數組中，將數組間對應的元素相乘，并返回乘積之和。
　　語法SUMPRODUCT（array1，［array2］， ［array3］， ...）
　　其中，數組參數Array1必需，Array2，array3，……可選，其相應元素需要進行相乘并求和。
　　SUMPRODUCT函數的基本功能是返回相應的區域或數組乘積的和。但也可利用SUMPRODUCT函數進行多條件計數和多條件求和。
　　若要統計同時滿足條件1、條件2到條件n的數據個數，則可使用
　　SUMPRODUCT（（條件1）*（條件2）*…*（條件n））
　　若要統計同時滿足條件1、條件2到條件n的數據的和，則可使用
　　SUMPRODUCT（（條件1）*（條件2）*…*（條件n）*求和區域）
　　1.2定義名稱
　　為了在公式計算中方便輸入和減少輸入量，給需使用的單元格區域定義簡單易記的名稱，它們分別為年級、院系名稱、理論成績和操作成績。
　　操作步驟：選擇菜單“插入”→“名稱”→“定義”命令，在定義名稱對話框中輸入名稱和引用位置，確定即可。定義名稱對話框如圖1所示。
　　
　　
　　
　　
　　
　　
　　
　　
　　圖1定義名稱對話框
　　名稱引用位置中使用了一個動態的公式“=OFFSET（成績表!$F$2，，，COUNTA（成績表!$A:$A）-1，1）”定義數據源，這樣就不用管共有多少條記錄了，COUNTA（成績表!$A:$A）-1會自動計算記錄數，并且如當一條新的紀錄添加或刪除時，數據源會自動擴展。
　　1.3創建統計表
　　添加一個新的工作表，用于存放統計數據，命名為“統計表”，表頭設計如下：
　　
　　
　　
　　在A和B列分別輸入年級和院系名稱。
　　在C3單元格輸入“=SUMPRODUCT（（年級=$A3）*（院系名稱=$B3）*（理論成績>=0））”；
　　在D3單元格輸入“=SUMPRODUCT（（年級=$A3）*（院系名稱=$B3）*（理論成績>=60））”；
　　在E3單元格輸入“=SUMPRODUCT（（年級=$A3）*（院系名稱=$B3）*（操作成績>=60））”；
　　在F3單元格輸入“=D3/C3”；在G3單元格輸入“=E3/C3”；
　　在H3單元格輸入“=SUMPRODUCT（（年級=$A3）*（院系名稱=$B3）*理論成績）/C3”；
　　在I3單元格輸入“=SUMPRODUCT（（年級=$A3）*（院系名稱=$B3）*操作成績）/C3”。
　　填充數據，并將小數位設置為兩位，部分統計結果顯示如下：
　　
　　
　　
　　
　　2.數據透視表法
　　2.1創建空白的數據透視表
　　選定數據清單中的任意單元格，選擇菜單“數據”→“數據透視表和數據透視圖”命令，在彈出的對話框中單擊“完成”按鈕。Excel將產生一個含有空白的數據透視表的工作表，其中顯示字段列表和數據透視表工具欄。
　　2.2對數據透視表進行布局
　　若需統計各年級、各院系的理論成績的平均分、及格人數和及格率等，則可從字段列表中將“年級”字段拖到頁字段區，將“院系名稱”拖到行字段區，將“理論成績”字段拖到列字段區，將“理論成績”、“學號”和“學號”字段依次拖到數據區。將數據透視表中的“數據”拖到右邊的單元格。
　　若要改變數據透視表的布局，就將欲刪除的字段拖出數據透視表，然后將新的字段從字段列表中拖到數據透視表相應區域即可。
　　2.3設置字段的顯示方式和匯總方式
　　將“理論成績”分為<60和≥60兩種情況顯示。操作方法：右擊數據透視表中的“理論成績”，在彈出的快捷菜單中選擇“組及顯示明細數據”→“組合”，在彈出的“組合”對話框中將步長設置為60。
　　Excel對“理論成績”字段默認的匯總方式為“求和”。若要更改匯總方式為“平均值”，可右擊“求和項:理論成績”，選擇“字段設置”，或先單擊“求和項:理論成績”，再單擊數據透視表工具欄上的“字段設置”按鈕，均可調出“數據透視表字段”對話框；在“數據透視表字段”對話框中，單擊“匯總方式”列表框中的“平均值”，然后單擊“確定”按鈕。這就將“理論成績”的匯總方式改成了“平均值”。
　　將“學號2”字段的顯示方式更改為“占同行數據總和的百分比”。操作方法：右擊“計數項:學號2”，選擇“字段設置”，在調出“數據透視表字段”的對話框中，單擊“選項”，在“數據顯示方式”列表框中選擇“占同行數據總和的百分比”，然后單擊“確定”按鈕。
　　2.4重命名字段
　　當向數據區域添加字段后，它們都將被重命名，例如“理論成績”變成了“平均值項:理論成績”，這樣加大了字段所在列的寬度，表格顯得不緊湊。如需重命名字段，只要單擊數據透視表中需要修改的標題單元格，在編輯欄中輸入新標題即可。我們將“平均值項:理論成績”改為“理論平均分”，“計數項:學號”改為“人數”，“計數項:學號2”改為“人數占比”，將“0~59”改為“不及格”，將“60~119”改為“及格”。
　　稍作修飾，將小數位數設置為兩位小數，隱藏“人數匯總”和“人數占比匯總”。至此，可得到如圖2所示的數據透視表。
　　
　　
　　
　　
　　
　　圖2數據透視表
　　數據透視表創建好后，任何時候都可根據需要，對數據透視表重新進行布局，即只需拖動字段按鈕就可，并可非常方便地調整顯示方式和匯總方式，以便從不同的角度查看分析數據，從中尋找有價值的信息，滿足新的數據分析的要求。
　　結語
　　Excel具有強大的數據分析和數據處理功能，包含了9大類、400多個內置函數和大量的分析工具。公式或函數法和數據透視表法都是Excel分析和處理數據的重要手段，合理選取，巧妙應用，能使數據處理方便且高效。
　　
　　參考文獻：
　　［1］魏零.巧用Excel數據透視表統計分析學生成績［J］.科技信息，2010.7（19）.
　　［2］Excel Home.Excel數據透視表應用大全［M］.北京：人民郵電出版社，2009.6.
　　［3］陳國良.Excel 2010函數與公式［M］.北京：電子工業出版社，2010.12.