excel - 在表格中获取重心

Question

我有 5x5 表和 25 个随机整数。我需要计算桌子的重力。

我花了几个小时试图弄清楚这段代码是如何工作的，它是用scala编写的，我试图将其更改为 VBA，但没有任何运气。这是我最好的尝试，但仍然不正确，而不是生成我手动输入的数字然后输入到 excel 中，这对我来说已经足够了。

表格

Sub calcGravity()
'get sum
For i = 1 To 5
    For j = 1 To 5
    Sum = Sum + Cells(i, j).Value
    Next j
Next i

For i = 1 To 4
    For j = 1 To 4
    x = x + (i + i) * Cells(i, j).Value / Sum
    y = y + (j + i) * Cells(i, j).Value / Sum
    Next j
Next i
Msgbox("Center :" & x & y)
End Sub

标签： excelvbaalgorithmmath

Answer 1

这是一个可以计算质心的UDF：

Function CenterOfGravity(Masses As Variant) As Variant
    'Masses is a 2-dimensional array of weights
    'calculates center of gravity, returning the answer as
    'an array of 2 value

    If TypeName(Masses) = "Range" Then Masses = Masses.Value

    Dim mass As Double
    Dim momentX As Double, momentY As Double
    Dim i As Long, j As Long

    For i = LBound(Masses, 1) To UBound(Masses, 1)
        For j = LBound(Masses, 2) To UBound(Masses, 2)
            mass = mass + Masses(i, j)
            momentX = momentX + j * Masses(i, j)
            momentY = momentY + i * Masses(i, j)
        Next j
    Next i
    CenterOfGravity = Array(momentX / mass, momentY / mass)
End Function

例如：

在上面的单元格 A7:B7 中，我输入=CenterOfGravity(A1:E5)了一个数组公式（Ctrl+Shift+Enter接受）。