excel - 如何仅在特定单元格中计算平均值？

Question

我有一个根据产品代码小计的列表。我试图通过这个函数进行平均，但最终选择了对平均数字产生不利影响的“0.0”值。我需要排除它们。另外，我想知道我是否可以进行 LastRow 计数，直到它到达具有 Cell.Style = "Note" 格式的第一个单元格。

  Sub Average_Completed()

    Dim MyCell As Range
    Dim rng0, rng1 As Range
    Set rng0 = Range("R:R").SpecialCells(xlCellTypeVisible)


    LastRow = .Range("N" & .Rows.Count).End(xlUp).Row
    'is it possible to set LastRow to first cell with Cell.Style = "Note"?

       For Each MyCell In rng0
         If Cell.Style = "Note" Then
              For i = MyCell To LastRow
                MyCell.Application.WorksheetFunction.AverageIf _
               MyCell.Offset(-4, 0).Value = i, rng0.Value)
         End If
       Next Cell

    End Sub

标签： excelvba

Answer 1

您可以使用数组公式

Option Explicit

Sub TestingAverage()

    Dim MyCell As Range
    Dim rng0 As Range
    Dim rngTemp As Range
    Dim rngNotes As Range
    Dim i As Long
    Dim LastRow As Long

    Set rng0 = Range("S:S").SpecialCells(xlCellTypeVisible) ' All visible cells in column "S"
    LastRow = Range("N" & Rows.Count).End(xlUp).Row ' Row number of the last item

    ' Looping through each visible cell in columns "S"
    ' We concatenate all ranges with "Notes" style
    For Each MyCell In rng0
        If MyCell.Style = "Note" Then
            If Not rngNotes Is Nothing Then
                Set rngNotes = Union(rngNotes, MyCell)
            Else
                Set rngNotes = MyCell
            End If
        End If
        ' Once we are past last item (current cell row > last row)
        ' add this cell like it has "Notes" style
        If MyCell.Row > LastRow Then
            If Not rngNotes Is Nothing Then
                Set rngNotes = Union(rngNotes, MyCell)
            Else
                Set rngNotes = MyCell
            End If
            Exit For
        End If
    Next MyCell
    ' We got a range of separate cells with "Notes" style
    ' This is why I asked if all "Notes" are parted with at least 1 row
    ' We can't loop through it's cells as usual though

    ' We loop through each of it's areas instead
    ' We have at least two cells in range (first "Note" cell and the "LastRow+1" cell)
    For i = 1 To rngNotes.Areas.Count - 1
        ' Current range is between current "Notes" style cell and next "Notes" style cell OR the "LastRow+1" cell
        Set rngTemp = Range(rngNotes.Areas(i).Cells(1).Offset(1, 0), _
                            rngNotes.Areas(i + 1).Cells(1).Offset(-1, 0))
        ' Since we can't exclude cells in AVERAGE we use custom formula instead
        ' Array of 1s and 0s (where 0s are unfullfilled condition not to be equal "0")
        ' is multiplied by array of values in item range
        ' it is then divided by number of values not equal "0"
        rngNotes.Areas(i).Cells(1).FormulaArray = _
            "=SUM((" & rngTemp.Address & "<>0)*" & rngTemp.Address & ")/SUM((" & rngTemp.Address & "<>0)*1)"
    Next i


End Sub

我没有测试它，因为我没有任何合适的数据，但它应该在理论上有效。