Как посчитать сумму в столбце в excel с фильтрами

Сумма по фильтру в Эксель (Excel) — как найти?

Как в программе Эксель (Excel) найти сумму ячеек, которые были отфильтрованы?

Например, если в столбце таблицы 100 ячеек, а после применения фильтра осталось 10, то как можно легко и быстро посчитать сумму этих 10 ячеек?

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

, где первый параметр — кодовое обозначение функции, которая будет подсчитываться (здесь — 9, код функции "СУММ"), а второй параметр — это весь диапазон, для которого нужно подсчитать сумму отфильтрованных значений. Более подробно см. в справке по данной функции.

Посчитать сумму по столбцу в таблице в Эксель очень просто.

Для подсчета необходимо выделить нужные нам ячейки. В результате выделения ячеек внизу таблицы будет отображаться следующая информация: «среднее», «количество» и «сумма».

Если говорить о формуле для определения количества, то она следующая:

В формуле первым параметром является кодовое обозначение функции. В нашем случае код это "СУММ" — 109, вторым параметром в формуле является диапазон ячеек из таблицы.

Сумма отфильтрованных ячеек в Excel

При работе в Excel иногда бывает необходимо не только отфильтровать данные в таблице, но и найти сумму по одному или нескольким столбцам, к которым был применён фильтр.

Расскажу как это можно сделать на конкретном примере.

Пусть исходная таблица будет такая:

Нужно оставить только фрукты и посчитать их суммарную стоимость.

I. Сначала устанавливаем фильтр: на панели инструментов Excel "Главная" нажимаем на "Сортировка и фильтр" -> "Фильтр", а затем щёлкаем на появившемся треугольнике в столбце "Фрукты / овощи".

Снимаем галочку с "Овощи" и нажимаем на "ОК".

Останутся только фрукты.

II. Вообще, сумма при фильтре отображается в строке состояния Excel — достаточно просто выделить нужный столбец.

То есть все фрукты стоят суммарно 430 рублей.

Но если эта цифра очень большая (например, миллионы или миллиарды) и вам её необходимо использовать где-то ещё, то вариант со строкой состояния не подойдёт — ведь придётся потом вручную набирать эти цифры.

Поэтому можно сделать так:

1) Поставить курсор в нижестоящую ячейку и на панели инструментов "Главная" нажать на "Автосумма".

2) Появится формула с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ

3) Нажимаем на клавишу Enter и получаем нужную нам сумму.

Это значение можно использовать в формулах и копировать куда угодно.

Если же вы поставите в фильтре другое условие, то сумма автоматически пересчитается.

В частности, в рассматриваемом примере можно поменять условие с "Фрукты" на "Овощи":

Также замечу, что в некоторых случаях для подсчёта суммы отдельных ячеек можно не фильтровать данные и не находить сумму по фильтру, а использовать функцию СУММЕСЛИ.

У этой функции 3 аргумента:

1) Диапазон — это диапазон ячеек, которые нужно проверить (удовлетворяют ли они какому-либо условию или нет).

В нашем случае это столбец "Фрукты / овощи" (B).

2) Критерий — указывается нужное условие.

В нашем случае это "Фрукты" (в самом мастере формул пишется без кавычек).

3) Диапазон_суммировани­ я — столбец со значениями, которые нужно суммировать.

В нашем случае это столбец с ценой (C).

Все достаточно просто. Нужно поступить таким образом: во-первых, выделить нужные ячейки.

Тогда внизу в строке состояния справа можно увидеть подсчитанные уже среднее, количество, а также сумму для выделенных нами мышкой значений. Ну, а можно воспользоваться и следующим приемом для этого выбрать формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ­ (параметр; диапазон ячеек)

, здесь 1 параметр — кодовое обозначение функции, которая будет подсчитываться (например — 9, код функции "СУММ"), а 2 параметр — это тот диапазон, для которого нужно подсчитать сумму значений, или среднее , или еще что нам нужно будет. 9 — код функции сумм для итога, включая скрытые строки, 109 — без скрытых строк.

Вычислить сумму в программе Excel очень просто, для этого вы можете выбрать все нужные ячейки и увидеть результаты в правом нижнем углу. Если вам нужно вычислить ячейку, к которой сложно применить фильтры, просто выберите и добавьте их, для этого пишем =ячейка+ячейка и нажимаем Ввод. Все формулы в программе Эксель должны начинаться со знака равенства.

Кроме того, можно использовать специальную формулу под названием промежуточные итоги. ля этого, заходим в структуру группы данных, нажимаем на промежуточные итоги. Его особенность в том, что в нём нет горизонтальных столбцов, только вертикальные столбцы. Это значительно упрощает работу в программе.

Как посчитать сумму в столбце в excel с фильтрами

Сегодня поговорим об отсутствующей функции в EXCEL– симбиозу СУММЕСЛИ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ . ( суммирование с условием на фильтре ).

Попросту, ответим на вопрос – как просуммировать или подсчитать значения с определенным условием, но только те, которые выбраны на фильтре ?

К сожалению в EXCEL нет номера функции в ПРОМЕЖУТОЧНЫЕ.ИТОГИ , соответствующей функции СУММЕСЛИ . ПРОМ.ИТОГИ суммируют все , что на фильтре без каких-либо условий.

В сети есть определенные решения с использованием функций массива и т.п., но они достаточно сложны для понимания и корректировки. Поэтому разделим задачу на 2 части .

Разберем пример с статусами обработки поступающих заявок. Создадим выражение, подсчитывающее к-во заявок в работе по тем критериям, которые выбраны на фильтре.

1. Добавим вспомогательный столбец «Признак» и введем функцию =ЕСЛИ(F8=1;1;»») , которая дает нам «1», если условие будет соблюдено (Ячейка=1) или пусто во всех остальных случаях «». Протягиваем формулу до конца списка.
   
2. В отдельную ячейку вводим функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ , выбираем номер функции «9» , т.е. СУММ и при очищенно фильтре выбираем весь диапазон в столбце признак. Именно эта функция и будет подсчитывать к-во заявок, соответствующих нашему условию.
   

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

Как сохранить фильтр в Excel используя представления данных

Допустим каждый месяц нам нужно создать отчет о результатах в продажах нашего отдела. Для подготовки отчета используем статистические показатели по всех транзакциях в журнале истории взаиморасчетов с клиентами, выполненных за прошедший период. Чтобы экспонировать данные по конкретному клиенту, например, для сравнения, необходимо каждый раз фильтровать таблицу истории взаиморасчетов под разными критериями. Нет необходимости тратить время и усилия на многоразовое фильтрование тех самых данных. Можно существенно облегчить решение данной задачи сгруппировав данные и сохранив их как нестандартный вид представления. Благодаря этому одним кликом мышки можно детально экспонировать необходимые данные на данный момент.

Как быстро пользоваться фильтром в Excel

Допустим наш журнал истории взаиморасчетов с клиентами отображается также как на рисунке.

История продаж и взаиморасчетов по клиентам:

Нам необходимо отобразить разные варианты данных с разделением на группы: название фирм, городов и сумм. К последней группе еще хотим добавить итоговый показатель.

Начнем работу с фильтрования данных, а потом разберем как их сохранять в разных режимах фильтра. Каждая группа будет записана как пользовательский вид представления данных на рабочем листе Excel.

Допустим первая группа данных должна включать в себя данные касающиеся транзакций сумой выше 20 000 рублей реализованной фирмой ADEX Comp. Для этого:

1. Выделите заголовок таблицы «Сумма» D1 и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр». Для исходной таблицы включиться режим автофильтра и в ее заголовках появятся кнопки выпадающего меню.
2. Откройте выпадающее меню ячейки D1 и выберите опцию «Числовые фильтры»-«Настраиваемый фильтр». В результате появиться диалоговое окно «Пользовательский автофильтр».
3. В первом выпадающем списке данного окна укажите на значение «больше», а в поле ввода напротив введите значение 20000. И нажмите ОК.
4. Теперь раскройте выпадающее меню в ячейке A1 («Название фирмы») и выберите только фирму ADEX Comp.

Применено второе условие фильтрования данных.

Как посчитать сумму по фильтру в Excel

Отфильтрованная группа готова, осталось только добавить итоговое значение для столбца «Сумма». Для этого выберите первую пустую ячейку под этим столбцом и выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сумма» или нажмите комбинацию клавиш CTR+=. После нажатия на клавишу Enter суммируются только видимые значения в столбце. По завершению присвойте для этой ячейки денежный формат.

Обратите внимание! Вместо стандартной функции СУММ мы используем функцию: Из ходя из названия сложно догадаться что данная функция будет суммировать значения. Но если в первом аргументе функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ находиться константа с кодом номер 9 – это значит, что мы подключаем встроенную функцию суммирования. Таким образом мы выполняем суммирование только промежуточных итогов, а не все значения в столбце. Стоит рассмотреть все встроенные функции в ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которые можно выполнять с отфильтрованными значениями в таблице:

1. – среднее значение, альтернатива функции СРЕДЗНАЧ.
2. – подсчет количества значений, альтернатива для СЧЁТ.
3. – подсчет только непустых ячеек в диапазоне, как СЧЁТЗ.
4. – возвращает максимальное значение (МАКС).
5. – возвращает минимальное значение (МИН).
6. – возвращает произведение аргументов (ПРОИЗВЕД).
7. – функция стандартного отклонения как СТАНДОТКЛОН.
8. – стандартное отклонение по генеральной совокупности (логические и текстовые значения – игнорируются) как в СТАНДОТКЛОНП.
9. – суммирует значение как СУММ.
10. – дисперсия по выборке как ДИСП.
11. – дисперсия для генеральной совокупности ДИСПР.

Примечание. Номера констант могут быть с включением и с исключением значений в скрытых строках инструментом: «ГЛАВНАЯ»-«Ячейки»-«Формат»-«Скрыть или отобразить». Отличие заключаться в коде константы:

• 1-11 – с включением;
• 101-111 – с исключением.

Если мы хотим получить суммирование итогов для другой фирмы, достаточно только изменить критерий фильтра, а итоговый результат автоматически просчитается благодаря функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Сохранение фильтра в представление данных

Когда первый набор значений для группы готовый, можно его сохранить как один из видов таблицы журнала истории взаиморасчетов по клиентам. Благодаря этому мы получим молниеносный доступ к организации новой структуры данных в таблице. Для этого:

1. Отфильтруйте таблицу по выше описанным критериям и выберите инструмент: «ВИД»-«Режимы просмотра книги»-«Представления».
2. В появившемся диалоговом окне «Представления» нажмите на кнопку «Добавить». Появиться новое окно «Добавление представления».
3. В поле «Имя:» введите название «ADEX Comp >20 тыс.руб.» для этого представления данных на рабочем листе и нажмите ОК.
4. Создайте новое представление для нового отображения таблицы с другими критериями фильтрования и придумайте им новые названия.

Теперь достаточно выбрать любой из списка видов представления данных на листе, используя выше указанный инструмент: «ВИД»-«Режимы просмотра книги»-«Представления». При выборе любого из преставлений данные на листе будут автоматически преобразовываться и группироваться в ранее сохраненный их вид. Теперь нет необходимости каждый раз заново создавать сложные фильтры по множеству критериев для их определения. Достаточно переключиться между видами представлений. Указать на представление в списке и нажать на кнопку «Применить».

Чтобы удалить представление снова вызовите диалоговое окно: «ВИД»-«Режимы просмотра книги»-«Представления». Потом выделите в списке ненужный вид и нажмите на кнопку «Удалить».