Способ 1. Формулы
Начнем, для разминки, с самого простого варианта – формул. Если у нас на входе имеется небольшая таблица, отсортированная по дате, то для расчета промежуточной суммы в отдельном столбце нам понадобится элементарная формула:
Главной особенностью здесь является хитрая фиксация диапазона внутри функции СУММ – ссылка на начало диапазона делается абсолютной (со знаками доллара), а на конец – относительной (без долларов). Соответственно, при копировании формулы на весь столбец мы получаем расширяющийся диапазон, сумму которого мы вычисляем.
Недостатки такого подхода очевидны:
- Таблица должна быть отсортирована по дате.
- При добавлении новых строк с данными формулу придется расширять вручную.
Способ 2. Сводная таблица
Этот способ немного сложнее, но гораздо приятнее. А для усугубления рассмотрим более серьёзную проблему — таблицу из 2000 строк данных, где нет сортировки по столбцу даты, но есть повторы (т.е. мы можем продавать несколько раз в один и тот же день):
Преобразуем нашу исходную таблицу в «умное» (динамическое) сочетание клавиш Ctrl+T или команда Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу), а затем строим по ней сводную таблицу командой Вставка — сводная таблица (Вставка — сводная таблица). Ставим дату в область строк в сводке, а количество проданных товаров в область значений:
Обратите внимание, если у вас не совсем старая версия Excel, то даты автоматически группируются по годам, кварталам и месяцам. Если вам нужна другая группировка (или она вообще не нужна), то это можно исправить, щелкнув правой кнопкой мыши по любой дате и выбрав команды Группировать/Разгруппировать (Группировать/Разгруппировать).
Если вы хотите видеть и итоговые итоги по периодам, и промежуточный итог в отдельном столбце, то имеет смысл перекинуть поле в область значений Продано еще раз получить дубликат поля – в нем включим отображение текущих итогов. Для этого щелкните правой кнопкой мыши по полю и выберите команду Дополнительные расчеты – накопительный итог (Показать значения как — текущие итоги):
Там же можно выбрать вариант роста итогов в процентах, а в следующем окне нужно выбрать поле, по которому будет идти накопление – в нашем случае это поле даты:
Преимущества такого подхода:
- Большой объем данных считывается быстро.
- Никакие формулы не нужно вводить вручную.
- При изменении исходных данных достаточно обновить сводку правой кнопкой мыши или командой Данные – Обновить все.
Недостатки вытекают из того, что это конспект, а значит, делать в нем все, что хочешь (вставлять строки, писать формулы, строить какие-то диаграммы и т. д.) уже не получится.
Способ 3: Power Query
Загрузим нашу «умную» таблицу с исходными данными в редактор запросов Power Query с помощью команды Данные – из таблицы/диапазона (Данные — из таблицы/диапазона). В последних версиях Excel его, кстати, переименовали – теперь он называется С листьями (Из листа):
Затем мы выполним следующие шаги:
1. Отсортируйте таблицу в порядке возрастания по столбцу даты командой Сортировать по возрастанию в раскрывающемся списке фильтров в заголовке таблицы.
2. Чуть позже для расчета промежуточной суммы нам понадобится вспомогательный столбец с порядковым номером строки. Добавим его командой Добавить столбец — индексный столбец — с 1 (Добавить столбец — Столбец индекса — С 1).
3. Также для расчета промежуточной суммы нам нужна ссылка на столбец Продано, где лежат наши обобщенные данные. В Power Query столбцы также называются списками (списками), и чтобы получить ссылку на них, щелкните правой кнопкой мыши заголовок столбца и выберите команду Детализация (Показать детали). В строке формул появится нужное нам выражение, состоящее из названия предыдущего шага. #”Индекс добавлен”, откуда берем таблицу и имя столбца [Продажи] из этой таблицы в квадратных скобках:
Скопируйте это выражение в буфер обмена для дальнейшего использования.
4. Удалите ненужный еще последний шаг Продано и добавьте вместо него вычисляемый столбец для расчета промежуточной суммы командой Добавление столбца – Пользовательский столбец (Добавить столбец — Пользовательский столбец). Нужная нам формула будет выглядеть так:
Здесь функция Список.Диапазон принимает исходный список (столбец [Продажи]) и извлекает из него элементы, начиная с первого (в формуле это 0, поскольку нумерация в Power Query начинается с нуля). Количество извлекаемых элементов — это номер строки, которую мы берем из столбца. [Показатель]. Таким образом, эта функция для первой строки возвращает только одну первую ячейку столбца. Продано. Для второй строки – уже первые две клетки, для третьей – первые три и т.д.
Ну, тогда функция Список.Сумма суммируем извлеченные значения и получаем в каждой строке сумму всех предыдущих элементов, т.е. нарастающий итог:
Осталось удалить ненужный нам столбец «Индекс» и загрузить результаты обратно в Excel командой «Главная» — «Закрыть и загрузить в».
Проблема решена.
Быстрый и яростный
В принципе, это можно было бы остановить, но есть маленькая ложка дегтя — созданный нами запрос работает со скоростью черепахи. Например, на моем не самом слабом ПК таблица всего в 2000 строк обрабатывается за 17 секунд. Что делать, если данных больше?
Для ускорения можно использовать буферизацию с помощью специальной функции List.Buffer, которая загружает переданный ей в качестве аргумента список (список) в оперативную память, что значительно ускоряет доступ к нему в дальнейшем. В нашем случае имеет смысл буферизовать список #”Добавленный индекс”[Продано], к которому Power Query должен иметь доступ при вычислении промежуточной суммы в каждой строке нашей таблицы из 2000 строк.
Для этого в редакторе Power Query на вкладке «Основные» нажмите кнопку «Расширенный редактор» (Главная — Расширенный редактор), чтобы открыть исходный код нашего запроса на встроенном в Power Query языке M:
А затем добавьте туда строку с переменной Мой список, значение которой возвращает функция буферизации, и на следующем шаге мы заменяем вызов списка на эту переменную:
После внесения этих изменений наш запрос станет значительно быстрее и справится с таблицей из 2000 строк всего за 0.3 секунды!
Другое дело, да? 🙂
- Диаграмма Парето (80/20) и как ее построить в Excel
- Поиск по ключевым словам в тексте и буферизация запросов в Power Query