办公软件 excel 查找不重复值函数方法集锦_chrisfang

内存数组法(数据源为内存定义, 最后查找结果也为内存数组)
/dispbbs.asp?boardid=3&replyid=407779&id=169505&page=1&skin=0&
如果数据源为文本
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)
注: 1, 原公式含义:newdata=LOOKUP(SMALL(IF(条件,ROW(INDIRECT("1:"&总行数))),ROW(INDIRECT("1:"&满足条件个数))),ROW(IND 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:"& 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)))))
注: 1, 如果数据源包含#N/A: 修改order定义: order=SMALL(IF(ISNA(data1),FALSE,data1),ROW(INDIRECT("1:"&SUM(--ISNUMBER
非内存数组(数据源为区域引用)
/dispbbs.asp?boardid=102&replyid=125412&id=103004&page=1&skin=
如果数据源为文本
方法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_
注意: 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的前半部分我在解答其他的竞赛题时想到了, 后来发现"开放式竞赛函数21题"中梧桐兄在43楼以及
summer.linn朋友在下面这个帖子中的18楼也采用了类似的思路(见方法2),
/dispbbs.asp?boardid=3&replyid=383401&id=165498&page=1&skin=0&Star=2
另外, gvntw版主在"开放式竞赛函数21题"的75楼对这种思路有更进一步的拓展, 直接清除了原公式的前面判断部分, 使得公式更为简化, 具
方法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)
注意: 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中的Lo 变化6也可以看成是把变化2中的Lookup用法替换成了Indirect+Min的用法。

5, 变化3的主公式有些朋友选择简化成为INDEX(data_arr,MATCH(,COUNTIF(D$83:D83,data_arr),)), 使用了一些缺省参数, 但两者含
方法3:(Frequency)
new_data1=IF(ROW(1:1)>ROUND(SUM(1/COUNTIF(data_arr,data_arr)),0),"",INDEX(data_arr,SMALL(IF(FREQUENCY(MATC
注意: 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))))&""
注意: 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
注意: 1, 这组方法为Countif的三维引用方法, 其中红色A$50、49为data_arr起始行位置, 需要根据数据区域实际位置进行调整。

2, 变化1改变了Index和Offset选取数据区域的起始位置。

有些朋友喜欢使用变化1这样的Offset写法, 认为这样不需要对数据源区域的但事实上, 使用此种Offset写法的前提条件是数据源区域所在列上面的那些单元格比较"干净", 不包含数据源中所含的元素, 否则就会出另外, 这样写也增加了Offset函数所生成的数组体积大小, 影响到公式的运算速度。

此方法为gvntw版主在"开放式竞赛函数21题"41楼的方法, 其中关于Countif函数的三维用法gvntw版主曾有专门的帖子讲解过:
/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
注意: 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(COUN
注意: 1, 其中a$175:a175、a175为结果公式位置的上一位置, 需要根据公式的实际位置进行调整。

2, 方法7主要是利用了COUNTIF(data_arr,">"&data_arr)来生成一个文字型数据中英文字母(或中文拼音)先后顺序的数组。

相当于用这个公式最后的结果是按照字母先后的"倒序"排列的。

3, 变化1为方法7的演化, 把Lookup的用法改成了Index+Match的用法。

4, 变化2为变化1的演化, 把结果改成按照字母先后的"顺序"排列。

5, 变化3为变化2的改进, 思路也是来源于gvntw版主的Countif帖中排序的用法。

6, 最后一种为Countif的三维引用方法, 类似于前面的方法5。

虽然这里Index的取数方法比前面方法5的更麻烦,明显是在舍近求远, 但这种×10^5+row()的取数方法在有些特别的例子中还是很有用的(比如前面的变化3), 所以这里也把这种方
如果数据源为数字
/dispbbs.asp?boardID=3&ID=165498&page=1&px=0
/dispbbs.asp?boardID=3&ID=141829&page=1
如果数据源为数字型元素, 前面文字型的公式方法一般都可以在此适用; 而且由于数字型的特殊性, 可以适用更多的函数方法。

另外, 对于数字型元素, 在结果的输出上可能会增加按照大小排序的要求, 一般情况下使用Small或Large函数就能解决这个输出要求,
也可以根据实际情况选取前面讨论过的另一些方法。

这里不再过多的讨论与前面重复的方法, 举几个特别适用于数字型的例子
方法1:方法2:方法3:方法4:方法5:方法6:
排序排序非排序排序非排序排序
注意: 1, 方法1为数字型元素特有的Frequency用法, 与文字型的方法3有些相似。

2, 方法2为Match用法, 与文字型的方法1十分类似。

3, 方法3为Countif用法, 与文字型的方法2的变化3、变化4十分类似。

4, 方法4是我对方法3的进一步改进, 使得结果具有排序功能。

这个方法很少在以前的帖子里见到。

5, 方法5去除了前面的判断部分, 与文字型的方法4十分类似。

6, 方法6为普通公式, 是我对aichong兄在前面帖子的12楼公式的进一步改进, 整合了首行的公式, 并且增加了公式前面的空行判断部
n=0&Star=1
等错误值, 否则需要加条件判断。

=rw))))),rw,data)
"&满足条件个数))),ROW(INDIRECT("1:"&总行数)),数据)
数, indirect部分简写:
行数为row条件。

"),rw),ROW(INDIRECT("1:"&SUM(N((MATCH(data,data,)=rw)*(data<>"")))))),rw,data) temp=rw))))),rw,data)
r公式之前需要增加条件判断。

ECT("1:"&SUM(--ISNUMBER(data1))))), 最后的newdata1定义公式不变。

skin=0&Star=8
arr,data_arr,0)=ROW(data_arr)-49,ROW(data_arr)-49),ROW(1:1))))
差, 可用round函数取整或+0.1来进行处理,
已经取出元素的个数(含重复元素), 例子见变化3。

断部分, 使得公式更为简化, 具体可以见方法4。

:a83,data_arr)=0),data_arr))
特点就是以目前已经得出的部分查找结果作为主公式的"比较部分"的引用参数。

目前已查找出来的结果中"不重复元素中的最后一个元素。

个参数2和后面那个被除数1都可以替换为其他的数字,
换为Offset的用法, 直接从数据区域来定位。

5也可以看成是把变化2中的Lookup用法替换成了Index+Match的用法。

用了一些缺省参数, 但两者含义基本相同, 其中变化3中的公式参数更为明确一些。

MATCH(data_arr,data_arr,),ROW(data_arr)-49),ROW(data_arr)-49),ROW(1:1)))) equency+Countif的组合, 虽然感觉有些舍近求远, 但也不失为一种独特的函数应用。

实际位置进行调整。

的前面判断部分, 使得公式更为简化。

SET(A$50,,,ROW(data_arr)-49),data_arr)=1,ROW(data_arr)-49),ROW(1:1))))
实际位置进行调整。

为这样不需要对数据源区域的起始位置进行定位, 简化了公式。

据源中所含的元素, 否则就会出错。

有专门的帖子讲解过:
RECT("1:"&SUM(1/COUNTIF(data_arr,data_arr))))),ROW(data_arr),data_arr)
与那里面的方法十分相似,
不作过多的变化展开。

了Index的用法;
也是一种独特的思路。

ntw版主对Countif函数用法进行整理的帖子。

OOKUP(2,1/N(SMALL(COUNTIF(data_arr,">"&data_arr),1+COUNTIF(data_arr,">="&a175))=COUNTIF(data_arr,">"&data_arr)),data_arr)) )先后顺序的数组。

相当于用Countif对文字型数据进行了排序。

变化3), 所以这里也把这种方法特别介绍一下。

更多的函数方法。

就能解决这个输出要求,
的例子。

增加了公式前面的空行判断部分。

_arr)),data_arr))。