以字符串为索引添加到 VBA 中的数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16447088/
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
Adding to an array in VBA with strings as the index
提问by McB
Not sure I've labelled this correctly.
不确定我是否正确标记了它。
I have a bunch of cells containing strings of data. Each cell consists of something like this:
我有一堆包含数据字符串的单元格。每个单元格由以下内容组成：
q1 = 1 | q2 = 3.2 | q3 = 5.6
q1 = 1.8 | q3 = 2.1 | q5 = 1.4
*Note: The delimiter is litteral, all that text is in a single cell, with a pipe char.
*注意：分隔符是无意义的，所有文本都在一个单元格中，带有管道字符。
I want to loop through each cell, explode (to use the PHP term) by the pipe (|) delimiter, and then do so again by the = sign.
我想遍历每个单元格，通过管道 (|) 分隔符分解（使用 PHP 术语），然后通过 = 符号再次执行此操作。
I want to create an array for each possible value to the left of the equal sign, and add the value found to the right to the array (not add as in sum, add as in append to the array).
我想为等号左侧的每个可能值创建一个数组，并将右侧找到的值添加到数组中（不是添加为总和，添加为附加到数组）。
Visually, I think the array should look something like this:
从视觉上看，我认为数组应该是这样的：
Vars[
q1 [ 1,1.8 ],
q2 [ 3.2 ],
q3 [ 5.6,2.1]....]
End goal being I'd like to get the average, mean and median for each of q1, q2 and q3.
最终目标是我想获得 q1、q2 和 q3 的平均值、平均值和中位数。
Is this doable in VB? I'm more familiar with PHP, but would like to keep this in Excel.
这在VB中可行吗？我更熟悉 PHP，但希望将其保留在 Excel 中。
Thanks.
谢谢。
回答by andrewmours
It's complicated, but it can be done. I tested this in excel based on your cell input, putting them in A1 and A2:
这很复杂，但可以做到。我根据您的单元格输入在 excel 中对此进行了测试，将它们放在 A1 和 A2 中：
q1 = 1 | q2 = 3.2 | q3 = 5.6
q1 = 1.8 | q3 = 2.1 | q5 = 1.4
I put together a macro in Excel called "Looper" that uses two loops to cycle through the cells in column A, split them at the '|' and search for each number value, convert it to a double and place it in the corresponding array.
我在 Excel 中组合了一个名为“Looper”的宏，它使用两个循环来循环遍历 A 列中的单元格，将它们拆分为“|” 并搜索每个数字值，将其转换为双精度值并将其放入相应的数组中。
Private Sub Looper()
''Loop Variables
Dim i, k As Integer
Dim MoveDown As String
''Variables to manipulate the string
Dim Selecter As String
Dim TotalCell As String
Dim Splitter As Variant
Dim strIncrement As String
''Array variables and counters
Dim q1(50) As Double
Dim q2(50) As Double
Dim q3(50) As Double
Dim qv1, qv2, qv3 As Integer
''Variables for finding the number in each increment
Dim Equals As Integer
Dim strNumber As String
Dim dblNumber As Double
''Set the array counters to 0
qv1 = 0
qv2 = 0
qv3 = 0
i = 0
Do Until MoveDown = "DONE"
Selector = "A" + Replace(Str(i), " ", "")
If Range(Selector).Value = "" Then
MoveDown = "DONE"
Else
TotalCell = Range(Selector).Value
Splitter = Split(TotalCell, "|")
For k = LBound(Splitter) To UBound(Splitter)
''strIncrement holds the data in between each |
strIncrement = Splitter(k)
''Remove any spaces
strIncrement = Replace(strIncrement, " ", "")
''Equals shows the location of the number (length of string - loc of =)
Equals = Len(strIncrement) - InStr(1, strIncrement, "=")
strNumber = Right(strIncrement, Equals)
dblNumber = CDbl(strNumber)
''Check for the array name and then add the data to the corresponding array
If InStr(1, strIncrement, "q1") > 0 Then
q1(qv1) = dblNumber
qv1 = qv1 + 1
Else
If InStr(1, strIncrement, "q2") > 0 Then
q2(qv2) = dblNumber
qv2 = qv2 + 1
Else
If InStr(1, strIncrement, "q3") > 0 Then
q3(qv3) = dblNumber
qv3 = qv3 + 1
End If
End If
End If
Next
End If
i = i + 1
Loop
End Sub
I was able to successfully add the data to the arrays, so it should be simple to go from there to calculate the means, etc.
我能够成功地将数据添加到数组中，所以从那里开始计算均值等应该很简单。
回答by Tim Williams
This will handle an arbitrary number of "keys" (q1,q2, etc)
这将处理任意数量的“键”（q1、q2 等）
Sub Tester()
'needs a reference to microsoft scripting runtime
Dim d As New Scripting.dictionary
Dim c As Range
Dim arrP, arrE
Dim q, v, tmpV, tmpP, tmpArr, uB
Dim i As Long, n As Long
Dim k
For Each c In Selection.Cells
tmpV = Trim(c.Value)
If InStr(tmpV, "=") > 0 Then
arrP = Split(tmpV, "|") 'split on pipe
For i = LBound(arrP) To UBound(arrP)
tmpP = arrP(i)
If InStr(tmpP, "=") > 0 Then
q = Trim(Split(tmpP, "=")(0))
v = Trim(Split(tmpP, "=")(1))
If IsNumeric(v) Then
If Not d.exists(q) Then
d.Add q, Array(v)
Else
tmpArr = d(q) 'get dict value into temp array
uB = UBound(tmpArr) + 1
ReDim Preserve tmpArr(0 To uB) 'extend array
tmpArr(uB) = v
d(q) = tmpArr 'put back into dict
End If
End If
End If
Next
End If 'cell has at least one "="
Next c
'dump the dictionary to the immediate pane
For Each k In d.keys
Debug.Print k, Join(d(k), ",")
Next k
End Sub
