lopende totaal (ook bekend askumulatiewe som) word in baie gevalle baie algemeen gebruik.Dit is 'n aanwyser wat jou vertel wat die som van die waardes tot dusver is.
Byvoorbeeld, as jy maandelikse verkoopsdata het, sal 'n lopende totaal jou vertel hoeveel verkope gemaak is vanaf die eerste dag van die maand tot 'n spesifieke datum.
Daar is ander situasies waar totale dikwels gebruik word, soos die berekening van kontantsaldo's in bankstate/grootboeke, die berekening van kalorieë in maaltydplanne, ens.
In Microsoft Excel is daar verskeie maniere om lopende totale te bereken.
Die metode wat jy kies hang ook af van die struktuur van die data.
Byvoorbeeld, as jy eenvoudige tabeldata het, kan jy 'n eenvoudige SOM-formule gebruik, maar as jy 'n Excel-tabel het, is dit beter om gestruktureerde verwysings te gebruik.Jy kan ook Power Query gebruik om dit te doen.
In hierdie tutoriaal sal ek almal dekBereken 'n lopende totaal in Excelverskillende metodes.
So kom ons begin!
Contents [show]
Bereken lopende totale met behulp van tabeldata
As jy tabeldata het (dit wil sê tabelle in Excel wat nie na Excel-tabelle omgeskakel word nie), kan jy 'n paar eenvoudige formules gebruik om lopende totale te bereken.
Gebruik die opteloperateur
Gestel jy het verkoopsdata volgens datum en jy wil在 in kolom CberekenLooptotaal.
Hier is die stappe om dit te doen.
stap 1 – In sel C2, die eerste sel waarvoor jy die totaal wil laat loop, voer in
=B2
Dit kry net dieselfde verkoopswaarde in sel B2.
Stap 2– Voer die volgende formule in sel C3 in:
= C2 + B3
Stap 3– Pas die formule toe op die hele kolom.Jy kan die vulhandvatsel gebruik om dit te kies en te sleep, of eenvoudig kopieer-plak sel C3 na al die oorblywende selle (dit sal die verwysing outomaties aanpas en die korrekte resultaat gee).
Dit sal jou die resultaat gee wat hieronder getoon word.
Dit is 'n baie eenvoudige metode wat in die meeste gevalle goed werk.
Die logika is eenvoudig - elke sel neem die waarde daarop (wat die kumulatiewe som tot die vorige dag is), en voeg die waarde in die aangrensende sel by (wat die huidige dag se verkoopswaarde is).
Daar is net een nadeel - as jy enige bestaande ry in hierdie datastel uitvee, sal alle selle hieronder 'n verwysingsfout terugstuur (#REF!)
As jou datastel hierdie moontlikheid het, gebruik die volgende metode deur die SOM-formule te gebruik
Gebruik SOM met gedeeltelik geslote selverwysings
Gestel jy het verkoopsdata volgens datum, en jy wil 'n lopende totaal in kolom C bereken.
Hieronder is die SOM-formule wat vir jou 'n lopende totaal sal gee.
=SOM($B$2:B2)
Kom ek verduidelik hoe hierdie formule werk.
In die SUM-formule hierbo het ek die verwysing gebruik om by te voeg as $B$2:B2
- $B$2 - Dit is 'n absolute verwysing, wat beteken dat hierdie verwysing nie verander wanneer ek dieselfde formule in die sel hieronder kopieer nie.So wanneer jy die formule in die sel hieronder kopieer, sal die formule verander na SUM($B$2:B3)
- B2 - Dit is die tweede deel van die verwysing, dit is 'n relatiewe verwysing, wat beteken dat wanneer ek die formule af of regs kopieer, dit aanpas.Dus wanneer die formule in die sel hieronder gekopieer word, word die waarde B3
Die lekker ding van hierdie benadering is dat as jy enige rye in die datastel uitvee, hierdie formule aanpas en steeds die korrekte lopende totaal gee.
Bereken lopende totaal in Excel-blad
Wanneer u met tabeldata in Excel werk, is dit die beste om dit na 'n Excel-tabel om te skakel.Dit maak dit makliker om data te bestuur, en dit maak dit ook maklik om gereedskap soos Power Query en Power Pivot te gebruik.
Die gebruik van 'n Excel-tabel bied verskeie voordele, soos gestruktureerde verwysings (wat dit baie maklik maak om data in die tabel te verwys en dit in formules te gebruik), en outomatiese aanpassing van verwysings soos jy data byvoeg of uit die tabel verwyder.
Terwyl jy nog steeds die bogenoemde formule kan gebruik wat ek jou in 'n Excel-blad gewys het, laat ek jou 'n paar beter maniere wys om dit te doen.
Gestel jy het 'n Excel-tabel soos die een hieronder, en jy wil 'n lopende totaal in kolom C bereken.
Hier is die formule om dit te doen:
=SOM(Verkoopdata[[#Opskrifte],[Verkope]]:[@Verkope])
Die formule hierbo lyk dalk 'n bietjie lank, maar jy hoef dit nie self te skryf nie.Wat jy in 'n somformule sien, word 'n gestruktureerde verwysing genoem, en dit is 'n doeltreffende manier vir Excel om na spesifieke datapunte in 'n Excel-tabel te verwys.
Byvoorbeeld, SalesData[[#Headers],[Sale]] verwys na die Verkope-opskrif in die SalesData-tabel (SalesData is die naam van die Excel-tabel wat ek gegee het toe ek die tabel geskep het)
Terwyl [@Sale] verwys na die waarde in 'n sel in dieselfde ry van die Sale-kolom.
Ek verduidelik dit net hier vir jou begrip, maar al weet jy niks van gestruktureerde verwysings nie, kan jy steeds maklik hierdie formule skep.
Hier is die stappe om dit te doen:
- In sel C2, voer =SUM(
- Kies sel B1, wat die kop van die kolom met verkoopswaarde is.Jy kan die muis gebruik of die pyltjie sleutels gebruik.Jy sal sien dat Excel outomaties 'n gestruktureerde verwysing na die sel invoer
- Voeg by: (dubbelpunt)
- Kies sel B2.Excel sal outomaties weer gestruktureerde verwysings na selle invoeg
- Maak die hakie toe en druk Enter
Jy sal ook agterkom dat jy nie die formule in die hele kolom hoef te kopieer nie, die Excel-blad doen dit outomaties vir jou.
Nog 'n voordeel van hierdie benadering is dat as jy 'n nuwe rekord by hierdie datastel voeg, die Excel-blad outomaties 'n lopende totaal vir alle nuwe rekords bereken.
Alhoewel ons die kolomopskrifte by die formule ingesluit het, hou in gedagte dat die formule die kopteks ignoreer en slegs die data in die kolom in ag neem
Bereken lopende totale met Power Query
Power Query is 'n wonderlike hulpmiddel wanneer dit kom by die koppeling aan databasisse, die onttrekking van data uit verskeie bronne en die transformasie daarvan voordat dit in Excel geplaas word.
As jy reeds Power Query gebruik, is dit meer doeltreffend om lopende totale by te voeg wanneer die data in Power Query Editor self getransformeer word (eerder as om eers die data in Excel te kry en dan lopende totale by te voeg deur enige van die metodes hierbo te gebruik).
Alhoewel daar geen ingeboude funksionaliteit in Power Query is om lopende totale by te voeg nie (ek wens daar was), kan jy dit steeds met 'n eenvoudige formule doen.
Gestel jy het 'n Excel-tabel wat so lyk, en jy wil lopende totale by hierdie data voeg:
Hier is die stappe om dit te doen:
- Kies enige sel in 'n Excel-tabel
- klik data
- Klik in die Kry & Transformeer-oortjie op die Van Tabel/Reeks-ikoon.Dit sal die tabel in Power Query Editor oopmaak
- [Opsioneel] As jou datumkolom nie reeds gesorteer is nie, klik die filterikoon in die datumkolom, klik dan Sorteer stygend
- Klik op die Voeg Kolom-oortjie in die Power Query Editor
- Klik in die Algemeen-groep op die Indekskolom-aftrekkieslys (moenie die Indekskolom-ikoon klik nie, maar die klein swart skuins pyltjie langsaan om meer opsies te openbaar)
- Klik op die opsie "Van 1".Deur dit te doen, sal 'n nuwe indekskolom bygevoeg word wat by 1 sal begin en nommers wat deur die kolom met 1 verhoog word, invoer
- Klik op die Pasgemaakte Kolomme-ikoon (ook in die Voeg kolomme by-oortjie)
- Voer 'n naam vir die nuwe kolom in in die Pasgemaakte kolomme-dialoog wat oopmaak.In hierdie voorbeeld sal ek die naam "Lopende totaal" gebruik
- Voer die volgende formule in die Pasgemaakte kolomformuleveld in:List.Sum(Lys.Reeks(#"Bygevoegde indeks"[Verkope],0,[Indeks]))
- Maak seker daar is 'n merkblokkie onderaan die dialoog wat sê "Geen sintaksfoute bespeur nie"
- Klik OK.Dit sal 'n nuwe lopende totaalkolom byvoeg
- druppel indeks kolom
- Klik op die lêer-oortjie, klik dan op Sluit en laai
Die bogenoemde stappe sal 'n nuwe werkblad in jou werkboek invoeg met 'n tabel met lopende totale.
Nou, as jy dink dit is te veel stappe in vergelyking met die vorige benadering met eenvoudige formules, is jy reg.
As jy reeds 'n datastel het en al wat jy hoef te doen is om lopende totale by te voeg, is dit die beste om nie Power Query te gebruik nie.
Die gebruik van Power Query maak sin in situasies waar jy data uit 'n databasis moet onttrek of data uit verskeie verskillende werkboeke moet kombineer en ook lopende totale by hulle moet voeg in die proses.
Ook, sodra jy dit outomatiseer met Power Query, die volgende keer dat jou datastel verander, hoef jy dit nie weer te doen nie, jy kan net die navraag verfris en dit sal jou resultate gee gebaseer op die nuwe datastel.
Hoe werk dit?
Laat ek nou vinnig verduidelik wat met hierdie benadering gebeur.
Die eerste ding wat ons in die Power Query Editor doen, is om 'n indekskolom in te voeg wat by 1 begin en inkrementeer soos dit in die sel afgaan.
Ons doen dit omdat ons hierdie kolom moet gebruik wanneer die lopende totaal in 'n ander kolom wat in die volgende stap ingevoeg word, bereken word.
Dan voeg ons 'n pasgemaakte kolom in en gebruik die volgende formule
List.Sum(Lys.Reeks(#"Bygevoegde indeks"[Verkope],0,[Indeks]))
Dit is 'n List.Sum-formule wat vir jou die som sal gee van die reeks wat daarin gespesifiseer word.
Die reeks word gespesifiseer deur die List.Range-funksie te gebruik.
Die List.Range-funksie neem as uitvoer die gespesifiseerde reeks in die verkoopkolom, en die reeks wissel volgens die Indekswaarde.Byvoorbeeld, vir die eerste rekord is die reeks die eerste verkoopwaarde.Hierdie reeks brei uit soos jy in die sel afgaan.
Dus, vir die eerste sel.List.Sum sal vir jou net die som van die eerste verkoopwaarde gee, vir die tweede sel sal dit vir jou die som van die eerste twee verkoopwaardes gee, ensovoorts.
Alhoewel hierdie benadering goed werk, kan dit baie stadig word vir groot datastelle (duisende rye).
Bereken lopende totale gebaseer op kriteria
Tot dusver het ons voorbeelde gesien van die berekening van 'n lopende totaal van alle waardes in 'n kolom.
Maar in sommige gevalle wil jy dalk 'n lopende totaal vir 'n spesifieke rekord bereken.
Byvoorbeeld, hieronder het ek 'n datastel waar ek die lopende totaal van drukkers en skandeerders in twee verskillende kolomme wil tel.
Dit kan gedoen word deur die SUMIF-formule te gebruik, wat 'n lopende totaal bereken terwyl verseker word dat aan die gespesifiseerde voorwaardes voldoen word.
Hier is die formule om dit vir die drukkerkolom te doen:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Net so, om die lopende totaal van die skandeerder te bereken, gebruik die volgende formule:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
In die bogenoemde formule het ek SUMIF gebruik wat vir my 'n som in 'n reeks sal gee wanneer aan spesifieke voorwaardes voldoen word.
Die formule neem drie parameters:
- reeks: Dit is die reeks toestande wat teen die gespesifiseerde toestand gekontroleer sal word
- kriteria: dit is die kriterium wat slegs nagegaan sal word as aan hierdie kriterium voldoen word, dan sal die waarde in die derde parameter, die somreeks bygevoeg word
- [som_reeks]: dit is die reeks van die som van die waardes om by te voeg wanneer die voorwaarde nagekom word
Daarbenewens, inreeks和som_reeksparameter, het ek die tweede deel van die verwysing gesluit sodat as ons in die sel af beweeg, die reeks aanhou uitbrei.Dit stel ons in staat om slegs waardes tot daardie reeks te oorweeg en by te voeg (dus 'n lopende totaal).
In hierdie formule gebruik ek die kopkolom (drukker en skandeerder) as kriteria.Jy kan ook die standaard hardkodeer as jou kolomopskrifte nie presies dieselfde is as die standaardteks nie.
As jy verskeie toestande moet nagaan, kan jy die SUMIFS-formule gebruik.
Lopende totale in 'n spilpunttabel
As jy 'n lopende totaal by jou PivotTable-resultate wil voeg, kan jy dit maklik doen deur die ingeboude funksionaliteit in PivotTables te gebruik.
Gestel jy het 'n spiltabel soos die een hieronder met datums in een kolom en verkoopswaardes in 'n ander.
Hier is die stappe om 'n bykomende kolom by te voeg wat die kumulatiewe verkope volgens datum sal wys:
- Sleep die Verkoop-veld en los dit in die Waarde-area.
- Dit sal nog 'n kolom met die verkoopswaarde byvoeg
- Klik op die Totale verkope 2-opsie in die Waarde-area
- Klik op die opsie Waardeveldinstellings
- In die dialoog Waardeveldinstellings, verander Gepasmaakte Naam na Lopende Totaal
- Klik op die blad "Vertoon waarde as".
- Kies die "Lopende totaal in" opsie in die Vertoon waarde as aftreklys
- In die Basiese Velde-opsies, maak seker dat Datum gekies is
- Klik OK
Die bogenoemde stappe sal die tweede verkoopskolom verander na 'n "lopende totaal"-kolom.
Dit is dus 'n paar van die metodes wat u kan gebruik om lopende totale in Excel te bereken.As jy data in tabelformaat het, kan jy eenvoudige formules gebruik, en as jy Excel-tabelle het, kan jy formules gebruik wat gestruktureerde verwysings gebruik.
Ek het ook gedek hoe om lopende totale te bereken met behulp van Power Query en PivotTables.
Ek hoop jy het hierdie tutoriaal nuttig gevind.
