Metoda 1. Formule
Krenimo, za zagrijavanje, s najjednostavnijom opcijom – formulama. Ako imamo malu tablicu sortiranu po datumu kao ulaz, tada za izračunavanje tekućeg ukupnog iznosa u zasebnom stupcu potrebna nam je elementarna formula:
Glavna značajka ovdje je lukavo fiksiranje raspona unutar funkcije SUM - referenca na početak raspona je apsolutna (sa znakovima dolara), a na kraj - relativna (bez dolara). Sukladno tome, kopiranjem formule na cijeli stupac dobivamo raspon koji se širi, čiji zbroj izračunavamo.
Nedostaci ovog pristupa su očiti:
- Tablica mora biti sortirana po datumu.
- Prilikom dodavanja novih redaka s podacima, formula će se morati proširiti ručno.
Metoda 2. Stožerna tablica
Ova metoda je malo kompliciranija, ali mnogo ugodnija. I da pogoršamo, razmotrimo ozbiljniji problem – tablicu od 2000 redaka podataka, gdje nema sortiranja po stupcu datuma, ali ima ponavljanja (tj. možemo prodavati nekoliko puta u istom danu):
Našu originalnu tablicu pretvaramo u "pametnu" (dinamičku) tipkovničku prečicu Ctrl+T ili tim Početna – Format kao tablica (Početna — Formatiraj kao tablicu), a zatim na njoj naredbom gradimo stožernu tablicu Umetanje – zaokretna tablica (Umetni — zaokretna tablica). Stavili smo datum u područje redaka u sažetku, a broj prodane robe u područje vrijednosti:
Imajte na umu da ako imate ne sasvim staru verziju Excela, datumi se automatski grupiraju po godinama, kvartalima i mjesecima. Ako trebate drugačije grupiranje (ili ga uopće ne trebate), tada to možete popraviti desnim klikom na bilo koji datum i odabirom naredbi Grupiraj / Razgrupiraj (Grupiraj / Razgrupiraj).
Ako želite vidjeti i rezultirajuće ukupne zbrojeve po razdobljima i tekući zbroj u zasebnom stupcu, tada ima smisla baciti polje u područje vrijednosti Prodan ponovno dobiti duplikat polja – u njemu ćemo uključiti prikaz tekućih zbrojeva. Da biste to učinili, desnom tipkom miša kliknite polje i odaberite naredbu Dodatni izračuni – kumulativni zbroj (Prikaži vrijednosti kao — tekuće ukupne vrijednosti):
Tamo možete odabrati i opciju postotnog rasta zbrojeva, au sljedećem prozoru potrebno je odabrati polje za koje će ići akumulacija – u našem slučaju to je polje datuma:
Prednosti ovog pristupa:
- Brzo se očitava velika količina podataka.
- Nije potrebno ručno unositi formule.
- Prilikom promjene izvornih podataka dovoljno je ažurirati sažetak desnom tipkom miša ili naredbom Podaci – Osvježi sve.
Nedostaci proizlaze iz činjenice da je ovo sažetak, što znači da u njemu ne možete raditi što god želite (umetati retke, pisati formule, graditi bilo kakve dijagrame itd.) više neće raditi.
Metoda 3: Power Query
Učitajmo našu “pametnu” tablicu s izvornim podacima u uređivač upita Power Query pomoću naredbe Podaci – iz tablice/raspona (Podaci — iz tablice/raspona). U najnovijim verzijama Excela, usput, preimenovan je - sada se zove S lišćem (Iz lista):
Zatim ćemo izvršiti sljedeće korake:
1. Naredbom poredajte tablicu uzlaznim redoslijedom prema stupcu datuma Poredaj uzlazno u padajućem popisu filtera u zaglavlju tablice.
2. Nešto kasnije, za izračun tekućeg zbroja, potreban nam je pomoćni stupac s rednim brojem retka. Dodajmo ga naredbom Dodaj stupac – stupac indeksa – od 1 (Dodaj stupac — stupac indeks — od 1).
3. Također, za izračunavanje tekućeg ukupnog iznosa potrebna nam je referenca na stupac Prodan, gdje se nalaze naši sažeti podaci. U Power Queryju stupci se nazivaju i popisi (list) i da biste dobili poveznicu na njega, desnom tipkom miša kliknite zaglavlje stupca i odaberite naredbu pojedinostima (Prikaži detalje). Izraz koji nam je potreban pojavit će se u traci formule, koji se sastoji od naziva prethodnog koraka #”Indeks dodan”, odakle preuzimamo tablicu i naziv stupca [Prodajni] iz ove tablice u uglatim zagradama:
Kopirajte ovaj izraz u međuspremnik za daljnju upotrebu.
4. Izbrišite nepotreban zadnji korak Prodan i umjesto toga dodajte izračunati stupac za izračun tekućeg ukupnog iznosa s naredbom Dodavanje stupca – prilagođeni stupac (Dodaj stupac — prilagođeni stupac). Formula koja nam treba izgledat će ovako:
Evo funkcije Popis.Raspon uzima izvorni popis (stupac [Prodajni]) i izdvaja elemente iz njega, počevši od prvog (u formuli je to 0, jer numeriranje u Power Queryju počinje od nule). Broj elemenata za dohvaćanje je broj retka koji uzimamo iz stupca [Indeks]. Dakle, ova funkcija za prvi redak vraća samo jednu prvu ćeliju stupca Prodan. Za drugu liniju - već prve dvije ćelije, za treću - prve tri, itd.
Pa onda funkcija Lista.Zbroj zbraja ekstrahirane vrijednosti i u svakom retku dobivamo zbroj svih prethodnih elemenata, tj. kumulativni zbroj:
Ostaje još da izbrišemo stupac Index koji nam više ne treba i uploadamo rezultate natrag u Excel naredbom Home – Close & Load to.
Problem je riješen.
Brzi i žestoki
U principu, to se moglo zaustaviti, ali postoji mala mušica u masti – zahtjev koji smo kreirali radi brzinom kornjače. Na primjer, na mom ne najslabijem računalu, tablica od samo 2000 redaka obrađuje se za 17 sekundi. Što ako ima više podataka?
Da biste ubrzali, možete koristiti međuspremnik pomoću posebne funkcije List.Buffer, koja učitava popis (popis) koji mu je dan kao argument u RAM, što uvelike ubrzava pristup njemu u budućnosti. U našem slučaju, ima smisla spremiti u međuspremnik popis #”Added index”[Sold], kojemu Power Query mora pristupiti prilikom izračuna tekućeg ukupnog iznosa u svakom retku naše tablice od 2000 redaka.
Da biste to učinili, u uređivaču Power Query na kartici Glavno kliknite gumb 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 tamo dodajte redak s varijablom Moj popisčiju vrijednost vraća funkcija međuspremnika, au sljedećem koraku zamijenimo poziv liste ovom varijablom:
Nakon uvođenja ovih promjena, naš će upit postati znatno brži i nositi se s tablicom od 2000 redaka za samo 0.3 sekunde!
Još jedna stvar, zar ne? 🙂
- Pareto grafikon (80/20) i kako ga izgraditi u Excelu
- Pretraživanje ključnih riječi u tekstu i međuspremnik upita u Power Queryju