數據透視表在統計匯總城市飲用水水質衛生指標監測數據中的應用

劉悅 霍本興 郝舒欣 王秋水

［摘要］ 在公共衛生領域的統計工作中，經常會分析大量、復雜的業務數據，使用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］ 黃海. Ｅｘｃｅｌ ＶＢＡ語法與應用詞典［Ｍ］. 北京：中國青年出版社，中國青年電子出版社，２００９.