Excel公式练习：根据条件获取唯一的第n个值

引言：本文的练习整理自chandoo.org。多一些练习，想想自己会怎么解决这个问题，看看别人又是怎样解决的，这样能够快速提高Excel公式编写水平。

本次的练习是：编写一个公式，用于显示数据（Data）列中与当前选定查找项目匹配的项目（Item）列的第n个最大的唯一值。

示例数据如下图1所示。

图1

其中，将单元格区域A2:A13命名为“i”，将单元格区域B2:B13命名为“d”，将单元格D2命名为“n”，将单元格E2命名为“l”。

单元格D2、E2中的数据可以输入，公式根据其数据返回相应的结果。根据不同的输入数据，公式的结果应该如下图2所示。

图2

规则：

1.公式中不能使用整列引用。

2.不能使用中间公式。

3.不能使用VBA。

4.无论数据放置在工作表中的任何地方，公式都能正常运行。

5.除了规定的名称“i”“d”“n”“l”外，不能有其它硬编码引用。

请写下你的公式。

解决方案

公式1：数组公式。

=LARGE(IFERROR(IF(MATCH(l&d,i&d,)=(ROW(d)-(MIN(ROW(d)-1))),d,),),n)

公式2：数组公式。

=LARGE(IFERROR(MATCH(l&d,i&d,)=(ROW(i)-MIN(ROW(i)-1)),)*d,n)

公式3：数组公式。

=LARGE(IFERROR((FREQUENCY(IF(i=l,d),d)>0)*d,),n)

或者：

=LARGE(IFERROR((FREQUENCY((i=l)*d,(i=l)*d)>0)*(i=l)*d,0),n)

或者：

=LARGE(IF(FREQUENCY(IF(i=l,d),d),d),n)

或者：

=LARGE(IF(FREQUENCY((i=l)*d,d),d),n)

公式4：数组公式。

=AGGREGATE(14,6,(FREQUENCY(IF(i=l,d),d)>0)*d,n)

或者：

=AGGREGATE(14,6,(MATCH(l&d,i&d,)=ROW(i)-MIN(ROW(i))+1)*d,n)

公式5：数组公式。

=LARGE((MATCH(l&d,i&d,)=ROW(i)-MIN(ROW(i)-1))*(i=l)*d,n)

……

上面列出的大多数公式都没有进行详细的解析，有兴趣的朋友可以参照前面文章给出的方法逐个研究。