Lopend totaal (ook wel cumulatieve som) wordt in veel situaties vrij vaak gebruikt. Het is een statistiek die u vertelt wat de som van de waarden tot nu toe is.
Als u bijvoorbeeld de maandelijkse verkoopgegevens heeft, geeft een lopend totaal aan hoeveel verkopen er zijn gedaan tot een bepaalde dag vanaf de eerste dag van de maand.
Er zijn ook enkele andere situaties waarin vaak lopend totaal wordt gebruikt, zoals het berekenen van uw kassaldo in uw bankafschriften/grootboek, het tellen van calorieën in uw maaltijdplan, enz.
In Microsoft Excel zijn er meerdere verschillende manieren om lopende totalen te berekenen.
De methode die u kiest, hangt ook af van hoe uw gegevens zijn gestructureerd.
Als u bijvoorbeeld eenvoudige tabelgegevens heeft, kunt u een eenvoudige SOM-formule gebruiken, maar als u een Excel-tabel heeft, kunt u het beste gestructureerde verwijzingen gebruiken. U kunt hiervoor ook Power Query gebruiken.
In deze zelfstudie ga ik al deze verschillende methoden behandelen om lopende totalen berekenen in Excel.
Dus laten we beginnen!
Lopend totaal berekenen met gegevens in tabelvorm
Als u tabelgegevens hebt (d.w.z. een tabel in Excel die niet is geconverteerd naar een Excel-tabel), kunt u enkele eenvoudige formules gebruiken om de lopende totalen te berekenen.
De toevoegingsoperator gebruiken
Stel dat u verkoopgegevens per datum hebt en u wilt het lopende totaal in kolom C berekenen.
Hieronder staan de stappen om dit te doen.
Stap 1 - In cel C2, de eerste cel waar u het lopende totaal wilt hebben, typt u
=B2
Dit krijgt gewoon dezelfde verkoopwaarden in cel B2.
Stap 2 - Voer in cel C3 de onderstaande formule in:
=C2+B3
Stap 3 - Pas de formule toe op de hele kolom. U kunt de vulgreep gebruiken om het te selecteren en te slepen, of gewoon en kopieer en plak de cel C3 naar alle resterende cellen (waardoor de verwijzing automatisch wordt aangepast en het juiste resultaat wordt gegeven).
Dit geeft u het resultaat zoals hieronder weergegeven.
Het is een heel eenvoudige methode en werkt in de meeste gevallen goed.
De logica is eenvoudig: elke cel neemt de waarde erboven op (wat de cumulatieve som is tot de datum ervoor) en voegt de waarde toe in de cel ernaast (wat de verkoopwaarde voor die dag is).
Er is slechts één nadeel: als u een van de bestaande rijen in deze gegevensset verwijdert, zouden alle onderstaande cellen een verwijzingsfout retourneren (#REF!)
Als dat een mogelijkheid is met uw dataset, gebruikt u de volgende methode die de SUM-formule gebruikt
SUM gebruiken met gedeeltelijk vergrendelde celreferentie
Stel dat u verkoopgegevens per datum hebt en u wilt het lopende totaal in kolom C berekenen.
Hieronder vindt u de SOM-formule die u het lopende totaal geeft.
=SOM($B$2:B2)
Laat me uitleggen hoe deze formule werkt.
In de bovenstaande SUM-formule heb ik de verwijzing gebruikt om toe te voegen als $ B $ 2: B2
- $B$2 - dit is een absolute referentie, wat betekent dat wanneer ik dezelfde formule kopieer in de onderstaande cellen, deze referentie niet zal veranderen. Dus bij het kopiëren van de formule in de onderstaande cel, zou de formule veranderen in SUM($B$2:B3)
- B2 - dit is het tweede deel van de verwijzing dat een relatieve verwijzing is, wat betekent dat dit zou worden aangepast als ik de formule naar beneden of naar rechts kopieer. Dus bij het kopiëren van de formule in de onderstaande cel wordt deze waarde B3
Lees ook: Absolute, relatieve en gemengde celverwijzingen in Excel
Het mooie van deze methode is dat als u een van de rijen in de gegevensset verwijdert, deze formule zich zou aanpassen en u toch de juiste lopende totalen zou geven.
Lopend totaal berekenen in Excel-tabel
Bij het werken met tabelgegevens in Excel is het een goed idee om deze om te zetten in een Excel-tabel. Het maakt het een stuk eenvoudiger om de gegevens te beheren en maakt het ook gemakkelijk om tools zoals Power Query en Power Pivot te gebruiken.
Het werken met Excel-tabellen biedt voordelen zoals gestructureerde verwijzingen (waardoor het heel gemakkelijk is om naar de gegevens in de tabel te verwijzen en deze in formules te gebruiken), en automatische aanpassing van verwijzingen voor het geval u gegevens aan de tabel toevoegt of verwijdert.
Hoewel u nog steeds de bovenstaande formule kunt gebruiken die ik u in een Excel-tabel heb laten zien, wil ik u enkele betere methoden laten zien om dit te doen.
Stel dat u een Excel-tabel heeft zoals hieronder weergegeven en dat u het lopende totaal in kolom C wilt berekenen.
Hieronder is de formule die dit zal doen:
=SOM(Verkoopgegevens[[#Headers],[Verkoop]]:[@Verkoop])
Bovenstaande formule lijkt misschien wat lang, maar je hoeft hem niet zelf te schrijven. wat u in de somformule ziet, worden gestructureerde verwijzingen genoemd, wat de efficiënte manier van Excel is om naar specifieke gegevenspunten in een Excel-tabel te verwijzen.
SalesData[[#Headers],[Sale]] verwijst bijvoorbeeld naar de Sale-header in de SalesData-tabel (SalesData is de naam van de Excel-tabel die ik heb opgegeven toen ik de tabel maakte)
En [@Sale] verwijst naar de waarde in de cel in dezelfde rij in de kolom Sale.
Ik heb dit hier zojuist uitgelegd voor uw begrip, maar zelfs als u niets weet over gestructureerde verwijzingen, kunt u deze formule nog steeds gemakkelijk maken.
Hieronder staan de stappen om dit te doen:
- Voer in cel C2 =SUM(
- Selecteer cel B1, de kop van de kolom met de verkoopwaarde. U kunt de muis gebruiken of de pijltjestoetsen gebruiken. U zult merken dat Excel automatisch de gestructureerde verwijzing voor die cel invoert
- Voeg een : (dubbele punt symbool) toe
- Selecteer cel B2. Excel zou opnieuw automatisch de gestructureerde verwijzing voor de cel invoegen
- Sluit het haakje en druk op enter
Je zult ook merken dat je de formule niet in de hele kolom hoeft te kopiëren, een Excel-tabel doet het automatisch voor je.
Een ander voordeel van deze methode is dat als u een nieuw record toevoegt aan deze dataset, de Excel-tabel automatisch het lopende totaal voor alle nieuwe records berekent.
Hoewel we de kop van de kolom in onze formule hebben opgenomen, moet u er rekening mee houden dat de formule de koptekst zou negeren en alleen de gegevens in de kolom zou beschouwen
Lopend totaal berekenen met Power Query
Power Query is een geweldige tool als het gaat om het verbinden met databases, het extraheren van de gegevens uit meerdere bronnen en het transformeren ervan voordat het in Excel wordt omgezet.
Als u al met Power Query werkt, is het efficiënter om lopende totalen toe te voegen terwijl u de gegevens in de Power Query-editor zelf transformeert (in plaats van eerst de gegevens in Excel op te halen en vervolgens de lopende totalen toe te voegen met een van de bovenstaande hierboven beschreven methoden).
Hoewel er geen ingebouwde functie in Power Query is om lopende totalen toe te voegen (wat ik wou dat er was), kun je dat nog steeds doen met een eenvoudige formule.
Stel dat u een Excel-tabel heeft zoals hieronder weergegeven en u wilt de lopende totalen bij deze gegevens optellen:
Hieronder staan de stappen om dit te doen:
- Selecteer een cel in de Excel-tabel
- Klik op Gegevens
- Klik op het tabblad Ophalen en transformeren op het pictogram van tabel/bereik. Dit opent de tabel in de Power Query-editor
- [Optioneel] Als uw kolom Datum nog niet is gesorteerd, klikt u op het filterpictogram in de kolom Datum en vervolgens op Oplopend sorteren
- Klik op het tabblad Kolom toevoegen in de Power Query-editor
- Klik in de groep Algemeen op de vervolgkeuzelijst Indexkolom (klik niet op het pictogram Indexkolom, maar op de kleine zwarte schuine pijl ernaast om meer opties weer te geven)
- Klik op de optie ‘Vanaf 1’. Als u dit doet, wordt er een nieuwe indexkolom toegevoegd die met één begint en getallen in de hele kolom met 1 ophogen
- Klik op het pictogram 'Aangepaste kolom' (die ook op het tabblad Kolom toevoegen staat)
- Voer in het dialoogvenster voor aangepaste kolommen dat wordt geopend een naam in voor de nieuwe kolom. in dit voorbeeld gebruik ik de naam 'Running Total'
- Voer in het veld Aangepaste kolomformule de onderstaande formule in: List.Sum(List.Range(#"Toegevoegde Index"[Sale],0,[Index]))
- Zorg ervoor dat er een selectievakje onder aan het dialoogvenster staat met de tekst: 'Er zijn geen syntaxisfouten gedetecteerd'
- Klik OK. Dit zou een nieuwe kolom voor het lopende totaal toevoegen
- De indexkolom verwijderen
- Klik op het tabblad Bestand en klik vervolgens op 'Sluiten en laden'
De bovenstaande stappen zouden een nieuw blad in uw werkmap invoegen met een tabel met de lopende totalen.
Als u nu denkt dat dit gewoon te veel stappen zijn in vergelijking met de vorige methoden voor het gebruik van eenvoudige formules, heeft u gelijk.
Als u al een dataset heeft en u alleen lopende totalen hoeft toe te voegen, kunt u Power Query beter niet gebruiken.
Het gebruik van Power Query is logisch wanneer u gegevens uit een database moet extraheren of gegevens uit meerdere verschillende werkmappen moet combineren en daarbij ook lopende totalen moet toevoegen.
Als u deze automatisering eenmaal heeft uitgevoerd met Power Query, hoeft u de volgende keer dat uw gegevensset wordt gewijzigd dit niet opnieuw te doen, kunt u de query eenvoudig vernieuwen en krijgt u het resultaat op basis van de nieuwe gegevensset.
Hoe werkt dit?
Laat me nu snel uitleggen wat er in deze methode gebeurt.
Het eerste dat we in de Power Query-editor doen, is een indexkolom invoegen, beginnend bij één en met één ophogend terwijl deze door de cellen gaat.
We doen dit omdat we deze kolom moeten gebruiken terwijl we het lopende totaal berekenen in een andere kolom die we in de volgende stap invoegen.
Vervolgens voegen we een aangepaste kolom in en gebruiken we de onderstaande formule:
List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
Dit is een List.Sum-formule die u de som geeft van het bereik dat erin is opgegeven.
En dat bereik wordt gespecificeerd met behulp van de List.Range-functie.
De functie List.Range geeft het opgegeven bereik in de verkoopkolom als uitvoer en dit bereik verandert op basis van de indexwaarde. Voor het eerste record zou het bereik bijvoorbeeld gewoon de eerste verkoopwaarde zijn. En als je door de cellen gaat, wordt dit bereik groter.
Dus voor de eerste cel. List.Sum geeft u alleen de som van de eerste verkoopwaarde, en voor de tweede cel geeft het u de som voor de eerste twee verkoopwaarden enzovoort.
Hoewel deze methode goed werkt, wordt hij erg traag met grote datasets - duizenden rijen. Als je te maken hebt met een grote dataset en er lopende totalen aan wilt toevoegen, bekijk dan deze tutorial die andere methoden laat zien die sneller zijn.Lopend totaal berekenen op basis van criteria
Tot nu toe hebben we voorbeelden gezien waarbij we het lopende totaal voor alle waarden in een kolom hebben berekend.
Maar er kunnen gevallen zijn waarin u het lopende totaal voor specifieke records wilt berekenen.
Hieronder heb ik bijvoorbeeld een dataset en wil ik het lopende totaal voor Printers en Scanners afzonderlijk in twee verschillende kolommen berekenen.
Dit kan worden gedaan met behulp van een SUMIF-formule die het lopende totaal berekent en ervoor zorgt dat aan de opgegeven voorwaarde wordt voldaan.
Hieronder vindt u de formule die dit voor de printerkolommen doet:
=SOM.ALS($C$2:C2,$D$1,$B$2:B2)
Op dezelfde manier gebruikt u de onderstaande formule om het lopende totaal voor scanners te berekenen:
=SOM.ALS($C$2:C2,$E$1,$B$2:B2)
In de bovenstaande formules heb ik SUMIF gebruikt, wat me de som in een bereik zou geven wanneer aan de opgegeven criteria wordt voldaan.
De formule heeft drie argumenten:
- bereik: dit is het criteriabereik dat zou worden gecontroleerd aan de hand van de opgegeven criteria
- criteria: dit zijn de criteria die alleen worden ingecheckt als aan dit criterium wordt voldaan, dan worden de waarden in het derde argument, het sombereik, toegevoegd
- [sum_range]: dit is het sombereik waarvan de waarden zouden worden opgeteld als aan de criteria wordt voldaan
Ook in de bereik en sum_range argument, ik heb het tweede deel van de verwijzing vergrendeld, zodat het bereik zich zou blijven uitbreiden als we door de cellen gaan. Hierdoor kunnen we alleen waarden beschouwen en toevoegen tot dat bereik (vandaar lopende totalen).
In deze formule heb ik de kopkolom (Printer en Scanner) als criteria gebruikt. u kunt de criteria ook hardcoderen als uw kolomkoppen niet precies hetzelfde zijn als de criteriatekst.
Als u meerdere voorwaarden heeft die u moet controleren, kunt u de SUMIFS-formule gebruiken.
Lopend totaal in draaitabellen
Als u lopende totalen wilt toevoegen aan een draaitabelresultaat, kunt u dat eenvoudig doen met behulp van een ingebouwde functionaliteit in draaitabellen.
Stel dat u een draaitabel heeft zoals hieronder weergegeven, waarbij ik de datum in de ene kolom en de verkoopwaarde in de andere kolom heb.
Hieronder vindt u de stappen om een extra kolom toe te voegen waarin het lopende totaal van de verkopen op datum wordt weergegeven:
- Sleep het veld Verkoop en plaats het in het gebied Waarde.
- Dit zal een nieuwe kolom toevoegen met de Verkoopwaarden
- Klik op de optie Som van verkoop2 in het gebied Waarde
- Klik op de optie 'Value Field Settings'
- Wijzig in het dialoogvenster Waardeveldinstellingen de aangepaste naam in 'Running Totals'
- Klik op het tabblad 'Waarde weergeven als'
- Selecteer in de vervolgkeuzelijst Waarde weergeven als de optie 'Running Total in'
- Zorg ervoor dat Datum is geselecteerd in de opties van het basisveld
- Klik OK
De bovenstaande stappen veranderen de tweede verkoopkolom in de kolom Lopend totaal.
Dit zijn dus enkele van de manieren die u kunt gebruiken om het lopende totaal in Excel te berekenen. Als u gegevens in tabelvorm hebt, kunt u eenvoudige formules gebruiken en als u een Excel-tabel hebt, kunt u formules gebruiken die gebruik maken van gestructureerde verwijzingen.
Ik heb ook besproken hoe u het lopende totaal kunt berekenen met Power Query en in draaitabellen.
Ik hoop dat je deze tutorial nuttig vond.
