如何使用 VBA 计算重复值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14896188/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count repeated values using VBA
提问by user2075655
I am new to VBA and I am trying to find the number of times the same values are repeated in particular column.
我是 VBA 的新手，我试图找出特定列中相同值重复的次数。
Then I need to paste the value and the count of the repeated value in column A and B of another sheet.
然后我需要将值和重复值的计数粘贴到另一张纸的 A 列和 B 列中。
I need to count the number of times repeated values:
我需要计算重复值的次数：
PSV_Cust_1
PSV_Cust_1
PSV_Cust_1
PSV_Cust_1
PSV_Cust_1
PSV_Cust_2
PSV_Cust_2
PSV_Cust_2
PSV_Cust_2
PSV_Cust_3
PSV_Cust_3
PSV_Cust_3
PSV_Cust_3
PSV_Cust_4
PSV_Cust_4
PSV_Cust_4
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
PSV_Cust_5
Result should be:
结果应该是：
Value Count
PSV_Cust_1 5
PSV_Cust_2 4
PSV_Cust_3 4
PSV_Cust_4 3
PSV_Cust_5 7
Please any one help to get the outpu using VBA code.
请任何人帮助使用 VBA 代码获取输出。
回答by StoriKnow
As others have suggested, a Pivot Tablewould be the simplest way to accomplish this. Here's how:
正如其他人所建议的那样， aPivot Table将是实现此目的的最简单方法。就是这样：
1)Select the rows you want to count
2)Choose
Insert -> PivotTablefrom the ribbon3)A window will appear, click
Okto create your pivot table:
1）选择要计算的行
2)
Insert -> PivotTable从功能区中选择3)将出现一个窗口，单击
Ok以创建您的数据透视表：
4)On the right under "PivotTable Field List: Choose fields to add to report:" click the checkbox next:
4）在右侧的“数据透视表字段列表：选择要添加到报告的字段：”下单击复选框：
5)Now drag the checkbox field you just clicked down to the "Values" list and let go:
5）现在将您刚刚单击的复选框字段向下拖动到“值”列表并放开：
6)That's it! You will now have what you asked for:
6）就是这样！您现在将拥有您所要求的：
回答by Peter Albert
This macro will do what you need:
这个宏会做你需要的：
Sub Summarize(rngSource As Range, rngTarget As Range)
Dim d As New Scripting.Dictionary
Dim rng As Range
Dim var As Variant
For Each rng In rngSource
If rng <> "" Then
If d.Exists(rng.Value) Then
d(rng.Value) = d(rng.Value) + 1
Else
d.Add rng.Value, 1
End If
End If
Next rng
rngTarget = "Value"
rngTarget.Offset(, 1) = "Count"
Set rng = rngTarget.Offset(1)
For Each var In d.Keys
rng = var
rng.Offset(, 1) = d(var)
Set rng = rng.Offset(1)
Next
End Sub
You need to add a reference to the Microsoft Scripting Libraryin the Visual Basic Editor (Tools->References). You can call it like this:
您需要在 Visual Basic 编辑器（工具->引用）中添加对Microsoft 脚本库的引用。你可以这样称呼它：
Summarize Sheet1.Range("A1:A24"), Sheet1.Range("C1")
回答by Floris
The COUNTIF(range, value) function will do what you want - it has the advantage that you can more easily constrain the values you want to search for (compared to a pivot table). Imagine your data is in range "Sheet1!A1:A25" which you have named "customers" (you do this by selecting the range and typing customersin the address box to the left of the formula bar).and contains "customer 1, customer 2... through customer 10", and you only want to know how many times customers 1 and 5 appear, you can create the following on sheet2
COUNTIF(range, value) 函数将执行您想要的操作 - 它的优点是您可以更轻松地限制要搜索的值（与数据透视表相比）。想象一下您的数据在您命名为“客户”的范围“Sheet1！A1：A25”中（您可以通过选择该范围并customers在公式栏左侧的地址框中键入来完成此操作）。并且包含“客户 1，客户2...通过客户10"，你只想知道客户1和5出现了多少次，你可以在sheet2上创建以下内容
col A col B
Value count
customer 1 =COUNTIF(customers, A1)
customer 5 =COUNTIF(customers, A2)
Of course you can just drag the formula down from cell B1 - you don't need to type it in again.
当然，您可以将公式从单元格 B1 中向下拖动 - 您无需再次输入。
This will count the customers, update automatically, ... I think it's easier than a pivot table in this example.
这将计算客户，自动更新，......我认为在这个例子中它比数据透视表更容易。
