Как усреднить значения в отфильтрованных ячейках или списке Excel?

Когда вы применяете функцию СРЗНАЧ напрямую к отфильтрованному набору данных, она всё равно усредняет все ячейки в Ограниченный диапазон, включая те, что скрыты фильтрацией. Это приводит к неверным результатам, если вы хотите усреднить только видимые строки. Чтобы получить истинное среднее значение отфильтрованных (видимых) данных, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel предлагает эффективное решение. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может корректировать расчёт с учётом фильтрации и скрытых строк, что делает её идеальной для этой цели.

Чтобы рассчитать среднее значение только для отфильтрованных результатов, выполните следующие действия:

• Определите диапазон, включающий все отфильтрованные данные в столбце, по которому вы хотите рассчитать среднее значение (в данном примере предполагается, что значения находятся в)C12:C24 столбца «Сумма»).
• В пустой ячейке введите следующую формулу:

=SUBTOTAL(1,C12:C24)

Эта формула вычисляет среднее значение видимых (отфильтрованных) ячеек в ограниченном диапазоне ()C12:C24). Параметр 1 указывает функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ использовать функцию СРЗНАЧ, и ПРОМЕЖУТОЧНЫЕ.ИТОГИ автоматически игнорирует строки, скрытые фильтрацией.

После ввода формулы нажмите Enter. Вы сразу увидите среднее значение только для видимых строк, как показано ниже:

Суммирование/подсчёт/усреднение только видимых ячеек с игнорированием скрытых или отфильтрованных ячеек/строк/столбцов

Стандартные функции СУММ, СЧЁТ и СРЗНАЧ в Excel выполняют расчёты по всем ячейкам в заданном диапазоне — независимо от того, видимы они или скрыты с помощью фильтров или вручную. Для более точной обработки таких случаев используйте Kutools для Excel! Благодаря специальным функциям SUMVISIBLE, COUNTVISIBLE и AVERAGEVISIBLE вы легко сможете рассчитывать суммы, количества и средние значения только для действительно видимых ячеек в любом диапазоне — исключая как отфильтрованные, так и вручную скрытые строки, столбцы или отдельные ячейки. Эта функциональность помогает избежать ошибок в сложных таблицах и экономит время по сравнению с использованием громоздких формул или пользовательского кода.

Усреднение отфильтрованных данных/списка с помощью функции АГРЕГАТ

Если вы используете Excel 2010 или новее, функция АГРЕГАТ даёт ещё больше гибкости по сравнению с ПРОМЕЖУТОЧНЫМИ.ИТОГАМИ при расчёте средних значений для отфильтрованных данных — она предлагает дополнительные возможности обработки ошибок и скрытых строк. Вот как её можно применить:

• В пустой ячейке введите следующую формулу (предполагая, что ваши отфильтрованные данные находятся в диапазоне C12:C24):

=AGGREGATE(1,5, C12:C24)

• Первый аргумент ()1) указывает функции СРЗНАЧ, как и в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
• Второй аргумент ()5) указывает функции АГРЕГАТ игнорировать скрытые строки (оставшиеся после фильтрации) и ошибки.

После ввода формулы нажмите Enter, чтобы мгновенно отобразить среднее значение видимых строк в вашем диапазоне фильтрации. Если вы хотите адаптировать формулу под другие способы скрытия строк или использовать другие агрегатные функции, просто измените второй параметр соответствующим образом.

Макрос VBA для усреднения только действительно видимых ячеек

Для решения более сложных или индивидуальных задач можно воспользоваться простым макросом VBA, чтобы усреднить только видимые ячейки (исключая скрытые и отфильтрованные) в выбранном диапазоне. Это особенно полезно на листах, где данные скрываются различными способами. Вот как это сделать:

1. Перейдите на вкладку Разработчик в Excel и выберите Visual Basic, чтобы открыть редактор VBA. В редакторе нажмите Вставка > Модуль, чтобы создать новый модуль.
2. Скопируйте и вставьте следующий код VBA в окно модуля:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. Закройте редактор VBA. Вернувшись на лист, нажмите Alt+F8, выберите AverageVisibleCells и нажмите Выполнить. При появлении запроса укажите целевой диапазон данных. Макрос вычислит и отобразит среднее значение только для видимых (не отфильтрованных и не скрытых) числовых ячеек.

Взвешенное среднее

Расчёт среднего/среднегодового темпа роста

Скользящее/подвижное среднее

Накопительная сумма / среднее

Средняя скорость изменения