白愛民
(云南省環(huán)境監(jiān)測中心站,云南昆明 650034)
Microsoft Excel是最優(yōu)秀的電子表格軟件之一,它在數(shù)據(jù)綜合管理和分析方面具有功能強(qiáng)大、技術(shù)先進(jìn)、使用方便等特點(diǎn)[1]。數(shù)據(jù)庫是Excel的三大功能 (表格、圖表、數(shù)據(jù)庫)之一,利用它可方便地對匯總數(shù)據(jù)進(jìn)行分析,并快速從大量的數(shù)據(jù)表中檢索出所需的數(shù)據(jù)。
在環(huán)境監(jiān)測數(shù)據(jù)統(tǒng)計工作中,常常需要進(jìn)行多條件環(huán)境下的數(shù)據(jù)分析與檢索,例如求解某一時間范圍、某一空間地域、甚至某一元素的監(jiān)測值范圍下,另一元素的監(jiān)測平均值等。使用Excel的數(shù)據(jù)庫和列表管理函數(shù)不僅可以求解出同時滿足多個條件的平均值、數(shù)據(jù)個數(shù)、最大值、最小值,還可以對數(shù)據(jù)庫中滿足條件的記錄的字段列中的數(shù)字求和,將數(shù)據(jù)庫中滿足條件的記錄的特定字段中的數(shù)值相乘,以及計算標(biāo)準(zhǔn)偏差和估算方差等,即可以輕松地解決此類通常難以處理的數(shù)據(jù)分析問題。
簡單來說,數(shù)據(jù)庫就是保存和管理數(shù)據(jù)的“倉庫”,嚴(yán)格一點(diǎn)說,數(shù)據(jù)庫是“按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的倉庫”。雖然Excel僅具有部分?jǐn)?shù)據(jù)庫的功能,但由于EXCEL的廣泛使用和簡單快捷,使其獲得很好的應(yīng)用。
列表管理函數(shù)是Microsoft Excel中包含的一些工作表函數(shù),用于對存儲在列表或數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行分析,這些函數(shù)統(tǒng)稱為 Dfunctions,在Excel包含的數(shù)據(jù)庫函數(shù)及其應(yīng)用詳見表1。
每個EXCEL數(shù)據(jù)庫函數(shù)均有3個參數(shù):database、field和criteria。這些參數(shù)指向函數(shù)所使用的工作表區(qū)域。數(shù)據(jù)庫函數(shù)的使用語法的一個例子為:DAVERAGE(database,field,criteria)。
表1 Excel包含的數(shù)據(jù)庫函數(shù)及其應(yīng)用
database:構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域,又稱為數(shù)據(jù)清單。數(shù)據(jù)清單是包含一組相關(guān)數(shù)據(jù)的EXCEL列表,EXCEL列表首行的單元格為列標(biāo)記(相當(dāng)于數(shù)據(jù)庫字段),或者說列表的第一行包含每一列的標(biāo)簽,列標(biāo)記下面的單元格為數(shù)據(jù)區(qū)域(相當(dāng)于字段中的數(shù)據(jù))。
field:指定函數(shù)所使用的列。輸入列標(biāo)簽,并將其包括在雙引號中,例如“SO2” (二氧化硫)或“NO2” (二氧化氮);此外,也可以輸入代表列表中列位置的數(shù)字 (不加引號):1表示第一列,2表示第二列,依此類推。
criteria:為包含指定條件的單元格區(qū)域??梢詾閰?shù)criteria指定任意區(qū)域,只要它至少包含一個列標(biāo)簽并且在列標(biāo)簽下方至少有一個用于指定條件的單元格。
列表管理函數(shù)中最值得一提的就是參數(shù)criteria,它使用最簡單且最直觀的方式來實現(xiàn)多條件計算或查詢。
表2是2010年全年云南省空氣日報基礎(chǔ)數(shù)據(jù)匯總表的一部分,監(jiān)測值的單位是mg/m3。因特殊規(guī)定,表中沒有采用EXCEL的日期格式。
表2 2010年云南省空氣日報基礎(chǔ)數(shù)據(jù)匯總表 (部分)
實際工作中經(jīng)常從中檢索數(shù)據(jù)或者進(jìn)行數(shù)據(jù)分析的情況如下:
(1)2010年12月份全省SO2測值>0.1 mg/m3的情況出現(xiàn)的頻次;
(2)昭通市SO2測值在0.05 mg/m3和0.1 mg/m3的情況出現(xiàn)了多少次;
(3)找出昆明2010年12月份和曲靖2010年11月份中SO2的最大測值;
(4)昆明關(guān)上測點(diǎn)2010年12月份的SO2平均值是多少;
表3 條件 (criteria)工作表
表4 計算結(jié)果 (result)工作表
(5)昆明市2010年12月份共上報了多少次數(shù)據(jù);
(6)2010年全省SO2測值>0.5 mg/m3的NO2的平均值。具體求解步驟如下:
①創(chuàng)建空白工作簿,將基礎(chǔ)數(shù)據(jù)導(dǎo)入到Excel的“Sheet1”中,并將“Sheet1”改名為“data”,選中全部數(shù)據(jù)區(qū)的數(shù)據(jù),重新命名所選擇區(qū)域為“Database”。將數(shù)據(jù)專門存放于一張獨(dú)立的工作表中并對數(shù)據(jù)區(qū)域重命名是一個好習(xí)慣,可以簡化公式并使公式易于理解,即便數(shù)據(jù)更新后 (發(fā)生變化),所建立的條件和公式仍可用。
②將上述各個要計算的條件分別輸入到同一工作簿的另一張工作表中,并重命名為“criteria”,上述6個求解的條件分別輸入到該表的第1行到第18行中,在A列中分別用C1、C2至C6表示。
③用同一工作簿的一張新工作表來存放結(jié)果,并重命名該表名為“result”,上述6個求解的公式和結(jié)果如表4。
條件 (criteria)表和計算結(jié)果也可以放在一張表中,但基礎(chǔ)數(shù)據(jù)最好存放在一張獨(dú)立的工作表中。在本例中,基礎(chǔ)數(shù)據(jù)表 (data)、條件表 (criteria)和結(jié)果表 (result)在同一工作簿的獨(dú)立的工作表中,具體如表5。
表5 各工作表表名
在不同的工作表中分別存放數(shù)據(jù)、條件及最終結(jié)果是一個很好的習(xí)慣,可方便數(shù)據(jù)的更新、條件的輸入和結(jié)果的查詢等。
掌握了上述計算多條件環(huán)境下平均值、最大值和頻次的方法后,求解多條件環(huán)境下的最小值、求和、求積、估算標(biāo)準(zhǔn)偏差、真實標(biāo)準(zhǔn)偏差、估算方差、真實方差等就很容易了。
通常,對大量的匯總數(shù)據(jù)進(jìn)行分析時,其數(shù)值計算和數(shù)據(jù)檢索是在特定的多條件環(huán)境下進(jìn)行的。如何建立多條件環(huán)境就是問題的關(guān)健,本文通過上面的實例,闡述了在EXCEL表中建立多條件環(huán)境的幾種情況。
(1)單列上具有多個條件
如果對于某一列具有兩個或多個篩選條件,那么可直接在各行中從上到下依次鍵入各個條件。例如,上面示例的條件區(qū)域C3中顯示的測站包括昆明和曲靖。
(2)一列有兩組以上條件
若要找到滿足兩組以上條件的行,要用相同的列標(biāo)包括多列。例如,上面示例的條件區(qū)域C1中顯示“SO2”列中包含“>=0.05”或“<=0.1”的行。
(3)多列上具有單個條件
若要在兩列或多列中查找滿足單個條件的數(shù)據(jù),要在條件區(qū)域的同一行中輸入所有條件。例如,上面示例的條件區(qū)域C4中顯示“測站名稱”為“昆明”、 “測點(diǎn)名稱”為“關(guān)上”、年份為2010,月份為12月的數(shù)據(jù)行。
(4)某一列上具有單個條件
若要找到滿足一列單個條件的數(shù)據(jù),請在條件區(qū)域輸入條件。例如,上面示例的條件區(qū)域C6中顯示所有在“SO2”列中“>=0.05”的數(shù)據(jù)行。
從上面介紹可看出,在匯總計算和數(shù)據(jù)檢索時,無論條件多么復(fù)雜,建立相應(yīng)的Criteria表總是很容易的,并可借此通過列表管理函數(shù)獲得所需要的結(jié)果。
環(huán)境監(jiān)測數(shù)據(jù)通常都以EXCEL數(shù)據(jù)表的形式存儲,即便存儲在其它的數(shù)據(jù)庫軟件系統(tǒng)中,也能很方便地通過引入外部數(shù)據(jù)、數(shù)據(jù)轉(zhuǎn)換等方式形成EXCEL數(shù)據(jù)表。在數(shù)據(jù)分析和數(shù)據(jù)查詢時,又常常需要進(jìn)行多條件組合篩選,此時,Excel數(shù)據(jù)庫和列表管理函數(shù)就顯示出其強(qiáng)大的優(yōu)越性。
充分利用數(shù)據(jù)庫和列表管理函數(shù)的多條件分析功能,可方便地獲得復(fù)雜條件下的數(shù)據(jù)檢索與分析,而利用EXCEL的引用功能,可方便快捷地完成大量重復(fù)計算的公式輸入[2]。將二者結(jié)合起來,即在列表管理函數(shù)選擇適當(dāng)?shù)腅xcel引用模式,就可從最基本的數(shù)據(jù)匯總表中方便迅速地制作出所需的數(shù)據(jù)分析表。如通過上例中的空氣基礎(chǔ)數(shù)據(jù)匯總表,可方便地獲得某月份各州市空氣監(jiān)測中各元素超標(biāo)匯總表、各州市數(shù)據(jù)上報情況分析表等。
[1]Excel研究組.Excel2007函數(shù)與公式速查手冊[M].北京:電子工業(yè)出版社,2008.
[2]白愛民.EXCEL與底泥數(shù)據(jù)的分析 [J].環(huán)境科學(xué)導(dǎo)刊,2010,29(5).