位置:首頁 > 軟件操作教程 > 辦公軟件 > Excel > 問題詳情

使用EXCEL制作學(xué)生成績單技巧

提問人:劉團(tuán)圓發(fā)布時(shí)間:2021-09-07

對于教師來說每次考試后整理學(xué)生成績都不是一件輕松的事情。通常收回的學(xué)生試卷并不可能按已有成績表中的順序排列,因此每次用Excel輸入成績前都得先把試卷按記錄表中的順序進(jìn)行整理排列,之后才能順次輸入,這自然是很麻煩的。實(shí)際上最快速的錄入方法應(yīng)該是按試卷的順序在Excel中逐個(gè)輸入學(xué)號和分?jǐn)?shù),由電腦按學(xué)號把成績填入成績表相應(yīng)學(xué)生的記錄行中。在Excel中實(shí)現(xiàn)這個(gè)要求并不難。


  首先我們得有一張Excel成績記錄表,然后在成績記錄表側(cè)增加四列(J:N),并輸入列標(biāo)題。


  1.表格設(shè)置  


  選中Excel表格的J1,單擊菜單;數(shù)據(jù)/有效性”,在;設(shè)置”選項(xiàng)卡中單擊;允許”的下拉列表選擇;序列”,在;來源”中輸入=$C$1:$H$1。選中K列右擊選擇;設(shè)置單元格格式”,在;設(shè)置單元格格式”窗口;數(shù)字”選項(xiàng)卡的;分類”中選中;文本”,確定設(shè)置為文本格式。


  2.輸入公式


  選中J2輸入公式=IF(ISERROR(VLOOKUP(A2,L:M,2,F(xiàn)ALSE)),"",VLOOKUP(A2,L:M,2,F(xiàn)ALSE)),按A2的學(xué)號在L:M查找并顯示相應(yīng)的分?jǐn)?shù),如果沒找到出現(xiàn)錯(cuò)誤則顯示為空。


  在Excel表格的L2輸入公式=VALUE("2007"&LEFT(K2,3)),提取K2數(shù)據(jù)左起三位數(shù)并在前面加上2007,然后轉(zhuǎn)換成數(shù)值。由于同班學(xué)生學(xué)號前面部分一般都是相同的,為了加快輸入速度我們不需要全部輸入,只要輸入學(xué)號的最后三位數(shù)即可,然后L2公式就會自動為提取的數(shù)字加上學(xué)號前相同的部分;2007”顯示完整學(xué)號。接著在M2輸入公式=VALUE(MID(K2,4,3)),提取Excel表格的K2數(shù)據(jù)從第4位以后的3個(gè)數(shù)字(即分?jǐn)?shù))并轉(zhuǎn)成數(shù)值。最后選中J2:L2單元格區(qū)域,拖動其填充柄向下復(fù)制填充出與學(xué)生記錄相同的行數(shù)。


  注:Excel的VALUE函數(shù)用于將提取的文本轉(zhuǎn)成數(shù)值。如果學(xué)號中有阿拉伯?dāng)?shù)字以外的字符,如2007-001或LS2007001,則學(xué)號就不再是數(shù)字格式而變成文本格式了,此時(shí)L2的公式就不必再轉(zhuǎn)成數(shù)值了,應(yīng)該改成="2007"&LEFT(K2,3),否則會出錯(cuò)。


  3.防止重復(fù)


  選中Excel表格的K列單擊菜單;格式/條件格式”,在;條件格式”窗口的條件1的下拉列表中選擇;公式”并輸入公式=L1=2007,不進(jìn)行格式設(shè)置。然后單擊;添加”按鈕,添加條件2,設(shè)置公式為=COUNTIF(L:L,L1)>1,單擊后面的;格式”按鈕,在格式窗口的;圖案”選項(xiàng)卡中設(shè)置底紋為紅色,確定完成設(shè)置。


  這樣,當(dāng)在Excel表格的L列中出現(xiàn)兩個(gè)相同學(xué)號時(shí),就會變成紅色顯示。按前面的公式設(shè)置,當(dāng)K列為空時(shí)L列將顯示為;2007”,因此前面條件1的當(dāng)L1=2007時(shí)不設(shè)置格式,就是為了避開這個(gè)重復(fù)。


  4.錄入成績


  需要輸入成績時(shí),先單擊Excel表格的J1后的下拉按鈕,從下拉列表中選擇要輸入的列標(biāo)題(如:期中),再選中K1輸入第一個(gè)學(xué)生的學(xué)號和成績00159,回車,后面的L1、M1就會顯示學(xué)生的學(xué)號2007001、分?jǐn)?shù)59,同時(shí)分?jǐn)?shù)會自動填寫到學(xué)號為2007001行的J列單元格。繼續(xù)輸入第二個(gè)學(xué)生學(xué)號分?jǐn)?shù)008112,回車,如此重復(fù)即可完成全部學(xué)生的成績錄入。在輸入時(shí)如果學(xué)號出現(xiàn)重復(fù),則輸入的單元格和與其重復(fù)的單元格會同時(shí)以紅色顯示,提醒你輸入可能出錯(cuò)了?! ∮捎贓xcel表格的學(xué)號和成績是按試卷順序輸入的,因此可以很方便地查出到底哪一個(gè)輸入值是錯(cuò)誤的。全部輸入完成后,只要選中J列進(jìn)行復(fù)制,再選中保存;期中”成績的E列,右擊選擇;選擇性粘貼”,在彈出的窗口中單擊選中;數(shù)值”選項(xiàng),確定即可把J列顯示的成績以數(shù)值方式粘貼到E列。


  這樣,只要順次在Excel表格中輸入學(xué)號分?jǐn)?shù)后回車就可以輕松完成成績錄入。至于新增用于輸入的J:M列內(nèi)容,平時(shí)不需要錄入成績時(shí)可選中J:M列右擊選擇;隱藏”將其隱藏起來。等需要時(shí)再拖動選中I:N列右擊選擇;取消隱藏”即可恢復(fù)。當(dāng)然你也可以一開始就把這4列的內(nèi)容建立保存在其他Excel工作表中,那就可以完全不影響原表了,不過筆者認(rèn)為還是放在同一表中會比較直觀。

繼續(xù)查找其他問題的答案?

回復(fù)(0)
返回頂部