Как суммировать только те строки в excel, которые не отфильтрованы?

Я использую формулу SUM(B1..B20) для суммирования столбца, но когда я фильтрую данные соседним столбцом, сумма не обновляется, чтобы отражать только те строки, которые не отфильтрованы. Как это сделать?

Ответ 1

Вам нужно использовать функцию SUBTOTAL. Функция SUBTOTAL игнорирует строки, которые были исключены фильтром.

Формула будет выглядеть так:

=SUBTOTAL(9,B1:B20)

Номер функции 9, сообщает ему использовать функцию СУММ в диапазоне данных B1: B20.

Если вы "фильтруете", скрывая строки, номер функции должен быть обновлен до 109.

=SUBTOTAL(109,B1:B20)

Номер функции 109 предназначен для функции SUM, но скрытые строки игнорируются.

Ответ 2

Если вы не используете автофильтр (т.е. у вас есть скрытые вручную строки), вам нужно будет использовать AGGREGATE вместо SUBTOTAL.

Ответ 3

Когда вы используете автофильтр для фильтрации результатов, Excel даже не скрывает их: он просто устанавливает высоту строки в ноль (до 2003 года, по крайней мере, не уверен в 2007 году).

Таким образом, следующая пользовательская функция должна дать вам стартер делать то, что вы хотите (проверенные целыми числами, не играли ни с чем другим):

Function SumVis(r As Range)
    Dim cell As Excel.Range
    Dim total As Variant

    For Each cell In r.Cells
        If cell.Height <> 0 Then
            total = total + cell.Value
        End If
    Next

    SumVis = total
End Function

Edit:

Вам нужно будет создать модуль в книге, чтобы включить функцию, тогда вы можете просто называть ее на своем листе, как и любую другую функцию (= SumVis (A1: A14)). Если вам нужна помощь в настройке модуля, дайте мне знать.