arrays - 如何遍历列并计算平均值？

Question

data series

我想找到一列下十个值的每个段的平均值。 (见数据系列图片)
一直持续到数据集的底部。数据集的长度可能不同，并且代码必须是“通用”的。

基于其他代码段，我尝试这样做:

Sub tenthavg()

Dim currentIndex As Long
Dim myArray() As Variant
Dim rng As Range

ReDim myArray(1 To 10)     

Range("b1", Range("b1").End(xlDown)).Select
Set myArray = Selection        
currentIndex = 1

Do Until currentIndex + 1 > UBound(myArray)
    ActiveSheet.Cells(currentIndex, "T") = AverageOfSubArray(myArray, currentIndex, 10)
    currentIndex = currentIndex + 1
Loop                   

End Sub

'=================================================================

Function AverageOfSubArray(myArray As Variant, startIndex As Long,   elementCount As Long) As Double
Dim runningTotal As Double
Dim i As Long

For i = startIndex To (startIndex + elementCount - 1)
    runningTotal = runningTotal + val(myArray(i))
Next i
AverageOfSubArray = runningTotal / elementCount
End Function

不幸的是我不能让它工作。我以正确的方式接近这个吗？

如果是这样，我做错了什么？

Answer 1

恕我直言，这不是很成功的方法......而不是Select学习 EndDown和其他从交互工作中借来的概念利用了 VBA 自己的机制。

“通用”方法将 Range 起始地址、批量大小和偏移量作为参数放在哪里...

Sub AvgX(MyR As Range, S As Integer, ORow As Integer, OCol As Integer)
' MyR = start of range
' S   = batch size
' OCol, ORow = Offsets to place result in relation to last batch value
Dim Idx As Integer, Jdx As Integer, RSum As Variant

    Idx = 1
    RSum = 0
    Do
        For Jdx = 1 To S
            RSum = RSum + MyR(Idx, 1)
            Idx = Idx + 1
            If MyR(Idx, 1) = "" Then Exit Do
        Next Jdx
        MyR(Idx - 1, 1).Offset(ORow, OCol) = RSum / (Jdx - 1)
        RSum = 0
    Loop
End Sub

并由

Sub Test()
    AvgX [C4], 10, 0, 1
End Sub

给你这个结果...