excelperfect
问题很简洁:判断单元格中的数字有无重复?如下图1所示。
图1
注:本文示例整理自chandoo.org。
有重复?还是没有重复?这是经常遇到的情形。
对于简单的少量的数据,一眼就看得出来。然而,对于大量的数据,最好借助于Excel提供给我们的工具,譬如,公式。
解决问题的思路有多种,对应的解决方案也会有多种,尤其对于提供了丰富函数的Excel来说,更是如此。
下面的公式使用了SUBSTITUTE函数(替换)。
下面的公式首先使用SUBSTITUTE函数让字符“x”替换单元格中第2次出现的数字,如果有的话,替换之后就会变成字符串,这样在使用1和SUM函数求和时就会发生错误,这就表明单元格中存在重复的数字。
=IF(ISERROR(SUM(1*SUBSTITUTE(A1,ROW($A$1:$A$10)-1,'x',2))),'有重复数','没重复数')
这是一个数组公式,输入完成后要按Ctrl+Shift+Enter组合键。
下面的公式使用SUBSTITUTE函数依次用空替换单元格中的数字,然后计算替换后的值的长度,如果存在重复的数字,则至少替换2次,其长度会至少减少2,因此,如果替换后长度的差值小于2,则表明没有重复的数字。
=IF((LEN(A1)-MIN(LEN(SUBSTITUTE(A1,ROW($A$1:$A$10)-1,''))))<2,'没重复数','有重复数')
这是一个数组公式,输入完成后要按Ctrl+Shift+Enter组合键。
下面的公式使用了SEARCH函数或FIND函数(查找)。
下面的公式简单明了,使用SEARCH函数直接查找。
=IF(COUNT(SEARCH({'0*0','1*1','2*2','3*3','4*4','5*5','6*6','7*7','8*8','9*9'},A1))>0,'有重复数','没重复数')
或者:
=IF(COUNT(SEARCH(REPT({0,1,2,3,4,5,6,7,8,9}&'*',2),A1))>0,'有重复数','没重复数')
下面的公式运用了一个数学公式,有兴趣的可以自己体会。
=IF(SUM(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1),0))=LEN(A1)*(LEN(A1)+1)/2,'没重复数','有重复数')
这是一个数组公式,输入完成后要按Ctrl+Shift+Enter组合键。
下面的公式统计FIND函数查找的结果,然后与单元格中数字的长度比较,从而判断是否存在重复数。
=IF(LEN(A1)>COUNT(FIND({0,1,2,3,4}+{0;5},A1)),'有重复数','没重复数')
或者:
=IF(COUNT(FIND((ROW(1:10)-1),A1))=LEN(A1),'没重复数','有重复数')
这是一个数组公式,输入完成后要按Ctrl+Shift+Enter组合键。
下面的公式使用了其它一些函数。
下面的公式使用MODE函数获取将单元格中数拆分后的数字中有无重复值。
=IF(ISNUMBER(MODE(--MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1))),'有重复数','没重复数')
下面的公式使用FREQUENCY函数获取数字在0-9各区间出现的次数,如果最大值大于1,说明存在重复数字。
=IF(MAX(FREQUENCY(--MID(A1,ROW(INDIRECT('1:'&LEN(A1))),1),{0,1,2,3,4,5,6,7,8,9}))>1,'有重复数','没重复数')
下面的公式计算组成单元格中数的各个数字出现的次数,如果有大于1的,表明存在重复数字。
=IF(MAX(MMULT(1*ISNUMBER(TRANSPOSE(ROW(INDIRECT('1:'& LEN(A1))))),1*(MID(A1,ROW(INDIRECT('1:' &LEN(A1))),1)={'0','1','2','3','4','5','6','7','8','9'})))>1,'有重复数','没重复数')
或者:
=IF(MAX(MMULT(1*ISNUMBER(TRANSPOSE(ROW(INDIRECT('1:'& LEN(A1))))),1*(--MID(A1,ROW(INDIRECT('1:' &LEN(A1))),1)={0,1,2,3,4,5,6,7,8,9})))>1,'有重复数','没重复数')
这是一个数组公式,输入完成后要按Ctrl+Shift+Enter组合键。
还有更简洁而优雅的公式吗?
联系客服