劉廣孚，殷愛貞

0 引言

高校擴招后，學生數(shù)量增加，考試完后成績處理的工作量也增大了。當學生班數(shù)較多，成績組成較復雜時，就需要花較長的時間進行繁瑣的成績匯總和分析，這樣出錯的可能性也大大增加。雖然出錯的只是少數(shù)，但對于學生來說卻關系重大，甚至可能關系到能不能拿到學位，直接影響以后的就業(yè)等等問題。所以，作為一名教師，必須避免在成績統(tǒng)計過程中出現(xiàn)錯誤。Excel 作為功能極強的電子表格軟件，可應用于學生成績處理，避免錯誤并大大提高處理效率，輔助完成大量的、繁瑣的、重復性的計算和統(tǒng)計工作[1] [2]。

現(xiàn)在很多老師都借助于Excel進行成績處理，一般只用其最基本的計算功能，把作業(yè)、考試、實驗成績等表格的結果復制到一個新表單中，再按照一定的算法進行計算得到匯總成績。這種處理方法雖然簡單，也確實減輕了老師的計算負擔，并且不要求使用者對Excel有太深的了解，但存在著工作量大、容易操作失誤的缺點。

雖然Excel具備VBA編程的功能[3]，不少人利用其編程功能進行成績統(tǒng)計，但使用者必須具備VBA編程知識才能使用。作者在使用Excel軟件進行成績處理的過程中，不斷摸索改進，制作了切實可行的模板，大大減輕了成績處理的工作量，達到了事半功倍的效果。

本文以我校的國家級精品課—電工電子學課程的成績處理過程為例，詳細說明成績處理模板的設計過程和使用方法。

1 設計思想

電工電子學(二)課程的上報成績分為平時成績、期末考試成績和總評成績，而平時成績由作業(yè)成績和實驗成績構成，實驗的電機控制實驗由張老師負責，而數(shù)字電路實驗由鄭老師負責。所以學生成成績由四部分組成：作業(yè)成績、考試成績、張老師負責的實驗成績和鄭老師負責的實驗成績，這四部分成績都使用Excel軟件進行統(tǒng)計生成，分別保存在一個Excel文件中，每個班又分別占用一個表單。

本文將利用這四部分成績進行成績匯總和統(tǒng)計分析，并考慮到可能各部分成績會做修改甚至大的調(diào)整，以及名單可能不一致。如果處理不當，可能會使得工作量很大，甚至出現(xiàn)嚴重錯誤，造成很壞的影響。

由于分數(shù)對于學生是非常重要的，所以作為教師絕對不應該給學生錯誤的成績。為了解決這個問題，作者針對成績匯總和處理中的問題，充分利用Excel的處理功能，總結出了切實可行的處理方法。

本文的指導思想是，先對第一個班進行成績處理，并生成成績處理模板，其他班的成績處理在模板的基礎上稍做修改即可。所以下面所講的都是模板的生成過程。本文介紹兩種成績匯總模板的生成方法，第一種方法對使用者的Excel知識要求不高，比較容易理解，模板的使用操作也比較簡單；第二種方法對Excel的知識要求稍高，生成模板的過程有些復雜，但模板的使用卻非常簡單，在以后的成績處理中只需非常少的改動即可，甚至只改動一個單元格的內(nèi)容就能實現(xiàn)另一個班的成績處理，效率是非常高的，并且該模板的通用性較好，達到了一勞永逸的效果。

在制作模板前，應該先做好以下的準備工作。

1) 準備好各種成績的Excel表格文件。

本例中，這些文件為：“考試成績.xls”、“作業(yè)成績.xls”“實驗成績_張.xls”、“實驗成績_鄭.xls”。這些文件中，每個文件都包含5個表單，分別為5個班的單項成績，表單的名稱均以班名來命名，分別為：車輛0701、車輛0702、安全0701、安全0702和安全0703。其實，有5個班還是50個班對模板的制作并沒有影響，主要關注的是第一個班的處理方法。

2) 打開這4個文件。

3) 生成一個新的Excel文件。

新文件名為：“成績匯總.xls”，用于對單成績進行處理，并最終形成模板。把第一個表單的名稱改為“車輛0701”。

2 成績的直接引用模板

該方法的指導思想是，利用Excel軟件的文件間數(shù)據(jù)引用的功能，把各單項成績引用到成績匯總表中，再進行成績匯總和分析，并用函數(shù)實現(xiàn)各文件間的名單核對。

本文成績處理的基本思想是：先把一位同學的各項成績匯總到一張表中，再進行處理得到總成績；先處理一位同學的成績，再復制公式而處理其他同學的成績；先處理一個班的成績，再套用模板處理其他班的成績。所以處理第一位同學的成績的方法是本文的關鍵。

成績處理模板的生成主要由以下5個步驟組成：

第1步，完成第一個班的第一位同學的信息成成績的引用。

第2步，完成第一個班的第一位同學的成績處理。

第3步，檢驗各份成績單的學生名單是否一致。

第4步，完成第一個班的所有同學的成績的處理。

第5步，對第一個班的成績進行分析，生成模板。

第6步，以第一個班為模板，完成對所有班的成績處理和分析。

下面詳細介紹模板的生成方法。然后現(xiàn)簡單介紹模板的使用方法。

第 1步，完成第一個班的第一位同學的信息及成績引用。

在Excel中，可以把不同文件不同表單的單元格的內(nèi)容引用到當前單元格中。這樣就可以把每位同學的學號、姓名、作業(yè)成績、實驗成績、考試成績都引用到一個新的表單中。這樣有兩個好處：一是不對原數(shù)據(jù)表格進行直接操作，避免操作不慎修改了原始數(shù)據(jù)，二是當原始數(shù)據(jù)變動時，引用過來的數(shù)據(jù)也同時更新，不必進行兩次操作。

先把第一個班—“車輛 0701”的第一位同學的所有主要相關信息都引用到匯總表單中。下面以學生學號為例說明引用的方法。

學號：每個成績表中都有學號和姓名，本例中從考試成績表格中進行引用。第一位同學的學號在“考試成績.xls”的“車輛 0701”表單中位于 A4單元格，需要引用到“成績匯總.xls”的“車輛0701”表單的A4單元格，則在“成績匯總.xls”文件的“車輛 0701”表單的 A4單元格中輸入 “=[考試成績.xls] 車輛0701!A4”，該公式表示當前單元格(A4)的內(nèi)容等于“考試成績.xls”的“車輛 0701”表單的 A4單元格的內(nèi)容，即第一位同學的學號。

姓名：與學號的引用方法類似，在B4單元格中輸入引用公式“=[考試成績.xls] 車輛 0701!B4”。

作業(yè)成績：在 C4單元格中輸入引用公式“=[作業(yè)成績.xls] 車輛 0701!G4”。

鄭老師的數(shù)字電路實驗的成績：在實驗成績中，第一位同學處于第5行的位置，三個實驗的總成績位于R5單元格，所以在匯總表的 D4單元格中輸入公式“=[實驗成績_鄭.xls] 車輛 0701!R5”。

張老師的電機控制實驗的成績：第一位同學也處于第5行的位置，兩個實驗的總成績位于M5單元格，所以在匯總表的 E4單元格中輸入公式“=[實驗成績_張.xls] 車輛0701!M5”。

期末考試成績：在F4單元格中輸入公式“=[試卷成績.xls] 車輛 0701!L4”。

這樣，第一位同學的所有信息及成績都引用到新的表單中，可以進行成績處理了。

第2步，完成第一個班的第一位同學的成績處理。

在學生成績處理中，需要得到學生的實驗成績、平時成績和總評成績，還要按總評成績分出優(yōu)、良、中、及格和不及格5個檔次。因為學生成績來自于4個文件，為了避免張冠李戴，還要進行名單審核。

張老師負責了2次實驗，鄭老師負責了3次實驗，所以實驗成績的計算公式為“=D4*0.6+E4*0.4”，放在G4單元格。

在平時成績中，作業(yè)成績占1/3，實驗成績占2/3，并且該成績一般應取為整數(shù)，以便上報。平時成績放在H4單元格，計算公式為“=ROUND((C4+G4*2)/3,0)”，其中ROUND為四舍五入的取整函數(shù)。

在總評成績中，平時成績占 30%，考試成績占 70%，結果取整，放在 I4單元格，計算公式為“=ROUND(F4*0.7+H4*0.3,0)”。

在總評成績中，借助于條件格式用不同的顏色醒目地標示低于60分的不及格成績，方法為：點擊第一位同學的總評成績單元格 I4，選擇主菜單的“格式”下的“條件格式”，選擇“單元格數(shù)值”和“介于”，在右邊的框中分別輸入“1”和“59“。點擊“格式”，在彈出的界面中選擇“顏色”，選中紅色作為不及格的標志，“確定”。不及格的分數(shù)將顯示為紅色，其余仍為黑色。

參加正?？荚嚨膶W生成績不可能為零。如果等于零，則意味著沒有參加考試或已轉出該班，不應參與成績分析。為了避免出現(xiàn)零分而影響以后的統(tǒng)計分析，將I4單元格的內(nèi)容 改 為 “ =IF(ROUND(F4*0.7+H4*0.3,0)＞0,ROUND(F4*0.7+H4*0.3,0), "")”。

成績檔次的劃分利用 IF語句完成，為“=IF(I4＞=90,"優(yōu)",IF(I4＞=80,"良",IF(I4＞=70,"中",IF(I4＞=60,"及格",IF(I4＞0,"不及格","")))))”，其中，等于零的分數(shù)的成績檔次為空白，表示該學生為非正常學生，不參與成績統(tǒng)計。

第3步，審核各份成績單的學生名單是否一致

由于不同的文件是由不同的老師生成和填寫的，如果班中有增減人的情況，就可能出現(xiàn)名單不完全一致的情況。如，有的老師把留級生放在前邊而有的放在后邊，有的把轉走的學生從名單中除去而有的則保留，這樣在引用時就會造成“張冠李戴”的成績錯亂。為了有效地避免這種情況出現(xiàn)，同時減輕老師審核的負擔，在表單中用公式對各個文件的名單進行核對，以確認同一行中的成績都來自于同一位學生。

利用IF函數(shù)實現(xiàn)名單的審核，如果一致，結果為“√”，如果不一致則指出與哪份名單不同，如果各份名單的名字均為空白則表示已到名單末尾，則結果為“空白”。公式為：

=IF(B4＜＞[作 業(yè) 成 績 .xls] 車 輛 0701!B4,"作 業(yè)",IF(B4＜＞[實驗成績_鄭.xls] 車輛0701!B5,"鄭",IF(B4＜＞[實驗成績_張.xls] 車輛 0701!B5,"張",IF(B4=" ","空白","√")))

因為名單是從“考試成績.xls”引用的，所以不再審核該文件。名單的審核可以避免大范圍的成績錯誤的發(fā)生，尤其在班數(shù)眾多時，非常有效。

第4步，完成第一個班的所有同學的成績的處理。

在完成了第一位同學的信息引用和成績處理后，通過復制公式的方式就可以完成其他同學的成績處理了。因為第一位同學成績引用時用的是相對引用，所以可以很方便地利用Excel的填充柄的功能進行公式復制。

例如，要實現(xiàn)對該班所有學生的學號的引用，進行以下操作即可。

用鼠標點擊A4單元格，再移到該單元格的右下角，當光標變成實心十字時，按住鼠標左鍵向下拖，當松開左鍵，就可以看到被拖過的單元格中出現(xiàn)了學生的學號，再從最后一個學號開始繼續(xù)向下進行填充復制，直到出現(xiàn)空白內(nèi)容為止，就實現(xiàn)了所有學生的學號引用。

用同樣的方法，對B4到I4的公式都用填充柄向下復制，就可以實現(xiàn)姓名和各項成績的引用，以及成績的處理。名單審核的公式也同樣復制，當名單出現(xiàn)不一致時，就會出現(xiàn)相應提示。

這樣就實現(xiàn)了第一個班的所有同學的成績處理。截圖如圖1所示。

圖1 成績引用和處理后的結果截圖

第5步，對第一個班的成績進行統(tǒng)計分析

對一個班的成績分析可以有多項內(nèi)容[4]，本文只統(tǒng)計幾項主要內(nèi)容：考試人數(shù)、平均成績、方差、每一檔次的人數(shù)。

由于每個班的人數(shù)是不相同的，這給統(tǒng)計工作帶來困難。有的文獻建議用的范圍寬一些，比如考慮一個班最多不會超過50人，那就按照50人來統(tǒng)計。但作者建議利用Excel的整列處理的功能進行統(tǒng)計。

當使用整列處理功能時，C-I列的學生成績下方單元格不再使用，而將統(tǒng)計信息放在右側，即L列之后。

總評成績平均值的計算公式為“=AVERAGE('總評')”，或“=AVERAGE(I:I)”，兩個公式的結果是一樣的，都表示對第 I列的所有數(shù)字求平均值。而標準差則用公式“=STDEV(I:I)”。

考試人數(shù)的統(tǒng)計公式為“=COUNTIF(I:I,"＞0")”。

統(tǒng)計每檔次的人數(shù)，以良好為例，公式為“=COUNTIF(I:I,"＞=80")-COUNTIF(I:I,"＞=90")”，即 80 分以上的人數(shù)減去90分以上的人數(shù)，就是80～89的人數(shù)。

至此，一個班的成績統(tǒng)計全部完成。部分截圖如圖 2所示。

圖2 成績統(tǒng)計截圖

第6步，以第一個班為模板，完成對所有班的成績處理和分析。

上面完成了一個班的學生成績處理，其實也同時生成了一個成績處理模板。

把車輛0701班的表單進行復制，更名為“車輛0702”。在車輛0702表單中，只需進行兩項操作：

1) 修改引用

把第一位同學的信息和成績的引用公式，以及審核公式中的“車輛0701”更改為“車輛0702”。

2) 復制公式

用填充柄對所有引用的公式及審核的公式進行復制。因為其余公式都是用的本表單的數(shù)據(jù)完成的，所以不必進行任何修改或復制。

這樣通過對模板的簡單修改就完成了另一個班的成績處理。這種方式簡單易學，可以在成績處理過程中提高效率和準確率，減輕老師的負擔。

按照這種方式就可以實現(xiàn)多個班以及下一次考試的成績處理了。

3 成績的間接引用模板

下面介紹一種更簡單的成績處理方法，只需修改班名稱就可以實現(xiàn)另一個班的成績處理。

上面的直接引用法在處理其余班的成績時，重新修改引用公式并復制公式就可以了，故稱為直接引用法。但在成績的引用上仍有缺憾，因為每一列的引用都源自于同一個表單，但在處理時還需要重新復制公式。如果把這種修改集中在一個單元格中，處理效率就會更高。間接引用法就是基于這種思想進行處理的。

第1步，建立引用信息表

在空余單元格中設計如圖3所示的表格。在表格中，將被引用的引用的信息內(nèi)容進行拆分。表格中輸入以下信息：

1) 班名(實際為表單名)。在本例中各文件的表單名均與班名一致，修改其內(nèi)容即可實現(xiàn)另一個班的成績處理。

2) 各成績文件的文件名。當處理其他考試的成績時，修改成實際的文件名稱即可。

3) 列號，即各個成績文件的平均成績所在的列號。

4) 起始行號。不同的老師建的表格中第一位同學所在的行號會有所不同。

5) 行號補差。被引用表格的起始行號與匯總表格的起始行號間的差值，引用時用來計算被引用單元格的行號。

圖3 被引用表格的信息表

第2步，生成間接引用公式

因為引用成績時，涉及到不同文件、不同表單、不同列與不同行，所以將這些因素作為變量處理，已經(jīng)列在了表3中。下面用INDIRECT函數(shù)實現(xiàn)對可變內(nèi)容的引用。

INDIRECT函數(shù)的功能是返回由文本字符串指定的引用[5] [6]。此函數(shù)立即對引用進行計算，并顯示其內(nèi)容。當需要更改公式中單元格的引用，而不更改公式本身，使用函數(shù)INDIRECT會非常方便。

該函數(shù)的語法為 INDIRECT(ref_text,a1)。在本例中，Ref_text的內(nèi)容為對單元格引用的內(nèi)容，a1省略。例如，“INDIRECT(“[作業(yè)成績.xls] 車輛 0701!G4”)”就相當于“=[作業(yè)成績.xls] 車輛 0701!G4”。

進一步，在 L1單元格中輸入內(nèi)容“[作業(yè)成績.xls] 車輛0701!G4”(注意沒有等號)，則該引用還可以寫為“INDIRECT($L$1)”,其中“$”表示絕對引用。

INDIRECT方法是按照字符串的內(nèi)容執(zhí)行引用的，前面所講的引用方法不同。“=[作業(yè)成績.xls] 車輛 0701!G4”中的G4是相對引用，用填充柄復制公式時其值是隨單元格位置不同而變化的，而 INDIRECT方法中的“G4”是一個字符串內(nèi)容，不會變化。而在成績處理時，信息引用需要隨位置變化而變化，本文用拼湊的方法實現(xiàn)，即，把引用中的幾項關鍵部分拆開成幾個便于修改的變量，當使用時，只需把幾個部分填寫正確就可以了。

當完善了所有被引用文件的相關信息后，就可以編輯公式進行引用了。在A4單元格中輸入學生考試成績的引用公式“=INDIRECT(M3&M2&"!"&P3&(ROW()+(R3)))”。其中“M3&M2&"!"&P3”等效于“[試卷成績.xls] 車輛 0701!L”，ROW()為當前單元格的行號，R3單元格中為要補償?shù)男袛?shù)，為了在其為負數(shù)或零時不使公式出錯，故用“(R3)”表示，而“(ROW()+(R3))”則為應該引用的單元格的行號。

其他引用公式類似。因?qū)W號和姓名的列數(shù)是固定的，故不作為變量處理。

第3步，成績處理及統(tǒng)計，生成模板

把所有的引用公式向下進行足夠多的復制，信息及成績的引用就完成了。

成績處理和統(tǒng)計的方法與直接引用法相同，不再贅述。

這樣，間接引用模板就生成了，如圖4所示。

圖4 間接引用的成績處理模板部分截圖

但每班學生人數(shù)不一，究竟復制多少算合適呢？為了防止出現(xiàn)遺漏，在復制審核公式時，一定要比其他公式多復制一行，這樣就保證一旦遺漏就必然出現(xiàn)審核錯誤，從而消除這一隱患。

第4步，利用模板處理成績

利用該模板處理學生成績時，可以進行表單復制，通過修改“班名”實現(xiàn)一個班的成績匯總及統(tǒng)計，也可以直接利用模板，僅修改班名就可實現(xiàn)另一個班的成績匯總及統(tǒng)計，從而實現(xiàn)了“一鍵搞定”的學生成績的“動態(tài)”處理。

當用于其他課程的成績處理時，只需改動文件信息和一些必要的表格文字就可以，如果成績的計算公式不同，則還需改動公式并復制，從而形成新的模板，再進行成績處理。

4 結論

本文巧妙利用Excel的公式處理功能，實現(xiàn)了兩種成績處理模板。第一種模板(直接引用法)操作簡單，易學易用，第二種模板(間接引用法)在使用時非常方便快捷，可以達到“一鍵搞定”的效果，處理一個班的成績僅需1～30秒的時間即可。不論用哪種方法，都大大提高了成績匯總處理的效率，把老師從繁瑣的數(shù)據(jù)處理中解放了出來。

本文獨特的名單審核功能成功地避免了學生成績“張冠李戴”的可能，保證了成績的準確性，同時減輕了老師審核的負擔。

總之，本文提出的學生成績處理的方法大大提高了成績處理的效率，能夠廣泛應用于各種課程的成績匯總處理，具有很好的推廣價值。

[1] 于愛兵.Excel 在實驗數(shù)據(jù)處理中的應用[J] .微型電腦應用,2001年第17卷第4期:63-64.

[2] 劉玉英.Excel 函數(shù)在成績統(tǒng)計表中的應用[J] .中國科技信息,2005年第14期:85,73.

[3] 曾憲棟,陳菲.學生成績統(tǒng)計及考試成績質(zhì)量分析軟件的研制[J] .廣東藥學院學報,2006年 8月第 22卷第 4期:476-477.

[4] 俞能福.成績統(tǒng)計與試卷分析的Excel實現(xiàn)[J] .安徽建筑工業(yè)學院學報(自然科學版),2008年 10月第 l6卷第 5期:104-107.

[5] 張澤江.Excel 在工程算量中的運用技巧[J] .山西建筑,2009年4月第35 卷第10期:362-364.

[6] 安維默,EXCEL功能的擴展[J] .北京統(tǒng)計,2004-4,總第168期:52-54.