计算列范围中不同值的功能 [英] Function to count distinct values in a column range

问题描述

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


|列A |
| ---------- |
| 1 |
| 2 |
| 3 |
| 3 |
| 3 |
| 3 |
| 4 |
| 4 |
| 5 |
| 5 |
| 6 |

行数= 11
不同的值= 6

以下是VBA的结构代码我试图用来构建一个可以在Excel中调用的函数：

 函数CountDistinct（dataRange As Range）
 
 Dim x As Double 
x = 0 
 
对于i = 1 To dataRange.Rows.Count 
 
x = x +（1 /（CountIf （dataRange，dataRange（i））））
 
下一个i 
 
结束函数
  

我完全是VBA编程的新功能，所以对上面的代码中所有明显的，尴尬的错误，如果甚至可以这样称呼，那么道歉。

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

另外，我的方法背后的伪逻辑如下：

1. 给出 CountDistinct 的功能数据范围


2. 循环遍历范围


3. 范围内的每个单元格，在整个范围内对该值执行 COUNTIF （因此在上面的示例中，第3-6行将返回 4 ，因为3号出现


4. 对于范围中的每个单元格，将1 /（步骤3的结果）添加到结果变量x

<




|值| CountIF（Value）| 1 / CountIF（Value）|
| -------- | ---------------- | ------------------- ---------- |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 4 | 0.25 |
| 3 | 4 | 0.25 |
| 3 | 4 | 0.25 |
| 3 | 4 | 0.25 |
| 4 | 2 | 0.5 |
| 4 | 2 | 0.5 |
| 5 | 2 | 0.5 |
| 5 | 2 | 0.5 |
| 6 | 1 | 1 |
| | | 1 / CountIF（Value）的SUM = 6 |






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

解决方案

第一步：

将 Option Explicit 添加到所有模块的标题中。它将捕获 OneVariable 和 OneVarlable 之间的差异。

使您的变量有意义 - 你会知道下一次你看这个代码是什么？





您的计数选项为



1. 用户工作表函数


2. 保存值，只计算那些不符合之前的值



使用工作表函数，



  Option Explicit 
 
函数CountUnique（dataRange As Range）As Long 
 Dim CheckCell 
 Dim Counter As Double 
 Counter = 0 
 
每个CheckCell在dataRange.Cells 
 Counter = Counter +（1 /（WorksheetFunction.CountIf（dataRange，CheckCell。值））
下一个
'最后，设置你的函数名称等于Counter，
'，所以它知道返回到Excel 
 CountUnique = Counter 
 End功能
  

使用保持轨迹

  ... 
'检查脚本字典
'更高级 - 现在保持简单
 ... 
  




I am attempting to create a function in VBA that, when given a range of values, will return a Count Distinct of those values. For example:

| Column A | |----------| | 1 | | 2 | | 3 | | 3 | | 3 | | 3 | | 4 | | 4 | | 5 | | 5 | | 6 | Count of Rows = 11 Distinct values = 6

Here is the structure of the VBA code I'm trying to use to build a function I can call in Excel:

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

I'm completely new to VBA programming, so apologies for all of the obvious, glaring mistakes made in the code above, if it can even be called that.

I know there are other ways to arrive at the correct answer, but I'm interested in learning how to create custom Excel functions.

Also, the pseudo-logic behind my approach is as follows:

1. Give the function CountDistinct a range of cells dataRange
2. Loop through the range
3. For each cell in the range, perform a COUNTIF on that value across the range (so in the example above, rows 3-6 would each return 4, since the number 3 appears 4 times in the range).
4. For each cell in the range, add 1/(the result of step 3) to the result variable x

| Values | CountIF(Value) | 1/CountIF(Value) | |--------|----------------|-----------------------------| | 1 | 1 | 1 | | 2 | 1 | 1 | | 3 | 4 | 0.25 | | 3 | 4 | 0.25 | | 3 | 4 | 0.25 | | 3 | 4 | 0.25 | | 4 | 2 | 0.5 | | 4 | 2 | 0.5 | | 5 | 2 | 0.5 | | 5 | 2 | 0.5 | | 6 | 1 | 1 | | | | SUM of 1/CountIF(Value) = 6 |

This will return the Count of Distinct values in column A == 6.

解决方案

First Steps:
Add Option Explicit to the header of all your modules. It will capture the difference between OneVariable and OneVarlable.
Make your variables meaningful - will you know what x and i were for next time you look at this code?

Your options for the count are

1. user the worksheet function
2. save the values, and only count those that don't match previous values

Using the worksheet function,

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

Using the keeping track

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

这篇关于计算列范围中不同值的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！