Excel公式练习95：返回不同值/重复值/唯一值作为数组
excelperfect
引言：本文的练习整理自chandoo.org。多一些练习，想想自己怎么解决问题，看看别人又是怎解决的，能够快速提高Excel公式编写水平。
本次的练习是：编写三个公式，分别返回一个数值单元格区域的不同值、重复值和唯一值。要求：
每个公式的输出必须是一个可以被其他公式直接使用的数组（例如MAX、MIN、AVERAGE等）。也就是说，我们想要的是一个可以直接合并到其他公式中的公式，而不是必须在工作表区域内输入才能工作的公式。
每个公式必须返回一个精确大小的数组，其中只包含不同的、重复的和唯一的值。因此，根据下图1示例数据，输出如下：
不同值的公式生成的数组的长度正好是14个元素。
唯一值公式生成的数组的长度正好是9个元素。
重复值公式生成的数组长度正好是5个元素。
每个公式都应该是完全动态的，并且能够用于任何大小的一维数组。
示例数据如下图1所示。其中，将单元格区域A2:A21命名为“data”。
图1
不应该使用任何辅助单元格、中间公式或者VBA。
不同值，是指不重复的数字。
唯一值，是指数字只出现一次。
重复值，是指数字出现不止一次。
写下你的公式。
解决方案
下面均为数组公式。
公式1：
不同值：
=SMALL(IF(FREQUENCY(data,data)>=1,data),ROW(OFFSET(A$1,,,SUM(N(FREQUENCY(data,data)>=1)))))
唯一值：
=SMALL(IF(FREQUENCY(data,data)=1,data),ROW(OFFSET(A$1,,,SUM(N(FREQUENCY(data,data)=1)))))
重复值：
=SMALL(IF(FREQUENCY(data,data)>=2,data),ROW(OFFSET(A$1,,,SUM(N(FREQUENCY(data,data)>=2)))))
公式2：
不同值：
=SMALL(IF(FREQUENCY(data,data)>0,data),ROW(INDEX(A:A,1):INDEX(A:A,SUM(N(FREQUENCY(data,data)>0)))))
唯一值：
=SMALL(IF(FREQUENCY(data,data)=1,data),ROW(INDEX(A:A,1):INDEX(A:A,SUM(N(FREQUENCY(data,data)=1)))))
重复值：
=SMALL(IF(FREQUENCY(data,data)>1,data),ROW(INDEX(A:A,1):INDEX(A:A,SUM(N(FREQUENCY(data,data)>1)))))
公式3：
不同值：
=AGGREGATE(15,6,IF(FREQUENCY(data,data)>0,data),ROW(OFFSET(A$1,,,SUM(N(FREQUENCY(data,data)>0)))))
唯一值：
=AGGREGATE(15,6,IF(FREQUENCY(data,data)=1,data),ROW(OFFSET(A$1,,,SUM(N(FREQUENCY(data,data)=1)))))
重复值：
=AGGREGATE(15,6,IF(FREQUENCY(data,data)>1,data),ROW(OFFSET(A$1,,,SUM(N(FREQUENCY(data,data)>1)))))
公式4：
不同值：
=SMALL(IF(FREQUENCY(A:A,SMALL(A:A,ROW(A$1:INDEX(A:A,COUNT(A:A)))))>0,SMALL(A:A,ROW(A$1:INDEX(A:A,COUNT(A:A))))),ROW(A$1:INDEX(A:A,SUM((FREQUENCY(A:A,A:A)>0)+0))))
唯一值：
=SMALL(IF(FREQUENCY(A:A,SMALL(A:A,ROW(A$1:INDEX(A:A,COUNT(A:A)))))=1,SMALL(A:A,ROW(A$1:INDEX(A:A,COUNT(A:A))))),ROW(A$1:INDEX(A:A,SUM((FREQUENCY(A:A,A:A)=1)+0))))
重复值：
=SMALL(IF(FREQUENCY(A:A,SMALL(A:A,ROW(A$1:INDEX(A:A,COUNT(A:A)))))>1,SMALL(A:A,ROW(A$1:INDEX(A:A,COUNT(A:A))))),ROW(A$1:INDEX(A:A,SUM((FREQUENCY(A:A,A:A)>1)+0))))
可以通过F9键或者公式求值功能，加深对上述公式的理解。
欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。