Как в excel посчитать сумму столбца с учетом фильтра
В Excel’е есть функция «Автосумма», которая автоматически вычисляет сумму заданных ячеек. Но, как-то я обратил внимание, что при использовании фильтра данная функция не исключает из суммы скрытые (не попадающие под фильтр) ячейки. Поясню на примере.
Рассмотрим простейшую таблицу – список мужских и женских имен с указанием пола и какой-то числовой характеристики, например, суммы премии за месяц.
В ячейку C10 вписываем нашу формулу: «=СУММ(C2:C8)» и видим, что Excel вычислил общую сумму премии всех сотрудников. Однако попробуем применить фильтр – допустим мы хотим узнать сумму премии только для девушек.
Видим, что сумма не изменилась. Excel не исключает из суммы скрытые фильтром ячейки. А как сделать чтобы исключал? Ведь нам нужна сумма значений колонки только для видимых строк.
Для этого необходимо использовать функцию «ПРОМЕЖУТОЧНЫЕ.ИТОГИ». Синтаксис у нее схожий с функцией «СУММ», только первым аргументом необходимо указать номер основной функции используемой при вычислении результата – т.е. в нашем случае это сама «СУММ» — номер 9.
Номера функций можно узнать, если поставить курсор в соответствующую позицию в формуле. В итоге, формула, которая решает нашу задачу, будет выглядеть так: «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C8)».
Суммирование только видимых ячеек
Некоторые из приведенных ниже способов чувствительны к этим факторам, некоторые – нет. Это нужно учитывать при выполнении вычислений:
СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 9 в первом аргументе – суммирует все видимые после фильтра ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.
Если нужно не суммировать, то можно использовать другие значения кода математической операции:
АГРЕГАТ (AGGREGATE) – самая мощная функция, появившаяся в Office 2010. Также как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ может не только суммировать, но и считать среднее, количество, минимум, максимум и т.д. — код операции задается первым аргументом. Плюс к этому имеет множество опций по подсчету, которые можно указать вторым аргументом:
Сумма по фильтру в Эксель (Excel) — как найти?
Как в программе Эксель (Excel) найти сумму ячеек, которые были отфильтрованы?
Например, если в столбце таблицы 100 ячеек, а после применения фильтра осталось 10, то как можно легко и быстро посчитать сумму этих 10 ячеек?
Проще всего — просто выделить эти ячейки. Тогда внизу для вас отобразится среднее, количество и сумма именно для отфильтрованных значений. Если же вам нужна формула, то придется использовать формулу ПРОМЕЖУТОЧНЫЕ.ИТОГ И, например
, где первый параметр — кодовое обозначение функции, которая будет подсчитываться (здесь — 9, код функции «СУММ»), а второй параметр — это весь диапазон, для которого нужно подсчитать сумму отфильтрованных значений. Более подробно см. в справке по данной функции.
Посчитать сумму по столбцу в таблице в Эксель очень просто.
Для подсчета необходимо выделить нужные нам ячейки. В результате выделения ячеек внизу таблицы будет отображаться следующая информация: «среднее», «количество» и «сумма».
Если говорить о формуле для определения количества, то она следующая:
В формуле первым параметром является кодовое обозначение функции. В нашем случае код это «СУММ» — 109, вторым параметром в формуле является диапазон ячеек из таблицы.
При работе в Excel иногда бывает необходимо не только отфильтровать данные в таблице, но и найти сумму по одному или нескольким столбцам, к которым был применён фильтр.
Расскажу как это можно сделать на конкретном примере.
Нужно оставить только фрукты и посчитать их суммарную стоимость.
I. Сначала устанавливаем фильтр: на панели инструментов Excel «Главная» нажимаем на «Сортировка и фильтр» -> «Фильтр», а затем щёлкаем на появившемся треугольнике в столбце «Фрукты / овощи».
II. Вообще, сумма при фильтре отображается в строке состояния Excel — достаточно просто выделить нужный столбец.
Но если эта цифра очень большая (например, миллионы или миллиарды) и вам её необходимо использовать где-то ещё, то вариант со строкой состояния не подойдёт — ведь придётся потом вручную набирать эти цифры.
1) Поставить курсор в нижестоящую ячейку и на панели инструментов «Главная» нажать на «Автосумма».
3) Нажимаем на клавишу Enter и получаем нужную нам сумму.
Это значение можно использовать в формулах и копировать куда угодно.
Если же вы поставите в фильтре другое условие, то сумма автоматически пересчитается.
В частности, в рассматриваемом примере можно поменять условие с «Фрукты» на «Овощи»:
Также замечу, что в некоторых случаях для подсчёта суммы отдельных ячеек можно не фильтровать данные и не находить сумму по фильтру, а использовать функцию СУММЕСЛИ.
1) Диапазон — это диапазон ячеек, которые нужно проверить (удовлетворяют ли они какому-либо условию или нет).
В нашем случае это «Фрукты» (в самом мастере формул пишется без кавычек).
3) Диапазон_суммировани я — столбец со значениями, которые нужно суммировать.
Посчитать сумму в программе Excel очень просто для этого можно выделить все нужные ячейки и посмотреть результаты внизу в правом углу.
Если нужно посчитать ячейку, фильтры к которым применить сложно, то просто выделим и сложим их, для этого пишем = ячейка + ячейка и нажимаем Enter. Напомню, что все формулы в программе должны начинаться со знака равно.
Кроме этого можно воспользоваться специальной формулой, называется она промежуточные итоги.
Заходим в Структуру из группы данные, нажимаем промежуточные итоги. Особенность её в том, что она не считает горизонтальные столбцы, а только вертикальные.
Суммирование несвязанных диапазонов
Простые логические функции такие как ЕСЛИ обычно предназначены для работы с одним столбцом или одной ячейкой. Excel также предлагает несколько других логических функций служащих для агрегирования данных. Например, функция СУММЕСЛИ для выборочного суммирования диапазона значений по условию.
