您的位置: 首页 > Office技巧 > excel技巧

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

时间:2013-12-13 整理:docExcel.net

[Excel如何查看公式明细]Excel如何查看公式明细?excel表格中的数据是公式算出来的,想要显示算出数据的公式,该怎么显示呢?下面我们就来看看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”,数组公式输入完毕后再向下填充。

本文地址:https://www.docexcel.net/show/3_3763.html

以下为关联文档:

excel如何快速提取手机号后4位数字有时在表格数据中需要提取手机号后4位数字另作他用,如何快速提取呢?一起来了解一下吧...

excel公式不会自动计算怎么办有时会发现明明公式相关的数值已经变化,按理说公式的结果也应该自动计算变化,可实际上没变化,这是怎么回事呢?一起来了解一下吧...

excel怎么找出被公式引用的单元格excel表格不知道数据是否被公式引用了,要想知道数据是否被公式引用了怎么办?一起来了解一下吧...

Word怎么设置段落公式和文字水平对齐不少用户在使用Word文档编排文字的时候,经常会在自己的Word文档中插入一些公式,那么Word怎么让段落的公式和文字水平对齐呢?一起来了解一下吧...

Excel怎么提取单元格中的数字怎么一键提取excel单元格内的数字呢?单元格内有数字还有文字,一个个编辑太影响工作效率,那么Excel怎么提取单元格中的数字?这篇文章主要介绍了Excel提取单元格中部分数字的方法,...

怎么提取Excel单元格中的部分内容文字放在一个单元格了,怎么批量提取Excel单元格中的部分内容呢?这篇文章主要介绍了Excel批量提取单元格中部分内容的方法,需要的朋友可以参考下...

excel函数公式大全 excel最常用的八个函数的使用方法这篇文章主要介绍了excel函数公式大全 excel最常用的八个函数的使用方法的相关资料,需要的朋友可以参考下本文详细内容介绍...

excel锁定公式怎么输入 excel绝对值符号输入教程在excel表格编辑中,“$”符号是经常用到的一个,可以锁定公式不被修改,可能还是有很多用户不清楚excel锁定公式$怎么输入,下面小编带来excel绝对值符号输入教程,希望对大家有所帮...

excel如何设置单元格显示公式不显示结果公式是工作表中非常常用的内容了。有时候,如果需要在工作表里面显示公式,而非公式计算的值,也是可以进行操作的...

相关推荐: