【辅助列解决实际问题_系列7】提取不重复值

cleverzhzhf

不重复值是一个永恒的话题，而且又是大家常用的，就因为此，微软从2007版本开始增加了一个直接的选项“删除重复项”，足见此功能的需求。
既然手动处理那么方便，为什么还要用函数呢？是否多次一举？

使用Excel的目的是什么？减少重复工作，能让系统一次性做好的，就尽量不要每次都要手动来处理，或者是减少手动处理的相应步骤。

三国公司8月份上旬各员工的销售业绩，现在要通过有销售额的员工，提取出来不重复的人员姓名，并且做一个数据有效性：

1. D4：=IFERROR(IF(MATCH(B4,B:B,0)=ROW(),ROW()),"")
   
2. E4：=IFERROR(INDEX(B:B,SMALL(D:D,ROW()-3)),"")

复制代码

数据有效性可供参考的三个方法：

1. =OFFSET($E$4,0,0,MATCH("",E:E,)-4)
   
2. =OFFSET($E$4,0,0,COUNTA($E$4:$E$30)-COUNTBLANK($E$4:$E$30))
   
3. =OFFSET($E$4,0,0,COUNTIF(E:E,"?*")-1)
   

复制代码

提取不重复值.zip (11.34 KB, 下载次数: 475)

2014-9-2 16:11 上传

点击文件名下载附件

---

一个公式得到E列的值，E4数组公式：

1. =IFERROR(INDEX(B:B,SMALL(IF(MATCH($B$4:$B$30,B:B,)=ROW($B$4:$B$30),ROW($B$4:$B$30)),ROW(1:1))),"")

复制代码

gvntw

不错，使用辅助列，以空间换时间。
COUNTA($E$4:$E$30)-COUNTBLANK($E$4:$E$30)可以用COUNTIF($E$4:$E$30,"?*")代替。

cleverzhzhf

首先要得到不重复数据：
第一步：
=IFERROR(IF(MATCH(B4,B:B,0)=ROW(),ROW()),"")
1、MATCH(B4,B:B,)=ROW()
这是一个比较经典的判断重复的方式
首先，通过MATCH，来判断C2的数字在C列第一次出现的位置
然后，用这个第一个出现的位置，与自己所在的行号作比较：
    如果相等：说明该数字，是在此列第一次出现
    如果不等：说明该数字，在靠上的位置，之前出现过，不是第一次出现
（相似使用方法可参考：[原创] 【辅助列解决实际问题_系列2】中国式排名 ）
2、IF(条件，ROW())
如果是第一次出现，则返回相应的行号
如果不是第一次出现，这里面用了一个技巧，省略IF的第三个参数，使得返回结果为FALSE。在数字比较当中，FALSE这种逻辑值是大于一切数字的，所以Small最后才会取到FALSE。
3、IFERROR(IF(条件，ROW()),"")
这里面就是为了容错。第一次可以把D列的辅助列做的很长很长，以后添加数据的时候，也不必每次还要改D列的尺寸。

第二步：
=IFERROR(INDEX(B:B,SMALL(D:D,ROW()-3)),"")
1、SMALL(D:D,ROW()-3)
从D列里面依次取最小值、次小值、再小值……，由于此公式写在了第4行，所以“-3”调整数值。根据个人喜好，也可以换成ROW(1:1)
2、INDEX(B:B,SMALL(D:D,ROW()-3))
通过Small取得相应的行号，使用Index进行简单的取值
3、IFERROR(INDEX,"")
这里面就是为了容错，当Small需要取到FALSE的时候会报错，所以强行返回空白即可

cleverzhzhf

数据有效性：
=OFFSET($E$4,0,0,MATCH("",E:E,)-4)
1、MATCH("",E:E,)
由公式返回的空白""，是可以进行精确匹配位置的。不过这里有一个弊端，当提取的不重复值那一列扩展的区域不够，有可能查不到""。
所以在使用的时候，一定需要知道自己的表格在做什么，需要什么数据。因为没有一成不变万能的方法，都可能需要根据细节来调整。
2、OFFSET($E$4,0,0,MATCH("",E:E,)-4)
通过MATCH可以知道第一个""的位置在哪，进而调整数值，得到有多少不重复的结果。
然后就是OFFSET的神奇扩展的功效了。

=OFFSET($E$4,0,0,COUNTA($E$4:$E$30)-COUNTBLANK($E$4:$E$30))
COUNTA($E$4:$E$30)-COUNTBLANK($E$4:$E$30)
由公式生成的空""，虽然眼睛看不到，但是Counta认识他，所以骗不过去。
而无论是“真空”，还是“公式生成的空”，CountBlank统统收入囊中。
这里使用这两个数值的差，便得到最终的结果。

=OFFSET($E$4,0,0,COUNTIF(E:E,"?*")-1)
（由gvntw版主提供）
COUNTIF(E:E,"?*")
这里面是巧妙的使用了通配符：
"?"，代表任意“1个”字符
"*"，代表任意“n个”字符，n大于等于0
"?*"，说明统计E列中，大于等于1个字符的单元格有多少个。而空""是不包含任何字符的，所以不在统计范围内。

sw=andy

前排旺铺招租！先顶再看！
好多人不喜欢辅助列，但辅助列确实可以大大降低工作难度。
顶！！！！！！

huangyongli

处理复杂的数据时，我一般都会用到辅助列（行），这样键盘上的F9键兴许能多用上几年

衣袂飘飘

路过，学习下。

first-hdy

学习受益了！谢谢！

winshaw

做个记号，收藏学习