|
如果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”，数组公式输入完毕后再向下填充 |
