Running total (også kalt kumulativ sum) er ganske vanlig brukt i mange situasjoner. Det er en beregning som forteller deg hva som er summen av verdiene så langt.
For eksempel, hvis du har månedlige salgsdata, vil en løpende sum fortelle deg hvor mye salg som er gjort til en bestemt dag fra den første dagen i måneden.
Det er også noen andre situasjoner der løpende totalsum ofte brukes, for eksempel beregning av kontantsaldo i kontoutskrifter/regnskap, telling av kalorier i måltidsplanen, etc.
I Microsoft Excel er det flere forskjellige måter å beregne løpende totaler på.
Metoden du velger vil også avhenge av hvordan dataene dine er strukturert.
For eksempel, hvis du har enkle tabelldata, kan du bruke en enkel SUM -formel, men hvis du har en Excel -tabell, er det best å bruke strukturerte referanser. Du kan også bruke Power Query til å gjøre dette.
I denne opplæringen skal jeg dekke alle disse forskjellige metodene til beregne løpende totaler i Excel.
Så la oss komme i gang!
Beregning av løpende total med tabelldata
Hvis du har tabelldata (dvs. en tabell i Excel som ikke er konvertert til en Excel -tabell), kan du bruke noen enkle formler til å beregne løpende totaler.
Bruke tilleggsoperatøren
Anta at du har datomessige salgsdata, og du vil beregne den totale summen i kolonne C.
Nedenfor er trinnene for å gjøre dette.
Trinn 1 - I celle C2, som er den første cellen der du vil ha den totale summen, skriver du inn
= B2
Dette vil ganske enkelt få de samme salgsverdiene i celle B2.
Steg 2 - Skriv inn formelen nedenfor i celle C3:
= C2+B3
Trinn 3 - Bruk formelen på hele kolonnen. Du kan bruke fyllehåndtaket til å velge og dra den, eller ganske enkelt kopiere og lime inn cellen C3 til alle de resterende cellene (som automatisk justerer referansen og gir det riktige resultatet).
Dette vil gi deg resultatet som vist nedenfor.
Det er en veldig enkel metode og fungerer bra i de fleste tilfeller.
Logikken er enkel - hver celle henter verdien over den (som er den kumulative summen til datoen før) og legger til verdien i cellen ved siden av den (som er salgsverdien for den dagen).
Det er bare en ulempe - hvis du sletter noen av de eksisterende radene i dette datasettet, vil alle cellene nedenfor som returnerer en referansefeil (#REF!)
Hvis det er en mulighet med datasettet ditt, kan du bruke den neste metoden som bruker SUM -formelen
Bruke SUM med delvis låst cellereferanse
Anta at du har datomessige salgsdata, og du vil beregne den totale summen i kolonne C.
Nedenfor er SUMM -formelen som gir deg løpende sum.
= SUM ($ B $ 2: B2)
La meg forklare hvordan denne formelen fungerer.
I formelen ovenfor SUM har jeg brukt referansen til å legge til som $ B $ 2: B2
- $ B $ 2 - dette er en absolutt referanse, noe som betyr at når jeg kopierer den samme formelen i cellene nedenfor, kommer denne referansen ikke til å endre seg. Så når du kopierer formelen i cellen nedenfor, vil formelen endres til SUM ($ B $ 2: B3)
- B2 - dette er den andre delen av referansen som er en relativ referanse, noe som betyr at dette ville justeres når jeg kopierer formelen ned eller til høyre. Så når du kopierer formelen i cellen nedenfor, blir denne verdien B3
Les også: Absolutte, relative og blandede cellereferanser i Excel
Det flotte med denne metoden er at i tilfelle du sletter noen av radene i datasettet, vil denne formelen justere og fremdeles gi deg de riktige totalsummerene.
Beregning av løpende total i Excel -tabell
Når du arbeider med tabelldata i Excel, er det en god idé å konvertere dem til en Excel -tabell. Det gjør det mye enklere å administrere dataene, og gjør det også enkelt å bruke verktøy som Power Query og Power Pivot.
Arbeidet med Excel -tabellene gir fordeler som strukturerte referanser (som gjør det veldig enkelt å referere til dataene i tabellen og bruke dem i formler), og automatisk justering av referanser i tilfelle du legger til eller sletter data fra tabellen.
Selv om du fremdeles kan bruke formelen ovenfor som jeg har vist deg i en Excel -tabell, la meg vise deg noen bedre metoder for å gjøre dette.
Anta at du har en Excel -tabell som vist nedenfor, og du vil beregne den totale summen i kolonne C.
Nedenfor er formelen som gjør dette:
= SUMME (SalesData [[#Headers], [Sale]]: [@Sale])
Formelen ovenfor kan se litt lang ut, men du trenger ikke å skrive den selv. det du ser i sumformelen kalles strukturerte referanser, som er Excels effektive måte å referere til spesifikke datapunkter i en Excel -tabell.
For eksempel refererer SalesData [[#Headers], [Sale]] til salgsoverskriften i SalesData -tabellen (SalesData er navnet på Excel -tabellen jeg ga da jeg opprettet tabellen)
Og [@Sale] refererer til verdien i cellen i samme rad i salgskolonnen.
Jeg forklarte dette her for din forståelse, men selv om du ikke vet noe om strukturerte referanser, kan du fortsatt enkelt lage denne formelen.
Nedenfor er trinnene for å gjøre dette:
- I celle C2, skriv inn = SUM (
- Velg celle B1, som er overskriften til kolonnen som har salgsverdien. Du kan bruke musen eller bruke piltastene. Du vil legge merke til at Excel automatisk angir den strukturerte referansen for den cellen
- Legg til et: (kolon -symbol)
- Velg celle B2. Excel ville igjen automatisk sette inn den strukturerte referansen for cellen
- Lukk braketten og trykk enter
Du vil også legge merke til at du ikke trenger å kopiere formelen i hele kolonnen, en Excel -tabell gjør det automatisk for deg.
En annen flott ting med denne metoden er at i tilfelle du legger til en ny post i dette datasettet, vil Excel -tabellen automatisk beregne den totale summen for alle de nye postene.
Selv om vi har inkludert overskriften til kolonnen i formelen vår, husk at formelen vil ignorere overskriftsteksten og bare vurdere dataene i kolonnen
Beregning av løpende total ved hjelp av Power Query
Power Query er et fantastisk verktøy når det gjelder å koble til databaser, trekke ut data fra flere kilder og transformere dem før du setter dem inn i Excel.
Hvis du allerede jobber med Power Query, ville det være mer effektivt å legge til løpende totaler mens du transformerer dataene i selve Power Query -redigeringsprogrammet (i stedet for først å få dataene i Excel og deretter legge til løpende totaler ved å bruke noen av de ovennevnte metodene dekket ovenfor).
Selv om det ikke er noen innebygd funksjon i Power Query for å legge til løpende totaler (som jeg skulle ønske det var), kan du fortsatt gjøre det ved å bruke en enkel formel.
Anta at du har en Excel -tabell som vist nedenfor, og du vil legge til løpende totaler til disse dataene:
Nedenfor er trinnene for å gjøre dette:
- Velg en celle i Excel -tabellen
- Klikk på Data
- I kategorien Get & Transform klikker du på ikonet fra Table/Range. Dette åpner tabellen i Power Query -editoren
- [Valgfritt] Hvis Dato -kolonnen ikke allerede er sortert, klikker du på filterikonet i Dato -kolonnen og klikker deretter på Sorter stigende
- Klikk på kategorien Legg til kolonne i Power Query -editoren
- I gruppen Generelt klikker du på rullegardinmenyen Indeks kolonne (ikke klikk på indeks kolonnen -ikonet, men på den lille svarte pilen ved siden av den for å vise flere alternativer)
- Klikk på "Fra 1" -alternativet. Hvis du gjør dette, vil du legge til en ny indekskolonne som starter fra en og angir tall som øker med 1 i hele kolonnen
- Klikk på ikonet "Egendefinert kolonne" (som også er i kategorien Legg til kolonne)
- Skriv inn et navn på den nye kolonnen i dialogboksen for egendefinert kolonne som åpnes. i dette eksemplet vil jeg bruke navnet 'Running Total'
- Skriv inn formelen nedenfor i feltet Egendefinert kolonneformel: List.Sum (List.Range (#”Lagt til indeks” [Salg], 0, [Indeks]))
- Sørg for at det er en avmerkingsboks nederst i dialogboksen som sier - 'Ingen syntaksfeil er oppdaget'
- Klikk OK. Dette vil legge til en ny løpende total kolonne
- Fjern indekskolonnen
- Klikk på Fil -fanen og klikk deretter på "Lukk og last"
Trinnene ovenfor vil sette inn et nytt ark i arbeidsboken din med en tabell som har løpende totaler.
Hvis du nå tenker at dette er for mange trinn i forhold til de tidligere metodene for å bruke enkle formler, har du rett.
Hvis du allerede har et datasett og alt du trenger å gjøre er å legge til løpende summer, er det bedre å ikke bruke Power Query.
Det er fornuftig å bruke Power Query hvor du må trekke ut data fra en database eller kombinere data fra flere forskjellige arbeidsbøker og i prosessen også legge til løpende totaler.
Når du gjør denne automatiseringen ved hjelp av Power Query, trenger du ikke gjøre det igjen neste gang datasettet endres, og du kan bare oppdatere spørringen, og det vil gi deg resultatet basert på det nye datasettet.
Hvordan virker dette?
La meg nå raskt forklare hva som skjer med denne metoden.
Det første vi gjør i Power Query -editoren er å sette inn en indekskolonne som starter fra en og øker med en når den går nedover cellene.
Vi gjør dette fordi vi må bruke denne kolonnen mens vi beregner den totale summen i en annen kolonne som vi setter inn i neste trinn.
Deretter setter vi inn en egendefinert kolonne og bruker formelen nedenfor
List.Sum (List.Range (#"Lagt til indeks" [Salg], 0, [Indeks]))
Dette er en List.Sum -formel som vil gi deg summen av området som er spesifisert i den.
Og det området er spesifisert ved hjelp av List.Range -funksjonen.
List.Range -funksjonen gir det angitte området i salgskolonnen som utdata, og dette området endres basert på indeksverdien. For eksempel, for den første posten, vil serien ganske enkelt være den første salgsverdien. Og når du går nedover cellene, vil dette området utvides.
Så, for den første cellen. List.Sum vil bare gi deg summen av den første salgsverdien, og for den andre cellen vil den gi deg summen for de to første salgsverdiene og så videre.
Selv om denne metoden fungerer bra, blir den veldig treg med store datasett - tusenvis av rader. Hvis du har å gjøre med et stort datasett og vil legge til løpende totaler i det, kan du se på denne opplæringen som viser andre metoder som er raskere.Beregning av løpende total basert på kriterier
Så langt har vi sett eksempler hvor vi har beregnet den totale summen for alle verdiene i en kolonne.
Men det kan være tilfeller der du vil beregne den totale summen for bestemte poster.
For eksempel har jeg et datasett nedenfor, og jeg vil beregne den totale summen for skrivere og skannere separat i to forskjellige kolonner.
Dette kan gjøres ved å bruke en SUMIF -formel som beregner løpende total mens du kontrollerer at den angitte betingelsen er oppfylt.
Nedenfor er formelen som gjør dette for skriverkolonnene:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
På samme måte, for å beregne den totale summen for skannere, bruker du formelen nedenfor:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
I formlene ovenfor har jeg brukt SUMIF, som vil gi meg summen i et område når de angitte kriteriene er oppfylt.
Formelen tar tre argumenter:
- område: dette er kriteriumsområdet som vil bli sjekket opp mot de angitte kriteriene
- kriterier: dette er kriteriene som bare ville blitt sjekket inn hvis dette kriteriet er oppfylt, vil verdiene i det tredje argumentet, som er sumområdet, bli lagt til
- [sum_range]: dette er sumområdet som verdiene vil bli lagt til hvis kriteriene er oppfylt
Også i område og sum_range argumentet, har jeg låst den andre delen av referansen, slik at når vi går nedover cellene, vil området fortsette å ekspandere. Dette lar oss bare vurdere og legge til verdier til dette området (derav kjørende totaler).
I denne formelen har jeg brukt topptekolonnen (skriver og skanner) som kriterier. Du kan også hardkode kriteriene hvis kolonneoverskriftene ikke er nøyaktig de samme som kriterieteksten.
Hvis du har flere forhold du må sjekke, kan du bruke SUMIFS -formelen.
Kjører totalt i pivottabeller
Hvis du vil legge til løpende totaler i et pivottabellresultat, kan du enkelt gjøre det ved å bruke en innebygd funksjonalitet i pivottabeller.
Anta at du har en pivottabell som vist nedenfor der jeg har datoen i den ene kolonnen og salgsverdien i den andre kolonnen.
Nedenfor er trinnene for å legge til en ekstra kolonne som viser den løpende summen av salget etter dato:
- Dra Salg -feltet og legg det i Verdi -området.
- Dette vil legge til en annen kolonne med salgsverdiene
- Klikk på alternativet Sum av salg2 i området Verdi
- Klikk på alternativet "Verdifeltinnstillinger"
- I dialogboksen Innstillinger for verdifelt endrer du det egendefinerte navnet til "Running Totals"
- Klikk på fanen "Vis verdi som"
- Velg rullegardinmenyen Vis verdi som
- I alternativene for Base -feltet må du kontrollere at Dato er valgt
- Klikk Ok
Trinnene ovenfor vil endre den andre salgskolonnen til Running Total -kolonnen.
Så dette er noen av måtene du kan bruke til å beregne den totale summen i Excel. Hvis du har data i tabellformat, kan du bruke enkle formler, og hvis du har en Excel -tabell, kan du bruke formler som bruker strukturerte referanser.
Jeg har også dekket hvordan jeg beregner løpende total ved hjelp av Power Query og i pivottabeller.
Jeg håper du synes denne opplæringen var nyttig.
