Суммирование только видимых ячеек
Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:
СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
Если нужно не суммировать, то можно использовать другие значения кода математической операции:
АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. — код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:
Ссылки по теме
- Выборочные вычисления по одному или нескольким условиям
- Вставка в отфильтрованные строки
- Быстрое скрытие и отображение ненужных строк и столбцов
Сумма видимых строк при фильтрации
Если вы хотите, чтобы суммировались только видимые строки в отфильтрованном списке (т.е. строки, которые не отфильтрованные), вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ с номером функции 9 или 109. ПРОМЕЖУТОЧНЫЕ.ИТОГИ особенно полезна в том, что она автоматически игнорирует строки, которые скрыты в отфильтрованном списке или таблице.
Следуя примеру, суммируя ячейки в столбце F для видимых строк, используйте формулу:
Если вы скрываете строки вручную (т.е. правой кнопкой мыши), используйте эту версию вместо той:
Изменяя номер функции, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнять множество других вычислений (например, СЧЕТ, СУММ, МАКС, МИН и т.д.).
Как получить СУММЕСЛИ только видимых ячеек?
https://docs.google.com/spreadsheets/d/1NU13NCXs4W.
Вот ссылка на таблицу.
Как получить сумму значений в ячейках при условии, что эти ячейки видимы после фильтра и удовлетворяют определенному значению в соседней ячейке?
Например, есть промежуточные итоги — она считает только видимые значения, но нет условия. Есть сумма если — есть условие но считает все значения и видимые и скрытые. Объединить не получается. Желательно решить этот вопрос без доп.списков и скриптов.
- Вопрос задан более трёх лет назад
- 1847 просмотров
Комментировать
Решения вопроса 0
Ответы на вопрос 1
Григорий Боев @ProgrammerForever Куратор тега Google Sheets
Учитель, автоэлектрик, программист, музыкант
Не совсем то, но думаю что поможет. Используейте FILTER(Диапазон;Условие1;Условие2;. УсловиеN) для подсчёта суммы.
например, в вашем случае, в B47 пишем:
=СУММ(FILTER($C$2:$C$45;$B$2:$B$45=A47))
Где A47 — фильтр-условие на диаметр трубопровода
А ещё лучше — сразу сделать так:
=ЕСЛИОШИБКА(СУММ(FILTER($C$2:$C$45;$B$2:$B$45=A47));0)Ответ написан более трёх лет назад
Комментировать
Нравится Комментировать
Сумма видимых строк. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Задача: функция СУММ суммирует все ячейки диапазона, являются ли они скрытыми или нет. Вы хотите суммировать только видимые строки.
Решение: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ вместо СУММ. Формула будет немного отличаться, в зависимости от того, как вы спрятали строки. Если вы выделили строки, кликнули правой кнопкой мыши, и в контекстном меню выбрали скрыть, можно использовать: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; диапазон) (рис. 1). Весьма необычно использовать для этих целей ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Как правило, эта функция нужна, чтобы Excel игнорировал другие подитоги внутри диапазона.
Рис. 1. Серия 100 в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ используется для обработки видимых строк
Скачать заметку в формате Word или pdf, примеры в формате Excel
ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнить 11 операций. Первый аргумент функции указывает ей на следующие операции: (1) СРЗНАЧ, (2) СЧЁТ, (3) СЧЁТЗ, (4) МАКС, (5) МИН, (6) ПРОИЗВЕД, (7) СТАНДОТКЛОН, (8) СТАНДОТКЛОНП, (9) СУММ, (10) ДИСП, (11) ДИСПР. При добавлении сотни выполняются те же операции, но только над видимыми ячейкам. Например, 104 найдет максимум среди видимых ячеек. Под видимыми имеется ввиду, не видимые на экране (например, 120 строк не уместятся на экране), а не скрытые, командой Скрыть.
В ячейке Е566 (см. рис. 1) используется формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;E2:E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, а именно – Е2;Е30;Е72;Е78;Е564.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к вертикальным наборам данных. Она не предназначена для горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы номер_функции от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;С2:F2) рис. 2), скрытие столбца не повлияет на результат.
Рис. 2. Формула не игнорирует ячейки в скрытых столбцах
Дополнительные сведения: существует необычное исключение в поведении функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Когда строки были скрыты по какой-либо из команд фильтра (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки даже в варианте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон). Нет необходимости использовать версию 109 (рис. 3). Здесь фильтр используется для поиска записей Chevron.
Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра
Почему я упоминаю об этой странности? Потому что есть малоизвестное сочетание клавиш для суммирования видимых строк, полученных в результате фильтрации. Попробуйте эти шаги:
- Выбрать любую ячейку в вашем наборе данных.
- Пройдите по меню ДАННЫЕ –>Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
- Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
- Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
- Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).
В Excel 2010 появилась еще одна подобная функция – АГРЕГАТ (подробнее см. Сравнение массивов и выборки по одному или нескольким условиям; раздел Функция АГРЕГАТ). Она имеет больше функций в своем «репертуаре» и больше опций, какие строки исключать, а какие обрабатывать. Основное ее достоинство – обработка ошибочных значений (например, #ДЕЛ/0!). К сожалению, эта функция также не применима к суммированию видимых столбцов.
Резюме: вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы игнорировать скрытые строки.