|
|
为了叙述的方便，为了减少我解释的难度，我把你的数据改了，改成没有空白的数据了，相信你看完本帖后还是能够处理它的。
提取不重复值，我习惯建立一个数组，并把它定义为一个名称，还习惯用X命名，数组X中所有数值为“不重复值”所在的行号，如本例，X={2;3;false;5;6;false}，X相当于一份花名册，下一步按花名册点名就是。
MATCH()是干什么的？
建立X数组，首先应归功于查找函数MATCH()，简单地说，按需要，MATCH()返回一个数据在指定区域中第一次出现的位置。我们用数据区域$A$2:$A$7作函数的前两个参数，第三个参数一般为0，公式为：MATCH($A$2:$A$7,$A$2:$A$7,0)，公式的运算结果相当于：
MATCH($A$2,$A$2:$A$7,0)
MATCH($A$3,$A$2:$A$7,0)
……
MATCH($A$7,$A$2:$A$7,0) '23楼 chenhuafu 更正
最后结果是一个数组(见①)。列中有两个yy01，两个yy01在指定区域中第一次出现的位置是相同的，都是1，我们要的就是这种效果，见下面。 判断了每一个数据在区域中首次出现的位置，接着再判断每一个数据在区域中所处的位置，用函数Row()，Row($A$2:$A$7)返回的结果相当于：
Row($A$2)
Row($A$3)
……
Row($A$7)
最后结果也是一个数组(见②，为了便于对比，②的结果为Row()-1)。 第三步，对以上两组结果进行对比，返回一个新的结果：
如果Row()-1=Match()，公式就记下这个数据所在的行号：
=IF(MATCH(…)=ROW(…)-1,ROW(…))
为什么要减1？因为数据区域$A$2:$A$7的第一行是2，Match()返回的第一个结果是1，首次出现的数据，它的行号减1就是Match()的值，如果数据区域是$A$20:$A$70，则公式为:
=IF(MATCH(…)=ROW(…)-19,ROW(…))
如果起始行不是A2,而是A1，公式应该怎样改？这样：
=IF(MATCH(…)=ROW(…),ROW(…))
最后结果见③，所有数值表示该行的数据是第一次出现，所有行号没有重复(我们需要的就是它)，所有重复数据在这个公式中，返回的结果不是行号，是一个逻辑值False。 实际运用的时候，我们可以在任一单元格中编写公式：
=IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7))
在编辑栏中选择公式，复制它。
然后按Ctrl+F3定义名称，在名称栏中写X，当然，你命名为Y或其它名称也行，在引用位置按Ctrl+V粘贴，确定。
定义名称完毕。 Index(A:A,行号)返回结果，如果数据在D列，则：
Index(D:D,行号) 这个行号，我们用Small()函数返回，在帮助中我们可以看到，Small()是“返回数据集中第 k 个最小值。”，这里，数据集是我们刚才定义的X：
Small(X,1) ==>X 中第1个最小值
Small(X,2) ==>X 中第2个最小值
就这个意思。 这里的参数k，我们不用数字1、2、3，而用Row(1:1)或Row(A1)
形式的函数，目的是方便公式的复制，写好第一个公式，用鼠标把它往下一拖，第二个公式中该位置，就变成了Row(2:2)或Row(A2)。记住，无论我们把结果写到工作表的哪个位置，第一个单元格该参数为Row(1:1)。结果见⑤。
我们把Small()函数取得的结果作为Index()函数的行号写入公式，就能取得我们要的结果，见⑥。 我们总不喜欢在结果中看见错误值 #NUM! 。外层函数If()的作用就是它。Count(x)是计算x数组中有几个数值，即我们要的不重复值有几个，当Row()行号大于它时，显示空值：
If(Row(1:1)>Count(x),"",……) 当结果不是垂直排列，而是水平排成一行时，名称X不变，单元格公式中将Row(1:1)改为Column(a:a)即可。
函数Large()是返回第k个最大值，如果用这个函数代替Small()，结果的排列顺序将会倒过来。
我们在定义X时，数据区域最好用绝对引用，避免不必要的麻烦，要编辑栏，写完地址按一下F4，它会帮你转换为绝对引用。写公式时不用写表名，定义名称时，系统自动会加上表名的，这是闲话。
关于不重复值，是个大话题，我只对本公式谈些心得就是，就本公式而言，也是千变万化的，也得根据实际情况作些调整，如X的结果不一定就是行号，Index()也可用Offste()等。 这是取不重复值解决办法的进阶问题，我们要排除数据列中的空白数据，可以在定义X时外层加一个If()函数：
x=IF($A$2:$A$7<>"",IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7)))
事实上，用该公式取不重复值，返回的结果也不能包含空值，Match()会因此返回错误值。
本例中，如果我们只取xx开头的数据，那么公式可以这样写
x=IF(LEFT($A$2:$A$7,2)="xx",IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7)))
你还可以把这个条件换成其它新鲜一些的，没关系，试试看，如Right(a2:a7,2)="01"。 
tNdYhdho.rar
(3.18 KB, 下载次数: 2293)
[em04][em15]
[此贴子已经被作者于2006-4-20 11:19:55编辑过] |
-
[求助]请问这个公式怎样理解，关于非重复、非空白单元格的？
![[求助]请问这个公式怎样理解，关于非重复、非空白单元格的？](https://files-c.excelhome.net/forum/dvbbs/2006-3/200631611395678190.jpg "[求助]请问这个公式怎样理解，关于非重复、非空白单元格的？")
评分
-
4
查看全部评分
-
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-3-15 10:38
楼主
发表于 2006-3-16 12:01
