利用excel的引用功能進(jìn)行分析

利用excel的引用功能進(jìn)行分析

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