显示满足条件的所有数据--vlookup,match

显示满足条件的所有数据—VLookup函数、IF函数、Row函数、Small函数、Index函数、Match函数、IFERROR函数、表结构的组合使用

2009年03月20日, 1:26 下午

(4人投票, 平均: 5.00 out of 5)

一个简单的示例：查找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所示。