Excel,VBA生成身體素質(zhì)測(cè)試成績(jī)登記表

1、ExcelVBA生成身體素質(zhì)測(cè)試成績(jī)登記表摘要體育中考時(shí)學(xué)校會(huì)按照要求對(duì)考生實(shí)行級(jí)分組，然后按照分組順序制作體育中考身體素質(zhì)成績(jī)登記表，用于記錄學(xué)生成績(jī)。根據(jù)要求，成績(jī)登記表有統(tǒng)一的格式，表中的數(shù)據(jù)前后順序有相關(guān)性，并且要準(zhǔn)備好數(shù)據(jù)一一對(duì)應(yīng)的Excel工作簿，用于考試成績(jī)批量錄入。如果純粹使用手工制作的話工作量會(huì)較繁重，且容易出錯(cuò)。作者簡(jiǎn)要分析了考生數(shù)據(jù)分組的規(guī)律性，利用ExcelVBA技術(shù)設(shè)計(jì)了一個(gè)宏程序，實(shí)現(xiàn)了對(duì)考生實(shí)行自動(dòng)分組，還實(shí)現(xiàn)了用模板自動(dòng)生成有統(tǒng)一規(guī)格的成績(jī)登記表。關(guān)鍵詞體育中考分組數(shù)據(jù)處理自動(dòng)生成ExcelVBA一、分組規(guī)律用于批量錄入成績(jī)的Excel工作表一般包含班級(jí)、姓名、

2、報(bào)名序號(hào)、體育代碼（也叫類(lèi)別代碼）等數(shù)據(jù)，如下表所示。班級(jí)姓名報(bào)名序號(hào)性別身份證號(hào)體育代碼901張*0441:男XXXXXX19960311XX；X2133905陳*0526男XXXXXX19960523XX；&2232903毛*02731男XXXXXX19960416XXXX2131904季*0535男XXXXXX19960910XXXX2434表表（1）還不能直接用來(lái)錄入成績(jī)，需要加工。按照考試要求，考生數(shù)據(jù)要先按照體育代碼排好，然后把體育代碼相同的分為一個(gè)組，每組12名考生；超過(guò)12名則另分一組，不足12名的即使只有1名考生也分為一組；組號(hào)為三位數(shù)。分組如下表（2）所示。班級(jí)姓名

3、報(bào)名序號(hào)性別身份證號(hào)體育代碼組號(hào)組內(nèi)編號(hào)904張*0272男XXXXXX19950321XXX：X21310011905張*0256男XXXXXX19960507XXX：X21310012907金*0273男XXXXXX19960413XXX：X21310013901季*0421男XXXXXX19960113XXX(X21330021902彭*0176男XXXXXX19960406XXX(X21340031902張*0233男XXXXXX19960920XXX(X21340032表二、體育代碼解釋體育代碼也叫類(lèi)別代碼?？忌惨獏⒓尤齻€(gè)項(xiàng)目的考試，根據(jù)項(xiàng)目性質(zhì)分成三類(lèi)。一類(lèi)是必考項(xiàng)目，男子考10

4、00米跑，女子考800米跑；二類(lèi)是球類(lèi)項(xiàng)目，三類(lèi)是非球類(lèi)項(xiàng)目。用一個(gè)五位數(shù)很容易代表考生所選的內(nèi)容。五位數(shù)的第1位是性別代碼，1為男，2為女；第2-3位為第二類(lèi)測(cè)試項(xiàng)目，如21為籃球；后2位為第三類(lèi)測(cè)試項(xiàng)目，如31為跳遠(yuǎn)。相關(guān)的項(xiàng)目名稱(chēng)后面的代碼清單中有介紹。三、成績(jī)登記表分析成績(jī)登記表如下表（3）所示，頁(yè)面為橫向A4格式，表格中間略有改動(dòng)。表中類(lèi)別、報(bào)名序號(hào)、姓名、項(xiàng)目名稱(chēng)、項(xiàng)目代碼、組別等項(xiàng)目是動(dòng)態(tài)變化的，其他內(nèi)容和格式則固定不變一一很容易讓人想到使用模板的方法來(lái)生成這樣表格。另外從表（3）中我們能夠看出，根據(jù)這樣的成績(jī)登記表，表（2）已能夠用來(lái)錄入成績(jī)了。*縣2011體育中考身體素質(zhì)測(cè)試

5、成績(jī)登記表（一類(lèi)二類(lèi)三類(lèi)）考點(diǎn)：*考點(diǎn)單位：*學(xué)校項(xiàng)目名稱(chēng)：乒乓球項(xiàng)目代碼：25女子第006組序號(hào)報(bào)名序號(hào)姓名測(cè)試得分補(bǔ)考得分成績(jī)成績(jī)1231231*0013，王*2*0014方*3*0015關(guān)E*中間略去11*0023宇鐘*12*0024蔣*考評(píng)員：監(jiān)察員：記錄員：學(xué)生代表：年月日表四、編程思路對(duì)于分組，可設(shè)置兩個(gè)變量，一個(gè)用于存放當(dāng)前行的體育代碼，一個(gè)用于存放下一行的體育代碼，在表(2)所示的工作表中從上到下循環(huán)讀取體育代碼，每讀一次比較一次，不同時(shí)則遞增組號(hào)，讀取次數(shù)達(dá)到12時(shí)則相對(duì)應(yīng)遞增組號(hào)。對(duì)于成績(jī)登記表，有幾個(gè)組就需要幾張成績(jī)登記表。根據(jù)前面的想法，只要在分組時(shí)復(fù)制出模板，然后在程

6、序中對(duì)復(fù)制出的模板實(shí)行動(dòng)態(tài)的數(shù)據(jù)填充就能夠完成各組成績(jī)登記表的制作。因?yàn)榫哂邢嚓P(guān)性，兩個(gè)操作能夠合并到一個(gè)程序中完成。當(dāng)然數(shù)據(jù)工作表和成績(jī)登記表模板以及程序要放在同一個(gè)工作簿中。因?yàn)椴捎孟嗤姆纸M辦法，對(duì)于不同類(lèi)別的成績(jī)登記表也能夠采用相同的程序，只要對(duì)源程序稍微修改一下就能夠適用于不同類(lèi)別成績(jī)登記表的制作。限于篇幅作者這里僅提供類(lèi)別二的程序清單，并作了解釋?zhuān)晕⒏囊幌戮湍芡ㄓ糜谄渌麅蓚€(gè)類(lèi)別。清單如下：ConstGCount=12'12個(gè)人一組6ConstsTitle1="*縣2011體育中考身體素質(zhì)測(cè)試成績(jī)登記表"&"(一類(lèi)二類(lèi)三類(lèi))"

7、ConstsTitle2="*縣2011體育中考身體素質(zhì)測(cè)試成績(jī)登記表"&"(一類(lèi)二類(lèi)£類(lèi)"ConstsTitle3="*縣2011體育中考身體素質(zhì)測(cè)試成績(jī)登記表"&"(一類(lèi)二類(lèi)三類(lèi))"Sub自動(dòng)分組二類(lèi)()'運(yùn)行本程序前先按體育類(lèi)別代碼排序'功能：1.根據(jù)排好的類(lèi)別代碼自動(dòng)填寫(xiě)組號(hào)和組內(nèi)序號(hào)'2.填寫(xiě)二類(lèi)名稱(chēng)、二類(lèi)代碼DimsDataBase,sDBGSDimsModel,tModelDimsSheetNameDimp,gNumber,nNumberDimsGNu

8、mberDimID,sStudentName'用于引用數(shù)據(jù)庫(kù)'用于引用模板'用于動(dòng)態(tài)指定工作表名'p數(shù)據(jù)庫(kù)指針，gNumber組號(hào)，nNumber組內(nèi)序號(hào)'用于填寫(xiě)指定的三位數(shù)組號(hào)'學(xué)生報(bào)名序號(hào)、姓名DimProjCode,ProjCodeTemp'ProjCode類(lèi)別代碼DimsSexDimsNameC2,sCodeC2DimsNameC3,sCodeC3'ProjCodeTemp用于保存臨時(shí)類(lèi)別代碼'性別'二類(lèi)名稱(chēng)、二類(lèi)代碼'三類(lèi)名稱(chēng)、三類(lèi)代碼'初始化p=2引用表(2)數(shù)據(jù)用于統(tǒng)計(jì)成績(jī)登記表模板

9、'當(dāng)前p行類(lèi)別代碼'取性別gNumber=1SetsDataBase=Sheets（"數(shù)據(jù)庫(kù)"）SetsDBGS=Sheets（"組名與類(lèi)別"）SetsModel=Sheets（"模板"）ProjCode=sDataBase.Cells（p,6）sSex=Mid（ProjCode,1,1）IfsSex=1ThensSex="男"ElsesSex="女"EndIfProjCodeTemp=sDataBase.Cells(p,6)'保存臨時(shí)類(lèi)別代碼'取二類(lèi)名稱(chēng)、二類(lèi)代

10、碼sCodeC2=Mid(ProjCode,2,2)sNameC2=sCode2sName2(sCodeC2)IfLen(sNameC2)<1ThenMsgBox"在分析二類(lèi)類(lèi)別代碼時(shí)發(fā)現(xiàn)錯(cuò)誤，請(qǐng)檢查數(shù)據(jù)或格式是否準(zhǔn)確"&_vbNewLine&"當(dāng)前執(zhí)行報(bào)名序號(hào):"&IDGoToendopEndIf'先復(fù)制一個(gè)模板sModel.CopyAfter:=Sheets(Sheets.Count)sSheetName="第"&gNumber&"組"&ProjIt

11、emSheets("模板(2)").Name=sSheetNameSettModel=Sheets(sSheetName)'初始化表格數(shù)據(jù)tModel.Cells(1,1)=sTitle2tModel.Cells(2,5)="項(xiàng)目名稱(chēng)："&sNameC2&_組""項(xiàng)目代碼："&sCodeC2&_""&sSex&"子第"&GetsGNumber(gNumber)&"'主循環(huán)Do'GCount

12、為每組學(xué)生數(shù)FornNumber=1ToGCount'更新臨時(shí)變量'保存臨時(shí)類(lèi)別代碼For循環(huán)ProjCodeTemp=sDataBase.Cells(p,6)'判斷類(lèi)別代碼是否發(fā)生變化如果變化則退出IfProjCodeTemp<>ProjCodeThenExitForElse'否則填寫(xiě)數(shù)據(jù)ID=sDataBase.Cells(p,3)'報(bào)名序號(hào)sStudentName=sDataBase.Cells(p,2)'取學(xué)生姓名'組號(hào)和組內(nèi)序號(hào)sDataBase.Cells(p,7)=GetsGNumber(gNumber)'

13、;三位數(shù)組號(hào)sDataBase.Cells(p,8)=nNumber'組內(nèi)序號(hào)tModel.Cells(5+nNumber,2)=IDtModel.Cells(5+nNumber,3)=sStudentNameEndIf'偏移指針p=p+1'判斷數(shù)據(jù)是否已經(jīng)讀完，讀完數(shù)據(jù)則結(jié)束程序，否則實(shí)行下一步處理IfsDataBase.Cells(p,2)=""ThenGoToendopEndIfNextnNumber'偏移組號(hào)gNumber=gNumber+1'更新項(xiàng)目變量和臨時(shí)項(xiàng)目變量ProjCode=sDataBase.Cells(p,6)

14、'類(lèi)別代碼ProjCodeTemp=sDataBase.Cells(p,6)'保存臨時(shí)類(lèi)別代碼sSex=Mid(ProjCode,1,1)'取性別IfsSex=1ThensSex="男"ElsesSex="女"EndIf'取二類(lèi)名稱(chēng)、二類(lèi)代碼sCodeC2=Mid(ProjCode,2,2)sNameC2=sCode2sName2(sCodeC2)IfLen(sNameC2)<1ThenMsgBox"在分析二類(lèi)類(lèi)別代碼時(shí)發(fā)現(xiàn)錯(cuò)誤，請(qǐng)檢查數(shù)據(jù)或格式是否準(zhǔn)確"&_vbNewLine&&

15、quot;當(dāng)前執(zhí)行報(bào)名序號(hào):"&IDGoToendopEndIf'取三類(lèi)名稱(chēng)、三類(lèi)代碼'sCodeC3=Mid(ProjCode,4,2)'復(fù)制模板sModel.CopyAfter:=Sheets(Sheets.Count)sSheetName="第"&gNumber&"組"&ProjItemSheets("模板(2)").Name=sSheetNameSettModel=Sheets(sSheetName)'初始化表格數(shù)據(jù)tModel.Cells(1,1)=s

16、Title2tModel.Cells(2,5)="項(xiàng)目名稱(chēng)："&sNameC2&_"項(xiàng)目代碼："&sCodeC2&_""&sSex&"子第"&GetsGNumber(gNumber)&"組"LoopUntilsDataBase.Cells(p,2)=""'當(dāng)報(bào)名序號(hào)為空時(shí)退出'結(jié)束處理MsgBox"二類(lèi)登記表造冊(cè)完畢"endop:SetsDataBase=NothingSets

17、Model=NothingSettModel=NothingSetsDBGS=NothingEndSub'自動(dòng)分組二類(lèi)'二類(lèi)代碼轉(zhuǎn)二類(lèi)名稱(chēng)FunctionsCode2sName2(sCodeC2)SelectCasesCodeC2Case21sCode2sName2="籃球"Case22sCode2sName2="排球"Case23sCode2sName2="足球"Case24sCode2sName2="羽毛球"Case25sCode2sName2="乒乓球"Case26sCod

18、e2sName2="毽球"CaseElsesCode2sName2=""EndSelectEndFunction'三類(lèi)代碼轉(zhuǎn)三類(lèi)名稱(chēng)FunctionsCode2sName3(sCodeC3)SelectCasesCodeC3Case31sCode2sName3="跳遠(yuǎn)"Case32sCode2sName3="50米"Case33sCode2sName3="實(shí)心球"Case34sCode2sName3="跳繩"Case35sCode2sName3="引體向上"Case36sCode2sName3="仰臥起坐"CaseElsesCode2sName