Excel公式技巧66:获取第n个匹配的值(使用INDEX函数)

excelperfect

在《Excel公式技巧65:获取第n个匹配的值(使用VLOOKUP函数)》中,我们构造了一个没有重复值的辅助列,从而可以使用VLOOKUP函数来查找指定的重复值。本文中仍然以此为例,使用INDEX函数来获取重复值中指定的值,但是不需要构造辅助列。

如下图1所示的工作表,在“商品”列中,存在一些重复的商品,现在我们要找出第2次出现的“笔记本”的销售量。

图1

在单元格G6中输入数组公式:

=INDEX($D$3:$D$14,SMALL(IF($C$3:$C$14=$G$2,ROW($C$3:$C$14)-ROW($C$3)+1),F6))

结果如下图2所示。

图2

公式中:

$C$3:$C$14=$G$2

将单元格区域C3:C14中的值与单元格G2中的值相比较,得到由布尔值组成的数组:

{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}

公式中:

ROW($C$3:$C$14)-ROW($C$3)+1

生成一系列由1至单元格区域的最大个数的连续整数组成的数组:

{1;2;3;4;5;6;7;8;9;10;11;12}

这样,公式中的:

IF($C$3:$C$14=$G$2,ROW($C$3:$C$14)-ROW($C$3)+1)

解析为:

IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12})

得到数组:

{1;FALSE;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;11;12}

即由所查找的商品对应的销售量和FALSE组成的数组。

代入SMALL函数中:

SMALL(IF($C$3:$C$14=$G$2,ROW($C$3:$C$14)-ROW($C$3)+1),F6)

解析为:

SMALL({1;FALSE;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;11;12},2)

得到结果:

6

即要查找的商品第2次出现的位置。

代入INDEX函数中,得到:

=INDEX($D$3:$D$14,6)

结果为单元格D8中的值10。

如果使用定义的名称,那么公式将更灵活,如下图3所示。

图3

(0)

相关推荐