EXCEL數據庫的應用

白愛民

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

Microsoft Excel是最優秀的電子表格軟件之一，它在數據綜合管理和分析方面具有功能強大、技術先進、使用方便等特點［1］。數據庫是Excel的三大功能 (表格、圖表、數據庫)之一，利用它可方便地對匯總數據進行分析，并快速從大量的數據表中檢索出所需的數據。

在環境監測數據統計工作中，常常需要進行多條件環境下的數據分析與檢索，例如求解某一時間范圍、某一空間地域、甚至某一元素的監測值范圍下，另一元素的監測平均值等。使用Excel的數據庫和列表管理函數不僅可以求解出同時滿足多個條件的平均值、數據個數、最大值、最小值，還可以對數據庫中滿足條件的記錄的字段列中的數字求和，將數據庫中滿足條件的記錄的特定字段中的數值相乘，以及計算標準偏差和估算方差等，即可以輕松地解決此類通常難以處理的數據分析問題。

1 數據庫和列表管理函數

簡單來說，數據庫就是保存和管理數據的“倉庫”，嚴格一點說，數據庫是“按照數據結構來組織、存儲和管理數據的倉庫”。雖然Excel僅具有部分數據庫的功能，但由于EXCEL的廣泛使用和簡單快捷，使其獲得很好的應用。

列表管理函數是Microsoft Excel中包含的一些工作表函數，用于對存儲在列表或數據庫中的數據進行分析，這些函數統稱為 Dfunctions，在Excel包含的數據庫函數及其應用詳見表1。

每個EXCEL數據庫函數均有3個參數:database、field和criteria。這些參數指向函數所使用的工作表區域。數據庫函數的使用語法的一個例子為:DAVERAGE(database，field，criteria)。

表1 Excel包含的數據庫函數及其應用

database:構成列表或數據庫的單元格區域，又稱為數據清單。數據清單是包含一組相關數據的EXCEL列表，EXCEL列表首行的單元格為列標記(相當于數據庫字段)，或者說列表的第一行包含每一列的標簽，列標記下面的單元格為數據區域(相當于字段中的數據)。

field:指定函數所使用的列。輸入列標簽，并將其包括在雙引號中，例如“SO2” (二氧化硫)或“NO2” (二氧化氮);此外，也可以輸入代表列表中列位置的數字 (不加引號):1表示第一列，2表示第二列，依此類推。

criteria:為包含指定條件的單元格區域。可以為參數criteria指定任意區域，只要它至少包含一個列標簽并且在列標簽下方至少有一個用于指定條件的單元格。

列表管理函數中最值得一提的就是參數criteria，它使用最簡單且最直觀的方式來實現多條件計算或查詢。

2 環境監測數據的處理

表2是2010年全年云南省空氣日報基礎數據匯總表的一部分，監測值的單位是mg/m3。因特殊規定，表中沒有采用EXCEL的日期格式。

表2 2010年云南省空氣日報基礎數據匯總表 (部分)

實際工作中經常從中檢索數據或者進行數據分析的情況如下:

(1)2010年12月份全省SO2測值＞0.1 mg/m3的情況出現的頻次;

(2)昭通市SO2測值在0.05 mg/m3和0.1 mg/m3的情況出現了多少次;

(3)找出昆明2010年12月份和曲靖2010年11月份中SO2的最大測值;

(4)昆明關上測點2010年12月份的SO2平均值是多少;

表3 條件 (criteria)工作表

表4 計算結果 (result)工作表

(5)昆明市2010年12月份共上報了多少次數據;

(6)2010年全省SO2測值＞0.5 mg/m3的NO2的平均值。具體求解步驟如下:

①創建空白工作簿，將基礎數據導入到Excel的“Sheet1”中，并將“Sheet1”改名為“data”，選中全部數據區的數據，重新命名所選擇區域為“Database”。將數據專門存放于一張獨立的工作表中并對數據區域重命名是一個好習慣，可以簡化公式并使公式易于理解，即便數據更新后 (發生變化)，所建立的條件和公式仍可用。

②將上述各個要計算的條件分別輸入到同一工作簿的另一張工作表中，并重命名為“criteria”，上述6個求解的條件分別輸入到該表的第1行到第18行中，在A列中分別用C1、C2至C6表示。

③用同一工作簿的一張新工作表來存放結果，并重命名該表名為“result”，上述6個求解的公式和結果如表4。

條件 (criteria)表和計算結果也可以放在一張表中，但基礎數據最好存放在一張獨立的工作表中。在本例中，基礎數據表 (data)、條件表 (criteria)和結果表 (result)在同一工作簿的獨立的工作表中，具體如表5。

表5 各工作表表名

在不同的工作表中分別存放數據、條件及最終結果是一個很好的習慣，可方便數據的更新、條件的輸入和結果的查詢等。

掌握了上述計算多條件環境下平均值、最大值和頻次的方法后，求解多條件環境下的最小值、求和、求積、估算標準偏差、真實標準偏差、估算方差、真實方差等就很容易了。

3 如何建立Criteria表

通常，對大量的匯總數據進行分析時，其數值計算和數據檢索是在特定的多條件環境下進行的。如何建立多條件環境就是問題的關健，本文通過上面的實例，闡述了在EXCEL表中建立多條件環境的幾種情況。

(1)單列上具有多個條件

如果對于某一列具有兩個或多個篩選條件，那么可直接在各行中從上到下依次鍵入各個條件。例如，上面示例的條件區域C3中顯示的測站包括昆明和曲靖。

(2)一列有兩組以上條件

若要找到滿足兩組以上條件的行，要用相同的列標包括多列。例如，上面示例的條件區域C1中顯示“SO2”列中包含“＞=0.05”或“＜=0.1”的行。

(3)多列上具有單個條件

若要在兩列或多列中查找滿足單個條件的數據，要在條件區域的同一行中輸入所有條件。例如，上面示例的條件區域C4中顯示“測站名稱”為“昆明”、 “測點名稱”為“關上”、年份為2010，月份為12月的數據行。

(4)某一列上具有單個條件

若要找到滿足一列單個條件的數據，請在條件區域輸入條件。例如，上面示例的條件區域C6中顯示所有在“SO2”列中“＞=0.05”的數據行。

從上面介紹可看出，在匯總計算和數據檢索時，無論條件多么復雜，建立相應的Criteria表總是很容易的，并可借此通過列表管理函數獲得所需要的結果。

4 結語

環境監測數據通常都以EXCEL數據表的形式存儲，即便存儲在其它的數據庫軟件系統中，也能很方便地通過引入外部數據、數據轉換等方式形成EXCEL數據表。在數據分析和數據查詢時，又常常需要進行多條件組合篩選，此時，Excel數據庫和列表管理函數就顯示出其強大的優越性。

充分利用數據庫和列表管理函數的多條件分析功能，可方便地獲得復雜條件下的數據檢索與分析，而利用EXCEL的引用功能，可方便快捷地完成大量重復計算的公式輸入［2］。將二者結合起來，即在列表管理函數選擇適當的Excel引用模式，就可從最基本的數據匯總表中方便迅速地制作出所需的數據分析表。如通過上例中的空氣基礎數據匯總表，可方便地獲得某月份各州市空氣監測中各元素超標匯總表、各州市數據上報情況分析表等。

［1］Excel研究組.Excel2007函數與公式速查手冊［M］.北京:電子工業出版社，2008.

［2］白愛民.EXCEL與底泥數據的分析 ［J］.環境科學導刊，2010，29(5).