经常有人问到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编程的高手不在乎这种小问题的
更多推荐
excel函数去重_Excel去除重复值方法汇总
发布评论