vba 在 Excel 中选择 (DISTINCT COUNT)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9876692/
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
SELECT (DISTINCT COUNT) in Excel
提问by rvphx
I have done this without much effort on T-SQL, but I want to implement the same on Excel VBA. How to achieve this? I have a field on my sheet called "Stops" which pertains to the Stops that a truck makes while delivering its order. I want to count the total number of stops. 1 stop can have multiple orders to be delivered. The data on the stops column is something like:
我在 T-SQL 上没有做太多努力，但我想在 Excel VBA 上实现相同的功能。如何实现这一目标？我的工作表上有一个名为“停止”的字段，它与卡车在交付订单时所做的停止有关。我想计算停靠站的总数。1 个停靠点可以有多个订单要交付。停止列上的数据类似于：
Order# Stops
1527305 1
1527305 1
1529418 2
1529418 2
1527299 3
1527299 3
1528894 5
1528894 5
1529529 6
1529529 6
1529518 7
1529518 7
1527522 8
1527522 8
So, the final count should be just 7 unique stops. The current code looks at the last row and takes it as the total stops (which is wrong). The code that I have right now is as follows:
因此，最终计数应该只是 7 个独特的停靠点。当前代码查看最后一行并将其作为总停靠点（这是错误的）。我现在拥有的代码如下：
ActiveCell.Offset(0, 7).Select ' H = stop number
Selection.Value = curStop 'sets stop number
If Selection.Value = 0 Then
ActiveCell.Offset(-1, 0).Select
curStop = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Selection.Value = curStop
End If
I have done something on T-SQL with one line of code, but dont know how to do this on Excel. Any help will be appreciated. Thanks!!
我用一行代码在 T-SQL 上做了一些事情，但不知道如何在 Excel 上做到这一点。任何帮助将不胜感激。谢谢！！
回答by playercharlie
You can also try
你也可以试试
=SUM(IF($B:$B,1/COUNTIF($B:$B,$B:$B)))
as an array formula solution, assuming your data is in B2:B15. Array formulae are, of course, entered with a Ctrl + Shift + Enter, rather than an enter, so maybe your solution is slightly better :)
作为数组公式解决方案，假设您的数据在 B2:B15 中。当然，数组公式是使用 Ctrl + Shift + Enter 输入的，而不是 Enter，所以也许您的解决方案会稍微好一些 :)
Also shouldn't your question say the answer is 7stops, and not 5???
也不应该你的问题说答案是7站，而不是5站？？？
回答by mechanical_meat
You already have a formula-based solution, and that may be what you wish to use in this case.
If you are curious about a way to solve the problem using VBA, read on.
您已经有了一个基于公式的解决方案，这可能就是您希望在这种情况下使用的解决方案。
如果您对使用 VBA 解决问题的方法感到好奇，请继续阅读。
For the problem of identifying unique values a dictionary can be an appropriate data-structure.
In particular, you may check for a key's existence before adding a key.
Tested example follows:
对于识别唯一值的问题，字典可以是合适的数据结构。
特别是，您可以在添加密钥之前检查密钥是否存在。
测试示例如下：
(Please note: you must enable (check the box) Tools > References > "Microsoft Scripting Runtime".)
（请注意：您必须启用（选中复选框）工具 > 参考 > “Microsoft Scripting Runtime”。）
Option Explicit
Function uniq_dict(ByRef row As Long, ByRef col As Long)
Dim dict As New Scripting.Dictionary
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
For row = 1 To ws.Cells(Rows.Count, col).End(xlUp).row
If Not dict.Exists(ws.Cells(row, col).Value) Then
dict.Add ws.Cells(row, col).Value, Null '# can insert something here
End If
Next row
Set uniq_dict = dict
End Function
Sub call_uniq_dict()
Dim i As Integer
Dim k() As Variant
Dim dict As New Scripting.Dictionary
Set dict = uniq_dict(1, 1)
Debug.Print "total items in dict:", dict.Count
k = dict.Keys
For i = 0 To dict.Count - 1
Debug.Print " dict key:", k(i)
Next
End Sub
Result:
结果：
total items in dict: 5 dict key: 1 dict key: 2 dict key: 3 dict key: 7 dict key: 8
