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

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

考試巧分場(chǎng)

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

實(shí)例：在有1646條記錄的表中填上為學(xué)生分配的考號(hào)及考場(chǎng)號(hào)，50人一個(gè)考場(chǎng)，學(xué)生的考號(hào)由考場(chǎng)號(hào)+座位號(hào)，其中12代表高一年級(jí)第二次考試（圖1）。

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

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

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

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

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

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

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

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

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

成績(jī)錄入巧鎖定

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

平均分、分?jǐn)?shù)段人數(shù)巧統(tǒng)計(jì)

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

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

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

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

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

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

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

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

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