查找不重復(fù)值函數(shù)方法集錦-chrisfang

"內(nèi)存數(shù)組法(數(shù)據(jù)源為內(nèi)存定義,最后查找結(jié)果也為內(nèi)存數(shù)組)",,,,,,,,

,,,,,,,,,

,,,,,,,,,

如果數(shù)據(jù)源為文本,,,,,,,,

"data={1;2;1;2;""a"";""a"";""b""}",,,,"注意:數(shù)組里沒有空值、false之類的干擾數(shù)值,或#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:,,,,,,

1,,1,,,,,,

2,,2,,,,,,

3,,a,,,,,,

4,,b,,,,,,

5,,,,,,,,

6,,,,,,,,

7,,,,,,,,

,,,,,,,,,

"注:1,原公式含義:newdata=LOOKUP(SMALL(IF(條件,ROW(INDIRECT(""1:""&總行數(shù)))),ROW(INDIRECT(""1:""&滿足條件個數(shù)))),ROW(INDIRECT(""1:""&總行數(shù))),數(shù)據(jù))",,,,,,,,

"2,如果用“row總”來表示一個數(shù)組(或單元格區(qū)域)data的原始行數(shù),用“row條件”表示滿足條件的data個數(shù),indirect部分簡寫:",,,,,,,,

"newdata=Lookup(small(if(條件,row(1:row總)),row(1:row條件)),row總,data),最后所得內(nèi)存數(shù)組的行數(shù)為row條件。",,,,,,,,

"3,如果數(shù)據(jù)源包含空值:修改公式為:newdata:=LOOKUP(SMALL(IF((MATCH(data,data,)=rw)*(data<>""""),rw),ROW(INDIRECT(""1:""&SUM(N((MATCH(data,data,)=rw)*(data<>"""")))))),rw,data)",,,,,,,,

"4,如果數(shù)據(jù)源包含#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)",,,,,,,,

,,,,,,,,,

如果數(shù)據(jù)源為數(shù)字(結(jié)果按大小排序),,,,,,,,

data1={1;2;false;1;2;4;5;9;9;17;false;false},,,,"注意:如果源數(shù)據(jù)中包含#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:,,,,,,

1,,1,,,,,,

1,,2,,,,,,

2,,4,,,,,,

2,,5,,,,,,

4,,9,,,,,,

5,,17,,,,,,

9,,,,,,,,

9,,,,,,,,

17,,,,,,,,

"注:1,如果數(shù)據(jù)源包含#N/A:修改order定義:order=SMALL(IF(ISNA(data1),FALSE,data1),ROW(INDIRECT(""1:""&SUM(--ISNUMBER(data1))))),最后的newdata1定義公式不變。",,,,,,,,

,,,,,,,,

,,,,,,,,,

,,,,,,,,,

非內(nèi)存數(shù)組(數(shù)據(jù)源為區(qū)域引用),,,,,,,,

/dispbbs.asp?boardid=102&replyid=125412&id=103004&page=1&skin=0&Star=8,,,,,,,,

如果數(shù)據(jù)源為文本,,,,,,,,

data_arr:,,,,,,,,

CC,,,,,,,,

CA,,,,,,,,

AA,,,,,,,,

CA,,,,,,,,

AB,,,,,,,,

BC,,,,,,,,

CC,,,,,,,,

AB,,,,,,,,

,,,,,,,,,

方法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:,,,,,

CC,CC,CC,CC,,,,,

CA,CA,CA,CA,,,,,

AA,AA,AA,AA,,,,,

AB,AB,AB,AB,,,,,

BC,BC,BC,BC,,,,,

,,,,,,,,

,,,,,,,,,

"注意:1,其中紅色49為data_arr起始行位置,需要根據(jù)數(shù)據(jù)區(qū)域?qū)嶋H位置進(jìn)行調(diào)整。",,,,,,,,

"2,公式中SUM(1/COUNTIF(data_arr,data_arr))部分為計算非重復(fù)元素個數(shù),可能因為浮點(diǎn)運(yùn)算產(chǎn)生誤差,可用round函數(shù)取整或+0.1來進(jìn)行處理,",,,,,,,,

"或用SUM(N(MATCH(data_arr,data_arr,0)=ROW(data_arr)-49))等方式進(jìn)行替換,例子見變化1、變化2。",,,,,,,,

"3,前面部分也可采用ROWS(data_arr)<=SUM(COUNTIF(data_arr,D$61:D61))來替換,作用為計算前面已經(jīng)取出元素的個數(shù)(含重復(fù)元素),例子見變化3。",,,,,,,,

,,,,,,,,,

"變化3的前半部分我在解答其他的競賽題時想到了,后來發(fā)現(xiàn)""開放式競賽函數(shù)21題""中梧桐兄在43樓以及",,,,,,,,

"summer.linn朋友在下面這個帖子中的18樓也采用了類似的思路(見方法2),",,,,,,,,

/dispbbs.asp?boardid=3&replyid=383401&id=165498&page=1&skin=0&Star=2,,,,,,,,

,,,,,,,,,

"另外,gvntw版主在""開放式競賽函數(shù)21題""的75樓對這種思路有更進(jìn)一步的拓展,直接清除了原公式的前面判斷部分,使得公式更為簡化,具體可以見方法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:,,

AB,AB,AB,CC,CC,CC,CC,,

CC,CA,CC,CA,CA,CA,CA,,

BC,CC,BC,AA,AA,AA,AA,,

CA,BC,CA,AB,AB,AB,AB,,

AA,AB,AA,BC,BC,BC,BC,,

,,,,,,,,

,,,,,,,,,

"注意:1,其中紅色a$83:a83為結(jié)果公式位置的上一位置,需要根據(jù)公式的實際位置進(jìn)行調(diào)整;這一組方法的特點(diǎn)就是以目前已經(jīng)得出的部分查找結(jié)果作為主公式的""比較部分""的引用參數(shù)。",,,,,,,,

"2,公式中LOOKUP(2,1/(COUNTIF(a$83:a83,data_arr)=0),data_arr)部分,作用為查找""數(shù)據(jù)區(qū)域中""與""目前已查找出來的結(jié)果中""不重復(fù)元素中的最后一個元素。",,,,,,,,

"其中,去除重復(fù)元素的算法利用到了除法中除數(shù)為0時所返回的#DIV/0!錯誤。另外,Lookup的第一個參數(shù)2和后面那個被除數(shù)1都可以替換為其他的數(shù)字,",,,,,,,,

"只要保證前一個數(shù)大于后一個數(shù),并不會影響公式的功能。",,,,,,,,

"3,變化1在本質(zhì)上沒有變化,只是更改了Lookup的查找參數(shù);變化2是將其中的Countif的用法替換為了Match的用法;",,,,,,,,

"變化3把Lookup替換為了Match,來作為Index的行參數(shù);變化4是在變化3的基礎(chǔ)上,將Index的用法替換為Offset的用法,直接從數(shù)據(jù)區(qū)域來定位。",,,,,,,,

"4,變化5也是在變化3的基礎(chǔ)上,將其中Countif的用法替換為Match的用法;如果與變化2聯(lián)系起來,變化5也可以看成是把變化2中的Lookup用法替換成了Index+Match的用法。",,,,,,,,

變化6也可以看成是把變化2中的Lookup用法替換成了Indirect+Min的用法。,,,,,,,,

"5,變化3的主公式有些朋友選擇簡化成為INDEX(data_arr,MATCH(,COUNTIF(D$83:D83,data_arr),)),使用了一些缺省參數(shù),但兩者含義基本相同,其中變化3中的公式參數(shù)更為明確一些。",,,,,,,,

,,,,,,,,,

,,,,,,,,,

方法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:,,,,,

CC,CC,CC,CC,,,,,

CA,CA,CA,CA,,,,,

AA,AA,AA,AA,,,,,

AB,AB,AB,AB,,,,,

BC,BC,BC,BC,,,,,

,,,,,,,,

,,,,,,,,,

"注意:1,其中紅色49為data_arr起始行位置,需要根據(jù)數(shù)據(jù)區(qū)域?qū)嶋H位置進(jìn)行調(diào)整。",,,,,,,,

"2,Frequency函數(shù)一般用于數(shù)字型數(shù)據(jù),在這個文本型源數(shù)據(jù)的例子中,可以使用Frequency+Match或Frequency+Countif的組合,雖然感覺有些舍近求遠(yuǎn),但也不失為一種獨(dú)特的函數(shù)應(yīng)用。",,,,,,,,

"3,變化1在本質(zhì)上沒有什么變化,只是修改了Frequency的參數(shù);變化2把Match的用法替換成Countif的用法;",,,,,,,,

"變化3是把Frequency放到了公式前半部分的判斷當(dāng)中的用法,也使得方法1增加了一種變化的方向。",,,,,,,,

,,,,,,,,,

,,,,,,,,,

方法4:,,,,,,,,

"new_data1=INDEX(A:A,MIN(IF(COUNTIF(A$120:A120,data_arr),65536,ROW(data_arr))))&""""",,,,,,,,

方法4:,變化1:,,,,,,,

CC,CC,,,,,,,

CA,CA,,,,,,,

AA,AA,,,,,,,

AB,AB,,,,,,,

BC,BC,,,,,,,

,,,,,,,,

"注意:1,其中紅色A:A為數(shù)據(jù)源data_arr所在列;A$120:A120為結(jié)果公式位置的上一位置,需要根據(jù)公式的實際位置進(jìn)行調(diào)整。",,,,,,,,

"2,變化1用T()函數(shù)代替了&""""。另外,此方法也可以演化成Offset、Indirect等形式,在此不再敷述。",,,,,,,,

,,,,,,,,,

"此方法為gvntw版主在""開放式競賽函數(shù)21題""75樓的做法,是對方法2的更進(jìn)一步的改進(jìn),直接清除了原公式的前面判斷部分,使得公式更為簡化。",,,,,,,,

,,,,,,,,,

,,,,,,,,,

,,,,,,,,,

方法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:,,,,,,,

CC,CC,,,,,,,

CA,CA,,,,,,,

AA,AA,,,,,,,

AB,AB,,,,,,,

BC,BC,,,,,,,

,,,,,,,,

,,,,,,,,,

"注意:1,這組方法為Countif的三維引用方法,其中紅色A$50、49為data_arr起始行位置,需要根據(jù)數(shù)據(jù)區(qū)域?qū)嶋H位置進(jìn)行調(diào)整。",,,,,,,,

"2,變化1改變了Index和Offset選取數(shù)據(jù)區(qū)域的起始位置。有些朋友喜歡使用變化1這樣的Offset寫法,認(rèn)為這樣不需要對數(shù)據(jù)源區(qū)域的起始位置進(jìn)行定位,簡化了公式。",,,,,,,,

"但事實上,使用此種Offset寫法的前提條件是數(shù)據(jù)源區(qū)域所在列上面的那些單元格比較""干凈"",不包含數(shù)據(jù)源中所含的元素,否則就會出錯。",,,,,,,,

"另外,這樣寫也增加了Offset函數(shù)所生成的數(shù)組體積大小,影響到公式的運(yùn)算速度。",,,,,,,,

,,,,,,,,

"此方法為gvntw版主在""開放式競賽函數(shù)21題""41樓的方法,其中關(guān)于Countif函數(shù)的三維用法gvntw版主曾有專門的帖子講解過:",,,,,,,,

/dispbbs.asp?BoardID=3&ID=93747&replyID=&skin=0,,,,,,,,

,,,,,,,,,

,,,,,,,,,

,,,,,,,,,

方法6:(內(nèi)存數(shù)組),,,,,,,,

"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:,,,,

CC,CC,CC,,CC,,,,

CA,CA,CA,,CA,,,,

AA,AA,AA,,AA,,,,

AB,AB,AB,,AB,,,,

BC,BC,BC,,BC,,,,

,,,,,,,,,

"注意:1,其中紅色49為data_arr起始行位置,需要根據(jù)數(shù)據(jù)區(qū)域?qū)嶋H位置進(jìn)行調(diào)整。",,,,,,,,

"2,這組方法的特點(diǎn)是最后得出的結(jié)果本身為一個內(nèi)存數(shù)組,前面討論過""內(nèi)存數(shù)組法""專題,這里的方法與那里面的方法十分相似,",,,,,,,,

"只是由于現(xiàn)在的數(shù)據(jù)源并不是內(nèi)存數(shù)組,所以這里的方法可以更靈活多樣一些。這里稍舉幾個例子,不作過多的變化展開。",,,,,,,,

"3,可以對比一下,方法6的公式與前面""內(nèi)存數(shù)組法""專題里面的公式幾乎完全相同;變化1把Lookup變成了Index的用法;",,,,,,,,

"變化2為Offset的用法,其中中心公式部分為Countif的三維用法,類似于方法5。",,,,,,,,

"4,MMULT的用法比較另類,是onkey兄在""開放式競賽函數(shù)21題""57樓的答案,雖然也有些舍近求遠(yuǎn),但也是一種獨(dú)特的思路。",,,,,,,,

方法7:,,,,,,,,

"下面這組方法是我對這個問題在其他方向上的一些思路拓展,主要涉及到了Countif函數(shù)的一些應(yīng)用。感謝gvntw版主對Countif函數(shù)用法進(jìn)行整理的帖子。",,,,,,,,

"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:,,三維引用,,,

CC,CC,AA,AA,,CC,,,

CA,CA,AB,AB,,CA,,,

BC,BC,BC,BC,,AA,,,

AB,AB,CA,CA,,AB,,,

AA,AA,CC,CC,,BC,,,

,,,,,,,,

"注意:1,其中a$175:a175、a175為結(jié)果公式位置的上一位置,需要根據(jù)公式的實際位置進(jìn)行調(diào)整。",,,,,,,,

"2,方法7主要是利用了COUNTIF(data_arr,"">""&data_arr)來生成一個文字型數(shù)據(jù)中英文字母(或中文拼音)先后順序的數(shù)組。相當(dāng)于用Countif對文字型數(shù)據(jù)進(jìn)行了排序。",,,,,,,,

"這個公式最后的結(jié)果是按照字母先后的""倒序""排列的。",,,,,,,,

"3,變化1為方法7的演化,把Lookup的用法改成了Index+Match的用法。",,,,,,,,

"4,變化2為變化1的演化,把結(jié)果改成按照字母先后的""順序""排列。",,,,,,,,

"5,變化3為變化2的改進(jìn),思路也是來源于gvntw版主的Countif帖中排序的用法。",,,,,,,,

"6,最后一種為Countif的三維引用方法,類似于前面的方法5。雖然這里Index的取數(shù)方法比前面方法5的更麻煩,",,,,,,,,

"明顯是在舍近求遠(yuǎn),但這種×10^5+row()的取數(shù)方法在有些特別的例子中還是很有用的(比如前面的變化3),所以這里也把這種方法特別介紹一下。",,,,,,,,

如果數(shù)據(jù)源為數(shù)字,,,,,,,,

/dispbbs.asp?boardID=3&ID=165498&page=1&px=0,,,,,,,,

/dispbbs.asp?boardID=3&ID=141