利用excel的引用功能进行分析

利用excel的引用功能进行分析

微软excel是最好的电子表格软件之一。它在数据的综合管理和分析方面发挥着重要作用，技术先进，使用方便等特点。数据库是Excel的三大功能(表格、图表、数据库)之一,利用它可方便地对汇总数据进行分析,并快速从大量的数据表中检索出所需的数据。在环境监测数据统计工作中,常常需要进行多条件环境下的数据分析与检索,例如求解某一时间范围、某一空间地域、甚至某一元素的监测值范围下,另一元素的监测平均值等。使用Excel的数据库和列表管理函数不仅可以求解出同时满足多个条件的平均值、数据个数、最大值、最小值,还可以对数据库中满足条件的记录的字段列中的数字求和,将数据库中满足条件的记录的特定字段中的数值相乘,以及计算标准偏差和估算方差等,即可以轻松地解决此类通常难以处理的数据分析问题。1数据库函数及其使用简单来说,数据库就是保存和管理数据的“仓库”,严格一点说,数据库是“按照数据结构来组织、存储和管理数据的仓库”。虽然Excel仅具有部分数据库的功能,但由于EXCEL的广泛使用和简单快捷,使其获得很好的应用。列表管理函数是MicrosoftExcel中包含的一些工作表函数,用于对存储在列表或数据库中的数据进行分析,这些函数统称为Dfunctions,在Excel包含的数据库函数及其应用详见表1。每个EXCEL数据库函数均有3个参数:database、field和criteria。这些参数指向函数所使用的工作表区域。数据库函数的使用语法的一个例子为:DAVERAGE(database,field,criteria)。database:构成列表或数据库的单元格区域,又称为数据清单。数据清单是包含一组相关数据的EXCEL列表,EXCEL列表首行的单元格为列标记(相当于数据库字段),或者说列表的第一行包含每一列的标签,列标记下面的单元格为数据区域(相当于字段中的数据)。field:指定函数所使用的列。输入列标签,并将其包括在双引号中,例如“SO2”(二氧化硫)或“NO2”(二氧化氮);此外,也可以输入代表列表中列位置的数字(不加引号):1表示第一列,2表示第二列,依此类推。criteria:为包含指定条件的单元格区域。可以为参数criteria指定任意区域,只要它至少包含一个列标签并且在列标签下方至少有一个用于指定条件的单元格。列表管理函数中最值得一提的就是参数criteria,它使用最简单且最直观的方式来实现多条件计算或查询。2so2测值的求解表2是2010年全年云南省空气日报基础数据汇总表的一部分,监测值的单位是mg/m3。因特殊规定,表中没有采用EXCEL的日期格式。实际工作中经常从中检索数据或者进行数据分析的情况如下:(1)2010年12月份全省SO2测值>0.1mg/m3的情况出现的频次;(2)昭通市SO2测值在0.05mg/m3和0.1mg/m3的情况出现了多少次;(3)找出昆明2010年12月份和曲靖2010年11月份中SO2的最大测值;(4)昆明关上测点2010年12月份的SO2平均值是多少;(5)昆明市2010年12月份共上报了多少次数据;(6)2010年全省SO2测值>0.5mg/m3的NO2的平均值。具体求解步骤如下:①创建空白工作簿,将基础数据导入到Excel的“Sheet1”中,并将“Sheet1”改名为“data”,选中全部数据区的数据,重新命名所选择区域为“Database”。将数据专门存放于一张独立的工作表中并对数据区域重命名是一个好习惯,可以简化公式并使公式易于理解,即便数据更新后(发生变化),所建立的条件和公式仍可用。②将上述各个要计算的条件分别输入到同一工作簿的另一张工作表中,并重命名为“criteria”,上述6个求解的条件分别输入到该表的第1行到第18行中,在A列中分别用C1、C2至C6表示。③用同一工作簿的一张新工作表来存放结果,并重命名该表名为“result”,上述6个求解的公式和结果如表4。条件(criteria)表和计算结果也可以放在一张表中,但基础数据最好存放在一张独立的工作表中。在本例中,基础数据表(data)、条件表(criteria)和结果表(result)在同一工作簿的独立的工作表中,具体如表5。在不同的工作表中分别存放数据、条件及最终结果是一个很好的习惯,可方便数据的更新、条件的输入和结果的查询等。掌握了上述计算多条件环境下平均值、最大值和频次的方法后,求解多条件环境下的最小值、求和、求积、估算标准偏差、真实标准偏差、估算方差、真实方差等就很容易了。3数据检索的研究通常,对大量的汇总数据进行分析时,其数值计算和数据检索是在特定的多条件环境下进行的。如何建立多条件环境就是问题的关健,本文通过上面的实例,阐述了在EXCEL表中建立多条件环境的几种情况。(1)直接进入各行工况的直接进入各行工况,以下至下依申请如果对于某一列具有两个或多个筛选条件,那么可直接在各行中从上到下依次键入各个条件。例如,上面示例的条件区域C3中显示的测站包括昆明和曲靖。(2)同的列标和多列若要找到满足两组以上条件的行,要用相同的列标包括多列。例如,上面示例的条件区域C1中显示“SO2”列中包含“>=0.05”或“<=0.1”的行。(3)输入条件输入若要在两列或多列中查找满足单个条件的数据,要在条件区域的同一行中输入所有条件。例如,上面示例的条件区域C4中显示“测站名称”为“昆明”、“测点名称”为“关上”、年份为2010,月份为12月的数据行。(4)条件区域c6若要找到满足一列单个条件的数据,请在条件区域输入条件。例如,上面示例的条件区域C6中显示所有在“SO2”列中“>=0.05”的数据行。从上面介绍可看出,在汇总计算和数据检索时,无论条件多么复杂,建立相应的Criteria表总是很容易的,并可借此通过列表管理函数获得所需要的结果。4数据库和可行推荐模式的选择环境监测数据通常都以EXCEL数据表的形式存储,即便存储在其它的数据库软件系统中,也能很方便地通过引入外部数据、数据转换等方式形成EXCEL数据表。在数据分析和数据查询时,又常常需要进行多条件组合筛选,此时,Excel数据库和列表管理函数就显示出其强大的优越性。充分利用数据库和列表管理函数的多条件分析功能,可方便地获得复杂条件下的数据检索与分析,而利用EXCEL的引用功