用EXCEL做一個學(xué)生成績管理系統(tǒng)

1、用EXCEL做一個學(xué)生成績管理系 統(tǒng)用EXCEL做一個學(xué)生成績管理系統(tǒng)Excel的數(shù)據(jù)處理功能的強大鮮為人知，許 多老師在教育教學(xué)的輔助應(yīng)用上對EXCEL的應(yīng) 用只局限在Excel的簡單功能應(yīng)用上，對于Excel 的函數(shù)功能卻始終停留在求和、求平均值等簡單 的函數(shù)應(yīng)用上。其實，Excel函數(shù)作為處理數(shù)據(jù)的一個最重 要手段，功能是十分強大的，許多知名企業(yè)在數(shù) 據(jù)管理上還喜歡應(yīng)用Excelo本人經(jīng)過一年多的 學(xué)習(xí)研究，在學(xué)生成績管理上應(yīng)用Excel函數(shù)可 以把它做成你可能想不到的復(fù)雜的統(tǒng)計管理表 格或者小型的數(shù)據(jù)庫系統(tǒng)。另外，應(yīng)用Excel函數(shù)功能制作出的表格具有一次設(shè)計好后可重復(fù)使用，只需要改變

2、原始數(shù) 據(jù)，就可以得到相應(yīng)的結(jié)果，并且不用重復(fù)進行 打印設(shè)置。本人所做的這個成績管理系列表格。雖然對于使用函數(shù)的高手來說，這些函數(shù)可能很熟悉, 但是，各種統(tǒng)計技巧的應(yīng)用可以為參考。我寫此 文章的目的不是為了讓教師去研究函數(shù)的應(yīng)用， 僅為了便于各個水平段的老師參考應(yīng)用在自己 的工作中去，實現(xiàn)快捷化的成績統(tǒng)計的自動生成。下面就依照整個系統(tǒng)的制作過程進行描述, 便于同志們同步制作與使用。選擇區(qū)域技巧：在填充公式或者數(shù)值時,我們常常會因為自動填充速度快拖拉位置難以 確定而煩惱。那么我們只要選中要復(fù)制填充其內(nèi) 容的這個單元格，然后用鼠標流動或者點擊滑條 翻頁找到要填充至此的那個單元格,先按住Shift

3、鍵再點擊此單元格，我們會看到整個要填充的區(qū) 域被選中。按shift鍵加上下光標鍵，可以自 由選取區(qū)域大小，直到滿意為止，再松開shift 鍵。填充技巧：在填充區(qū)域被選中的情況下我們按Ctrl鍵加enter鍵，整個區(qū)域就自動填充該數(shù) 值或者該公式。一、 總成績表的制作此表的作用是：1、原始成績的輸入；2、各班成績單的打印。成績總表的制作：1、求和。也就是計算每個學(xué)生的總分成績，圖例中是G歹！J。選中G2單元格，然 后點擊求和按鈕或者直接輸入“ = SUM（H2： P2）”按ENTER,正確后就拖拉填充至整個成績表的最后一行即可。2、求班名次。新建一列（圖中的E列）, 函數(shù)：=RANK（$G768

4、,$G$682:$G$781）函數(shù)意思：G768單元格中的數(shù)值在G682至G781中的排名。公式可以上下填充。不同的班級只須把區(qū)域范圍變一下。如10班改為“$G$782:$G$881”后填充10班的該列 區(qū)域。3、求在年級中的名次。公式與求班名次一樣。不同的是總分范圍：從第2個單元 格至最后一個單元格?？梢允謱戄斎胍部梢?在公式編輯狀態(tài)下用鼠標拖拉選中總分范圍。圖例中沒有從第2個單元格起是因為我 們在12個班分兩部分。4、插入班級一列，以便于按班級排序和按班級打印成績單。5、插入學(xué)號一列，以便于輸入學(xué)生成績時參考。6、每個班固定一定的行數(shù)并且比人數(shù)最多的班再多幾行，整十整百更好，以便于以后 公

5、式的編輯和打印各班成績單時的紙張設(shè) 置。這里前六個班每班80行，后六個班每 班100行。7、此表制好后應(yīng)輸入或者從以往的成績單 中復(fù)制來一些名單、數(shù)據(jù)便于制作其他工作 表時參考也便于編寫后面公式時可以看出 效果。二、 制作年級“前X名”的工作表1、表格樣式如圖之學(xué)生成靖與信息管理ABL cDEFGHIJKL1級名班姓名總分級名班姓名總分級名班2110李晶晶506511147310012T3112陳天然5065211472.8101124310魏百讓504.85310471.4103115411503.45411471.410212一659502.2559方整470.61051176712105

6、02569王倩君470.4103128498.65610孟楠470.41099要舉導(dǎo)到這樣一張既有級名次、班級、姓名、總分項目，又能在每次打印時不用重新排版設(shè)計,一勞永逸，我們就要做一個動態(tài)連接的表，以不 變應(yīng)萬變。請按以下步驟做:1、新建一個工作表，我們把它取名 叫“橋”。其意是在總表與前X名之間建 立一種連接和過度。我們把總表中的“級 名”、“班”、“姓名”、“總分”名列分別復(fù) 制，再在“橋”中粘貼連接，就形成下圖 中表的樣式。2、再新建一個前X名的工作表。如上圖。本例所示意為A4紙張，每張三欄, 一欄50名學(xué)生。先設(shè)計好標題后，就可 以從“橋”中選取前四列，如圖例選取前 四列的前50人的

7、區(qū)域復(fù)制。在此表中也粘貼連接于前50名預(yù)留處。依此類推,等時，只須要把“橋”中的數(shù)據(jù)按總分遞減排序即可。另外，注意每次修改完成績,“橋”中都要重排序。圖例中總分427.1來源于總成績表的R762,意為：這個成 績就是總成績表中的R列（總分列）第762個單元格的內(nèi)容（成績）。三、 及格人數(shù)、及格率、平均分、優(yōu)秀人數(shù)、優(yōu)秀率表格的設(shè)計見下頁圖，（為了能看到更多的項目，圖中的其他班級破隱藏）也可以設(shè)計成其 他形式。如果我們設(shè)計的列標與總”表各科列 標順序一致，在填充公式時會大大方便。下面介 紹的公式編寫內(nèi)容是在我們所需要顯示內(nèi)容的 單元格里進行。“語文60”制作方法：單元格中輸入60數(shù) 字后擊鼠標右

8、鍵進行設(shè)置。選擇窗體中的自定 義，再在右邊框內(nèi)選取“G/通用格式”，然后在 上面的小編輯框中編輯為“語文G/通用格式” 確定之后我們在單元格內(nèi)就看到語文60、任意改數(shù)值語文不變。其他各科方法同。同類型公式填充的方法與技巧：前面我們所提到相同公式的填充技巧，這里我介紹的是同類 型的公式填充的技巧。如我們編輯每個班的及格 人數(shù)時，不同的班級公式中僅是區(qū)域的不同，所 以我們拖拉填充公式后再逐個的改動區(qū)域數(shù)字 就行了；不同科目的填充可以用鼠標按住填充柄 向左右填充即可得到其他各科的各種數(shù)據(jù)。此表 中的“次”（既名次）這列是在所有公式填充完 成后再逐個插入列編寫（方法同班名次，只是范 圍小）。語文各項編

9、寫填充完成之后選中全部各 項名次，按住Ctrl鍵用鼠標拖拉復(fù)制到其他各科 目后的名次列既可得到其他各科的各項目班級 排名。1、及格人數(shù)和優(yōu)秀人數(shù)：公式的編輯為:=COUNTIF（總!H982:H1081,n>=n&Cl）。COUNTIF的函數(shù)意義是“計算滿足條件的單元格的數(shù)目”，其中“總!”意是工作表名稱（后面不 再重述），“H982:H1081”指的是一個區(qū)域范月（在示例中是三（12）班語文）="&C1這是表 達式的條件意思:大于等于C1單元格中的數(shù)值,這個條件是一個可以變化的值，同樣我們可以用 來計算優(yōu)秀人數(shù)，只要我們把C1中的60改為80既可。當把及格人數(shù)

10、的各班各科公式編好后復(fù)制并且改為優(yōu)秀人數(shù)，既得優(yōu)秀人數(shù)欄。注意 雙引號必須是非中文輸入狀態(tài)。公式寫完按ENTER按完成編輯。我們會看到在這個單元格里顯示的數(shù)字就是某班的語文學(xué)科及格人數(shù)。2、及格率和優(yōu)秀率：公式的編寫為：=C7/COUNTA(總!$H$982:$H$1081)。C7 是三(12)班的及格人數(shù)，COUNTA的函數(shù)意是“計算非空單元格的數(shù)目”。也就是計算某班語文學(xué) 科的參考人數(shù)o公式寫完按ENTER按完成編輯o 這時我們看到的只是一個小數(shù)而不是百分數(shù)。僅 需要在此單元格內(nèi)單擊右鍵選擇單元格格式，在 彈出的對話框里選擇數(shù)字標貼，在選擇框中找到 百分數(shù)選中，按確定即可顯示百分數(shù)。優(yōu)秀率

11、參 考及格率制作。3、平均分：公式的編寫為： =AVERAGE(總!H982:H1081)。AVERAGE 的 函數(shù)意義是：“計算某數(shù)字單元格區(qū)域的算術(shù)平 均值”。示例中意為：計算“總”工作表中H列 第982個單元格至第1081個單元格的所有非空單元格釉字的平均值C既為: ri"、"三（12）語文。 " . m = = "= exd ” /L-J UST QEILiA 1 rn = " * -.1 J 8 N. 4.C7j” 二總!H982:H108I/ABCDEFG 1 HIJKLM1項目班級語文60次英語60次數(shù)學(xué)60次政治30次歷史30次

12、物理30 15及三（10）85人490人183 A288人 290人 190人6格三（11） 89人190 人183人289人190人 190人7人三（12）87人329 人 478A 4S6人 489人3*7人11及三（10） 94. 4%4 100.0% 192.2% 297. 8% 2 100. 0% 1 100. 0%12格三（11） 98. 9%1 100. 0% 192. 2% 298. 9%1 100. （）% 1 100. 0%13軍三（12） 97.2%3 100. 0% 187. 6% 496.6% 4 100. 0% 1 97. 8%171三（10） 71.842 86.

13、 58 475.86 238. 71 141. 57 345. 50 113埒三）70.284 88.34 275. 19 338. 41 244. 62 145.77 ，人/ J二門 2173. 11189. 62 174. 06 438.39 343. 70 245. 00 /四、求各班在年級總分前X名的學(xué)生人數(shù)1、表格樣式如下圖:公式編寫為:=COUNTIF（總!$D$402:$D$501；f<=36H）。COUNTIF這個函數(shù)前面我們已經(jīng)熟悉。完整意為：計算在D列中第402個單元格至第501個 單元格中數(shù)值小于等于36的單元格數(shù)目?！?6” 是年級前36名的意思。式中的“ $D$

14、 ”指的“總” 工作表的D列（也就是學(xué)生的年級名次這一列）， 其中的“$D$”等同與“D”。的作用以及單 元格中出現(xiàn)的“人”字的方法將會在其它相關(guān)文 章中介紹。l_l 匕，g 必 *-t=J 隹3 。| -. - . J /奸At - zv ,o rrJ. 工B7二|=二。3UIfn/總!即$4D2:$D$501/"36”)ACDE1三年級第一次月考各班各段優(yōu)秀人數(shù)情況2班級前36名前50名前80名詼120名前200名5三5人8人13人21A41人6三(10)13人15人26人36460人7三(11)9人13人23人32450人2、計算各班前50名、前80名、前X名只需把公式中的36

15、改為50、80、X即可。3、自動更新前X名我們已經(jīng)設(shè)置好的前X名，有時需要變換具 體的數(shù)字。比如前36名要改為前40名等等。按第1步的公式，我們必須進行每一列的替換, 把36替換為40,那么，我們就來修改一下這個 公式，讓它能適應(yīng)前X的變化。在原有公式中:=COUNTIF(總!$D$402:$D$501,n<=36n)中的 36替換為B2單元格中的數(shù)字。公式應(yīng)該寫為： =COUNTIF( !$D$402:$D$501, ,<= "&B2)這 時又發(fā)現(xiàn)結(jié)果是“0”，這是因為B2單元格的設(shè) 置問題。我們必須把B2單元格中“前36名”改 為清除后再直接改為數(shù)字“36”，

16、這樣就會看到 我們所需要的數(shù)值。為了看到“前36名”這樣的效果，我們再回頭把B2單元格進行設(shè)置，方 法前面已經(jīng)介紹過。=COUNTIF(總!$D$402:$D$501；f<= n&B2)之后，我們會看到這樣的效果：當把B2中輸入數(shù) 字“50”時所得到的結(jié)果與C列前50名的結(jié)果 一樣。如果第一個單元格中的公式都改為:=COUNTIF(總!$D$402:$D$501；f<= n&B2)樣 式，那么，我們想要前幾名、前十幾名、前幾百 名時僅需要在列標題行改動一下數(shù)字即可。五、分段、分班、分科平均分1、首先，我們把“各班優(yōu)秀人數(shù)表”復(fù)制一份。把我們所需要的單元格進行拖拉重新

17、組ABCDEFGHIJn1三年級第二次月考分段比較情況2班級前36名語英數(shù)政歷物化5三(9)583Z7. 694. 22，645. 85g. 6g396三(10)1383.3184. 7797. 628644. 9257.5537. 27三(11)982. 89182-3395. 2287. 3347. 0657.7338.42三。2)9M 33名工n91. 2286. 6745. 5658.27389班級前50幺36 至5。語英數(shù)政歷物化12三8人3人R2. 33R2. 3387, 67E3. 6746. 335 6. 237. 913三Q0)15人2人80. 5829776, 546狙23

18、614三QD13人4人83. 583. 251582, 54656. S53715三(12)14人5人84.48384. 686, £46.455. 9235. 816班級前80名51 至 80語英數(shù)政歷物化19三13A5人83. 882.480. 881,44557.3636. 520三(10)26A11人80.6478. 0991.1882. 2742. 1855.8736. 7合(為了保證公式所引用的區(qū)域不變必須是 拖拉而非復(fù)制)為我們所需要的形式如圖：J LJ = UEI P 2 % | 一 ， 一 | /Q Z，幺/ , io 工 =| 三獷 二： 二1"氏，三。

19、"，于(總11=0,”,人7正；0而基??；總!1402,0. (, 3B7,1)力 J2、A列B列就是原表的所有內(nèi)容。我們再建一列C列為段中人數(shù)。相信這個數(shù)字的得 來大家都知道是同一個班的前50名單元格 減去前36名單元格(均用單元格的位置編 碼如B12-B5這種形式)。3、C列每個小標題：如：36至50的自動生成。編寫：=B2&”至"&B9即可；再向下 的小標題僅需要在引用單元格后面加1,如:=B9+1&"至"&B16。4、分段平均分的計算：公式：=AVERAGE(OFFSET(總!I402,0,0,$B7,l)函數(shù)示意

20、：AVERAGE是示平均值;OFFSET是一個以某個單元格為參照的區(qū)域引用 函 數(shù)AVERAGE(OFFSET(總!1402,0,0$B7,l)的表達式意思是:求 OFFSET(總!1402,0,0$B7,l)這個區(qū)域的平均值，OFFSET(總!I402,0,0$B7,l)的區(qū)域是：以 個“0”就是從本行開始向下0行為起點(即402 行)，第二個“0”就是從本列開始向右第0列為 起點(即I列)，$B7代表區(qū)域的行數(shù)(即9行), 引用“B7”的目的就是為了適應(yīng)每次成績的變化, 因為每次考試該段的人數(shù)一定是變化的，如果我 們每次改公式中的數(shù)據(jù)，那么我們編寫公式也就 沒有意義了。 “1”代表區(qū)域的列

21、數(shù)為1列（在 此即為I列）?！翱偂惫ぷ鞅淼?402這個單元格為參第一前36名至前50名這個區(qū)間的公式稍有改動 為：AVERAGE(OFFSET(總!I402$B7,0$C14,l)由于“總”工作表我們是按照班級遞增和總分遞減的順序排列的，所以前36名至前50名我 們要計算的第二個區(qū)域。這個公式中的“$B7” 的位置與上面的公式中的位置不同，所代表的意 思也不同，在這意思是：從“總”工作表1402 單元格向下第“$B7”（即9）行為起點。“$C14” 引用的是可變量（每次考試進入該段的人數(shù)是變 化的），也就是這個區(qū)域包含的行數(shù)。六、有效上線人數(shù)有效上線人數(shù)的意義在于我們能通過各分數(shù)段 學(xué)生的偏科

22、情況。見下圖:lj "七a.« .D5 發(fā)二IFaB5=0",IFCa!Hl=0",8UNT1FSFFSET(EJ.H682,0, 0, 385,1)>二&02)ABCD油FGHIJnN12-r三年級第二次月考各名次段上陵人數(shù)語74.42 英90.6 數(shù)8L5 政393物45化4工5歷463三88 16816g76三 (10)119911g9997 三 Hi)1079106910108 三(12)141213111014999班級前go名41 至 80語74.42 英90.6 數(shù)8L5 政39.8物45化475 歷46.812三20人13A

23、10896410113 =(10)19人8人446627514三)22人12A71089g91115三(12)20人6人463154416班級1前12。名81至120悟74.42 英906 數(shù)81.5 政39.8物45化475歷46319三22人8A4255224on97 KQ J.441AA公式=IF($B5=0J”,IF(總!Hl=0；nCOUNTIF(OFFSET(總!H682,0,0,$B5,l) J>=” &D2)公式 中的表 達式意義：$B5=0,即B5單元格數(shù)字為“0”時此 單元顯示空白，否則，按下一層函數(shù)執(zhí)行?？?Hl=0,意義是“H1是語文的標題，如果語 文這一

24、科沒有考試就把：“總”工作表的語文標 題清除，相應(yīng)的是D3這個單元格中也會顯示空 白(而不顯示錯誤符號)，否則，按下一函數(shù)執(zhí)行即COUNTIF(OFFSET(總!H682,0,0$B5,l)J>="& D2) o這才是真正重要的內(nèi)容?！癘FFSET(總!H682,0,0$B5,l) ” 前面已經(jīng)提到; 而這個條件卻是需要介紹的，意 義是：大于和等于D2單元數(shù)值。(而D2單元格又嵌套著一個可變函數(shù)值，下面要說到)。下面 就把整個函數(shù)式的意思解釋：“如果B5=0,則D3顯示空白;如果H1=O,貝！| D3還顯示空白; 否則顯示在“總”工作表中從H682算起向下“$B5”個(

25、這里為8個)單元中大于等于D2(這里為74.42)數(shù)值的單元格個數(shù)。這個個數(shù) 就我們所要的語文上線人數(shù)。關(guān)于D2、C2、J2的公式編寫。以D2為 例:=AVERAGE(OFFSET(總!H$682,0,0,$C$5,l), OFFSET(總!H$782,0,0$C$6,l)QFFSET(總!H$ 882,0,0$C$7,l)QFFSET(總!H$982,0,0$C$8,l)這個公式解釋起來比較簡單：求年級前200 名的語文平均分。其中，總!H$682、總!H$782、 總!H$982為各班語文在總表中的起始位置(也是參照位置),$C$5> $C$6、-$C$8 為以各參位置為準向下包括的

26、單元格個數(shù)。“$C$5、$C$6、$C$8”又是一個個可變函數(shù)。下面接著介紹。以$C$5為例：=COUNTIF(總!$F$682:$F$781 J v="&C2)這個函數(shù)前面也已經(jīng)提到過在這里只把它的 應(yīng)用意思講解一下：求三(9)班年級名次中小于等于“C2”的單元格個數(shù)。這樣我們就得出 來前200名中三（9）班有幾人，也便于前面講 到的函數(shù)進行引用。那么，C2是一個可變值，因為我們會根據(jù)需 要選擇參考前多少名的平均成績?yōu)閰⒄?。C2的 值一變，上面我們介紹的內(nèi)容也就跟著變。另外，在這里介紹一下“$”作用。只有在進行填充公式時才用得著。有些公式我們希 望隨著單元格的變化而進行變化

27、，而更多時候我 們需要引用的單元格只向行的方向變化或者只 向列的方向變化，這時就需要“$”來幫忙了。上例中的公式我們需要填充到五段中，十二個 班，七個科目的單元格中（12X5X7=420個單 元格中）。如果我們希望向左右列中的同一行填 充公式而所引用的單元格不變，那么我們只須代 表此單元格的列標字母前加上即可。就向 上面公式里的“$B7”，在該班級該段中各科引用 的都是“B7”這個單元格，我們當然希望從語文這里向右填充時B7不變化就。如果不加“$”, 有個“絕對引用”是字母前后都加“$”，但是, 不要忘了我們的公式還需要向下填充；而向下填就會隨著填充而變?yōu)镃7、D7、o有人知道充時我們又希望B

28、7會變成B8、B9、，如 果字母后面加了“$”,那向下填充時就不會變了, 我們還得一個一個的修改。最后，我想大家這樣 理解和記憶是有效的："$”就是“絕對引用”的 意思。而且，我們又知道“B7”中的B就是B列，“7”既第7行。例如：第一種情況：絕對引用某一單元格，如:“$B$7”讀作：“絕對引用B列又絕對引用7行”，這樣一來不管往個方向填充公式B7的引用不變。第二種情況(絕對引 用列)：“$B7”讀作：“絕對引用B列而不限制 為第7行”，那么填充時數(shù)字7就會隨著行的變 化而進行變化，第三種情況(絕對引用行):“ B$7 ” 讀作：“不限制B列而絕對引用第7行”，無論向 哪個方向填充都

29、引用的是第7行。七、個人查分成績單的查詢1、個人單次考試的成績查詢(也可查多人) 公式格式為：=IF($A3&$B3=nn；,nJF($A3=nn ,VLOOKUP($B3,總 ！$D$2:P$1081,13,FALSE),VLOOKUP($A3, !$C$2:P$108144,FALSE)C：如果A3和B3都為空，那么查詢也為空，否則按第二判斷；如果A3為空，貝!|按B3 內(nèi)容在“總”表中查找$D$21$1081區(qū)域首列中 與B3相同的單元格，然后返回這一行的第13 中的數(shù)據(jù)?！癋ALSE”的意思為精確查找。這樣，我們只要把公式左右填充之后修改其中的返回列數(shù)字即可得到該學(xué)生的各科成績

30、。這時我們只要再選中這一行的各科公式單元格向下填充至平時所能用的人數(shù)為止o般情況下填充30行足夠。N4 .二IF($A4&$B4一/1 IF($A4=1VL00KUP($B£ 總！ $D，2:P$1081,- I B $A4,總!$C$2:P$1081,14, FALSE)第次月考個人成績單2學(xué)號,姓名、班名次級名次性別總分語英數(shù)政歷物3閻璐、5020104242294975541624趙云姍71266o46594861055238475閆嬌56208048298105825938556閆寒1105651011131206442707游荊371470的988102966546