计算包含文本的唯一值的数量

I have the following code that counts the number of cells in a column that contains the string, "ABC-QR":

我有以下代码来计算包含字符串“ABC-QR”的列中的单元格数:

Ctr = Application.WorksheetFunction.CountIf(Sheet1.Range("D4:D1500"), "*ABC-QR*")
EU.Cells(16, 3) = Ctr

I used "ABC-QR" because that's the part of the data that doesn't change. The true data that's in those cells is, for example, "ABC-QR00012345", or whatever number it may have. I would like to modify my code to not include duplicates when it's counting.

我使用“ABC-QR”,因为这是数据中不会改变的部分。这些单元格中的真实数据例如是“ABC-QR00012345”,或者它可能具有的任何数字。我想修改我的代码,以便在计算时不包含重复项。

2 个解决方案

#1

Firstly, you must enable 'Microsoft Scripting Runtime' from within Tools --> References within the Visual Basic Editor.

首先,您必须在Visual Basic编辑器中的工具 - >引用中启用“Microsoft Scripting Runtime”。

You assign the data from the worksheet into an array; then import everything which fits the string criteria, and isn't a duplicate, into a dictionary. You can check for duplicates in the dictionary using the .Exists method.

您将工作表中的数据分配到数组中;然后将符合字符串条件的所有内容导入字典中,并且不重复。您可以使用.Exists方法检查字典中的重复项。

EDIT: As noted by @Zev in the comments, you don't even need to use the .Exists method. You can just assign the array element to the key of the dictionary, and assign the item value as 1. Any duplicate values from the Array will overwrite the previous key, so duplicates will automatically be dealt with.

编辑:正如@Zev在评论中所指出的,您甚至不需要使用.Exists方法。您可以将数组元素分配给字典的键,并将项值指定为1.数组中的任何重复值都将覆盖前一个键,因此将自动处理重复项。

Once everything which isn't a duplicate has been imported into the dictionary, you can then use the .Count property on the dictionary. This will tell you how many records fit your string criteria, and are not duplicates, within the range passed into the array.

一旦将不重复的所有内容导入到字典中,您就可以在字典上使用.Count属性。这将告诉您在传递到数组的范围内,有多少记录符合您的字符串条件,并且不是重复的。

Option Explicit
Sub countNonDuplicates()
    Dim wb As Workbook, ws As Worksheet
    Dim dict As Scripting.Dictionary
    Dim myValues() As Variant
    Dim lRow As Long, i As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)
    Set dict = New Scripting.Dictionary
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    myValues = Range(Cells(1, 1), Cells(lRow, 1))

    For i = 1 To UBound(myValues, 1)
        If InStr(myValues(i, 1), "ABC-QR") Then dict(myValues(i,1)) = 1 'arbitrary value
    Next i
    MsgBox (dict.Count)
End Sub

The above currently gets the last row of Column A and then takes the range and assigns it to the array. If you wish to use a different column, then update the following statements with the column number required (example below now uses Column D)

上面的内容当前获取了A列的最后一行,然后获取范围并将其分配给数组。如果您希望使用其他列,请使用所需的列号更新以下语句(下面的示例现在使用D列)

lRow = Cells(Rows.Count, 4).End(xlUp).Row
myValues = Range(Cells(1, 4), Cells(lRow, 4))

Also it's currently performing the above on Sheets(1). Change the worksheet number to what you require.

此外,它目前在Sheets(1)上执行上述操作。将工作表编号更改为您需要的编号。

On 100,000 records this took 0.2 seconds to produce the count.

在100,000条记录上,这需要0.2秒才能产生计数。

#2

This array formula should do the trick:

这个数组公式应该做的伎俩:

EU.Cells(16,3).FormulaArray = "=SUM(IF(ISERROR(FIND(""ABC-QR"",D4:D1500)),0,1/(COUNTIF(D4:D1500,D4:D1500))))"

Since it's an array formula, it will operate on each cell in your range in turn and look for your text (FIND("ABC-QR",D4:D1500)). If it's not found, it returns 0 to the running SUM(). If it is found, it uses the value 1/count, where count is the number of times the cell value being tested exists in your range.

由于它是一个数组公式,它将依次操作您范围内的每个单元格并查找您的文本(FIND(“ABC-QR”,D4:D1500))。如果找不到,则返回0到运行的SUM()。如果找到,则使用值1 / count,其中count是您测试的单元格值在您的范围内的次数。

#1