lopend totaal (ook bekend alscumulatieve som) wordt in veel gevallen zeer vaak gebruikt.Dit is een statistiek die u vertelt wat de som van de waarden tot nu toe is.
Als u bijvoorbeeld maandelijkse verkoopgegevens heeft, geeft een lopend totaal aan hoeveel verkopen er zijn gemaakt vanaf de eerste dag van de maand tot een specifieke datum.
Er zijn andere situaties waarin vaak totalen worden gebruikt, zoals het berekenen van kassaldi in bankafschriften/grootboeken, het berekenen van calorieën in maaltijdplannen, enz.
In Microsoft Excel zijn er verschillende manieren om lopende totalen te berekenen.
De methode die u kiest, hangt ook af van de structuur van de gegevens.
Als u bijvoorbeeld eenvoudige tabelgegevens heeft, kunt u een eenvoudige SOM-formule gebruiken, maar als u een Excel-tabel heeft, is het beter om gestructureerde verwijzingen te gebruiken.U kunt hiervoor ook Power Query gebruiken.
In deze tutorial zal ik ze allemaal behandelenBereken een lopend totaal in Excelverschillende methoden.
Dus laten we beginnen!
Inhoud
Lopende totalen berekenen met behulp van tabelgegevens
Als u tabelgegevens hebt (dat wil zeggen tabellen in Excel die niet zijn geconverteerd naar Excel-tabellen), kunt u enkele eenvoudige formules gebruiken om lopende totalen te berekenen.
Gebruik de opteloperator
Stel dat u verkoopgegevens op datum heeft en u wilt:在 in kolom CBerekeningLopend totaal.
Hier zijn de stappen om dit te doen.
Stap 1 – Voer in cel C2 de eerste cel waarvoor u het totaal wilt uitvoeren, in
= B2
Dit krijgt gewoon dezelfde verkoopwaarde in cel B2.
Stap 2– Voer in cel C3 de volgende 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 cel C3 kopiëren en plakken naar alle resterende cellen (hierdoor wordt de verwijzing automatisch aangepast en krijgt u het juiste resultaat).
Dit geeft u het onderstaande resultaat.
Dit is een zeer eenvoudige methode die in de meeste gevallen goed werkt.
De logica is eenvoudig: elke cel neemt de waarde erop (wat de cumulatieve som is tot de vorige dag) en voegt de waarde toe in de aangrenzende cel (wat de verkoopwaarde van de huidige dag is).
Er is slechts één nadeel: als u een bestaande rij in deze dataset verwijdert, retourneren alle onderstaande cellen een verwijzingsfout (#REF!)
Als uw dataset deze mogelijkheid heeft, gebruik dan de volgende methode met behulp van de SUM-formule
SUM gebruiken met gedeeltelijk vergrendelde celverwijzingen
Stel dat u verkoopgegevens op datum hebt en u wilt een lopend totaal berekenen in kolom C.
Hieronder vindt u de SOM-formule waarmee u een lopend totaal krijgt.
= SOM ($ B $ 2: B2)
Laat me uitleggen hoe deze formule werkt.
In de SUM-formule hierboven heb ik de verwijzing gebruikt om toe te voegen als $ B $ 2: B2
- $B$2 - Dit is een absolute referentie, wat betekent dat deze referentie niet verandert wanneer ik dezelfde formule in de cel hieronder kopieer.Dus wanneer u de formule in de onderstaande cel kopieert, verandert de formule in SUM($B$2:B3)
- B2 - Dit is het tweede deel van de referentie, het is een relatieve referentie, wat betekent dat wanneer ik de formule naar beneden of naar rechts kopieer, dit wordt aangepast.Dus bij het kopiëren van de formule in de onderstaande cel wordt de waarde B3
Het leuke van deze aanpak is dat als je rijen in de dataset verwijdert, deze formule zich aanpast en je nog steeds het juiste lopende totaal geeft.
Bereken lopend totaal in Excel-blad
Wanneer u in Excel met tabelgegevens werkt, kunt u deze het beste converteren naar een Excel-tabel.Het maakt het gemakkelijker om gegevens te beheren en maakt het ook gemakkelijk om tools zoals Power Query en Power Pivot te gebruiken.
Het gebruik van een Excel-tabel biedt verschillende voordelen, zoals gestructureerde verwijzingen (waardoor het heel gemakkelijk is om naar gegevens in de tabel te verwijzen en deze in formules te gebruiken), en automatische aanpassing van verwijzingen wanneer u gegevens toevoegt aan of verwijdert uit de tabel.
Hoewel je nog steeds de bovenstaande formule kunt gebruiken die ik je in een Excel-blad heb laten zien, wil ik je enkele betere manieren laten zien om het te doen.
Stel dat u een Excel-tabel hebt zoals hieronder, en u wilt een lopend totaal berekenen in kolom C.
Hier is de formule om dit te doen:
=SOM(Verkoopgegevens[[#Headers],[Verkoop]]:[@Verkoop])
Bovenstaande formule lijkt misschien wat lang, maar je hoeft hem niet zelf te schrijven.Wat u in een sommatieformule ziet, wordt een gestructureerde verwijzing genoemd en het is een efficiënte manier voor Excel om naar specifieke gegevenspunten in een Excel-tabel te verwijzen.
SalesData[[#Headers],[Sale]] verwijst bijvoorbeeld naar de Sales-header in de SalesData-tabel (SalesData is de naam van de Excel-tabel die ik heb opgegeven toen ik de tabel maakte)
Terwijl [@Sale] verwijst naar de waarde in een cel in dezelfde rij van de kolom Sale.
Ik leg dit hier alleen uit voor uw begrip, maar zelfs als u niets weet over gestructureerde verwijzingen, kunt u deze formule toch gemakkelijk maken.
Hier zijn de stappen om dit te doen:
- Voer in cel C2 =SUM(
- Selecteer cel B1, de kop van de kolom met verkoopwaarde.U kunt de muis gebruiken of de pijltjestoetsen gebruiken.U zult merken dat Excel automatisch een gestructureerde verwijzing naar de cel invoert
- Toevoegen: (dubbele punt)
- Selecteer cel B2.Excel zal automatisch weer gestructureerde verwijzingen naar cellen invoegen
- Sluit de haak en druk op Enter
Je zult ook merken dat je de formule niet in de hele kolom hoeft te kopiëren, het Excel-blad doet dit automatisch voor je.
Een ander voordeel van deze aanpak is dat als u een nieuw record aan deze dataset toevoegt, het Excel-blad automatisch een lopend totaal berekent voor alle nieuwe records.
Hoewel we de kolomkoppen in de formule hebben opgenomen, moet u er rekening mee houden dat de formule de koptekst negeert en alleen de gegevens in de kolom in overweging neemt
Lopende totalen berekenen met Power Query
Power Query is een geweldige tool als het gaat om het verbinden met databases, het extraheren van gegevens uit meerdere bronnen en het transformeren ervan voordat het in Excel wordt omgezet.
Als u Power Query al gebruikt, is het efficiënter om lopende totalen toe te voegen bij het transformeren van de gegevens in Power Query Editor zelf (in plaats van eerst de gegevens in Excel op te halen en vervolgens lopende totalen toe te voegen met een van de bovenstaande methoden).
Hoewel er geen ingebouwde functionaliteit in Power Query is om lopende totalen toe te voegen (ik wou dat die er waren), kun je het toch doen met een eenvoudige formule.
Stel dat u een Excel-tabel hebt die er als volgt uitziet, en u wilt lopende totalen aan deze gegevens toevoegen:
Hier zijn de stappen om dit te doen:
- Selecteer een cel in een Excel-tabel
- klik op gegevens
- Klik op het tabblad Ophalen en transformeren op het pictogram van tabel/bereik.Dit opent de tabel in Power Query Editor
- [Optioneel] Als uw datumkolom nog niet is gesorteerd, klikt u op het filterpictogram in de datumkolom en vervolgens op Oplopend sorteren
- Klik op het tabblad Kolom toevoegen in de Power Query-editor
- Klik in de groep Algemeen op het vervolgkeuzemenu 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 "Van 1".Als u dit doet, wordt een nieuwe indexkolom toegevoegd die begint bij 1 en getallen invoert die met 1 worden verhoogd in de kolom
- Klik op het pictogram Aangepaste kolommen (ook op het tabblad Kolommen toevoegen)
- Voer in het dialoogvenster 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 volgende formule in:List.Sum(List.Range(#"Added Index"[Sale],0,[Index]))
- Zorg ervoor dat er een selectievakje onder aan het dialoogvenster staat met de tekst "Geen syntaxisfouten gedetecteerd"
- Klik OK.Dit zal een nieuwe kolom voor het lopende totaal toevoegen
- laat indexkolom vallen
- Klik op het tabblad Bestand en klik vervolgens op Sluiten en laden
Met de bovenstaande stappen wordt een nieuw werkblad in uw werkmap ingevoegd met een tabel met lopende totalen.
Als u denkt dat dit te veel stappen zijn in vergelijking met de vorige benadering met eenvoudige formules, heeft u gelijk.
Als u al een dataset heeft en u alleen lopende totalen hoeft toe te voegen, kunt u Power Query het beste niet gebruiken.
Het gebruik van Power Query is zinvol in situaties waarin u gegevens uit een database moet extraheren of gegevens uit verschillende werkmappen moet combineren en daarbij ook lopende totalen moet toevoegen.
En als u dit eenmaal hebt geautomatiseerd met Power Query, hoeft u de volgende keer dat uw gegevensset verandert, dit niet opnieuw te doen. U kunt de query gewoon vernieuwen en u krijgt resultaten op basis van de nieuwe gegevensset.
Hoe werkt dit?
Laat me nu snel uitleggen wat er gebeurt met deze benadering.
Het eerste dat we in de Power Query-editor doen, is een indexkolom invoegen die begint bij 1 en toeneemt als deze door de cel gaat.
We doen dit omdat we deze kolom moeten gebruiken bij het berekenen van het lopende totaal in een andere kolom die in de volgende stap wordt ingevoegd.
Vervolgens voegen we een aangepaste kolom in en gebruiken we de volgende 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 gespecificeerd.
Het bereik wordt gespecificeerd met behulp van de List.Range-functie.
De functie List.Range neemt als uitvoer het gespecificeerde bereik in de verkoopkolom en het bereik varieert volgens de indexwaarde.Voor de eerste record is het bereik bijvoorbeeld de eerste verkoopwaarde.Dit bereik wordt groter naarmate je verder door de cel gaat.
Dus voor de eerste cel.List.Sum geeft u alleen de som van de eerste verkoopwaarde, voor de tweede cel geeft het u de som van de eerste twee verkoopwaarden, enzovoort.
Hoewel deze aanpak goed werkt, kan deze erg traag worden voor grote datasets (duizenden rijen).
Bereken lopende totalen op basis van criteria
Tot nu toe hebben we voorbeelden gezien van het berekenen van een lopend totaal van alle waarden in een kolom.
Maar in sommige gevallen wilt u misschien een lopend totaal berekenen voor een specifiek record.
Hieronder heb ik bijvoorbeeld een dataset waarin ik het lopende totaal van printers en scanners in twee verschillende kolommen wil tellen.
Dit kan worden gedaan met behulp van de SUMIF-formule, die een lopend totaal berekent en ervoor zorgt dat aan bepaalde voorwaarden wordt voldaan.
Hier is de formule om dit voor de printerkolom te doen:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Evenzo, om het lopende totaal van de scanner te berekenen, gebruikt u de volgende formule:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
In de bovenstaande formule heb ik SUMIF gebruikt, wat me een som in een bereik geeft wanneer aan bepaalde voorwaarden wordt voldaan.
De formule heeft drie parameters:
- reeks: Dit is het bereik van voorwaarden die zullen worden vergeleken met de gespecificeerde voorwaarde
- criteria: dit is het criterium dat alleen wordt gecontroleerd als aan dit criterium wordt voldaan, dan wordt de waarde in de derde parameter, het sombereik toegevoegd
- [som_bereik]: dit is het bereik van de som van de waarden die moeten worden toegevoegd wanneer aan de voorwaarde is voldaan
Bovendien, inreeks和totaalbereikparameter, heb ik het tweede deel van de verwijzing vergrendeld, zodat het bereik steeds groter wordt naarmate we verder door de cel gaan.Hierdoor kunnen we alleen waarden beschouwen en optellen tot dat bereik (vandaar een lopend totaal).
In deze formule gebruik ik de kopkolommen (Printers en Scanners) als criteria.U kunt de standaard ook hardcoderen als uw kolomkoppen niet exact hetzelfde zijn als de standaardtekst.
Als u meerdere voorwaarden moet controleren, kunt u de SUMIFS-formule gebruiken.
Lopende totalen in een draaitabel
Als u een lopend totaal aan uw draaitabelresultaten wilt toevoegen, kunt u dit eenvoudig doen met de ingebouwde functionaliteit in draaitabellen.
Stel dat u een draaitabel heeft zoals hieronder met datums in de ene kolom en verkoopwaarden in een andere.
Dit zijn de stappen om een extra kolom toe te voegen waarin de cumulatieve verkopen op datum worden weergegeven:
- Sleep het veld Verkoop en zet het neer in het gebied Waarde.
- Dit voegt een nieuwe kolom toe met de verkoopwaarde
- Klik op de optie Totale verkoop 2 in het gebied Waarde
- Klik op de optie Waardeveldinstellingen
- Wijzig in het dialoogvenster Waardeveldinstellingen Aangepaste naam in Lopend totaal
- Klik op het tabblad "Weergavewaarde als"
- Selecteer in het vervolgkeuzemenu Waarde weergeven als de optie "Running Total in"
- Zorg ervoor dat Datum is geselecteerd in de opties voor Basisvelden
- Klik OK
Met de bovenstaande stappen wordt de tweede verkoopkolom gewijzigd in een kolom 'lopend totaal'.
Dit zijn dus enkele van de methoden die u kunt gebruiken om lopende totalen in Excel te berekenen.Als u gegevens in tabelvorm hebt, kunt u eenvoudige formules gebruiken en als u Excel-tabellen hebt, kunt u formules gebruiken die gestructureerde verwijzingen gebruiken.
Ik heb ook besproken hoe u lopende totalen kunt berekenen met Power Query en draaitabellen.
Ik hoop dat je deze tutorial nuttig vond.
