vba 计算excel中的不同值 - 频率函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1425289/
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
Counting distinct values in excel - frequency function
提问by Trent
I was tasked with counting the number of distinct strings in a column in excel. A quick Google search later yielded the following formula found here:
我的任务是计算 excel 列中不同字符串的数量。快速谷歌搜索后得出了以下公式发现这里：
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
Consider the data:
考虑数据：
A B C D A B E C
A B C D A B E C
Now, the match function would return an array (as the first argument is an array):
现在，匹配函数将返回一个数组（因为第一个参数是一个数组）：
1 2 3 4 1 2 7 3
1 2 3 4 1 2 7 3
So far so good. What I don't understand is how the FREQUENCY function works here, in particular how it treats bins that are replicated (for example the bin 1 is replicated in the above data). The result of the frequency function is:
到现在为止还挺好。我不明白的是 FREQUENCY 函数在这里是如何工作的，特别是它如何处理复制的 bin（例如 bin 1 在上述数据中被复制）。频率函数的结果是：
2 2 2 1 0 0 1 0 0
2 2 2 1 0 0 1 0 0
Thanks
谢谢
Taras
塔拉斯
采纳答案by Joel Goodwin
EDIT: I realised how your solution was working - amended to reflect this.
编辑：我意识到你的解决方案是如何工作的 - 修改以反映这一点。
FREQUENCY is searching for entries from your bins in the search array. Here's how it's working:
FREQUENCY 正在搜索数组中的 bin 中搜索条目。这是它的工作原理：
Search array: 1 2 3 4 1 2 7 3
搜索数组：1 2 3 4 1 2 7 3
Bins: 1 2 3 4 1 2 7 3
箱：1 2 3 4 1 2 7 3
Bin 1 => there are two 1's => 2
Bin 1 => 有两个 1 => 2
Bin 2 => there are two 2's => 2
Bin 2 => 有两个 2 => 2
Bin 3 => there are two 3's => 2
Bin 3 => 有两个 3 => 2
Bin 4 => there is one 4 => 1
Bin 4 => 有一个 4 => 1
Bin 1 repeated => 1 already counted => 0
Bin 1 重复 => 1 已计算 => 0
Bin 2 repeated => 2 already counted => 0
Bin 2 重复 => 2 已经计算 => 0
Bin 7 => there is one 7 => 1
Bin 7 => 有一个 7 => 1
Bin 3 repeated => 3 already counted => 0
Bin 3 重复 => 3 已经计算 => 0
It almost seems that the solution is exploiting a FREQUENCY quirk, that is, it won't count the same bin twice, because you mightexpect the second bin with value 1 to be non-zero as well. But that's how it works -- as it will only count the number of occurrences for the first bin and not a duplicate bin, the number of rows with a value greater than zero will give you the number of distinct entries.
似乎该解决方案正在利用 FREQUENCY 怪癖，也就是说，它不会将同一个 bin 计算两次，因为您可能期望值为 1 的第二个 bin 也是非零的。但这就是它的工作原理——因为它只会计算第一个 bin 的出现次数而不是重复 bin，值大于零的行数将为您提供不同条目的数量。
Here's an alternative approach which you might find useful. it can be used to calculate the number of distinct values:
这是您可能会发现有用的替代方法。它可用于计算不同值的数量：
Suppose your string range is B2:B10. Fill down in another column
假设您的字符串范围是 B2:B10。填写另一列
=(MATCH(B2,B:B2,1)-(ROW(B2)-ROW(B)))>0
The row should change as you copy down, so the second row should be, for example:
当您向下复制时，该行应该更改，因此第二行应该是，例如：
=(MATCH(B3,B:B3,1)-(ROW(B3)-ROW(B)))>0
This is signalling TRUE if the current row contains the first instance of a string (if you give it a couple of minutes you should be able to work out what it's doing). Therefore, if you count the number of TRUEs with COUNTIF() then you should get the number of distinct strings.
如果当前行包含字符串的第一个实例（如果你给它几分钟，你应该能够弄清楚它在做什么），这就是 TRUE 的信号。因此，如果您使用 COUNTIF() 计算 TRUE 的数量，那么您应该获得不同字符串的数量。
回答by dendarii
You could use a vba routine:
您可以使用 vba 例程：
Sub Uniques()
Dim rng As Range
Dim c As Range
Dim clnUnique As New Collection
Set rng = Range("A1:A8")
On Error Resume Next
For Each c In rng
clnUnique.Add c.Value, CStr(c.Value)
Next c
On Error GoTo 0
MsgBox "Number of unique values = " & clnUnique.Count
End Sub
If you need to display the unique results, you can just loop through the collection and write the values on your worksheet.
如果您需要显示唯一结果，您可以循环遍历集合并在工作表上写入值。
