应用ExcelVBA高效完成统计分析-侯旭宏

2005122612ehinJEpidenliol,Deeembe:2005,vol.26,No,2.·ExcelVBA【】(SMR),ExcelvBA,。19912003,。2000,ExcelV13A19912003。19912003()。Excel(),ExcelVBAExcel、。ExcelVI3A、。【】;;;ApplieationofExcelvisualBasieforeiffeientlyepeetstatisticanalysisIIOUXu,ong`,SHENuF-zie,wANGJan,:ua,JNGQfnwu’SxthPhJPle’oHsPiralofhSanai]iaoOTngUni*rsity,llSanghai200233,China[Atarctlobjcetivexnordertoanalyz。multipl。statisti。tablesmoreeffieientlyExeelvisualBasieofrApplication(VBA)wa、intrdeueedthorughtheuseofanexampleofcaleulatingstandardizedmortalityrates(SMRs).Meth5Mortalit)·dataofcaneerand。adrivoaseulardisaeses,bysexandage,havebeeneolleetedform1991to2003bytheCenterofrDiseaseoCntorlandPreventionofShanghaiHuangpuDiotriet.tSandadrpopulationeomxlitionwasdefinedasChineseeensusstatistiesin2000.Themale’sSMRswereeaieulated,usingExeelVBAofreaehyearandelassifieationofeaneers.ResultsThemale’sSMRswereobtainedbyyearanddifferenteaneers.Attheasmetime,theresultswerelistedintheeaneer’sSMRstableformale.ConclusionsExeel15moreflexiblethangeneraldatabaseontheeombinationofdataandannotation.ExeelVBA15betterthanthebasieExeelinoPeratingmultipletablessimultaneouslyandmanmaehineconversation.Statistieanaly、15eanbeeffieientlycomPletedbyusingExeelVBA.[Kwords]Database;caneer;Caiovaseulardisease;Standardizedrnortalityrates、,,ExcelVBA、、。1.:OffieeExeel2002visualBasiCfo:Applieation(VBA)。offieeVBA,VBE(visualbasiceditor)。Excelxls,Excel“//visaulabis。”,VBE。、、、:200233();();();()、、、、、、n。。2.:EcxelVBAVBE(xls):MicorsoftEcxel,,(Excel);1,(),。l。,Excel“//”,xls,“”。1“”。200512262ehinJEpidemiol,Deeember2005,vol.26,No.123.:①,。②、,、,、,i(i)。t(sMR)[’]:。,(P,,.`.N_.__~_~.,.、,,.,,_、、_._~~i。、’N~,’“”~/`’~、、’’`’-。(、SMR)。③,。ExeelVBA19912003、SMR。2.Ecxel:.xls。,,:①19912003,13,(“1991”)。,。,“1991”2。②19912003,“”。,。3。③“”,2000、()。,、、(4)。④2,:“”,“”(5)。、,“()”。14.、VBA:ExcelVBE“/vBAPojrect”。“”,、,。EcxelVBE,“/”。1.:19912003(),。2000,21991()3.,“.xls”VisualBasicVBE1。1VBA,。01〕ti()nPrivateModulePuiblcsub()I〕inlNianFenAs1lltgeer2005xZ2612(、hinJEpidomiol,Deeember2005,vol.26,No2997D111BingZhongAsInteger,BingZhongstAsInteger,BingZhongEndAsIntegerDinlNianLingZusAIntegerDinlHuangPuNianLingZuAsIntegerDinHuangPuNianFenAsIntegerDinlNianFenNoAsInregerDiniChinaNianLingZuASIntegerDinTmplAsSingle,TmpZAs51le,Tmp3AsSingleDimT。4PASString’Tm4P=InputoBx(“”,“”)Worksheets(Tm4P).eSleet’NianFen=’Tm+0,’,,BingZhongst2BingZhongEnd=14’“”NianFenNo=NianeFn1988,“”()HuaPuNianF’en=2(NianeFrl1991:l,3,OPrBingZhong=BingZhongstToBingZhongEndTmP3=0,oFrNianLingZu=51’023,“”HuangPuNianLingZu=NianLingZu3,“”ChinaNianLingZu=NianLingZuTmpl=Cells(Bingzhong,NianLizu).value/worksheets(“”).Cells(HuaPuNianLingZ。,HuangPuNiallFen).ValueTmpZ=Tmpl,Worksheets(“〔1”).Cells(ChinaNianLiflgZu,6).vae,1000’1000TmP3=Tmp3+TmpZNextNianLignZuWorksheets(“”).Cells(NianFenNo,BingZhong).Value=Tmp3NextBiZhoMsgoBx(“”)EndSub4.“”,VBA。,,。19912003()。。19912003()5。SMREcxelVBA、、9982005122612ehsnJEpidemiol,Deeember2005,Vol.26,No.12。,VBAExcel,。,,Excel、。VBAiVsualBasie(VB),VBAVB。VBA,。,VBA,、、,。,Excel(、);ExcelVBAExcel、,Excel`l。,Ecxe!VBA、。l..:,..4.:,2002.6670.2(;ini(burter,nAnetteMauis,,,,.Exeol2002().:,2002.3,.ExcelVBA.:,2004.4Johnwalkenbach.,,.Exeel2003().:,2004.(:20050523)(:)··(EPEC),,,,。2005419.,3497,209,5.98%。、EPEC0125:K70。、。3497(2737,760),5、2。1,、,Zm,4m,80。n、.;,。43,49,209,25,5.98%,7,2。,209,1,,1829;160,49,3.27:1(3.60:l),。5。:(50.24%)、(37.80%)、(37.3%)、(28.71%)、(16,27%);(31.10%),38.5℃,:030012(、、、);(、、)39℃;,40%,.24%,;210/,10;23。.3EPEC0125:K70,9.38%;1;7209/L11x109/L,;5(-、4),omL,1()EPEC0125:K70,1,5,2。,,,、。、,EPEC0125:K70。。2001,,,,,、Zm;,,,,;,。,,,,,。(,)(:20050623)(:)