EXCEL數(shù)據(jù)庫(kù)的應(yīng)用

白愛(ài)民

(云南省環(huán)境監(jiān)測(cè)中心站，云南昆明 650034)

Microsoft Excel是最優(yōu)秀的電子表格軟件之一，它在數(shù)據(jù)綜合管理和分析方面具有功能強(qiáng)大、技術(shù)先進(jìn)、使用方便等特點(diǎn)［1］。數(shù)據(jù)庫(kù)是Excel的三大功能 (表格、圖表、數(shù)據(jù)庫(kù))之一，利用它可方便地對(duì)匯總數(shù)據(jù)進(jìn)行分析，并快速?gòu)拇罅康臄?shù)據(jù)表中檢索出所需的數(shù)據(jù)。

在環(huán)境監(jiān)測(cè)數(shù)據(jù)統(tǒng)計(jì)工作中，常常需要進(jìn)行多條件環(huán)境下的數(shù)據(jù)分析與檢索，例如求解某一時(shí)間范圍、某一空間地域、甚至某一元素的監(jiān)測(cè)值范圍下，另一元素的監(jiān)測(cè)平均值等。使用Excel的數(shù)據(jù)庫(kù)和列表管理函數(shù)不僅可以求解出同時(shí)滿(mǎn)足多個(gè)條件的平均值、數(shù)據(jù)個(gè)數(shù)、最大值、最小值，還可以對(duì)數(shù)據(jù)庫(kù)中滿(mǎn)足條件的記錄的字段列中的數(shù)字求和，將數(shù)據(jù)庫(kù)中滿(mǎn)足條件的記錄的特定字段中的數(shù)值相乘，以及計(jì)算標(biāo)準(zhǔn)偏差和估算方差等，即可以輕松地解決此類(lèi)通常難以處理的數(shù)據(jù)分析問(wèn)題。

1 數(shù)據(jù)庫(kù)和列表管理函數(shù)

簡(jiǎn)單來(lái)說(shuō)，數(shù)據(jù)庫(kù)就是保存和管理數(shù)據(jù)的“倉(cāng)庫(kù)”，嚴(yán)格一點(diǎn)說(shuō)，數(shù)據(jù)庫(kù)是“按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)”。雖然Excel僅具有部分?jǐn)?shù)據(jù)庫(kù)的功能，但由于EXCEL的廣泛使用和簡(jiǎn)單快捷，使其獲得很好的應(yīng)用。

列表管理函數(shù)是Microsoft Excel中包含的一些工作表函數(shù)，用于對(duì)存儲(chǔ)在列表或數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行分析，這些函數(shù)統(tǒng)稱(chēng)為 Dfunctions，在Excel包含的數(shù)據(jù)庫(kù)函數(shù)及其應(yīng)用詳見(jiàn)表1。

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

表1 Excel包含的數(shù)據(jù)庫(kù)函數(shù)及其應(yīng)用

database:構(gòu)成列表或數(shù)據(jù)庫(kù)的單元格區(qū)域，又稱(chēng)為數(shù)據(jù)清單。數(shù)據(jù)清單是包含一組相關(guān)數(shù)據(jù)的EXCEL列表，EXCEL列表首行的單元格為列標(biāo)記(相當(dāng)于數(shù)據(jù)庫(kù)字段)，或者說(shuō)列表的第一行包含每一列的標(biāo)簽，列標(biāo)記下面的單元格為數(shù)據(jù)區(qū)域(相當(dāng)于字段中的數(shù)據(jù))。

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

criteria:為包含指定條件的單元格區(qū)域?？梢詾閰?shù)criteria指定任意區(qū)域，只要它至少包含一個(gè)列標(biāo)簽并且在列標(biāo)簽下方至少有一個(gè)用于指定條件的單元格。

列表管理函數(shù)中最值得一提的就是參數(shù)criteria，它使用最簡(jiǎn)單且最直觀的方式來(lái)實(shí)現(xiàn)多條件計(jì)算或查詢(xún)。

2 環(huán)境監(jiān)測(cè)數(shù)據(jù)的處理

表2是2010年全年云南省空氣日?qǐng)?bào)基礎(chǔ)數(shù)據(jù)匯總表的一部分，監(jiān)測(cè)值的單位是mg/m3。因特殊規(guī)定，表中沒(méi)有采用EXCEL的日期格式。

表2 2010年云南省空氣日?qǐng)?bào)基礎(chǔ)數(shù)據(jù)匯總表 (部分)

實(shí)際工作中經(jīng)常從中檢索數(shù)據(jù)或者進(jìn)行數(shù)據(jù)分析的情況如下:

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

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

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

(4)昆明關(guān)上測(cè)點(diǎn)2010年12月份的SO2平均值是多少;

表3 條件 (criteria)工作表

表4 計(jì)算結(jié)果 (result)工作表

(5)昆明市2010年12月份共上報(bào)了多少次數(shù)據(jù);

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

①創(chuàng)建空白工作簿，將基礎(chǔ)數(shù)據(jù)導(dǎo)入到Excel的“Sheet1”中，并將“Sheet1”改名為“data”，選中全部數(shù)據(jù)區(qū)的數(shù)據(jù)，重新命名所選擇區(qū)域?yàn)椤癉atabase”。將數(shù)據(jù)專(zhuān)門(mén)存放于一張獨(dú)立的工作表中并對(duì)數(shù)據(jù)區(qū)域重命名是一個(gè)好習(xí)慣，可以簡(jiǎn)化公式并使公式易于理解，即便數(shù)據(jù)更新后 (發(fā)生變化)，所建立的條件和公式仍可用。

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

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

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

表5 各工作表表名

在不同的工作表中分別存放數(shù)據(jù)、條件及最終結(jié)果是一個(gè)很好的習(xí)慣，可方便數(shù)據(jù)的更新、條件的輸入和結(jié)果的查詢(xún)等。

掌握了上述計(jì)算多條件環(huán)境下平均值、最大值和頻次的方法后，求解多條件環(huán)境下的最小值、求和、求積、估算標(biāo)準(zhǔn)偏差、真實(shí)標(biāo)準(zhǔn)偏差、估算方差、真實(shí)方差等就很容易了。

3 如何建立Criteria表

通常，對(duì)大量的匯總數(shù)據(jù)進(jìn)行分析時(shí)，其數(shù)值計(jì)算和數(shù)據(jù)檢索是在特定的多條件環(huán)境下進(jìn)行的。如何建立多條件環(huán)境就是問(wèn)題的關(guān)健，本文通過(guò)上面的實(shí)例，闡述了在EXCEL表中建立多條件環(huán)境的幾種情況。

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

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

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

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

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

若要在兩列或多列中查找滿(mǎn)足單個(gè)條件的數(shù)據(jù)，要在條件區(qū)域的同一行中輸入所有條件。例如，上面示例的條件區(qū)域C4中顯示“測(cè)站名稱(chēng)”為“昆明”、 “測(cè)點(diǎn)名稱(chēng)”為“關(guān)上”、年份為2010，月份為12月的數(shù)據(jù)行。

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

若要找到滿(mǎn)足一列單個(gè)條件的數(shù)據(jù)，請(qǐng)?jiān)跅l件區(qū)域輸入條件。例如，上面示例的條件區(qū)域C6中顯示所有在“SO2”列中“＞=0.05”的數(shù)據(jù)行。

從上面介紹可看出，在匯總計(jì)算和數(shù)據(jù)檢索時(shí)，無(wú)論條件多么復(fù)雜，建立相應(yīng)的Criteria表總是很容易的，并可借此通過(guò)列表管理函數(shù)獲得所需要的結(jié)果。

4 結(jié)語(yǔ)

環(huán)境監(jiān)測(cè)數(shù)據(jù)通常都以EXCEL數(shù)據(jù)表的形式存儲(chǔ)，即便存儲(chǔ)在其它的數(shù)據(jù)庫(kù)軟件系統(tǒng)中，也能很方便地通過(guò)引入外部數(shù)據(jù)、數(shù)據(jù)轉(zhuǎn)換等方式形成EXCEL數(shù)據(jù)表。在數(shù)據(jù)分析和數(shù)據(jù)查詢(xún)時(shí)，又常常需要進(jìn)行多條件組合篩選，此時(shí)，Excel數(shù)據(jù)庫(kù)和列表管理函數(shù)就顯示出其強(qiáng)大的優(yōu)越性。

充分利用數(shù)據(jù)庫(kù)和列表管理函數(shù)的多條件分析功能，可方便地獲得復(fù)雜條件下的數(shù)據(jù)檢索與分析，而利用EXCEL的引用功能，可方便快捷地完成大量重復(fù)計(jì)算的公式輸入［2］。將二者結(jié)合起來(lái)，即在列表管理函數(shù)選擇適當(dāng)?shù)腅xcel引用模式，就可從最基本的數(shù)據(jù)匯總表中方便迅速地制作出所需的數(shù)據(jù)分析表。如通過(guò)上例中的空氣基礎(chǔ)數(shù)據(jù)匯總表，可方便地獲得某月份各州市空氣監(jiān)測(cè)中各元素超標(biāo)匯總表、各州市數(shù)據(jù)上報(bào)情況分析表等。

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

［2］白愛(ài)民.EXCEL與底泥數(shù)據(jù)的分析 ［J］.環(huán)境科學(xué)導(dǎo)刊，2010，29(5).