用EXCEL輕松的準(zhǔn)備考前工作

       大考在即，主任要求“考務(wù)工作必須電子化”，為了萬(wàn)無(wú)一失，還特意提供給班主任一份考務(wù)工作流程圖：

     考前：考場(chǎng)編排→打印單科成績(jī)冊(cè)→打印考場(chǎng)記錄單→打印準(zhǔn)考證號(hào)

     考后：錄入各科成績(jī)→計(jì)算總分、平均分并排定名次→統(tǒng)計(jì)各科分?jǐn)?shù)段人數(shù)、及格率、優(yōu)秀率及綜合指數(shù)→打印各種統(tǒng)計(jì)報(bào)表→制作各科統(tǒng)計(jì)分析圖表

　　既然任務(wù)已經(jīng)明確，先把考前的準(zhǔn)備工作做好吧！具體工作包括：考場(chǎng)編排→打印單科成績(jī)冊(cè)→打印考場(chǎng)記錄單→打印準(zhǔn)考證號(hào)

　　1、單科成績(jī)冊(cè)的編制和準(zhǔn)考證號(hào)的自動(dòng)編制

　　（1）我負(fù)責(zé)的班級(jí)這次考試共7門(mén)課程，為了區(qū)分科目，只需在Excel表頭處設(shè)置“科目”、“班級(jí)”和“任課教師姓名”等?？忌男彰梢詮膶W(xué)生學(xué)籍表中提取，考號(hào)如何編制和處理呢？為了以后登分和查找方便，考號(hào)基本按學(xué)籍號(hào)順序把整個(gè)年級(jí)的考號(hào)放在一起編碼。編碼由兩部分組成：①考場(chǎng)編號(hào)（三位數(shù)字），②班內(nèi)順序號(hào)（三位數(shù)字）。為了加快錄入速度，分別把它們放在兩列中，這樣可以分別進(jìn)行填充。

　　（2）新建工作表“考場(chǎng)編號(hào)”（如圖1），從學(xué)生學(xué)籍表中復(fù)制“姓名”列置于該表A列，首先設(shè)置B（準(zhǔn)考證號(hào)）、C（班內(nèi)編號(hào)）、D（考場(chǎng)編號(hào)）列的數(shù)據(jù)格式為文本方式。  

　　（3）在B2單元格輸入公式“=D2&C2”（其中的“&”為連接運(yùn)算，將“考場(chǎng)編號(hào)”和“班內(nèi)編號(hào)”連接成一個(gè)字符串），用鼠標(biāo)選中B2單元格，將鼠標(biāo)指向該區(qū)域右下角的填充柄，雙擊鼠標(biāo)（這可是本人總結(jié)出來(lái)的最快的填充方法，下稱(chēng)“雙擊填充法”）即可將該公式自動(dòng)填充至最后一個(gè)考生（因?yàn)榇藭r(shí)還沒(méi)有在C、D列輸入數(shù)據(jù)，暫時(shí)B列中數(shù)據(jù)也沒(méi)有出現(xiàn)）。在C2單元格中輸入“001”，C3單元格輸入“002”，用鼠標(biāo)拖動(dòng)區(qū)域C2：C3，將鼠標(biāo)指向該區(qū)域右下角的填充柄，用上述“雙擊填充大法”即可對(duì)全班學(xué)生順序編號(hào)。然后每隔30人（一個(gè)標(biāo)準(zhǔn)考場(chǎng)）插入一空行，在D2和D3單元格中全部輸入“001”，同樣用“雙擊填充大法”迅速將第一考場(chǎng)的編號(hào)全部填充。此時(shí)B2：B31單元格已經(jīng)全部自動(dòng)填上了考生的準(zhǔn)考證號(hào)。對(duì)第二考場(chǎng)，只需在D33和D34格中輸入“002”，并向下填充即可。

         2、打印考場(chǎng)記錄單

　　考場(chǎng)記錄單是供監(jiān)考教師監(jiān)考時(shí)核對(duì)考生數(shù)目和身份時(shí)用的。在上述“考場(chǎng)編號(hào)”工作表中，利用Excel的“分類(lèi)匯總”功能，可以快速方便的達(dá)到這一目的。操作步驟如下：

　?。?）在表中單擊任一數(shù)據(jù)單元格，在“數(shù)據(jù)”菜單中，單擊“分類(lèi)匯總”命令。

　?。?）在“分類(lèi)字段”下拉列表框中，單擊需要用來(lái)分類(lèi)匯總的數(shù)據(jù)列（“考場(chǎng)編號(hào)”列）。

　　(3）在“匯總方式”下拉列表框中，選擇“計(jì)數(shù)”。

　?。?）在“選定匯總項(xiàng)（可有多個(gè)）”框中，復(fù)選“考場(chǎng)編號(hào)”框。

　　（5） 復(fù)選“每組數(shù)據(jù)分頁(yè)”選項(xiàng)，以便每個(gè)考場(chǎng)單打印一頁(yè)。其他兩項(xiàng)“替換當(dāng)前分類(lèi)匯總”和“匯兌結(jié)果顯示在數(shù)據(jù)下方”可根據(jù)情況選定。

　?。?）單擊[確定]，考場(chǎng)記錄單就已經(jīng)做好了（如圖2）。 　　

　　（7）為了使每一頁(yè)都打印同樣的表頭：“2001年期末考試 第X考場(chǎng) 考場(chǎng)單”，單擊菜單“文件→頁(yè)面設(shè)置→頁(yè)眉/頁(yè)腳→自定義頁(yè)眉(C)……”，進(jìn)行相關(guān)設(shè)置。為了更加美觀，在“頁(yè)面設(shè)置”的“頁(yè)邊距”窗口復(fù)選“水平居中”和“垂直居中”，并定義好紙張的大小。

　　說(shuō)明：

　　（1）“分類(lèi)匯總”前，必須按“考場(chǎng)編號(hào)”列排序，本例中已經(jīng)是排好順序的。

　?。?）對(duì)“分類(lèi)匯總結(jié)”的結(jié)果不滿(mǎn)意時(shí)，可以清除“分類(lèi)匯總”，Excel將同時(shí)清除分級(jí)顯示和插入“分類(lèi)匯總”時(shí)產(chǎn)生的所有自動(dòng)分頁(yè)符。 方法是：?jiǎn)螕羧我粏卧?，在“?shù)據(jù)”菜單中，選擇“分類(lèi)匯總”命令，單擊[全部刪除]按鈕。

        3、打印準(zhǔn)考證號(hào)碼條

　　準(zhǔn)考證號(hào)碼條一般粘貼在課桌左上角，供考生尋找自己的座位用，過(guò)去主要靠手工抄寫(xiě)的方法完成。這里我用word的“郵件合并”功能巧妙地完成了這一任務(wù)。方法如下：

　　啟動(dòng)word 2000，執(zhí)行“工具”菜單下的“郵件合并”命令，彈出“郵件合并幫助器”，首先在當(dāng)前窗口創(chuàng)建一個(gè)郵件標(biāo)簽。依次點(diǎn)擊“創(chuàng)建→郵件標(biāo)簽→活動(dòng)窗口”，然后點(diǎn)擊[獲取數(shù)據(jù)]按鈕，在下拉列表中選“打開(kāi)數(shù)據(jù)源”，在文件類(lèi)型列表框中把文件類(lèi)型改為“MS Excel工作簿”，打開(kāi)剛才“考場(chǎng)編號(hào)”工作表，按[確定]按鈕。接著設(shè)置主文檔，點(diǎn)擊[新建標(biāo)簽]按鈕，彈出如圖3所示“新建自定義標(biāo)簽”對(duì)話框。　　 　　

　　在“標(biāo)簽名稱(chēng)”文本框中填上“座位號(hào)”，在“頁(yè)面尺寸”下拉列表框中選“A4橫向”。按[確定]按鈕，出現(xiàn)“創(chuàng)建標(biāo)簽”對(duì)話框。點(diǎn)擊“插入合并域”，插入“準(zhǔn)考證號(hào)”、“姓名”兩個(gè)域，按[確定]按鈕，最后按[合并]按鈕。

　　好，現(xiàn)在的考前準(zhǔn)備工作基本完成，只等考試成績(jī)出來(lái)了！

       用EXCEL輕松的處理學(xué)生成績(jī)

     期末考試結(jié)束后，主任要求班主任自已統(tǒng)計(jì)本班成績(jī)，盡快上報(bào)教導(dǎo)處。流程包括錄入各科成績(jī)→計(jì)算總分、平均分并排定名次→統(tǒng)計(jì)各科分?jǐn)?shù)段人數(shù)、及格率、優(yōu)秀率及綜合指數(shù)→打印各種統(tǒng)計(jì)報(bào)表→制作各科統(tǒng)計(jì)分析圖表等。有了Excel，我們可用不著躬著身、駝著背、拿著計(jì)算器一個(gè)一個(gè)算著學(xué)生的成績(jī)了！

　　我迅速地打開(kāi)電腦，啟動(dòng)Excel2000，錄入學(xué)生的考試成績(jī)，如圖1所示。然后在J2單元格處輸入公式"=sum(c2:i2)",然后拖動(dòng)填充柄向下填充，便得到了每人的總分。接著在k2單元格處輸入公式"=average(c2:i2)"，然后拖動(dòng)填充柄向下填充，便得到了每人的平均分。

　　平均分只需保留一位小數(shù)，多了沒(méi)用。所以選中第k列，用鼠標(biāo)右鍵單擊，從彈出的快捷菜單中選"設(shè)置單元格格式（F）…",如圖2所示，在數(shù)字標(biāo)簽中選中"數(shù)值"，小數(shù)位數(shù)設(shè)置為1位?！?　

　　下面按總分給學(xué)生排出名次。

     在L2單元格處輸入公式"RANK(J2,J$2:J$77,0)",然后拖動(dòng)填充柄向下填充，即可得到每人在班中的名次（請(qǐng)參考圖1）。

　　說(shuō)明：此處排名次用到了RANK函數(shù)，它的語(yǔ)法為：

　　RANK(number,ref,order)

　　其中number為需要找到排位的數(shù)字。

　　Ref為包含一組數(shù)字的數(shù)組或引用。Ref 中的非數(shù)值型參數(shù)將被忽略。

　　Order為一數(shù)字，指明排位的方式。

　　·如果 order 為 0 或省略，Microsoft Excel 將 ref 當(dāng)作按降序排列的數(shù)據(jù)清單進(jìn)行排位。

　　·如果 order 不為零，Microsoft Excel 將 ref 當(dāng)作按升序排列的數(shù)據(jù)清單進(jìn)行排位。

　　最后，單擊L1單元格，然后在“工具”菜單中選“排序”－＞“升序”，即可按照名次順序顯示各學(xué)生成績(jī)。

　　另外，我們還希望把不及格的學(xué)科突出顯示，最好用紅色顯示。于是拖拉選擇C2:E78(即所有學(xué)生語(yǔ)、數(shù)、外三科成績(jī))，然后執(zhí)行"格式"菜單下"條件格式"命令，彈出"條件格式對(duì)話框"。我們把條件設(shè)為小于72分的用紅色顯示（因?yàn)檫@三科每科總分為120分），點(diǎn)擊"格式"按鈕，把顏色設(shè)為紅色。再按"確定"按鈕。然后用同樣的方法把理、化、政、歷四科小于60分的也用紅色顯示（因?yàn)檫@四科每科總分為100分）。

　　下面我們來(lái)統(tǒng)計(jì)各科的分?jǐn)?shù)段以及及格率、優(yōu)生率、綜合指數(shù)等。

        下面我們來(lái)統(tǒng)計(jì)各科的分?jǐn)?shù)段以及及格率、優(yōu)生率、綜合指數(shù)等。

　 （1）60分以下人數(shù)：在C78單元格處輸入公式"=COUNTIF(C2:C77,"〈60")"，拖動(dòng)填充柄向右填充至I78單元格處；

　 （2）60分~69分人數(shù)：在C79單元格處輸入公式"=COUNTIF(C2:C77,"〉=60")-COUNTIF(C2:C77,"〉=70")"，拖動(dòng)填充柄向右填充；

　 （3）70分~79分人數(shù)：在C80單元格處輸入公式"=COUNTIF(C2:C77,"〉=70")-COUNTIF(C2:C77,"〉=80")"，拖動(dòng)填充柄向右填充；

　 （4）80分~89分人數(shù)：在C81單元格處輸入公式"=COUNTIF(C2:C77,"〉=80")-COUNTIF(C2:C77,"〉=90")"，拖動(dòng)填充柄向右填充；

　 （5）90分以上人數(shù)：在C82單元格處輸入公式"=COUNTIF(C2:C77,"〉=90")"，拖動(dòng)填充柄向右填充；

　 （6）平均分：在C83單元格處輸入公式"=AVERAGE(C2:C77)",拖動(dòng)填充柄向右填充至I83；

　 （7）最高分：在C84單元格處輸入公式"=MAX(C2:C77)",拖動(dòng)填充柄向右填充至I84；

　 （8）低分率：是指各科40分以下人數(shù)與總?cè)藬?shù)的比值。在C85單元格處輸入公式"=COUNTIF(C2:C77,"〈=40")/COUNT(C2:C77)*100"，拖動(dòng)填充柄向右填充至I85；

　 （9）及格率：語(yǔ)、數(shù)、外三科及格分為72分，所以在C86單元格處輸入公式"=(COUNTIF(C2:C77,"〉=72")/COUNT(C2:C77))*100"，并拖動(dòng)填充柄向右填充至E86；而理、化、政、歷等四科及格分60分，所以在F86單元格處輸入公式"=(COUNTIF(F2:F77,"〉=60")/COUNT(F2:F77))*100"，并拖動(dòng)填充柄向右填充至I86；

　 （10）優(yōu)生率：語(yǔ)、數(shù)、外三科96分以上為優(yōu)生，所以在C87單元格處輸入公式"=(COUNTIF(C2:C77,"〉=96")/COUNT(C2:C77))*100"，拖動(dòng)填充柄向右填充至E87；理、化、政、歷等四科80分以上為優(yōu)生，所以在F87單元格處輸入公式"=(COUNTIF(F2:F77,"〉=80")/COUNT(F2:F77))*100"，拖動(dòng)填充柄向右填充至I87處；如圖3所示。

　 （11）綜合指數(shù)：我們學(xué)校的綜合指數(shù)的計(jì)算公式為z=[(1+優(yōu)生率－低分率)/2+及格率+平均分/該科總分]/3。所以在C88單元格處輸入公式"=((1+C87/100-C85/100)/2+C86/100+C83/120)/3"，拖動(dòng)填充柄向右填充至E88；在F88單元格處輸入公式"=((1+F87/100-F85/100)/2+F86/100+F83/100)/3"，拖動(dòng)填充柄向右填充至I88。如圖3所示。

　　對(duì)了，為了讓別人對(duì)各科的分?jǐn)?shù)段有一個(gè)較直觀的認(rèn)識(shí)，可以考慮采用圖表。單擊“插入”菜單中“圖表”命令，彈出“圖表向?qū)А睂?duì)話框，在“圖表類(lèi)型”列表框中選擇一種圖型，如“餅圖”，單擊“下一步”，單擊“數(shù)據(jù)區(qū)域”文本框右邊的壓縮列表框，拖拉選擇B78：C82，再次點(diǎn)擊該壓縮列表框；單擊“下一步”，輸入圖表標(biāo)題，如“高一（1）班語(yǔ)文成績(jī)分析圖”；單擊“下一步”，再單擊“完成”。如圖4所示。其它各科同樣處理，但在拖拉選擇數(shù)據(jù)區(qū)域時(shí)，因?yàn)槭遣贿B續(xù)的區(qū)域，所以要按住“Ctrl”鍵。好！一切OK！

　　且慢！為了以后的考試中不再重復(fù)上述繁瑣的工作，最好把上述工作表另存為一個(gè)模板。于是我把上述工作表復(fù)制一份到另一工作簿中，然后刪掉所有學(xué)生的單科成績(jī)（即表中C2:I77部分），執(zhí)行"文件"菜單中的"另存為"命令，在"保存類(lèi)型"下拉列表框中選"模板（*.xlt）"，把它保存為一個(gè)模板文件，這下可以一勞永逸了。

        Excel快速查找成績(jī)表中出錯(cuò)的號(hào)碼

        為了高考，學(xué)校購(gòu)進(jìn)了光電閱卷機(jī)，方便了老師們的閱卷，而且還可以很方便地將學(xué)生的卷一部分成績(jī)導(dǎo)出到Excel表格。老師們只需要將每位學(xué)生的卷二部分得分再輸入到Excel表格中，兩部分成績(jī)相加，就可以得到該生該科目的最后成績(jī)了。看起來(lái)似乎很簡(jiǎn)單，不過(guò)，卷一成績(jī)表中只有學(xué)生的準(zhǔn)考證號(hào)而沒(méi)有姓名信息，又經(jīng)常有個(gè)別學(xué)生將自己的準(zhǔn)考證號(hào)涂錯(cuò)，結(jié)果學(xué)生的卷一、卷二的號(hào)、名對(duì)應(yīng)不起來(lái)，把個(gè)簡(jiǎn)單的事情搞得挺麻煩。那么能不能利用Excel現(xiàn)有的功能，把這些出錯(cuò)學(xué)生的姓名快速找出來(lái)呢？答案當(dāng)然應(yīng)該是肯定的。    

        如圖1所示，這是一份卷一和卷二成績(jī)表。前者系自動(dòng)生成，而后者我們可以手工輸入并將它和卷一成績(jī)表放到同一個(gè)工作簿中。如果有錯(cuò)誤考號(hào)出現(xiàn)的話，那么通常出現(xiàn)在“卷一成績(jī)”工作表中。

1

        我們要做的是：?jiǎn)螕簟熬硪怀煽?jī)”工作表中的C1：E！單元格，分別輸入“卷二分?jǐn)?shù)”、“總分”、“姓名”，然后單擊C2單元格，輸入如下公式：=VLOOKUP($A2,卷二成績(jī)!$A$2:$C$21,3,FALSE)，按下回車(chē)鍵，現(xiàn)在，應(yīng)該可以看到該單元格出現(xiàn)了該同學(xué)的卷二分?jǐn)?shù)。單擊D2單元格，輸入公式：=SUM(B2:C2)，按下回車(chē)鍵后計(jì)算出該同學(xué)的實(shí)際分?jǐn)?shù)。單擊E2單元格，輸入公式：=VLOOKUP($A2,卷二成績(jī)!$A$2:$C$21,2,FALSE)，按下回車(chē)鍵就可以查找到該考號(hào)所對(duì)應(yīng)的學(xué)生姓名了。

     選中C2：E2單元格，將鼠標(biāo)移至右下角的填充句柄，向下拖動(dòng)至最后一行，松開(kāi)鼠標(biāo)，則所有學(xué)生的卷二成績(jī)、總分及相應(yīng)姓名都可以顯示出來(lái)了。如果準(zhǔn)考證號(hào)涂寫(xiě)有錯(cuò)誤的話，則會(huì)出現(xiàn)錯(cuò)誤信息進(jìn)行必要的提醒。如圖2所示。怎么樣？不錯(cuò)吧！

2

        點(diǎn)擊“卷二成績(jī)”工作表。單擊D1單元格和E1單元格，分別輸入“卷一分?jǐn)?shù)”和“總分”。然后單擊中D2單元格，輸入如下內(nèi)容：=VLOOKUP($A2,卷一成績(jī)!$A$2:$C$21,2,FALSE)，按下回車(chē)鍵，顯示該同學(xué)卷一成績(jī)。單擊E2單元格，輸入公式：=SUM(C2:D2)，按下回車(chē)鍵，顯示總分。將鼠標(biāo)移至右下角的填充句柄，按下鼠標(biāo)向下拖動(dòng)到最后一行。則可以得到全體學(xué)生的全部成績(jī)。如果“卷一成績(jī)”工作表中準(zhǔn)考證號(hào)涂寫(xiě)有錯(cuò)，那么同樣會(huì)出現(xiàn)相關(guān)提示。如圖3所示。綜合兩份工作表中的相關(guān)信息，現(xiàn)在我們很容易就可以看出本例是831160207同學(xué)出錯(cuò)了。

3

        您可以把這個(gè)工作簿保存起來(lái)，以后每次考試，只需要把兩份成績(jī)分別拷貝到相應(yīng)的工作表中就可以了。方便吧？
        用EXCEL進(jìn)行學(xué)生成績(jī)管理小技巧

　　一、快速轉(zhuǎn)換學(xué)生考試成績(jī)等級(jí)

　　有的時(shí)候，會(huì)遇到要將學(xué)生的考試成績(jī)按實(shí)際考試分?jǐn)?shù)轉(zhuǎn)換成相應(yīng)成績(jī)等級(jí)的情況，如將考試成績(jī)?cè)?0分以上的成績(jī)轉(zhuǎn)換成“A+”形式，85-89分的成績(jī)轉(zhuǎn)換成“A”形式...。一般情況，在EXCEL表格中大家會(huì)采用IF()函數(shù)來(lái)設(shè)計(jì)公式進(jìn)行轉(zhuǎn)換，這樣所設(shè)計(jì)的公式會(huì)變得很復(fù)雜，如果進(jìn)行轉(zhuǎn)換的成績(jī)等級(jí)類(lèi)型超過(guò)IF()函數(shù)的最大嵌套(7層)時(shí)，IF()函數(shù)就無(wú)能為力了。這時(shí)我們可用如下的方法來(lái)簡(jiǎn)化操作。

　　1、打開(kāi)學(xué)生成績(jī)工作表(格式內(nèi)容見(jiàn)圖1)。

　　                                                                           圖1

　　2、在G2到I12單元格錄入考試成績(jī)分?jǐn)?shù)段與考試成績(jī)等級(jí)對(duì)照表。

　　3、在D3單元格錄入公式“=INDEX(I$3:I$12,MATCH(1,(C3>=G$3:G$12)*(C3<=H$3:H$12),0))”，由于該公式為數(shù)組公式，在錄入完上述內(nèi)容后，必須同時(shí)按下“Ctrl+Shift+Enter”鍵，為上述公式內(nèi)容加上數(shù)組公式標(biāo)志即大括號(hào)“{}”。該公式的作用就是，根據(jù)C3單元格中的學(xué)生成績(jī)，在D3單元格自動(dòng)將該成績(jī)轉(zhuǎn)換成相應(yīng)的成績(jī)等級(jí)。

　　4、將光標(biāo)移到D3單元格，向下拖動(dòng)填充柄至D12單元格，將公式進(jìn)行快速?gòu)?fù)制，這樣就可以迅速完成轉(zhuǎn)換學(xué)生成績(jī)等級(jí)的工作(效果見(jiàn)圖2)。

　　                                                                圖2

　　5、還可以按照自己的喜好，將G2至I12的單元格區(qū)域設(shè)置為“隱藏”，以使表格更加美觀。

        巧用Excel函數(shù)統(tǒng)計(jì)體育成績(jī)    

　　用Excel計(jì)算學(xué)生學(xué)習(xí)成績(jī)已經(jīng)是一個(gè)老話題了，但很少有用Excel來(lái)計(jì)算體育成績(jī)。體育成績(jī)跟其它成績(jī)不同，學(xué)生測(cè)試以后沒(méi)有直接得出分?jǐn)?shù)，而是一項(xiàng)一項(xiàng)的數(shù)據(jù)，如100米跑了12秒這樣的數(shù)據(jù)。然后要把這些數(shù)據(jù)按照《國(guó)家體育鍛煉標(biāo)準(zhǔn)》折合成各項(xiàng)分?jǐn)?shù)，最后還要將各項(xiàng)分?jǐn)?shù)按一定比例累加起來(lái)得出最終的體育成績(jī)。

　　如果用Excel的LOOKUP函數(shù)來(lái)做就顯得非常簡(jiǎn)單，你只要在Excel中進(jìn)行幾個(gè)簡(jiǎn)單的操作就可以計(jì)算出學(xué)生體育成績(jī)。下面我們看看如何用Excel來(lái)計(jì)算學(xué)生的體育成績(jī)：

　　Step1 錄入各項(xiàng)成績(jī)

　　體育科目里有些成績(jī)，如中長(zhǎng)跑是以時(shí)間來(lái)記分的，一個(gè)一個(gè)錄入時(shí)間太麻煩了，所以為了提高錄入速度，對(duì)于要求輸入時(shí)間格式的單元格(如我們表中第D列)，可以選定D列，然后點(diǎn)擊“格式→單元格”，在“數(shù)字”選項(xiàng)卡下選擇“自定義”格式， 暗ピ 窀袷健弊遠(yuǎn)ㄒ邐 ?#′##″”型(圖1)。這樣當(dāng)我們需要錄入時(shí)間“2′45″”時(shí)，只要在單元格中錄入“245”回車(chē)即可得到，給我們錄入時(shí)間成績(jī)帶來(lái)極大的方便。

　　在每一項(xiàng)(列)成績(jī)錄完后應(yīng)在該列右邊空上一列用來(lái)給這項(xiàng)成績(jī)打分(圖2)。如果你經(jīng)常用到這個(gè)評(píng)分表，還不如把這個(gè)文件保存為模板文件(*.XLT)，下次用到的時(shí)候就不必重新設(shè)置了。

        

          1．考試混合編，成績(jī)統(tǒng)計(jì)--老方法遇到新問(wèn)題

　　關(guān)于使用Excel進(jìn)行學(xué)生成績(jī)處理，已經(jīng)是老話題了。但在實(shí)際工作中還是會(huì)有很多新問(wèn)題，例如，現(xiàn)在很多學(xué)校都是全年級(jí)各班混在一起考試，以防考試改卷中的不正當(dāng)競(jìng)爭(zhēng)。而統(tǒng)計(jì)成績(jī)時(shí)，則是將已判分但未拆封的考卷統(tǒng)一交到教務(wù)處，先按座位號(hào)順序(每本考卷的自然順序)錄入各科分?jǐn)?shù)，再分析統(tǒng)計(jì)出全年級(jí)各科成績(jī)。舉例說(shuō)明，如圖1(記錄11至830隱藏了)，要統(tǒng)計(jì)二(1)班優(yōu)秀人數(shù)，傳統(tǒng)做法就是先按考試號(hào)排序，再通過(guò)公式“=COUNTIF(分?jǐn)?shù)!D2:D69,">=96")”求出。它的弊端是要手工逐個(gè)修改 “D2:D69”這個(gè)參數(shù)中的兩個(gè)行號(hào)(2和69)，這可是一項(xiàng)工作量很大的工作。當(dāng)然，簡(jiǎn)單的方法還是有的，往下看吧。

　　2．初步準(zhǔn)備--考試號(hào)里提班級(jí)

　　如圖1，從B列的考試號(hào)中取出前三位(班級(jí)編號(hào))放在S列，即在單元格S2輸入公式“=LEFT(B2,3)”，然后雙擊(或拖動(dòng))S2單元格右下角的填充柄即可。

　　3．再做輔表--班級(jí)等級(jí)二合一

　　在圖1所示的工作簿中再新建一工作表，并將其命名為“等級(jí)”，在單元格A1中輸入公式“=分?jǐn)?shù)!A1”，回車(chē)，選定A1，按住A1右下角的填充柄向右下拖至C840單元格，將“分?jǐn)?shù)”工作表中的姓名、考號(hào)、座位號(hào)引用到“等級(jí)”工作表中(注意，千萬(wàn)不能復(fù)制粘貼過(guò)來(lái)，這樣不能保持兩表數(shù)據(jù)的一致性)。再選定C1，按住C1右下角的填充柄向右拖至L1單元格，將語(yǔ)文、數(shù)學(xué)等9個(gè)學(xué)科科目引用過(guò)來(lái)。接著，在D2單元格中輸入IF嵌套公式“=IF(分?jǐn)?shù)!D2>=96,分?jǐn)?shù)!$S2&&"a",IF(分?jǐn)?shù)!D2>=72,分?jǐn)?shù)!$S2&&"b",IF(分?jǐn)?shù)!D2<48,分?jǐn)?shù)!$S2&&"d",分?jǐn)?shù)!$S2&&"c")))”。D2單元格中公式的含義是：看“分?jǐn)?shù)!D2”單元格中的分?jǐn)?shù)(即“分?jǐn)?shù)”工作表中李?lèi)偟恼Z(yǔ)文分?jǐn)?shù))是否大于等于96。如果是，則在D2單元格中填入“201a”——“分?jǐn)?shù)”工作表中S2單元格中的字符“201”加上“a”(“201”表示二(1)班，“a”表示成績(jī)等級(jí)為“優(yōu)秀”)；如果不是(即小于96)，再看是否大于等于72。如果是，則在D2單元格中填入“201b”；如果不是(即小于72)，再看是否小于48。如果是，則在D2單元格中填入“201d”；如果不是(即小于72大于48)，則在D2單元格中填入“201c”。最后按住D2單元格右下角的填充柄向右下拖至L840單元格，就可以將每個(gè)學(xué)生各科成績(jī)的等級(jí)及所屬班級(jí)都填好了

　　4．最終統(tǒng)計(jì)--所需數(shù)據(jù)瞬間齊

　　輔表制好之后，言歸正傳回到“統(tǒng)計(jì)”工作表(如圖2)中，在A17到E28單元格區(qū)域中利用自動(dòng)填充功能再制作一小塊輔助數(shù)據(jù)(如圖2)。

        每次考試結(jié)束之后，最讓老師頭痛的就是成績(jī)查詢(xún)了?，F(xiàn)在，我們可以利用Excel 2000的系統(tǒng)函數(shù)建立一個(gè)成績(jī)查詢(xún)系統(tǒng)，只要您在指定單元格中輸入欲查詢(xún)成績(jī)的學(xué)生姓名，他的各科考試成績(jī)和名次就會(huì)自動(dòng)顯示出來(lái)。非常便利。

　　在Excel中，雙擊“Sheet1”，將其命名為“成績(jī)統(tǒng)計(jì)”，然后建立一個(gè)如圖1所示的成績(jī)統(tǒng)計(jì)表，輸入全部學(xué)生的各科成績(jī)備用。用常規(guī)的方法計(jì)算出各學(xué)生的總分，并排出名次（這里就不詳述做法了）。

圖1

　　雙擊“Sheet2”，將其命名為“成績(jī)查詢(xún)”。在“成績(jī)查詢(xún)”工作表中，建立如圖2所示的表格。單擊B2單元格，輸入欲查詢(xún)成績(jī)的學(xué)生姓名。單擊B3單元格，在其中輸入“=VLOOKUP($B$2,成績(jī)統(tǒng)計(jì)!$B $3:$I$56,2,FALSE)”，按回車(chē)鍵，則可以得到該名學(xué)生的語(yǔ)文成績(jī)；單擊B4單元格，在其中輸入“=VLOOKUP($B$2,成績(jī)統(tǒng)計(jì)!$B$3:$I$56,3,FALSE)”，按回車(chē)鍵，得到數(shù)學(xué)成績(jī)；單擊B5單元格，在其中輸入“=VLOOKUP($B$2,成績(jī)統(tǒng)計(jì)!$B$3:$I$56,4,FALSE)”，按回車(chē)鍵，得到英語(yǔ)成績(jī)；單擊B6單元格，在其中輸入“=VLOOKUP($B$2,成績(jī)統(tǒng)計(jì)!$B$3:$I$56,5,FALSE)”，按回車(chē)鍵，得到機(jī)械成績(jī)；單擊B7單元格，在其中輸入“=VLOOKUP($B$2,成績(jī)統(tǒng)計(jì)!$B$3:$I$56,6,FALSE)”，按回車(chē)鍵，得到電工成績(jī)；單擊B8單元格，在其中輸入“=VLOOKUP($B$2,成績(jī)統(tǒng)計(jì)!$B$3:$I$56,7,FALSE)”，按回車(chē)鍵，得到總分；單擊B9單元格，在其中輸入“=VLOOKUP($B$2,成績(jī)統(tǒng)計(jì)!$B$3:$I$56,8,FALSE)”，按回車(chē)鍵，得到名次。到此，我們就可以得到該名學(xué)生的全部考試成績(jī)以及總分、名次了。

圖2

　　有了這個(gè)，以后我們只需要在“成績(jī)查詢(xún)”工作表的B2單元格輸入要查詢(xún)成績(jī)的學(xué)生姓名，然后回車(chē)，則相關(guān)的信息就立刻可以顯示?，F(xiàn)在，查詢(xún)成績(jī)?cè)摵茌p松了吧！

        Excel函數(shù)自動(dòng)判斷、填充信息

　　筆者的一位朋友在一所大學(xué)的學(xué)生信息中心工作，她常常需要用Excel制作如圖1所示的學(xué)生信息表?？梢钥闯鲞@張表格有來(lái)自不同年級(jí)、學(xué)院和系別的同學(xué)，由于這個(gè)原因，平時(shí)她基本上都是手動(dòng)輸入這些信息的。

　　由于筆者也完成過(guò)類(lèi)似的任務(wù)，知道位于第一列的學(xué)號(hào)是按一定規(guī)律編排的。為了提高工作效率，其實(shí)可以根據(jù)這個(gè)規(guī)律，結(jié)合Excel的公式和函數(shù)快速地從學(xué)號(hào)中提取出相應(yīng)的信息，自動(dòng)填入到相應(yīng)的單元格中。當(dāng)數(shù)據(jù)量比較大時(shí)，這種方法的優(yōu)越性非常明顯。

　　一、前提條件：了解學(xué)號(hào)的編排規(guī)律

　　利用這個(gè)方法的前提條件是學(xué)號(hào)是按一定規(guī)律編排的，在實(shí)際情況中幾乎都是如此。例如，圖1表格中學(xué)號(hào)的編排規(guī)律是：學(xué)號(hào)的第一位數(shù)代表生源地區(qū)，如果是“5”就代表“外地生源”，如果是“4”就代表“本地生源”。第二至第三位代表年級(jí)，第四至第六位代表學(xué)院，比如“110”代表“數(shù)科院”，“111”代表“信息學(xué)院”等。第七至第八位代表“系別”，比如“24”代表“數(shù)學(xué)系”，“27”代表“計(jì)算機(jī)系”等，最后三位代表所在班級(jí)的學(xué)號(hào)。

　　二、 使用IF函數(shù)建立公式從學(xué)號(hào)中提取信息

　　1．由于“學(xué)號(hào)”的數(shù)據(jù)類(lèi)型為文本，因此設(shè)置A列的單元格格式的數(shù)據(jù)類(lèi)型為“文本”。

　　2．選中C2單元格，建立根據(jù)學(xué)號(hào)得出學(xué)生生源信息數(shù)據(jù)的公式。在C2單元格中輸入的公式為：“=IF(A2="","",IF(LEFT(A2,1)="5","外地生源",IF(LEFT(A2,1)="4","本地生源","無(wú)效的生源代碼")))”。這個(gè)公式的含義是：如果在A2中還沒(méi)填入學(xué)號(hào)，就返回一個(gè)空字符串，如果填入的學(xué)號(hào)的第一位字符為“5”，則返回字符“外地生源”，如果第一位字符為“4”，則返回“本地生源”，如果是其他字符，就返回“無(wú)效的生源代碼”。輸入公式后，利用公式填充柄復(fù)制公式到下方恰當(dāng)?shù)膯卧瘛：竺嬲?qǐng)每次輸入公式后，都復(fù)制到恰當(dāng)?shù)膯卧瘛?（函數(shù)具體用法請(qǐng)參閱《Excel常用函數(shù)及實(shí)例剖析》）

　　小提示：由于學(xué)號(hào)的數(shù)據(jù)類(lèi)型為文本，而不是數(shù)值，所以在條件“LEFT(A2,1)="5"”中，要對(duì)數(shù)字5加引號(hào)。下面的情況同理。

　　3．選中D2單元格，建立根據(jù)學(xué)號(hào)得出學(xué)生年級(jí)數(shù)據(jù)的公式。在D2單元格中輸入的公式為：“=IF(A2="","",MID(A2,2,2)&"級(jí)")”。其中MID(A2,2,2)的含義是，從A2學(xué)號(hào)中的第二位開(kāi)始，提取二個(gè)字符。

　　4．選中E2單元格，建立根據(jù)學(xué)號(hào)得出學(xué)生學(xué)院的公式。在E2單元格中輸入的公式為：“=IF(MID(A2,4,3)="110","數(shù)科院",IF(MID(A2,4,3)="111","信息學(xué)院",IF(MID(A2,4,3)="112","外語(yǔ)學(xué)院","無(wú)效的學(xué)院代碼")))”。這個(gè)公式的含義是：如果代表學(xué)院的字符串為“110”，則返回“數(shù)科院”；若是“111”，則返回“信息學(xué)院”；若是“112”則返回“外語(yǔ)學(xué)院”，若是其他情況，則返回“無(wú)效的學(xué)院代碼”。

　　小提示：這里嵌套使用了IF函數(shù)，由于IF函數(shù)最多只能嵌套七層，因此最多可以根據(jù)代碼返回八個(gè)學(xué)院。如果實(shí)際情況中需要使用的學(xué)院數(shù)目超過(guò)了八個(gè)，則可以使用自定義函數(shù)。

      用Excel實(shí)現(xiàn)網(wǎng)絡(luò)化的考試

        早就想設(shè)計(jì)一個(gè)在線的網(wǎng)絡(luò)考試系統(tǒng)，但是筆者的編程技術(shù)有限，暫時(shí)還無(wú)法完成一個(gè)正規(guī)的考試系統(tǒng)。后來(lái)經(jīng)過(guò)摸索，筆者使用Excel完成了一個(gè)簡(jiǎn)單的考試系統(tǒng)，經(jīng)過(guò)幾次考試的實(shí)驗(yàn)與改進(jìn)，完全可以在平時(shí)的教學(xué)中運(yùn)用?，F(xiàn)把實(shí)現(xiàn)的方法與大家分享。

        服務(wù)器端設(shè)置

　　首先在教師機(jī)上建立一個(gè)test文件夾，并建立兩個(gè)電子表格文件，分別命名為“test.xls”和“匯總.xls”，其中“test.xls”為試卷文件，“匯總.xls”是用來(lái)匯總的文件。同時(shí)在該文件夾內(nèi)建立58個(gè)文件夾（假設(shè)58個(gè)學(xué)生機(jī)），可以通過(guò)批處理的方法實(shí)現(xiàn)：

　　md s1

　　md s2

　　……

　　md s58

　　然后再將test.xls文件分別復(fù)制到這58個(gè)文件夾中：我們可以首先向s1中復(fù)制一個(gè)test.xls文件，然后再執(zhí)行copy.dat，copy.dat內(nèi)容如下：

　　copy s1\test.xls s2

　　copy s2\test.xls s3

　　……

　　copy s56\test.xls s57

　　copy s57\test.xls s58

　　現(xiàn)在每個(gè)文件夾中都有一個(gè)test.xls了，下面我們編輯test.xls和匯總.xls文件。

　　1． 試卷test．xls的設(shè)置

　　test.xls如圖1所示（假設(shè)以20道選擇題為例）。其中C1單元格為姓名填寫(xiě)處，E1單元格為學(xué)號(hào)填寫(xiě)處，A3～A22為答案填寫(xiě)區(qū)（20道題），選擇題的選項(xiàng)用數(shù)字“1、2、3、4”，不要用字母，以防止學(xué)生輸入字母與空格的混合。并把答題區(qū)的顏色設(shè)置為灰色，防止學(xué)生抄襲。

　　2． 匯總．xls的設(shè)置

　　如圖2所示，建立兩個(gè)表，第一個(gè)為答案表，用來(lái)收集試卷的第一列的答案和學(xué)號(hào)姓名。A2單元格為“='D:\test\s1\[test.xls]選擇填空題'!$E$1”（統(tǒng)計(jì)學(xué)號(hào)），B2單元格為“=D:\test\s1\[test.xls]選擇填空題'!$C$1”（統(tǒng)計(jì)姓名），C2:V2分別為“='D:\test\s1\[test.xls]選擇填空題'!$A$3”（統(tǒng)計(jì)答案）、“='D:\test\s1\[test.xls]選擇填空題'!$A$4”……“='D:\test\s1\[test.xls]選擇填空題'!$A$22”。

　　然后選中A2:V2，將鼠標(biāo)移動(dòng)到右下角，變成黑色十字的時(shí)候，向下拖動(dòng)到第59行（假設(shè)有58臺(tái)學(xué)生機(jī)），此時(shí)第3～59行與第2行是相同的，都是“='D:\test\test\s1\[test.xls]……”，這里需要改一下，第4行改為s2、第5行該為s3，依次類(lèi)推。為了更快捷，也可以使用“編輯”菜單中的“替換”命令，將每行中的“s1”替換掉。中04-5-31 15:43:04

　　然后再設(shè)計(jì)分?jǐn)?shù)表，用來(lái)判斷對(duì)錯(cuò)并統(tǒng)計(jì)分?jǐn)?shù)，如圖2所示。c2中輸入“=IF(答案表!C2=C$61,$C$62,"0")”，將標(biāo)準(zhǔn)答案寫(xiě)在從c61單元格開(kāi)始的第61行，該公式表示：如果c2等于標(biāo)準(zhǔn)答案c61單元格的數(shù)值，則顯示c62指定的分?jǐn)?shù)，否則為0分。然后選中該單元格，鼠標(biāo)移動(dòng)到右下角橫向拖動(dòng)20個(gè)格，然后再選中第一行縱向拖動(dòng)58個(gè)格。

　　在C62中輸入“2”，表示每道題2分，如圖3所示。如果正確給2分，如果錯(cuò)誤給0分。然后分別橫向求和，就可以得到每個(gè)學(xué)生的分?jǐn)?shù)。

　　學(xué)生機(jī)端設(shè)置

　　首先在學(xué)生機(jī)上建立一個(gè)隱藏共享文件夾，筆者所在機(jī)房中的d盤(pán)都是隱藏共享的。教師機(jī)上再共享一個(gè)“test$”共享文件夾，教師把考試文件復(fù)制到其中，讓學(xué)生訪問(wèn)教師機(jī)上的“領(lǐng)取試卷”共享文件夾，運(yùn)行其中的“領(lǐng)取試卷.dat”，內(nèi)容為“copy \\teacher\test$\test.xls d:\”。

　　這樣試卷就復(fù)制到學(xué)生機(jī)的桌面上了，學(xué)生在指定的地方填好答案后單擊[保存]按鈕即可，考試結(jié)束時(shí)，教師運(yùn)行收試卷程序：

　　copy \\s1\d$\ test.xls D:\test\s1

　　copy \\s2\d$\test.xls D:\test\s2

　　……

　　copy \\s58\d$\test.xls D:\test\s58

　　并把統(tǒng)計(jì).xls復(fù)制到d:\test文件夾中，打開(kāi)統(tǒng)計(jì).xls，成績(jī)就一目了然了。

圖1 test.xls文件

圖2 匯總.xls文件

圖3 分?jǐn)?shù)設(shè)置

      excel為學(xué)生考試成績(jī)排名

       見(jiàn)圖，表格中學(xué)號(hào)和姓名等是用計(jì)算機(jī)錄入并打印出來(lái)，老師只是手寫(xiě)填上考試成績(jī)和排名，然后讓打字員錄入表格中。當(dāng)然我也看到或聽(tīng)說(shuō)過(guò)其他學(xué)校的學(xué)生成績(jī)統(tǒng)計(jì)分?jǐn)?shù)排名表。它是對(duì)學(xué)生成績(jī)進(jìn)行升序或降序的排列表。這在Excel中是比較簡(jiǎn)單的。這種排序表的好處是前幾名"優(yōu)生"和后幾名"差生"一目了然。真正起到了"鼓勵(lì)"和"鞭策"的作用。本人不擅長(zhǎng)office，也不知道其他學(xué)校是否用計(jì)算機(jī)對(duì)圖中在不改動(dòng)學(xué)號(hào)順序和對(duì)應(yīng)姓名關(guān)系的前提下，完成"名次"項(xiàng)填寫(xiě)。

         筆者經(jīng)過(guò)反復(fù)摸索?？偨Y(jié)出自認(rèn)為是最簡(jiǎn)單易行的方法，供讀者參考。

        方法和步驟如下：

        1． 錄入完分?jǐn)?shù)后，對(duì)學(xué)號(hào)、姓名和分?jǐn)?shù)進(jìn)行多個(gè)字段排序。方法是以分?jǐn)?shù)為主排序，鼠標(biāo)點(diǎn)擊第一個(gè)分?jǐn)?shù)按住向左下拖動(dòng)選定前三列后，點(diǎn)擊降序按鈕，排出了高分到低分的分?jǐn)?shù)排序。

        2． 在名次列中從上到下輸入1……10，最簡(jiǎn)單的方法是輸入1后右擊鼠標(biāo)按住向下拖動(dòng)至10，選定序列方式填充即可。

         3． 以學(xué)號(hào)為主排序，即恢復(fù)原來(lái)的學(xué)號(hào)排序。鼠標(biāo)點(diǎn)擊學(xué)號(hào)列第一個(gè)（不是一號(hào)）按住向右下拖動(dòng)至名次列最后選定前四列后，點(diǎn)擊升序按鈕，還原學(xué)號(hào)排序。

     這樣就完成了圖表中的第一次考試成績(jī)和名次的錄入。如果要完成以后幾次考試成績(jī)和名次的錄入工作，可把前次考試成績(jī)和名次兩列隱藏起來(lái)后，按上述方法錄入完后，取消隱藏即可。