查找不重复值函数方法集锦

内存数组法(数据源为内存定义,最后查找结果也为内存数组)http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=407779&id=169505&page=1&skin=0&Star=1如果数据源为文本data={1;2;1;2;"a";"a";"b"}注意:数组里没有空值、false之类的干扰数值,或#N/A等错误值,否则需要加条件判断。rw=ROW(INDIRECT("1:"&ROWS(data)))newdata＝LOOKUP(SMALL(IF(MATCH(data,data,)=rw,rw),ROW(INDIRECT("1:"&SUM(N(MATCH(data,data,)=rw))))),rw,data)rw:newdata:11223a4b567注:1,原公式含义:newdata=LOOKUP(SMALL(IF(条件,ROW(INDIRECT("1:"&总行数))),ROW(INDIRECT("1:"&满足条件个数))),ROW(INDIRECT("1:"&总行数)),数据)2,如果用“row总”来表示一个数组(或单元格区域)data的原始行数,用“row条件”表示满足条件的data个数,indirect部分简写:newdata=Lookup(small(if(条件,row(1:row总)),row(1:row条件)),row总,data),最后所得内存数组的行数为row条件。3,如果数据源包含空值:修改公式为:newdata:=LOOKUP(SMALL(IF((MATCH(data,data,)=rw)*(data<>""),rw),ROW(INDIRECT("1:"&SUM(N((MATCH(data,data,)=rw)*(data<>"")))))),rw,data)4,如果数据源包含#N/A:增加定义名称:data_temp=IF(ISNA(MATCH(data,data,)),0,MATCH(data,data,))修改公式为:newdata=LOOKUP(SMALL(IF(data_temp=rw,rw),ROW(INDIRECT("1:"&SUM(N(data_temp=rw))))),rw,data)如果数据源为数字(结果按大小排序)data1={1;2;false;1;2;4;5;9;9;17;false;false}注意:如果源数据中包含#N/A等错误返回值的话,order公式之前需要增加条件判断。order=SMALL(data1,ROW(INDIRECT("1:"&SUM(--ISNUMBER(data1)))))newdata1=SMALL(IF(FREQUENCY(order,order),order),ROW(INDIRECT("1:"&SUM(--(FREQUENCY(order,order)>0)))))order:newdata1:11122425495179917注:1,如果数据源包含#N/A:修改order定义:order=SMALL(IF(ISNA(data1),FALSE,data1),ROW(INDIRECT("1:"&SUM(--ISNUMBER(data1))))),最后的newdata1定义公式不变。非内存数组(数据源为区域引用)http://club.excelhome.net/dispbbs.asp?boardid=102&replyid=125412&id=103004&page=1&skin=0&Star=8如果数据源为文本data_arr:CCCAAACAABBCCCAB方法1:new_data1=IF(ROW(1:1)>SUM(1/COUNTIF(data_arr,data_arr)),"",INDEX(data_arr,SMALL(IF(MATCH(data_arr,data_arr,0)=ROW(data_arr)-49,ROW(data_arr)-49),ROW(1:1))))方法1:变化1:变化2:变化3:CCCCCCCCCACACACAAAAAAAAAABABABABBCBCBCBC注意:1,其中红色49为data_arr起始行位置,需要根据数据区域实际位置进行调整。2,公式中SUM(1/COUNTIF(data_arr,data_arr))部分为计算非重复元素个数,可能因为浮点运算产生误差,可用round函数取整或+0.1来进行处理,或用SUM(N(MATCH(data_arr,data_arr,0)=ROW(data_arr)-49))等方式进行替换,例子见变化1、变化2。3,前面部分也可采用ROWS(data_arr)<=SUM(COUNTIF(data_arr,D$61:D61))来替换,作用为计算前面已经取出元素的个数(含重复元素),例子见变化3。变化3的前半部分我在解答其他的竞赛题时想到了,后来发现"开放式竞赛函数21题"中梧桐兄在43楼以及summer.linn朋友在下面这个帖子中的18楼也采用了类似的思路(见方法2),http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=383401&id=165498&page=1&skin=0&Star=2另外,gvntw版主在"开放式竞赛函数21题"的75楼对这种思路有更进一步的拓展,直接清除了原公式的前面判断部分,使得公式更为简化,具体可以见方法4。方法2:new_data1=IF(ROW(1:1)>ROUND(SUM(1/COUNTIF(data_arr,data_arr)),0),"",LOOKUP(2,1/(COUNTIF(a$83:a83,data_arr)=0),data_arr))方法2:变化1:变化2:变化3:变化4:变化5:变化6:ABABABCCCCCCCCCCCACCCACACACABCCCBCAAAAAAAACABCCAABABABABAAABAABCBCBCBC注意:1,其中红色a$83:a83为结果公式位置的上一位置,需要根据公式的实际位置进行调整;这一组方法的特点就是以目前已经得出的部分查找结果作为主公式的"比较部分"的引用参数。2,公式中LOOKUP(2,1/(COUNTIF(a$83:a83,data_arr)=0),data_arr)部分,作用为查找"数据区域中"与"目前已查找出来的结果中"不重复元素中的最后一个元素。其中,去除重复元素的算法利用到了除法中除数为0时所返回的#DIV/0!错误。另外,Lookup的第一个参数2和后面那个被除数1都可以替换为其他的数字,只要保证前一个数大于后一个数,并不会影响公式的功能。3,变化1在本质上没有变化,只是更改了Lookup的查找参数;变化2是将其中的Countif的用法替换为了Match的用法;变化3把Lookup替换为了Match,来作为Index的行参数;变化4是在变化3的基础上,将Index的用法替换为Offset的用法,直接从数据区域来定位。4,变化5也是在变化3的基础上,将其中Countif的用法替换为Match的用法;如果与变化2联系起来,变化5也可以看成是把变化2中的Lookup用法替换成了Index+Match的用法。变化6也可以看成是把变化2中的Lookup用法替换成了Indirect+Min的用法。5,变化3的主公式有些朋友选择简化成为INDEX(data_arr,MATCH(,COUNTIF(D$83:D83,data_arr),)),使用了一些缺省参数,但两者含义基本相同,其中变化3中的公式参数更为明确一些。方法3:(Frequency)new_data1=IF(ROW(1:1)>ROUND(SUM(1/COUNTIF(data_arr,data_arr)),0),"",INDEX(data_arr,SMALL(IF(FREQUENCY(MATCH(data_arr,data_arr,),ROW(data_arr)-49),ROW(data_arr)-49),ROW(1:1))))方法3:变化1:变化2:变化3:CCCCCCCCCACACACAAAAAAAAAABABABABBCBCBCBC注意:1,其中红色49为data_arr起始行位置,需要根据数据区域实际位置进行调整。2,Frequency函数一般用于数字型数据,在这个文本型源数据的例子中,可以使用Frequency+Match或Frequency+Countif的组合,虽然感觉有些舍近求远,但也不失为一种独特的函数应用。3,变化1在本质上没有什么变化,只是修改了Frequency的参数;变化2把Match的用法替换成Countif的用法;变化3是把Frequency放到了公式前半部分的判断当中的用法,也使得方法1增加了一种变化的方向。方法4:new_data1=INDEX(A:A,MIN(IF(COUNTIF(A$120:A120,data_arr),65536,ROW(data_arr))))&""方法4:变化1:CCCCCACAAAAAABABBCBC注意:1,其中红色A:A为数据源data_arr所在列;A$120:A120为结果公式位置的上一位置,需要根据公式的实际位置进行调整。2,变化1用T()函数代替了&""。另外,此方法也可以演化成Offset、Indirect等形式,在此不再敷述。此方法为gvntw版主在"开放式竞赛函数21题"75楼的做法,是对方法2的更进一步的改进,直接清除了原公式的前面判断部分,使得公式更为简化。方法5:new_data1=IF(ROW(1:1)>ROUND(SUM(1/COUNTIF(data_arr,data_arr)),0),"",INDEX(data_arr,SMALL(IF(COUNTIF(OFFSET(A$50,,,ROW(data_arr)-49),data_arr)=1,ROW(data_arr)-49),ROW(1:1))))方法5:变化1:CCCCCACAAAAAABABBCBC注意:1,这组方法为Countif的三维引用方法,其中红色A$50、49为data_arr起始行位置,需要根据数据区域实际位置进行调整。2,变化1改变了Index和Offset选取数据区域的起始位置。有些朋友喜欢使用变化1这样的Offset写法,认为这样不需要对数据源区域的起始位置进行定位,简化了公式。但事实上,使用此种Offset写法的前提条件是数据源区域所在列上面的那些单元格比较"干净",不包含数据源中所含的元素,否则就会出错。另外,这样写也增加了Offset函数所生成的数组体积大小,影响到公式的运算速度。此方法为gvntw版主在"开放式竞赛函数21题"41楼的方法,其中关于Countif函数的三维用法gvntw版主曾有专门的帖子讲解过:http://club.excelhome.net/dispbbs.asp?BoardID=3&ID=93747&replyID=&skin=0方法6:(内存数组)new_data1=LOOKUP(SMALL(IF(MATCH(data_arr,data_arr,)=ROW(data_arr)-49,ROW(data_arr)),ROW(INDIRECT("1:"&SUM(1/COUNTIF(data_arr,data_arr))))),ROW(data_arr),data_arr)方法6:变化1:变化2:MMULT:CCCCCCCCCACACACAAAAAAAAAABABABABBCBCBCBC注意:1,其中红色49为data_arr起始行位置,需要根据数据区域实际位置进行调整。2,这组方法的特点是最后得出的结果本身为一个内存数组,前面讨论过"内存数组法"专题,这里的方法与那里面的方法十分相似,只是由于现在的数据源并不是内存数组,所以这里的方法可以更灵活多样一些。这里稍举几个例子,不作过多的变化展开。3,可以对比一下,方法6的公式与前面"内存数组法"专题里面的公式几乎完全相同;变化1把Lookup变成了Index的用法;变化2为Offset的用法,其中中心公式部分为Countif的三维用法,类似于方法5。4,MMULT的用法比较另类,是onkey兄在"开放式竞赛函数21题"57楼的答案,虽然也有些舍近求远,但也是一种独特的思路。方法7:下面这组方法是我对这个问题在其他方向上的一些思路拓展,主要涉及到了Countif函数的一些应用。感谢gvntw版主对Countif函数用法进行整理的帖子。new_data1=IF(MAX(COUNTIF(data_arr,">="&(a$175:a175)))>MAX(COUNTIF(data_arr,">"&data_arr)),"",LOOKUP(2,1/N(SMALL(COUNTIF(data_arr,">"&data_arr),1+COUNTIF(data_arr,">="&a175))=COUNTIF(data_arr,">"&data_arr)),data_arr))方法7:变化1:变化2:变化3:三维引用CCCCAAAACCCACAABABCABCBCBCBCAAABABCACAABAAAACCCCBC注意:1,其中a$175:a175、a175为结果公式位置的上一位置,需要根据公式的实际位置进行调整。2,方法7主要是利用了COUNTIF(data_arr,">"&data_arr)来生成一个文字型数据中英文字母(或中文拼音)先后顺序的数组。相当于用Countif对文字型数据进行了排序。这个公式最后的结果是按照字母先后的"倒序"排列的。3,变化1为方法7的演化,把Lookup的用法改成了Index+Match的用法。4,变化2为变化1的演化,把结果改成按照字母先后的"顺序"排列。5,变化3为变化2的改进,思路也是来源于gvntw版主的Countif帖中排序的用法。6,最后一种为Countif的三维引用方法,类似于前面的方法5。虽然这里Index的取数方法比前面方法5的更麻烦,明显是在舍近求远,但这种×10^5+row()的取数方法在有些特别的例子中还是很有用的(比如前面的变化3),所以这里也把这种方法特别介绍一下。如果数据源为数字http://club.excelhome.net/dispbbs.asp?boardID=3&ID=165498&page=1&px=0http://club.excelhome.net/dispbbs.asp?boardID=3&ID=141829&page=1如果数据源为数字型元素,前面文字型的公式方法一般都可以在此适用;而且由于数字型的特殊性,可以适用更多的函数方法。另外,对于数字型元素,在结果的输出上可能会增加按照大小排序的要求,一般情况下使用Small或Large函数就能解决这个输出要求,也可以根据实际情况选取前面讨论过的另一些方法。这里不再过多的讨论与前面重复的方法,举几个特别适用于数字型的例子。dat_arr:方法1:方法2:方法3:方法4:方法5:方法6:2frequencymatchcountifcountifcountif非数组公式5222222175555551177177177511111111111111171771771772排序排序非排序排序非排序排序注意:1,方法1为数字型元素特有的Frequency用法,与文字型的方法3有些相似。2,方法2为Match用法,与文字型的方法1十分类似。3,方法3为Countif用法,与文字型的方法2的变化3、变化4十分类似。4,方法4是我对方法3的进一步改进,使得结果具有排序功能。这个方法很少在以前的帖子里见到。5,方法5去除了前面的判断部分,与文字型的方法4十分类似。6,方法6为普通公式,是我对aichong兄在前面帖子的12楼公式的进一步改进,整合了首行的公式,并且增加了公式前面的空行判断部分。