数据透视表在统计汇总城市饮用水水质卫生指标监测数据中的应用

刘悦 霍本兴 郝舒欣 王秋水

［摘要］ 在公共卫生领域的统计工作中，经常会分析大量、复杂的业务数据，使用Excel数据透视表，不仅可以快速筛选、比较和汇总数据，还可以根据需要生成统计报表。本文介绍如何使用Excel数据透视表统计汇总2010年度全国15个省82个地区的城市饮用水水质卫生指标监测数据。

［关键词］ 饮用水； 水质； 卫生指标； Excel； 数据透视表； 统计； 汇总

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2012 . 02. 038

［中图分类号］TP317.3［文献标识码］A［文章编号］1673 - 0194（2012）02- 0067- 04

1引言

数据透视表是Ｅｘｃｅｌ提供的一种可以快速汇总大量数据的交互式方法。使用数据透视表，不仅可以对数据进行筛选、排序、分组和汇总，还可以移动行和列查看数据的不同汇总结果，对于快速分析汇总较大的数字列表非常有效。

在公共卫生的统计工作中，经常会分析大量、复杂的业务数据，使用Ｅｘｃｅｌ数据透视表，不仅可以快速筛选、比较和汇总数据，还可以根据需要生成统计报表。本文阐述如何使用Ｅｘｃｅｌ数据透视表统计汇总２０１０年城市饮用水水质卫生指标监测数据。

2统计要求

在本文中，城市饮用水水质卫生指标监测数据统计工作需要统计汇总２０１０年度全国１５个省８２个地区的城市饮用水水质卫生指标（包括色度、浑浊度、总大肠杆菌等共计１０６项）的合格数量和不合格数量。具体统计要求如下：① 水质卫生指标需根据指标分类（常规指标、非常规指标）和指标二级分类（感官性状和一般化学指标、毒理指标、微生物指标、放射性指标、消毒剂指标）进行分类。② 统计报表需根据供水类型（市政供水、自建水厂）、监测类型（出厂水、二次供水和末梢水）和监测频率（月报、季报、丰水期、枯水期）进行分类汇总，并生成１５张分类汇总表（如：市政供水出厂水丰水期汇总表，市政供水出厂水枯水期汇总表等）。

3数据来源

城市饮用水水质卫生指标监测数据统计工作的原始数据来源于城市饮用水水质和水性疾病监测数据库。该数据库采用低冗余、高关联的结构化设计思路，因此水质卫生指标统计所需的原始数据分布在监测点表（ｊｉａｎｃｅｄｉａｎ）、样品表（ｓａｍｐｌｅ）、指标数据表（ｚｈｉｂｉａｏｄａｔａ）和参照表（ｓａｍｐｌｅ）4个不同的表单中，并且只是其中的一部分数据，因此使用ＳＱＬ语句通过多表关联查询，筛选出数据并生成视图（ｚｈｉｂｉａｏ＿ｖｉｅｗ），从而达到快速、准确抽取原始数据的目标。具体ＳＱＬ代码如下：

ｃｒｅａｔｅ ｖｉｅｗ ｚｈｉｂｉａｏ＿ｖｉｅｗ ａｓ ｓｅｌｅｃｔ ｊｉａｎｃｅｄｉａｎ．省，ｊｉａｎｃｅｄｉａｎ．地区，ｊｉａｎｃｅｄｉａｎ．监测类型，ｊｉａｎｃｅｄｉａｎ．供水类型，ｓａｍｐｌｅ．监测频率，ｃａｎｚｈａｏｂｉａｏ．序号， ｃａｎｚｈａｏｂｉａｏ．指标名称， ｃａｎｚｈａｏｂｉａｏ．指标限值，ｚｈｉｂｉａｏｄａｔａ．指标监测值， ｚｈｉｂｉａｏｄａｔａ．是否合格，ｃａｎｚｈａｏｂｉａｏ．指标分类，ｃａｎｚｈａｏｂｉａｏ．指标二级分类ｆｒｏｍ ｊｉａｎｃｅｄｉａｎ，ｓａｍｐｌｅ，ｚｈｉｂｉａｏｄａｔａ，ｃａｎｚｈａｏｂｉａｏ ｗｈｅｒｅ ｊｉａｎｃｅｄｉａｎ．序号＝ｓａｍｐｌｅ．监测点序号 ａｎｄ ｚｈｉｂｉａｏｄａｔａ．样品序号＝ｓａｍｐｌｅ．序号 ａｎｄ ｚｈｉｂｉａｏｄａｔａ．参照表序号＝ｃａｎｚｈａｏｂｉａｏ．序号 ａｎｄ ｚｈｉｂｉａｏｄａｔａ．是否合格！＝未检测ａｎｄ ｓｕｂｓｔｒ（ｓａｍｐｌｅ．采样日期，１，４）＝ ２０１０；

通过执行上述ＳＱＬ代码，共计查询到２５７ ３０５条数据记录，这些数据记录就是本次统计工作所需的原始数据。

4导入原始数据

由于原始数据记录比较多，因此使用ＯＤＢＣ将原始数据导入Ｅｘｃｅｌ，Ｅｘｃｅｌ选用２００７版本。由于城市饮用水水质和水性疾病监测数据库使用的是ＭｙＳQL数据库，ＯＤＢＣ默认的连接中没有ＭｙＳQL的驱动程序，因此导入原始数据需要完成以下几个步骤：① 安装ＭｙＳQL的ＯＤＢＣ驱动程序。② 创建ＭｙＳQL 数据源。③ 配置ＭｙＳQL数据源参数，包括数据源名称、服务器地址、端口、用户名、密码和数据库等。④ 测试ＭｙＳQL数据源，如果成功，新建的ＭｙＳQL 数据源将出现在ＯＤＢＣ数据源管理中。⑤ 在Ｅｘｃｅｌ中通过ＯＤＢＣ数据源连接到城市饮用水水质和水性疾病监测数据库的水质卫生指标数据视图。⑥ 数据导入成功，原始数据出现在Ｅｘｃｅｌ中（见图1）。

5使用数据透视表统计汇总原始数据

数据透视表主要由字段列表、报表筛选、行标签、列标签和数值组成。字段列表列出导入的原始数据的所有字段名称，报表筛选根据筛选字段显示筛选数据，行标签将字段显示为报表左侧的行，列标签将字段显示为报表顶部的列，数值显示汇总数值数据。因此，根据统计要求合理地选择报表筛选字段、行标签字段、列标签字段和数值字段，并根据原始数据特点确定行标签、列标签中字段的排列顺序和数值字段的计算类型，是使用数据透视表统计分析数据的重点。具体操作步骤如下：

（1） 确定报表筛选字段：报表筛选字段决定统计报表的分类要求，筛选值决定具体的分类统计报表。根据统计要求，选择供水类型、监测类型和监测频率３个字段作为报表筛选字段，根据３个字段的值进行筛选、组合生成１５张分类统计汇总表。如，市政供水末梢水月报统计汇总表，供水类型的字段值选择“市政供水”，监测类型的字段值选择“末梢水”，监测频率的字段值选择“月报”（参见图2）。

（2） 确定行标签和列标签字段：行标签字段和列标签的字段决定统计报表的式样。根据统计要求，选择省、地区２个字段作为行标签字段。指标分类、指标二级分类、指标名称和是否合格４个字段作为列标签字段。

（3） 确定数值字段：数值字段决定统计报表中的计算数据。根据统计要求，选择是否合格作为数值字段。

（4） 确定行标签字段排列顺序：行标签中字段从上到下的排列顺序对应行字段从左到右的排列顺序，由于省和地区２个字段具有包含与被包含的关系，因此行标签中字段的排列顺序从上到下依次为：省、市。

（5） 确定列标签字段排列顺序：根据原始数据特点和统计要求，指标分类、指标二级分类和指标名称分类逐步细化，并且每个指标都对应合格数量和不合格数量，因此列标签中字段的顺序从上到下依次为：指标分类、指标二级分类、指标名称、是否合格。

（6） 确定数值字段的计算类型：由于需要统计每一项水质指标的合格数量和不合格数量，因此数值字段的计算类型选择为“计数”。

（7） 突出显示不合格的数据记录：为了区分报表中合格与不合格的指标数量，通过创建条件格式中的突出单元格规则和项目选取规则，使得数据透视表中等于“不合格”的单元格和不合格指标记录的数值标记为醒目的红色。

（8） 美化统计报表：将数据透视表转换成工作表，通过调整行宽、列宽、合并单元格等方法设计统计报表格式，使统计报表更加合理、美观（见图3）。

6结束语

Ｅｘｃｅｌ数据透视表是一款简单、实用的统计工具，使用它不仅可以方便、快速、准确地统计汇总大量、复杂的数据，还可以设计结构合理、式样美观的统计汇总报表，非常适合公共卫生领域的统计工作。但是需要注意的是，Ｅｘｃｅｌ ２００７之前的版本支持的最大记录数为６５ ５３６条，Ｅｘｃｅｌ ２００７版本支持的最大记录数为１ ０４８ ５７６条，如果统计数据比较多，超过Ｅｘｃｅｌ支持的最大记录数，则无法使用Ｅｘｃｅｌ进行统计汇总。

主要参考文献

［1］ Ｅｘｃｅｌ Ｈｏｍｅ. Ｅxcel数据处理与分析实战技巧精粹［Ｍ］. 北京：人民邮电出版社，２００８.

［2］ Ｅｘｃｅｌ Ｈｏｍｅ. Ｅｘｃｅｌ数据透视表应用大全［Ｍ］. 北京：人民邮电出版社，２００９.

［3］ 吴爱妤. Ｅｘｃｅｌ ２００７数据处理与分析范例精解：函数、图表、数据透视表篇［Ｍ］. 北京：机械工业出版社，２００９.

［4］ 神龙工作室. Ｅｘｃｅｌ数据透视表与数据分析范例应用［Ｍ］. 北京：人民邮电出版社，２０１０.

［5］ 黄海. Ｅｘｃｅｌ ＶＢＡ语法与应用词典［Ｍ］. 北京：中国青年出版社，中国青年电子出版社，２００９.