Суммируем с условием только видимые строки в Excel и Google Таблицах

Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки - это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (про нее подробнее здесь).

А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все.

Так что ни одна из них "в чистом виде" тут не поможет: одна будет обрабатывать видимые строки (SUBTOTAL), другая суммировать по условию (SUMIFS). Нам надо совместить.

Со вспомогательным столбцом

Можно добавить дополнительный столбец, в котором мы просто продублируем столбец с числами (Сумма), но используем функцию SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Ее вторым аргументом будет одна-единственная ячейка. Функцию можно использовать любую, которая на выходе даст то же самое число — например, сумму (9), максимум (4) и т.д.

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4; ссылка на ячейку с числом)

Теперь можно применить к этому столбцу функцию SUMIFS / СУММЕСЛИМН с нужным нам условием. В общем виде:

=СУММЕСЛИМН(диапазон с функциями SUBTOTAL; диапазон условия ; условие)

Без вспомогательного столбца с помощью LAMBDA

Если у вас Microsoft 365 или Google Таблицы, можно воспользоваться функцией LAMBDA и сформировать диапазон с числами только из видимых строк виртуально, а не делать дополнительный столбец.

=MAP(диапазон с числами;LAMBDA(ячейка;SUBTOTAL(4;ячейка)))

Здесь ячейка — это название переменной. Оно может быть и другим на ваш вкус.

На выходе мы получаем массив чисел того же размера, что и исходный, но не отображаемые в моменте числа там превращаются в нули.

Нам остается добавить поверх этого нужное условие. Можно сформировать массив того же размера, в котором будет проверяться условие. Например, с помощью знака "равно" (или знаков больше >, меньше <, не равно <>, больше либо равно >=, меньше либо равно <=):

=диапазон условия = "условие"

Если нужно совпадение с учетом регистра, то используйте вместо "равно" функцию EXACT / СОВПАД.

=EXACT(диапазон условия;"условие")

Итак, у нас массив, где ИСТИНА / TRUE будет только там, где выполняется наше условие. Нам осталось скрестить два массива - с видимыми числами и этот с истинами. Можно их перемножить. Тогда на выходе получим только числа в тех строках, где выполняется условие (потому что там ИСТИНА) и где есть число (а не ноль, который получился в результате работы функции SUBTOTAL).

=MAP(диапазон с числами;
LAMBDA(ячейка;SUBTOTAL(4;ячейка))) * (диапазон условия="условие")

И последний шаг — просуммировать это безобразие:

=СУММ(MAP(диапазон с числами;
LAMBDA(ячейка;SUBTOTAL(4;ячейка))) * (диапазон условия="условие"))

Условие, конечно, можно брать и из ячейки:

Также можно отобрать ячейки по условию с помощью функции FILTER:

=СУММ(FILTER(MAP(диапазон с числами;
LAMBDA(ячейка;SUBTOTAL(4;ячейка));диапазон условия="условие"))

Этот пример в Google Таблицах

Google Таблица с примером

Рубрики:
Комментарии
Подписаться
Уведомить о
0 комментариев
Межтекстовые Отзывы
Посмотреть все комментарии
Темы, на которые я пишу: