Metode 1. Formler
Lad os starte, til opvarmning, med den enkleste mulighed – formler. Hvis vi har en lille tabel sorteret efter dato som input, så for at beregne den løbende total i en separat kolonne, har vi brug for en elementær formel:
Hovedfunktionen her er den vanskelige fastsættelse af intervallet inde i SUM-funktionen - referencen til begyndelsen af intervallet gøres absolut (med dollartegn) og til slutningen - relativ (uden dollars). Følgelig, når vi kopierer formlen ned til hele kolonnen, får vi et ekspanderende område, hvis sum vi beregner.
Ulemperne ved denne fremgangsmåde er indlysende:
- Tabellen skal sorteres efter dato.
- Når du tilføjer nye rækker med data, skal formlen udvides manuelt.
Metode 2. Pivottabel
Denne metode er lidt mere kompliceret, men meget mere behagelig. Og for at forværre, lad os overveje et mere alvorligt problem – en tabel med 2000 rækker af data, hvor der ikke er sortering efter datokolonnen, men der er gentagelser (dvs. vi kan sælge flere gange på samme dag):
Vi konverterer vores originale tabel til en "smart" (dynamisk) tastaturgenvej Ctrl+T eller hold Hjem – Formater som en tabel (Hjem — Formater som tabel), og så bygger vi en pivottabel på den med kommandoen Indsæt – Pivottabel (Indsæt — pivottabel). Vi sætter datoen i rækkeområdet i oversigten og antallet af solgte varer i værdiområdet:
Bemærk venligst, at hvis du har en ikke helt gammel version af Excel, så bliver datoerne automatisk grupperet efter år, kvartaler og måneder. Hvis du har brug for en anden gruppering (eller slet ikke har brug for den), så kan du rette den ved at højreklikke på en hvilken som helst dato og vælge kommandoer Gruppe / Ophæv gruppe (Grupp / Ophæv gruppe).
Hvis du vil se både de resulterende totaler efter perioder og den løbende total i en separat kolonne, så giver det mening at smide feltet ind i værdiområdet Solgt igen for at få et duplikat af feltet – i det vil vi slå visningen af løbende totaler til. For at gøre dette skal du højreklikke på feltet og vælge kommandoen Yderligere beregninger – Akkumuleret total (Vis værdier som – løbende totaler):
Der kan du også vælge muligheden for voksende totaler i procent, og i det næste vindue skal du vælge det felt, som akkumuleringen skal gå til – i vores tilfælde er dette datofeltet:
Fordelene ved denne tilgang:
- En stor mængde data aflæses hurtigt.
- Ingen formler skal indtastes manuelt.
- Når du ændrer kildedataene, er det nok at opdatere oversigten med højre museknap eller med kommandoen Data – Opdater alle.
Ulemperne følger af, at dette er et resumé, hvilket betyder, at du ikke kan gøre, hvad du vil i det (indsætte linjer, skrive formler, bygge eventuelle diagrammer osv.) vil ikke længere virke.
Metode 3: Power Query
Lad os indlæse vores "smarte" tabel med kildedata i Power Query-forespørgselseditoren ved hjælp af kommandoen Data – Fra tabel/område (Data — fra tabel/område). I de seneste versioner af Excel blev den i øvrigt omdøbt – nu hedder den Med blade (Fra ark):
Derefter vil vi udføre følgende trin:
1. Sorter tabellen i stigende rækkefølge efter datokolonnen med kommandoen Sorter stigende i filterrullelisten i tabeloverskriften.
2. Lidt senere, for at beregne den løbende total, har vi brug for en hjælpekolonne med det ordinære rækkenummer. Lad os tilføje det med kommandoen Tilføj kolonne – Indekskolonne – Fra 1 (Tilføj kolonne — Indekskolonne — Fra 1).
3. For at beregne den løbende total har vi også brug for en reference til kolonnen Solgt, hvor vores opsummerede data ligger. I Power Query kaldes kolonner også for lister (liste) og for at få et link til den skal du højreklikke på kolonneoverskriften og vælge kommandoen Detailing (Vis detaljer). Det udtryk, vi har brug for, vises i formellinjen, bestående af navnet på det forrige trin #"Indeks tilføjet", hvorfra vi tager tabellen og kolonnenavnet [salg] fra denne tabel i firkantede parenteser:
Kopier dette udtryk til udklipsholderen for yderligere brug.
4. Slet unødvendigt mere sidste trin Solgt og tilføj i stedet en beregnet kolonne til beregning af den løbende total med kommandoen Tilføjelse af en kolonne – Brugerdefineret kolonne (Tilføj kolonne – tilpasset kolonne). Formlen vi skal bruge ser sådan ud:
Her er funktionen Liste.Range tager den originale liste (kolonne [Salg]) og udtrækker elementer fra den, startende fra den første (i formlen er dette 0, da nummerering i Power Query starter fra nul). Antallet af elementer, der skal hentes, er det rækkenummer, vi tager fra kolonnen [Indeks]. Så denne funktion for den første række returnerer kun én første celle i kolonnen Solgt. For den anden linje - allerede de to første celler, for den tredje - de første tre osv.
Nå, så funktionen Liste.Sum summerer de udtrukne værdier, og vi får i hver række summen af alle tidligere elementer, dvs. kumulativ total:
Det er tilbage at slette indekskolonnen, som vi ikke længere har brug for, og uploade resultaterne tilbage til Excel med kommandoen Hjem – Luk og indlæs.
Problemet er løst.
Hurtig og rasende
I princippet kunne dette have været stoppet, men der er en lille flue i salven – den anmodning, vi lavede, virker med en skildpaddes hastighed. For eksempel, på min ikke den svageste pc, behandles en tabel på kun 2000 rækker på 17 sekunder. Hvad hvis der er flere data?
For at fremskynde, kan du bruge buffering ved at bruge den specielle List.Buffer-funktion, som indlæser listen (listen) givet til den som et argument i RAM, hvilket i høj grad fremskynder adgangen til den i fremtiden. I vores tilfælde giver det mening at buffer #"Tilføjet indeks"[Solgt]-listen, som Power Query skal have adgang til, når man beregner den løbende total i hver række i vores tabel med 2000 rækker.
For at gøre dette skal du i Power Query-editoren på hovedfanen klikke på knappen Advanced Editor (Hjem – Advanced Editor) for at åbne kildekoden til vores forespørgsel på M-sproget, der er indbygget i Power Query:
Og tilføj så en linje med en variabel der Min liste, hvis værdi returneres af bufferfunktionen, og i næste trin erstatter vi kaldet til listen med denne variabel:
Efter at have foretaget disse ændringer, vil vores forespørgsel blive betydeligt hurtigere og vil klare en tabel med 2000 rækker på kun 0.3 sekunder!
En anden ting, ikke? 🙂
- Pareto-diagram (80/20) og hvordan man bygger det i Excel
- Søgeordssøgning i tekst og forespørgselsbuffring i Power Query