一个简单的示例:查找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:完成的界面表,使用数组公式向下填充以获取所有相关信息
示例文档下载: