Metode 1. Formler
La oss starte, for oppvarming, med det enkleste alternativet – formler. Hvis vi har en liten tabell sortert etter dato som input, trenger vi en elementær formel for å beregne totalsummen i en egen kolonne:
Hovedfunksjonen her er den vanskelige fikseringen av området inne i SUM-funksjonen - referansen til begynnelsen av området gjøres absolutt (med dollartegn), og til slutten - relativ (uten dollar). Følgelig, når vi kopierer formelen ned til hele kolonnen, får vi et ekspanderende område, summen som vi beregner.
Ulempene med denne tilnærmingen er åpenbare:
- Tabellen skal sorteres etter dato.
- Når du legger til nye rader med data, vil formelen måtte utvides manuelt.
Metode 2. Pivottabell
Denne metoden er litt mer komplisert, men mye mer behagelig. Og for å forverre, la oss vurdere et mer alvorlig problem – en tabell med 2000 rader med data, der det ikke er sortering etter datokolonnen, men det er repetisjoner (dvs. vi kan selge flere ganger på samme dag):
Vi konverterer vår originale tabell til en "smart" (dynamisk) hurtigtast Ctrl+T eller team Hjem – Formater som en tabell (Hjem — Formater som tabell), og så bygger vi en pivottabell på den med kommandoen Sett inn – Pivottabell (Sett inn – pivottabell). Vi legger inn datoen i radområdet i sammendraget, og antall solgte varer i verdiområdet:
Vær oppmerksom på at hvis du har en ikke helt gammel versjon av Excel, blir datoene automatisk gruppert etter år, kvartaler og måneder. Hvis du trenger en annen gruppering (eller ikke trenger den i det hele tatt), kan du fikse det ved å høyreklikke på en hvilken som helst dato og velge kommandoer Gruppe / Opphev gruppe (Gruppe / Dele opp).
Hvis du vil se både de resulterende summene etter perioder og den løpende totalen i en egen kolonne, er det fornuftig å kaste feltet inn i verdiområdet Solgt igjen for å få et duplikat av feltet – i det vil vi slå på visningen av løpende totaler. For å gjøre dette, høyreklikk på feltet og velg kommandoen Tilleggsberegninger – Akkumulert total (Vis verdier som – løpende totaler):
Der kan du også velge alternativet for voksende totaler i prosent, og i neste vindu må du velge feltet som akkumuleringen skal gå til – i vårt tilfelle er dette datofeltet:
Fordelene med denne tilnærmingen:
- En stor mengde data leses raskt.
- Ingen formler må legges inn manuelt.
- Når du endrer kildedataene, er det nok å oppdatere sammendraget med høyre museknapp eller med kommandoen Data – Oppdater alle.
Ulempene følger av at dette er en oppsummering, som betyr at du ikke kan gjøre hva du vil i den (sette inn linjer, skrive formler, bygge eventuelle diagrammer osv.) ikke lenger vil fungere.
Metode 3: Power Query
La oss laste vår "smarte" tabell med kildedata inn i Power Query-spørringseditoren ved å bruke kommandoen Data – Fra tabell/område (Data – fra tabell/område). I de siste versjonene av Excel ble den forresten omdøpt – nå heter den Med blader (Fra ark):
Deretter vil vi utføre følgende trinn:
1. Sorter tabellen i stigende rekkefølge etter datokolonnen med kommandoen Sorter stigende i filterrullegardinlisten i tabelloverskriften.
2. Litt senere, for å beregne den løpende totalen, trenger vi en hjelpekolonne med ordinær radnummer. La oss legge det til med kommandoen Legg til kolonne – Indekskolonne – Fra 1 (Legg til kolonne — Indekskolonne — Fra 1).
3. For å beregne den løpende totalen trenger vi også en referanse til kolonnen Solgt, hvor våre oppsummerte data ligger. I Power Query kalles kolonner også lister (liste) og for å få en lenke til den, høyreklikk på kolonneoverskriften og velg kommandoen detaljering (Vis detaljer). Uttrykket vi trenger vil vises i formellinjen, som består av navnet på forrige trinn #"Indeks lagt til", hvorfra vi tar tabellen og kolonnenavnet [Salg] fra denne tabellen i firkantede parenteser:
Kopier dette uttrykket til utklippstavlen for videre bruk.
4. Slett unødvendig mer siste trinn Solgt og legg i stedet til en beregnet kolonne for å beregne den løpende totalen med kommandoen Legge til en kolonne – egendefinert kolonne (Legg til kolonne – egendefinert kolonne). Formelen vi trenger vil se slik ut:
Her er funksjonen Liste.Rekkevidde tar den opprinnelige listen (kolonne [Salg]) og trekker ut elementer fra den, fra den første (i formelen er dette 0, siden nummerering i Power Query starter fra null). Antall elementer som skal hentes er radnummeret vi tar fra kolonnen [Indeks]. Så denne funksjonen for den første raden returnerer bare én første celle i kolonnen Solgt. For den andre linjen – allerede de to første cellene, for den tredje – de tre første osv.
Vel, funksjonen da Liste.Sum summerer de ekstraherte verdiene og vi får i hver rad summen av alle tidligere elementer, dvs. kumulativ total:
Det gjenstår å slette indekskolonnen som vi ikke lenger trenger og laste opp resultatene tilbake til Excel med kommandoen Hjem – Lukk og last inn.
Problemet er løst.
Rask og rasende
I prinsippet kunne dette vært stoppet, men det er en liten flue i salven – forespørselen vi laget fungerer i hastigheten til en skilpadde. For eksempel, på min ikke den svakeste PC-en, behandles en tabell med bare 2000 rader på 17 sekunder. Hva om det er mer data?
For å øke hastigheten kan du bruke buffering ved å bruke den spesielle List.Buffer-funksjonen, som laster listen (listen) som er gitt til den som et argument inn i RAM, noe som gjør tilgangen til den mye raskere i fremtiden. I vårt tilfelle er det fornuftig å bufre #"Added index"[Sold]-listen, som Power Query må få tilgang til når man beregner den løpende totalen i hver rad i tabellen med 2000 rader.
For å gjøre dette, i Power Query-redigeringsprogrammet på hovedfanen, klikker du på Advanced Editor-knappen (Hjem – Advanced Editor) for å åpne kildekoden til spørringen vår på M-språket innebygd i Power Query:
Og legg deretter til en linje med en variabel der Min liste, verdien som returneres av bufferfunksjonen, og i neste trinn erstatter vi kallet til listen med denne variabelen:
Etter å ha gjort disse endringene, vil søket vårt bli betydelig raskere og vil klare en tabell med 2000 rader på bare 0.3 sekunder!
En annen ting, ikke sant? 🙂
- Pareto-diagram (80/20) og hvordan du bygger det i Excel
- Nøkkelordsøk i tekst og spørringsbuffring i Power Query