Excel周末编程速成班第27课：数据库任务

excelperfect

主要内容：

• Excel中的数据库

• 数据库基础知识：记录和字段

• 排序记录

• 筛选记录

• 创建和使用数据输入窗体

• 使用数据库统计函数

Excel被设计为电子表格应用程序时，它还提供了实现数据库功能的工具。本课讲解什么是数据库应用程序以及如何使用Excel来执行数据库任务。

数据库与Excel

数据库程序无疑是使用最广泛的计算机应用程序类型。

• 当你使用图书馆的在线目录查找书籍时，将使用数据库应用程序。

• 当你订购服装、软件或电子产品的电话订单时，与你交谈的销售代表正在使用数据库应用程序检查库存并输入你的订单。

• 当你使用Quicken或其他家庭财务程序来平衡支票簿时，它也是一个数据库程序。

大多数数据库应用程序都是高度专业化的数据库任务，无法执行其他任何操作。作为电子表格程序，Excel不具备这些专用程序的数据库功能，并且有许多数据库任务完全超出了Excel的能力。即便如此，Excel的数据库工具仍提供了很多功能，对于执行更简单的数据库任务来说已经绰绰有余了。附带的好处是，由于Excel的数据库功能有限，因此相对容易学习和使用。

数据库基础

术语数据库是一种组织信息的特殊方式。这是一种常识性的方法，几乎每个人都曾经以一种或另一种方式使用它，即使他们从未使用过计算机。你有通讯录吗？那是一个数据库，尽管是手动数据库，而不是计算机数据库。

数据库基于记录和字段的概念：

• 一条记录包含有关数据库正在跟踪的一项信息。在地址列表中，一条记录对应一个人。在汽车零件清单中，一条记录对应一个零件。

• 一个字段包含记录的一个信息。地址列表数据库将包含“名字”、“姓氏”、“地址”、“电话号码”等字段。

数据库的一个重要方面是每个记录都包含与其他每个记录相同的字段。当然，字段中的数据因记录而异，但是字段是相同的。

你可能已经想到，数据库的结构和Excel工作簿的结构似乎是相互关联的。通过将工作表行视为数据库记录，将每一列视为数据库字段，将数据库数据放入电子表格中很简单。第一行用于字段名称，其下的所有行用于实际数据。图27-1给出了一个示例，在这个工作表中，字段名称以粗体显示，但这只是为了显示外观。

图27-1：数据库表的记录和字段与Excel的行列结构正好匹配。

在这一点上，一些读者可能在想“那又怎样？”在工作表中创建数据列表的功能并不是什么新鲜事物或令人兴奋。确实如此，但是有趣的是Excel提供的特殊工具用于处理这种记录和字段格式的数据。

提示：术语表有时用于表示记录和字段中组织为数据库的数据。有时一个数据库将包含多个表。Excel有时以相同的方式使用术语列表。

排序数据

可以基于一个或多个字段中的数据对表中的数据进行排序。例如，可以使用姓氏字段按字母顺序对名称列表进行排序。要在Excel中执行排序，将光标放在表格中的任意位置，然后选择功能区“数据”选项卡，单击“排序和筛选”组中的“排序”命令，Excel自动选择数据表并显示“排序”对话框（参见图27-2）。

图27-2：使用Excel的排序命令排序数据表

如果你的表在每一列的顶部都具有字段名，则该对话框中的“列”下拉列表中的每一个都会列出所有字段名，选择应在其上对数据进行排序的字段。要进行其他排序，单击“添加条件”按钮，这将用于在第一个字段中有相同的数据的记录进行排序。

要使用VBA代码对数据进行排序，使用Range对象的Sort方法。语法为：

SomeRange.SortKey1, Order1, Key2, Order2, Header

该方法的参数说明如下：

• SomeRange。代表Range对象，标识要排序的单元格区域。它可以引用整个区域或该区域中的任意单个单元格（在这种情况下，Excel自动选择包含数据的整个区域）。

• Key1。代表指定排序所基于的列的Range对象，可以引用列中的任何单元格。

• Order1。可选常量，指定排序顺序。设置为xlAscending（默认为A-Z，升序）或xlDescending（降序）。

• Key2。可选，指定辅助排序列的Range对象。

• Order2。可选，指定辅助排序顺序。

• Header。可选常量，指定表是否具有字段名称的标题行。设置为xlYes，xlNo或xlGuess（默认值）。如果使用xlGuess，Excel将检查数据以确定是否存在标题行。

提示：在Sort方法的语法中，这里省略了一些超出本课范围的可选参数。因此，在调用此方法时，应始终使用命名参数。

下面是使用Sort方法对表进行排序的示例。假定工作表包含一个具有标题行的表，并且单元格A2在该表内。代码按列B中的字段且以列D为辅助排序字段进行排序。

Worksheets(“SalesData”).Range(“A2”).Sort Key1:= _

Worksheets(“SalesData”).Range(“B2”),Key2:= _

Worksheets(“SalesData”).Range(“D2”),Header:=xlYes

筛选数据

术语筛选是指从表中选择某些数据。例如，对于地址数据库，选择居住在加利福尼亚州的所有人员是一种筛选。在Excel中工作时，可以应用所谓的自动筛选。你的表应具有一个字段名称的标题行，才能使用此功能。

1. 将活动单元格置于数据表中的任意位置。

2. 选择功能区“开始”选项卡“编辑”组中的“排序和筛选——筛选”，或者功能区“数据”选项卡“排序和筛选”中的“筛选”命令。

3. 单击要筛选的字段名称旁边的箭头。Excel将显示可用的筛选器列表（参见图27-3）。这些包括：

• （全选）：删除以前应用的筛选并显示所有记录。

• 特定值：根据该值进行筛选（例如，图中的Mendez）。

图27-3：使用自动筛选功能

4. 要移除自动筛选并显示所有记录，再次重复上述操作2。

提示：筛选表时，某些记录似乎消失了。不用担心它们仍然在那里-只是暂时隐藏了。

要在代码中应用筛选，使用AutoFilter方法。语法为：

SomeRange.AutoFilter(Field,Criteria1, Operator, Criteria2, VisibleDropDown)

其中：

• SomeRange。代表标识要排序的单元格区域的Range对象，可以引用整个区域或该区域中的任意单个单元格（在这种情况下，Excel自动选择包含数据的整个区域）。

• Field。可选的数值，标识要在其上进行筛选的字段。该值是这些字段的索引，最左边的字段是1。

• Criteria1。筛选的可选条件。如果省略，则选择所有记录。

• Operator。可选常量，指定如何解释Criteria1（有时甚至是Criteria2）。有关详细信息，参见表27-1。

• Criteria2。筛选的可选辅助标准。

• VisibleDropDown。可选的True/False值。如果为True（默认值），则Excel显示工作表中已筛选字段的下拉箭头，允许用户手动使用筛选。如果为False，则不显示箭头。

表27-1：AutoFilter方法的参数Operator的常量

注意：如果调用不带参数的AutoFilter方法，则其作用是在指定区域内切换自动筛选下拉箭头的显示。应用自动筛选时，筛选表所在的字段中的下拉按钮显示为蓝色，而不是通常的黑色。

AutoFilter方法的条件是一个字符串，指定要进行筛选的值。例如，将Criteria1设置为“NY”并在“State”字段上进行筛选会选择状态为NY的所有记录。你可以将两个特殊值用作条件：

• =。选择字段为空白的记录。

• <>。选择字段不为空的记录。

参数Operator控制筛选，如表27-1中所述。xlAnd和xlOr设置用于筛选由Criteria1和Criteria2指定的两个字段。其他设置用于省略条件参数的特殊筛选。

下面的示例使用图27-1中所示的数据库表，假设该表中会有更多记录。所有示例均假定活动单元格在表内。这段代码筛选表仅显示纽约市居民的记录：

Selection.AutoFilter Field:=4, Criteria1:=”NewYork”

下面的代码筛选记录最少的十个州的居民：

Selection.AutoFilter Field:=5,Operator:=xlBottom10Items

下面的代码筛选姓氏是Smith或Jones的居民：

Selection.AutoFilter Field:=2, Criteria1:=”Smith”,Criteria2:=”Jones”, Operator:=xlOr

数据输入窗体

当使用数据库表时，在工作表中直接输入和编辑数据并不总是最好的方法。最好有一个自定义窗体，以一种易于阅读和使用的格式显示数据（一次一条记录数据）。对于更复杂的数据库应用程序，可能希望设计一个用户窗体（在第18至21课中介绍过），该窗体为你提供了设计窗体外观的完全灵活性，并且还允许你进行数据验证。

对于更简单的任务，你可以使用Excel自动生成的窗体。将活动单元格放在数据库表中，使用Excel的“记录单”功能，然后Excel将根据表中的字段自动创建并显示一个窗体，图27-4中显示了一个示例。

图27-4：Excel为地址数据库表生成的数据窗体

注意，在Excel2007及以后的版本中，隐藏了“记录单”功能，你需要设置功能区，添加该功能。

要通过VBA程序显示自动数据窗体，调用Worksheet对象的ShowDataForm方法。例如：

ActiveSheet.ShowDataForm

调用此方法时，宏将暂停直到用户关闭该窗体。只有这样，宏中的后续语句才会执行。

数据库函数

Excel提供了许多内置函数，这些函数专门设计用于处理表中的数据。它们都称为数据库统计函数，它们的名称均以D开头，以将其标记为数据库函数。每个数据库函数都有一个对应的功能，可以在数据库表之外执行相同的任务。例如，数据库函数DSUM具有对应的函数SUM，DAVERAGE具有函数AVERAGE，依此类推。表27-2中描述了数据库函数。

表27-2：Excel数据库函数

数据库函数具有下面的语法：

FunctionName(database,field, criteria)

其中：

• database。包含数据库表的单元格区域，例如A6:F30。

• field。要在其上执行计算的数据库字段，可以是包含字段名称的字符串，也可以是给出字段相对于表格左边缘的偏移量的数字（第一列=1）。

• criteria。包含条件的单元格区域，例如A1:A2。

条件可能是使用数据库函数最棘手的部分。你可以在两个工作表单元格的区域内指定条件，一个单元格在另一个单元格上方：

• 上部的单元格包含要应用的条件的字段的名称。

• 下部的单元格包含条件本身，使用VBA的比较操作符。

假设你的数据库表包含一个名为Age的字段，并且想要创建一个仅选择Age大于40的记录的条件。条件区域的上部单元格应包含Age，而下部单元格应包含>40。

下面使用数据库统计函数的示例基于图27-5中所示的数据库表。

图27-5：用于数据库函数示例的数据库表

假设你要确定Sales部门的人数及其平均薪酬。步骤如下。

1.使用单元格A1：A2作为条件。在单元格A1中输入“部门”以指示该条件将应用于“部门”字段。

2.在单元格A2中输入Sales。

3.在单元格C2中的输入“Sales的员工总数”。

4.在单元格C3中输入“平均薪酬”。

5.在单元格E2中输入= DCOUNT(A7:F14,”Salary”, A1:A2)。

6.在单元格E3中输入= DAVERAGE(A7:F14,”Salary”, A1:A2)。

7.单击功能区中的“货币样式”按钮，将“货币”格式应用于单元格E3。

结果工作表如图27-6所示。可以看到输入的数据库公式显示了其结果。如果更改表中的数据，结果将根据需要自动更新。但是注意，如果将一个或多个新记录添加到表中，则公式不会考虑这一点。这是因为包含表的区域被硬编码到公式中，你必须根据需要编辑公式。

图27-6：展示数据库函数结果的完整工作表

要点回顾

本课程向你展示了如何使用Excel和VBA处理数据库数据。

• 数据库将数据组织到记录和字段中。

• 在Excel中，记录由行表示，字段由列表示。

• 要对表中的记录进行排序，使用Range.Sort方法。

• 要筛选表中的记录，使用AutoFilter方法。

• 数据库统计函数用于从表中提取汇总信息。

自我测评

1.数据库表中每一列的顶部应该是什么？

2.辅助排序关键字的目的是什么？

3.VBA代码中使用哪个方法来筛选数据库表？

4.假设你不想设计用于数据库表的用户窗体，还有其他选择吗？如果有，该怎么办？

5.如何创建与数据库统计函数一起使用的条件？

注：本文是在知识星球App的完美Excel社群中发表的Excel VBA编程系列文章中的一篇，翻译整理自《Excel Programming Weekend Crash Course》。这些文章正陆续更新中。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。