Как исключить #VALUE! или # div / 0! в столбце для вычисления среднего значения с использованием VBA

rozario_k спросил: 28 апреля 2018 в 08:21 в: vba

У меня есть ситуация, чтобы вычислить среднее значение для столбца (в этом столбце есть много строк с числами и некоторыми строками с #DIV/0! и #VALUE!).

У меня есть макрос, написанный для вычисления средней функции. Если он заполнен числами, тогда он легко вычисляет среднее значение столбца, но если в ячейке есть код #VALUE! или #DIV/0!. то он возвращает пустую ячейку. Как я могу исключить ошибку #VALUE! и #DIV/0! и принять среднее значение только для чисел.

У меня есть более 5K файлов для вычисления среднего значения.

Private Function data As Boolean
    Dim Avg_velocity  As String
    Dim Avg_length As String
    Avg_velocity  = Application.WorksheetFunction.Average(Sheets("Data").Range("K5:K650"))
    Avg_length  = Application.WorksheetFunction.Average(Sheets("Data").Range("I7:I607"))    Sheets("Log").Range("A2:AI2").Insert
    Sheets("Log").Cells(2, "AA").value = Avg_velocity
    Sheets("Log").Cells(2, "AB").value = Avg_length
End Function

3 ответа

Есть решение
Rory ответил: 28 апреля 2018 в 09:08

Вы также можете просто использовать AverageIf, чтобы игнорировать ошибки, предоставляя критерии действительно большого числа, например:

Avg_velocity = Application.WorksheetFunction.AverageIf(Sheets("Data").Range("K5:K650"), "<9E307")
rozario_k ответил: 28 апреля 2018 в 10:47
Благодарю. Оно работает!!!!!
Vityata ответил: 28 апреля 2018 в 08:30

Что-то вроде этого должно работать вполне нормально:

Option ExplicitPublic Sub TestMe()    Dim myRng1  As Range
    Dim myCell  As Range
    Dim myRng2  As Range    Set myRng1 = Range("A1:A5")    For Each myCell In myRng1
        If Not IsError(myCell) Then
            If Not myRng2 Is Nothing Then
                Set myRng2 = Union(myRng2, myCell)
            Else
                Set myRng2 = myCell
            End If
        End If
    Next myCell    If Not myRng2 Is Nothing Then myRng2.SelectEnd Sub

Он проходит через диапазон и любезно подбирает только ячейки, которые не являются ошибками:

QHarr ответил: 28 апреля 2018 в 09:14

Также должна быть возможность использовать функцию агрегации

=Application.worksheetFunction.Aggregate(1,6,range)
Rory ответил: 28 апреля 2018 в 09:32
До тех пор, пока вы не забудете версию до 2010 года. ;)
QHarr ответил: 28 апреля 2018 в 09:37
Хорошая точка @rory
QHarr ответил: 28 апреля 2018 в 09:37
Невозможно вспомнить, есть ли у промежуточного итога существующий параметр, который может компенсировать #google
Rory ответил: 28 апреля 2018 в 09:38
Нет, нет. Это одна из лучших вещей в AGGREGATE. :)