Microsoft Excel - 交互式平均值

Question

这是一项作业。给出了 1 月至 6 月的数据，7 月的数据为空。输入 7 月数据后，会生成平均值。

任务是我必须修复一个程序，当我将 7 月份的数据放入其中后，就会创建一个 8 月份的单独行，而当我将 8 月份的数据放入其中时，就会创建平均值并显示 9 月份的行。

我不需要任何代码。只需告诉我正确的方向，我可以从哪里开始。

Answer 1

此代码执行以下任务：

Private Sub Worksheet_Change(ByVal Target As Range) ' It is executed each time there is a change on the sheet
    Application.EnableEvents = False 'Disable detection of further events
    Dim wks As Worksheet
    Set wks = ActiveSheet 'Assign a variable name to the active sheet
    Dim months(1 To 12) As String ' Creates an array of 12 elements to store month names
    months(1) = "january"
    months(2) = "february"
    months(3) = "march"
    months(4) = "april"
    months(5) = "may"
    months(6) = "june"
    months(7) = "july"
    months(8) = "august"
    months(9) = "september"
    months(10) = "october"
    months(11) = "november"
    months(12) = "december"
    totalrows = wks.Cells(Rows.Count, 2).End(xlUp).Row 'Row of the last value entered
    If totalrows <= 12 Then 'If it is inside the defined range of months
        wks.Cells(totalrows + 1, 1) = months(totalrows) 'Put the next month name
        For j = totalrows + 2 To 13 'Cleans the rows below this
            wks.Cells(j, 1) = ""
        Next j
    End If
    dataAverage = 0 'calculate the average
    For i = 2 To totalrows
        dataAverage = dataAverage + wks.Cells(i, 2)
    Next i
    dataAverage = dataAverage / (totalrows - 1)
    wks.Cells(2, 5) = dataAverage 'Put the average in the designated cell
    Application.EnableEvents = True 'Enable the detection of events

ErrorHandler:
    Application.EnableEvents = True 'Whenever the Sub generates an error executes this line
End Sub

它与这样的工作表一起工作：

您可以自定义代码以使其适应行/列的不同位置。

使用Alt+打开 VBA / Macros F11，双击要使用的工作表并将代码粘贴到右侧。