Running total (även kallad kumulativ summa) används ganska ofta i många situationer. Det är ett mått som berättar vad som är summan av värdena hittills.
Om du till exempel har månadsförsäljningsdata, visar en löpande summa hur mycket försäljning som har gjorts till en viss dag från den första dagen i månaden.
Det finns också några andra situationer där löpande summa ofta används, till exempel att beräkna ditt kontantsaldo i dina kontoutdrag/bokföring, räkna kalorier i din måltidsplan etc.
I Microsoft Excel finns det flera olika sätt att beräkna löpande totaler.
Metoden du väljer beror också på hur din data är uppbyggd.
Om du till exempel har enkla tabelldata kan du använda en enkel SUM -formel, men om du har en Excel -tabell är det bäst att använda strukturerade referenser. Du kan också använda Power Query för att göra detta.
I denna handledning kommer jag att täcka alla dessa olika metoder för beräkna löpande totalsumma i Excel.
Så låt oss komma igång!
Beräkning av körningssumma med tabelldata
Om du har tabelldata (dvs. en tabell i Excel som inte konverteras till en Excel -tabell) kan du använda några enkla formler för att beräkna de totala löpningarna.
Använda tilläggsoperatören
Antag att du har datummässiga säljdata och du vill beräkna den löpande totalen i kolumn C.
Nedan följer stegen för att göra detta.
Steg 1 - I cell C2, som är den första cellen där du vill ha den totala löpningen, anger du
= B2
Detta får helt enkelt samma försäljningsvärden i cell B2.
Steg 2 - I cell C3 anger du formeln nedan:
= C2+B3
Steg 3 - Applicera formeln på hela kolumnen. Du kan använda Fyllhandtaget för att markera och dra det, eller helt enkelt kopiera och klistra in cellen C3 i alla återstående celler (vilket automatiskt skulle justera referensen och ge rätt resultat).
Detta ger dig resultatet enligt nedan.
Det är en riktigt enkel metod och fungerar bra i de flesta fall.
Logiken är enkel - varje cell hämtar värdet ovanför den (vilket är den kumulativa summan till datumet före) och lägger till värdet i cellen intill den (vilket är försäljningsvärdet för den dagen).
Det finns bara en nackdel - om du tar bort någon av de befintliga raderna i den här datamängden kommer alla celler nedan som skulle ge ett referensfel (#REF!)
Om det är en möjlighet med din datamängd, använd nästa metod som använder SUM -formeln
Använda SUM med delvis låst cellreferens
Antag att du har datummässiga säljdata och du vill beräkna den löpande totalen i kolumn C.
Nedan är SUMM -formeln som ger dig löpningssumman.
= SUMMA ($ B $ 2: B2)
Låt mig förklara hur denna formel fungerar.
I ovanstående SUMM -formel har jag använt referensen för att lägga till som $ B $ 2: B2
- $ B $ 2 - detta är en absolut referens, vilket innebär att när jag kopierar samma formel i cellerna nedan kommer denna referens inte att ändras. Så när formeln kopieras i cellen nedan skulle formeln ändras till SUM ($ B $ 2: B3)
- B2 - det här är den andra delen av referensen som är en relativ referens, vilket innebär att detta skulle justeras när jag kopierar formeln ner eller till höger. Så när du kopierar formeln i cellen nedan blir detta värde B3
Läs också: Absoluta, relativa och blandade cellreferenser i Excel
Det fina med den här metoden är att om du tar bort någon av raderna i datamängden, skulle denna formel justera och ändå ge dig rätt löpande totalsumma.
Beräkning av körningssumma i Excel -tabell
När du arbetar med tabelldata i Excel är det en bra idé att konvertera den till en Excel -tabell. Det gör det mycket enklare att hantera data och gör det också lätt att använda verktyg som Power Query och Power Pivot.
Att arbeta i Excel -tabellerna har fördelar som strukturerade referenser (vilket gör det väldigt enkelt att hänvisa till data i tabellen och använda den i formler) och automatisk justering av referenser om du lägger till eller tar bort data från tabellen.
Medan du fortfarande kan använda ovanstående formel som jag har visat dig i en Excel -tabell, låt mig visa dig några bättre metoder för att göra detta.
Antag att du har en Excel -tabell som visas nedan och du vill beräkna den löpande totalen i kolumn C.
Nedan är formeln som gör detta:
= SUMMA (SalesData [[#Headers], [Sale]]: [@Sale])
Ovanstående formel kan se lite lång ut, men du behöver inte skriva den själv. det du ser i summanformeln kallas strukturerade referenser, vilket är Excels effektiva sätt att hänvisa till specifika datapunkter i en Excel -tabell.
Till exempel refererar SalesData [[#Headers], [Sale]] till försäljningsrubriken i tabellen SalesData (SalesData är namnet på Excel -tabellen som jag gav när jag skapade tabellen)
Och [@Sale] hänvisar till värdet i cellen på samma rad i kolumnen Försäljning.
Jag förklarade det här för din förståelse, men även om du inte vet något om strukturerade referenser kan du fortfarande enkelt skapa denna formel.
Nedan följer stegen för att göra detta:
- I cell C2 anger du = SUMMA (
- Välj cell B1, som är rubriken på kolumnen som har försäljningsvärdet. Du kan använda musen eller använda piltangenterna. Du kommer att märka att Excel automatiskt anger den strukturerade referensen för den cellen
- Lägg till en: (kolon -symbol)
- Välj cell B2. Excel skulle igen automatiskt infoga den strukturerade referensen för cellen
- Stäng fästet och tryck enter
Du kommer också att märka att du inte behöver kopiera formeln i hela kolumnen, en Excel -tabell gör det automatiskt åt dig.
En annan bra sak med den här metoden är att om du lägger till en ny post i den här datamängden, skulle Excel -tabellen automatiskt beräkna den totala löpningen för alla nya poster.
Medan vi har inkluderat kolumnrubriken i vår formel, kom ihåg att formeln ignorerar rubriktexten och endast beaktar data i kolumnen
Beräknar körningssumma med hjälp av Power Query
Power Query är ett fantastiskt verktyg när det gäller att ansluta till databaser, extrahera data från flera källor och transformera dem innan de läggs in i Excel.
Om du redan arbetar med Power Query skulle det vara mer effektivt att lägga till löpande totaler medan du omvandlar data i själva Power Query -redigeraren (istället för att först hämta data i Excel och sedan lägga till löpande totaler med någon av ovanstående metoder som behandlas ovan).
Även om det inte finns någon inbyggd funktion i Power Query för att lägga till löpande totalsummor (vilket jag önskar att det fanns) kan du fortfarande göra det med en enkel formel.
Anta att du har en Excel -tabell som visas nedan och du vill lägga till löpande totaler till dessa data:
Nedan följer stegen för att göra detta:
- Markera vilken cell som helst i Excel -tabellen
- Klicka på Data
- På fliken Get & Transform, klicka på ikonen från tabell/intervall. Detta öppnar tabellen i Power Query -redigeraren
- [Valfritt] Om din datumkolumn inte redan är sorterad klickar du på filterikonen i kolumnen Datum och klickar sedan på Sortera stigande
- Klicka på fliken Lägg till kolumn i Power Query -redigeraren
- I gruppen Allmänt klickar du på rullgardinsmenyn Indexkolumn (klicka inte på ikonen Indexkolumn utan på den lilla svarta pilen bredvid den för att visa fler alternativ)
- Klicka på alternativet "Från 1". Om du gör detta läggs en ny indexkolumn till som startar från en och anger siffror som ökar med 1 i hela kolumnen
- Klicka på ikonen "Anpassad kolumn" (som också finns på fliken Lägg till kolumn)
- Ange ett namn på den nya kolumnen i dialogrutan för anpassad kolumn som öppnas. i det här exemplet kommer jag att använda namnet 'Running Total'
- I fältet Anpassad kolumnformel anger du formeln nedan: List.Sum (List.Range (#”Added Index” [Sale], 0, [Index]))
- Se till att det finns en kryssruta längst ner i dialogrutan som säger - "Inga syntaxfel har upptäckts"
- Klicka på OK. Detta skulle lägga till en ny löpande total kolumn
- Ta bort indexkolumnen
- Klicka på fliken Arkiv och klicka sedan på "Stäng och ladda"
Stegen ovan skulle infoga ett nytt blad i din arbetsbok med en tabell med löpande totalsummor.
Om du nu tänker att det här är alldeles för många steg jämfört med de tidigare metoderna för att använda enkla formler, har du rätt.
Om du redan har en datauppsättning och allt du behöver göra är att lägga till löpande totals, är det bättre att inte använda Power Query.
Att använda Power Query är meningsfullt där du måste extrahera data från en databas eller kombinera data från flera olika arbetsböcker och i processen också lägga till löpande totaler till den.
När du automatiskt använder Power Query behöver du inte göra det igen nästa gång din datamängd ändras och du behöver bara uppdatera frågan och det ger dig resultatet baserat på den nya datamängden.
Hur fungerar detta?
Låt mig nu snabbt förklara vad som händer i denna metod.
Det första vi gör i Power Query -redigeraren är att infoga en indexkolumn som börjar från en och ökar med en när den går ner i cellerna.
Vi gör detta för att vi måste använda den här kolumnen medan vi beräknar löptotalen i en annan kolumn som vi infogar i nästa steg.
Sedan sätter vi in en anpassad kolumn och använder formeln nedan
List.Sum (List.Range (#"Added Index" [Sale], 0, [Index]))
Detta är en List.Sum -formel som ger dig summan av intervallet som anges inom den.
Och det intervallet anges med funktionen List.Range.
List.Range -funktionen ger det angivna intervallet i försäljningskolumnen som utdata och detta intervall ändras baserat på indexvärdet. Till exempel, för den första posten, skulle intervallet helt enkelt vara det första försäljningsvärdet. Och när du går ner i cellerna skulle detta intervall expandera.
Så, för den första cellen. List.Sum skulle bara ge dig summan av det första försäljningsvärdet, och för den andra cellen skulle det ge dig summan för de två första försäljningsvärdena och så vidare.
Även om denna metod fungerar bra, blir den väldigt långsam med stora datamängder - tusentals rader. Om du har att göra med en stor datamängd och vill lägga till löpande totaler till den, ta en titt på den här självstudien som visar andra snabbare metoder.Beräkning av körningssumma baserat på kriterier
Hittills har vi sett exempel där vi har beräknat löptotalen för alla värden i en kolumn.
Men det kan finnas fall där du vill beräkna löptotalen för specifika poster.
Till exempel, nedan har jag en datamängd och jag vill beräkna den löpande totalen för skrivare och skannrar separat i två olika kolumner.
Detta kan göras med en SUMIF -formel som beräknar den totala löpningen samtidigt som det angivna villkoret är uppfyllt.
Nedan är formeln som gör detta för skrivarkolumnerna:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
På samma sätt, för att beräkna den totala löpningen för skannrar, använd följande formel:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
I ovanstående formler har jag använt SUMIF, vilket skulle ge mig summan i ett intervall när de angivna kriterierna är uppfyllda.
Formeln tar tre argument:
- räckvidd: detta är kriterieintervallet som skulle kontrolleras mot de angivna kriterierna
- kriterier: detta är de kriterier som skulle checkas in endast om detta kriterium är uppfyllt så skulle värdena i det tredje argumentet, som är summan, läggas till
- [sum_range]: detta är summan från vilket värdena skulle läggas till om kriterierna uppfylls
Också i räckvidd och sum_range argument, har jag låst den andra delen av referensen, så att när vi går ner i cellerna skulle intervallet fortsätta att expandera. Detta gör att vi bara kan överväga och lägga till värden till det intervallet (därav löpande totals).
I denna formel har jag använt rubrikkolumnen (Skrivare och skanner) som kriterier. Du kan också hårdkoda kriterierna om dina kolumnrubriker inte är exakt samma som kriterieteksten.
Om du har flera villkor som du behöver kontrollera kan du använda SUMIFS -formeln.
Kör totalt i pivottabeller
Om du vill lägga till löpande totalsummor i ett pivottabellresultat kan du enkelt göra det med en inbyggd funktionalitet i pivottabeller.
Antag att du har en pivottabell som visas nedan där jag har datumet i en kolumn och försäljningsvärdet i den andra kolumnen.
Nedan följer stegen för att lägga till en ytterligare kolumn som visar försäljningens löpande totala datum:
- Dra fältet Försäljning och lägg det i området Värde.
- Detta kommer att lägga till ytterligare en kolumn med försäljningsvärden
- Klicka på alternativet Summa försäljning2 i området Värde
- Klicka på alternativet "Värdefältinställningar"
- I dialogrutan Inställningar för värdefält ändrar du det anpassade namnet till "Running Totals"
- Klicka på fliken "Visa värde som"
- I listrutan Visa värde som väljer du alternativet "Kör totalt i"
- Kontrollera att datum är valt i basfältalternativen
- Klicka på Ok
Stegen ovan skulle ändra den andra försäljningskolumnen till kolumnen Running Total.
Så det här är några av de sätt du kan använda för att beräkna den löpande totalen i Excel. Om du har data i tabellformat kan du använda enkla formler, och om du har en Excel -tabell kan du använda formler som använder strukturerade referenser.
Jag har också täckt hur man beräknar löpande total med Power Query och i pivottabeller.
Jag hoppas att du fann denna handledning användbar.
