函數(shù)助力 考試服務一條龍

精品文檔-下載后可編輯函數(shù)助力考試服務一條龍學校每學期都要進行若干次大型考試，無論是年級的階段考試還是整個學校的大型期中、期末考試，如果光靠手工加計算器的方式對考試情況進行分析統(tǒng)計，這項工作是多么繁重、煩瑣。借助Excel這個重要工具，我們只需幾個函數(shù)就能左手茶杯、右手鼠標，輕松完成從考試分場、分數(shù)錄入到成績統(tǒng)計等整個考試過程。

考試巧分場

考試分場通常的做法是確定每場人數(shù)后，在Excel中將學生按某個字段（如總分等）排好序，從上到下按每場人數(shù)一場一填充。試想如果有幾十場，這種操作是不是非常費時費力？有沒有比較簡單的方法呢？

實例：在有1646條記錄的表中填上為學生分配的考號及考場號，50人一個考場，學生的考號由考場號+座位號，其中12代表高一年級第二次考試（圖1）。

筆者的做法是在工作表中的C3、D3位置輸入圖中的公式，然后雙擊圓圈處的向下填充柄進行填充，這樣就為每個考生按照命名規(guī)則分配了考號。

CONCATENATE函數(shù)的功能是將多個文本字符串合并成一個，它的語法是CONCATENATE（text1,text2,….），在這個公式中它是把“12”、考場號和座位號合并在一起。

TEXT函數(shù)的功能是根據(jù)指定格式將數(shù)值轉換成文本，其語法是TEXT(value,format_txt)，value指的是數(shù)值，format_txt就是轉換后的格式，具體在公式中兩個TEXT分別將考場號和座位號轉換成“00”格式，即01、02、03……

IF函數(shù)的功能是判斷一個條件是否成立，如果成立返回一個值，如果不成立則返回另一個值，它的語法是IF(logical_test,value_if_true,value_if_false)，其中l(wèi)ogical_test指的是判斷條件，value_if_true指當條件成立時返回的值，value_if_false指條件不成立時返回的值。

本例中第1個TEXT包括的IF函數(shù)的作用就是當前行號減2除以50后，如果整除則返回1，如果不整除再。如果當前行號減2除以50后取余數(shù)，如果余數(shù)是0，則返回整數(shù)商，否則返回整數(shù)商加1。這樣設置的目的是每50個人分配一個考場號，考場號依次加1。同理，根據(jù)當前行號減2后除50取余數(shù)，如果余數(shù)是0，分配座位號50，否則座位號就是余數(shù)，即1、2、3……49。至于為什么行號減2，就是因為表頭占去了兩行，需要填充考號的單元格不是從第一行開始的。

INT函數(shù)的功能是將數(shù)值向下取整為最近的整數(shù)，其語法是INT(number)，number指的是被取整的數(shù)值。

MOD函數(shù)的功能是返回兩數(shù)相除的余數(shù)，其語法是MOD(number,divisor)。

LEFT函數(shù)的功能是從一個文本字符串的第一個字符開始返回指定個數(shù)的字符，其語法是LEFT(text,num_chars)。

解釋了這么多，你看明白了嗎？不明白也沒關系，你可以把它復制到你的工作表中，使用時只需把其中的50換成相應的人數(shù)就行了。

成績錄入巧鎖定

由于大量的錄入工作不可能是一個人完成，所以我們可以把考號復制粘貼到一個新表中，注意：復制粘貼的時候，要選擇“編輯選擇性粘貼”菜單，選擇“數(shù)值”。在進行成績錄入時，一般都是教師根據(jù)考號按考場利用數(shù)字小鍵盤進行盲打錄入，由于人數(shù)眾多，錄入難免有出錯的時候，為了盡量減少出錯，錄入分數(shù)時不超出范圍，比如滿分為150分，如果比150分多或是小于0分就報錯，需要將分數(shù)限制在一定的范圍之內(nèi)。我們可以選定需要錄入的分數(shù)列，比如語文列，然后選擇“數(shù)據(jù)有效性”，在彈出的窗口中進行分數(shù)范圍設置（圖2）。

平均分、分數(shù)段人數(shù)巧統(tǒng)計

分數(shù)匯總、排名及各科平均分統(tǒng)計。我們把分場表中的數(shù)據(jù)復制到一個新表中，注意復制粘貼的時候，要選擇“編輯選擇性粘貼”菜單，選擇“數(shù)值”。將錄入完成后的各科成績匯總到這個表中。然后，用SUM、RANK、AVERAGE、IF函數(shù)對表中數(shù)據(jù)進行成績匯總、排名及求各科的年級平均分（圖3）。

各班各科平均分及分數(shù)段人數(shù)統(tǒng)計。統(tǒng)計各班各科平均分及分數(shù)線人數(shù)，一般采取的方法是一個班一個班地自動篩選或是分類匯總或是手工計算。在整個年級成績庫中做這些工作確實很麻煩、很煩瑣，筆者設計了兩個公式能夠輕松搞定上述任務，把你從繁重的工作中解脫出來。

如果統(tǒng)計各班各科平均分，首先新建一個表，設置好表頭、班級、科目，在需要統(tǒng)計平均分的位置輸入公式（圖4）。

上述公式也屬于數(shù)組公式，所以輸入完后也要按住Ctrl+Shift+Enter組合鍵用{}將其組合。其表示為返回班級等于A列中指定班并且分數(shù)不等于0的單元格的平均值。公式中為了進行向下或向右填充，所以不能將單元格區(qū)域設置成絕對區(qū)域。

如果統(tǒng)計各班的分數(shù)段人數(shù)，也需要建立一個新表，設置好表頭、班級和所需分數(shù)線，在需要統(tǒng)計分數(shù)線人數(shù)的位置輸入公式，道理和求平均分是一樣的（圖5）。

成績分析巧統(tǒng)計

考試成績的分析對于任課教師來說是特別重要的，尤其是優(yōu)秀率、及格率和低分率的統(tǒng)計。如果在年級成績匯總表中一個班一個班的、一個學科一個學科地統(tǒng)計，我們的工作量是可想而知的，但是如果利用SUMPRODUCT函數(shù)就可以使我們的工作化繁為簡、輕松自在了。

首先建立一個新表，輸入如表頭及班級，并把要填寫數(shù)據(jù)的單元格設置為百分比格式，然后在相應單元格中輸入公式（圖6）。

使用SUMPRODUCT可以進行多條件計數(shù)，其語法是=SUMPRODUCT((條件1)*(條件2)*(條件3)*…(條件n))，作用是統(tǒng)計同時滿足條件1、條