Спосіб 1. Формули
Почнемо, для розминки, з найпростішого варіанту – формули. Якщо у нас є невелика таблиця, відсортована за датою, то для обчислення поточного підсумку в окремому стовпці нам потрібна елементарна формула:
Головною особливістю тут є хитра фіксація діапазону всередині функції SUM – посилання на початок діапазону робиться абсолютним (зі знаками долара), а на кінець – відносним (без доларів). Відповідно, копіюючи формулу на весь стовпець, ми отримуємо діапазон, що розширюється, суму якого обчислюємо.
Недоліки такого підходу очевидні:
- Таблицю необхідно відсортувати за датою.
- При додаванні нових рядків з даними формулу доведеться розширювати вручну.
Спосіб 2. Зведена таблиця
Цей спосіб трохи складніше, але набагато приємніше. І для загострення давайте розглянемо більш серйозну проблему – таблицю з 2000 рядків даних, де немає сортування за стовпцем дати, але є повторення (тобто ми можемо продавати кілька разів в один день):
Ми перетворюємо нашу вихідну таблицю на «розумну» (динамічну) комбінацію клавіш Ctrl+T або команда Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю), а потім ми створюємо на ньому зведену таблицю за допомогою команди Вставка – зведена таблиця (Вставити — зведена таблиця). В області рядків в підсумку ставимо дату, а в області значень кількість проданих товарів:
Зверніть увагу, якщо у вас не зовсім стара версія Excel, то дати автоматично групуються по роках, кварталах і місяцях. Якщо вам потрібна інша групування (або вона не потрібна взагалі), то ви можете це виправити, клацнувши правою кнопкою миші будь-яку дату та вибравши команди Згрупувати / розгрупувати (Групувати / Розгрупувати).
Якщо ви хочете побачити підсумкові підсумки за періодами та поточний підсумок в окремому стовпці, тоді має сенс перекинути поле в область значень проданий знову отримати дублікат поля – в ньому ми включимо відображення поточних підсумків. Для цього клацніть правою кнопкою миші на полі та виберіть команду Додаткові розрахунки – сукупний підсумок (Показати значення як — поточні підсумки):
Там же можна вибрати опцію зростання підсумків у відсотках, а в наступному вікні потрібно вибрати поле, за яким буде йти накопичення – в нашому випадку це поле дати:
Переваги цього підходу:
- Швидко зчитується великий обсяг даних.
- Не потрібно вводити формули вручну.
- При зміні вихідних даних досить оновити зведення правою кнопкою миші або командою Дані – Оновити все.
Недоліки випливають з того, що це конспект, а значить, робити в ньому все, що завгодно (вставляти рядки, писати формули, будувати будь-які діаграми і т. д.) вже не вийде.
Спосіб 3: Power Query
Давайте завантажимо нашу «розумну» таблицю з вихідними даними в редактор запитів Power Query за допомогою команди Дані – з таблиці/діапазону (Дані — з таблиці/діапазону). В останніх версіях Excel, до речі, перейменували – тепер називається З листям (З аркуша):
Потім ми виконаємо наступні кроки:
1. Відсортуйте таблицю в порядку зростання за стовпцем дати за допомогою команди Сортувати за зростанням у розкривному списку фільтрів у заголовку таблиці.
2. Трохи пізніше для підрахунку назавжди знадобиться допоміжний стовпець з порядковим номером рядка. Додамо його за допомогою команди Додати стовпець – стовпець індексу – від 1 (Додати стовпець — стовпець індексу — від 1).
3. Крім того, щоб обчислити поточну суму, нам потрібно посилання на стовпець проданий, де лежать наші зведені дані. У Power Query стовпці також називаються списками (списком), і щоб отримати посилання на нього, клацніть правою кнопкою миші заголовок стовпця та виберіть команду Деталізація (Показати деталі). У рядку формул з'явиться потрібний нам вираз, що складається з назви попереднього кроку #"Індекс додано", звідки ми беремо таблицю та назву стовпця [Продажі] з цієї таблиці в квадратних дужках:
Скопіюйте цей вираз у буфер обміну для подальшого використання.
4. Видаліть непотрібне ще останній крок проданий і замість цього додайте обчислений стовпець для обчислення поточного підсумку за допомогою команди Додавання стовпця – спеціальний стовпець (Додати стовпець — спеціальний стовпець). Потрібна нам формула буде виглядати так:
Ось функція Список.Діапазон приймає вихідний список (стовпець [Продажі]) і витягує з нього елементи, починаючи з першого (у формулі це 0, оскільки нумерація в Power Query починається з нуля). Кількість елементів для отримання - це номер рядка, який ми беремо зі стовпця [Індекс]. Отже, ця функція для першого рядка повертає лише одну першу клітинку стовпця проданий. Для другого рядка – вже перші дві клітинки, для третього – перші три і т.д.
Ну а далі функція Список.Сума підсумовує витягнуті значення, і ми отримуємо в кожному рядку суму всіх попередніх елементів, тобто кумулятивний підсумок:
Залишається видалити стовпець Index, який нам більше не потрібен, і завантажити результати назад в Excel за допомогою команди Home – Close & Load to.
Проблема вирішена.
Швидкий і шалений
В принципі, це можна було зупинити, але є маленька ложка дьогтю – створений нами запит працює зі швидкістю черепахи. Наприклад, на моєму не самому слабкому ПК таблиця всього в 2000 рядків обробляється за 17 секунд. Що робити, якщо є більше даних?
Для прискорення можна використовувати буферизацію за допомогою спеціальної функції List.Buffer, яка завантажує наданий їй як аргумент список (список) в оперативну пам'ять, що значно прискорює доступ до нього в майбутньому. У нашому випадку має сенс буферизувати список #”Added index”[Sold], до якого Power Query має отримати доступ під час обчислення поточного підсумку в кожному рядку нашої таблиці з 2000 рядків.
Для цього в редакторі Power Query на вкладці Головне натисніть кнопку Розширений редактор (Домашня сторінка – Розширений редактор), щоб відкрити вихідний код нашого запиту мовою М, вбудованою в Power Query:
А потім додайте туди рядок зі змінною Мій список, значення якого повертає функція буферизації, а на наступному кроці ми замінюємо виклик списку цією змінною:
Після внесення цих змін наш запит стане значно швидшим і впорається з таблицею з 2000 рядків всього за 0.3 секунди!
Інша справа, правда? 🙂
- Діаграма Парето (80/20) і як її побудувати в Excel
- Пошук за ключовими словами в тексті та буферизація запитів у Power Query