EXCEL中提取不重复的数据

EXCEL中提取不重复的数据

利用EXCEL的高级筛选可以从一列中提取出不重复的数据，但是EXCEL的这一功能无法对多列单元格进行操作。本方法提供的VBA脚本可以实现从多个单元格区域中提取不重复数据的功能。

激活要操作的工作表，右键单击宋戴工作表名称，在弹出的快捷菜单中点击“查看代码”，在弹出的窗口中贴入以下代码：

Sub 提取不重复的数据()

Dim rngSrc As Range, rngDes As Range, rng As Range, rngPt As Range

Set rngSrc = Application.InputBox("请选择提取区域", "提示", , , , , , 8)

If rngSrc.Columns.Count > rngSrc.Parent.UsedRange.Columns.Count Then

Set rngSrc = rngSrc.Parent.Range(rngSrc.Item(1, 1), rngSrc.Item(rngSrc.Rows.Count, rngSrc.Parent.UsedRange.Columns.Count))

End If

排搁 If rngSrc.Rows.Count > rngSrc.Parent.UsedRange.Rows.Count Then

Set rngSrc = rngSrc.Parent.Range(rngSrc.Item(1, 1), rngSrc.Item(rngSrc.Parent.UsedRange.Rows.Count, rngSrc.Columns.Count))

End If

Set rngDes = Application.InputBox("请选择数据保存区域", "提示", , , , , , 8)

Set rngDes = rngDes.Item(1, 1)

Set rngPt = rngDes

Application.ScreenUpdating = False

On Error Resume Next

For Each rng In rngSrc

If rng <> "" Then '跳过空白单元格

If Application.WorksheetFunction.CountIf(rngDes, rng.Value) = 0 Then

rngPt.Value = rng.Value

Set rngDes = Union(rngDes, rngPt)

择称率 Set rngPt = rngPt.Offset(1)

End If

End If

Next rng

rngDes.Parent.Activate

rngDes.Select

End Sub

运行宏“提取不重复的数据”，按提示操作即可得到结果。可以同时选择多个区域。如何运行宏，及宏安全设置，请另行百度。