Meetod 1. Valemid
Alustame soojenduseks kõige lihtsamast variandist – valemitest. Kui meil on sisendiks väike tabel, mis on sorteeritud kuupäeva järgi, siis jooksva kogusumma arvutamiseks eraldi veerus vajame elementaarset valemit:
Peamine omadus on siin vahemiku keeruline fikseerimine funktsiooni SUM sees – viide vahemiku algusele muudetakse absoluutseks (dollarimärkidega) ja lõppu suhteliseks (ilma dollariteta). Vastavalt sellele, kopeerides valemi kogu veergu, saame laieneva vahemiku, mille summa arvutame.
Selle lähenemisviisi puudused on ilmsed:
- Tabel tuleb sorteerida kuupäeva järgi.
- Uute andmetega ridade lisamisel tuleb valemit käsitsi laiendada.
2. meetod. Pivot tabel
See meetod on veidi keerulisem, kuid palju meeldivam. Ja süvenemiseks vaatleme tõsisemat probleemi – 2000 rida andmeid, kus kuupäevaveeru järgi sorteerimist ei toimu, kuid esineb kordusi (st samal päeval saame müüa mitu korda):
Teisendame oma algse tabeli "nutikaks" (dünaamiliseks) kiirklahviks Ctrl+T või meeskond Avaleht – vorminda tabelina (Avaleht — vorminda tabelina), ja siis ehitame sellele käsuga pivot-tabeli Sisesta – PivotTable (Sisesta – Pivot Table). Kuupäeva paneme kokkuvõtte ridade alale ja müüdud kaupade arvu väärtuste alale:
Pange tähele, et kui teil on mitte päris vana Exceli versioon, rühmitatakse kuupäevad automaatselt aastate, kvartalite ja kuude järgi. Kui vajate teistsugust rühmitamist (või ei vaja seda üldse), saate selle parandada, paremklõpsates mis tahes kuupäeval ja valides käsud Rühmitada / rühmitada lahti (Grupeerida / rühmitada lahti).
Kui soovite näha nii saadud summasid perioodide kaupa kui ka jooksvat kogusummat eraldi veerus, siis on mõttekas visata väli väärtusalasse Müüdud uuesti välja duplikaadi saamiseks – selles lülitame sisse jooksvate kogusummade kuvamise. Selleks paremklõpsake väljal ja valige käsk Täiendavad arvutused – kumulatiivne kogusumma (Väärtuste kuvamine – jooksvad kogusummad):
Seal saab valida ka kogusummade protsendina kasvatamise võimaluse ning järgmises aknas tuleb valida väli, millele akumulatsioon läheb – meie puhul on selleks kuupäevaväli:
Selle lähenemisviisi eelised:
- Suur hulk andmeid loetakse kiiresti.
- Valemeid pole vaja käsitsi sisestada.
- Lähteandmete muutmisel piisab kokkuvõtte uuendamisest hiire parema nupuga või käsuga Andmed – Refresh All.
Miinused tulenevad sellest, et tegemist on kokkuvõttega, mis tähendab, et seal ei saa teha kõike, mida tahad (ridade lisamine, valemite kirjutamine, mistahes diagrammide koostamine jne) enam ei tööta.
3. meetod: Power Query
Laadime oma "nutika" tabeli lähteandmetega Power Query päringuredaktorisse, kasutades käsku Andmed – tabelist/vahemikust (Andmed – tabelist/vahemikust). Muide, Exceli viimastes versioonides nimetati see ümber - nüüd nimetatakse seda Lehtedega (Lehelt):
Seejärel teostame järgmised sammud:
1. Sorteeri tabel kuupäeva veeru järgi kasvavas järjekorras käsuga Järjesta kasvavalt tabeli päises olevas filtri rippmenüüs.
2. Veidi hiljem vajame jooksva kogusumma arvutamiseks abiveergu järjekorranumbriga. Lisame selle käsuga Lisa veerg – indeksi veerg – alates 1 (Lisa veerg — indeksi veerg — alates 1).
3. Samuti vajame jooksva kogusumma arvutamiseks viidet veerule Müüdud, kus asuvad meie kokkuvõtlikud andmed. Power Querys nimetatakse veerge ka loenditeks (loendiks) ja sellele lingi saamiseks tehke veeru päisel paremklõps ja valige käsk Detailimine (Kuva üksikasjad). Vajalik avaldis ilmub valemiribale, mis koosneb eelmise sammu nimest #"Indeks lisatud", kust võtame tabeli ja veeru nime [müük] sellest tabelist nurksulgudes:
Kopeerige see avaldis edasiseks kasutamiseks lõikepuhvrisse.
4. Kustutage veel mittevajalik viimane samm Müüdud ja lisage selle asemel arvutatud veerg jooksva kogusumma arvutamiseks käsuga Veeru lisamine – kohandatud veerg (Lisa veerg – kohandatud veerg). Vajalik valem näeb välja selline:
Siin on funktsioon Nimekiri. Vahemik võtab algse loendi (veerg [Müük]) ja eraldab sellest elemendid, alustades esimesest (valemis on see 0, kuna Power Query nummerdamine algab nullist). Allalaaditavate elementide arv on rea number, mille me veerust võtame [Indeks]. Seega tagastab see esimese rea funktsioon ainult veeru ühe esimese lahtri Müüdud. Teise rea jaoks – juba kaks esimest lahtrit, kolmanda jaoks – kolm esimest jne.
Noh, siis funktsioon Nimekiri.Summa liidab ekstraheeritud väärtused ja saame igas reas kõigi eelmiste elementide summa, st kumulatiivse kogusumma:
Jääb üle kustutada veerg Indeks, mida me enam ei vaja, ja laadida tulemused tagasi Excelisse käsuga Home – Close & Load to.
Probleem on lahendatud.
Kiire ja vihane
Põhimõtteliselt oleks võinud selle peatada, aga väike kärbes on salvis – meie loodud palve töötab kilpkonna kiirusel. Näiteks minu mitte kõige nõrgemal arvutil töödeldakse ainult 2000 rea tabelit 17 sekundiga. Mis siis, kui andmeid on rohkem?
Kiirendamiseks saab kasutada puhverdamist spetsiaalse funktsiooni List.Buffer abil, mis laeb RAM-i argumendina antud nimekirja (loendi), mis kiirendab sellele edaspidi oluliselt ligipääsu. Meie puhul on mõttekas puhverdada loend #”Lisatud indeks”[Müüdud], millele Power Queryl on juurdepääs meie 2000-realise tabeli iga rea jooksva kogusumma arvutamisel.
Selleks klõpsake Power Query redaktoris vahekaardil Peamine nuppu Täpsem redaktor (Kodu – Täpsem redaktor), et avada meie päringu lähtekood Power Querysse sisseehitatud M-keeles:
Ja siis lisa sinna rida muutujaga Minu nimekiri, mille väärtuse tagastab puhverdusfunktsioon, ja järgmises etapis asendame loendi kutse selle muutujaga:
Pärast nende muudatuste tegemist muutub meie päring oluliselt kiiremaks ja saab 2000-realise tabeliga hakkama vaid 0.3 sekundiga!
Teine asi, eks? 🙂
- Pareto diagramm (80/20) ja selle koostamine Excelis
- Märksõnaotsing tekstis ja päringu puhverdamine Power Querys