唯一计数(Excel VBA 与公式)更快的方法 [英] Unique Count (Excel VBA vs Formulas) Faster Approach

问题描述

64 位 Win7 上的 32 位 Excel 365工作表 300600 行 x 105 列目标:计算每列中唯一条目的数量

32 Bit Excel 365 on 64 Bit Win7 Worksheet 300600 Rows x 105 Columns Goal: Calculate the Number of Unique Entries in each Column

尝试的解决方案 1:公式

Attempted Solution 1: Formula

{=SUM(1/COUNTIF(A8:A300600,A8:A300600))}

问题:运行时间长，冻结 Excel，必须停止计算

Issue: Long Runtime, Freezes Excel, Must Stop Calculation

尝试的解决方案 2:VBA UDF

Attempted Solution 2: VBA UDF

Function UniqueCount(Selection As Range) As Integer
Dim UniqueArray()
ReDim UniqueArray(0 To Selection.Count)
Dim Rng As Range
Dim CUniqueCount As Integer
CUniqueCount = 0
For Each Rng In Selection
    For i = 0 To Selection.Count
        If UniqueArray(i) = Rng.Value Then Exit For
        If UniqueArray(i) = "" Then
            UniqueArray(i) = Rng.Value
            CUniqueCount = CUniqueCount + 1
            Exit For
        End If
    Next i
Next
UniqueCount = CUniqueCount
End Function

注意:这要快得多，但我仍在寻找更快的方法

Note: This is Much faster, but I'm still looking for an even faster approach

推荐答案

试试这个

'Set a reference to MS Scripting runtime ('Microsoft Scripting Runtime')
Function UniqueCount(SelRange As Range)
    Dim Rng As Range
    Dim dict As New Scripting.Dictionary
    Set dict = CreateObject("Scripting.Dictionary")
    For Each Rng In SelRange
        If Not dict.Exists(Rng.Value) Then
            dict.Add Rng.Value, 0
        End If
    Next Rng
    UniqueCount = dict.Count
    Set dict = Nothing
End Function

这篇关于唯一计数(Excel VBA 与公式)更快的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！