Løbende total (også kaldet kumulativ sum) bruges ret ofte i mange situationer. Det er en metrik, der fortæller dig, hvad summen af værdierne er indtil videre.
For eksempel, hvis du har de månedlige salgsdata, vil en løbende sum fortælle dig, hvor meget salg der er foretaget indtil en bestemt dag fra den første dag i måneden.
Der er også nogle andre situationer, hvor løbende total ofte bruges, såsom beregning af din kontantsaldo i dine kontoudtog/hovedbog, tælling af kalorier i din madplan osv.
I Microsoft Excel er der flere forskellige måder at beregne løbende totaler på.
Den metode, du vælger, afhænger også af, hvordan dine data er struktureret.
For eksempel, hvis du har simple tabeldata, kan du bruge en simpel SUM -formel, men hvis du har en Excel -tabel, er det bedst at bruge strukturerede referencer. Du kan også bruge Power Query til at gøre dette.
I denne vejledning vil jeg dække alle disse forskellige metoder til beregne løbende totaler i Excel.
Så lad os komme i gang!
Beregning af løbende total med tabeldata
Hvis du har tabeldata (dvs. en tabel i Excel, som ikke er konverteret til en Excel -tabel), kan du bruge nogle enkle formler til at beregne de løbende totaler.
Brug af tilføjelsesoperatoren
Antag, at du har datidrevne salgsdata, og du vil beregne det samlede løb i kolonne C.
Nedenfor er trinene til at gøre dette.
Trin 1 - Indtast i celle C2, som er den første celle, hvor du vil have det samlede løb
= B2
Dette får simpelthen de samme salgsværdier i celle B2.
Trin 2 - Indtast nedenstående formel i celle C3:
= C2+B3
Trin 3 - Anvend formlen på hele kolonnen. Du kan bruge fyldhåndtaget til at vælge og trække det, eller simpelthen og kopiere og indsætte cellen C3 i alle de resterende celler (hvilket automatisk justerer referencen og giver det rigtige resultat).
Dette vil give dig resultatet som vist nedenfor.
Det er en virkelig enkel metode og fungerer godt i de fleste tilfælde.
Logikken er enkel - hver celle henter værdien over den (som er den kumulative sum indtil datoen før) og tilføjer værdien i cellen ved siden af den (som er salgsværdien for den dag).
Der er kun en ulempe - hvis du sletter en af de eksisterende rækker i dette datasæt, vil alle cellerne nedenfor, der returnerer en referencefejl (#REF!)
Hvis det er en mulighed med dit datasæt, skal du bruge den næste metode, der bruger SUM -formlen
Brug af SUM med delvist låst cellereference
Antag, at du har datidrevne salgsdata, og du vil beregne det samlede løb i kolonne C.
Nedenfor er SUM -formlen, der giver dig den løbende total.
= SUM ($ B $ 2: B2)
Lad mig forklare, hvordan denne formel fungerer.
I ovenstående SUM -formel har jeg brugt referencen til at tilføje som $ B $ 2: B2
- $ B $ 2 - dette er en absolut reference, hvilket betyder, at når jeg kopierer den samme formel i cellerne herunder, vil denne reference ikke ændre sig. Så når formlen kopieres i cellen herunder, ville formlen ændres til SUM ($ B $ 2: B3)
- B2 - dette er den anden del af referencen, som er en relativ reference, hvilket betyder, at dette ville justeres, når jeg kopierer formlen ned eller til højre. Så når du kopierer formlen i cellen herunder, bliver denne værdi B3
Læs også: Absolutte, relative og blandede cellereferencer i Excel
Det gode ved denne metode er, at hvis du sletter nogen af rækkerne i datasættet, ville denne formel justere og stadig give dig de rigtige løbende totaler.
Beregning af løbende total i Excel -tabel
Når du arbejder med tabeldata i Excel, er det en god idé at konvertere dem til en Excel -tabel. Det gør det meget lettere at administrere dataene og gør det også let at bruge værktøjer som Power Query og Power Pivot.
Arbejde med Excel -tabellerne har fordele såsom strukturerede referencer (hvilket gør det virkelig let at henvise til dataene i tabellen og bruge dem i formler) og automatisk justering af referencer, hvis du tilføjer eller sletter data fra tabellen.
Selvom du stadig kan bruge ovenstående formel, som jeg har vist dig i en Excel -tabel, lad mig vise dig nogle bedre metoder til at gøre dette.
Antag, at du har en Excel -tabel som vist nedenfor, og du vil beregne det samlede løb i kolonne C.
Nedenfor er formlen, der gør dette:
= SUM (Salgsdata [[#overskrifter], [Salg]]: [@Salg])
Ovenstående formel ser måske lidt lang ud, men du behøver ikke at skrive den selv. det, du ser i sumformlen, kaldes strukturerede referencer, hvilket er Excels effektive måde at henvise til bestemte datapunkter i en Excel -tabel.
For eksempel refererer SalesData [[#Headers], [Sale]] til salgsoverskriften i tabellen SalesData (SalesData er navnet på Excel -tabellen, som jeg gav, da jeg oprettede tabellen)
Og [@Sale] refererer til værdien i cellen i den samme række i kolonnen Salg.
Jeg har lige forklaret dette her for din forståelse, men selvom du ikke ved noget om strukturerede referencer, kan du stadig nemt oprette denne formel.
Nedenfor er trinene for at gøre dette:
- I celle C2 skal du indtaste = SUM (
- Vælg celle B1, som er overskriften på den kolonne, der har salgsværdien. Du kan bruge musen eller bruge piletasterne. Du vil bemærke, at Excel automatisk indtaster den strukturerede reference for den pågældende celle
- Tilføj et: (kolon symbol)
- Vælg celle B2. Excel ville igen automatisk indsætte den strukturerede reference for cellen
- Luk beslaget og tryk enter
Du vil også bemærke, at du ikke behøver at kopiere formlen i hele kolonnen, en Excel -tabel gør det automatisk for dig.
En anden stor ting ved denne metode er, at hvis du tilføjer en ny post i dette datasæt, ville Excel -tabellen automatisk beregne det samlede løb for alle de nye poster.
Selvom vi har inkluderet kolonneoverskriften i vores formel, skal du huske, at formlen ville ignorere overskriftsteksten og kun overveje dataene i kolonnen
Beregning af kørende total ved hjælp af strømforespørgsel
Power Query er et fantastisk værktøj, når det kommer til at oprette forbindelse til databaser, udtrække data fra flere kilder og transformere dem, før de sættes i Excel.
Hvis du allerede arbejder med Power Query, ville det være mere effektivt at tilføje løbende totaler, mens du transformerer dataene i selve Power Query -editoren (i stedet for først at få dataene i Excel og derefter tilføje de løbende totaler ved hjælp af en af ovenstående metoder beskrevet ovenfor).
Selvom der ikke er nogen indbygget funktion i Power Query til at tilføje løbende totaler (som jeg ville ønske, der var), kan du stadig gøre det ved hjælp af en simpel formel.
Antag, at du har en Excel -tabel som vist nedenfor, og du vil føje de løbende totaler til disse data:
Nedenfor er trinene for at gøre dette:
- Vælg en hvilken som helst celle i Excel -tabellen
- Klik på Data
- På fanen Hent og transformer skal du klikke på ikonet fra tabel/område. Dette åbner tabellen i Power Query -editoren
- [Valgfrit] Hvis din dato -kolonne ikke allerede er sorteret, skal du klikke på filterikonet i kolonnen Dato og derefter klikke på Sorter stigende
- Klik på fanen Tilføj kolonne i Power Query -editoren
- I gruppen Generelt skal du klikke på rullemenuen Indekskolonne (klik ikke på ikonet Indekskolonne, men på den lille sorte, vippede pil lige ved siden af den for at vise flere muligheder)
- Klik på indstillingen 'Fra 1'. Hvis du gør dette, tilføjes en ny indekskolonne, der starter fra en og indtaster tal, der øges med 1 i hele kolonnen
- Klik på ikonet 'Tilpasset kolonne' (som også er under fanen Tilføj kolonne)
- Indtast et navn på den nye kolonne i dialogboksen til tilpasset kolonne, der åbnes. i dette eksempel vil jeg bruge navnet 'Running Total'
- Indtast nedenstående formel i feltet Tilpasset kolonneformel: List.Sum (List.Range (#”Tilføjet indeks” [Salg], 0, [Indeks]))
- Sørg for, at der er et afkrydsningsfelt nederst i dialogboksen, der siger - 'Der er ikke fundet syntaksfejl'
- Klik på OK. Dette ville tilføje en ny løbende samlet kolonne
- Fjern indekssøjlen
- Klik på fanen Filer, og klik derefter på 'Luk og indlæs'
Ovenstående trin ville indsætte et nyt ark i din projektmappe med en tabel, der har de løbende totaler.
Hvis du nu tænker, at disse bare er for mange trin i forhold til de tidligere metoder til at bruge enkle formler, har du ret.
Hvis du allerede har et datasæt, og alt du skal gøre er at tilføje løbende totaler, er det bedre ikke at bruge Power Query.
Brug af Power Query giver mening, hvor du skal udtrække data fra en database eller kombinere data fra flere forskellige projektmapper og i processen også tilføje løbende totaler til den.
Når du først har foretaget denne automatisering ved hjælp af Power Query, skal du ikke genoprette forespørgslen, næste gang dit datasæt ændres, og det vil give dig resultatet baseret på det nye datasæt.
Hvordan virker det?
Lad mig nu hurtigt forklare, hvad der sker i denne metode.
Den første ting, vi gør i Power Query -editoren, er at indsætte en indekssøjle, der starter fra en og øges med en, når den går ned i cellerne.
Vi gør dette, fordi vi skal bruge denne kolonne, mens vi beregner det samlede løb i en anden kolonne, som vi indsætter i det næste trin.
Derefter indsætter vi en brugerdefineret kolonne og bruger nedenstående formel
List.Sum (List.Range (#"Tilføjet indeks" [Salg], 0, [Indeks]))
Dette er en List.Sum -formel, der giver dig summen af det område, der er angivet inden for det.
Og dette område er angivet ved hjælp af funktionen List.Range.
List.Range -funktionen giver det angivne område i salgskolonnen som output, og dette område ændres baseret på indeksværdien. For eksempel for den første rekord ville intervallet simpelthen være den første salgsværdi. Og når du går ned i cellerne, vil dette område udvides.
Så for den første celle. List.Sum ville kun give dig summen af den første salgsværdi, og for den anden celle ville den give dig summen for de to første salgsværdier og så videre.
Selvom denne metode fungerer godt, bliver den virkelig langsom med store datasæt - tusinder af rækker. Hvis du har at gøre med et stort datasæt og vil tilføje løbende totaler til det, skal du kigge på denne vejledning, der viser andre hurtigere metoder.Beregning af løbende total baseret på kriterier
Hidtil har vi set eksempler, hvor vi har beregnet den samlede drift for alle værdierne i en kolonne.
Men der kan være tilfælde, hvor du vil beregne den samlede drift for bestemte poster.
For eksempel har jeg nedenfor et datasæt, og jeg vil beregne den samlede drift for printere og scannere separat i to forskellige kolonner.
Dette kan gøres ved hjælp af en SUMIF -formel, der beregner det samlede løb, samtidig med at den angivne betingelse er opfyldt.
Nedenfor er formlen, der gør dette for printerkolonnerne:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Tilsvarende, for at beregne den løbende total for scannere, skal du bruge nedenstående formel:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
I ovenstående formler har jeg brugt SUMIF, hvilket ville give mig summen i et område, når de angivne kriterier er opfyldt.
Formlen tager tre argumenter:
- rækkevidde: dette er kriterieområdet, der ville blive kontrolleret i forhold til de angivne kriterier
- kriterier: dette er de kriterier, der kun ville blive tjekket ind, hvis dette kriterium er opfyldt, vil værdierne i det tredje argument, som er summen, blive tilføjet
- [sum_range]: dette er det suminterval, hvorfra værdierne vil blive tilføjet, hvis kriterierne er opfyldt
Også i rækkevidde og sum_range argument, har jeg låst den anden del af referencen, så når vi går ned i cellerne, vil området blive ved med at udvide. Dette giver os mulighed for kun at overveje og tilføje værdier indtil dette område (derfor kører totaler).
I denne formel har jeg brugt headerkolonnen (printer og scanner) som kriterier. du kan også hardkode kriterierne, hvis dine kolonneoverskrifter ikke helt er de samme som kriterieteksten.
Hvis du har flere betingelser, som du skal kontrollere, kan du bruge SUMIFS -formlen.
Kører i alt i pivottabeller
Hvis du vil tilføje løbende totaler i et pivottabelresultat, kan du nemt gøre det ved hjælp af en indbygget funktionalitet i pivottabeller.
Antag, at du har en pivottabel som vist herunder, hvor jeg har datoen i en kolonne og salgsværdien i den anden kolonne.
Nedenfor er trinene for at tilføje en ekstra kolonne, der viser den løbende sum af salget efter dato:
- Træk feltet Salg, og placer det i området Værdi.
- Dette tilføjer endnu en kolonne med salgsværdierne
- Klik på indstillingen Sum af salg2 i området Værdi
- Klik på indstillingen 'Værdifeltindstillinger'
- I dialogboksen Værdifeltindstillinger skal du ændre brugerdefineret navn til 'Kører totaler'
- Klik på fanen 'Vis værdi som'
- I rullemenuen Vis værdi som vælger du indstillingen ‘Kører alt i’
- Sørg for, at dato er valgt i feltet Basismuligheder
- Klik på Ok
Ovenstående trin ville ændre den anden salgskolonne til kolonnen Running Total.
Så det er nogle af de måder, du kan bruge til at beregne det samlede løb i Excel. Hvis du har data i tabelformat, kan du bruge enkle formler, og hvis du har en Excel -tabel, kan du bruge formler, der gør brug af strukturerede referencer.
Jeg har også dækket, hvordan man beregner løbende total ved hjælp af Power Query og i pivottabeller.
Jeg håber, at du fandt denne vejledning nyttig.
