Metode 1. Formules
Kom ons begin, vir opwarming, met die eenvoudigste opsie – formules. As ons 'n klein tabel het wat volgens datum as invoer gesorteer is, dan benodig ons 'n elementêre formule om die lopende totaal in 'n aparte kolom te bereken:
Die belangrikste kenmerk hier is die moeilike vasstelling van die reeks binne die SUM-funksie - die verwysing na die begin van die reeks word absoluut gemaak (met dollartekens), en tot die einde - relatief (sonder dollars). Gevolglik, wanneer ons die formule na die hele kolom kopieer, kry ons 'n uitbreidende reeks, waarvan die som ons bereken.
Die nadele van hierdie benadering is duidelik:
- Die tabel moet volgens datum gesorteer word.
- Wanneer nuwe rye met data bygevoeg word, sal die formule handmatig uitgebrei moet word.
Metode 2. Spiltabel
Hierdie metode is 'n bietjie meer ingewikkeld, maar baie aangenamer. En om te vererger, kom ons oorweeg 'n ernstiger probleem - 'n tabel van 2000 rye data, waar daar geen sortering volgens die datumkolom is nie, maar daar is herhalings (dws ons kan verskeie kere op dieselfde dag verkoop):
Ons verander ons oorspronklike tabel in 'n "slim" (dinamiese) sleutelbordkortpad Ctrl+T of span Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel), en dan bou ons 'n spiltabel daarop met die opdrag Voeg in – draaitabel (Voeg in – Spiltabel). Ons plaas die datum in die ry-area in die opsomming, en die aantal goedere verkoop in die waardes-area:
Neem asseblief kennis dat as jy 'n nie heeltemal ou weergawe van Excel het nie, die datums outomaties volgens jare, kwartale en maande gegroepeer word. As jy 'n ander groepering nodig het (of glad nie nodig het nie), dan kan jy dit regmaak deur regs te klik op enige datum en opdragte te kies Groep / Ontgroepeer (Groep / Ontgroepeer).
As jy beide die resulterende totale volgens periodes en die lopende totaal in 'n aparte kolom wil sien, maak dit sin om die veld in die waardearea te gooi verkoop weer om 'n duplikaat van die veld te kry – daarin sal ons die vertoning van lopende totale aanskakel. Om dit te doen, regskliek op die veld en kies die opdrag Bykomende berekeninge – Kumulatiewe totaal (Wys waardes as — Lopende totale):
Daar kan jy ook die opsie kies om totale as 'n persentasie te laat groei, en in die volgende venster moet jy die veld kies waarvoor die akkumulasie gaan – in ons geval is dit die datumveld:
Die voordele van hierdie benadering:
- 'n Groot hoeveelheid data word vinnig gelees.
- Geen formules hoef handmatig ingevoer te word nie.
- As u die brondata verander, is dit genoeg om die opsomming op te dateer met die regtermuisknoppie of met die Data – Refresh All-opdrag.
Die nadele volg uit die feit dat dit 'n opsomming is, wat beteken dat jy nie daarin kan doen wat jy wil nie (reëls invoeg, formules skryf, enige diagramme bou, ens.) nie meer sal werk nie.
Metode 3: Kragnavraag
Kom ons laai ons "slim" tabel met brondata in die Power Query-navraagredigeerder deur gebruik te maak van die opdrag Data – Van tabel/reeks (Data – van tabel/reeks). In die jongste weergawes van Excel is dit terloops hernoem – nou word dit genoem Met blare (Vanuit Blad):
Dan sal ons die volgende stappe uitvoer:
1. Sorteer die tabel in stygende volgorde volgens die datumkolom met die opdrag Sorteer stygend in die filter-aftreklys in die tabelopskrif.
2. 'n Bietjie later, om die lopende totaal te bereken, benodig ons 'n hulpkolom met die rangorde-rynommer. Kom ons voeg dit by met die opdrag Voeg kolom by – Indekskolom – Vanaf 1 (Voeg kolom by — Indekskolom — Vanaf 1).
3. Ook, om die lopende totaal te bereken, het ons 'n verwysing na die kolom nodig verkoop, waar ons opgesomde data lê. In Power Query word kolomme ook lyste (lys) genoem en om 'n skakel daarna te kry, regskliek op die kolomkop en kies die opdrag besonderhede (Wys detail). Die uitdrukking wat ons benodig, sal in die formulebalk verskyn, wat bestaan uit die naam van die vorige stap #"Indeks bygevoeg", vanwaar ons die tabel en die kolomnaam neem [Verkope] van hierdie tabel in vierkantige hakies:
Kopieer hierdie uitdrukking na die knipbord vir verdere gebruik.
4. Vee onnodige meer laaste stap uit verkoop en voeg in plaas daarvan 'n berekende kolom by vir die berekening van die lopende totaal met die opdrag Voeg 'n kolom by - Pasgemaakte kolom (Voeg kolom by – Pasgemaakte kolom). Die formule wat ons benodig sal soos volg lyk:
Hier is die funksie Lys.Reeks neem die oorspronklike lys (kolom [Verkope]) en onttrek elemente daaruit, vanaf die eerste (in die formule is dit 0, aangesien nommering in Power Query vanaf nul begin). Die aantal elemente om te herwin is die rynommer wat ons uit die kolom neem [Indeks]. So hierdie funksie vir die eerste ry gee slegs een eerste sel van die kolom terug verkoop. Vir die tweede reël – reeds die eerste twee selle, vir die derde – die eerste drie, ens.
Wel, dan die funksie Lys.Som som die onttrekte waardes op en ons kry in elke ry die som van alle vorige elemente, dws kumulatiewe totaal:
Dit bly oor om die Indekskolom wat ons nie meer nodig het nie, uit te vee en die resultate terug te laai na Excel met die Home - Close & Load to command.
Die probleem is opgelos.
Vinnig en woedend
In beginsel kon dit gestop gewees het, maar daar is 'n klein vliegie in die salf – die versoek wat ons geskep het werk teen die spoed van 'n skilpad. Byvoorbeeld, op my nie die swakste rekenaar nie, word 'n tabel van slegs 2000 rye in 17 sekondes verwerk. Wat as daar meer data is?
Om te bespoedig, kan jy buffer gebruik deur die spesiale List.Buffer-funksie te gebruik, wat die lys (lys) wat daaraan gegee word as 'n argument in RAM laai, wat toegang daartoe in die toekoms aansienlik versnel. In ons geval is dit sinvol om die #"Added index"[Sold]-lys te buffer, waartoe Power Query toegang moet kry wanneer die lopende totaal in elke ry van ons 2000-ry-tabel bereken word.
Om dit te doen, klik in die Power Query-redigeerder op die Hoofoortjie die Advanced Editor-knoppie (Tuis – Advanced Editor) om die bronkode van ons navraag oop te maak in die M-taal wat in Power Query ingebou is:
En voeg dan 'n reël by met 'n veranderlike daar My Lys, waarvan die waarde deur die bufferfunksie teruggestuur word, en by die volgende stap vervang ons die oproep na die lys met hierdie veranderlike:
Nadat ons hierdie veranderinge aangebring het, sal ons navraag aansienlik vinniger word en sal 'n 2000-ry-tabel in net 0.3 sekondes hanteer!
Nog iets, reg? 🙂
- Pareto-kaart (80/20) en hoe om dit in Excel te bou
- Sleutelwoordsoektog in teks en navraagbuffering in Power Query