Содержание

Как в 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. Напомню, что все формулы в программе должны начинаться со знака равно.

Кроме этого можно воспользоваться специальной формулой, называется она промежуточные итоги.

Заходим в Структуру из группы данные, нажимаем промежуточные итоги. Особенность её в том, что она не считает горизонтальные столбцы, а только вертикальные.

[expert_bq id=»1570″]При выгрузке из других программ данные экспортируются в текстовом формате, о чем Excel нам сообщает зелененьким треугольничком. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Откроется окно функции, в котором обычно два поля для диапазона ячеек «Число 1» и «Число 2». Для простых операций достаточно одного поля. Ставим курсор в поле «Число 1» и обычным выделением при помощи мыши выбираем диапазон чисел в таблице. Ячейки с данными автоматически добавятся, достаточно нажать кнопку ОК в нижней правой части панели функций.