加载中…
Excel单列数据去重 提取唯一值列表 不重复值提取
(2015-07-03 14:30:50)
标签：
杂谈 |
分类： Excel |
方法一：高级筛选法，适用于所有版本的excel，步骤多，稍显麻烦
单击“数据”选项卡--》“排序和筛选”功能区--》“高级”按钮，弹出“高级筛选”对话框，如下图所示：
选中“将筛选结果复制到其他位置”，选择“列表区域”和“复制到”的位置，同时勾选“选择不重复的记录”复选框，如下图所示，单击“确定”就完成了重复值的筛选。
如下图，是本次操作的源数据。
单击“数据”选项卡--》“数据工具”功能区--》“删除重复项”，弹出“删除重复项”对话框，单击“确定”即可删除单列数据
方法三：数据透视表法，适用于大批量数据，但是局限在于只能通过过渡方式，对于有些想直接处理的数据不便
依然使用上面的数据源，单击“插入”选项卡--》“表格”功能区--》“数据透视表”，出现如下图的提示框，这里我选择现有工作表
接着，勾选“姓名”前面的复选框，“姓名”字段就出现在《行》字段的框框里，如下图：
然后，我们对数据透视表的数据进行修饰，单击“行标签”所在的单元格，将单元格的文字改成“姓名”，然后单击数据透视表内的任意单元格，单击“数据透视表工具”--》“设计”选项卡--》“布局”功能区--》“总计”下的“对行和列禁用”按钮就完成了，如下图所示：
方法四：公式法
4.1.{=INDEX($A$1:$A$30,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$30),0))&""}
1）当没有标题行时，Excel会提示循环引用，计算结果不对；开启“允许迭代计算”可以解决这个问题（默认情况下是关闭的）
2）在不开启迭代计算的模式下，添加标题行可有效规避这个问题，公式要修改为如下：
{=INDEX($A$1:$A$31,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$31),0))&""}
公式解析：
-
COUNTIF($B$1:B1,$A$1:$A$31)，将countif函数的range和criteria换位思考，即用目标列表的值在结果列表中查找，得出个数，{0;0;0........;0;0;1}的数组，这里之所以31比目标列多一行是为了避免当目标全部为唯一值时match函数出错，即到最后一行查找0时，还有31行满足条件；
-
MATCH(0,？,0))，在第一步的数组里查找0所在的位置，返回第一个0值，精确返回；
-
INDEX($A$1:$A$30,？)，返回序列里第几行的数据，第三参数列号默认则等于1；
-
&“”是为了当前面的值返回第31行的空值时，让单元格不显示错误信息
4.2
{=IFERROR(INDEX(A:A,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($1:$12),ROW($2:$13)),ROW(1:1))),"")}
D:\Useful提取唯一值列表
公式解析：
-
MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($1:$12)，{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}，找出每个值在序列中对应出现的位置
-
IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($1:$12),ROW($2:$13))，{2;3;4;5;FALSE;7;FALSE;9;FALSE;11;FALSE;13}，找出为true的值即唯一值在序列中的位置
-
SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($1:$12),ROW($2:$13)),ROW(1:1))，{2}，按顺序找到对应的行号
-
INDEX(A:A,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($1:$12),ROW($2:$13)),ROW(1:1)))，32465，返回行号所对应的序列值
-
IFERROR(INDEX(A:A,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0)=ROW($1:$12),ROW($2:$13)),ROW(1:1))),"")，容错
如图，在C1单元格输入如下公式，然后同时按Ctrl+Shift+Enter三个键结束，接着拖动输入公式单元格右下角的填充柄，完成此次不重复数据的筛选。
接着我们对公式进行逐步的讲解，首先，MATCH($A$1:$A$10,$A$1:$A$10,)=ROW($1:$10)表示查找A1至A10单元格在引用区域$A$1:$A$10的位置是否等于当前单元格行号所在的位置，如果相等，则说明该数据在这个区域中唯一，然后通过IF(MATCH())组合函数返回这个字段的行号，否则返回2^20=1048576，接着用SMALL函数对获取的行号进行升序排序，最后通过INDEX函数查找行号所在位置的值，&“”主要是为了容错处理，试想，如果数据都取完了，就剩下1048576的位置了，然后INDEX(A:A,1048576)=0，加个&“”则返回空文本。
4.3
{=INDEX(A:A,MIN(IF(COUNTIF(C$1:C1,$A$2:$A$13),2^20,ROW($2:$13))))&""}
2^20=1048576
D:\Useful提取唯一值列表
方法五：VBA法
5.1
感觉与我常用的数组去重是一样的（我是通过两层循环判断比较，重复的清为空值，但是我的做法中，对于后面遇到的空值会跳过，提高运行效率，楼主的，在最后几个单元格会无意义的跑空……），不过，巧妙的是利用了单元格删除后，后面单元格自动上移一格的特点，在内存数组中相当于是把元素前赶了一个位置，如此，不需要另行清除空值了……
-
Sub 矩形1_Click() -
i = Range("A65536").End(xlUp).Row -
For s = 1 To i -
For ss = i To s + 1 Step -1 -
If Cells(ss, 1) = Cells(s, 1) Then -
Cells(ss, 1).Delete shift:=xlUp -
End If -
Next ss -
Next s -
End Sub
-
Sub 矩形2_Click() -
i = Range("A65536").End(xlUp).Row -
For s = i To 1 Step -1 -
If Application.WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(s, 1)), Cells(s, 1)) > 1 Then -
Cells(s, 1).Delete shift:=xlUp -
End If -
Next -
End Sub
-
Sub 矩形3_Click() -
i = Range("A65536").End(xlUp).Row -
Dim dic As Object, ii&, arr, ra -
Set dic = CreateObject("Scripting.Dictionary") -
arr = Range("a1:a" & i) -
For ii = 1 To UBound(arr) -
ra = dic(arr(ii, 1)) -
Next -
Range("a:a").ClearContents -
Range("a1").Resize(dic.Count, 1) = Application.Transpose(dic.Keys) -
End Sub
-
Sub 矩形3_Click() -
i = Range("A65536").End(xlUp).Row -
Dim dic As Object, ii&, arr -
Set dic = CreateObject("Scripting.Dictionary") -
arr = Range("a1:a" & i) -
For ii = 1 To UBound(arr) -
dic(arr(ii, 1)) = ii -
Next -
Range("a:a").ClearContents -
Range("a1").Resize(dic.Count, 1) = Application.Transpose(dic.Keys) -
End Sub
-
Sub 矩形4_Click() -
Columns(1).RemoveDuplicates 1 -
End Sub
“Excel 开发人员参考
Range.RemoveDuplicates 方法
从值区域中删除重复的值。
版本信息
已添加版本：
语法
表达式.RemoveDuplicates(Columns, Header)
表达式
参数
名称 必选/可选 数据类型 说明
Columns 可选 Variant 包含重复信息的列的索引数组。如果没有传递任何内容，则假定所有列都包含重复信息。
Header 可选 XlYesNoGuess 指定第一行是否包含标题信息。xlNo 是默认值；如果希望 Excel 确定标题，则指定 xlGuess。
”
VBA去重复只有两种方法：
① 整数数值型数据，可以直接使用数组下标去重复
② 通用方法：字典去重复
其它的方法都没啥意思。给你整理个数组循环去重复排序的自定义过程吧，
以后凡是VBA内的一维数组排序，都可以直接拿去用：
-
Sub RecSortTest() -
arr = Array(5, 4, 2, 1, 5, 8, 7, 2, 7, 9, 3, 6, "22", "23", "221", 22, 23, 221, "a", "z", "c") '测试数组 -
' arr = WorksheetFunction.Transpose([a1].CurrentRegion) '如果工作表区域要转为一维数组 -
trr = RecSort(arr) '仅排序(按默认格式) -
trr1 = RecSort(arr, 1) '去重复排序(按默认格式) -
trr2 = RecSort(arr, 1, 1) '去重复排序 数值不按文本格式 -
Stop -
End Sub -
Function RecSort(arr, Optional z& = 0, Optional c& = 0) 'A-Z 升序排序(/可去重复)的自定义过程 -
Dim i&, j&, k&, l&, n&, u&, t -
l = LBound(arr): n = l: u = UBound(arr) -
ReDim trr(l To u) -
For i = l To u -
t = arr(i): If c Then If IsNumeric(t) Then t = Val(t) 'c=1 按数值/c=0 按源数据格式 -
For j = l To n -
If z Then If trr(j) = t Then n = n - 1: Exit For 'z=1 去重复/z=0 保留 -
If trr(j) > t Then '检查直到比当前值t大位置时停止 -
For k = n To j + 1 Step -1 '倒序向后移动所有比当前值大的已排序内容 以便腾出空位 -
trr(k) = trr(k - 1) -
Next -
trr(k) = t '空位写入t -
Exit For -
End If -
Next -
If j > n Then trr(j - 1) = t '如果都没有比当前值大 则在最后新的位置写入t -
n = n + 1 -
Next -
If z Then ReDim Preserve trr(l To n - 1) -
RecSort = trr -
End Function
这个代码是用了比较插入排序算法，但已由我做了改进。
如果需要降序排序，把比较部分语句中>改成<即可。
If trr(j)
If trr(j)
方法六：SQL法
单击“数据”选项卡--》"获取外部数据"功能区--》现有连接，打开“现有连接”对话框，单击左下角的“浏览更多”，然后找到你操作数据源所在的工作簿的路径，单击“打开”，出现“选择表格”对话框，保持默认状态，单击“确定”按钮，如下图所示：
接着我们选择“表”单选按钮，这里我选择现有工作表的C1单元格(大家根据需要可以选择新工作表)，单击“属性”按钮，打开"连接属性"对话框，找到“定义"选项卡，然后清除”命令文本“
喜欢
0
赠金笔