Метод 1. Формули
Да почнеме, за загревање, со наједноставната опција – формулите. Ако имаме мала табела подредена по датум како влез, тогаш за да го пресметаме вкупниот број во посебна колона, потребна ни е елементарна формула:
Главната карактеристика овде е незгодното фиксирање на опсегот во функцијата SUM - упатувањето на почетокот на опсегот е апсолутно (со знаци на долар), а до крајот - релативно (без долари). Според тоа, при копирање на формулата до целата колона, добиваме опсег на проширување, чиј збир го пресметуваме.
Недостатоците на овој пристап се очигледни:
- Табелата мора да биде подредена по датум.
- Кога додавате нови редови со податоци, формулата ќе треба рачно да се прошири.
Метод 2. Стожерна табела
Овој метод е малку покомплициран, но многу попријатен. И за да се влоши, да разгледаме еден посериозен проблем – табела од 2000 редови на податоци, каде што нема подредување по колоната за датум, но има повторувања (т.е. можеме да продаваме неколку пати во ист ден):
Ја претвораме нашата оригинална табела во „паметна“ (динамична) кратенка на тастатурата Ctrl+T или тим Дома – Форматирајте како табела (Дома - Формат како табела), а потоа на неа градиме стожерна табела со командата Вметнете – Стожерна табела (Вметни - Стожерна табела). Го ставаме датумот во областа на редови во резимето, а бројот на продадени стоки во областа на вредности:
Ве молиме имајте предвид дека ако имате не сосема стара верзија на Excel, тогаш датумите автоматски се групираат по години, четвртини и месеци. Ако ви треба различно групирање (или воопшто не ви треба), тогаш можете да го поправите со десен клик на кој било датум и избирање команди Група / Одгрупирај (Група / Одгрупирај).
Ако сакате да ги видите и добиените збирови по точки и вкупниот број во посебна колона, тогаш има смисла да го фрлите полето во областа на вредности Продадени повторно да се добие дупликат од полето – во него ќе го вклучиме приказот на вкупните збирки. За да го направите ова, кликнете со десното копче на полето и изберете ја командата Дополнителни пресметки – Кумулативен Вкупно (Прикажи ги вредностите како - Вкупни збирки):
Таму, исто така, можете да ја изберете опцијата за зголемување на збирките како процент, а во следниот прозорец треба да го изберете полето за кое ќе оди акумулацијата - во нашиот случај, ова е полето за датум:
Предностите на овој пристап:
- Голема количина на податоци брзо се чита.
- Не треба рачно да се внесуваат формули.
- При промена на изворните податоци, доволно е да се ажурира резимето со десното копче на глувчето или со командата Data – Refresh All.
Недостатоците произлегуваат од фактот дека ова е резиме, што значи дека во него нема да можете да правите што сакате (вметнете линии, пишувајте формули, градете какви било дијаграми итн.) повеќе нема да работи.
Метод 3: Побарување за напојување
Ајде да ја вчитаме нашата „паметна“ табела со изворни податоци во уредувачот за пребарување Power Query користејќи ја командата Податоци – од табела/опсег (Податоци - од табела/опсег). Во најновите верзии на Excel, патем, беше преименуван - сега се нарекува Со лисја (Од лист):
Потоа ќе ги извршиме следните чекори:
1. Подредете ја табелата во растечки редослед по колоната датум со командата Подреди растечки во паѓачката листа за филтри во заглавието на табелата.
2. Малку подоцна, за да го пресметаме вкупниот број, потребна ни е помошна колона со бројот на редниот ред. Ајде да го додадеме со командата Додадете колона – Индекс колона – од 1 (Додај колона — Индекс колона — од 1).
3. Исто така, за да го пресметаме вкупниот број, потребна ни е референца за колоната Продадени, каде лежат нашите сумирани податоци. Во Power Query, колоните се нарекуваат и списоци (листа) и за да добиете врска до неа, кликнете со десното копче на заглавието на колоната и изберете ја командата Детали (Покажи детали). Изразот што ни треба ќе се појави во лентата со формула, која се состои од името на претходниот чекор #„Додаден индекс“, од каде ја земаме табелата и името на колоната [Продажба] од оваа табела во квадратни загради:
Копирајте го овој израз на таблата со исечоци за понатамошна употреба.
4. Избришете го непотребниот последен чекор Продадени и наместо тоа додадете пресметана колона за пресметување на вкупниот број со командата Додавање колона – Прилагодена колона (Додај колона - Прилагодена колона). Формулата што ни треба ќе изгледа вака:
Еве ја функцијата Список.Опсег ја зема оригиналната листа (колона [Продажба]) и извлекува елементи од него, почнувајќи од првото (во формулата, ова е 0, бидејќи нумерирањето во Power Query започнува од нула). Бројот на елементи што треба да се преземат е бројот на редот што го земаме од колоната [Индекс]. Значи, оваа функција за првиот ред враќа само една прва ќелија од колоната Продадени. За втората линија - веќе првите две ќелии, за третата - првите три, итн.
Па, тогаш функцијата Список.Сум ги сумира извлечените вредности и во секој ред го добиваме збирот на сите претходни елементи, односно кумулативниот вкупен износ:
Останува да ја избришеме колоната Индекс што повеќе не ни треба и да ги поставиме резултатите назад во Excel со командата Home – Close & Load.
Проблемот е решен.
Брзи и бесни
Во принцип, ова можеше да се спречи, но во мастата има и мала мушичка – барањето што го создадовме функционира со брзина на желка. На пример, на мојот не најслаб компјутер, табела од само 2000 редови се обработува за 17 секунди. Што ако има повеќе податоци?
За да забрзате, можете да користите баферирање со помош на специјалната функција List.Buffer, која ја вчитува листата (листата) дадена како аргумент во RAM меморијата, што значително го забрзува пристапот до него во иднина. Во нашиот случај, има смисла да се тампонира списокот #“Доддаден индекс“[продадено], до која Power Query треба да пристапи кога се пресметува вкупниот број во секој ред од нашата табела од 2000 реда.
За да го направите ова, во уредувачот Power Query на табулаторот Main, кликнете на копчето Advanced Editor (Home – Advanced Editor) за да го отворите изворниот код на нашето барање на јазикот M вграден во Power Query:
И потоа додадете линија со променлива таму Моја листа, чија вредност ја враќа функцијата за баферирање и на следниот чекор го заменуваме повикот до листата со оваа променлива:
Откако ќе ги направиме овие промени, нашето барање ќе стане значително побрзо и ќе се справи со табела од 2000 редови за само 0.3 секунди!
Друга работа, нели? 🙂
- Табела Парето (80/20) и како да се изгради во Excel
- Пребарување клучни зборови во текст и баферирање на барања во Power Query