查找不重复值函数方法集锦_chrisfang
- 格式：xls
- 大小：82.50 KB
- 文档页数：20
只 要保证前一 个数大于后 一个数, 并不 会影响公式 的功能。
3, 变 化1在本质上 没有变化, 只 是更改了 Lookup的查 找参数; 变 化2是将其中 的Countif的 用法替换为 了Match的 用法;
变 化3把 Lookup替换 为了Match, 来作为Index 的行参数; 变化4是在变 化3的基础 上, 将Index 的用法替换 为Offset的 用法, 直接从 数据区域来 定位。
rw: 1 2 3 4 5 6 7
newdata: 1 2 a b
注意: 数组 里没有空值 、false之类 的干扰数值, 或#N/A等错 误值, 否则需 要加条件判 断。
注: 1, 原公 式含义: newdata=L OOKUP(SMA LL(IF(条 件,ROW(IND IRECT("1:"& 总行 数))),ROW(I NDIRECT("1 :"&满足条件 个 数))),ROW(I NDIRECT("1 :"&总行数)), 数据)
非内存数组 (数据源为区 域ht引tp用:/)/clu b.excelho /di spbbs.as p?boardid =102&re plyid=12 5412&id= 103004& page=1& skin=0&S tar=8
如果数据源 为文本
data_arr: CC
CA
AA
CA
如果数据源 为数字(结果 按大小排序)
data1={1;2; false;1;2;4; 5;9;9;17;fal se;false} order=SMAL L(data1,RO W(INDIREC T("1:"&SUM (-ISNUMBER( data1)))))
newdata1=S MALL(IF(FR EQUENCY(or der,order),o rder),ROW(I NDIRECT("1 :"&SUM(-(FREQUENC Y(order,orde r)>0)))))
Frequency+ Match或
Frequency+ Countif的组 合, 虽然感觉 有些舍近求 远, 但也不失 为一种独特 的函数应用 。
3, 变 化1在本质上 没有什么变 化, 只是修改 了Frequency 的参数; 变 化2把Match 的用法替换 成Countif的 用法;
变 化3是把 Frequency放 到了公式前 半部分的判 断当中的用 法, 也使得方 法1增加了 一种变化的 方向。
方法2:
AB
CC
BC
CA
AA
变化1: AB CA CC BC AB
变化2: AB CC BC CA AA
变化3: CC CA AA AB BC
变化4: CC CA AA AB BC
变化5: CC CA AA AB BC
变化6: CC CA AA AB BC
注意: 1, 其 中红色
a$83:a83 为结果公式 位置的上一 位置, 需要根 据公式的实 际位置进行 调整; 这一 组方法的特 点就是以目 前已经得出 的部分查找 结果作为主 公式的"比较 部分"的引用 参数。
4, 如果 数据源包含 #N/A: 增加 定义名称: data_temp =IF(ISNA(M ATCH(data,d ata,)),0,MAT CH(data,dat a,))
修改 公式为: newdata=L OOKUP(SMA LL(IF(data_ temp=rw,r w),ROW(IN DIRECT("1:" &SUM(N(da ta_temp=r w))))),rw,da ta)
2, 公 式中
LOOKUP(2,1
/(COUNTIF(
a$83:a83,da
ta_arr)=0),d ata_arr)部 分, 作用为查 找"数据区域 中"与"目前 已查找出来 的结果中"不 重复元素中 的最后一个 元素。
其 中, 去除重复 元素的算法 利用到了除 法中除数为0 时所返回的 #DIV/0!错 误。 另外, Lookup的第 一个参数2和 后面那个被 除数1都可以 替换为其他 的数字,
方法5: new_data1= IF(ROW(1:1 )>ROUND(S UM(1/COUN TIF(data_arr ,data_arr)), 0),"",INDEX( data_arr,SM ALL(IF(COU NTIF(OFFSE T(A$50,,,RO W(data_arr) 49),data_arr )=1,ROW(d ata_arr)49),ROW(1: 1))))
2, 如果 用“row总” 来表示一, 用“row条件 ”表示满足条 件的data个 数, indirect 部分简写:
newdata=Lo okup(small(i f(条 件,row(1:ro w 总)),row(1:r ow条 件)),row 总,data), 最 后所得内存 数组的行数 为row条件 。
内存数组法 (数据源为内 存定义, 最 后查找结果 也为内存数 组ht)tp://clu b.excelho /di spbbs.as p?boardid =3&replyi d=40777 9&id=169 505&pag e=1&skin =0&Star =1
如果数据源 为文本
data={1;2;1 ;2;"a";"a";"b "} rw=ROW(IN DIRECT("1:" &ROWS(dat a))) newdata＝ LOOKUP(SM ALL(IF(MAT CH(data,dat a,)=rw,rw), ROW(INDIR ECT("1:"&S UM(N(MATC H(data,data, )=rw))))),rw ,data)
方法1:
CC
CA
AA
AB
BC
变化1: CC CA AA AB BC
变化2: CC CA AA AB BC
变化3: CC CA AA AB BC
注意: 1, 其 中红色49为 data_arr起 始行位置, 需 要根据数据 区域实际位 置进行调整 。
2, 公 式中 SUM(1/COU NTIF(data_a rr,data_arr)) 部分为计算 非重复元素 个数, 可能因 为浮点运算 产生误差, 可 用round函数 取整或+0.1 来进行处理,
3, 如果 数据源包含 空值: 修改 公式为: newdata:=L OOKUP(SMA LL(IF((MATC H(data,data, )=rw)*(data <>""),rw),R OW(INDIRE CT("1:"&SU M(N((MATC H(data,data, )=rw)*(data <>"")))))),r w,data)
变化3的前半 部分我在解 答其他的竞 赛题时想到 了, 后来发现 "开放式竞赛 函数21题" 中梧桐兄在 43楼以及 summer.li nn朋友在下 面这个帖子 中的18楼也 采用了类似 的思路(见方 法2), http://club .excelhom /disp bbs.asp?b oardid=3& replyid=38 3401&id= 165498&p age=1&ski n=0&Star =2
另外, gvntw版主 在"开放式竞 赛函数21题 "的75楼对这 种思路有更 进一步的拓 展, 直接清除 了原公式的 前面判断部 分, 使得公式 更为简化, 具 体可以见方 法4。
方法2: new_data1= IF(ROW(1:1 )>ROUND(S UM(1/COUN TIF(data_arr ,data_arr)), 0),"",LOOKU P(2,1/(COU NTIF(a$83:a 83,data_arr) =0),data_ar r))
order: 1 1 2
newdata1: 1 2 4
注意: 如果 源数据中包 含#N/A等错 误返回值的 话, order公 式之前需要 增加条件判 断。
2
5
4
9
5
17
9
9
17 注: 1, 如果 数据源包含 #N/A: 修改 order定义: order=SMA LL(IF(ISNA (data1),FA LSE,data1) ,ROW(INDIR ECT("1:"&S UM(-ISNUMBER( data1))))), 最后的 newdata1定 义公式不变 。
或 用 SUM(N(MAT CH(data_arr ,data_arr,0) =ROW(data _arr)-49))等 方式进行替 换, 例子见变 化1、变化2 。
3, 前 面部分也可 采用 ROWS(data _arr)<=SUM (COUNTIF(d ata_arr,D$6 1:D61))来替 换, 作用为计 算前面已经 取出元素的 个数(含重复 元素), 例子 见变化3。
方法5:
CC
CA
AA
AB
BC
变化1: CC CA AA AB BC
注意: 1, 这 组方法为 Countif的三 维引用方法, 其中红色 A$50、49 为data_arr 起始行位置, 需要根据数 据区域实际 位置进行调 整。
2, 变 化1改变了 Index和 Offset选取 数据区域的 起始位置。 有些朋友喜 欢使用变化1 这样的 Offset写法, 认为这样不 需要对数据 源区域的起 始位置进行 定位, 简化了 公式。
3, 变 化1在本质上 没有变化, 只 是更改了 Lookup的查 找参数; 变 化2是将其中 的Countif的 用法替换为 了Match的 用法;
变 化3把 Lookup替换 为了Match, 来作为Index 的行参数; 变化4是在变 化3的基础 上, 将Index 的用法替换 为Offset的 用法, 直接从 数据区域来 定位。
rw: 1 2 3 4 5 6 7
newdata: 1 2 a b
注意: 数组 里没有空值 、false之类 的干扰数值, 或#N/A等错 误值, 否则需 要加条件判 断。
注: 1, 原公 式含义: newdata=L OOKUP(SMA LL(IF(条 件,ROW(IND IRECT("1:"& 总行 数))),ROW(I NDIRECT("1 :"&满足条件 个 数))),ROW(I NDIRECT("1 :"&总行数)), 数据)
非内存数组 (数据源为区 域ht引tp用:/)/clu b.excelho /di spbbs.as p?boardid =102&re plyid=12 5412&id= 103004& page=1& skin=0&S tar=8
如果数据源 为文本
data_arr: CC
CA
AA
CA
如果数据源 为数字(结果 按大小排序)
data1={1;2; false;1;2;4; 5;9;9;17;fal se;false} order=SMAL L(data1,RO W(INDIREC T("1:"&SUM (-ISNUMBER( data1)))))
newdata1=S MALL(IF(FR EQUENCY(or der,order),o rder),ROW(I NDIRECT("1 :"&SUM(-(FREQUENC Y(order,orde r)>0)))))
Frequency+ Match或
Frequency+ Countif的组 合, 虽然感觉 有些舍近求 远, 但也不失 为一种独特 的函数应用 。
3, 变 化1在本质上 没有什么变 化, 只是修改 了Frequency 的参数; 变 化2把Match 的用法替换 成Countif的 用法;
变 化3是把 Frequency放 到了公式前 半部分的判 断当中的用 法, 也使得方 法1增加了 一种变化的 方向。
方法2:
AB
CC
BC
CA
AA
变化1: AB CA CC BC AB
变化2: AB CC BC CA AA
变化3: CC CA AA AB BC
变化4: CC CA AA AB BC
变化5: CC CA AA AB BC
变化6: CC CA AA AB BC
注意: 1, 其 中红色
a$83:a83 为结果公式 位置的上一 位置, 需要根 据公式的实 际位置进行 调整; 这一 组方法的特 点就是以目 前已经得出 的部分查找 结果作为主 公式的"比较 部分"的引用 参数。
4, 如果 数据源包含 #N/A: 增加 定义名称: data_temp =IF(ISNA(M ATCH(data,d ata,)),0,MAT CH(data,dat a,))
修改 公式为: newdata=L OOKUP(SMA LL(IF(data_ temp=rw,r w),ROW(IN DIRECT("1:" &SUM(N(da ta_temp=r w))))),rw,da ta)
2, 公 式中
LOOKUP(2,1
/(COUNTIF(
a$83:a83,da
ta_arr)=0),d ata_arr)部 分, 作用为查 找"数据区域 中"与"目前 已查找出来 的结果中"不 重复元素中 的最后一个 元素。
其 中, 去除重复 元素的算法 利用到了除 法中除数为0 时所返回的 #DIV/0!错 误。 另外, Lookup的第 一个参数2和 后面那个被 除数1都可以 替换为其他 的数字,
方法5: new_data1= IF(ROW(1:1 )>ROUND(S UM(1/COUN TIF(data_arr ,data_arr)), 0),"",INDEX( data_arr,SM ALL(IF(COU NTIF(OFFSE T(A$50,,,RO W(data_arr) 49),data_arr )=1,ROW(d ata_arr)49),ROW(1: 1))))
2, 如果 用“row总” 来表示一, 用“row条件 ”表示满足条 件的data个 数, indirect 部分简写:
newdata=Lo okup(small(i f(条 件,row(1:ro w 总)),row(1:r ow条 件)),row 总,data), 最 后所得内存 数组的行数 为row条件 。
内存数组法 (数据源为内 存定义, 最 后查找结果 也为内存数 组ht)tp://clu b.excelho /di spbbs.as p?boardid =3&replyi d=40777 9&id=169 505&pag e=1&skin =0&Star =1
如果数据源 为文本
data={1;2;1 ;2;"a";"a";"b "} rw=ROW(IN DIRECT("1:" &ROWS(dat a))) newdata＝ LOOKUP(SM ALL(IF(MAT CH(data,dat a,)=rw,rw), ROW(INDIR ECT("1:"&S UM(N(MATC H(data,data, )=rw))))),rw ,data)
方法1:
CC
CA
AA
AB
BC
变化1: CC CA AA AB BC
变化2: CC CA AA AB BC
变化3: CC CA AA AB BC
注意: 1, 其 中红色49为 data_arr起 始行位置, 需 要根据数据 区域实际位 置进行调整 。
2, 公 式中 SUM(1/COU NTIF(data_a rr,data_arr)) 部分为计算 非重复元素 个数, 可能因 为浮点运算 产生误差, 可 用round函数 取整或+0.1 来进行处理,
3, 如果 数据源包含 空值: 修改 公式为: newdata:=L OOKUP(SMA LL(IF((MATC H(data,data, )=rw)*(data <>""),rw),R OW(INDIRE CT("1:"&SU M(N((MATC H(data,data, )=rw)*(data <>"")))))),r w,data)
变化3的前半 部分我在解 答其他的竞 赛题时想到 了, 后来发现 "开放式竞赛 函数21题" 中梧桐兄在 43楼以及 summer.li nn朋友在下 面这个帖子 中的18楼也 采用了类似 的思路(见方 法2), http://club .excelhom /disp bbs.asp?b oardid=3& replyid=38 3401&id= 165498&p age=1&ski n=0&Star =2
另外, gvntw版主 在"开放式竞 赛函数21题 "的75楼对这 种思路有更 进一步的拓 展, 直接清除 了原公式的 前面判断部 分, 使得公式 更为简化, 具 体可以见方 法4。
方法2: new_data1= IF(ROW(1:1 )>ROUND(S UM(1/COUN TIF(data_arr ,data_arr)), 0),"",LOOKU P(2,1/(COU NTIF(a$83:a 83,data_arr) =0),data_ar r))
order: 1 1 2
newdata1: 1 2 4
注意: 如果 源数据中包 含#N/A等错 误返回值的 话, order公 式之前需要 增加条件判 断。
2
5
4
9
5
17
9
9
17 注: 1, 如果 数据源包含 #N/A: 修改 order定义: order=SMA LL(IF(ISNA (data1),FA LSE,data1) ,ROW(INDIR ECT("1:"&S UM(-ISNUMBER( data1))))), 最后的 newdata1定 义公式不变 。
或 用 SUM(N(MAT CH(data_arr ,data_arr,0) =ROW(data _arr)-49))等 方式进行替 换, 例子见变 化1、变化2 。
3, 前 面部分也可 采用 ROWS(data _arr)<=SUM (COUNTIF(d ata_arr,D$6 1:D61))来替 换, 作用为计 算前面已经 取出元素的 个数(含重复 元素), 例子 见变化3。
方法5:
CC
CA
AA
AB
BC
变化1: CC CA AA AB BC
注意: 1, 这 组方法为 Countif的三 维引用方法, 其中红色 A$50、49 为data_arr 起始行位置, 需要根据数 据区域实际 位置进行调 整。
2, 变 化1改变了 Index和 Offset选取 数据区域的 起始位置。 有些朋友喜 欢使用变化1 这样的 Offset写法, 认为这样不 需要对数据 源区域的起 始位置进行 定位, 简化了 公式。
