用数组公式提取一列中的重复项

来源：excel格子社区

如果Excel工作表的某列中包含有重复的数据，要提取该列中所有重复的数据，可以用下面的数组公式。假如数据在A2:A30区域中，现在要在B列中提取该区域中有重复的数据，在B2单元格中输入下列数组公式：

    =INDEX($A$2:$A$30,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$30)+IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,0,1),0))

    公式输入完毕后按Ctrl+Shift+Enter结束，然后拖动填充柄向下填充，直到出现“#N/A”错误为止。

    

 

    说明：

    用INDEX函数返回A2:A30区域中的重复项，该函数的行号参数“row_num”为MATCH函数。MATCH函数可在区域或数组中搜索指定项，然后返回该项在区域或数组中的相对位置。由于本例中包含重复数据的区域为A2:A30，所以可以用最简单的“1”和“0”来构造一个数组，用MATCH函数返回“0”在数组中的位置。

    由于要返回重复项，首先对于A2:A30区域（包含29个数据）中的重复项，在数组中的对应位置用“0”来表示，非重复项就用“1”来表示，用COUNTIF函数和IF函数组合后就可以实现：

    IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,0,1)

    其中COUNTIF函数在本例中的说明见后。由于上述部分公式处于MATCH函数的“lookup_array”参数中，它返回一个包含29个元素的一维垂直数组：

   {1;0;0;1;1;0;0;0;1;0;0;0;1;1;1;1;0;1;1;1;1;0;1;1;1;1;1;0;1}

    这个数组可以通过选择公式中的这部分后按F9键来查看。

    其次，对于在B列中已出现的数据，也应该在数组中用“1”来表示将其排除掉。以B6单元格中的公式（返回结果为“欧阳锋”）为例，对于B2:B5区域中已返回的数据，数组中对应的位置要用“1”来表示，未出现的数据用“0”来表示，这用COUNTIF函数来实现：

    COUNTIF($B$1:B5,$A$2:$A$30)

    上述COUNTIF函数对区域中满足单个指定条件的单元格进行计数，语法为：

    COUNTIF(range, criteria)

    本例中的“range”参数为$B$1:B5，“criteria”参数为$A$2:$A$30。因需要对其上的区域进行判断，还要能向下填充，所以“range”参数从B1单元格开始，并且采用绝对引用与相对引用混合的方式，填充到B6单元格，就是$B$1:B5。该函数将对$A$2:$A$30区域中的每个数据（29个），都统计其在区域$B$1:B5中出现的次数，由于$B$1:B5区域中没有重复项，所以它返回另一个由“0”和“1”组成的、包含29个元素的一维垂直数组：

    {0;1;1;0;0;1;1;1;0;1;1;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;1;0}

    将上述两个数组合并，即：

   COUNTIF($B$1:B5,$A$2:$A$30)+IF(COUNTIF($A$2:$A$30,$A$2:$A$30)>1,0,1)

    返回数组：

   {1;1;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1}

    然后用MATCH函数判断数组中第一个“0”出现的位置，此时公式可理解为：

   =INDEX($A$2:$A$30,MATCH(0,{1;1;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1;1;1;0;1;1;1;1;1;1;1},0))

    即：

    =INDEX($A$2:$A$30,12)

    最终B6单元格中的公式返回“欧阳锋”。

    在B7单元格中，由于合并后的数组中元素都为“1”，MATCH函数找不到“0”，故返回“#N/A”错误，表示重复项已全部列出。

    另外，如果要在其他列中显示所提取的重复项，例如要在F列中从F7单元格开始提取，需将公式中用红色标识的“$B$1:B1”改为“$F$6:F6”，数组公式输入完毕后再向下填充。