PIVOT TABLE在監測數據統計中的應用

白愛民

(云南省環境監測中心站，云南昆明650034)

1 概述

云南省每年獲取的環境監測數據數以百萬，這些數據包括文本、數字或其他可以反映真實環境質量的字符。對數據的處理和分析，掌握環境狀況和質量變化、主要污染物等信息，并根據國家《環境監測報告制度》的有關規定上報是環境監測部門的例行工作之一［1］。

PIVOT TABLE(數據透視表)是EXCEL提供的一個數據分析工具，它可以快速匯總大量數據并進行交互，還可以深入分析數據，回答一些預計不到的數據問題［2］。熟練掌握PIVOT TABLE的使用，可快速以多種友好方式查詢數據、分析數據，并以多種方式獲取分類統計結果。

2 環境報告

環境報告是各級政府環境保護行政主管部門向同級人民政府及上級環境保護行政主管部門提交的環境質量狀況報告，可作為行政決策與環境監督管理的依據，是制定環境保護規劃和各類環境管理制度、政策的重要依據，也是公眾了解環境質量的重要途徑［3］。

常見的環境報告有環境監測快報、簡報、月報、季報、年報、環境質量報告書及污染源監測報告［2］，此外，還有為完成某些專題、研究、評價、仲裁等專項任務而編寫的環境報告。

環境報告數據和資料的來源，除環境監測部門的監測數據和資料外，還需要收集調研其他權威部門的相關自然環境要素和社會環境的監測數據和資料。對收集調研的監測數據和資料應根據環境報告的編寫目的進行分析和處理［3］。

3 環境監測報表

環境監測數據統計的主要目的之一就是在大量的歷史監測數據中，找出特定時空范圍的環境質量狀況信息，并形成相應的環境監測報表。

環境監測報表是各類環境報告的重要組成部分，也是完成環境報告的基礎。根據報告上報的周期性，可將環境監測報表分為常規數據報表和非常規數據報表。

常規數據報表是周期性上報的報表，如月報、季報、年鑒等。這類報表的空間、時間、監測項目、報表格式和計算方法等基本固定并反復使用，以反映環境質量的變化情況。

非常規性報表是為完成某些專題、研究、評價等特殊任務而編制的報表，這類報表的特點通常是擬獲環境信息的空間范圍小、監測項目少、時間跨度大。

4 數據透視表簡介

有了計算機和數據庫，一般的分類求和成為舉手之勞。然而，要快速匯總大量數據和建立交叉列表設計的交互式表格，對普通的數據庫管理系統的確有點強人所難［4］，而且程序的編寫也絕不是一蹴而就的事。

面對這種緩不濟急的情況，Excel的數據透視表可大顯身手。數據透視表是一個功能非常強大的數據分析工具，集排序、篩選、分類匯總及合并計算為一體。數據透視表是包含相關數據的各行各列的集合，就像數據庫中的一個表格?？商峁┍容^、揭示模式和關系以及分析趨勢［5］。

5 數據透視表的使用

創建能實現預期目標的數據透視表，通常有以下步驟［6］:①提出問題，明確預期的結果;②源數據準備;③使用向導創建數據透視表;④移動數據，篩選數據，組織數據;⑤調整數據格式和表格格式，提交問題的答案。下面輔以實例來說明。

5.1 問題的提出

按要求，需要獲取云南省任意兩個城市2013年空氣監測數據中SO2和PM10按半年匯總的統計表 (包括計數、最大值、最小值和平均值)。

5.2 源數據準備

此處的源數據表指的是一個二維表，本例是建立在一個EXCEL工作表中 (也可以是ACCESS、SQL和其它數據庫中的一個表)。對EXCEL表有特殊要求，具體為:第一行必須是標題行，其后為數據行;數據區不能有空行或空列;每列中只能有一種數據類型;數據區不要有統計結果行的存在;數據源中不能有合并單元格。

5.3 創建數據透視表

在工作表中選擇包括數據的任意單元格 (圖1)，或選擇要在表中包括的所有數據和列。在“數據”菜單上，單擊“數據透視表或數據透視圖”。

在向導的步驟1中，在“請指定待分析的數據的數據源類型”部分，確保選中“Microsoft Excel數據列表或數據庫”;在“所需創建的報表類型”部分，確保選中“數據透視表”。單擊“完成”。

在接下來出現的窗口中，右側是數據透視表的字段列表，左側 (A1:G16)是數據透視表的工作區域 (圖2)。要配置數據透視表以實現監測報表，只要用鼠標將按鈕拖至“行”、 “列”、 “數據”或“頁”字段區域即可。其中拖入到“頁”字段中的按鈕相當于選擇了“自動篩選”命令，可以控制顯示每一項;拖入到“行”字段中的按鈕變成了行標題;拖入到“列”字段中的按鈕變成了列標題;拖入到“數據”字段中的按鈕相當于選擇了“分類匯總”命令。

5.4 組織數據、調整匯總項

(1)將數據透視表字段列表中的“年”拖移到頁字段位置 (A1單元格)，單擊B1單元格右側的倒三角按鍵后，選擇2013年;

(2)將字段列表中的“測站名稱”和“月”分別拖移到行字段位置:單擊A4單元格右側的倒三角按鍵，選擇好二個城市;鼠標右鍵單擊B4單元格，在“組及顯示明細數據”子菜單中選擇“組合”，將步長改為6(步長為6個月，即要求的半年，如步長改為1、2或3，就可按月、雙月或季等匯總數據);

(3)將字段列表中的“項目”拖移到列字段位置，選擇2個監測指標 (SO2、PM10);

(4)將字段列表中的“監測值”分四次拖移到數據項區域，鼠標右鍵分別單擊C5至C8單元格，選擇彈出菜單中的“字段設置”通過字段設置將匯總方式分別調整為計數、最大值、最小值和平均值，名稱也作相應改動 (圖3);

(5)用鼠標拖動C4單元格到D4單元格的下沿，統計量由三級行標題，變為二級列標題。

5.5 調整數據格式和表格格式

最后，對小數位、列寬、一級列字段按項目合并并居中對齊。必要時，還可以調整監測項目或城市的順序，整個過程完成 (圖4)。

上述建立數據透視表過程，敘述起來感覺很長，但源數據表按數據庫規范準備好后，實現信息提取的操作只是一、二分鐘的事。

總之，設置數據透視表的表格視圖或布局，主要靠鼠標的拖移，如將字段列表中的字段，分別拖移到數據透視表工作區的“行”、“列”、“數據”或“頁”字段區域，可設置多級子標題 (圖4中，行列標題都有二級)，可實現行、列、頁標題的互換。篩選和組合表格中的字段則通過鼠標加菜單實現，如右鍵單擊后顯示內容通過各標題名稱單元格右側的倒三角鍵可選擇部分或全部擬出現的標題內容，鼠標右鍵單擊“標題名稱”，可設置字段 (如選擇哪一個統計指標)、組合字段 (按半年匯總數據)和設置表格的顯示格式等。

6 結語

編制環境報告需要收集匯總大量的數據，完成大量的數據處理與分析比較，形成支持環境報告的環境報表。EXCEL的數據透視表是功能強大的數據分析和報表生成工具，利用它，拖動幾下鼠標，進行幾個簡單的設置，就能完成監測報表的編制。

從事數據處理與分析的工作人員已經很熟悉EXCEL，但會使用EXCEL數據透視表功能的人員并不多見，遠沒有體現出EXCEL在數據分析與監測報表方面的優勢。掌握PIVOT TABLE可輕松完成大量數據的分析，迅速完成各類環境報表的編制。EXCEL的PIVOT TABLE易學易懂，不需要編寫程序，也不需要記住復雜的計算公式，即便是不太熟悉EXCEL的人員也能很快掌握這項功能。

［1］國家環保局.環境監測報告制度 環監〔1996〕914號［Z］.

［2］數據透視表制作方法 ［EB/OL］.http://wenku.baidu.com/linkurl=neVnHTOrytyKEKsVYdjTd8M9OHAsiWI5eN_8otC2DsStToBMBy0PoqmxXCE049bEWsRQ9ZT6UhBtmnZtYyJVM emtDlv6lO2FUeP06Ojukbe.

［3］HJ 641－2012，環境質量報告書編寫技術規范［S］.

［4］用數據透視表做工作總結與計劃表 ［EB/OL］.http://wenku.baidu.com/link url=9WzjRKfi8uuA6zQt STlDYocgZLkmsW-WYP0Ap8hWjF5Tj37mLpT0zBzCayMijJpAoMxiRSR-FClSAllJ N2OJnfDVyiKgzHpgmtY97UCRd7.

［5］用數據透視表進行數據分析 ［EB/OL］.http://wenku.baidu.com/view/b68e3ff4ba0d4a7302763a61.html.

［6］本書編委會.EXCEL HOME，Excel2010數據透視表應用大全［M］.北京:人民郵電出版社，2009.