自己動(dòng)手,用EXCEL做一個(gè)考試成績分析應(yīng)用軟件

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

2、去完成計(jì)算三率和的工作。下面我們通過一個(gè)實(shí)例來看一下。 比如錄入初一年級(jí)某次考試成績：Di MicFwoit Excel -熱"統(tǒng)計(jì)線。£43文產(chǎn)值缺（I）格式屯工匡心數(shù)據(jù)頷安匚由幫翡婚,fix:Ij u修J E L4上i -，*9.p沱，工小到urn皿*金呆伸 12-1 »7 U= T %,二、彳C47w*李祥Arc1 EGHrJKLK 211年級(jí)bjmikullBL則W1.富dlMEzf2 105OfpOl尚倩信71?！可坠さ?63OfipOl李詵r0102如67第0例閆晶晶r010391加65OEpM牛亞幄0104加陽5飛6OEpOl障麝0105Z4皿537

3、 0&oepcn陳晶*0106TS82范pUL張冰潔U1Q7S3T35。9市OEpUL件言13108019。狷1。枇OEpUl趙憧F010J92TS3711 *06師01楊慶航加。q?944912 7)606p01疆招招飛口】S395好13 *06oepDi賀勒0112E'i料14%S06p01當(dāng)嬌妹3113S27943IFbe06阿山D本1W31140S5445if,_ 二 _%plil黑文思飛115-r 1 - 1r S3好50i=11, .二錄入結(jié)束后，利用EXCEL自帶的一些函數(shù)，就能 方便地把結(jié)果統(tǒng)計(jì)出來。如圖:E Mtci traott Excel Hl 既計(jì)傻兼-

4、1! x|:置 文件中 端孤 視因也 取一 格式肛工具中 密瘡 窗口中 幫助皿1-日上口才/ dQ"數(shù)由修，/”圖皿 - o|I來* 1。-岫2 g I *同一國1需H »孤魂聿學(xué)LL>*A，BG9E -&報(bào)獷ABCnEFGHITXL1初一感拜比計(jì)表9 j語文34如3蜥&人蛇春考人物平均升就格率女奇車=Wnin最區(qū)愷0-5 £»00-7980-i0n5劃二58.076. 384. E62.1222.9郭士音笛09. n13. fi3 i160除口 252.033.3100.073.1256. M54650.014.03R. nX,C

5、80,4姐LS3.7232.2張長征98S9kC24,029 j)XO6p04&5.079.691.166.1236. 7皿255.0U. 037. UJ|訃口553丁72.9限541. c200. F吳有甌加03.024.023.0LC火訓(xùn)6*.076, 790.7鈍mW Cm mg505.QL，Q35.011知丁74.29"35？202,3宋立志筑04.C虱口120p0377 89342.921X6釜454.028 fi24.013血口勺56.0?1.9 1嘰532.1HLG郭雛東07.031,018. U1日OEpld5S.070. F明.E30.919.80丸080,

6、01?. U15k U55沼U用385Q.2E1L 205LQ2羽一27 T. U1C5 1 丁 M1 口卜、敬i三生l_3;成齒/朦。分漏f丁而n四就廣I”徐困m，j 自選圉播m，x、nc 自 NddUTi小，u，a三工言 在開始學(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ù)組公式的

7、單元格區(qū)域。(2)輸入數(shù)組公式。(3)同時(shí)按“ Crtl+Shift+Enter”組合鍵，則 Excel自動(dòng)在公式的兩邊加上大括號(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中的

8、相應(yīng)單元格滿足條件時(shí)，才對(duì)sum_range中的單元格求和。如果省略sum_range,則直接對(duì) range中的單元格求和。3 . AVERAGES 數(shù)AVERAGE!數(shù)的功能是計(jì)算給定參數(shù)的算術(shù)平均值。公式為=AVERAGE(參數(shù)1,參數(shù)2,，參數(shù) N)函數(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 確定哪些單

9、元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。COUNT函數(shù)和COUNTIF函數(shù)在數(shù)據(jù)匯總統(tǒng)計(jì)分析中是非常有用的函數(shù)。有了這些基礎(chǔ)知識(shí)以后，下面，筆者就將這一過程詳細(xì)展現(xiàn)給大家。首先，新建一個(gè)EXCE戊檔，將文檔的第一個(gè)工作表sheetl重命名為“成績”，將第二個(gè)工作表sheet2重命名為"統(tǒng)計(jì)"?；氐匠煽児ぷ鞅碇?，從第一個(gè)單元格開始， 依次輸入年級(jí)、班級(jí)、姓名、考號(hào)、語文、數(shù)學(xué)、 英語、政治、歷史、地理、生物、總分等。然后單擊菜單欄中的“插入”“名稱”一“定 義名稱"，在定義欄中輸入“年級(jí)”，然后單擊添加。如圖所示：再將引用位置中的“=成績!$A$2”

10、，改寫為“=成績!$A$2:$A$1000”，它的意思是說定義“年 級(jí)”這個(gè)名稱是由 A列的第2行開始到第1000行結(jié)束，在這個(gè)區(qū)域有效（也可以根據(jù)自己 的實(shí)際情況進(jìn)行定義，但一定要保證所定義的名稱范圍超出數(shù)據(jù)輸入的范圍，其它的定義和 它是類似的）?！澳昙?jí)”定義結(jié)束后，我們采用同樣的方法去定義“班級(jí)”這個(gè)名稱。單擊B2單元格，單擊“插入”“名稱”一“定義名稱”。這時(shí)由于“班級(jí)”這一列是在 B列上，因此需將它的引用位置“=成績!$B$2”改寫成“=成績!$B$2:$B$1000”。采用類似的方法依次定義“姓名”、“考號(hào)”、“語文”、“數(shù)學(xué)”、“英語”、“政治”、“歷史”、“地理”、“生物”、“總

11、分”，注意要將它們的引用位置寫正確！ 這樣，在“成績”工作表中我們所要做的準(zhǔn)備工作就基本完成了。現(xiàn)在就可以在此“成績” 工作表中進(jìn)行學(xué)生成績的錄入工作，學(xué)生的成績也可以是從其它的EXCE戊檔中復(fù)制一粘貼過來，但是順序必須要和“成績”表中的順序保持一致。 在打開“統(tǒng)計(jì)”這個(gè)工作表。單擊“插入一名稱定義”，你會(huì)發(fā)現(xiàn)，在“成績”工作表中定義的名稱，在“統(tǒng)計(jì)”這個(gè)工作表中也可以顯示出來。我們?cè)凇敖y(tǒng)計(jì)”工作表中建立如圖 所示的一張表：次文件® 編用反觀固播LD格式工且少熟況總畝F蛇珅聯(lián)口口母P者昌工9 X電電。6 囁魚片制制 世 W 飛，. "5 H -ABCD三FGHJ區(qū)L 11初

12、一區(qū)求統(tǒng)計(jì)表2忸Wr3q螂?？杖司纫犯衤蕛?yōu)旃0-印eo-70¥0-1005由06jjfi27nRr.O3391006p061 二帆Qf1E13眄鄧14ilGvlO1516注意，第15行的06表示這個(gè)年級(jí)是06級(jí)。單擊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）,語文）”，它的意思是說要計(jì)算 A5這

13、個(gè)班的語文平均分，同時(shí)按下 ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組 公式。單擊 D5這個(gè)單元格，在公式編輯欄中輸入“=SUM（班級(jí)=A5）*（語文=60）/COUNTIF（班級(jí),A5）*100 ”，意思是說要統(tǒng)計(jì) A5這個(gè)班級(jí)當(dāng)中語文成績大于60分（及格分?jǐn)?shù)是自己定義），也就是說統(tǒng)計(jì)該班的語文科的及格人數(shù)，然后除以這個(gè)班實(shí)際參加考試的人數(shù)再乘以100,就得出了 A5這個(gè)班的及格率。注意“= SUM（班級(jí)=A5）*（語文=60）”中的這個(gè)*,表示的是 邏輯運(yùn)算中的“與"。而COUNTIF班級(jí),A5）*100”中的*表示乘的意思，這兩個(gè)*的含義是不 一樣的。同理，單擊E5

14、這個(gè)單元格，在公式編輯欄中輸入“=SUM（班級(jí)二人5）*（語文=80）/COUNTIF班級(jí),A5）*100"，便可以得出這個(gè)班的語文優(yōu)秀率（實(shí)際上優(yōu)秀的分?jǐn)?shù)也是自己根據(jù)實(shí)際情況 進(jìn)行定義的）。同時(shí)按下ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊F5這個(gè)單元格，在公式欄中輸入“=C5+D5+E6，就得出了三率和。然后再用鼠標(biāo)拖拽選中B5到F5這些連續(xù)的單擊格，向下填充到第14行。如圖：ACDEF | GH二JK-JIz1勒一成銀克.計(jì)表2語文3省分曲段人感4平均分及格率立身窣二率制物加量品分是帳怫D-&060-7'JSCrlOQ!，）&阿5B

15、. u?.3LI 4. b62.12.8郭士雷652. 0S3.二100. 0邙潮T斷“51080-1398, 15k T232.2球長征S猊IX蘇城5為譯&L. 1Fl230.7955. Q72.985.541.8200.1關(guān)一秀麗JLQ口軌比54.076.7MTSi. 3232.21106阿5&0小.292.勺35.7202.3宋立杰121邳口之科一。77. S密方蚣g213.613能演956. rri.gFir. h32,1191.6郭春樂1455.0E5. 52q1JS6,S15"。6552.076,390.fiSO. 2217.216 1N 1猊計(jì)三軍/蚓腐

16、耐噬計(jì)/鴕進(jìn)值爐7"I圄j %，蕓/3” = 三4|就緒求社轉(zhuǎn)的1ETT-! x|；因 文件5 端餐 視留 帖上。 格M他 注 駐搪11 翻口口 培劭圜-國乂2若H修卻國，學(xué)” 小電b/叼*"打的畫舊帆，婚|:象ff-埃 a 1 U 拿回三圉粵端，口J3I學(xué)季達(dá)總的 *kcoim”：班殷,她）接下來要做的工作是，單擊B15這個(gè)單擊格，在公式欄中輸入“=COUNTIF怦級(jí),A15）”，同時(shí)按下ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊 C15單元格，在公式欄中輸入D HiaEdoM Cxcd -敦計(jì)械依“=AVERAGE（IF郃級(jí)=A15），語文）”，同

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

18、該班的最高分。單擊 I5這個(gè)單元格，在公式欄中輸入“ =MIN（IF（班級(jí)=A5）,語文）”，同時(shí)按下ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊 J5這個(gè)單擊格，在公式欄中輸入“=SUM（班級(jí)=人5）*（語文=59）”,同時(shí)按下ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊K5單元格后，在公式欄中輸入“=SUM（班級(jí)=A5）*（語文=60）*（語文=79）”，同時(shí)按下ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊L5單元格，在公式欄中輸入 “=SUM（班 級(jí)二人5）*（語文=80）*（語文=100）”,同時(shí)按下ctrl+shift+en

19、ter這三個(gè)鍵，生成一個(gè)數(shù)組公式。然后用鼠標(biāo)拖拽選中H5到L5,向下進(jìn)行填充。如圖：IE NKTOtoft bccel -初一統(tǒng)詩然能-JDJ x.M 0較困的©塔工旦督口甲勤陰-ft XydJ >4 iji uJ d4晶/' a 懸£,A 1 Z 八*j陋畫皿 12 ti 1v =g=國寰% M予足聿生'A * ：HS1班傳帖】*語文）_fcBCDHFHTJKLM1初一庇績城計(jì)衣£.語文30理姬爨著人教平均分*格里優(yōu)秀率三拳tc接M量離身量收也D-59的-T900-1005。即口 1EQ. 口7C.3S4. 562.1222.943口9 n

20、12. n3£PC5。即ns&2.Q33,3Wd (i丫3,125&. 494E5口. 014. n3&Q7fiEpOGS4.D如£8, 53.7232,2張長在98581+0以029.0061-.04&6. 0ME91.1阻1236. T265. Uid. o黃Utk'Ub55.072.5眠S41.533 Hl吳靈麗3T03.02d. 023.01-utrOt5107.7的.T64.3232.2相0氏00限口1LQ6p(J7啊,££2, 9,總？202.3宋立杰柒0心。33.02：.：12Q6p08訊口77.S三

21、2. 9213. &州454.028.024.013。卵的&6. 071.<鞏53Z119LS郭繼東口0n1n31.01SL"55. U70. £3S.S30.9 1136. S30. 014優(yōu)山99118. n17.01r0&ESS. 0?e.3駁g&021T.20bl. (J224.0S77. 016Ml 3M *r，心麻i-t三軍】Z/n版U除統(tǒng)i十/苑計(jì)曬到/1:制Hl能國* t自比以r八、口 O 用篇 d ;：:%.1,三里三LtttL由爭(zhēng)二,而士然后單擊 H15這個(gè)單擊格，在公式欄中輸入“=MAX（IF（年級(jí)=A15），語文

22、）”，同時(shí)按下ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊 I15這個(gè)單元格，在公式欄中輸入“=MIN（IF（年級(jí)=A15），語文）”，同時(shí)按下 ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單 擊J15單元格，在公式欄中輸入“ =SUM（語文=59）*（年級(jí)=A15）”，同時(shí)按下ctrl+shift+enter 這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊K15單元格，在公式欄中輸入“=SUM（年級(jí)二人15）*（語文=60）*（語文=79）”,同時(shí)按下 ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。單擊 L15 單元格，在公式欄中輸入“=SUM（年級(jí)=A1

23、5）*（語文=80）*（語文=100）”，同時(shí)按下 ctrl+shift+enter這三個(gè)鍵，生成一個(gè)數(shù)組公式。到此為止，對(duì)初一語文這一學(xué)科所做的工作已經(jīng)結(jié)束。接下來，仿照語文這一科，我們便可以依次作出數(shù)學(xué)，英語，政治等初一所有的學(xué)科。如圖：E MkfoscH Lied 4一孰 f+帙:二旦N:固攵件叩褊蜀鈍臣4：我入格式叫 _1_具9超。吉口四潮四-0*口 1U白晝琴£U電電，yI,£TI HI修國期 *|利' 1Z - j u m匿五型尊% 瞋£!|事事喳&ABL14， 柒 仁SUH田配Al4K（語文A8UA（語文41001）A 上BcDEFGHIJELI7 SB語文3各科強(qiáng)兒弛4西提歲七J旁手坳分黃福率優(yōu)身車二平和敕師泰島分量低