excel - 计算列中空单元格中单元格的平均值

Question

下面的 VBA 代码计算 Excel 中一列中空单元格上方的单元格总和。列中每个空单元格之前的行数不相同。我想调整代码来计算平均值。可以添加一个计数器，然后将总和(已计算)除以计数器。
原始问题和代码(由 Bernard Liengme 编写)显示在以下链接中:
https://answers.microsoft.com/en-us/msoffice/forum/all/automatically-calculate-the-sum-of-data-separated/a691afcf-683e-463f-bad7-9fa3a81cf48c
谢谢。

Sub tryme()
   lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   For k = 1 To lastRow
    If Cells(k, "A") <> "" Then
      Subtotal = Subtotal + Cells(k, "B")
    Else
      Cells(k, "B") = Subtotal
      Subtotal = 0
    End If
   Next k
   Cells(lastRow + 1, "B") = Subtotal
End Sub

Answer 1

添加次均值
快速修复

Option Explicit

Sub AddSubAVG()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve
    
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim k As Long
    Dim tCount As Long
    Dim tSum As Double
    
    For k = 1 To LastRow
        If ws.Cells(k, "A").Value <> "" Then
            tSum = tSum + ws.Cells(k, "B").Value
            tCount = tCount + 1
        Else
            If tCount > 0 Then
                ws.Cells(k, "B").Value = tSum / tCount
                tSum = 0
                tCount = 0
            End If
        End If
    Next k
    
    If tCount > 0 Then ws.Cells(LastRow + 1, "B").Value = tSum / tCount

End Sub