淺談EXCEL函數等相關知識在教師日常工作中的應用

【摘要】教師在學校工作中，不僅包含教學、教研還包括對學生的成績的管理、分析和評價，成績管理統計中不僅包括多次的單元考核成績、還包括紀律、考勤、課堂參與度等都要評價、量化、排名情況以及進行各分數段的人數統計等工作，要完成這這些看似簡單卻計算繁瑣的工作常常花費我們大量的時間。如何快速準確的完成這些操作，通過EXCEL的函數及相關知識對學生成績進行管理、統計有助于提高辦公效率和正確率。下面就自己工作中一些應用和大家分享。

【關鍵詞】教師；成績管理；函數；統計

教師在學校工作中，不僅包含教學、教研還包括對學生的成績的管理、分析和評價，成績管理統計中不僅包括多次的單元考核成績、還包括紀律、考勤、課堂參與度等都要評價、量化、排名情況以及進行各分數段的人數統計等工作，要完成這這些看似簡單卻計算繁瑣的工作常常花費我們大量的時間。如何快速準確的完成這些操作，通過EXCEL的函數及相關知識對學生成績進行管理統計有助于提高辦公效率和正確率。下面就自己工作中函數及相關知識的一些應用和大家分享。

函數是excel預先定義好的公式，下面利用excel函數及相關知識完成學生成績的統計、分析和管理。

一、新建空白Excel 工作簿

將自動建立的Sheet1、Sheet2工作表重命名為班級如12401班、12402班等等。為安全和防護工作薄、工作表、單元格，對學生成績單的讀寫需要做一些權限的設置，所以可以設置對整個工作簿保護，還可以通過對單元格的鎖定，保護工作表來完成對單元格內數據的保護。

二、工作表的輸入

在12401班成績工作表中，第一行輸入名稱，如“**學院2013-2014第一學期成績表”，在第二行從A2單元格開始順序輸入記錄字段名：學號（A列）、姓名（B列）、語文（C列）、政治（D列）、計算機（E列）、數學（F列）、英語（G列）、總分（H列）、平均分（I列），排名（J列），評價（K列）等。假設全班學生有50人，所以從第3行到第53 行用來輸入所有學生各科的成績。

1.成績的正確輸入：成績管理運算中，經常遇到的四舍五入問題：大多數人都會使用“格式欄”里的“增加小數位數”和“減少小數位數”來處理，雖然處理的數據看起來已經四舍五入了，但是實際數據未發生變化，當進行數據運算時就會發生數據不一致性，如某個學生各科成績的平均分是59.6，減少小數位后，成績是顯示60分，進行數據統計時，卻被統計在不及格的人數中，如果使用函數處理四舍五入問題，就不會發生此類情況，方法如下，例如需要四舍五入的數據在B列，選擇B1數據右邊的空白單元格C1，選擇“插入函數”中的“ROUND”函數，在“函數參數”界面中number中輸入“B1”，num_digits中輸入四舍五入的位數，點擊確定，C1中會出現B1四舍五入后的數據。也可以直接在C1單元格中輸入“=ROUND（B1，0）”，C1中會出現B1保留零個小數后的數據。將鼠標移到C1單元格的右下角的小黑方塊填充柄上，這時鼠標就會變成一個黑色十字形，按住鼠標左鍵向下拖動，拖到B列數據結束，C列數據就為B列四舍五入后的數據，復制C列數據，選中B列數據，點擊右鍵選擇“選擇性粘貼”中的“數值”，點擊“確定”，B列中的數據就會變成實實在在四舍五入后的數據，這樣完成的數據后進行數據運算和統計就不會出現數據的部一致性。

2.條件格式化輸入：在利用Excel 進行數據管理時，為了使計算的數據更加醒目，分類更加清晰，往往會對不同的結果數據設置不同的格式。例如將學生考試成績表中各門功課的成績在60 分以上的用藍色表示，而將成績在60 分以下的用紅色表示。 “條件格式化”功能，選中要設置格式的單元格，工具欄 “條件格式”進行相應的設置，可以使用“格式刷”將格式復制到其他需要的單元格中。

三、函數在成績管理分析中的應用

1.求出各科的參加考試人數COUNT函數，及條件計數COUNTIF函數的應用

用COUNT函數，求出各科的參加考試人數將把數值型的數字計算進去；但是錯誤值、空值、邏輯值、日期、文字則被忽略，如有同學缺考，或緩考不被計數，如果在C3：C53這個區域中存放語文成績可在C54單元格語文成績的最后一行單元格中輸入“=COUNT（C3：C53）” 函數用于計算給定區域內的數值型的數字單元格的數目，就求出了班級的語文參考人數。然后選中C54 單元格，拖動C54 單元格右下角的小“十”字填充柄圖標至最后一各科目成績如G54 英語單元格（復制公式），這樣完成各科參考人數計算。

用COUNTIF函數進行及格率統計，假如C3：C53存放著50名學生的考試成績，在c54單元格內輸入公式“=COUNTIF（C3：C53，″>=60″）/COUNTA（C3：C53）”回車，即可計算出該列成績的及格率（即分數為60及以上的人數占總人數的百分比）。

2.求學生總分SUM函數和SUMIF， AVERAGEIF函數根據指定條件對若干單元格求總分和平均分。

SUM函數，如A3：G3存放學生各科成績，可直在H3單元格中輸入“=SUM（A3：G3）”直接求出學生各科成績總分。

SUMIF AVERAGEIF函數根據指定條件對若干單元格、區域或引用求和及平均分。

假如C1：C50單元格存放某班學生的考試成績，若要計算及格學生的平均分， “= AVERAGEIF（C1：C50，″>=60″，C1：C50）。計算及格學生的總分“=SUMIF（C1：C50，″>=60″，C1：C50）”，

3.用FREQUENCY函數統計進行各分數段的人數統計

如要統計小于60分，60到69，70到79，80到89，90到100分中，各分數段的人數，方法很多，可用COUNTIF、DCOUNT和FREQUENCY函數統計，下面用FREQUENCY函數統計舉例，這是一個專門用于統計某個區域中數據的頻率分布函數，如C1：C50單元格存放某班學生的考試成績，求各分數段學生人數。首先：分別選中O64至O68單元格，輸入分數段的分隔數值，59，69，79，89，100，然后 同時選中E64至E68單元格區域，在“編輯欄”中輸入公式：=FREQUENCY（C1：C50，$O$64：$O$68），輸入完成后，按下“Ctrl+Shift+Enter”組合鍵進行確認，即可一次性統計出學生成績各分數段的學生人數。注意：首先輸入公式是選中E64至E68單元格區域，，實際上此處輸入的是一個數組公式，數組公式輸入完成后，不能按“Enter”鍵進行確認，而是要按“Ctrl+Shift+Enter”組合鍵進行確認。確認完成后，在公式兩端出現一個數組公式的標志“{}”（該標志不能用鍵盤直接輸入）。

4.Rank函數，進行成績排名應用

rank函數，如果成績在C1：C50列，公式在D列輸入公式，在D1單元格輸入公式“=rank（c1，c$1：c$50，0）”，拖拽D1單元格的填充柄完成公式復制直到D50。就可以看到排名了，如果有相同的成績，就會看到并列排名。如果有兩個第三名，就不會有第四名，下一名是第五名。

參考文獻

[1]Excel Home著Excel 2010應用大全[M].北京：人民郵電出版社，2011.

[2]王作鵬，殷慧文，神龍工作室編.Word/Excel/PPT 2010辦公應用從入門到精通[M].人民郵電出版社，2013.