Метод 1. Формуле
Почнимо, за загревање, од најједноставније опције - формула. Ако имамо малу табелу сортирану по датуму као улаз, онда да бисмо израчунали текући збир у посебној колони, потребна нам је елементарна формула:
Главна карактеристика овде је лукаво фиксирање опсега унутар функције СУМ – референца на почетак опсега је апсолутна (са знаковима долара), а до краја – релативна (без долара). Сходно томе, када копирамо формулу доле на целу колону, добијамо шири опсег, чији збир израчунавамо.
Недостаци овог приступа су очигледни:
- Табела мора бити сортирана по датуму.
- Приликом додавања нових редова са подацима, формула ће морати да се прошири ручно.
Метод 2. Пивот табела
Овај метод је мало компликованији, али много пријатнији. И да погоршамо, хајде да размотримо озбиљнији проблем – табелу од 2000 редова података, где нема сортирања по колони датума, али има понављања (тј. можемо продати неколико пута у истом дану):
Конвертујемо нашу оригиналну табелу у „паметну“ (динамичку) пречицу на тастатури Цтрл+T или тим Почетна – Формат као табела (Почетна — Формат као табела), а затим на њему са командом правимо стожерну табелу Уметање – заокретна табела (Инсерт — Пивот Табле). Стављамо датум у област редова у резимеу, а број продатих роба у област вредности:
Имајте на уму да ако имате не баш стару верзију Екцел-а, онда се датуми аутоматски групишу по годинама, кварталима и месецима. Ако вам је потребно другачије груписање (или вам уопште није потребно), можете то поправити тако што ћете кликнути десним тастером миша на било који датум и изабрати команде Група / Разгруписавање (Групирај / Разгрупиши).
Ако желите да видите и резултујуће збројеве по периодима и текући збир у посебној колони, онда има смисла бацити поље у област вредности Продато поново да добијемо дупликат поља – у њему ћемо укључити приказ текућих зброја. Да бисте то урадили, кликните десним тастером миша на поље и изаберите команду Додатни прорачуни – Кумулативно укупно (Прикажи вредности као — Текући укупни):
Ту можете изабрати и опцију повећања укупних износа у процентима, ау следећем прозору треба да изаберете поље за које ће ићи акумулација – у нашем случају, ово је поље датума:
Предности овог приступа:
- Велика количина података се брзо чита.
- Формуле не треба уносити ручно.
- Приликом промене изворних података, довољно је ажурирати резиме десним тастером миша или командом Подаци – Освежи све.
Недостаци произилазе из чињенице да је ово сажетак, што значи да у њему не можете радити шта год желите (убацивати линије, писати формуле, правити било какве дијаграме итд.) више неће радити.
Метод 3: Повер Куери
Хајде да учитамо нашу „паметну“ табелу са изворним подацима у Повер Куери уређивач упита користећи команду Подаци – из табеле/опсега (Подаци — из табеле/опсега). У најновијим верзијама Екцел-а, иначе, преименован је - сада се зове Са лишћем (Са листа):
Затим ћемо извршити следеће кораке:
1. Сортирајте табелу узлазним редоследом према колони датума помоћу команде Сортирај узлазно у падајућој листи филтера у заглављу табеле.
2. Нешто касније, да бисмо израчунали текући збир, потребна нам је помоћна колона са редним бројем реда. Хајде да га додамо командом Додај колону – Колона индекса – Од 1 (Додај колону — Колона индекса — Од 1).
3. Такође, да бисмо израчунали текући збир, потребна нам је референца на колону Продато, где се налазе наши сумирани подаци. У Повер Куерију колоне се такође називају листе (лист) и да бисте добили везу до њих, кликните десним тастером миша на заглавље колоне и изаберите команду Детаилинг (Прикажи детаље). Израз који нам је потребан појавиће се у траци формуле, који се састоји од назива претходног корака #”Индекс је додат”, одакле преузимамо табелу и назив колоне [Продаја] из ове табеле у угластим заградама:
Копирајте овај израз у међуспремник за даљу употребу.
4. Избришите непотребан још последњи корак Продато и додајте уместо тога израчунату колону за израчунавање текућег тотала помоћу команде Додавање колоне – Прилагођена колона (Додај колону — Прилагођена колона). Формула која нам је потребна ће изгледати овако:
Овде је функција Лист.Ранге преузима оригиналну листу (колона [Продаја]) и издваја елементе из њега, почевши од првог (у формули, ово је 0, пошто нумерисање у Повер Куерију почиње од нуле). Број елемената за преузимање је број реда који узимамо из колоне [Индекс]. Дакле, ова функција за први ред враћа само једну прву ћелију колоне Продато. За други ред – већ прве две ћелије, за трећи – прве три, итд.
Па, онда функција Лист.Сум сумира извучене вредности и у сваком реду добијамо збир свих претходних елемената, односно кумулативни збир:
Остаје да избришемо колону Индекс која нам више није потребна и вратимо резултате у Екцел помоћу команде Почетна – Затвори и учитај у.
Проблем је решен.
Паклене улице
У принципу, то је могло да се заустави, али постоји мала мува – захтев који смо креирали ради брзином корњаче. На пример, на мом не најслабијем рачунару, табела од само 2000 редова се обрађује за 17 секунди. Шта ако има више података?
Да бисте убрзали, можете користити баферовање помоћу посебне функције Лист.Буффер, која учитава листу (листу) која јој је дата као аргумент у РАМ, што у великој мери убрзава приступ њој у будућности. У нашем случају, има смисла баферовати листу #”Аддед индек”[Солд], којој Повер Куери мора да приступи када израчунава текући износ у сваком реду наше табеле од 2000 редова.
Да бисте то урадили, у уређивачу Повер Куери на картици Маин кликните на дугме Напредни уређивач (Хоме – Адванцед Едитор) да бисте отворили изворни код нашег упита на М језику уграђеном у Повер Куери:
И онда додајте ред са променљивом тамо Моја листачију вредност враћа функција баферовања, а у следећем кораку замењујемо позив листе са овом променљивом:
Након уношења ових промена, наш упит ће постати знатно бржи и снаћи ће се са табелом од 2000 редова за само 0.3 секунде!
Још једна ствар, зар не? 🙂
- Парето графикон (80/20) и како га направити у Екцел-у
- Претрага кључних речи у тексту и баферовање упита у Повер Куерију