løpende total (også kjent somkumulativ sum) er svært vanlig brukt i mange tilfeller.Dette er en beregning som forteller deg hva summen av verdiene så langt er.
Hvis du for eksempel har månedlige salgsdata, vil en løpende sum fortelle deg hvor mange salg som ble gjort fra den første dagen i måneden til en bestemt dato.
Det er andre situasjoner hvor totaler ofte brukes, for eksempel beregning av kontantbeholdninger i kontoutskrifter/reskontro, beregning av kalorier i måltidsplaner, etc.
I Microsoft Excel er det flere forskjellige måter å beregne løpende totaler på.
Metoden du velger avhenger også av strukturen til dataene.
Hvis du for eksempel har enkle tabelldata, kan du bruke en enkel SUM-formel, men hvis du har en Excel-tabell er det bedre å bruke strukturerte referanser.Du kan også bruke Power Query til å gjøre dette.
I denne opplæringen vil jeg dekke dem alleBeregn en løpende total i Excelulike metoder.
Så la oss komme i gang!
Innhold
Beregn løpende totaler ved å bruke tabelldata
Hvis du har tabelldata (det vil si tabeller i Excel som ikke er konvertert til Excel-tabeller), kan du bruke noen enkle formler for å beregne løpende totaler.
Bruk addisjonsoperatoren
Anta at du har salgsdata etter dato og du vil在 i kolonne Cregne utLøpende totalt.
Her er fremgangsmåten for å gjøre dette.
trinn 1 – I celle C2, den første cellen du vil kjøre totalen for, skriv inn
= B2
Dette får bare samme salgsverdi i celle B2.
Steg 2– I celle C3 skriver du inn følgende formel:
= C2 + B3
Steg 3– Bruk formelen på hele kolonnen.Du kan bruke fyllhåndtaket til å velge og dra det, eller ganske enkelt kopiere og lime inn celle C3 til alle gjenværende celler (dette vil automatisk justere referansen og gi riktig resultat).
Dette vil gi deg resultatet vist nedenfor.
Dette er en veldig enkel metode som fungerer bra i de fleste tilfeller.
Logikken er enkel - hver celle tar verdien på den (som er den kumulative summen opp til forrige dag), og legger til verdien i den tilstøtende cellen (som er dagens salgsverdi).
Det er bare én ulempe - hvis du sletter en eksisterende rad i dette datasettet, vil alle cellene nedenfor returnere en referansefeil (#REF!)
Hvis datasettet ditt har denne muligheten, bruk neste metode ved å bruke SUM-formelen
Bruk SUM med delvis låste cellereferanser
Anta at du har salgsdata etter dato, og du vil beregne en løpende totalsum i kolonne C.
Nedenfor er SUM-formelen som vil gi deg en løpende total.
=SUM($B$2:B2)
La meg forklare hvordan denne formelen fungerer.
I SUM-formelen ovenfor brukte jeg referansen for å legge til som $B$2:B2
- $B$2 - Dette er en absolutt referanse, noe som betyr at denne referansen ikke endres når jeg kopierer den samme formelen i cellen nedenfor.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, det er en relativ referanse, som betyr at når jeg kopierer formelen ned eller til høyre, justeres denne.Så når du kopierer formelen i cellen under, blir verdien B3
Det fine med denne tilnærmingen er at hvis du sletter noen rader i datasettet, justeres denne formelen og gir deg fortsatt den riktige løpende totalen.
Beregn løpende total i Excel-ark
Når du arbeider med tabelldata i Excel, er det best å konvertere det til en Excel-tabell.Det gjør det enklere å administrere data, og det gjør det også enkelt å bruke verktøy som Power Query og Power Pivot.
Å bruke en Excel-tabell gir flere fordeler, som strukturerte referanser (som gjør det veldig enkelt å referere til data i tabellen og bruke dem i formler), og automatisk justering av referanser når du legger til eller fjerner data fra tabellen.
Mens du fortsatt kan bruke formelen ovenfor jeg viste deg i et Excel-ark, la meg vise deg noen bedre måter å gjøre det på.
Anta at du har en Excel-tabell som den nedenfor, og du vil beregne en løpende total i kolonne C.
Her er formelen for å gjøre dette:
=SUM(Salgsdata[[#Overskrifter],[Salg]]:[@Salg])
Formelen ovenfor kan virke litt lang, men du trenger ikke skrive den selv.Det du ser i en summeringsformel kalles en strukturert referanse, og det er en effektiv måte for Excel å referere til bestemte datapunkter i en Excel-tabell.
For eksempel refererer SalesData[[#Headers],[Salg]] til Sales-overskriften i SalesData-tabellen (SalesData er navnet på Excel-tabellen jeg ga da jeg opprettet tabellen)
Mens [@Salg] refererer til verdien i en celle i samme rad i Salg-kolonnen.
Jeg forklarer bare dette her for din forståelse, men selv om du ikke kan noe om strukturerte referanser, kan du fortsatt enkelt lage denne formelen.
Her er trinnene for å gjøre dette:
- I celle C2 skriver du inn =SUM(
- Velg celle B1, som er overskriften til kolonnen med salgsverdi.Du kan bruke musen eller bruke piltastene.Du vil legge merke til at Excel automatisk legger inn en strukturert referanse til cellen
- Legg til: (kolon)
- Velg celle B2.Excel vil automatisk sette inn strukturerte referanser til celler igjen
- Lukk parentesen og trykk Enter
Du vil også legge merke til at du ikke trenger å kopiere formelen i hele kolonnen, Excel-arket gjør det automatisk for deg.
En annen fordel med denne tilnærmingen er at hvis du legger til en ny post i dette datasettet, beregner Excel-arket automatisk en løpende totalsum for alle nye poster.
Selv om vi inkluderte kolonneoverskriftene i formelen, husk at formelen ignorerer overskriftsteksten og kun tar hensyn til dataene i kolonnen
Beregn løpende totaler med Power Query
Power Query er et fantastisk verktøy når det gjelder å koble til databaser, trekke ut data fra flere kilder og transformere det før det settes inn i Excel.
Hvis du allerede bruker Power Query, er det mer effektivt å legge til løpende totaler når du transformerer dataene i selve Power Query Editor (i stedet for først å hente dataene i Excel og deretter legge til løpende totaler ved å bruke en av metodene ovenfor).
Selv om det ikke er noen innebygd funksjonalitet i Power Query for å legge til løpende totaler (jeg skulle ønske det var), kan du fortsatt gjøre det med en enkel formel.
Anta at du har en Excel-tabell som ser slik ut, og du vil legge til løpende totaler til disse dataene:
Her er trinnene for å gjøre dette:
- Velg en hvilken som helst celle i en Excel-tabell
- klikkdata
- I kategorien Get & Transform klikker du på fra tabell/område-ikonet.Dette åpner tabellen i Power Query Editor
- [Valgfritt] Hvis datokolonnen din ikke allerede er sortert, klikker du på filterikonet i datokolonnen, og klikker deretter Sorter stigende
- Klikk på fanen Legg til kolonne i Power Query Editor
- I Generelt-gruppen klikker du på rullegardinmenyen Indekskolonne (ikke klikk på Indekskolonne-ikonet, men den lille svarte, skråstilte pilen ved siden av for å vise flere alternativer)
- Klikk på "Fra 1"-alternativet.Hvis du gjør dette, legges til en ny indekskolonne som starter på 1 og legger inn tall som økes med 1 gjennom hele kolonnen
- Klikk på Egendefinerte kolonner-ikonet (også i fanen Legg til kolonner)
- I dialogboksen Egendefinerte kolonner som åpnes, skriv inn et navn for den nye kolonnen.I dette eksemplet vil jeg bruke navnet "Running Total"
- I feltet Egendefinert kolonneformel skriver du inn følgende formel:List.Sum(List.Range(#"Added Index"[Salg],0,[Index]))
- Pass på at det er en avmerkingsboks nederst i dialogboksen som sier "Ingen syntaksfeil oppdaget"
- Klikk OK.Dette vil legge til en ny løpende totalkolonne
- slipp indekskolonnen
- Klikk på Fil-fanen, og klikk deretter på Lukk og last
Trinnene ovenfor vil sette inn et nytt regneark i arbeidsboken din med en tabell med løpende totaler.
Nå, hvis du synes dette er for mange trinn sammenlignet med den forrige tilnærmingen med enkle formler, har du rett.
Hvis du allerede har et datasett og alt du trenger å gjøre er å legge til løpende totaler, er det best å ikke bruke Power Query.
Å bruke Power Query er fornuftig i situasjoner der du må trekke ut data fra en database eller kombinere data fra flere forskjellige arbeidsbøker og også legge til løpende totaler til dem i prosessen.
Når du har automatisert dette med Power Query, neste gang datasettet endres, trenger du ikke å gjøre det igjen, du kan bare oppdatere spørringen og den vil gi deg resultater basert på det nye datasettet.
Hvordan virker dette?
La meg nå raskt forklare hva som skjer med denne tilnærmingen.
Det første vi gjør i Power Query Editor er å sette inn en indekskolonne som starter på 1 og øker etter hvert som den går nedover cellen.
Vi gjør dette fordi vi må bruke denne kolonnen når vi beregner den løpende totalen i en annen kolonne som settes inn i neste trinn.
Deretter setter vi inn en egendefinert kolonne og bruker følgende formel
List.Sum(List.Range(#"Added Index"[Salg],0,[Index]))
Dette er en List.Sum-formel som vil gi deg summen av området spesifisert i den.
Området spesifiseres ved hjelp av List.Range-funksjonen.
List.Range-funksjonen tar som utdata det spesifiserte området i salgskolonnen, og området varierer i henhold til indeksverdien.For eksempel, for den første posten, er området den første salgsverdien.Dette området utvides etter hvert som du går nedover cellen.
Så for den første cellen.List.Sum vil bare gi deg summen av den første salgsverdien, for den andre cellen vil den gi deg summen av de to første salgsverdiene, og så videre.
Selv om denne tilnærmingen fungerer bra, kan den bli veldig treg for store datasett (tusenvis av rader).
Beregn løpende totaler basert på kriterier
Så langt har vi sett eksempler på å beregne en løpende sum av alle verdier i en kolonne.
Men i noen tilfeller vil du kanskje beregne en løpende totalsum for en bestemt post.
Nedenfor har jeg for eksempel et datasett der jeg ønsker å telle den løpende summen av skrivere og skannere i to forskjellige kolonner.
Dette kan gjøres ved å bruke SUMIF-formelen, som beregner en løpende total samtidig som man sikrer at spesifiserte betingelser er oppfylt.
Her er formelen for å gjøre dette for skriverkolonnen:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
På samme måte, for å beregne totalsummen til skanneren, bruk følgende formel:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
I formelen ovenfor har jeg brukt SUMIF som vil gi meg en sum i et område når spesifiserte betingelser er oppfylt.
Formelen tar tre parametere:
- område: Dette er utvalget av tilstander som vil bli sjekket mot den angitte tilstanden
- kriterier: dette er kriteriet som kun vil bli sjekket hvis dette kriteriet er oppfylt, så vil verdien i den tredje parameteren, sumområdet bli lagt til
- [sum_range]: dette er området for summen av verdiene som skal legges til når betingelsen er oppfylt
I tillegg, iområdeOgsum_rangeparameter, har jeg låst den andre delen av referansen slik at rekkevidden utvides etter hvert som vi beveger oss nedover i cellen.Dette lar oss bare vurdere og legge til verdier opp til det området (derav en løpende totalsum).
I denne formelen bruker jeg overskriftskolonnen (Skriver og skanner) som kriterier.Du kan også hardkode standarden hvis kolonneoverskriftene dine ikke er helt de samme som standardteksten.
Hvis du trenger å sjekke flere forhold, kan du bruke SUMIFS-formelen.
Løpende totaler i en pivottabell
Hvis du vil legge til en løpende totalsum til pivottabellresultatene dine, kan du enkelt gjøre det ved å bruke den innebygde funksjonaliteten i pivottabeller.
Anta at du har en pivottabell som den nedenfor med datoer i én kolonne og salgsverdier i en annen.
Her er trinnene for å legge til en ekstra kolonne som viser det kumulative salget etter dato:
- Dra Salg-feltet og slipp det i Verdi-området.
- Dette vil legge til en annen kolonne med salgsverdien
- Klikk på Totalt salg 2-alternativet i Verdi-området
- Klikk på alternativet Verdifeltinnstillinger
- I dialogboksen Innstillinger for verdifelt endrer du Egendefinert navn til Løpende total
- Klikk på "Vis verdi som"-fanen
- I rullegardinmenyen Vis verdi som velger du alternativet "Running Total in".
- Kontroller at Dato er valgt i Basic Fields-alternativene
- Klikk OK
Trinnene ovenfor vil endre den andre salgskolonnen til en "løpende total"-kolonne.
Så dette er noen av metodene du kan bruke til å beregne løpende totaler i Excel.Hvis du har data i tabellformat kan du bruke enkle formler, og hvis du har Excel-tabeller kan du bruke formler som bruker strukturerte referanser.
Jeg dekket også hvordan du beregner løpende totaler ved å bruke Power Query og pivottabeller.
Jeg håper du fant denne opplæringen nyttig.
