Excel非重复、非空白单元格提取

我们来看下面一道例子： 
  yy01
yy01 xx01
xx01 yy02
yy01 xx02
yy02 　
xx02 　
xx02  
   
数据 结果
 以上黄色单元格区域中公式该如何理解：1.x=IF(Sheet1!$A$2:$A$7<>"",IF(MATCH(Sheet1!$A$2:$A$7,Sheet1!$A$2:$A$7,0)=ROW(Sheet1!$A$2:$A$7)-1,ROW(Sheet1!$A$2:$A$7)))   =IF(ROW(2:2)>COUNT(x),"",INDEX(A:A,SMALL(x,ROW(2:2))))2.如果起始行不是A2,而是A1，公式应该怎样改？3 B列的公式能实现挑出非重复、非空白的单元的功能。如果要到达C列的效果，即只挑出非空白的单元格，应该怎样实现？分析： 
①   ②   ③   ④
     
MATCH()   ROW()   IF()……   X
1   1   2   2
2   2   3   3
1   3   FALSE   FALSE
4   4   5   5
5   5   6   6
5   6   FALSE   FALSE
             
⑤   ⑥   ⑦    
       
SMALL()   INDEX()   IF()    
2   yy01   yy01    
3   xx01   xx01    
5   yy02   yy02    
6   xx02   xx02    
#NUM!   #NUM!   　    
#NUM!   #NUM!   　    
 为了叙述的方便，为了减少解释的难度，我把数据改了，改成没有空白的数据了，相信看完后还是能够处理它的。提取不重复值，我习惯建立一个数组，并把它定义为一个名称，还习惯用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)，最后结果是一个数组(见①)。列中有两个yy01，两个yy01在指定区域中第一次出现的位置是相同的，都是1，我们要的就是这种效果。      二。Row() ==>返回一个引用的行号。判断了每一个数据在区域中首次出现的位置，接着再判断每一个数据在区域中所处的位置，用函数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()，我们现在研究的数据是在同一列，因此，公式可以用：Index(A:A,行号)返回结果，如果数据在D列，则： Index(D:D,行号)这个行号，我们用Small()函数返回，在帮助中我们可以看到，Small()是“返回数据集中第 k 个最小值。”，这里，数据集是我们刚才定义的X： Small(X,1) ==>X 中第1个最小值 Small(X,2) ==>X 中第2个最小值就这个意思。关于Small(X,Row(1:1))这里的参数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"。资料来自: 中国自学网(Www.CnZiXue.Com) 详细出处参考：http://www.cnzixue.com/html/office/excel/Function/2010/0626/7167.html