Excel. Сумма значений при включенном фильтре.
В Excel’е есть функция «Автосумма», которая автоматически вычисляет сумму заданных ячеек. Но, как-то я обратил внимание, что при использовании фильтра данная функция не исключает из суммы скрытые (не попадающие под фильтр) ячейки. Поясню на примере.
Рассмотрим простейшую таблицу – список мужских и женских имен с указанием пола и какой-то числовой характеристики, например, суммы премии за месяц.
В ячейку C10 вписываем нашу формулу: «=СУММ(C2:C8)» и видим, что Excel вычислил общую сумму премии всех сотрудников. Однако попробуем применить фильтр – допустим мы хотим узнать сумму премии только для девушек.
Видим, что сумма не изменилась. Excel не исключает из суммы скрытые фильтром ячейки. А как сделать чтобы исключал? Ведь нам нужна сумма значений колонки только для видимых строк.
Для этого необходимо использовать функцию «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Синтаксис у нее схожий с функцией «СУММ», только первым аргументом необходимо указать номер основной функции используемой при вычислении результата – т.е. в нашем случае это сама «СУММ» — номер 9.
Номера функций можно узнать, если поставить курсор в соответствующую позицию в формуле. В итоге, формула, которая решает нашу задачу, будет выглядеть так: «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C8)».
Осталось заметить, что если использовать «Автосумму» ДО применения фильтра, то получим вышеописанную ситуацию. Т.е. будет использоваться функция «СУММ». Если же использовать «Автосумму» уже ПОСЛЕ применения фильтра, то будет использоваться функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Т.е. в нашем случае достаточно было сначала применить фильтр на девушек, а потом уже вставлять «Автосумму» в ячейку C10. После этого фильтр можно использовать как угодно – сумма будет вычисляется правильно.
Как посчитать сумму в столбце в excel по фильтру
- Советы по Excel
- Функции Excel
- Формулы Excel
- Советы по словам
- Советы по Outlook
Как подсчитать / суммировать ячейки на основе фильтра с критериями в Excel?
На самом деле, в Excel мы можем быстро подсчитать и суммировать ячейки с помощью функций СЧЁТ и СУММ в нормальном диапазоне данных, но эти функции не будут работать правильно в случае фильтрации. Чтобы подсчитать или суммировать ячейки на основе фильтра или фильтра с критериями, эта статья может оказать вам услугу.
Следующие формулы могут помочь вам быстро и легко подсчитать или суммировать отфильтрованные значения ячеек. Сделайте следующее:
Чтобы подсчитать количество ячеек из отфильтрованных данных, примените эту формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (3; C6: C19) (C6: C19 это диапазон данных, который отфильтрован, из которого вы хотите вести подсчет), а затем нажмите Enter ключ. Смотрите скриншот:
Чтобы суммировать значения ячеек на основе отфильтрованных данных, примените эту формулу: = ПРОМЕЖУТОЧНЫЙ ИТОГ (9; C6: C19) (C6: C19 это диапазон данных, который отфильтрован, вы хотите суммировать), а затем нажмите Enter ключ. Смотрите скриншот:
Если у вас есть Kutools for Excel, Countvisible и Sumvisible функции также могут помочь вам сразу подсчитать и суммировать отфильтрованные ячейки.
После установки Kutools for Excel, введите следующие формулы для подсчета или суммирования отфильтрованных ячеек:
Подсчитайте отфильтрованные ячейки: = СОВМЕСТНО (C6: C19)
Суммируйте отфильтрованные ячейки: = СУММВИДИМЫЕ (C6: C19)
Советы: Вы также можете применить эти функции, нажав Kutools > Kutools Функции > Статистические и математические > СРЕДНЕВИДИМЫЙ / СОВМЕСТНО / СУМВИДИМЫЙ как вам нужно. Смотрите скриншот:
Иногда в отфильтрованных данных вы хотите подсчитать или суммировать на основе критериев. Например, у меня есть следующие отфильтрованные данные. Теперь мне нужно подсчитать и суммировать заказы с именем «Нелли». Здесь я представлю несколько формул для ее решения.
Подсчет ячеек на основе данных фильтра с определенными критериями:
Пожалуйста, введите эту формулу: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)), —( B6:B19="Nelly")) (B6: B19 это отфильтрованные данные, которые вы хотите использовать, а текст Нелли является критерием, по которому вы хотите вести подсчет), а затем нажмите Enter ключ для получения результата:
Суммируйте ячейки на основе данных фильтра с определенными критериями:
Чтобы суммировать отфильтрованные значения в столбце C на основе критериев, введите следующую формулу: =SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),( B6:B19="Nelly")*(C6:C19)) (B6: B19 содержит критерии, которые вы хотите использовать, текст Нелли это критерии, и C6: C19 — значения ячеек, которые вы хотите суммировать), а затем нажмите Enter чтобы вернуть результат, как показано на следующем снимке экрана: