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函数介绍1．DCOUNT/ DCOUNTA 函数格式：DCOUNT(database, field, criteria)DCOUNTA(database, field, criteria)DCOUNT函数计算数据库的指定字段中，满足给定条件并且包含数值的单元格数目参数field为可选项，如果省略， DCOUNT函数返回数据库中满足条件criteria 的所有记录数DCOUNTA函数计算数据库的指定字段中，满足给定条件的非空单元格的数目。

如果忽略 field，则DCOUNTA函数将返回符合条件的所有记录的计数如果包含field，则DCOUNTA函数只返回在field列中有值且符合条件的记录2．DAVERAGE函数格式：DAVERAGE(database, field, criteria)DAVERAGE函数计算数据库中满足给定条件的数据列中的数值的平均值3．DMAX / DMIN函数格式：DMAX(database, field, criteria)DMIN(database, field, criteria)DMAX函数计算数据库的指定列中，满足给定条件单元格中的最大数值DMIN函数计算数据库的指定列中，满足给定条件单元格中的最小数值4．DSUM函数格式：DSUM(database, field, criteria)DSUM函数计算数据库的指定列中，满足给定条件单元格中的数字之和5．DPRODUCT函数格式：DPRODUCT(database, field, criteria)该函数计算数据库的指定列中，满足给定条件的单元格中的数值乘积6．DSTDEV函数格式：DSTDEV(database, field, criteria)在数据库的指定列中，将满足给定条件单元格中的数字作为一个样本，估算样本总体的标准偏差。

7．DSTDEVP函数格式：DSTDEVP(database, field, criteria)在数据库的指定列中，将满足给定条件的单元格中的数字作为样本总体，计算总体的标准偏差8．DVAR函数格式：DVAR(database, field, criteria)在数据库的指定列中，将满足给定条件的单元格中的数字作为一个样本，估算样本总体的方差9．DVARP函数格式：DVARP(database, field, criteria)在数据库的指定列中，将满足给定条件的单元格中的数字作为样本总体，计算总体的方差8.2.3 D函数应用的举例图8.2是某电器生产厂产品的年产量和生产成本的数据库每个记录包含一种产品的情况数据库定义在B10:G17上，条件定义在 B6:H8 上为了应用的方便，首先定义数据库区域（B10:G17）的名字为DataBase下面是各种D函数的应用举例DCOUNT(DataBase,"产量",B6:H7)=1此函数查找产量在500~800之间的电视的记录，并计算这些记录中字段包含数字的单元格数目，结果为1表示只有一种电视的产量在500~800之间DCOUNTA(DataBase,"产量",E6:H7)=2。

此函数查找产量为 500~800之间的各种产品记录，并计算这些记录中字段非空的单元格数目，结果为2表明有两种产量介于500~800之间的产品，从图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,"产量",。