Metod 1. Formler
Låt oss börja, för uppvärmning, med det enklaste alternativet – formler. Om vi har en liten tabell sorterad efter datum som indata, behöver vi en elementär formel för att beräkna den löpande summan i en separat kolumn:
Huvudfunktionen här är den knepiga fixeringen av intervallet inuti SUM-funktionen - referensen till början av intervallet görs absolut (med dollartecken) och till slutet - relativ (utan dollar). Följaktligen, när vi kopierar formeln ner till hela kolumnen, får vi ett expanderande intervall, vars summa vi beräknar.
Nackdelarna med detta tillvägagångssätt är uppenbara:
- Tabellen måste sorteras efter datum.
- När du lägger till nya rader med data måste formeln utökas manuellt.
Metod 2. Pivottabell
Denna metod är lite mer komplicerad, men mycket trevligare. Och för att förvärra, låt oss överväga ett allvarligare problem – en tabell med 2000 rader med data, där det inte finns någon sortering efter datumkolumnen, men det finns upprepningar (dvs vi kan sälja flera gånger på samma dag):
Vi konverterar vår ursprungliga tabell till en "smart" (dynamisk) kortkommando ctrl+T eller team Hem – Formatera som en tabell (Hem — Formatera som tabell), och sedan bygger vi en pivottabell på den med kommandot Infoga – Pivottabell (Infoga — pivottabell). Vi lägger in datumet i radområdet i sammanfattningen och antalet sålda varor i värdeområdet:
Observera att om du har en inte riktigt gammal version av Excel, så grupperas datumen automatiskt efter år, kvartal och månader. Om du behöver en annan gruppering (eller inte behöver den alls) kan du fixa det genom att högerklicka på vilket datum som helst och välja kommandon Grupp / Avgruppera (Grupp/Dela upp).
Om du vill se både de resulterande summorna per perioder och den löpande summan i en separat kolumn, är det meningsfullt att kasta fältet i värdeområdet Såld igen för att få en dubblett av fältet – i det kommer vi att aktivera visningen av löpande totaler. För att göra detta, högerklicka på fältet och välj kommandot Ytterligare beräkningar – Kumulativ summa (Visa värden som – löpande summor):
Där kan du också välja alternativet att växa totalt i procent, och i nästa fönster måste du välja fältet som ackumuleringen ska gå till – i vårt fall är detta datumfältet:
Fördelarna med detta tillvägagångssätt:
- En stor mängd data läses snabbt.
- Inga formler behöver matas in manuellt.
- När du ändrar källdata räcker det att uppdatera sammanfattningen med höger musknapp eller med kommandot Data – Uppdatera alla.
Nackdelarna följer av att detta är en sammanfattning, vilket gör att du inte kan göra vad du vill i den (sätta in rader, skriva formler, bygga några diagram etc.) inte längre fungerar.
Metod 3: Power Query
Låt oss ladda vår "smarta" tabell med källdata till Power Query-frågeredigeraren med kommandot Data – Från tabell/intervall (Data – från tabell/intervall). I de senaste versionerna av Excel döptes det förresten om – nu heter det Med löv (Från ark):
Sedan kommer vi att utföra följande steg:
1. Sortera tabellen i stigande ordning efter datumkolumnen med kommandot Sortera stigande i rullgardinsmenyn för filter i tabellhuvudet.
2. Lite senare, för att beräkna den löpande summan, behöver vi en hjälpkolumn med ordningsradens nummer. Låt oss lägga till det med kommandot Lägg till kolumn – Indexkolumn – Från 1 (Lägg till kolumn — Indexkolumn — Från 1).
3. För att beräkna den löpande summan behöver vi också en referens till kolumnen Såld, där vår sammanfattade data ligger. I Power Query kallas kolumner även listor (lista) och för att få en länk till den högerklickar du på kolumnrubriken och väljer kommandot detailing (Visa detaljer). Uttrycket vi behöver visas i formelfältet, bestående av namnet på föregående steg #”Index lagt till”, varifrån vi tar tabellen och kolumnnamnet [Försäljning] från denna tabell inom hakparenteser:
Kopiera detta uttryck till urklipp för vidare användning.
4. Ta bort onödigt mer sista steg Såld och lägg istället till en beräknad kolumn för att beräkna den löpande summan med kommandot Lägga till en kolumn – Anpassad kolumn (Lägg till kolumn – Anpassad kolumn). Formeln vi behöver kommer att se ut så här:
Här är funktionen List.Range tar den ursprungliga listan (kolumn [Försäljning]) och extraherar element från den, med början från den första (i formeln är detta 0, eftersom numrering i Power Query börjar från noll). Antalet element att hämta är radnumret vi tar från kolumnen [Index]. Så den här funktionen för den första raden returnerar bara en första cell i kolumnen Såld. För den andra raden - redan de två första cellerna, för den tredje - de tre första, etc.
Jo, funktionen då List.Summa summerar de extraherade värdena och vi får i varje rad summan av alla tidigare element, dvs kumulativ summa:
Det återstår att ta bort Index-kolumnen som vi inte längre behöver och ladda upp resultaten tillbaka till Excel med kommandot Hem – Stäng & Ladda för att.
Problemet är löst.
Snabb och Furious
I princip kunde detta ha stoppats, men det finns en liten fluga i salvan – förfrågan vi skapade fungerar i en sköldpaddas hastighet. Till exempel, på min inte den svagaste PC, bearbetas en tabell med endast 2000 rader på 17 sekunder. Vad händer om det finns mer data?
För att snabba upp kan du använda buffring med hjälp av den speciella List.Buffer-funktionen, som laddar listan (listan) som ges till den som ett argument i RAM, vilket avsevärt snabbar upp åtkomsten till den i framtiden. I vårt fall är det vettigt att buffra #”Added index”[Sold]-listan, som Power Query måste komma åt när man beräknar den löpande summan i varje rad i vår 2000-radstabell.
För att göra detta, i Power Query-redigeraren på huvudfliken, klicka på knappen Advanced Editor (Hem – Advanced Editor) för att öppna källkoden för vår fråga på M-språket som är inbyggt i Power Query:
Och lägg sedan till en rad med en variabel där Min lista, vars värde returneras av buffertfunktionen, och i nästa steg ersätter vi anropet till listan med denna variabel:
Efter att ha gjort dessa ändringar kommer vår fråga att bli betydligt snabbare och kommer att klara en tabell med 2000 rader på bara 0.3 sekunder!
En annan sak, eller hur? 🙂
- Paretodiagram (80/20) och hur man bygger det i Excel
- Nyckelordssökning i text och frågebuffring i Power Query