Excel公式练习98：从文本和数字组成的数组中返回不同的/重复的/唯一的值组成的数组

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

在《Excel公式练习95：返回不同值/重复值/唯一值作为数组》中，我们处理了从仅包含数值的区域中提取不同的/重复的/唯一的值的数组。

在《Excel公式练习96：返回不同的/重复的/唯一的字符串作为数组》中，我们处理了从仅包含文本值的区域中提取不同的/重复的/唯一的值的数组。

本次的练习是：你能想出一个公式，从包含数字和文本值的区域中提取一组不同值、重复值和唯一值。要求：

• 原始数据区域命名为“data”，在公式中可以引用该区域。除此之外，不应使用任何命名区域或中间单元格。

• 每个公式的输出必须是一个数组，可以直接被其他公式（如 COUNTA等）使用。也就是说，我们追求的是一个可以直接合并到其他公式中的公式，而不是必须在工作表区域内输入才能工作的公式。

• 每个公式都应该是完全动态的，并且能够用于任何大小的一维数组。

• 每个公式必须返回一个精确大小的数组，其中只包含不同的、重复的和唯一的值。因此，根据下图1所示数据，输出为：

不同值公式生成的数组正好是14个元素。

唯一值公式生成的数组的长度正好是9个元素。

重复值公式生成的数组长度正好是5个元素

示例数据如下图1所示。其中，将单元格区域A2:A21命名为“data”。

图1

不应该使用任何辅助单元格、中间公式或者VBA。

注：不同值，是指不重复的数字。

唯一值，是指数字只出现一次。

重复值，是指数字出现不止一次。

写下你的公式。

解决方案

下面均为数组公式。

公式1：

不同值：

=LOOKUP(MODE.MULT(IF(MATCH(data,data,0)=(ROW(data)-MIN(ROW(data))+{1,1}),ROW(data))),ROW(data),data)

唯一值：

=LOOKUP(MODE.MULT(IF(COUNTIF(data,data)=1,ROW(data)*{1,1})),ROW(data),data)

重复值：

=LOOKUP(MODE.MULT(IF((COUNTIF(data,data)>1)*MATCH(data,data,0)=ROW(data)-MIN(ROW(data))+{1,1},ROW(data))),ROW(data),data)

公式2：

不同值：

=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>0,ROW(data)),ROW(OFFSET(B1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>0))))))

唯一值：

=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))=1,ROW(data)),ROW(OFFSET(B1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))=1))))))

重复值：

=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1,ROW(data)),ROW(OFFSET(B1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1))))))

公式3：

不同值：

=LOOKUP(SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,)),ROW(data)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(data,data))))),ROW(data),data)

唯一值：

=LOOKUP(SMALL(IF(COUNTIF(data,data)=1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(data,data)=1))))),ROW(data),data)

重复值：

=LOOKUP(SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1))))),ROW(data),data)

公式4：

不同值：

=OFFSET(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>0,ROW(data)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(data,data)))))-1,0),,)

唯一值：

=OFFSET(OFFSET(A1,SMALL(IF(COUNTIF(data,data)=1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(data,data)=1)))))-1,),,)

重复值：

=OFFSET(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1)))))-1,),,)

公式5：

不同值：

=CELL('contents',OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,0),MATCH(data,data,0)),ROW(data)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(data,data)))))-1,))

唯一值：

=CELL('contents',OFFSET(A1,SMALL(IF(COUNTIF(data,data)=1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(data,data)=1)))))-1,))

重复值：

=CELL('contents',OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,0),MATCH(data,data,0))>1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1)))))-1,))

可以通过F9键或者公式求值功能，加深对上述公式的理解。