Metoda 1. Formule
Začnimo, za ogrevanje, z najpreprostejšo možnostjo – formulami. Če imamo kot vhod majhno tabelo, razvrščeno po datumu, potem za izračun tekoče vsote v ločenem stolpcu potrebujemo osnovno formulo:
Glavna značilnost tukaj je zapleteno določanje obsega znotraj funkcije SUM - sklicevanje na začetek obsega je absolutno (z znaki za dolar), na konec pa relativno (brez dolarjev). Skladno s tem, ko kopiramo formulo navzdol na celoten stolpec, dobimo razširljiv obseg, katerega vsoto izračunamo.
Slabosti tega pristopa so očitne:
- Tabela mora biti razvrščena po datumu.
- Pri dodajanju novih vrstic s podatki bo treba formulo razširiti ročno.
Metoda 2. Vrtilna tabela
Ta metoda je nekoliko bolj zapletena, vendar veliko bolj prijetna. In da poglobimo, razmislimo o resnejšem problemu – tabeli z 2000 vrsticami podatkov, kjer ni razvrščanja po datumskem stolpcu, so pa ponavljanja (torej lahko prodajamo večkrat na isti dan):
Prvotno tabelo pretvorimo v »pametno« (dinamično) bližnjico na tipkovnici Ctrl+T ali ekipa Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo), nato pa na njej z ukazom zgradimo vrtilno tabelo Vstavi – vrtilna tabela (Vstavi — vrtilna tabela). Datum postavimo v območje vrstic v povzetku, število prodanega blaga pa v območje vrednosti:
Upoštevajte, da če imate ne povsem staro različico Excela, so datumi samodejno razvrščeni po letih, četrtletjih in mesecih. Če potrebujete drugačno razvrščanje v skupine (ali ga sploh ne potrebujete), lahko to popravite tako, da z desno miškino tipko kliknete kateri koli datum in izberete ukaze Združi / Razdruži (Združi / Razdruži).
Če želite videti tako dobljene vsote po obdobjih kot tekočo vsoto v ločenem stolpcu, potem je smiselno, da vržete polje v območje vrednosti razprodano spet dobimo dvojnik polja – v njem bomo vključili prikaz tekočih seštevkov. To storite tako, da z desno miškino tipko kliknete polje in izberete ukaz Dodatni izračuni – kumulativni seštevek (Prikaži vrednosti kot — tekoče vsote):
Tam lahko izberete tudi možnost naraščajočih vsot v odstotkih, v naslednjem oknu pa morate izbrati polje, za katerega se bo zbiralo – v našem primeru je to datumsko polje:
Prednosti tega pristopa:
- Velika količina podatkov se hitro prebere.
- Formul ni treba vnesti ročno.
- Pri spreminjanju izvornih podatkov je dovolj, da povzetek posodobimo z desnim gumbom miške ali z ukazom Podatki – Osveži vse.
Slabosti izhajajo iz dejstva, da je to povzetek, kar pomeni, da v njem ne morete početi, kar želite (vstavljati vrstice, pisati formule, sestavljati kakršne koli diagrame itd.) Ne bo več delovalo.
3. način: Power Query
Naložimo našo »pametno« tabelo z izvornimi podatki v urejevalnik poizvedb Power Query z ukazom Podatki – iz tabele/razpona (Podatki — iz tabele/razpona). Mimogrede, v najnovejših različicah Excela je bil preimenovan - zdaj se imenuje Z listi (Iz lista):
Nato bomo izvedli naslednje korake:
1. Z ukazom razvrstite tabelo v naraščajočem vrstnem redu po datumskem stolpcu Razvrsti naraščajoče na spustnem seznamu filtrov v glavi tabele.
2. Malo kasneje za izračun tekočega seštevka potrebujemo pomožni stolpec z zaporedno številko vrstice. Dodajmo ga z ukazom Dodaj stolpec – Indeksni stolpec – Od 1 (Dodaj stolpec — Stolpec indeks — Od 1).
3. Poleg tega za izračun tekoče vsote potrebujemo sklic na stolpec razprodano, kjer se nahajajo naši povzeti podatki. V Power Query se stolpci imenujejo tudi seznami (seznam) in za povezavo do njih dobite tako, da z desno miškino tipko kliknete glavo stolpca in izberete ukaz Podrobnosti (Pokaži podrobnosti). Izraz, ki ga potrebujemo, se bo pojavil v vrstici formule, sestavljen iz imena prejšnjega koraka #»Dodan indeks«, od koder vzamemo tabelo in ime stolpca [Prodaja] iz te tabele v oglatih oklepajih:
Kopirajte ta izraz v odložišče za nadaljnjo uporabo.
4. Izbrišite nepotrebne več zadnji korak razprodano in namesto tega dodajte izračunani stolpec za izračun tekoče vsote z ukazom Dodajanje stolpca – stolpec po meri (Dodaj stolpec — stolpec po meri). Formula, ki jo potrebujemo, bo videti takole:
Tukaj je funkcija Seznam.Razpon prevzame izvirni seznam (stolpec [Prodaja]) in iz njega izvleče elemente, začenši od prvega (v formuli je to 0, saj se oštevilčevanje v Power Queryju začne od nič). Število elementov, ki jih je treba pridobiti, je številka vrstice, ki jo vzamemo iz stolpca [Kazalo]. Torej ta funkcija za prvo vrstico vrne samo eno prvo celico stolpca razprodano. Za drugo vrstico - že prvi dve celici, za tretjo - prve tri itd.
No, potem pa funkcija Seznam.Vsota sešteje izluščene vrednosti in v vsaki vrstici dobimo vsoto vseh prejšnjih elementov, tj kumulativno vsoto:
Preostalo je še, da izbrišemo stolpec Index, ki ga ne potrebujemo več, in rezultate naložimo nazaj v Excel z ukazom Home – Close & Load to.
Problem je rešen.
Hitri in drzni
Načeloma bi se to dalo ustaviti, vendar je mala muha na koncu – zahteva, ki smo jo ustvarili, deluje z želvjo hitrostjo. Na primer, na mojem ne najšibkejšem računalniku se tabela s samo 2000 vrsticami obdela v 17 sekundah. Kaj pa, če je podatkov več?
Za pospešitev lahko uporabite medpomnjenje s posebno funkcijo List.Buffer, ki naloži seznam (seznam), ki mu je dan kot argument, v RAM, kar močno pospeši dostop do njega v prihodnosti. V našem primeru je smiselno shraniti v medpomnilnik seznam #»Added index«[Sold], do katerega ima Power Query dostop pri izračunu tekoče vsote v vsaki vrstici naše 2000-vrstične tabele.
Če želite to narediti, v urejevalniku Power Query na zavihku Glavno kliknite gumb Napredni urejevalnik (Domov – Napredni urejevalnik), da odprete izvorno kodo naše poizvedbe v jeziku M, vgrajenem v Power Query:
In nato tam dodajte vrstico s spremenljivko Moj seznam, katerega vrednost vrne funkcija medpomnilnika, v naslednjem koraku pa zamenjamo klic seznama s to spremenljivko:
Po teh spremembah bo naša poizvedba postala bistveno hitrejša in se bo spopadla s tabelo z 2000 vrsticami v samo 0.3 sekunde!
Še ena stvar, kajne? 🙂
- Pareto grafikon (80/20) in kako ga zgraditi v Excelu
- Iskanje po ključnih besedah v besedilu in medpomnjenje poizvedbe v Power Query