[分享]条条大路去重复

apirl2008 · 发表于 2012-12-13 22:24

本帖最后由 apirl2008 于 2013-4-25 13:13 编辑

从2010年1月16日开始注册EH至目前为止已经为时快要3年了，从当年的小菜鸟到现在却从未写分享，这个构思也是在大脑里想了很久，今天终于付于笔端，如果有讲错的地方还请大家指正，谢谢。
apirl2008-李子（2012年12月13日22：37写于开始前）
在众多茫茫数据中，想要找到那个唯一的数据，那怎么办？俗话说的好，条条大路通罗马，总有一条大路是可以被你所接受的。
单列数据提取不重复值
如图所示数据源：

标题-1.png

要求：提取A列不重复值

方法一：辅助列各种函数法（countif或者match)--------------------------------------------------------------------2楼

方法二：直接使用数组函数提取法（各类的index,indirect,offset,vlookup,lookup等）----------------------------3楼

方法三：数据透视表方法----------------------------------------------------------------------------------------------4楼

方法四：SQL方法------------------------------------------------------------------------------------------------------5楼

方法五：高级筛选法---------------------------------------------------------------------------------------------------6楼

方法六：VBA法--------------------------------------------------------------------------------------------------------7楼

方法七：2007以上EXCEL中自带的删除重复项----------------------------------------------------------------------8楼
（未完，待续）

oldyuan · 发表于 2012-12-13 22:32

本帖最后由 apirl2008 于 2012-12-14 21:26 编辑

在B2单元格写入公式

=COUNTIF($A$2:$A2,A2)

复制代码

并向下填充，此函数利用countif第1参数的区域在不断的变化，使得得到的结果是第1次出现的值标记为1，第2次出现的值标记为2等

此时对B列进行筛选，条件选择为1，即可完成提取不重复值。
当然，此函数还可以换成其他类的函数，比如说：match 函数
在C2单元写入公式，并向下填充

=MATCH(A2,$A$1:$A2,)=ROW()

复制代码

然后对TRUE进行筛选即可。

好处：简单快速，不需要太多的思考，就可以完成了不重复值的筛选
坏处：不够自动化，如果需要复制到其他区域，如果数据源发生了变化，就需要重新进行筛选与复制，粘贴

杨小军 · 发表于 2012-12-13 22:34

本帖最后由 apirl2008 于 2012-12-14 22:00 编辑

这里就以index函数讲一下吧

在C2单元格内写入

=INDEX($A$1:$A$17,SMALL(IF(MATCH($A$2:$A$16,$A$1:$A$16,)=ROW($A$2:$A$16),ROW($A$2:$A$16),17),ROW(A1)))

复制代码

三键结束（在编辑状态时，同时按下键盘上的ctrl,shift,enter三个键)

MATCH($A$2:$A$16,$A$1:$A$16,)

复制代码

目的：将原数据源中的数据进行标记上位置序号，只要是相同的数据将得到相同的序号

MATCH($A$2:$A$16,$A$1:$A$16,)=ROW($A$2:$A$16)

复制代码

用到的序号与行号进行比较，（ROW函数将会得到一个行序列号），第一次出现的值是与ROW序列号是一致的，第二次出现的序号就会与ROW序列号不同，所以将会得到一个

{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

复制代码

IF(MATCH($A$2:$A$16,$A$1:$A$16,)=ROW($A$2:$A$16),ROW($A$2:$A$16),17)

复制代码

无处不在的if函数啊，很神奇的将TURE与FALSE进行区分，只要是TURE那么得到的值就会是ROW序列号，FALSE得到的值就是17（这里的17指的是数据源后第一个空白的行号）

SMALL(IF(MATCH($A$2:$A$16,$A$1:$A$16,)=ROW($A$2:$A$16),ROW($A$2:$A$16),17),ROW(A1))

复制代码

small函数将得到的值进行从小至大排序并提取ROW（）小的值。

=INDEX($A$1:$A$17,SMALL(IF(MATCH($A$2:$A$16,$A$1:$A$16,)=ROW($A$2:$A$16),ROW($A$2:$A$16),17),ROW(A1)))

复制代码

完成提取。
另再给几个数组函数进行参考

=INDIRECT("a"&SMALL(IF(COUNTIF($C$1:C1,$A$2:$A$16),4^8,ROW($2:$16)),ROW($A$1)))&""

复制代码

=INDEX($A$1:$A$16,MIN(IF(COUNTIF($D$1:D1,$A$2:$A$16),4^8,ROW($2:$16))))&""

复制代码

=OFFSET($A$1,SMALL(IF(MATCH($A$2:$A$16,$A$1:$A$16,)=ROW($A$2:$A$16),ROW($A$1:$A$15),16),ROW($A1)),,,)

复制代码

好处：自动化的提取，随着数据源的变化而变化
坏处：在应对大量的数据的时候，容易假死

apirl2008 · 发表于 2012-12-14 08:06

本帖最后由 apirl2008 于 2012-12-15 09:09 编辑

步骤1、单击数据中的任意单元格----》插入----》数据透视表

步骤2、设置数据透视表，如下图所示

步骤3、将品名拖至行标签，即可完成了不重复值的提取

好处：自动化的提取，随着数据源的变化而变化
坏处：需要不断的刷新数据透视表（不过此坏处可以使用VBA自动刷新改善）

大灰灰 · 发表于 2012-12-14 08:55

本帖最后由 apirl2008 于 2012-12-14 23:05 编辑

SELECT distinct 品名 FROM [方法四$A1:A16]

复制代码

第1步：点击数据----》现有连接----》弹开现有连接窗口----》点击浏览更多----》找到此工作簿文件----》点击确定

第2步：选择表格，选择数据源所在的工作表（方法四)，单击确定

第3步：写入SQL代码，点击属性按钮----》弹开连接属性窗口----》点击定义----》在命令文本中写入代码----》点击确定按钮

勾选表的复选项----》确定放置位置为：方法四工作表的B1单元格为启始----》单击确定

完成

注释：
在SQL中，SELECT与FROM联合使用指的是从（FROM）工作表区域为：方法四工作表中的A1：A16区域中查找品名；
DISTINCT：用来过滤掉重复记录，只保留一条。

好处：自动化的提取，随着数据源的变化而变化
坏处：对数据类型要求比较高，比如说数值型，文本型，日期型

YFZDH767 · 发表于 2012-12-14 09:48

本帖最后由 apirl2008 于 2012-12-14 17:14 编辑

依次单击数据----》排序和筛选----》高级

在弹开的高级筛选窗口中进行相应的设置
1、勾选将筛选结果复制到其他位置
2、列表区域：

$A$1:$A$16

复制代码

3、复制到：

sheet2!$C$1

复制代码

4、勾选选择不重复的记录
单击确定按钮，完成

好处：简单快速，就可以完成了不重复值的提取
坏处：不够自动化，如果数据源发生了变化，就需要重新进行高级筛选，可以配合录宏进行改善

apirl2008 · 发表于 2012-12-14 15:32

本帖最后由 apirl2008 于 2012-12-14 17:10 编辑

Sub 去重复()
Dim arr, d, i '定义三个变量，arr为数组，d为字典对象，i为数值
Set d = CreateObject("scripting.dictionary") '创建字典对象d，用于存放不重复数据
arr = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row) '将A列有数据区域的值赋值给arr数组。
For i = 1 To UBound(arr) '循环数组，利用字典key不能重复特性保存不重复内容。
d(arr(i, 1)) = ""
Next
[c1].Resize(d.Count, 1) = Application.Transpose(d.keys) '因为字典key保存的是横向数组，需要转置填充到C列单元格。
Set d = Nothing '清空字典对象。
End Sub

复制代码

打开VB代码窗口
（各种方法打开。。。
方法1、在工作表标签处单击右键，点击查看代码
方法2、alt+F11
方法3、依次单击开发工具----》控件组----》查看代码）
在代码窗口写入代码，如下图所示

返回活动工作表中，插入表单控件，并指定宏代码

将表单控件名称修改为“去重复”，单击按钮，完成提取

好处：简单快速，随着数据源的改变点击按钮而改变
坏处：对于初学者不懂VBA人来说，写代码将是一个痛苦的过程

apirl2008 · 发表于 2012-12-14 15:33

本帖最后由 apirl2008 于 2012-12-14 17:25 编辑

单击数据----数据工具----》删除重复项，如下图所示

在弹开的删除重复项窗口中保留“品名”的勾选项，单击确定

Excel将会弹开一个提示窗口，告之您发现了9个重复值，并已经清除，单击确定完成去掉重复项。

好处：简单快速，不需要太多的思考，就可以完成了重复值的删除
坏处：只能在2007及以上版本中使用，并对数据源直接进行了删除，可以在操作之前进行先备份。

ctp_119 · 发表于 2012-12-15 16:26

我建议多工作表或多列提取不重复值，代码法:添加纯数组法，一般代码法（即不用数组，字典，对象），对象法等等。。。

ctp_119 · 发表于 2012-12-15 16:49

李子谦虚了不是，你不会谁会呀？再说了，你不是有师傅和二锅吗？叫他们传授给你呀！！！期待你的佳作！！！

		自动登录	找回密码
密码			免费注册

[分享]条条大路去重复

评分

方法一：辅助列各种函数法（countif或者match)（征用此楼）

方法二：直接使用数组函数提取法（index,indirect,offset等）（征用此楼）

评分

方法三：数据透视表方法

点评

方法四：SQL方法（征用此楼）

点评

方法五:高级筛选法（征用此楼）

方法六：VBA法（字典+数组）

方法七:2007以上EXCEL中自带的删除重复项

点评

点评

浏览过的版块