应用Excel VBA高效完成统计分析

Application of Excel Visnal Basic for efficiently complete statistic analysis

目的 通过标准化死亡率(SMR)计算的实例讲解，介绍如何使用Excel VBA对统计分析中的大量统计表格共同操作，高效快速地完成统计工作。方法 利用上海市黄浦区疾病预防控制中心1991-2003年的肿瘤及心血管疾病的死亡登记资料，以及同期的人口数资料。以2000年全国普查的人口构成为标准人口构成，使用Excel VBA编程来计算1991-2003年各年份男性肿瘤的标准化死亡率。结果 得到1991-2003各年份男性肿瘤的标准化死亡率并输出至预先构建的黄浦区肿瘤标化死亡率表(男性子表)中。结论 Excel比一般数据库系统的表格形式更加灵活(如可直接进行数据和注解的混排)，Excel VBA比基本的Excel在多表共同操作、人机交互功能上更是高出一个层次。使用Excel VBA能够方便、高效地完成统计分析。

Objective In order to analyze multiple statistic tables more efficiently Excel Visual Basic for Application（VBA） was introduecd through the use of an example of calculating standardized mortality rates （SMRs）. Methods Mortality data of cancer and cardiovascular diseases, by sex and age, have been collected from 1991 to 2003 by the Center for Disease Control and Prevention of Shanghai Huangpu District. Standard population composition was defined as Chinese census statistics in 2000. The male＇ s SMRs were calculated, using Excel VBA for each year and classification of cancers. Results The male＇s SMRs were obtained by year and different cancers. At the same time, the results were listed in the cancer＇s SMRs table for male. Conclusions Excel is more flexible than general database on the combination of data and annotation, Excel VBA is better than the basic Excel in operating multiple tables simultaneously and man-machine conversation. Statistic analysis can be efficiently completed by using Excel VBA.