Суммирование нарастающим итогом в EXCEL
Пусть дана таблица продаж товара по месяцам (см. файл примера ).
В соседнем столбце будем суммировать продажи нарастающим итогом. Для этого в ячейке С2 введем формулу =СУММ($B$2:B2) и скопируем ее вниз Маркером заполнения . Обратите внимание, что в формуле использована абсолютная и относительная адресация .
При копировании формулы в ячейки ниже первая ссылка на ячейку B2 не изменяется, т.к. она введена с помощью абсолютной адресации $B$2 . Т.к. копирование ведется только в нижние ячейки, то достаточно было написать знак $ только перед номером строки, т.е. B$2. Напротив, вторая ссылка на ячейку B2 при копировании формулы внизменяется, т.к. она введена с помощью относительной адресации B2 .
В итоге, при копировании формулы из С2 в ячейку С3 формула примет вид =СУММ($B$2:B3) , а в С8 она будет =СУММ($B$2:B8)
Как сказано выше, формула =СУММ(B$2:B2) также будет работать.
Нарастающий итог в 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
Обычная сумма суммирует все ячейки в заданном диапазоне. Но иногда нужно, чтобы в каждой следующей ячейке была сумма предыдущих и еще одной. В этом нам поможет абсолютная ссылка $C$6. В результате при копировании диапазона он будет расширяться, а первое значение останется неизменным:
Оцените статью
Добавить комментарий Отменить ответ
Обо мне и сайте
Привет! Меня зовут Якушев Дмитрий! Моя цель — помочь тебе изучить Excel.
На этом сайте буду выкладывать полезные статьи по Excel, VBA с примерами и трюками. Делаю это, чтобы помочь тебе лучше узнать Excel. Читай, прокачивай навыки, чтобы не тратить кучу времени на неинтересную рутину. Вот тут можно прочитать обо мне.
Нарастающий итог в Excel
А вы знаете как в таблице Excel быстро посчитать нарастающий итог?
В первой ячейке диапазона составим формулу с функцией СУММ() в которой границы диапазона будут состоять из одной и той же ячейки.
Но! Первую ссылку на ячейку мы зафиксируем, а вторую оставим абсолютной.
Больше лайфхаков в нашем Telegram Подписаться
Так, при протягивании формулы на соседние ячейки диапазон будет расширяться.
Протянем формулу вниз.
Нарастающий итог готов.