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

Способ 1. Формулы

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

Нарастающие итоги формулой в 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):

Загружаем данные в Power Query

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

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

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

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

Ссылка на нужный столбец-список в Power Query

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

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

Добавляем вычисляемый столбец для нарастающего итога

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

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

Готовый нарастающий итог в Power Query

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

Задача решена.

Форсаж

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

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

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

Исходный код запроса

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

Буферизованный запрос

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

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

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




Наверх