Du kommer sannolikt att komma över behovet av att köra summor om du har att göra med någon form av dagliga data.

Tänk dig att du spårar försäljningen varje dag. Dina data innehåller en rad för varje datum med ett totalt försäljningsbelopp, men kanske vill du veta den totala försäljningen för månaden varje dag. Detta är en löpande summa, det är summan av all försäljning till och med nuvarande dagar försäljning.

i det här inlägget kommer vi att täcka flera sätt att beräkna en löpande summa för dina dagliga data. Vi undersöker hur du använder kalkylbladsformler, pivottabeller, power pivot med DAX och power query.

Vi undersöker också vad som händer med den löpande totala beräkningen när du infogar eller tar bort rader med data och hur du uppdaterar resultaten.

hämta filen med alla exempel.

Running Totals med en enkel formel

det är möjligt att skapa en grundläggande running total formel med operatorn+.

Vi måste dock använda två olika formler för att få jobbet gjort.

1. = C3 kommer att vara den första formeln och kommer bara att vara i den första raden av den löpande summan.
2. = C4 + D3 kommer att vara i den andra raden och kan kopieras ner de återstående raderna för den löpande summan.

formeln i vår första rad kan inte lägga till cellen ovanför den till summan eftersom den innehåller ett textvärde för en kolumnrubrik. Detta skulle orsaka ett # värde! fel att visas i den löpande summan eftersom + inte kan hantera textvärden. Vi undviker detta med en annan formel i den första raden som inte refererar till cellen ovan.

vad händer med den löpande summan när vi infogar eller tar bort rader i våra data?

infoga en ny rad kommer att resultera i ett gap i den löpande summan. För att åtgärda detta måste vi kopiera formeln ner från den första cellen ovanför de nyligen infogade raderna hela vägen ner till sista raden.

Om du tar bort några rader kommer det att resultera i #REF! fel sedan radering av en rad innebär att du tar bort en cell som refereras av formeln under den. För att åtgärda detta måste vi kopiera formeln ner från den sista felfria cellen hela vägen ner till sista raden.

löpande summor med en SUM-formel

Vi kan undvika besvärligheten med att använda två olika formler i vår löpande totalkolumn genom att använda SUM-funktionen istället för + – operatören. När SUM-funktionen stöter på en textcell kommer den att behandla den på samma sätt som en om den innehöll en 0.

På så sätt kan vi använda följande formel enhetligt för varje rad inklusive den första raden.

=SUM (C3, D2)

denna formel kommer att referera till kolumnrubriken som innehåller text för den första raden, men det är ok eftersom det behandlas som en 0.

När du infogar eller tar bort rader kommer vi fortfarande att stöta på samma problem med tomma celler och fel. Vi kan fixa dem på samma sätt som med löpande summor i den enkla formelmetoden.

kör summor med ett delvis fast intervall

ett annat alternativ med SUM-funktionen är att endast referera till försäljningskolumnen och använda en delvis fast intervallreferens.

om vi använder följande formel =summa($C$3:C3) kan vi kopiera och klistra in detta i intervallet. Det kommer inte att referera till några kolumnrubriker och intervallet refereras kommer att växa till varje rad.

tyvärr kommer detta också att ha samma problem (och lösningar) med att infoga eller radera rader.

kör summor med ett relativt namngivet intervall

Vi kan undvika problem med att infoga och ta bort rader från våra data om vi använder ett relativt namngivet intervall. Detta kommer att hänvisa till cellen direkt ovanför oavsett hur många rader vi sätter in eller tar bort.

detta är ett trick som innebär att tillfälligt byta Excel-referensstil från A1 till R1C1. Definiera sedan ett namngivet intervall med R1C1-notationen. Sedan byter referensstilen tillbaka till A1.

i R1C1-referensstil hänvisas celler till hur långt de är från cellen med hjälp av referensen. Till exempel = RC hänvisar till cellen 2 upp och 3 till höger om cellen med denna formel.

Vi kan använda denna relativa referens för att skapa ett namngivet intervall som alltid är en cell ovanför den hänvisande cellen med formeln =RC.

för att byta referensstil, gå till fliken Arkiv och välj sedan Alternativ. Gå till avsnittet formel i Excel-alternativmenyn och markera rutan R1C1 referensstil och tryck sedan på OK-knappen.

Nu kan vi lägga till vårt namngivna sortiment. Gå till fliken formel i Excel-bandet och välj kommandot Definiera namn.

infoga ett namn som ”ovan” som namnet på intervallet. Lägg till formeln =RC i refererar till ingången och tryck på OK-knappen.

Vi kan nu växla Excel tillbaka till standardreferensstilen. Gå till fliken Arkiv > alternativ Formelsektionen > avmarkera rutan R1C1 referensstil > tryck sedan på OK-knappen.

Nu kan vi använda formeln =summa (ovan) i vår löpande totalkolumn.

det angivna området ovan kommer alltid att referera till cellen direkt ovanför. När vi infogar eller tar bort rader justeras det relativa namngivna intervallet i enlighet därmed och ingen åtgärd behövs.

faktum är att om vi placerar våra data i en Excel-tabell fylls formeln automatiskt för alla nya rader eftersom formeln är enhetlig för hela kolumnen. Ingen åtgärd behövs för att kopiera ner några formler.

kör summor med en pivottabell

pivottabeller är mycket användbara för att sammanfatta alla typer av data. Det finns mer för dem än att bara lägga till, räkna och hitta medelvärden. Det finns många andra typer av beräkningar inbyggda, och det finns faktiskt en löpande totalberäkning!

först måste vi infoga en pivottabell baserad på data. Välj en cell i data och gå till fliken Infoga och välj kommandot pivottabell. Gå sedan igenom fönstret Skapa pivottabell för att välja var du vill ha pivottabellen, antingen i ett nytt kalkylblad eller någonstans i ett befintligt.

Lägg till datumfältet i området rader i pivottabellen och lägg sedan till försäljningsfältet i området värden i pivottabellen. Lägg nu till en annan instans av försäljningsfältet i området rader.

Vi bör nu ha två identiska Försäljningsfält där ett av dem är märkt summan av Försäljning2. Vi kan byta namn på den här etiketten när som helst genom att helt enkelt skriva över den med något som Running Total.

högerklicka på något av värdena i fältet summa av Sales2 och välj Visa värde som och välj Kör totalt i.

Vi vill visa den löpande summan efter datum, så i nästa fönster måste vi välja datum som basfält.

det är det, vi har nu en ny beräkning som visar den löpande summan av vår försäljning i pivottabellen.

vad händer om vi lägger till eller tar bort en rad i vår källdata, hur påverkar detta den löpande summan? Pivottabellberäkningarna är dynamiska och tar hänsyn till nya data i sin löpande totala beräkning, vi behöver bara uppdatera pivottabellen.

högerklicka var som helst i pivottabellen och välj Uppdatera från menyn.

Running summor med Power Pivot och DAX åtgärder

de första par steg för detta är exakt samma med en vanlig pivottabell.

Välj en cell i data och gå till fliken Infoga och välj kommandot pivottabell.

När du kommer till menyn Skapa pivottabell markerar du rutan Lägg till dessa data i datamodellen för att lägga till data i datamodellen och aktivera den för användning med power pivot.

placera fältet Datum i området rader och fältet försäljning i området värden i pivottabellen.

med power pivot måste vi skapa några extra beräkningar vi vill använda DAX-språket. Högerklicka på tabellnamnet i fönstret Pivottabellfält och välj sedan Lägg till mått för att skapa en ny beräkning. Observera att detta endast är tillgängligt med datamodellen.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

Nu kan vi skapa vårt nya löpande Totala mått.

1. i Mätfönstret måste vi lägga till ett Måttnamn. I det här fallet kan vi namnge den nya åtgärden som löpande Total.
2. Vi måste också lägga till ovanstående formel i Formelrutan.
3. det coola med power pivot är möjligheten att tilldela ett talformat till en åtgärd. Vi kan välja valutaformat för vår åtgärd. När vi använder denna åtgärd i en pivottabell kommer formatet automatiskt att tillämpas.

Tryck på OK-knappen så skapas det nya måttet.

ett nytt fält visas i fönstret Pivottabellfält. Den har en liten fx-ikon till vänster för att beteckna att det är ett mått och inte ett vanligt fält i data.

Vi kan använda det här nya fältet precis som alla andra fält och dra det till området värden för att lägga till vår löpande totala beräkning i pivottabellen.

vad händer med den löpande summan när vi lägger till eller tar bort data från källtabellen? Precis som en vanlig pivottabell behöver vi helt enkelt högerklicka på pivottabellen och välja Uppdatera för att uppdatera beräkningen.

löpande summor med en Power Query

Vi kan också lägga till löpande summor till våra data med power query.

först måste vi importera tabellen till power query. Välj datatabellen och gå till fliken Data och välj alternativet från tabell/intervall. Detta öppnar power query-redigeraren.

nästa kan vi Sortera våra data efter datum. Detta är ett valfritt steg som vi kan lägga till så att om vi ändrar ordningen på våra källdata kommer den löpande summan fortfarande att visas efter datum.

klicka på filterväxeln i datumkolumnrubriken och välj Sortera stigande från alternativen.

Vi måste lägga till en indexkolumn. Detta kommer att användas i den löpande totala beräkningen senare. Gå till fliken Lägg till kolumn och klicka på den lilla pilen bredvid Indexkolumnen för att infoga ett index som börjar vid 1 i första raden.

Vi måste lägga till en ny kolumn i vår Fråga för att beräkna den löpande summan. Gå till fliken Lägg till kolumn och välj kommandot anpassad kolumn.

Vi kan namnge kolumnen som kör totalt och lägga till följande formel.

listan.Range-funktionen skapar en lista med värden från försäljningskolumnen som börjar på 1: a raden (0: e objektet) som sträcker sig över ett antal rader baserat på värdet i indexkolumnen.

listan.Summa funktion lägger sedan upp denna lista över värden som är vår löpande total.

vi behöver inte längre indexkolumnen, den har tjänat sitt syfte och vi kan ta bort den. Högerklicka på kolumnrubriken och välj Ta bort från alternativen.

Vi har vår löpande summa och är färdiga med frågeredigeraren. Vi kan stänga frågan och ladda resultaten i ett nytt kalkylblad. Gå till fliken Hem i frågeredigeraren och tryck på knappen Stäng & ladda.

vad händer med den löpande summan när vi lägger till eller tar bort rader från våra källdata? Vi måste uppdatera power query – utmatningstabellen för att uppdatera den löpande summan med ändringarna. Högerklicka var som helst på bordet och välj Uppdatera för att uppdatera tabellen.

med det valfria sorteringssteget ovan, om vi lägger till datum i ordning till källdata, kommer power query att Sortera efter datum och returnera rätt ordning efter datum för den löpande summan.

slutsatser

det finns många olika alternativ för att beräkna löpande summor i Excel.

Vi har utforskat alternativ inklusive formler i kalkylbladet, pivottabeller, power pivot DAX-formler och power query. Vissa erbjuder en mer robust lösning när du lägger till eller tar bort rader från data, andra metoder erbjuder en enklare implementering.

enkla formler i kalkylbladet är enkla att ställa in men hanterar inte enkelt att infoga eller ta bort nya rader med data. Andra lösningar som pivottabeller, DAX och power query är mer robusta och hanterar enkelt att infoga eller ta bort rader med data men är svårare att ställa in.

det är bra att vara medveten om fördelarna och nackdelarna med varje metod och välja den som passar bäst. Om du inte kommer att infoga eller ta bort nya data kan kalkylbladsformler vara vägen att gå.