|
经常有人问到excel中去除重复值的问题，这个问题本来也一直有很多人讨论，现将做法汇总出来共大家参考。 什么是重复值 顾名思义，重复值是指一组数据中有重复记录，去除这些重复的记录就叫去除重复值，具体有两种情况：源数据如图
去重后
结果一：所有在源数据中出现过二次及以上的记录均已经去除，结果二指，结果数据表示保留的数据均在源数据中有，但在结果中只出现一次 结果二就是我们常说的去重 去重方法常见的去重方法很多，本文列举自带工具去重、高级筛选去重、函数去重、VBA去重、透视表去重和SQL去重共六种方法 1. 利用excel自带去重工具去重 自excel2007以后，excel就增加了去重功能，具体看下图 操作步骤：选中需要去重的原始数据/点击数据选项卡/点击删除重复项/确定
2. 所有excel版本均可实现的办法：高级筛选 点击数据选项卡/点击排序和筛选组的高级按钮，弹出对话框 按上图设置后，确定即可。A1:A9系源数据，条件区域E1是一个空白单元格，关键点在于要勾选“选择不重复记录” 注意Excel97、XP及2003的高级筛选在数据菜单 以上两种办法是最常见的办法，高级筛选还可以跨表操作，但实际工作中，去重不但要跨表，还要能更新列表，比如源数据是不断更新的，去重结果也是需要不断更新的，所以前文的方法并不能带来多少实际的用途，自然，更先进的办法也就来了
3.函数去重 太多的人喜欢用函数处理数据，所以去重也少不了他的身影，请看截图 数据源在H1：H11区域，函数运算结果在I列，在I1单元格输入的函数公式：=INDEX(H:H,SMALL(IF(MATCH($H$1:$H$11,$H$1:$H$11,)=ROW($1:$11),ROW($1:$11),4^8),ROW(A1)))&""这是流传最广的函数写法之一 提示：数组公式要三键结束（ctrl+shift+enter），然后下拉填充至出现空格 公式翻译：I1单元格结果为index引用H列中的数据，数据排序号是数组按小排序的最小值1，该数组构成为 如果在H1：H11区域中来精确查找H1：H11值的序号等于行号时取行号，否则取当前行号和65536（4^8或者2^16，EXCEL最大行号值2003及以前版本，2007以后版本为1048576即2的10次方2^20） 上面太拗口，看公式解释： a. MATCH($H$1:$H$11,$H$1:$H$11,)是match的简写，标准写法为MATCH($H$1:$H$11,$H$1:$H$11,0)，意思是在H1:H11区域中查找H1:H11的值，并且精确匹配，其结果是{1;2;3;4;2;6;3;8;8;2;2}，可以解释为：H1:H11的出现顺序，从中可以看到第五个数据等于2，重复了，其余类似， b. MATCH($H$1:$H$11,$H$1:$H$11,)=ROW($1:$11)表示将{1;2;3;4;2;6;3;8;8;2;2}与H1:H11的行号比较（ROW是计算行号函数），得到的结果为{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE} c. 加上IF函数后，取值为{1;2;3;4;65536;6;65536;8;65536;65536;65536}， d. 再加上small函数，数组变成{1;2;3;4;6;8;65536; 65536;65536;65536;65536} e. 最好，index按顺序取值并连上””[&""]|排除错误或零值，得到结果 f. 当源数据区域发生变化，对应修改函数的取值区域后，三键结尾，下拉公式
4.VBA代码去重 用excel的VBA编程也可解决去重问题，最常使用的方法有以下 a. ActiveSheet.Range("$H$1:$H$11").RemoveDuplicatesColumns:=1, Header:=xlNo这是VBA的RemoveDuplicates删除重复记录方法 b. Range("H1:H11").AdvancedFilterAction:=xlFilterCopy, CopyToRange:=Range("X1"), Unique:=True 这是利用的VBA筛选去重 c. 用VBA字典看示例 以下三个来源于论坛，非原创 示例1（可以添加三列记录到字典的） Sub Test() Dim i&, DicA As Object, DicB As Object, DicC As Object Dim Arr1, Arr2, Arr3 Set DicA = CreateObject("scripting.dictionary") Set DicB = CreateObject("scripting.dictionary") Set DicC = CreateObject("scripting.dictionary") Arr1 = Range("A1:A60000") Arr2 = Range("B1:B60000") Arr3 = Range("C1:C60000") For i = LBound(Arr1) To UBound(Arr1) '循环数组Arr1 DicA(Arr1(i, 1)) ="" '添加不重复值到字典DicA Next i For i = LBound(Arr2) To UBound(Arr2) '循环数组Arr2 If DicA.exists(Arr2(i, 1))Then '如果Arr2中的值存在于字典DicA中，则往字典DicB添加重复的值 DicB(Arr2(i, 1)) ="" End If Next i For i = LBound(Arr3) To UBound(Arr3) '循环数组Arr3 If DicB.exists(Arr3(i, 1))Then '如果Arr3中的值存在于字典DicB中，则往字典DicC中添加重复的值 DicC(Arr3(i, 1)) ="" End If Next i Range("D1").Resize(DicC.Count, 1) =Application.Transpose(DicC.keys) '把DicC的keys值赋予给D列 Set DicA = Nothing Set DicB = Nothing Set DicC = Nothing End Sub 类似的，一个入库单汇总的小程序 示例2 SubDataWrtin() Dim Arr, k%, str$ Dim Ary, i%, icl% Dim Dic As Object Dim Sh As Worksheet
Set Sh = Sheets("入库单数据库") Set Dic =CreateObject("Scripting.Dictionary") Arr =Sh.Range("E5", Sh.[E65536].End(3)(1, 3))” Ary = Arr i = 0 For k = 1 To UBound(Arr) str =Join(Application.Index(Arr, k), " ") If Not Dic.exists(str)Then Dic(str) ="" i = i + 1 For icl = 1 To 3 Ary(i, icl) =Arr(k, icl) Next End If Next Dic.RemoveAll
Sheets("目录").[A5].Resize(i,3) = Ary End Sub E5请做相应修改 简单适用的B列元数据，E列去重结果 示例3 Sub 筛选不重复数据() Set dic = CreateObject("Scripting.Dictionary") '字典 For Each r In Sheets("Sheet1").Range("b2:b"& Sheets("Sheet1").[b65536].End(xlUp).Row) '数组 On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息 If Not r.Value ="" Then dic.Add r.Value, "" '如果不是空，给字典添加内容 Next Sheets("Sheet2").Range("e2").Resize(dic.Count, 1) =Application.WorksheetFunction.Transpose(dic.keys) '从e2单元开始向下放 On Error GoTo 0 '恢复正常的错误提示 End Sub 用VBA也比较麻烦，而且要求有一定的编程基础，所以接下来来跟容易的
5.数据透视 点插入选项卡/数据透视表，按图设置 把姓名拖入行标签中，右键透视表/透视表选项，取消列总计复选框，确定 得到结果
当据源发生变化时，右键透视表，刷新即可更新列表 这是最简单的能更新的办法
6.SQL联合查询去重 利用SQL语句的去重功能实现，也能更新数据 点数据选项卡/现有连接，出现如图 点击浏览更多按钮，按资源管理器方式找到本excel文件并点击打开 选中sheet1，确定 点击属性按钮，打开属性对话框 点击定义选项卡 删除命令文本里面的内容，写入select distinct 姓名from [sheet2$]语句 注意：除中文外，空格及标点均为英文小写状态输入，]后没有内容，点击确定 选择放置结果的起始单元格，确定 完成 需要更新时，右击结果，刷新即可
前述六种办法，一般情况下推荐使用第一二办法，需要经常更新时，建议采用第五第六种办法，使用数组函数在数据太多时电脑运行缓慢，不推荐，至于VBA，呵呵 ，能用VBA编程的高手不在乎这种小问题的 |
|
|
