ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 Office知识技巧免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 21749|回复: 57

[分享]条条大路去重复

  [复制链接]

TA的精华主题

TA的得分主题

发表于 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楼
(未完,待续)










点评

知识树索引推荐内容位于:1-8楼  发表于 2013-10-15 16:27

评分

参与人数 11财富 +100 鲜花 +16 技术 +2 收起 理由
km122712 + 2 优秀作品
weiyingde + 2
好002 + 2 优秀作品
临时户口 + 2 优秀作品
99253415 + 2 值得肯定

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-12-13 22:32 | 显示全部楼层

方法一:辅助列各种函数法(countif或者match)(征用此楼)

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

在B2单元格写入公式
  1. =COUNTIF($A$2:$A2,A2)
复制代码

1-1.png

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

1-2.png

此时对B列进行筛选,条件选择为1,即可完成提取不重复值。
当然,此函数还可以换成其他类的函数,比如说:match 函数
在C2单元写入公式,并向下填充
  1. =MATCH(A2,$A$1:$A2,)=ROW()
复制代码

1-3.png

然后对TRUE进行筛选即可。

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

TA的精华主题

TA的得分主题

发表于 2012-12-13 22:34 | 显示全部楼层

方法二:直接使用数组函数提取法(index,indirect,offset等)(征用此楼)

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

这里就以index函数讲一下吧

2-1.png

在C2单元格内写入


  1. =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三个键)


  1. MATCH($A$2:$A$16,$A$1:$A$16,)
复制代码
目的:将原数据源中的数据进行标记上位置序号,只要是相同的数据将得到相同的序号


  1. MATCH($A$2:$A$16,$A$1:$A$16,)=ROW($A$2:$A$16)
复制代码
用到的序号与行号进行比较,(ROW函数将会得到一个行序列号),第一次出现的值是与ROW序列号是一致的,第二次出现的序号就会与ROW序列号不同,所以将会得到一个
  1. {TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}
复制代码


  1. 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指的是数据源后第一个空白的行号)


  1. 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()小的值。


  1. =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)))
复制代码
完成提取。
另再给几个数组函数进行参考
  1. =INDIRECT("a"&SMALL(IF(COUNTIF($C$1:C1,$A$2:$A$16),4^8,ROW($2:$16)),ROW($A$1)))&""
复制代码
  1. =INDEX($A$1:$A$16,MIN(IF(COUNTIF($D$1:D1,$A$2:$A$16),4^8,ROW($2:$16))))&""
复制代码
  1. =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)),,,)
复制代码

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

评分

参与人数 2鲜花 +4 收起 理由
好002 + 2 如果有附件下载,就好了。
cjsum + 2 值得肯定

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-14 08:06 | 显示全部楼层

方法三:数据透视表方法

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


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

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

3-2.png

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

3-3.png

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

点评

此坏处在应对大量数据时算好处了。  发表于 2014-5-13 20:03

TA的精华主题

TA的得分主题

发表于 2012-12-14 08:55 | 显示全部楼层

方法四:SQL方法(征用此楼)

本帖最后由 apirl2008 于 2012-12-14 23:05 编辑
  1. SELECT distinct 品名 FROM [方法四$A1:A16]
复制代码
第1步:点击数据----》现有连接----》弹开现有连接窗口----》点击浏览更多----》找到此工作簿文件----》点击确定

4-1.png
4-1-2.png
4-1-1.png

第2步:选择表格,选择数据源所在的工作表(方法四),单击确定

4-2.png

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

4-3-2.png

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

4-3-1.png


完成

4-4.png

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

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


点评

傻妞  发表于 2012-12-14 08:58

TA的精华主题

TA的得分主题

发表于 2012-12-14 09:48 | 显示全部楼层

方法五:高级筛选法(征用此楼)

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

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

5-1.jpg

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

3、复制到:
  1. sheet2!$C$1
复制代码

4、勾选选择不重复的记录
单击确定按钮,完成
5-2.jpg

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-14 15:32 | 显示全部楼层

方法六:VBA法(字典+数组)

本帖最后由 apirl2008 于 2012-12-14 17:10 编辑
  1. Sub 去重复()
  2. Dim arr, d, i '定义三个变量,arr为数组,d为字典对象,i为数值
  3. Set d = CreateObject("scripting.dictionary") '创建字典对象d,用于存放不重复数据
  4. arr = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row) '将A列有数据区域的值赋值给arr数组。
  5. For i = 1 To UBound(arr)  '循环数组,利用字典key不能重复特性保存不重复内容。
  6.   d(arr(i, 1)) = ""
  7. Next
  8. [c1].Resize(d.Count, 1) = Application.Transpose(d.keys)  '因为字典key保存的是横向数组,需要转置填充到C列单元格。
  9. Set d = Nothing '清空字典对象。
  10. End Sub
复制代码

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

6-1.jpg

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

6-2.jpg

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

6-3.jpg

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-14 15:33 | 显示全部楼层

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

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

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

7-1.jpg

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

7-2.jpg

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

7-3.jpg

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

TA的精华主题

TA的得分主题

发表于 2012-12-15 16:26 | 显示全部楼层
我建议多工作表或多列提取不重复值,代码法:添加纯数组法,一般代码法(即不用数组,字典,对象),对象法等等。。。

点评

嗯,空着的楼就是打算写多工作表提取不重复值,VBA菜鸟,不懂怎么写那些,多数用技巧与SQL了  发表于 2012-12-15 16:44

TA的精华主题

TA的得分主题

发表于 2012-12-15 16:49 | 显示全部楼层
李子谦虚了不是,你不会谁会呀?再说了,你不是有师傅和二锅吗?叫他们传授给你呀!!!期待你的佳作!!!

点评

俺师父就是二锅,嘿嘿  发表于 2012-12-15 21:25
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2020-7-11 05:42 , Processed in 0.101495 second(s), 21 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表