用VBA统计重复数据

在一个多行多列的区域中，有大量的数据，其中许多是重复的。如果想显示出各数据的重复出现次数，是一件比较麻烦的事情，如果数据都分布在同一列中还好办，在多列中就不方便了。

 

一般的做法是把所有列中的数据分别复制，再分别粘贴到同一个列中，依次向下排列，然后对这一列使用“高级筛选－选择不重复记录”，再用COUNTIF函数统计重复次数。

 

如果使用一段VBA代码来做，就会非常快捷，一瞬间就会将统计结果显示出来。

步骤/方法

第一步：准备数据

下面是一个例子，假设数据在 Sheet1 工作表中：

 

例中只用了四列数据，实际使用时可对更多的列进行操作，只要在代码中改变一个变量的值就行了。

 

用鼠标右键点当前工作表下面的标签名 ，选右键快捷菜单中的“查看代码”。

 

第二步：编写代码

这时就弹出VBA窗口，将下面代码复制后，粘贴到VBA窗口中，点击“执行”后即可得到统计结果。

 

Sub 统计()

 

y1 = 1 '开始列为A列（在EXCEL中，A列的列号为1）

 

y2 = 4 '结束列为D列（在EXCEL中，D列的列号为4）

 

x = 2

 

n1 = 255 '辅助列

 

n2 = y2+2 '结果显示列，结果显示在源数据列的右侧，中间间隔一列。

 

For i = y1 To y2

 

s = Cells(65536, y1).End(xlUp).Row '各列数据的数量

 

Range(Cells(1, i), Cells(s, i)).Copy Cells(x, n1) '把所有数据复制到辅助列中

 

x = x + s

 

Next

 

Cells(1, n1) = "数据": Cells(1, n2 + 1) = "次数"

 

'使用“高级筛选”功能将不重复数据显示在“结果显示列”中

 

Columns(n1).AdvancedFilter 2, , Cells(1, n2), 1

 

s1 = Cells(65536, n2).End(xlUp).Row

 

'下面代码用COUNTIF函数统计重复次数

 

For i = 2 To s1

 

Cells(i, n2 + 1) = WorksheetFunction.CountIf(Columns(n1), Cells(i, n2))

 

Next

 

'消除辅助列内容

 

Columns(n1) = ""

 

End Sub

 

代码中的含义已经做了批注，大致意思是：将源数据复制到一个辅助列中，对这个辅助列使用高级筛选，得到不重复的数据，使用COUNTIF函数进行统计计数，得到各数据的重复出现次数，最后删除辅助列内容。

 

上面代码的执行方法分两种情况，一种是在EXCEL窗口执行，另一种是在VBA窗口执行。

 

在EXCEL窗口时，以EXCEL2003版为例，按“工具－宏－宏”，会出现宏窗口对话框，在其中选择“统计”宏（就是我们的代码名字），确定即可执行。还可给这个宏指定一个快捷键，通过快捷键来执行代码。

 

在VBA窗口时，可用鼠标点击工具栏上的执行按钮（上图红圈中的向右三角按钮），也可点击工具栏上的“运行”，选“运行子过程”。还可按键盘最上方的F5功能键来快速执行代码。

下图是运行代码后的效果：