Metoda 1. Formule
Počnimo, za zagrijavanje, od najjednostavnije opcije – formula. Ako imamo malu tabelu sortiranu po datumu kao ulaz, onda da bismo izračunali tekući zbroj u zasebnoj koloni, potrebna nam je elementarna formula:
Glavna karakteristika ovdje je lukavo fiksiranje raspona unutar funkcije SUM – referenca na početak raspona je apsolutna (sa znakovima dolara), a na kraj – relativna (bez dolara). U skladu s tim, kada kopiramo formulu na cijeli stupac, dobijamo širi raspon, čiji zbroj izračunavamo.
Nedostaci ovog pristupa su očigledni:
- Tabela mora biti sortirana po datumu.
- Prilikom dodavanja novih redova sa podacima, formula će se morati ručno proširiti.
Metoda 2. Zaokretna tabela
Ova metoda je malo složenija, ali mnogo ugodnija. I da pogoršamo, razmotrimo ozbiljniji problem – tabelu od 2000 redova podataka, gde nema sortiranja po koloni datuma, ali ima ponavljanja (tj. možemo prodati nekoliko puta u istom danu):
Konvertujemo našu originalnu tabelu u „pametnu“ (dinamičku) prečicu na tastaturi Ctrl+T ili tim Početna – Formatirajte kao tabelu (Početna — Format kao tabela), a zatim na njemu sa komandom pravimo zaokretnu tabelu Umetanje – zaokretna tabela (Insert — Pivot Table). Datum stavljamo u područje redova u sažetku, a broj prodane robe u područje vrijednosti:
Imajte na umu da ako imate ne baš staru verziju Excela, datumi se automatski grupišu po godinama, kvartalima i mjesecima. Ako vam je potrebno drugačije grupisanje (ili vam uopće nije potrebno), to možete popraviti desnim klikom na bilo koji datum i odabirom naredbi Grupirajte / Razgrupirajte (Grupiraj / Razgrupiši).
Ako želite vidjeti i rezultirajuće zbrojeve po periodima i tekući zbroj u zasebnoj koloni, onda ima smisla baciti polje u područje vrijednosti Prodato ponovo da dobijemo duplikat polja – u njemu ćemo uključiti prikaz tekućih zbroja. Da biste to učinili, desnom tipkom miša kliknite polje i odaberite naredbu Dodatni proračuni – Kumulativno ukupno (Prikaži vrijednosti kao — tekući zbrojevi):
Tu možete odabrati i opciju povećanja ukupnih iznosa u procentima, au sljedećem prozoru morate odabrati polje za koje će ići akumulacija – u našem slučaju to je polje datuma:
Prednosti ovog pristupa:
- Velika količina podataka se brzo čita.
- Formule se ne moraju unositi ručno.
- Prilikom promjene izvornih podataka, dovoljno je ažurirati sažetak desnim gumbom miša ili komandom Podaci – Osvježi sve.
Nedostaci proizlaze iz činjenice da je ovo sažetak, što znači da u njemu ne možete raditi šta god želite (umetati linije, pisati formule, praviti dijagrame itd.) više neće raditi.
Metod 3: Power Query
Učitajmo našu “pametnu” tabelu sa izvornim podacima u uređivač upita Power Query koristeći naredbu Podaci – iz tabele/opseg (Podaci — iz tabele/opseg). U najnovijim verzijama Excela, inače, preimenovan je – sada se zove Sa lišćem (Sa lista):
Zatim ćemo izvršiti sljedeće korake:
1. Sortirajte tabelu uzlaznim redoslijedom prema stupcu datuma pomoću naredbe Sortiraj uzlazno na padajućoj listi filtera u zaglavlju tabele.
2. Malo kasnije, da bismo izračunali tekući zbroj, potrebna nam je pomoćna kolona sa rednim brojem reda. Dodajmo ga komandom Dodaj kolonu – Kolona indeksa – Od 1 (Dodaj kolonu — Indeksni stupac — Od 1).
3. Takođe, da bismo izračunali tekući zbroj, potrebna nam je referenca na kolonu Prodato, gdje se nalaze naši sumirani podaci. U Power Queryju kolone se nazivaju i liste (list) i da biste dobili vezu do njih, kliknite desnim tasterom miša na zaglavlje kolone i izaberite komandu Detaljno (Prikaži detalje). Izraz koji nam je potreban pojavit će se u traci formule, a sastoji se od naziva prethodnog koraka #”Indeks dodan”, odakle preuzimamo tabelu i ime kolone [prodaja] iz ove tabele u uglastim zagradama:
Kopirajte ovaj izraz u međuspremnik za dalju upotrebu.
4. Izbrišite nepotreban još posljednji korak Prodato i dodajte umjesto toga izračunatu kolonu za izračunavanje tekućeg iznosa pomoću naredbe Dodavanje kolone – Prilagođena kolona (Dodaj kolonu — Prilagođena kolona). Formula koja nam treba izgledat će ovako:
Evo funkcije List.Range preuzima originalnu listu (kolona [Prodaja]) i izdvaja elemente iz njega, počevši od prvog (u formuli, ovo je 0, pošto numerisanje u Power Queryju počinje od nule). Broj elemenata za dohvat je broj reda koji uzimamo iz kolone [Indeks]. Dakle, ova funkcija za prvi red vraća samo jednu prvu ćeliju kolone Prodato. Za drugi red – već prve dvije ćelije, za treći – prve tri, itd.
Pa, onda funkcija List.Sum zbraja ekstrahovane vrednosti i u svakom redu dobijamo zbir svih prethodnih elemenata, odnosno kumulativni zbroj:
Ostaje da izbrišemo kolonu Indeks koja nam više nije potrebna i vratimo rezultate u Excel pomoću naredbe Početna – Zatvori i učitaj u.
Problem je riješen.
Brzi i zestoki
U principu, to se moglo zaustaviti, ali postoji mala mušica – zahtjev koji smo kreirali radi brzinom kornjače. Na primjer, na mom ne najslabijem računaru, tabela od samo 2000 redova se obradi za 17 sekundi. Šta ako ima više podataka?
Da biste ubrzali, možete koristiti baferovanje pomoću posebne funkcije List.Buffer, koja učitava listu (listu) koja joj je data kao argument u RAM, što znatno ubrzava pristup njoj u budućnosti. U našem slučaju, ima smisla baferovati listu #”Added index”[Sold], kojoj Power Query mora pristupiti prilikom izračunavanja tekućeg ukupnog iznosa u svakom redu naše tabele od 2000 redova.
Da biste to uradili, u uređivaču Power Query na kartici Glavna kliknite na dugme Napredni uređivač (Početna – Napredni uređivač) da biste otvorili izvorni kod našeg upita na M jeziku ugrađenom u Power Query:
I onda dodajte red sa promenljivom tamo MyListčiju vrijednost vraća funkcija baferiranja, a u sljedećem koraku zamjenjujemo poziv na listu sa ovom varijablom:
Nakon uvođenja ovih izmjena, naš upit će postati znatno brži i snaći će se s tablicom od 2000 redova za samo 0.3 sekunde!
Još jedna stvar, zar ne? 🙂
- Pareto grafikon (80/20) i kako ga napraviti u Excel-u
- Pretraživanje ključnih riječi u tekstu i baferiranje upita u Power Queryju