代码之家 › 专栏 › 技术社区 › Pspl

数组的累积和

cumulative-sum excel vba arrays

Pspl · 技术社区 · 7 年前

S = Data(1) + Data(2) + ... + Data(x) ).

Function CumulativeSum(Data() As Integer, k As Integer) As Integer
    For entry = 1 To k
        CumulativeSum = CumulativeSum + Data(entry)
    Next entry
End Function

它工作得很好。然而,我想知道是否有更好的方法(主要是不使用任何额外的函数,基本上只使用excel函数,如 Application.Sum ). 我在网上做了一个小搜索,但没有在此基础上找到任何东西。

我知道我不是在要求更正任何代码,我只是在要求一个不是本论坛真正目的的替代方案。然而,我也怀疑答案可能很简单,所以。。。如果有人愿意帮助我,我将非常感谢!如果你找到一个类似问题的答案,请与我共享链接,我将删除此链接。

很抱歉,我可能没有按我的要求明确说明:我只是想找到一种简单的方法,使用宏例程本身的简单函数计算累积和,而不使用 CumulativeSum

4 回复 | 直到 7 年前

Vityata 7 年前

如果你想的话数组(a,a+b,a+b+c)中的类数组(a,a+b,a+b+c),如果要传递开始和结束参数,则这是实现它的函数:

Public Sub TestMe()

    Dim outputArray     As Variant
    Dim inputArray      As Variant
    Dim counter         As Long

    inputArray = Array(1, 2, 4, 8, 16, 32, 64)

    outputArray = generateCumulativeArray(inputArray, 1, 4)
    For counter = LBound(outputArray) To UBound(outputArray)
        Debug.Print outputArray(counter)
    Next counter

    outputArray = generateCumulativeArray(inputArray, toValue:=4)
    For counter = LBound(outputArray) To UBound(outputArray)
        Debug.Print outputArray(counter)
    Next counter

End Sub

Public Function generateCumulativeArray(dataInput As Variant, _
                                        Optional fromValue As Long = 0, _
                                        Optional toValue As Long = 0) As Variant

    Dim i                   As Long
    Dim dataReturn          As Variant
    ReDim dataReturn(0)
    dataReturn(0) = dataInput(fromValue)

    For i = 1 To toValue - fromValue
        ReDim Preserve dataReturn(i)
        dataReturn(i) = dataReturn(i - 1) + dataInput(fromValue + i)
    Next i
    generateCumulativeArray = dataReturn
End Function

关于 ,方法如下: 您可以使用 WorksheetFunction. Average , Min , Max 等:

Option Explicit

Public Sub TestMe()

    Dim k As Variant
    k = Array(2, 10, 200)
    Debug.Print WorksheetFunction.Sum(k)
    Debug.Print WorksheetFunction.Average(k)

End Sub

从给定起点到给定终点的总和 ,最简单的方法可能是生成一个新数组并将其完全求和。在Python中,这称为切片,在VBA中,这可以手动完成:

Public Sub TestMe()

    Dim varArr          As Variant
    Dim colSample       As New Collection

    varArr = Array(1, 2, 4, 8, 16, 32, 64)

    colSample.Add (1)
    colSample.Add (2)
    colSample.Add (4)
    colSample.Add (8)

    Debug.Print WorksheetFunction.Sum(generateArray(varArr, 2, 4))
    Debug.Print WorksheetFunction.Sum(generateArray(colSample, 2, 4))

End Sub

Public Function generateArray(data As Variant, _
                              fromValue As Long, _
                              toValue As Long) As Variant

    Dim i                   As Long
    Dim dataInternal        As Variant
    Dim size                As Long

    size = toValue - fromValue
    ReDim dataInternal(size)

    For i = LBound(dataInternal) To UBound(dataInternal)
        dataInternal(i) = data(i + fromValue)
    Next i

    generateArray = dataInternal

End Function

这个想法是 generateArray

Public Function generateArray(data As Variant, _
                              fromValue As Long, _
                              toValue As Long) As Variant

    Dim i                   As Long
    Dim dataInternal        As Variant
    Dim size                As Long

    size = toValue - fromValue
    ReDim dataInternal(size)

    If IsArray(data) Then
        For i = LBound(dataInternal) To UBound(dataInternal)
            dataInternal(i) = data(i + fromValue)
        Next i
    Else
        For i = LBound(dataInternal) To UBound(dataInternal)
            dataInternal(i) = data(i + fromValue + 1)
        Next i
    End If

    generateArray = dataInternal

End Function

或写入 Option Base 1 在顶部,阵列将从1开始(不建议!)。

MacroMarc 7 年前

试试这个:

Sub test()
Dim arr As Variant
arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Dim mySum As Long, k As Long
Dim wsf As WorksheetFunction
Set wsf = Application.WorksheetFunction

k = 6
'operative line below
mySum = wsf.Sum(wsf.Index(arr, 1, Evaluate("ROW(1:" & k & ")")))

MsgBox mySum

End Sub

Mrig 7 年前

对于累积总和,请尝试以下操作

Function CumulativeSum(Data() As Integer, k As Integer) As Integer
    Dim tempArr
    tempArr = Data
    ReDim Preserve temp(0 To k - 1)
    CumulativeSum = WorksheetFunction.Sum(tempArr)
End Function

编辑:

Sub Demo()
    Dim MyArray
    Dim i As Long
    MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)

    Debug.Print MyArray(LBound(MyArray))
    For i = LBound(MyArray) + 1 To UBound(MyArray)
        MyArray(i) = MyArray(i - 1) + MyArray(i)
        Debug.Print MyArray(i)
    Next i
End Sub

以上代码更新数组 arr 从…起
1, 2, 3, 4, 5, 6, 7, 8, 9

1, 3, 6, 10, 15, 21, 28, 36, 45

Alfredo Castaneda 4 年前

此函数返回具有原始向量的累积和的数组。

Function CumuVector(Vec As Variant) As Variant()
Dim element, v() As Variant
Dim i As Integer
lastindexinvec = 0
For Each element In Vec
    lastindexinvec = last + 1
Next 
ReDim v(lastindexinvec) As Variant
i = 0
For Each element In Vec
    If i < last Then
    sum = sum + element
    v(i) = sum
    i = i + 1
    End If
Next
CumuVector = v
End Function