完美Excel

在Excel对象模型中，ListObjects对象表示表（Table）。下面列举一些在Excel 2007中使用VBA处理表的示例代码。
创建表
下面的代码将某区域转换为表。

Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name = "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub

下面的代码改变表的格式，即改变表底部的线条样式：

Sub ChangeTableStyles()
    ActiveWorkbook.TableStyles(2).TableStyleElements(xlWholeTable) _
        .Borders(xlEdgeBottom).LineStyle = xlDash
End Sub

列出所有的表
下面的代码列出当前工作表中所有的表及其地址：

Sub FindAllTablesOnSheet()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "发现表: " & oLo.Name & ", " & oLo.Range.Address
    Next
End Sub

在表中进行选择
下面的示例代码使用了不同的方式，选择特定的表中指定的部分。

Sub SelectingPartOfTable()
  Dim oSh As Worksheet
  Set oSh = ActiveSheet
  With oSh.ListObjects("Table1")
    MsgBox .Name
    '选择整个表
    .Range.Select
    '仅选择表中的数据区域
    .DataBodyRange.Select
    '选择第3列
    .ListColumns(3).Range.Select
    '选择第一列中的数据区域
    .ListColumns(1).DataBodyRange.Select
    '只选择第4行(标题行不计在内!)
    .ListRows(4).Range.Select
  End With
 
  '选择整列(仅数据)
  oSh.Range("Table1[列2]").Select
  '选择整列(数据加标题)
  oSh.Range("Table1[[#All],[列1]]").Select
  '选择表中的整个数据部分
  oSh.Range("Table1").Select
  '选择整个表
  oSh.Range("Table1[#All]").Select
  '选择表中的一行
  oSh.Range("A5:F5").Select
End Sub

插入行和列

Sub TableInsertingExamples()
  '在指定的位置插入
  Selection.ListObject.ListColumns.Add Position:=4
  '在右侧插入
  Selection.ListObject.ListColumns.Add
  '在上方插入
  Selection.ListObject.ListRows.Add (11)
  '在下方插入
  Selection.ListObject.ListRows.Add AlwaysInsert:=True
End Sub

在表中添加批注

Sub AddComment2Table()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    '在表中添加一个批注
    oSh.ListObjects("Table1").Comment = "这是表的批注"
End Sub

将表转换为正常的区域

Sub RemoveTableStyle()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    '删除表或列表样式
    oSh.ListObjects("Table1").Unlist
End Sub

排序和筛选

Sub SortingAndFiltering()
  '下面的代码仅在Excel 2007中运行
  With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add( _
      Range("Table1[[#All],[列2]]"), xlSortOnCellColor, xlAscending, , _
         xlSortNormal).SortOnValue.Color = RGB(255, 235, 156)
    With .Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
  End With
  '下面的代码在2003中使用
  ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, _
      Criteria1:=RGB(156, 0, 6), Operator:=xlFilterFontColor
End Sub

注：本文整理自Jan Karel Pieterse的《Working with Tables in Excel 2007 (VBA)》一文，供研究和参考。

表是什么？
Excel 2007引入了一个处理数据表的新概念，称作“表”。实际上，Excel 2007中的表替代并增强了Excel 2003中的“列表”功能。
“表”是一系列包含相关数据的行和列。
创建表
在Excel 2007中能够很方便地创建表。
1、在工作表中，选择要转换为表的数据区域（或空单元格区域，若没有数据的话），如图1所示。

图1：选择要创建表的数据区域
2、在“开始”功能区中，选取“样式”组中的“套用表格样式”按钮（如图2所示）。

图2：“开始”功能区内“样式”组中的“套用表格格式”按钮
单击该按钮，此时，Excel 2007显示一组已预设好的表格样式和选项（该界面在Office 2007中称作库），如图3所示。

图3：表格样式图形目录
在其中选中任一样式并单击，Excel会弹出一个“套用表格式”的对话框（如图4所示）。在该对话框中，可以输入想要转换为表的单元格区域，如果表中包含有标题行，则选中“表包含标题”前的复选框，然后单击“确定”按钮将所选数据区域转换为表。

图4：询问转换为表的数据区域的对话框
完成后的结果如图5所示。

图5：转换为表后的单元格区域
此外，还可以使用“插入”功能区中“表”组中的“表”按钮（如图6所示）来创建表。
图6
单击“表”按钮后，弹出如图7所示的“创建表”对话框（该对话框与图4对话框相同，仅对话框名称不同）。
图7
选定要转换为表的区域并单击“确定”按钮后，将会以默认的样式格式化数据区域。
Excel 2007中表的主要功能和作用
1、整合了自动筛选和排序功能
如果表中包含标题行，那么在标题行中总是有筛选和排序下拉箭头，如图8所示。

图8：含有标题行的表中包括排序和筛选功能
2、快速设置表的格式
可以通过选择“表样式”中已经预设好的格式来迅速对表区域进行格式，也可以自定义表样式来格式化表区域，还可以通过“表样式选项”对标题行、汇总行、第一列、最后一列等进行设置。
3、容易选择
可以很方便地选择表中的整列或整行。将鼠标放在表的顶部直到指针变成向下的箭头（如图9）并单击，则选中该列中的数据，再次单击将选中表中的整列（包括标题和汇总行）。
图9：选择表内列中的全部数据
同样，将鼠标放在表的左侧直到指针变成向右的箭头并单击，则选中该行中的数据。
也可以将鼠标放在表的左上角，当指针变成东南方向的箭头（如图10）后单击，则选中表内的所有数据；再次单击则选中整个表。
图10：鼠标箭头变成此形状后单击一次选中表中的所有数据，再次单击则选中整个表
此外，还可以在表中的任何位置按Ctrl+A组合键选择表格中的所有数据，再按一次则选择整个表。也可以将当前单元格定位到左上角单元格，然后按Ctrl+Shift+End组合键选择自该单元格开始到最后一个单元格之间的区域。
4、当滚动时标题行仍可见
Excel 2007有一个新功能，如果表大于屏幕且向下滚动，当活动单元格在表内时，列字母暂时用表的标题名代替，如图11所示。

图11：滚动时，表的标题行在Excel的列标题中
5、表自动扩展
如果在表下方输入了数据，Excel会猜想您想扩展表，因而自动增加表的尺寸来包括新的输入行（或列）。此时，有一个智能标签能够选择是否自动扩展。当然，也可以完全关闭这项功能。
6、自动重新调整格式
在表中插入或删除一行（或一列）时，Excel将自动调整格式，即间隔的阴影行将在合适的位置。
7、自动调整图表和其它对象的数据源区域
如果图表或者其它对象使用表作为数据源，则在表中添加行时，该图表或其它对象将自动调整以包含新增的数据。
8、创建计算列
可以创建计算列对表中的每一行按某公式进行计算。创建计算列很简单，可在表的最右侧相邻的空白列的任一单元格中输入公式即可自动创建计算列。也可以在“开始”功能区中的“单元格”组中单击“插入”按钮，从弹出的菜单中选择“在左侧插入表格列”来创建计算列。计算列会自动扩展包含表中的每一行。
9、快速汇总数据
如果将“表样式选项”组中“汇总行”前的复选框选中，就会在表下方出现一个汇总行。此时，可在每列汇总行单元格下拉列表中选择要进行汇总的函数，对该列数据进行相应的汇总计算。
10、使用结构引用
可以使用结构引用在公式中引用表名及相应的单元格。
功能区（Ribbon）中表的功能选项
一旦创建了表或者选择了已创建的表中的任何单元格，就会有一个新的选项卡出现在功能区中，该选项卡称为“表工具——设计”，如图12所示。

图12：在Ribbon中“临时”增加的“表工具”选项卡
下面简单介绍一下该选项卡上的每组功能。
属性组
属性组（见下图13）能够处理两件事情：
图13：“表工具”选项卡中的“属性”组
1、改变表的名称
在公式中，当指向表中的单元格时，会使用表的名称，下面进行演示：
单击紧接着表右侧的单元格，输入=号，然后输入SUM（，接着单击表中带有数据的任一单元格，将会得到如下的公式（如图14）：

图14：公式中名称转换
=SUM(表1[[#此行],[单碟容量]])
注意，Excel进行了名称转换，指向表中的单元格。其中，“表1”为表的名称，[#此行]表示数据来源于与公式单元格所在的同一行，[单碟容量]为表内部的列。
因为要进行名称转换，因此不允许在表里有多于一列带有特别的标题。一旦试图输入一个重复现有标题的新标题，Excel将通过为新列名添加一个数字自动改正这个重复的标题。
能够立即自动扩展是表的一个极好的功能。只要您按下回车键，表会自动扩展而包括公式列（Excel也会为您补充一个列标题），并且公式会沿着数据自动向下复制填充整列。当然，这些操作都能通过使用显示的智能标签中的选项来撤销。
上面只是一个单元格列，如果要计算多个单元格，只需键入函数名和左括号后，单击想要的计算的单元格，输入逗号，然后再单击另一单元格。例如，要计算两个单元格，则公式如下：
=SUM(表1[[#此行],[容量]],表1[[#此行],[缓存]])
该公式计算表中与公式所在的单元格同一行中标题为“容量”和“缓存”的列所对应的单元格值的和（如图15）。

图15：公式中名称转换
2、调整表格大小
该按钮用来改变表的大小。
工具组
这个组（如图16）有三个控制按钮：
图16：“表工具”选项卡中的“工具”组
1、通过数据透视表汇总
其功能正如按钮所描述的一样。创建数据透视表后，不需要再担心要更新数据透视表的数据源区域。如果在表中添加了数据，Excel会自动扩展数据透视表的数据源区域来响应变化。当然，仍然需要更新数据透视表来查看结果。
2、删除重复项
这是在Excel 2007中添加的新功能。单击该控制按钮后，出现一个名为“删除重复项”的对话框，可以选择一个或多个想要该列的值都是唯一值的列（如图17）。

图17：“删除重复项”对话框
3、转换为区域
该按钮将使表恢复为正常的单元格区域，但仍保留在表中的格式，如间隔的阴影行、加粗的字体等，此时，可以选择“开始”功能区内“编辑”组中“清除”按钮下的“清除格式”命令来清除格式。注意，如果在数据透视表的数据源区域执行这项操作，那么数据透视表源数据区域不会被更新且不再可以更新数据透视表。
外部表数据组
该组（如图18所示）是所有关于表的源数据，仅用于使用数据库或网络查询或sharepoint列表输入到Excel的表中的数据。
图18：“表工具”选项卡中的“外部表数据”组
该组有5个按钮：
1、导出数据
事实上这是一个组合按钮。如果按下后，出现两个选项：“将表格导出到SharePoint列表”和“将表导出到Visio数据透视关系图”。
2、刷新
使用这个组合按钮来刷新表中的外部数据。单击该按钮下面的箭头，将出现一个菜单，包括“全部刷新”，可以更新文件中所有的外部数据区域。
3、数据区域属性
改变外部数据的属性。
4、用浏览器打开
如果表是一个SharePoint列表，该按钮可以打开带有该表的浏览器窗口。
5、取消链接
如果表是一个SharePoint列表，该按钮断开从列表的连接。
表样式选项组
这组控制选项决定提供给表的一些主要样式，或者对表样式进行调整。
图19：“表工具”选项卡中的“表样式选项”组
1、标题行
当取消该项前面的选择时，Excel移除表中的标题。标题行的单元格被清除，但Excel记得标题行。如果您现在在空行中的任意单元格中输入数据，Excel将不会覆盖掉信息，当您又选中标题行复选框时。取而代之的是，Excel将插入新行来显示标题，表下面的单元格相应下移。
2、汇总行
如果想在表下方汇总数据，则选中此复选框。Excel将在表中的最后一列的下方自动添加Sum函数，且在汇总行的其它单元格中单击后，会出现一个下拉箭头，单击该箭头后会出现一个下拉的函数列表供选择。
3、镶边行
选中此复选框后，表中的行将取得间隔阴影，即奇偶行以不同的方式显示。
4、第一列
如果选中此复选框，表中的第一列将被格式化为与其它列不同的格式。
5、最后一列
格式化表中的最后一列与其它列不同。
6、镶边列
选中此复选框，表中的列取得间隔阴影，即奇偶列以不同的方式显示。
表样式组
使用该组工具可以快速改变表的样式(如图20)。

图20：“表工具”选项卡中的“表样式”组
单击图形库右侧的下拉按钮，看到所有可选项。当鼠标悬浮到某特定的样式上时，在工作表中可以看到该样式应用后的效果。图形库的底部有两个额外的选项：
1、新建表样式
该选项可以创建自已的表样式。
2、清除
删除整个表中的表样式，但格式仍然保留。
其它一些操作
1、删除表
选择表，按Delete键。
2、自定义表样式
在“开始”选项卡的“样式”组中，单击“套用表格格式”，然后单击“新建表样式”；或者，如果已创建了表，则可在“表工具”选项卡的“表样式”组中选择同样的命令。出现如图21所示的对话框。
图21：自定义表样式
在“名称”框中输入表样式名称，在“表元素”列表框中，可以根据现有表样式设置格式，也可清除表样式。如果要将某表样式作为默认的表样式，则选中下面的“设为此文档的默认表快速样式”。
3、去除表样式
只须选择要去除表样式的表，然后在“表样式”组中选择“清除”命令即可。
小结
Excel 2007中的表增强了Excel的功能，虽然这些功能大部分已经存在于Excel2003的列表功能中，但Excel 2007的表增强并改进了该功能。最重要的改进有：

• 整合了自动筛选和排序功能（且筛选和排序功能在Excel 2007中已得到了改进）。
• 更容易选择数据。
• 在表的范围内滚动时，表标题行始终可见。（新功能）
• 表可以自动扩展。
• 表可以自动重新调整。（新功能）
• 自动调整图表和其它对象的数据源区域。

注：本文是在Jan Karel Pieterse的文章《Working with Tables in Excel 2007》和Excel 2007帮助中的相关内容的基础上整理归纳而成。

使用Excel数据透视表，可以从完全不同的角度来查看数据。使用数据透视表，可以汇总上千行数据，显示总计、平均、或其它计算。下面的内容介绍数据透视表的概念，使用数据透视表的好处，以及如何准备数据以便使用这些数据作为数据透视表的数据源。
什么是数据透视表
数据透视表是Excel中的一个工具，能够帮助汇总成千上万行数据为简洁的报表。
理解使用数据透视表的好处
有些人避免使用数据透视表，因为他们觉得数据透视表是复杂的并且难以使用。这些人错过了Excel的一个最强大且容易使用的功能，当他们花费时间编写复杂的公式来汇总数据时，你只需要使用鼠标几分钟就能在数据透视表中汇总数据。
无论你正在处理财务数据、销售订单、顾客服务报表、站点统计、资源跟踪、事件计划、或任何其它记录，数据透视表都能帮助你查看、分析、监控、以及汇报数据。当报表需要改变时，可以对数据透视表稍作调整来代替重新汇总工作表。
Excel 2007中，一些数据透视表功能已经发生了根本的变化，包括创建数据透视表的方式，同时还提供了新的和改进的格式工具以及更容易连接Excel外部数据的方式。
准备创建数据透视表（设计源数据）
在创建数据透视表之前，需要收集数据并且以Excel能够使用的方式组织数据。在创建数据透视表之前，可能数据已经进行了正确的格式化，或者可能必须做一些准备工作。可以在Excel工作簿、外部数据库、或者其他数据源……甚至是在文本文件中组织数据。在创建数据透视表之前，没有太多的规则，但是正确地设置源数据是重要的。花一些时间准备数据，确保从创建的数据透视表中获得最好的结果。
1、设计数据透视表的数据源
许多数据透视表都是从Excel的工作表数据中创建的，下面的示例在Excel中组织数据作为数据透视表的源数据，源数据由许多行和列或者成千上万行和列组成，但是基本的布局需求是相同的。
在下面的示例中，涉及以保险公司的保单信息。当你卖出一份新保单时，会记录一些信息，例如该份保单的开始和结束日期、投保的商业类型、在哪里购买的保险、投保财产的价值。你希望分析保单数据，了解在每个区域中投保的商业类型，以及每类建筑投保值总计。
示例数据截图如下：

2、在行和列中组织数据
必须在行和列中组织作为数据透视表数据源的Excel数据，每行包含一条记录的信息，本例中每行包含关于一份保单的信息。以相同的顺序存储每条记录中的信息，第1列是保单编号、第2列是开始日期，等等。第1行包含标题，表明存储在每列中的信息。列也被称作字段，第1行中的标题是字段名。本例共有11列、927行数据。
3、添加列标题
源数据中每列必须包含标题，应该使用简短的、具有描述性的、独立的标题。标题应该简要地说明包含在列中的特定的数据，本例中EQ是一个简洁的标题，表明是否在地震区域。
虽然可以为多列使用相同的标题，但是这会导致使用数据透视表的人混淆。如果标题重复，那么在创建数据透视表时Excel将会添加数字使标题唯一。
如果试图从包含空标题单元格的数据中创建数据透视表，将会出现错误。
4、在每列中输入简单的数据
源数据的每列应该包含一种类型的数据。示例中，列B包含日期，列G包含贷币值，列H包含文本。
5、将数据分成多列
要创建有效的数据透视表，一些源数据应该被分成多列，而不是使用单列。例如，包含完整的地址的列，应该分成3列：一列代表街道，一列代表城市，一列代表州。这样，可以通过城市或州来分析数据。
6、移除重复列
不要创建存储相同类型信息的多列，这会造成难以通过数据透视表分析特定的数据。
7、在每行中输入相关的数据
源数据中每行应该包含一条记录的详细信息。如有可能，为每行设置独立的标识符，例如本例中的保单编号。这将更容易跟踪数据透视表中的信息。
8、创建独立的数据块
源数据中不应该有任何空行，不能包含任何完整的空列。如果由于美观的原因，希望源数据里显示空列，那么该列必须至少包含标题，可以将标题格式化为与单元格背景相同的颜色，使它显示好像是空。
源数据应该与工作表中的任何其他数据分隔开，至少在源数据与其他数据之间放置一个空行和一个空列。理想的情形是工作表中仅存在唯一的源数据，将其他数据移到到单个的工作表中。在本例中，没有空行和空列，工作表中没有其他数据。
创建Excel表
准备源数据的最后一步是，从工作表的数据中创建Excel表，这将激活特别的功能，例如自动扩展作为添加到现有数据末尾的新行的能力。
使用Excel表功能，更容易维护数据透视表的源数据。在Excel表中，如果添加行或列，那么当更新数据透视表时自动包括新数据。如果基于非格式化的源数据的数据透视表，那么不会发现新行或新列，必须手工调整源数据区域，确保在数据透视表中包括新数据。或者，你可能忘记调整源数据区域来包括新数据，数据透视表显示不准确的结果。
Excel表具有很多有用的功能和好处，包括自动包括新创建的行和列、使标题始终可见、Excel会自动给表命名、可以快速且容易地排序和筛选数据。
小结
数据透视表是一个强大的Excel工具，能够用于快速分析大量的数据。并且，很容易创建数据透视表，只需几次鼠标单击。
在创建数据透视表之前，应该设置数据源。为了创建有效的数据透视表，应该确保正确地构造数据源。通常，源数据应满足下列要求：

• 以行和列形式组织数据
• 每列有一个唯一的、有描述性的标题
• 每列中有相似的数据
• 诸如详细地址信息数据应该被分隔到多列中
• 不要有存储相似数据的重复列
• 每行包含一条记录的相关数据
• 数据是工作表中的独立块

准备源数据最好的方法是将其格式化为Excel表，这样在添加数据时将自动扩展。Excel表工具也很容易进行排序和筛选，因此能够检查数据透视表的数据，或者在发生问题时解决问题。

注:本文源自Debra Dalgleish的《Beginning PivotTables in Excel 2007》.

一个简单的示例：查找Excel工作表中的重复数据
记得一位网友曾问：要求找出Excel工作表中的重复数据并显示在工作表相应的单元格中。我给出了一个数组公式供参考，但不是太符合要求，因为这个数组公式虽然找出了重复数据，但是如果将数组公式向下复制时超出了出现重复数据的数量，会在相应单元格中显示错误。不久，这位朋友获得了更好的一个公式。这个公式非常好，完美地解决了这类问题，因此，我将其转贴于此，供有兴趣的朋友参考。
先看看下图：

在列A和列B中存在一系列数据（表中只是示例，可能还有更多的数据），要求找出某人（即列A中的姓名）所对应的所有培训记录（即列B中的数据）。也就是说，在单元格E1中输入某人的姓名后，下面会自动显示这个人所有的培训记录。
我们知道，Excel的LOOKUP系列函数能够很方便地实现查找，但是对于查找后返回一系列的结果，这类函数无能为力，因此只能联合其它函数来实现。
这里，在方法一中使用了INDEX函数、SMALL函数、IF函数和ROW函数，在方法二中还使用了Excel 2007中新增的IFERROR函数。
方法一：

• 选择单元格E3；
• 输入公式：
  =INDEX(B:B,SMALL(IF($A$2:$A$25=$E$1,ROW($A$2:$A$25),65536),ROW(1:1))) & “”
  然后同时按下Ctrl+Shift+Enter键，即输入数组公式。
• 选择单元格E3后下拉至所有单元格。

方法二：

• 选择单元格F3；
• 输入公 式：
  =IFERROR(INDEX($A$2:$B$9,SMALL(IF($A$2:$A$9=$E$1,ROW($A$2:$A$9)-ROW($A$2)+1,ROW($A$9)+1),ROW(1:1)),2),”")
  然后同时按下Ctrl+Shift+Enter键，即输入数组公式。
• 选择单元格F3后下拉至所有单元格。

示例文档下载：

一个复杂的示例：查找不同工作表中的数据并显示满足条件的所有数据
对于VLOOKUP函数来说，其主要缺点是仅能返回与查找条件相匹配的单条数据，不能够返回与某条件相匹配的所有数据，但其优势是能够相当容易地获取与所给条件相匹配的第一条数据，如下图1所示。

图1：使用VLOOKUP函数返回包含某顾客姓名相应的行中的数据是很容易的方式
在Excel 2007中使用结构化引用，VLOOKUP函数不仅容易使用，而且可读性也很强。在示例中，将表Table1中第一列的值与单元格A9中的值相匹配，并从表中第3列返回值。但是，不能返回表中第二个与“Dan”相匹配的值。事实上，很多时候我们都会碰到这样的情况，我们想要返回与条件相匹配的所有值，但是VLOOKUP函数只能返回满足条件的第一个值。
下面，让我们看看如何从表中获取满足条件的所有数据。首先，准备一些要操作的数据。我们以Office自带的Northwind示例数据库的一部分数据来演示，将其中的部分数据导入Excel表中，如下图2、图3、图4所示。

图2：导入到工作表Customers中的“Customer”表并命名为“tblCustomers”

图3：导入到工作表Orders中的“Orders”表并命名为“tblOrders”

图4：导入到工作表Details中的“OrderDetails”表并命名为“tblDetails”
当然，上述数据都不需要自已手工输入，只需从Northwind数据库中导入即可。现在，希望选择订单号（Order Number）后，能够显示该订单的详细信息，如下图5所示。

图5：当选择某订单号后，会显示该订单的详细信息
下面是图5显示的界面的主要设计过程。
步骤1 在工作表相应的单元格中输入下列字符：
单元格B1：Order Number
单元格C3：Order Information
单元格C4：Customer
单元格F4：Order Date
单元格F5：Status
单元格F6：Salesperson
单元格F7：Ship Date
单元格C10：Order Details
单元格C11：Product
单元格D11：Quantity
单元格E11：Unit Price
单元格F11：Discount
单元格G11：Total Price
单元格H11：Status ID
步骤2 创建包含所有订单IDs的一个命名区域，然后使用该名称在数据有效性中创建订单号的下拉列表。为此，单击“公式—定义名称”，在“新建名称”对话框中输入：
名称：OrderIds
引用位置：=tblOrders[ID]
注：数据有效性不能够引用不同工作表中的单元格区域，除非为该区域定义名称。
步骤3 选择单元格D1，将其命名为rngOrderId。
步骤4 选择单元格D1，单击“数据—数据有效性”，在“允许”中选择“序列”，在“来源”框中输入“=OrderIds”。
步骤5 选择单元格C5，输入下列函数：
=VLOOKUP(rngOrderId, tblOrders, MATCH(C4, tblOrders[#标题], 0), FALSE)
步骤6 与单元格C5中的函数相似，设置剩余单元格的查找函数。
C6：=VLOOKUP($C$5,tblCustomers,MATCH(“Address”,tblCustomers[#标题],0), FALSE)
C7：=VLOOKUP($C$5, tblCustomers, MATCH(“City”,tblCustomers[#标题],0), FALSE) & “, ” & VLOOKUP($C$5, tblCustomers, MATCH(“State”,tblCustomers[#标题],0), FALSE) & ” ” & VLOOKUP($C$5, tblCustomers, MATCH(“Zip”,tblCustomers[#标题],0), FALSE)
H4：=VLOOKUP(rngOrderId, tblOrders, MATCH(F4, tblOrders[#标题], 0), FALSE)
H5：=VLOOKUP(rngOrderId, tblOrders, MATCH(F5, tblOrders[#标题], 0), FALSE)
H6：=VLOOKUP(rngOrderId, tblOrders, MATCH(F6, tblOrders[#标题], 0), FALSE)
H7：=VLOOKUP(rngOrderId, tblOrders, MATCH(F7, tblOrders[#标题], 0), FALSE)
至此，完成了我们的界面的上半部分，如图6所示。

图6：已完成的查找界面
接下来，让我们看看后面将要使用的公式中的一些函数。

• 实际上，我们将不会使用VLOOKUP函数。因为需要返回多个条目，因此需要一种方式来返回一组值，而VLOOKUP函数不具备这样的功能。这里，我们使用INDEX函数返回指定行和列交叉部分的值。
• 我们需要指定想要在单元格中显示的数组中的条目，这里可使用SMALL函数和ROW函数来实现。
• 我们希望如果在源数据中添加额外的列时确保数据能够自动调整，这里使用MATCH函数。
• 最后，我们希望有错误处理功能，这里使用IFERROR函数来确保如果在计算中有错误将只是显示空单元格。（IFERROR函数是Excel 2007中新增的函数）

好了，先让我们来看看已经完成的函数，如下图7中的单元格C12所示（注意，这是一个数组公式，其左右两侧的花括号不需要自已输入。在输完公式后按下Ctrl+Shift+Enter组合键即可）。

图7：数组公式用于返回与特定值相匹配的第N项
这个公式看起来很复杂，为便于阅读，将其书写如下：
{
=IFERROR(
INDEX(tblDetails,
SMALL(
IF(tblDetails[Order ID]=rngOrderId,
ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])
),
ROW(1:1)
),
MATCH(C$11, tblDetails[#标题], 0)
),
“”)
}
首先，看看SMALL函数，该函数接受一组值并从中返回第N小的值。
SMALL(
IF(tblDetails[Order ID]=rngOrderId,
ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])
),
ROW(1:1)
),
本例中，数组的值由IF函数确定。特别地，如果Details表中某行的Order ID与在下拉列表中选择的Order ID相等，那么添加该行的行号到数组值中。通过使用ROW函数，Excel能够自动调整公式来向下填充单元格。并且，通过使用结构化引用，使得工作表数据能够自动适应源数据的变化。
然后，再看看INDEX函数，该函数接受一个二维数据并返回数组中指定行列位置的值。
INDEX(tblDetails,
SMALL(
IF(tblDetails[Order ID]=rngOrderId,
ROW(tblDetails[Order ID])-ROW(tblDetails[#标题])
),
ROW(1:1)
),
MATCH(C$11, tblDetails[#标题], 0)
),
我们已经知道，本例中的SMALL函数返回与输入的Order ID相匹配的Order Details列表中ROW(Nth)的值，这里是第一个值即行号。接着需要获取列号，即使用MATCH函数，本例中该函数接受C11的值并查找在Details表中有相同名称的列。
最后使用的是IFERROR函数，如果包含的计算式发生错误，使用空字符串（””）替换错误值。
现在，选择单元格C12，拖动其右下角的填充柄至单元格H12。选择单元格区域C12：H12，拖动填充句柄向下10行或更多。现在，将显示满足该订单号的所有信息，并且当我们改变Order ID时，将会自动更新相关信息。

图8：完成的界面表，使用数组公式向下填充以获取所有相关信息
示例文档下载：