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

С помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая игнорирует строки, скрытые фильтром, вы легко просуммируете только видимые ячейки. Выполните следующие действия:

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

1. В пустую ячейку, например C13, введите формулу: =SUBTOTAL(109,C2:C12) (109 означает, что при суммировании скрытые значения будут игнорироваться; C2:C12 — это диапазон, который будет просуммирован с игнорированием отфильтрованных строк), и нажмите клавишу Enter.

Примечание: Эта формула также позволяет суммировать только видимые ячейки, если на листе есть скрытые строки. Однако она не учитывает ячейки в скрытых столбцах.

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

Стандартные функции СУММ, СЧЁТ и СРЗНАЧ учитывают все ячейки в ограниченном диапазоне — независимо от того, скрыты они или отфильтрованы. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ позволяет суммировать, подсчитывать или усреднять значения, игнорируя только скрытые строки. А вот функции Kutools для Excel SUMVISIBLE, COUNTVISIBLE и AVERAGEVISIBLE легко вычисляют сумму, количество и среднее значение в ограниченном диапазоне, полностью игнорируя любые скрытые ячейки, строки или столбцы.

Если вас интересует приведённый ниже код, он также позволяет суммировать только видимые ячейки.

1. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic для приложений.

2. Нажмите Вставка > Модуль и вставьте следующий код в окно модуля.

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

3. Сохраните этот код и введите формулу =SumVisible(C2:C12) в пустую ячейку. См. скриншот:

4. Затем нажмите клавишу Enter — и вы получите желаемый результат.

Если у вас установлен Kutools для Excel, вы сможете мгновенно рассчитать сумму, количество или среднее значение только для видимых или отфильтрованных ячеек в Excel.

Например, если вы хотите просуммировать только видимые ячейки, выберите ячейку для размещения результата, введите формулу =SUMVISIBLE(C3:C12) (C3:C12 — диапазон, в котором будут суммироваться только видимые ячейки) и нажмите клавишу Enter.

В результате сумма будет рассчитана с игнорированием всех скрытых ячеек. См. скриншот:

Чтобы подсчитать только видимые ячейки, используйте формулу =COUNTVISIBLE(C3:C12); чтобы найти среднее значение только видимых ячеек, используйте формулу =AVERAGEVISIBLE(C3:C12).

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

1. Выберите ячейку, в которую нужно поместить результат суммирования, и нажмите Kutools > Функции > Статистические и математические > SUMVISIBLE(или)AVERAGEVISIBLE, COUNTVISIBLE — в зависимости от ваших задач). См. скриншот:

2. В открывшемся диалоговом окне «Аргументы функции» укажите диапазон, в котором нужно просуммировать данные, игнорируя скрытые ячейки, и нажмите кнопку ОК. См. скриншот:

В результате сумма будет рассчитана без учёта всех скрытых ячеек.

Как просуммировать абсолютные значения в Excel?

Как быстро просуммировать каждую вторую или n-ю строку/столбец в Excel?