如图,在A列单元格区域中,有一些内容。
但是部分内容重复了。在Excel里面,有很多方法可以来查找到重复值。今天来介绍一下使用公式,在B列里面列出哪些值是有重复的。
在单元格B2照片那个,输入公式:
=INDEX($A$1:$A$14,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$14)+IF(COUNTIF($A$1:$A$14,$A$1:$A$14)>1,0,1),0))
这里要用到数组公式,输入以后按Ctrl+Shift+Enter来返回结果。然后拖动单元格右下角向下复制公式,如图所示,有重复值的会显示出来,但是当没有重复值以后,结果就显示错误了。
下面就来详细分解一下这个公式。
1. COUNTIF($B$1:B1,$A$1:$A$14)
COUNTIF函数是根据条件来进行计数,在这里条件为$A$1:$A$14的每一个单元格内容,区域是单元格B1。这时单元格B1为空,因此不管条件是什么,计数结果都是0,数组公式的话,结果就是:{0;0;0;0;0;0;0;0;0;0;0;0;0;0}
将这部分公式直接输入到单元格中再按Ctrl+Shift+Enter的话返回的结果为0。如果需要查看,选择单元格B2,在编辑栏中选中这部分公式,按F9键,就会显示出来了。
2. COUNTIF($A$1:$A$14,$A$1:$A$14)
这是第二个COUNTIF函数,条件和区域均为$A$1:$A$14,在这个数组函数中,每一个单元格都会和其他单元格进行查找匹配,如果有重复值的话,COUNTIF计数就会加一。例如A1单元格为苹果,与A2:A14单元格中的值进行匹配,发现了一个匹配上的单元格A12,因此就加上1。
这部分公式返回的结果为:{2;2;2;1;1;1;1;1;1;1;1;2;2;2}。也可以使用F9键来进行查看。
3. IF(COUNTIF($A$1:$A$14,$A$1:$A$14)>1,0,1)
判断COUNTIF函数返回的结果是否大于1,是的话返回0,否则返回1。根据上面一步COUNTIF函数的结果,如果有重复的话,返回的值是2大于1,IF函数就会返回0;否则就返回1。
这里判断以后的结果是:{0;0;0;1;1;1;1;1;1;1;1;0;0;0}
4. COUNTIF($B$1:B1,$A$1:$A$14)+IF(COUNTIF($A$1:$A$14,$A$1:$A$14)>1,0,1)
这里比较简单,直接相加就可以,返回的结果为:{0;0;0;1;1;1;1;1;1;1;1;0;0;0}
5.根据前面的步骤简化了以后为:MATCH(0,{0;0;0;1;1;1;1;1;1;1;1;0;0;0},0)
查找值0需要在数组{0;0;0;1;1;1;1;1;1;1;1;0;0;0}中进行精确匹配,匹配到第一个精确的值就会返回位置。在这个数组中第一个值就匹配,因此返回第一个值的位置1。
6. 根据前面的步骤简化了以后为:=INDEX($A$1:$A$14,1)
返回$A$1:$A$14中位置为1的单元格的值,这里就会返回苹果。
上述是在单元格B2里面输入以后的返回结果,验证以后是正确的,在$A$1:$A$14中苹果是有重复出现的。
下拉复制以后,在单元格B3里面,公式变为:
=INDEX($A$1:$A$14,MATCH(0,COUNTIF($B$1:B2,$A$1:$A$14)+IF(COUNTIF($A$1:$A$14,$A$1:$A$14)>1,0,1),0))
第一个COUNTIF中变为$B$1:B2,这样可以把查找过的值排除,不会再接在来的MATCH函数中匹配到。一直向下复制的话这部分引用区域会自动更新。因此,当没有重复值,无法匹配的时候,就会返回错误。
以上就是在另一列中返回重复值的公式和步骤详解了,可以实际进行尝试一下。
网站声明:如果转载,请联系本站管理员。否则一切后果自行承担。
加入交流群
请使用微信扫一扫!