自己開發(fā)的Excel成績統(tǒng)計模塊的教程

1、中小學(xué)成績統(tǒng)計加載宏教程一、加載宏的安裝1、首先確定是在計算機(jī)系統(tǒng)管理員帳戶下進(jìn)行操作（非管理員帳戶不能安裝），然后退出殺毒軟件及360安全衛(wèi)士（可能誤報模塊為病毒或木馬，導(dǎo)致安裝失?。?，打開excel2003（模塊支持excel2003和excel2007，教程以excel2003為例），將安全性設(shè)置到“中”，最后退出excel。2、找到安裝壓縮包，打開壓縮包并雙擊里面的liyunfei20130919.exe(有可能是liyunfei*.exe)，如彈出“注冊后使用全部功能”消息框，說明安裝成功。模塊安裝程序下載頁面：二、加載宏的注冊1、重新啟動excel，excel2003版的界面如下，

2、其中增加了“成績統(tǒng)計”和“其它”兩個菜單。如果是excel2007，則界面中增加了“加載項”，在“加載項”中有“成績統(tǒng)計”和“其它”兩個菜單。2、在“成績統(tǒng)計”菜單中找到“注冊”選項并單擊，如excel的安全性設(shè)置為“中”，將彈出安全警告，單擊“啟用宏”（成績統(tǒng)計模塊主要由lyfei001.xla，lyfei002.xla，lyfei003.xla組成，今后出現(xiàn)安全警告要注意），將依次出現(xiàn)幾個提示框，在提示輸入注冊碼的對話框中輸入獲得的注冊碼（注意：機(jī)器碼不同，注冊碼就不相同，輸入時可不區(qū)分大小寫，但不要輸入空格字符），不注冊在各科綜合統(tǒng)計和各科分?jǐn)?shù)段統(tǒng)計時有班數(shù)和科目數(shù)的限制。三、用加載宏進(jìn)

3、行成績統(tǒng)計注意事項：為了方便用戶實時填入數(shù)據(jù)，實時得到結(jié)果，在有些命令中加載宏在運行中使用或產(chǎn)生了大量的數(shù)組公式。因此，在運行時不要打開含有大量數(shù)組公式的其它工作簿以及含有鏈結(jié)的單元格照相的其它工作簿。程序產(chǎn)生的新工作簿也要盡快把結(jié)果復(fù)制出來，然后關(guān)閉。否則，將出現(xiàn)程序運行十分緩慢的情況！本加載宏最大統(tǒng)計范圍：1個年級、1次考試，100個班、10000人、50個學(xué)科下面以測試文件（測試文件“周中11初六2”在liyunfei20130202.rar壓縮包內(nèi)）為例，演示加載宏的主要功能。測試文件“周中11初六2”代表周家中學(xué)初中11級第六學(xué)期第2次月考，（初中三年可分成六個學(xué)期，大家可結(jié)合自己學(xué)

4、校的實際簡化文件的命名），這是一個有三個工作表的工作簿，sheet1表中存放著12初全級剛錄入完的原始成績，sheet2 和sheet3表為空表，將用來存放統(tǒng)計數(shù)據(jù)（注意：統(tǒng)計數(shù)據(jù)和原始成績不能放在一個表中，如果空表不夠，可以添加新的空表）。原始成績表的格式設(shè)置及其它一些事項見下表：圖中第3行為空行，只是行距很窄。如果格式不符合要求或選錯了要統(tǒng)計的表，則可能出現(xiàn)如下一些警告，并激活有問題的單元格或行列，并中止程序運行，請根據(jù)警告做相應(yīng)的處理。1、數(shù)據(jù)備份統(tǒng)計之前最好對被統(tǒng)計文件進(jìn)行備份，打開“周中11初六2(測試文件).xls”，執(zhí)行“成績統(tǒng)計”“統(tǒng)計前準(zhǔn)備”“數(shù)據(jù)備份至d盤”，然后單擊“強(qiáng)制

5、備份”按鈕，出現(xiàn)“備份成功”消息框，則當(dāng)前的工作簿就被復(fù)制到目錄“d:成績統(tǒng)計備份”下。備份文件名是由當(dāng)前文件名加上備份時間組成的，在需要的時候可以打開，注意目錄“d:成績統(tǒng)計備份”有可能是隱藏的。 目錄“d:成績統(tǒng)計備份”下的文件可以隔幾年才清理一次，因為每個文件一般情況下都比較小，當(dāng)然不排除由于對excel使用不當(dāng)而造成的大文件情況。2、數(shù)據(jù)檢查在統(tǒng)計前要對被統(tǒng)計的數(shù)據(jù)進(jìn)行檢查，因為被統(tǒng)計的數(shù)據(jù)可能有輸入錯誤，比如78.5被輸入成78.5，多一個小數(shù)點就成了文本格式，或者錄入時沒有按回車符，輸入了一個很大的數(shù)。另外也有可能是從其它軟件中復(fù)制來的數(shù)據(jù)，格式不符合統(tǒng)計要求。數(shù)據(jù)檢查操作如下：激

6、活要檢查的工作表，本實例中是文件“周中11初六2(測試文件).xls”的sheet1表，執(zhí)行“成績統(tǒng)計”“統(tǒng)計前準(zhǔn)備”“檢查”。（“檢查”菜單中的“n”表示執(zhí)行命令后，數(shù)據(jù)將產(chǎn)生在新建的工作簿中，如果是“na”則表示執(zhí)行命令后，數(shù)據(jù)將產(chǎn)生在新建的工作簿中并且有參數(shù)可以調(diào)整，以后的其它菜單意義相同）執(zhí)行命令后，將在新建工作簿中報告檢查結(jié)果。以上表中顯示的結(jié)果表示被檢查的數(shù)據(jù)沒有錯誤，可以進(jìn)行下一步操作，而下表中顯示的結(jié)果表示被檢查的數(shù)據(jù)有錯誤，需要處理后，再次檢查，直至檢查沒有錯誤為止，才可以進(jìn)行下一步操作。上表中必須要處理的數(shù)據(jù)有：姓名字段中有一空格，語文字段中有一個文本格式，數(shù)學(xué)字段下最大值

7、超過了該科分制；可能需要處理的數(shù)據(jù)有：英語字段下有5個空格（分情況處理：可能缺考，也可能是錄入人員忘記錄入）。特別注意：如果要統(tǒng)計缺考，請保留空格，如果不統(tǒng)計缺考，請在空格中輸入0。如果檢查沒有錯誤，關(guān)閉新建的檢查報告工作簿（不保存）。3、基本統(tǒng)計基本統(tǒng)計（注意：基本統(tǒng)計的四個命令都是直接在當(dāng)前激活的工作表中執(zhí)行）（1）總分在總分字段下的第一個單元格（本實例在單元格l5）中輸入總分公式“=e5/1.2+sum(f5:k5)”，在這里公式的含義是：語文分制是120，計算總分時語文要折算成100，其它學(xué)科全部計入總分。公式輸入完成后，按回車，然后執(zhí)行“成績統(tǒng)計”“基本統(tǒng)計”“總分”。如果公式符合要

8、求且沒有錯誤，執(zhí)行后將出現(xiàn)如下對話框。建議選“是”，這樣當(dāng)前工作簿將在總分之前被復(fù)制備份，當(dāng)然如果選“否”，也將總分，但不會對當(dāng)前工作簿備份。選擇后公式將復(fù)制至全級。特別注意：總分公式是由用戶自己輸入，因為不同的學(xué)校、年級、考試，總分的折算大不相同。在輸入總分公式時不能輸入“$”絕對引用符，也不能出現(xiàn)明顯的錯誤。如果出現(xiàn)明顯錯誤，執(zhí)行“總分”命令時，將出現(xiàn)如下提示。（2）排名執(zhí)行“成績統(tǒng)計”“基本統(tǒng)計”“排名”，即可完成按總分由高到低的排序，并顯示名次（3）升降如果前次（上次考試的名次）字段下有數(shù)據(jù)，則可執(zhí)行“成績統(tǒng)計”“基本統(tǒng)計”“升降”，則可計算出每個學(xué)生兩次考試的名次升降情況。（4）班名

9、次執(zhí)行“成績統(tǒng)計”“基本統(tǒng)計”“班名次”，即可計算出每個學(xué)生的總分班名次。4、名次分布統(tǒng)計（1）名次分布的簡單統(tǒng)計名次分布的簡單統(tǒng)計（常見的10名遞增包含式）執(zhí)行“成績統(tǒng)計”“名次分布統(tǒng)計”“名次分布（簡）n”，可計算出全級總分名次在各班的分布情況，程序?qū)⒆詣有陆ㄒ粋€工作簿存放計算結(jié)果。將新建工作簿中需要的數(shù)據(jù)復(fù)制到測試文件工作簿“周中11初六2(測試文件).xls”的sheet2空表中（這里假設(shè)復(fù)制的是前10名至前100名的數(shù)據(jù)），復(fù)制完后關(guān)閉新建的工作簿并不保存。如果想徹底去除0值，從而使表格更加簡潔，可以先選中你想去除的區(qū)域，然后執(zhí)行“其它”“清除0值和錯誤值”。單擊“確定”，最終結(jié)果如

10、下。去除0值在以后還會用到，本教程只演示一次，使用這個命令要注意：一定要先選擇想去除的區(qū)域，如果有些0值需要保留，就不要選擇。（2）名次分布的稍復(fù)雜統(tǒng)計名次分布的稍復(fù)雜統(tǒng)計（常見的遞增包含式和分段式，用戶可在紅色單元格內(nèi)自由輸入各名次段的間隔的起止，比如下表中以6的倍數(shù)遞增）重新激活測試文件工作簿“周中11初六2(測試文件).xls”的sheet1表，執(zhí)行“成績統(tǒng)計”“名次分布統(tǒng)計”“名次分布（詳）na”，可計算出全級總分名次在各班的分布情況，程序?qū)⒆詣有陆ㄒ粋€工作簿存，并在兩個工作表中分別存放名次分布包含式和分段式統(tǒng)計結(jié)果。注意：凡是帶有這種紅色框的單元格，都是可以由用戶輸入?yún)?shù)，并實時得到

11、結(jié)果。這些表其實是由數(shù)組公式做出來的，它們有可能影響下一個命令運行的速度，所以在得到了滿意的統(tǒng)計結(jié)果后，立即將數(shù)據(jù)復(fù)制出來，然后關(guān)閉這個工作簿（不保存）。5、總分分?jǐn)?shù)段統(tǒng)計總分分?jǐn)?shù)段統(tǒng)計（操作與名次分布統(tǒng)計相同）注意：在分?jǐn)?shù)段詳細(xì)統(tǒng)計中，(490，480表示小于490大于等于480，即490x=480。6、各科（按各班或全級）統(tǒng)計（1）各科綜合統(tǒng)計各科綜合統(tǒng)計可以任意抽取各班的人數(shù)來進(jìn)行統(tǒng)計，抽取的依據(jù)有兩種，一種是按考生的總分的班名次，另一種是按考生單科成績的班名次?？梢詫σy(tǒng)計的幾個包含式分?jǐn)?shù)段、優(yōu)秀分、及格分、低分等進(jìn)行自定義；統(tǒng)計的項目主要包括：幾個包含式分?jǐn)?shù)段統(tǒng)計、最高分、最低分、優(yōu)

12、秀率、及格率、低分率、總分、平均分、平均分名次、優(yōu)秀率名次、及格率名次、低分率名次等激活測試文件工作簿“周中11初六2(測試文件).xls”的sheet1表，執(zhí)行“成績統(tǒng)計”“各科（按各班或全級）統(tǒng)計”“各科綜合統(tǒng)計na”。執(zhí)行后出現(xiàn)以下對話框。如果選擇“是”，是表示以后在按總分（或單科）的班名次作為統(tǒng)計數(shù)依據(jù)時，不會考慮并列名次，只計并列的班名次中在表中位置最靠前的一名，選擇“否”，則會全部計算。比如5班，班名次（按總分排）第40名有4人，現(xiàn)在假如要在5班按總分成績由高到低抽40人出來統(tǒng)計，如選“是”，則只會在第40名中選位置靠前的1人，從而保證總數(shù)為40人，如選“否”，則會把4個第40名全

13、部計算在內(nèi)，從而總數(shù)為43人。一般情況下，請選擇“是”。如果出現(xiàn)缺考現(xiàn)象（某科目字段下有空單元格），則出現(xiàn)下面的對話框，可以根據(jù)需要進(jìn)行選擇，如果你校缺考是被當(dāng)作0計算的，就選“是”；如果你校缺考是不計人數(shù)的，就選“否”。注意：上面的對話框選擇不同，可能會影響到后面的統(tǒng)計結(jié)果。選擇完成后將出現(xiàn)下面一個新建工作簿。在上圖中，紅色框區(qū)域代表可輸入?yún)?shù)區(qū)（程序已經(jīng)預(yù)設(shè)了一些參數(shù)），用戶可以自己修改并輸入?yún)?shù)，紫色框區(qū)域代表可選擇項，用戶可點擊并選擇里面的選項，藍(lán)色框區(qū)域代表參考區(qū)，用戶可在黃色框區(qū)域內(nèi)輸入?yún)?shù)，得到想?yún)⒖嫉臄?shù)據(jù)。以上各參數(shù)區(qū)，如果用戶不作任何設(shè)置，就相當(dāng)于各班所有參考人數(shù)按默認(rèn)參數(shù)進(jìn)

14、行統(tǒng)計。另外在表的下方還有三率計算方式的修改，這里有兩種計算方式，以優(yōu)秀率為例：1、各班抽取人數(shù)中的優(yōu)秀人數(shù)/各班抽取人數(shù)，2、各班抽取人數(shù)中的優(yōu)秀人數(shù)/全級當(dāng)前抽取人數(shù)中的優(yōu)秀人數(shù)。由于各科綜合統(tǒng)計是本模塊中最復(fù)雜的部分，下面用3個統(tǒng)計任務(wù)來進(jìn)行演示。 班按總分抽取前20名進(jìn)行各科綜合統(tǒng)計，并統(tǒng)計單科進(jìn)入全級前200名的人數(shù)，各科優(yōu)秀分以達(dá)到各科全級人數(shù)前10%為標(biāo)準(zhǔn),各科及格分以達(dá)到各科全級人數(shù)前30%為標(biāo)準(zhǔn)。具體操作如下：在d2到d13中輸入20（可用excel的拖放功能快速輸入），在j21單元格輸入200后回車，在k21:r21單元格就得到了全級各科前200名的具體分?jǐn)?shù)，復(fù)制k21:r

15、21到k9:r9。在j21單元格輸入公式“=h1*0.1”后回車(h1單元格是全級總?cè)藬?shù))，在k21:r21單元格就得到了全級各科前10%的具體分?jǐn)?shù)，復(fù)制k21:r21到k10:r10。再次在j21單元格輸入公式“=h1*0.3”后回車，在k21:r21單元格就得到了全級各科前30%的具體分?jǐn)?shù)，復(fù)制k21:r21到k11:r11。單擊“按各班人數(shù)條件統(tǒng)計”按鈕，等幾秒后（假如1000人以內(nèi)的正常情況下）出現(xiàn)如下對話框（如果你打開了其它本應(yīng)該關(guān)閉的工作簿或被統(tǒng)計工作簿中含有大量的數(shù)組公式或數(shù)據(jù)鏈結(jié)，那計算時間可能會大大延長而使你受不了）。一般選擇“是”，然后在新建的工作簿中的sheet2表中出現(xiàn)

16、了統(tǒng)計結(jié)果左邊紅色框就是各班前20名且各科進(jìn)入前200名的人數(shù)，右邊紅色框的優(yōu)秀率和及格率是根據(jù)自定義的分?jǐn)?shù)計算出來的。將需要的數(shù)據(jù)復(fù)制到測試文件工作簿“周中11初六2(測試文件).xls”的空表中，如果沒有空表，可以添加。各班按總分由高到低抽取各班總?cè)藬?shù)的70%進(jìn)行各科綜合統(tǒng)計，最低分不包括0分，并匹配任課教師。具體操作如下：接上例操作，選擇“人數(shù)表”，也可重新激活測試文件工作簿“周中11初六2(測試文件).xls”的sheet1表，執(zhí)行“成績統(tǒng)計”“各科（按各班或全級）統(tǒng)計”“各科綜合統(tǒng)計na”，再次生成“人數(shù)表”。在“人數(shù)表”的g12中，選擇“除0以外”。在“人數(shù)表”的g14中，單擊“重

17、新設(shè)定各班人數(shù)”，在彈出的對話框中選各班，并輸入70,然后點“確定”，則各班人數(shù)將班名次抽取70%。單擊“指定匹配任課教師表”找到先在d盤準(zhǔn)備好的2011年教師任課一覽表，一般學(xué)校教務(wù)處都有這個表，本實例中文件下載鏈結(jié)為 上面圖中，是直接打開的“2011年教師任課一覽表.xls”，藍(lán)色框為行標(biāo)記，行標(biāo)記的行數(shù)是2，紅色框為列標(biāo)記，列標(biāo)記的列數(shù)為1，紫色框中顯示該工作簿有4個工作表，而要匹配的數(shù)據(jù)在“11級”工作表中。單擊“打開”按鈕后，在單元格g21中選擇對應(yīng)要匹配數(shù)據(jù)的表，在單元格h22填入“2”。單擊“按各班人數(shù)條件統(tǒng)計”按鈕，并在幾秒后出現(xiàn)的對話框中選“是”，則在sheet2表中出現(xiàn)了統(tǒng)

18、計結(jié)果，并匹配了任課教師。將需要的數(shù)據(jù)復(fù)制到測試文件工作簿“周中11初六2(測試文件).xls”的空表中，如果沒有空表，可以添加后再復(fù)制數(shù)據(jù)。各班按單科成績抽取各班各科20人（各科分?jǐn)?shù)單獨由高到低，如有并列，選滿為止），進(jìn)行各科綜合統(tǒng)計。接上例操作，選擇“人數(shù)表”，也可重新激活測試文件工作簿“周中11初六2(測試文件).xls”的sheet1表，執(zhí)行“成績統(tǒng)計”“各科（按各班或全級）統(tǒng)計”“各科綜合統(tǒng)計na”，再次生成“人數(shù)表”。在d2中輸入“20”，然后能過填充柄拖放到d13，單擊“切換名次依據(jù)模式”按鈕，使單元格h29的內(nèi)容變成“按單科名次抽取”，再單擊“按各班人數(shù)條件統(tǒng)計”按鈕，并有幾秒

19、后出現(xiàn)的對話框中選“是”，則在sheet2表中出現(xiàn)了統(tǒng)計結(jié)果，最后仍將需要的數(shù)據(jù)復(fù)制到測試文件工作簿“周中11初六2(測試文件).xls”的空表中。（2）各科分?jǐn)?shù)段統(tǒng)計各科分?jǐn)?shù)段統(tǒng)計，執(zhí)行“成績統(tǒng)計”“各科（按各班或全級）統(tǒng)計”“各科分?jǐn)?shù)段統(tǒng)計na”，這個統(tǒng)計與各科綜合統(tǒng)計操作大體相同，只是要注意一些可調(diào)選項。下面是最終統(tǒng)計結(jié)果（已清除部分0值）（3）各科平均分統(tǒng)計及各科單科名次分布統(tǒng)計如果僅僅統(tǒng)計全級或各班的各科平均分或各科單科名次的分布，則執(zhí)行“成績統(tǒng)計”“各科（按各班或全級）統(tǒng)計”“各科平均分統(tǒng)計及各科單科名次分布統(tǒng)計na”，則新增一個工作簿。里面包含有8個表格（包含大量數(shù)組公式，統(tǒng)計完成后要關(guān)閉，否則影響下一個命令運行速度），一定要注意各個表格的意義。（4）各科滿分統(tǒng)計這個命令比較簡單，這里就不作講解演示了。（5）各科特定分?jǐn)?shù)段及最高