Excel公式练习96：返回不同的/重复的/唯一的字符串作为数组

excelperfect

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

本次的练习是：编写三个公式，分别返回字符串数组的不同值、重复值和唯一值。要求：

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

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

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

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

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

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

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

图1

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

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

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

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

写下你的公式。

解决方案

下面均为数组公式。

公式1：

不同值：

=T(OFFSET(A1,SMALL(IF(ISNUMBER(1/(MATCH(Data,A:A,0)=ROW(Data))),ROW(Data)),ROW(A$1:INDEX(A:A,SUM(ISNUMBER(1/(MATCH(Data,A:A,0)=ROW(Data)))+0))))-1,))

唯一值：

=T(OFFSET(A1,SMALL(IF(COUNTIF(Data,Data)=1,ROW(Data)),ROW(A$1:INDEX(A:A,SUM(N(COUNTIF(Data,Data)=1)))))-1,))

重复值：

=T(OFFSET(A1,SMALL(IF(FREQUENCY(IF(COUNTIF(Data,Data)>1,MATCH(Data,A:A,0)),ROW(Data)),ROW(Data)),ROW(A$1:INDEX(A:A,SUM((FREQUENCY(IF(COUNTIF(Data,Data)>1,MATCH(Data,A:A,0)),ROW(Data))>0)+0)))),))

公式2：

不同值：

=T(OFFSET(A$1,SMALL(IF(FREQUENCY(MATCH(Data,Data,0),MATCH(Data,Data,0))>=1,ROW(Data)),ROW(OFFSET(A$1,,,SUM(1/COUNTIF(Data,Data)))))-1,0))

唯一值：

=T(OFFSET(A$1,SMALL(IF(COUNTIF(Data,Data)=1,ROW(Data)),ROW(OFFSET(A$1,,,SUM(N(COUNTIF(Data,Data)=1)))))-1,0))

重复值：

=T(OFFSET(A$1,SMALL(IF(FREQUENCY(MATCH(Data,Data,0),MATCH(Data,Data,0))>1,ROW(Data)),ROW(OFFSET(A$1,,,SUM(N(FREQUENCY(MATCH(Data,Data,0),MATCH(Data,Data,0))>1)))))-1,0))

公式3：

不同值：

=T(OFFSET(A$1,SMALL(IF(FREQUENCY(MATCH(Data,Data,0),MATCH(Data,Data,0))>0,ROW(Data)),ROW(INDIRECT('1:'&SUM(--(FREQUENCY(MATCH(Data,Data,0),MATCH(Data,Data,0))>0)))))-1,))

唯一值：

=T(OFFSET(A$1,SMALL(IF(COUNTIF(Data,Data)=1,ROW(Data)),ROW(INDIRECT('1:'&SUM(--(COUNTIF(Data,Data)=1)))))-1,))

重复值：

=T(OFFSET(A$1,SMALL(IF(FREQUENCY(MATCH(Data,Data,0),MATCH(Data,Data,0))>1,ROW(Data)),ROW(INDIRECT('1:'&SUM(--(FREQUENCY(MATCH(Data,Data,0),MATCH(Data,Data,0))>1)))))-1,))

公式4：

不同值：

=T(OFFSET(Data,-1+SMALL(IFERROR(MATCH(IF(MATCH(Data,Data&'',0)=ROW(Data)-MIN(ROW(Data))+1,Data),Data,0),''),ROW(INDEX(A:A,1):INDEX(A:A,COUNT(MATCH(IF(MATCH(Data,Data&'',0)=ROW(Data)-MIN(ROW(Data))+1,Data),Data,0))))),0))

唯一值：

=T(OFFSET(Data,-1+SMALL(IFERROR(MATCH(Data,REPT(Data,MMULT(N(Data=TRANSPOSE(Data)),ROW(Data)^0)=1),0),''),ROW(INDEX(A:A,1):INDEX(A:A,COUNT(MATCH(Data,REPT(Data,MMULT(N(Data=TRANSPOSE(Data)),ROW(Data)^0)=1),0))))),0))

重复值：

=T(OFFSET(Data,-1+SMALL(IFERROR(MATCH(REPT(Data,(MMULT(N(Data&''=TRANSPOSE(Data)),ROW(Data)^0)>1)*(ROW(Data)-MIN(ROW(Data))+1)=MATCH(Data,Data,0)),Data,0),''),ROW(INDEX(A:A,1):INDEX(A:A,SUM(N(MATCH(Data,Data,0)=((MMULT(N(Data&''=TRANSPOSE(Data)),ROW(Data)^0)>1)*(ROW(Data)-MIN(ROW(Data))+1))))))),0))

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

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。