vba - 对列范围内的不同值进行计数的函数

Question

我正在尝试在 VBA 中创建一个函数，当给定一系列值时，该函数将返回这些值的 Count Distinct。例如:


| A 栏 |
|----------|
| 1 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
| 4 |
| 4 |
| 5 |
| 5 |
| 6 |
行数 = 11不同值 = 6

以下是我尝试用来构建可在 Excel 中调用的函数的 VBA 代码的结构:

Function CountDistinct(dataRange As Range)

Dim x As Double
x = 0

For i = 1 To dataRange.Rows.Count

x = x + (1 / (CountIf(dataRange, dataRange(i))))

Next i

End Function

我对 VBA 编程完全陌生，所以对于上面代码中所犯的所有明显的、明显的错误(如果可以这样称呼的话)表示歉意。

我知道还有其他方法可以得出正确答案，但我有兴趣学习如何创建自定义 Excel 函数。

此外，我的方法背后的伪逻辑如下:

1. 为函数CountDistinct指定一个单元格范围dataRange
2. 循环范围
3. 对于范围内的每个单元格，对该范围内的值执行 COUNTIF(因此在上面的示例中，第 3-6 行将分别返回 4，因为数字 3 在范围内出现 4 次)。
4. 对于范围内的每个单元格，将 1/(第 3 步的结果)添加到结果变量 x


|值(value)观 | CountIF(值)| 1/CountIF(值)|
|--------|----------------|------------------------ ------|
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 4 | 0.25 | 0.25
| 3 | 4 | 0.25 | 0.25
| 3 | 4 | 0.25 | 0.25
| 3 | 4 | 0.25 | 0.25
| 4 | 2 | 0.5 | 0.5
| 4 | 2 | 0.5 | 0.5
| 5 | 2 | 0.5 | 0.5
| 5 | 2 | 0.5 | 0.5
| 6 | 1 | 1 |
| | | 1/CountIF(值) 的总和 = 6 |


这将返回 A 列中不同值的计数 == 6。

Answer 1

第一步:
将 Option Explicit 添加到所有模块的 header 。它将捕获 OneVariable 和 OneVarlable 之间的差异。
让你的变量有意义——下次你看这段代码时你会知道 x 和 i 代表什么吗？

您的计数选项是

1. 使用工作表函数
2. 保存值，并仅计算与先前值不匹配的值

使用工作表功能，

Option Explicit

Function CountUnique(dataRange As Range) As Long
Dim CheckCell
Dim Counter As Double
Counter = 0

For Each CheckCell In dataRange.Cells
    Counter = Counter + (1 / (WorksheetFunction.CountIf(dataRange, CheckCell.Value)))
Next
' Finally, set your function name equal to the Counter, 
'   so it knows what to return to Excel
CountUnique = Counter
End Function

使用跟踪

...
' check out scripting dictionaries
' much more advanced - Keep it simple for now
...