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