Excel函數(shù)在學生成績錄入和分析中的應用

摘 要： 高校課程考核方式是課程改革重要環(huán)節(jié)，它的變化帶來了成績處理復雜化的問題，而Excel函數(shù)在數(shù)據(jù)處理方面的強大功能，可以大大提高工作效率和準確性。

關鍵詞： Excel；函數(shù)；成績錄入；成績分析

1.引言

遼寧對外經(jīng)貿(mào)學院的期末考試總評成績是由兩大部分構成，一是平時成績，一個是期末卷面成績。平時成績又由三部分構成，分別是課堂出勤、課堂表現(xiàn)和作業(yè)成績，這些成績的錄入是個很大的工程。同時，還要對期末卷面成績相關數(shù)據(jù)進行統(tǒng)計和分析。這給絕大多數(shù)任課教師帶來了不小的困難，如何準確快速的實現(xiàn)相關數(shù)據(jù)的生成、統(tǒng)計和分析是教師們迫切想解決的問題。

2. 成績的錄入

2.1 自動輸入小數(shù)點

成績在錄入過程中，有的時候需要輸入一批規(guī)則的帶有小數(shù)點的數(shù)字。試卷中最常見的是帶有小數(shù)點0.5，如果一個一個的輸入小數(shù)點的話，工作量就會非常大，而且如果在沒有小鍵盤的筆記本上輸入的話，因為數(shù)字鍵和小數(shù)點鍵相隔較遠，操作起來也不方便。那么我們可以預先設置好小數(shù)點的位置，然后直接輸入數(shù)字就可以了。操作步驟為：

（1）在【文件】中選擇“選項”-“高級”。（2）在“編輯選項”中，選中“自動插入小數(shù)點”項，然后在“位數(shù)”中輸入小數(shù)點的位數(shù)。如圖所示。

設置完后，再在單元格中輸入數(shù)值，數(shù)值會自動帶上小數(shù)點。如果選擇的位數(shù)是“1”，輸入35將顯示3.5，輸入125將顯示12.5。但要注意，如果想顯示整數(shù)4，我們需要輸入40。

2.2 輸入重復數(shù)據(jù)

在輸入平時成績的考勤一項時，由于學校管理嚴格，學生考勤結果大都良好，成績基本相同。這時候如果一個一個的輸入就太浪費時間了。

我們可以“Ctrl”或“Shift”鍵選擇需要輸入相同數(shù)字的單元格（或單元格區(qū)域），輸入數(shù)值，同時按下“Ctrl+Enter”鍵。

2.3 自動換行輸入數(shù)據(jù)

在一個單元格區(qū)域輸入數(shù)據(jù)時，往往需要換行。在大批量輸入數(shù)據(jù)時，無論是利用上下左右箭頭鍵換行，還是利用鼠標換行，都會影響輸入的效率。

我們可以先選中要輸入數(shù)據(jù)的區(qū)域，輸入完一個數(shù)據(jù)后，利用Tab鍵向右移動，移動到區(qū)域邊界時，在按在Tab鍵，會自動換行。還要注意，如果某個數(shù)據(jù)輸入錯誤，不要用鼠標返回，而是要利用“Tab+ Shift”鍵。

2.4 數(shù)據(jù)有效性設定

學生的各項成績都在一定的范圍之間 ，我們希望在輸入錯誤范圍數(shù)據(jù)時，系統(tǒng)能給予提示。設置錯誤提示功能的操作步驟為 ：

（1）選擇需要輸入數(shù)據(jù)的單元格區(qū)域（如果數(shù)據(jù)范圍不同，我們就要分別設置規(guī)則）。

（2）選擇“數(shù)據(jù)”-“數(shù)據(jù)有效性” –“設置”，在“允許”選項下，根據(jù)實際情況選擇整數(shù)或小數(shù)和最大值與最小值。

進行設置后 ，只要在設置了的單元格內(nèi)中輸入不符合要求的數(shù)值，則會出現(xiàn)提示錯誤提示，提高了輸入成績的準確性。

3. 統(tǒng)計分析成績

3.1 查找總成績中的不及格成績

在成績分析時，需要找出不及格的成績，還不想改變成績所在的位置，可以采取以下方法。

方法一 圈識不及格成績，操作步驟為：

（1）選中一個空白單元格。

（2）打開數(shù)據(jù)工具，選擇數(shù)據(jù)有效性-選擇數(shù)據(jù)有效性-設置-允許-整數(shù)，在最小值處輸入60，在最大值處輸入100，確定。

（3）復制這個單元格，選擇性粘貼-有效性驗證到需要查找的數(shù)據(jù)區(qū)域。

（4）打開數(shù)據(jù)工具，選擇數(shù)據(jù)有效性-詮釋無效數(shù)據(jù)。結果如圖所示。

方法二 借助IF函數(shù)查找，操作步驟為：

（1）在空白單元格F2中輸入函數(shù)“=IF（E2<60， "不及格"，""）”，確定。

（2）拖動填充控制柄，復制公式。結果如圖所示。

方法三 突出顯示不及格成績， 操作步驟為：

（1） 選中數(shù)據(jù)所在的單元格區(qū)域，選擇開始-條件格式-突出顯示單元格規(guī)則-介于。

（2）在打開的對話框中，設置“單元格數(shù)據(jù) ”介于0與59之間，格式設置中字體的顏色設置為紅色，確定。結果如圖所示。

3.2 統(tǒng)計各分數(shù)段人數(shù)及占總人數(shù)的比例，畫出餅狀圖

3.2.1 統(tǒng)計各分數(shù)段人數(shù)， 操作步驟為：

（1）選擇一個區(qū)域輸入分數(shù)段的界值，要按升序排列。一般按：成績<60（不及格）、60≤成績<70（及格）、70≤成績<80（中）、80≤成績<90（良）、成績≥90（優(yōu)）劃分成績段，所以輸入的界值分別是59，69，79，89，100.如圖在單元格區(qū)域H1：H5中輸入。

（2）選擇單元格區(qū)域I1：I5，輸入函數(shù)“=FREQUENCY（E2：E45，H1：H5）”。注：數(shù)據(jù)區(qū)域（E2：E45）是成績數(shù)據(jù)區(qū)域，（H1：H5）是分數(shù)段的界值區(qū)域也叫數(shù)據(jù)分組區(qū)域。

（3）同時按 Ctrl+Shift+Enter，在 I1：I5區(qū)域看到統(tǒng)計結果。

（4）選擇單元格區(qū)域I1：I5，插入餅狀圖。

3.2.2 各分數(shù)段人數(shù)占總人數(shù)的比例， 操作步驟為：

（1）在單元格G2中輸入班級人數(shù)45，選擇單元格區(qū)域J1，輸入函數(shù)“=ROUND（I1/$G$2，3）”，確定。（也可以選擇單元格區(qū)域J1，輸入函數(shù)“=ROUND（I1/SUM（I$1：I$5），3）”）

（2）拖動填充控制柄，復制公式。得如圖所示結果。

注：顯示各分數(shù)段人數(shù)占總人數(shù)的比例也可以利用餅狀圖-添加數(shù)據(jù)標簽，然后右鍵選擇任意數(shù)據(jù)標簽，在彈出的快捷菜單中選擇設置數(shù)據(jù)標簽格式-標簽選項-百分比。

3.3 不改變順序進行分數(shù)排名

分數(shù)排名有兩種排名方式有，一種是用排序方式，但會改變成績單原來的順序。另一種是利用函數(shù)RANK， 操作步驟為：

（1）選擇空白單元格G2，在單元格內(nèi)輸入函數(shù)“=RANK（E2，$E$2：$E$46，0）”。

（2）拖動填充控制柄，復制公式。得如圖所示結果。

3.4 統(tǒng)計最高分 、最低分和平均分

統(tǒng)計最高分 ，最低分 ，平均分的方法有兩個，方法一是利用工具欄的“自動求和”選項。另一個方法是利用函數(shù)：求最高分用函數(shù)“MAX”，最低分函數(shù)“MIN”，平均分用函數(shù) “AVERAGE”。

4. 結束語

以上的方法是筆者多年總結的經(jīng)驗，希望能給老師們在成績錄入和分析的過程中提供一些幫助。

參考文獻

[1] 魏雪萍 ，李嵐.Excel應用大全 [Ml].北京 ：人 民郵 電出版社 ，2008.

[2] 龍建祥 ，張鐵軍.EXCEL函數(shù)與公式綜合應用技巧[M].北京：人民郵電出版社，2010.

作者簡介：梁海濱（1978-），女，遼寧人，遼寧對外經(jīng)貿(mào)學院基礎課教研部副教授，從事高等數(shù)學教學研究。endprint