Как в экселе сделать итого

Нарастающий итог в Excel

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

• Таблица обязательно должна быть отсортирована по дате.
• При добавлении новых строк с данными формулу придётся допротягивать вручную.

Способ 2. Сводная таблица

Этот способ чуть сложнее, но гораздо приятнее. Причём для усугубления давайте рассмотрим задачку посерьезнее — таблицу из 2000 строк с данными, где по столбцу даты нет сортировки, зато есть повторы (т.е. мы можем несколько раз продать в один и тот же день):

Конвертируем нашу исходную таблицу в «умную» (динамическую) сочетанием клавиш Ctrl + T или командой Главная — Форматировать как таблицу (Home — Format as Table) , а затем строим по ней сводную таблицу командой Вставка — Сводная таблица (Insert — Pivot Table) . В область строк в сводной закидываем дату, а в область значений — число проданных товаров:

Обратите внимание, что если у вас не совсем старая версия Excel, то даты автоматически сгруппируются по годам, кварталам и месяцам. Если вам нужна другая группировка (или она совсем не нужна), то это можно исправить, щёлкнув правой кнопкой мыши по любой дате и выбрав команды Группировать / Разгруппировать (Group / Ungroup) .

Если хочется видеть одновременно и получившиеся итоги по периодам и нарастающий итог в отдельном столбце, то имеет смысл закинуть в область значений поле Продано ещё раз, чтобы получить дубликат поля — в нём мы и включим отображение нарастающих итогов. Для этого щёлкните правой кнопкой мыши по полю и выберите команду Дополнительные вычисления — Нарастающий итог (Show Values as — Running Totals) :

Там же можно выбрать и вариант нарастающих итогов в процентах, а в следующем окне нужно выбрать поле, по которому пойдет накопление — в нашем случае это поле даты:

Плюсы такого подхода:

• Быстро считается большой объем данных.
• Не нужно вводить вручную никаких формул.
• При изменении в исходных данных достаточно обновить сводную правой кнопкой мыши или командой Данные — Обновить всё (Data — Refresh All).

Способ 3. Запрос Power Query

Загрузим нашу «умную» таблицу с исходными данными в редактор запросов Power Query, используя команду Данные — Из таблицы / диапазона (Data — From Table/Range) . В последних версиях Excel её, кстати, переименовали — теперь она называется С листа (From Sheet) :

Затем выполним следующие шаги:

1 . Отсортируем таблицу по возрастанию по столбцу даты командой Сортировать по возрастанию в выпадающем списке фильтра в шапке таблицы.

2 . Чуть позже для подсчета нарастающего итога нам потребуется вспомогательный столбец с порядковым номером строки. Добавим его командой Добавление столбца — Столбец индекса — От 1 (Add column — Index column — From 1) .

3 . Также для подсчёта нарастающего итога нам потребуется ссылка на столбец Продано, где лежат наши суммируемые данные. В Power Query столбцы ещё называются списками (list) и чтобы получить ссылку на него, щёлкнем правой кнопкой мыши по заголовку столбца и выберем команду Детализация (Show detail) . В строке формул появится нужное нам выражение, состоящее из имени предыдущего шага #»Добавлен индекс», откуда мы берём таблицу и имени столбца [Продано] из этой таблицы в квадратных скобках:

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

4 . Удаляем ненужный более последний шаг Продано и добавляем вместо него вычисляемый столбец для подсчета нарастающего итога командой Добавление столбца — Настраиваемый столбец (Add column — Custom column) . Нужная нам формула будет выглядеть так:

Здесь функция List.Range берёт исходный список (столбец [Продано]) и извлекает из него элементы, начиная с первого (в формуле это 0, т.к. нумерация в Power Query начинается с нуля). Количество извлекаемых элементов — это номер строки, который мы берём из столбца [Индекс]. Таким образом, эта функция для первой строки возвращает только одну первую ячейку столбца Продано. Для второй строки — уже первых две ячейки, для третьей — первых три и т.д.

Ну, а затем функция List.Sum суммирует извлечённые значения и мы получаем в каждой строке сумму всех предыдущих элементов, т.е. нарастающий итог:

Осталось удалить ненужный нам больше столбец Индекс и выгрузить результаты обратно в Excel командой Главная — Закрыть и загрузить (Home — Close&Load to).

Форсаж

В принципе, на этом можно было бы и остановиться, но есть небольшая ложка дёгтя — созданный нами запрос работает со скоростью черепахи. Для примера, на моем не самом слабом ПК таблица всего в 2000 строк обрабатывается 17 секунд. А если данных будет больше?

Для ускорения можно использовать буферизацию при помощи специальной функции List.Buffer, которая загружает заданный ей в качестве аргумента список (list) в оперативную память, что сильно ускоряет обращение к нему в дальнейшем. В нашем случае имеет смысл буферизовать список #»Добавлен индекс»[Продано], к которому Power Query вынужден обращаться при расчёте нарастающего итога в каждой строке нашей 2000-строчной таблицы.

Для этого в редакторе Power Query на Главной вкладке жмём кнопку Расширенный редактор (Home — Advanced Editor), чтобы открыть исходный код нашего запроса на встроенном в Power Query языке М:

А затем добавляем туда строку с переменной MyList, значение которой возвращает функция буферизации и подменяем на следующем шаге обращение к списку на эту переменную:

После внесения таких изменений наш запрос станет существенно резвее и справится с 2000-строчной таблицей всего за 0.3 секунды!

Другое дело, правда? 🙂

Ссылки по теме

• Диаграмма Парето (80/20) и как построить её в Excel
• Поиск ключевых слов в тексте и буферизация запроса в Power Query

Функция Excel Промежуточные.итоги()

Функция «Промежуточные итоги()» используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые сроки.

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами Excel (например, автофильтрами).

Для демонстрации пользы от функции Excel Промежуточные.итоги() рассмотрим урезанную выгрузку из базы мероприятий.

Задача: по выбранным параметрам оценить ключевые показатели в сравнении с ситуацией по всем направлениям.

Мероприятия можно фильтровать по разным параметрам, но нам для сравнения хотелось бы понимать, показатели по отфильтрованным параметрам и сравнивать с ситуацией в целом по всем направлениям.

Для этого можно воспользоваться функцией Excel =Промежуточные.итоги(). Функция =Промежуточные.итоги() делает расчет по отфильтрованным значениям.

В формуле можно задать расчет:

• Средней – номер 1 или 101 – для выборки
• Счет значений – номер 3 или 103
• Сумма — 9 или 109
• И т.д.

• Если номер функции трехзначный, например, 109, то функция работает как промежуточные итоги, т.е. рассчитывает значения по отфильтрованным параметрам.
• Если номер однозначный или двузначный 2 или 11, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.

Перейдем к нашим мероприятиям:

У нас есть выгрузка из базы по следующим столбцам:

• Ситуация
• Действия
• Кол-во клиентов
• Объём_Продаж_До
• Объём_Продаж_План
• Объём_Продаж_Факт
• Затраты_План
• Затраты_Факт

Для оценки ситуации в целом введем стандартную формулу суммирования:

Для расчета промежуточных итогов по выбранным параметрам введем =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;R[3]C:R[65533]C), где

• 109 – функция суммы для расчета промежуточных итогов;
• R[3]C:R[65533]C – ссылка на диапазон суммирования.

Теперь, отфильтровав столбец «Ситуация» — «Ситуация 4» и «Действие» — Действие 2 и 3, мы получим перерасчет промежуточных итогов по каждому из показателей и можем их сравнить с показателями «Итого»:

В результате: видно, что % прироста продаж по отфильтрованным мероприятиям выше, чем по всем, причем планировали результаты получить ниже, чем по итого, а получили выше. Т.е. сработали лучше, чем планировали и лучше чем по всем мероприятиям.

Формула Excel =промежуточные.итоги() – отличный инструмент для расчета показателей по отфильтрованным параметрам.

Если есть вопросы — обращайтесь!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel .
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Промежуточные итоги в Excel с примерами функций

Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».

Важное условие применения средств – значения организованы в виде списка или базы данных, одинаковые записи находятся в одной группе. При создании сводного отчета промежуточные итоги формируются автоматически.

Вычисление промежуточных итогов в Excel

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

Необходимо подсчитать выручку от реализации отдельных групп товаров. Если использовать фильтр, то можно получить однотипные записи по заданному критерию отбора. Но значения придется подсчитывать вручную. Поэтому воспользуемся другим инструментом Microsoft Excel – командой «Промежуточные итоги».

Чтобы функция выдала правильный результат, проверьте диапазон на соответствие следующим условиям:

• Таблица оформлена в виде простого списка или базы данных.
• Первая строка – названия столбцов.
• В столбцах содержатся однотипные значения.
• В таблице нет пустых строк или столбцов.

1. Отсортируем диапазон по значению первого столбца – однотипные данные должны оказаться рядом.
2. Выделяем любую ячейку в таблице. Выбираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».
3. Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.
4. Закрываем диалоговое окно, нажав кнопку ОК. Исходная таблица приобретает следующий вид:

Если свернуть строки в подгруппах (нажать на «минусы» слева от номеров строк), то получим таблицу только из промежуточных итогов:

При каждом изменении столбца «Название» пересчитывается промежуточный итог в столбце «Продажи».

Чтобы за каждым промежуточным итогом следовал разрыв страницы, в диалоговом окне поставьте галочку «Конец страницы между группами».

Чтобы промежуточные данные отображались НАД группой, снимите условие «Итоги под данными».

Команда промежуточные итоги позволяет использовать одновременно несколько статистических функций. Мы уже назначили операцию «Сумма». Добавим средние значения продаж по каждой группе товаров.

Снова вызываем меню «Промежуточные итоги». Снимаем галочку «Заменить текущие». В поле «Операция» выбираем «Среднее».

Формула «Промежуточные итоги» в Excel: примеры

Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

Номер функции – число от 1 до 11, которое указывает статистическую функцию для расчета промежуточных итогов:

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

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

Особенности «работы» функции:

• выдает результат по явным и скрытым строкам;
• исключает строки, не включенные в фильтр;
• считает только в столбцах, для строк не подходит.

Рассмотрим на примере использование функции:

1. Создаем дополнительную строку для отображения промежуточных итогов. Например, «сумма отобранных значений».
2. Включим фильтр. Оставим в таблице только данные по значению «Обеденная группа «Амадис»».
3. В ячейку В2 введем формулу: .

Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .

Формула для максимального значения (для спален): .

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

В сводной таблице можно показывать или прятать промежуточные итоги для строк и столбцов.

1. При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.
2. Чтобы применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Параметры» находим группу «Активное поле». Курсор должен стоять в ячейке того столбца, к значениям которого будет применяться функция. Нажимаем кнопку «Параметры поля». В открывшемся меню выбираем «другие». Назначаем нужную функцию для промежуточных итогов.
3. Для выведения на экран итогов по отдельным значениям используйте кнопку фильтра в правом углу названия столбца.

В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».

Таким образом, для отображения промежуточных итогов в списках Excel применяется три способа: команда группы «Структура», встроенная функция и сводная таблица.

• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Как в экселе сделать итого

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

Итак, приступим. Есть у нас вот такая таблица:

Мы выделяем все числовые данные плюс одну строку снизу и один столбец справа, чтобы получилось так:

Переходим во вкладку «Главная» и в разделе «Редактирование» нажимаем кнопку «Автосумма«:

И MS Excel все сделал за нас:

Нам осталось только написать недостающие названия строки и столбца.

А если наша таблица выглядит чуть сложнее и нам недостаточно посчитать общие итоги? Давайте разберем такую таблицу:

Помимо общих итогов по столбцам и строкам, нам необходимо получить промежуточные итоги по менеджерам, чтобы было так:

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

В открывшемся диалоговом окне в списке «Добавить итоги по:» устанавливаем галочки напротив пунктов «Январь«, «Февраль«, «Март» и нажимаем кнопку «ОК«:

После чего наша таблица приобретет следующий вид:

Если вас «напрягают» плюсики и минусики, образовавшиеся слева, во вкладке «Данные» в разделе «Структура» нажмите кнопку «Разгруппировать«, в выпавшем списке нажмите пункт «Удалить структуру«:

Что делать дальше вы уже, наверное, догадались? Выделяем все числовые данные и один пустой столбец справа, во вкладке меню «Главная» в разделе «Редактирование» нажимаем «Автосумма«:

Таблица приобретет такой вид:

Форматируем необходимые строки и столбцы и получаем что хотели: