EXCEL做考試成績(jī)分析

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

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

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

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

5、數(shù)據(jù)匯總統(tǒng)計(jì)分析中是非常有用的函數(shù)。有了這些基礎(chǔ)知識(shí)以后，下面，筆者就將這一過(guò)程詳細(xì)展 現(xiàn)給大家。 首先，新建一個(gè)EXCEL文檔，將文檔的第一個(gè)工作表sheetl重命名為“成績(jī)”，將第二個(gè)工作表sheet2重命名 為“統(tǒng)計(jì)”。 回到成績(jī)工作表中，從第一個(gè)單元格開(kāi)始，依次輸入年級(jí)、班級(jí)、姓名、考號(hào)、語(yǔ)文、數(shù)學(xué)、英語(yǔ)、政治、歷史、地理、生物、總分 等。然后單擊菜單欄中的“插入”“名稱”一“定義名稱”在定義欄中輸入“年級(jí)”然后單擊添加。如圖所示： 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 再將引用位置中的“=成績(jī)!$A$2”，改寫為“二成績(jī)!$A$2:$A$1000 ”，它的意思是說(shuō)定義“年級(jí)”這個(gè)名稱是由 列的第2行開(kāi)始到第1000行結(jié)束，在這個(gè)區(qū)域有效（也可以根據(jù)自己的實(shí)際情況進(jìn)行定義，但一定要保證所定義 的名稱范圍超出數(shù)據(jù)輸入的范圍，其它的定義和它是類似的）?！澳昙?jí)”定義結(jié)束后，我們采用同樣的方法去定義“班級(jí)”這個(gè)名 稱。單擊B2單元格，單擊“插入”“名稱”一“定義名稱”。這時(shí)由于“班級(jí)”這一列是在B列上，因此需將它的引用位置 “=成績(jī)!$B$2”改寫成“=成績(jī)!$B$2:$B$1000

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

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

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

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

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

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

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