Excel VBA将值放在多维数组中 [英] Excel VBA Place values in multidimensional array

问题描述

我有一个这种类型结构的工作表（实际工作表中有更多列，但不是很多）：

I have a worksheet with this type of structure (there are more columns in the real sheet, but not many):

ColumnAValue1   ColumnBValue1   23
ColumnAValue1   ColumnBValue1   45
ColumnAValue1   ColumnBValue1   2.4
ColumnAValue1   ColumnBValue2   1
ColumnAValue1   ColumnBValue2   3
ColumnAValue2   ColumnBValue1   5
ColumnAValue2   ColumnBValue1   6
ColumnAValue2   ColumnBValue1   7
ColumnAValue2   ColumnBValue2   355
ColumnAValue2   ColumnBValue2   221

我想获得每个组合的平均值，项目编号和偏差（例如，ColumnAValue1 ColumnBValue1将是23,45和2.4的平均值）。所以我以为把数组，数组或字典中的所有数据（我不知道是否存在任何类似多维字典）将是有用的。我想以一个类似于以下结构的多维数组（或集合）结束：

And I want to get averages, item numbers and deviation for each combination (for example, ColumnAValue1 ColumnBValue1 would be the average of 23, 45 and 2.4). So I thought that getting all data in an Array, or Collection or Dictionary (I don't know if anything like "Multidimensional Dictionary" exists) would be useful. I wanted to end with a multidimentional array (or Collection) with a structure similar to this:

AllData(
        ColumnAValue1(
                    ColumnBValue1(23,45,2.4)
                    ColumnBValue2(1,3)
                    )
        ColumnAValue2(
                    ColumnBValue1(5,6,7)
                    ColumnBValue2(355,221)
                    )
        )

我知道如何获取唯一值从列中。

I know how to obtain unique values from columns.

我的两个问题：
1）如何使用正确的键创建数组（或集合） ColumnAValue1和ColumnAValue2用于第一个维度，ColumnBValue1和ColumnBValue2为第二个）和 2）然后循环遍历所有我的数据和放置值在相应的子阵列中。

My two questions: 1) How can I create an Array (or Collection) with the proper Keys (ColumnAValue1 and ColumnAValue2 for the first dimention, and ColumnBValue1 and ColumnBValue2 for the second), and 2) then loop through all my data and "place" values in the corresponding subarray.

推荐答案

Sub Test()
  Dim c As Collection
  Set c = New Collection

  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("Sheet1")

  Dim i As Long
  For i = 1 To 10 'Assume 10 rows
    AddToLayeredCollection c, ws.Cells(i, 3).value, ws.Cells(i, 1).value, ws.Cells(i, 2).value 'Assume two columns for keys, A and B
  Next

  'Add 'c' to the watch window and examine it
End Sub

Public Sub AddToLayeredCollection(ByVal root_collection As Collection, ByVal value As Variant, ParamArray keys() As Variant)
  Dim i As Long
  Dim target_collection As Collection

  Set target_collection = root_collection
  For i = LBound(keys) To UBound(keys)
    Set target_collection = ResolveToCollection(target_collection, keys(i))
  Next

  target_collection.Add value
End Sub

Private Function ResolveToCollection(ByVal parent_collection As Collection, ByVal key As Variant) As Collection
  On Error Resume Next
  Set ResolveToCollection = parent_collection(key)(1)
  On Error GoTo 0

  If ResolveToCollection Is Nothing Then
    Set ResolveToCollection = New Collection
    parent_collection.Add Array(key, ResolveToCollection), key
  End If
End Function

我唯一的原因使用 Array（）的东西是能够从集合中检索密钥。您可以使用词典而不是，并删除 Array（）。

The only reason I'm using the Array() thing is to be able to retrieve keys from the collection. You can use Dictionary instead and remove the Array().

这篇关于Excel VBA将值放在多维数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！