EXCEL做考試成績分析

1、自己動手，用EXCEL做一個考試成績分析應(yīng)用小軟件河南省沁陽市第二學(xué)王東慶柴紅偉長期以來，在每次考完試后，不僅要去處 理學(xué)生的成績，還要計算和分析教師的成績。這是一件非常煩瑣和無可奈何的事情。如果只是對學(xué)生的總分、名次、各科的平均分進行 統(tǒng)計的話，用EXCEL去完成這項工作是一件非常容易的事情，然而許多學(xué)校還要對任課教師進行成績統(tǒng)計，依據(jù)就是該任課教師所 教該學(xué)科的“三率和”?！叭屎汀笔侵笇⒛骋粚W(xué)科的平均分+及格率（及格人數(shù)/參加考試的人數(shù)*100 ） +優(yōu)秀率（優(yōu)秀人數(shù)/參 考人數(shù)*100 ） o實際上我們用EXCEL是可以輕松做到的。只需將學(xué)生的考試成績按照一定的格式錄入，那么EXCEL

2、就可以自 動去完成計算三率和的工作。下面我們通過一個實例來看一下。 比如錄入初一年級某次考試成績： 在開始學(xué)習(xí)之前，大家有必要了解一些EXCLE函數(shù)方面的知識。公式和函數(shù)是Excel最基本、最重要的應(yīng)用工具，是Excel的核 心，因此，應(yīng)對公式和函數(shù)熟練掌握，才能在實際應(yīng)用中得心應(yīng)手。 1數(shù)組公式的輸入 數(shù)組公式的輸入步驟如下：（1）選定單元格或單元格區(qū)域。如果數(shù)組公式將返回一個結(jié)果，單擊需要輸入數(shù)組公式的單元格；如果數(shù)組公式將返回多個結(jié)果，則要選定需要輸入數(shù)組公式的單元格區(qū)域。 (2) 輸入數(shù)組公式。 (3) 同時按“ Crtl+Shift+Enter”組合鍵，則Excel自動在公式的兩邊加

3、上大括號。特別要注意的是，第(3)步相當(dāng)重要，只 有輸入公式后同時按“Crtl+Shift+Enter ”組合鍵，系統(tǒng)才會把公式視 為一個數(shù)組公式。否則，如果只按Enter鍵，則輸入的只是一個簡單的公式，也只在選中的單元格區(qū)域的第1個 單元格顯示出一個計算結(jié)果。 2條件求和SUMIF函數(shù) SUMIF函數(shù)的功能是根據(jù)指定條件對若干單元格求和，公式為： =SUMIF (range,criteria,sum_range) 式中range用于條件判斷的單元格區(qū)域，只有當(dāng)range中的相應(yīng)單元格滿足條件時，才對sumange中的單元格求和。如果省略 sumangejfj直接對range中的單元格求和。

4、3. AVERAGE 函數(shù) AVERAGE函數(shù)的功能是計算給定參數(shù)的算術(shù)平均值。公式為 =AVERAGE (參數(shù)1，參數(shù)2，參數(shù)N ) 4. COUNT函數(shù)和COUNTIF函數(shù) COUNT函數(shù)的功能是計算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目。公式為 =COUNT (參數(shù)1，參數(shù)2，參數(shù)N ) COUNTIF函數(shù)的功能是計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。公式為 =COUNTIF ( range criteria ) 式中range需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域； criteria 確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。 COUNT函數(shù)和COUNTIF函數(shù)在

5、數(shù)據(jù)匯總統(tǒng)計分析中是非常有用的函數(shù)。有了這些基礎(chǔ)知識以后，下面，筆者就將這一過程詳細展 現(xiàn)給大家。 首先，新建一個EXCEL文檔，將文檔的第一個工作表sheetl重命名為“成績”，將第二個工作表sheet2重命名 為“統(tǒng)計”。 回到成績工作表中，從第一個單元格開始，依次輸入年級、班級、姓名、考號、語文、數(shù)學(xué)、英語、政治、歷史、地理、生物、總分 等。然后單擊菜單欄中的“插入”“名稱”一“定義名稱”在定義欄中輸入“年級”然后單擊添加。如圖所示： 1T g A J 5 c DI no GH XJ X L iW 亜価改AS Pit!地嗖 it* 10 11_ 12 13 14 15 u r IT 18

6、 n J!L 22 29 25 26 2T 28 | 29 4 4 B WXEMaW 再將引用位置中的“=成績!$A$2”，改寫為“二成績!$A$2:$A$1000 ”，它的意思是說定義“年級”這個名稱是由 列的第2行開始到第1000行結(jié)束，在這個區(qū)域有效（也可以根據(jù)自己的實際情況進行定義，但一定要保證所定義 的名稱范圍超出數(shù)據(jù)輸入的范圍，其它的定義和它是類似的）。“年級”定義結(jié)束后，我們采用同樣的方法去定義“班級”這個名 稱。單擊B2單元格，單擊“插入”“名稱”一“定義名稱”。這時由于“班級”這一列是在B列上，因此需將它的引用位置 “=成績!$B$2”改寫成“=成績!$B$2:$B$1000

7、 ”。采用類似的方法依次定義“姓名”、“考號”、“語文”、“數(shù)學(xué)”、“英 語”、“政治”、“歷史”、“地理”、“生物”、“總分”，注意要將它們的引用位置寫正確！這樣，在“成績”工作表中我們所 要做的準(zhǔn)備工作就基本完成了?，F(xiàn)在就可以在此“成績”工作表中進行學(xué)生成績的錄入工作，學(xué)生的成績也可以是從其它的EXCEL 文檔中復(fù)制一粘貼過來，但是順序必須要和“成績”表中的順序保持一致。 在打開“統(tǒng)計”這個工作表。單擊“插入一名稱一定義”，你會發(fā)現(xiàn)，在“成績”工作表中定義的名稱，在“統(tǒng)計”這個工作表中也 可以顯示出來。我們在“統(tǒng)計”工作表中建立如圖所示的一張表： 注意，第15行的06表示這個年級是06級。

8、 單擊B5這個單元格，在公式編輯欄中輸入“ =COUNTIF （班級,A5） ”，它的意思是要統(tǒng)計我們定義的名稱“班級”當(dāng)中的A5 這個班（即06p01班）的參加考試人數(shù)。接著同時按下ctrl+shift+enter這三個鍵，生成一個計算班級參加考試人數(shù)的數(shù)組公式。 接著再單擊C5這個單元格，在公式編輯欄中輸入“ =AVERAGE （IF （班級二A5）,語文）”，它的意思是說要計算A5這個 班的語文平均分，同時按下ctrl+shift+enter這三個鍵，生成一個數(shù)組公式。 單擊D5這個單元格，在公式編輯欄中輸入“=SUM （班級=A5） * （語文=60） ） /COUNTIF （班級,A

9、5） *100 M，意思是 說要統(tǒng)計A5這個班級當(dāng)中語文成績大于60分（及格分數(shù)是自己定義），也就是說統(tǒng)計該班的語文科的及格人數(shù)，然后除以這個班 實際參加考試的人數(shù)再乘以100，就得出了 A5這個班的及格率。注意“=SUM （班級=A5） * （語 文=60） ”中的這個*，表示的是邏輯運算中的“與”。而COUNTIF （班級,A5） *100 ”中的表示乘的意思，這兩個*的 含義是不一樣的。 同理，單擊E5這個單元格，在公式編輯欄中輸入“ =SUM （班級=A5） * （語文=80） ） /COUNTIF （班級,A5） *100 ” ， 便可以得出這個班的語文優(yōu)秀率（實際上優(yōu)秀的分數(shù)也是自

10、己根據(jù)實際情況進行定義的）。同時按下ctrl+shift+enter這三個鍵，生 成一個數(shù)組公式。 單擊F5這個單元格，在公式欄中輸入“=C5+D5+E5 ”，就得出了三率和。 然后再用鼠標(biāo)拖拽選中B5到F5這些連續(xù)的單擊格，向下填充到第14行。如圖： 接下來要做的工作是，單擊B15這個單擊格，在公式欄中輸入“二COUNTIF （年級,A15） ”，同時按下ctrl+shift+enter這三 個鍵，生成一個數(shù)組公式。單擊C15單元格，在公式欄中輸入“ AVERAGE （IF （年級=A15）,語文）”，同時按下 ctrl+shift+enter這三個鍵，生成一個數(shù)組公式。單擊D15單元格，在

11、公式欄中輸入“ =SUM （年級=A15） * （語文=60） /B15*100，同時按下ctrl+shift+enter這三個鍵，生成一個數(shù)組公式。單擊E15單元格，在公式欄中輸入“=SUM （年級 =A15） * （語文=80） ） /B15M00 ”，同時按下ctrl+shift+enter這三個鍵，生成一個數(shù)組公式。單擊F15單元格，在公式欄中 輸入 “=D15+E15+C15”。 接著我們要做的是各分數(shù)段人數(shù)的統(tǒng)計工作。 單擊H5單元格，在公式欄中輸入“ =MAX （IF （班級=A5），語文）”，它的意思是統(tǒng)計出該班的最高分。單擊 I5這 個單元格，在公式欄中輸入“ =MIN （I

12、F （班級=A5）,語文）”，同時按下ctrl+shift+enter這三個鍵，生成一個數(shù)組公 式。 單擊J5這個單擊格，在公式欄中輸入“=SUM （班級=A5） * （語文V =59） ”，同時按下ctrl+shift+enter這三個鍵，生成 一個數(shù)組公式。單擊K5單元格后，在公式欄中輸入“ =SUM （班級=A5） * （語文=60） * （語文=79） ”，同時按下 ctrl+shift+enter這三個鍵，生成一個數(shù)組公式。單擊L5單元格，在公式欄中輸入“ =SUM （班級=A5） * （語文=80） * （語文V=100） ”，同時按下ctrl+shift+enter這三個鍵，生成

13、一個數(shù)組公式。 然后用鼠標(biāo)拖拽選中H5到L5，向下進行填充。如圖： 然后單擊H15這個單擊格，在公式欄中輸入“=MAX （IF （年級=A15）,語文）”，同時按下ctrl+shift+enter這三個鍵， 生成一個數(shù)組公式。單擊115這個單元格，在公式欄中輸入“=MIN （IF （年級=A15），語文）”，同時按下ctrl+shift+enter 這三個鍵，生成一個數(shù)組公式。單擊J15單元格，在公式欄中輸入“ =SUM （語文v=59） * （年級=A15） ”，同時按下 ctrl+shift+enter這三個鍵，生成一個數(shù)組公式。單擊K15單元格，在公式欄中輸入“ =SUM （年級=A15） * （語文=60） * （語文v=79） ” ，同時按下ctrl+shift+enter這三個鍵，生成一個數(shù)組公式。單擊L15單元格，在公式欄中輸入“ =SUM （年級=A15） * （語文=80） ” （語文=100） ” ，同時按下ctrl+shift+enter這三