ch8-1Excel数据库与数据库函数.doc

8.1 Excel数据库简介8.1.1 Excel数据库概述Excel提供了一种极其简易的数据库管理功能，运用它可以进行简单的数据的组织管理工作，如在工作表中输入一个单位的财务数据或人事档案，并对这些数据进行排序、筛选、分类、汇总、查询等操作。但是，Excel并不能取代真正意义上的数据库系统，也不能用于建立较为复杂的数据库信息系统。在数据量很大，数据的种类较多，数据的关系比较复杂时，用户用它来建立数据库管理信息系统时，很难处理好数据之间的各种关系。但这并不表示不能用Excel进行企业的数据管理，毕竟，复杂的大型数据库信息系统是较少数的。在数据量不大（一个工作表最多65 536行），数据种类不多，企业规模不大时，用Excel进行数据的组织和管理工作会给我们的工作带来许多方便，简化我们的工作，提高工作效率。同专业数据库系统相比较，Excel操作简单，数据的表示非常直观，与我们平常工作中所见的表格没有什么两样，任何人只需通过简单的学习就会使用，不像数据库系统要经过专业的学习才会运用。而且，Excel具有相当强大的数据计算功能，提供了许多有用的函数和数据分析工具，如财务函数、统计函数、图表分析等，这些功能恰好是某些专业数据库系统较弱的地方。用户用它进行小单位的财务管理，财务分析，资产管理等非常方便。例如，人们可以直接调用Excel的财务函数进行投资分析、资产折旧、债券分析、工资计算等。8.1.2 认识Excel数据库在Excel中，一个数据库是一张特殊的工作表，这种工作表和数据清单相似，有关数据清单的内容在第5章已有较为详细的讨论，请读者参考。图8.1就是一个Excel数据库，实际它也是一个数据清单。图8.1 数据库示例如图8.1所示的Excel数据库具有以下特征：（1）数据库是一个数据区域，该区域的首行是一些描述性的词组，它能够表明相应列的数据性质及数据类型。在数据库中，这一行称为数据库的结构名，每一列的第一行称为属性或列标，也称为字段。例如，在图8.1中，数据库是从第6行开始的数据区域。第6行中的“姓名”、“公医号”、“类别”、“工龄”、“门自付金额”、“门实报金额”都是字段。同一字段的取值（即Excel数据库的相同列）类型相同。例如，在“姓名”字段列中的每一个单元格内都应该是字符，而不应是数值型数据。遗憾的是，Excel并不进行数据的强制性检查，就算在“姓名”列的某个单元格中输入数值，它也不会报错，这就只有靠用户的正确输入来保证数据的正确性。这是Excel数据库与其他专业数据库管理系统的一个很大的区别。（2）数据库中的每一行称为一个记录，一个记录代表一个客观事物的各种数据特征。在如图8.1所示的数据库中，每一行表示同一个职工的各种数据，如第7行就表示了一个职工的各项数据，姓名为彭波、公医号为2153（3）在多数情况下，数据库中应该有一个条件区域，条件区域可用作数据库函数的参数。例如，数据的筛选、查询等数据库函数就需要一个条件区域。在图8.1中，A1:F2就是一个条件区域。在Excel中，用户可以很容易地将数据清单用作数据库。在执行数据库操作时，Excel 会自动将数据清单视作数据库，这时，数据清单中的列是数据库中的字段。数据清单中的列标志是数据库中的字段名称。数据清单中的每一行对应数据库中的一个记录。同一个正规的数据库系统相比，Excel的数据库具有一些局限性： 一个Excel工作表只有65 536行，256列，对于一般的表格而言，它已经非常大了，但对于一个大型数据库而言，60 000多条记录就太少了。 不具备数据的完整性检查，需要用户自己考虑设置数据的有效性和正确性。 占用的存储空间比真正的数据库管理系统要多。因为Excel的数据库中除了存放数据之外，还要存放工作表的格式，各单元格中的公式，甚至图形等内容。在建立和使用数据库时，用户还应注意以下问题： 避免在一个工作表中建立多个数据库。因为在一个工作表中建立多个数据库不便于数据库的各种操作。 数据库所占的单元格区域与其他数据之间至少要留出一个空行或空列，这样才不会导致数据混淆。 不能在数据库的各记录之间或各字段列之间放置空行或空列，因为空行或空列会把一个数据库一分为二。 数据库区域中的每个列都应该有一个列标题，这个列标题就是字段名。 条件区域最好不要放在数据库的数据区域下方。因为用记录单添加数据时，Excel会在原数据库的下边添加数据记录，如果数据库的下边非空，就不能利用记录单添加数据。8.2 数据库函数8.2.1 D函数概述Execl提供了12个工作表函数用于数据清单或数据库中的数据分析，这些函数统称D函数，每个D函数都有3个相同的参数：database，field，criteria。这些参数都与数据库的数据区域或条件区域有关。D函数有着共同的格式，其形式如下：Dfunction(database, field, criteria)其中，Dfunction代表函数的名称，它可以是12个D函数中的任一个；database为构成数据清单或数据库的单元格区域。field为指定函数所使用的数据列。数据清单中的数据列必须在第一行具有列标志。field可以是文本，即两端带引号的标志项。例如，在如图8.1所示的数据库中，“姓名”、“性别”等都是字段名，它们可以出现在D函数的field参数位置。此外，field也可以数据清单中，代表数据列所在的位置的数字：1表示数据库区域的第一列，2表示第二列，其余的以此类推。criteria为对一组单元格区域的引用。这组单元格区域用来设定函数的匹配条件。数据库函数可以返回数据清单中与条件区域所设定的条件相匹配的信息。条件区域包含了函数所要汇总的数据列（即字段）在数据清单中的列标志的一个副本。此条件引用可以以单元格区域的形式输入。例如，在如图8.1所示的数据库中，A1:F2就是数据库的条件区域，可以在该数据库D函数的criteria位置输入A1:F2，表示函数应该满足的条件；也可以先为条件区域取一个名字，然后在D函数的criteria位置输入代表条件区域的名称。说明： 可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。例如，图8.1中的C1:C2区域就是一个条件区域，它表示“类别”为“在职”的职工；同理，D1:D2也可作为一个条件区域，它表示工龄高于20年的职工；也可以把C1:D2作为一个条件区域，它表示的条件是“工龄高于20年的在职职工”。可以定义一个名字来表示条件区域。例如，可以将C1:D2区域命名为WorkAge，那么在数据库函数中就可使用该名称作为 criteria参数，也可以用C1:D2区域作为D函数的参数criteria，两者的意义完全相同。 虽然条件区域可以建立在工作表的任意位置，但不要将条件区域置于数据清单的下方。如果使用“数据”菜单中的“记录单”命令在数据清单中添加信息，新的信息将被加在数据清单下方的第一行上。如果数据清单下方的行非空，Microsoft Excel 将无法添加新的信息。 条件区域不能与数据库的数据区域相重叠。 如果要对数据库的整个列进行操作，需要在条件区域的相应列标志下方保留一个空行。8.2.2 D函数介绍1DCOUNT/ DCOUNTA 函数格式：DCOUNT(database, field, criteria)DCOUNTA(database, field, criteria)DCOUNT函数计算数据库的指定字段中，满足给定条件并且包含数值的单元格数目。参数field为可选项，如果省略， DCOUNT函数返回数据库中满足条件criteria 的所有记录数。DCOUNTA函数计算数据库的指定字段中，满足给定条件的非空单元格的数目。如果忽略 field，则DCOUNTA函数将返回符合条件的所有记录的计数。如果包含field，则DCOUNTA函数只返回在field列中有值且符合条件的记录。2DAVERAGE函数格式：DAVERAGE(database, field, criteria)DAVERAGE函数计算数据库中满足给定条件的数据列中的数值的平均值。3DMAX / DMIN函数格式：DMAX(database, field, criteria)DMIN(database, field, criteria)DMAX函数计算数据库的指定列中，满足给定条件单元格中的最大数值。DMIN函数计算数据库的指定列中，满足给定条件单元格中的最小数值。4DSUM函数格式：DSUM(database, field, criteria)DSUM函数计算数据库的指定列中，满足给定条件单元格中的数字之和。5DPRODUCT函数格式：DPRODUCT(database, field, criteria)该函数计算数据库的指定列中，满足给定条件的单元格中的数值乘积。6DSTDEV函数格式：DSTDEV(database, field, criteria)在数据库的指定列中，将满足给定条件单元格中的数字作为一个样本，估算样本总体的标准偏差。7DSTDEVP函数格式：DSTDEVP(database, field, criteria)在数据库的指定列中，将满足给定条件的单元格中的数字作为样本总体，计算总体的标准偏差。8DVAR函数格式：DVAR(database, field, criteria)在数据库的指定列中，将满足给定条件的单元格中的数字作为一个样本，估算样本总体的方差。9DVARP函数格式：DVARP(database, field, criteria)在数据库的指定列中，将满足给定条件的单元格中的数字作为样本总体，计算总体的方差。8.2.3 D函数应用的举例图8.2是某电器生产厂产品的年产量和生产成本的数据库。每个记录包含一种产品的情况。数据库定义在B10:G17上，条件定义在 B6:H8 上。为了应用的方便，首先定义数据库区域（B10:G17）的名字为DataBase。下面是各种D函数的应用举例。DCOUNT(DataBase,产量,B6:H7)=1。此函数查找产量在500800之间的电视的记录，并计算这些记录中字段包含数字的单元格数目，结果为1表示只有一种电视的产量在500800之间。DCOUNTA(DataBase,产量,E6:H7)=2。此函数查找产量为 500800之间的各种产品记录，并计算这些记录中字段非空的单元格数目，结果为2表明有两种产量介于500800之间的产品，从图8.2可以看出一种是34英寸的电视，一种是239H的电冰箱。图8.2 一个简易的电器生产数据库DMAX(DataBase,利润,B6:B8)=1 851，它是电视和空调的最大利润。DMIN(DataBase,利润,B6:B8)=316，它是电视和空调的最小利润。DSUM(Database,利润,B6:B8)= 4 909，是电视和空调的总利润。DSUM(Database,产量, B6:B8)= 3 077，是电视和空调的总产量。DPRODUCT(DataBase,产量,B6:B7)=205 205 574，这个数字没有多大的实际意义，它是各种电视的产量相乘而得出的值。DAVERAGE(DataBase,产量,B6:B7)=608，是各种电视的平均生产量。同样，函数DAVERAGE(DataBase,4,B6:B7)也等于608，该函数的第2个参数“4”表示如图8.2所示的数据库的第4列，这列就是“产量”。DSTDEV(DataBase,产量,B6:B7)=184.366，如果数据库中的数据只是该厂所有产品的一个样本，此值就是电视产品的估算标准偏差。当然，这个样例并不恰当，因为如图