基于Excel數(shù)據(jù)分析處理功能統(tǒng)計(jì)學(xué)生成績(jī)

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