Метод 1. Формули
Да започнем, за загряване, с най-простия вариант – формули. Ако имаме малка таблица, сортирана по дата като вход, тогава за да изчислим общата сума в отделна колона, се нуждаем от елементарна формула:
Основната особеност тук е сложното фиксиране на диапазона във функцията SUM – препратката към началото на диапазона се прави абсолютна (със знаци за долар), а към края – относителна (без долари). Съответно, когато копираме формулата до цялата колона, получаваме разширяващ се диапазон, чиято сума изчисляваме.
Недостатъците на този подход са очевидни:
- Таблицата трябва да бъде сортирана по дата.
- При добавяне на нови редове с данни формулата ще трябва да се разшири ръчно.
Метод 2. Оборотна таблица
Този метод е малко по-сложен, но много по-приятен. И за да изострим, нека разгледаме по-сериозен проблем – таблица от 2000 реда данни, където няма сортиране по колона за дата, но има повторения (т.е. можем да продаваме няколко пъти в един и същи ден):
Преобразуваме нашата оригинална таблица в „интелигентна“ (динамична) клавишна комбинация Ctrl+T или екип Начало – Форматиране като таблица (Начало — Форматиране като таблица)и след това изграждаме обобщена таблица върху него с командата Вмъкване – обобщена таблица (Вмъкване — осева таблица). Поставяме датата в областта на редовете в резюмето и броя на продадените стоки в областта на стойностите:
Моля, обърнете внимание, че ако имате не съвсем стара версия на Excel, тогава датите автоматично се групират по години, тримесечия и месеци. Ако имате нужда от различно групиране (или изобщо не се нуждаете от него), тогава можете да го коригирате, като щракнете с десния бутон върху произволна дата и изберете команди Групиране/Разгрупиране (Групиране/Разгрупиране).
Ако искате да видите както получените суми по периоди, така и текущата сума в отделна колона, тогава има смисъл да хвърлите полето в областта на стойността продаден отново да получим дубликат на полето – в него ще включим показването на текущи суми. За да направите това, щракнете с десния бутон върху полето и изберете командата Допълнителни изчисления – кумулативен сбор (Показване на стойностите като — текущи суми):
Там също можете да изберете опцията за нарастване на сумите като процент, а в следващия прозорец трябва да изберете полето, за което ще отива натрупването – в нашия случай това е полето за дата:
Предимствата на този подход:
- Голямо количество данни се четат бързо.
- Не е необходимо ръчно въвеждане на формули.
- При промяна на изходните данни е достатъчно да актуализирате резюмето с десния бутон на мишката или с командата Данни – Обнови всички.
Недостатъците произтичат от факта, че това е резюме, което означава, че не можете да правите каквото искате в него (вмъкване на редове, писане на формули, изграждане на диаграми и т.н.) вече няма да работи.
Метод 3: Power Query
Нека заредим нашата „умна“ таблица с изходни данни в редактора на заявки на Power Query, използвайки командата Данни – От таблица/диапазон (Данни — от таблица/обхват). В най-новите версии на Excel, между другото, той беше преименуван - сега се нарича С листа (От лист):
След това ще изпълним следните стъпки:
1. Сортирайте таблицата във възходящ ред по колоната с дата с командата Подреждане във възходящ ред в падащия списък с филтри в заглавката на таблицата.
2. Малко по-късно, за да изчислим общата сума, се нуждаем от спомагателна колона с поредния номер на реда. Нека го добавим с командата Добавяне на колона – Индексна колона – От 1 (Добавяне на колона — Индексна колона — От 1).
3. Също така, за да изчислим общата сума, имаме нужда от препратка към колоната продаден, където се намират нашите обобщени данни. В Power Query колоните се наричат още списъци (списък) и за да получите връзка към тях, щракнете с десния бутон върху заглавката на колоната и изберете командата Detailing (Показване на подробности). Изразът, от който се нуждаем, ще се появи в лентата с формули, състоящ се от името на предишната стъпка #"Добавен индекс", откъдето вземаме таблицата и името на колоната [Продажби] от тази таблица в квадратни скоби:
Копирайте този израз в клипборда за по-нататъшна употреба.
4. Изтрийте ненужната последна стъпка продаден и вместо това добавете изчислена колона за изчисляване на общата сума с командата Добавяне на колона – Персонализирана колона (Добавяне на колона — Персонализирана колона). Формулата, от която се нуждаем, ще изглежда така:
Ето функцията Списък.Диапазон взема оригиналния списък (колона [Продажби]) и извлича елементи от него, започвайки от първия (във формулата това е 0, тъй като номерирането в Power Query започва от нула). Броят на елементите за извличане е номерът на реда, който вземаме от колоната [Индекс]. Така че тази функция за първия ред връща само една първа клетка от колоната продаден. За втория ред – вече първите две клетки, за третия – първите три и т.н.
Е, тогава функцията Списък.Сума сумира извлечените стойности и във всеки ред получаваме сумата от всички предишни елементи, т.е. кумулативната сума:
Остава да изтрием вече ненужната ни колона Index и да качим резултатите обратно в Excel с командата Home – Close & Load to.
Проблемът е решен.
Бързи и яростни
По принцип това можеше да бъде спряно, но има малка мука в мехлема – заявката, която създадохме, работи със скоростта на костенурка. Например, на моя не най-слаб компютър, таблица от само 2000 реда се обработва за 17 секунди. Ами ако има повече данни?
За да ускорите, можете да използвате буфериране, като използвате специалната функция List.Buffer, която зарежда списъка (списъка), даден му като аргумент в RAM, което значително ускорява достъпа до него в бъдеще. В нашия случай има смисъл да буферирате списъка #”Added index”[Sold], до който Power Query трябва да има достъп, когато изчислява общата сума във всеки ред от нашата таблица с 2000 реда.
За да направите това, в редактора на Power Query в раздела Main щракнете върху бутона Разширен редактор (Начало – Разширен редактор), за да отворите изходния код на нашата заявка на езика M, вграден в Power Query:
И след това добавете ред с променлива там Моят списък, чиято стойност се връща от буфериращата функция, а на следващата стъпка заместваме извикването на списъка с тази променлива:
След като направим тези промени, нашата заявка ще стане значително по-бърза и ще се справи с таблица с 2000 реда само за 0.3 секунди!
Друго нещо, нали? 🙂
- Диаграма на Парето (80/20) и как да я изградите в Excel
- Търсене по ключови думи в текст и буфериране на заявки в Power Query