VBA excel查找集合的统计模式 [英] VBA excel finding the statistical mode of a collection

问题描述

所以我试图分析Excel中的一些数据，并有一些麻烦找到最常见的数字。我有一个未知数量的位置，可能有未知数量的捐款。例如

So I'm trying to analysis some data in Excel and having some trouble finding the most frequent numbers. I have an unknown number of locations which can have an unknown number of donations. For example

• Brantford $ 50.00


• Brantford $ 25.00


• Brantford $ 50.00


• 温莎$ 200.00


• 魁北克$ 25.00


• 魁北克$ 100.00


• Quebec $ 50.00


• 
  
• 
  
• 
  
• 魁北克$ 25.00


• 魁北克$ 50.00
b
• 
  
• 
  
• 魁北克$ 25.00


• 魁北克$ 100.00


• 魁北克$ 40.00


• Windsor $ 140.00


• Windsor $ 20.00


• Windsor $ 20.00

• Brantford $50.00
• Brantford $25.00
• Brantford $50.00
• Windsor $200.00
• Quebec $25.00
• Quebec $100.00
• Quebec $50.00
• Quebec $50.00
• Quebec $25.00
• Quebec $50.00
• Quebec $50.00
• Quebec $25.00
• Quebec $100.00
• Quebec $40.00
• Windsor $140.00
• Windsor $20.00
• Windsor $20.00

所以我需要使用VBA来找到每个位置的计数，总和，平均值和模式（必须通过VBA来完成，不能只使用高级过滤器/枢轴

So I need to use VBA to find for each location the count, sum, mean and mode (has to be done through VBA, can't just write instructions on how to do this using advanced filters/pivot tables :().

现在使用VBA我有一个字典对象存储位置名称作为一个键和每个捐赠在集合中使用计数收集我有计数，可以很容易地循环通过收集的总和，使用那些我有平均值;但是，我不确定最有效的方式来获得模式。

So right now using VBA I have a dictionary object that stores the location name as a key and each donation in a collection. Using the count of the collection I have the count, can easily loop through the collection for the sum, using those I have the mean; but, I am not sure the most efficient way to get the mode.

我知道我可以找到它，如果我的数据在一个数组使用Application.mode，但是似乎并不适用于集合:(。将集合转换为一个数组虽然找到的模式，虽然真的不打击我作为最有效的解决方案只有其他选项我可以找到的是排序的集合，然后循环通过他们找到模式。

I know I can find it if my data was in an array using Application.mode, but that doesn't appear to work for collections :(. Converting a collection to an array though to find the mode though really doesn't strike me as the most efficient solution. Only other option i can find of though is to sort the collections then loop through them to find the mode.

那么想知道是否有人知道一个好的方法来找到集合的统计模式？

So wondering if anyone knows of a good way to find the statistical mode of a collection?

Dim locdata As Object
Set locdata = CreateObject("scripting.dictionary")  

For counter = 2 To max
    mykey = Cells(counter, loccol).value
    If Not (locdata.exists(mykey)) Then
        locdata.Add (mykey), New Collection
    End If
    locdata(mykey).Add (Cells(counter, donamountcol).value)
Next counter
For Each k In locdata.keys
    locname = k
    Cells(counter, 1) = k
    Cells(counter, 2) = locdata(k).Count
    donationtotal = 0
    For Each donvalue In locdata(k)
        donationtotal = donationtotal + donvalue
    Next donvalue
    Cells(counter, 3) = donationtotal
    Cells(counter, 4) = donationtotal / CDbl(locdata(k).Count)
    'Cells(counter, 5) = Application.mode(locdata(k)) doesn't work :(
    counter = counter + 1
Next k

edit：理想情况下，输出应为以魁北克为例）
魁北克：计数：10和：515平均：51.5模式：50

edit: Ideally the output should be (using Quebec as an example) Quebec: Count: 10 Sum: 515 Average: 51.5 Mode: 50

推荐答案

实际上只是决定做一个字典的字典。所以我有位置和每个位置比有一个字典的每个捐款数量的计数。很容易比较计数的方式来找到模式。

I actually just decided to make a dictionary of dictionaries. So I have the locations and each location than has a dictionary of the count of each donation amount. Was easy enough to compare counts that way to find the mode.

这篇关于VBA excel查找集合的统计模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！