backtotop

Categories: Excel/ Формулы

Если у вас есть таблица данных, то посчитать сумму по столбцу можно обычной функцией =СУММ(). Вы наверное, замечали, что в случае отбора фильтром определенных значений такая функция работает неверно. Как посчитать сумму только отобранных значений. Для этого есть возможность создать промежуточные итоги в Excel.

Промежуточные итоги в Excel

Функция промежуточные итоги в Excel имеет 2 аргумента:

1. Номер_функции. Это функция которой нужно воспользоваться для расчета итогов.

Промежуточные итоги в Excel

2. Ссылка. Это тот диапазон по которому нужно найти итог.

Пример. Посчитайте сумму по столбцу Сумма для всех месяцев где продано больше 20 единиц. 

Как вы видите на первой картинке, мы отобрали фильтром все значение по столбцу «Продано единиц» большие 20. В ячейки С15 записываем формулу вида

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C13)

Значение функции будет меняться если изменить условия фильтра.

Пример

Прошу не забывать, что это же действие можно сделать функцией =СУММЕСЛИ()

Промежуточные итоги в Excel, сумма только видимых ячеек

Как посчитать только те ячейки, которые вы не делали скрытыми?

Интересно, что в списке номеров функций для формулы промежуточных итогов, на картинке выше, нет интересной функции. Что подсчитать сумму всех видимых ячеек необходимо воспользоваться кодом 109.

Промежуточные итоги в Excel. Создать таблицу на отдельном листе

т.е. с промежуточными итогами по каждой группе строк, например по полугодиям в нашем примере.

Промежуточные итоги в Excel

Что нужно сделать:

1. Убедитесь что данные отсортированы, хотя бы по одному столбцу

2. Выберите диапазон

3. Зайдите в ленте инструментов в меню Данные, раздел Структура и выберите Промежуточные итоги

Промежуточные итоги в Excel

В открывшемся меню делаем настройки, выбираем по какому столбцу сортироваться (Полугодие) и что делать (Операция), а так же в каком столбце проводить расчет:

Промежуточные итоги в Excel

Должно получиться как на рисунке выше.

Пример2

Очень удобно, когда таблица большая и нужно отсортировать по номенклатуре, кварталу, счету и т.д.

Важно отметить:

— при таком способе расчета итогов Excel группирует данные (плюсы слева на панели), т.е. вы можете скрывать лишние данные. Подробнее здесь

— если используете возможность Таблица (Вставка -Таблица) в эту интерактивную таблиц при расчете суммы автоматом добавляется «Строка итогов». Которая во многом заменяет промежуточные итоги. В открывшейся вкладке Работа с таблицами – Конструктор вы можете настроить интерактивную таблицу почти под любые условия. Но я, честно говоря, пока почти не использую эту возможность, т.к. такая таблица даже на 10 тысяч строк значительно грузить систему. Хотя в Excel я работаю до 500 тыс. строк без проблем, даже без PowerPivot.

.

.

.


Comments

Там нет комментариев

Leave a Comment:

Ваш e-mail не будет опубликован. Обязательные поля помечены *

10 + 17 =