U zult waarschijnlijk te maken krijgen met de behoefte aan lopende totalen als u te maken hebt met dagelijkse gegevens.
Stel u voor dat u elke dag de verkoop bijhoudt. Uw gegevens bevatten voor elke datum een rij met een totaal verkoopbedrag, maar misschien wilt u voor elke dag het totaal van de maand weten. Dit is een lopend totaal, het is de som van alle verkopen tot en met de verkoop van de huidige dag.
In dit artikel bespreken we verschillende manieren om een lopend totaal voor uw dagelijkse gegevens te berekenen. We onderzoeken hoe je werkbladformules, draaitabellen, power pivot met DAX en power query’s kunt gebruiken.
We onderzoeken ook wat er met de berekening van het doorlopende totaal gebeurt als je rijen gegevens invoegt of verwijdert en hoe je de resultaten bijwerkt.
Haal het bestand met alle voorbeelden op.
Inhoudsopgave
Lopende totalen met een eenvoudige formule
Het is mogelijk om een basisformule voor lopende totalen te maken met behulp van de operator +.
We moeten echter twee verschillende formules gebruiken om de klus te klaren.
- =C3 is de eerste formule en staat alleen in de eerste rij van het lopende totaal.
- =C4+D3 staat in de tweede rij en kan in de overige rijen worden gekopieerd voor het lopende totaal.
De formule in onze eerste rij kan de cel erboven niet aan het totaal toevoegen, omdat deze een tekstwaarde voor een kolomkop bevat. Dit zou een #VALUE! fout veroorzaken in het lopende totaal omdat de + niet overweg kan met tekst waarden. We vermijden dit met een andere formule in de eerste rij die niet verwijst naar de cel erboven.
Wat gebeurt er met het lopende totaal wanneer we rijen in onze gegevens invoegen of verwijderen?
Een nieuwe rij invoegen zal resulteren in een gat in het lopende totaal. Om dit te verhelpen, moeten we de formule van de eerste cel boven de nieuw ingevoegde rijen helemaal naar beneden kopiëren, tot aan de laatste rij.
Het verwijderen van rijen zal resulteren in #REF! fouten, aangezien het verwijderen van een rij betekent dat een cel wordt verwijderd waarnaar wordt verwezen door de formule eronder. Om dit op te lossen, moeten we de formule van de laatste foutloze cel helemaal naar beneden kopiëren naar de laatste rij.
Totalen berekenen met een SUM-formule
We kunnen de onhandigheid van het gebruik van twee verschillende formules in onze doorlopende totaalkolom vermijden door de SUM-functie te gebruiken in plaats van de +-operator. Wanneer de SUM-functie een tekstcel tegenkomt, wordt deze op dezelfde manier behandeld als wanneer er een 0 in zou staan.
Op deze manier kunnen we de volgende formule uniform gebruiken voor elke rij, inclusief de eerste rij.
=SUM(C3,D2)
Deze formule zal verwijzen naar de kolomkop die tekst bevat voor de eerste rij, maar dit is ok omdat het wordt behandeld als een 0.
Wanneer we rijen invoegen of verwijderen, zullen we nog steeds dezelfde problemen tegenkomen met lege cellen en fouten. We kunnen ze op dezelfde manier oplossen als bij het berekenen van totalen in de eenvoudige formule methode.
Totalen berekenen met een gedeeltelijk vast bereik
Een andere optie met de SUM functie is om alleen naar de kolom Verkoop te verwijzen en een gedeeltelijk vast bereik te gebruiken.
Als we de volgende formule =SUM($C$3:C3) gebruiken, kunnen we deze kopiëren en plakken in het bereik. Er wordt niet verwezen naar kolomtitels en het bereik waarnaar wordt verwezen groeit mee met elke rij.
Helaas heeft ook dit dezelfde problemen (en oplossingen) met het invoegen of verwijderen van rijen.
Totalen uitvoeren met een relatieve reeks op naam
We kunnen de problemen met het invoegen en verwijderen van rijen uit onze gegevens voorkomen als we een relatieve reeks op naam gebruiken. Deze verwijst naar de cel direct erboven, ongeacht het aantal rijen dat we invoegen of verwijderen.
Dit is een truc die inhoudt dat we de Excel-referentiestijl tijdelijk wijzigen van A1 in R1C1. Vervolgens definiëren we een genoemd bereik met behulp van de R1C1 notatie. Daarna schakelt u de referentie stijl weer terug naar A1.
In de R1C1 referentie stijl, worden cellen aangeduid door hoe ver ze verwijderd zijn van de cel die de referentie gebruikt. Zo verwijst =RC naar de cel 2 boven en 3 rechts van de cel die deze formule gebruikt.
We kunnen deze relatieve verwijzing gebruiken om een benoemd bereik te maken dat altijd één cel boven de verwijzende cel met de formule =RC staat.
Om van referentiestijl te veranderen, gaat u naar het tabblad Bestand en kiest u Opties. Ga naar het gedeelte Formule in het menu Opties van Excel en vink het vakje R1C1-referentiestijl aan en druk vervolgens op de knop OK.
Nu kunnen we ons benoemde bereik toevoegen. Ga naar het tabblad Formule van het Excel-lint en kies de opdracht Naam definiëren.
Voeg een naam als “Boven” in als naam van het bereik. Voeg de formule =RC toe aan de invoer Refereert naar en druk op de knop OK.
We kunnen nu Excel terugzetten op de standaard verwijzingsstijl. Ga naar het tabblad Bestand > Opties het gedeelte Formule > vink het vakje R1C1 referentiestijl uit > druk dan op de OK knop.
Nu kunnen we de formule =SUM(,Boven) gebruiken in onze doorlopende totaalkolom.
Het benoemde bereik Boven zal altijd verwijzen naar de cel direct erboven. Wanneer we rijen invoegen of verwijderen, zal het relatieve genoemde bereik zich dienovereenkomstig aanpassen en is er geen actie nodig.
In feite als we onze gegevens in een Excel tabel plaatsen dan zal de formule automatisch naar beneden vullen voor elke nieuwe rij aangezien de formule uniform is voor de gehele kolom. Er is geen actie nodig om formules te kopiëren.
Totalen berekenen met een draaitabel
Pivot tabellen zijn super handig voor het samenvatten van elk type gegevens. Er komt meer bij kijken dan alleen optellen, tellen en gemiddelden vinden. Er zijn veel andere soorten berekeningen ingebouwd, en er is zelfs een lopende totaalberekening!
Eerst moeten we een draaitabel invoegen op basis van de gegevens. Selecteer een cel in de gegevens en ga naar het tabblad Invoegen en kies de opdracht Draaitabel. Ga vervolgens door het venster Maak draaitabel om te kiezen waar u de draaitabel wilt hebben, in een nieuw werkblad of ergens in een bestaand werkblad.
Voeg het veld Datum toe aan het gedeelte Rijen van de draaitabel en voeg vervolgens het veld Verkoop toe aan het gedeelte Waarden van de draaitabel. Voeg nu nog een exemplaar van het veld Verkoop toe in het Rijen-gebied.
We moeten nu twee identieke verkoopvelden hebben, waarvan er een het label Som van verkoop2 krijgt. We kunnen dit label op elk gewenst moment een andere naam geven door er iets overheen te typen, zoals Lopend totaal.
Rechtsklik op een van de waarden in het veld Som van verkoop2 en selecteer Waarde weergeven als en kies vervolgens Lopend totaal in.
We willen het lopende totaal op datum weergeven, dus in het volgende venster moeten we Datum selecteren als het basisveld.
Dat is het, we hebben nu een nieuwe berekening die het doorlopende totaal van onze verkopen in de draaitabel weergeeft.
Wat gebeurt er als we een rij in onze brongegevens toevoegen of verwijderen, wat voor effect heeft dit op het doorlopende totaal? De berekeningen van de draaitabel zijn dynamisch en houden rekening met alle nieuwe gegevens bij de berekening van het doorlopende totaal. We hoeven de draaitabel alleen maar te vernieuwen.
Rechtsklik ergens in de draaitabel en kies Vernieuwen in het menu.
Totalen berekenen met Power Pivot en DAX Measures
De eerste paar stappen hiervoor zijn precies dezelfde als bij een gewone draaitabel.
Selecteer een cel in de gegevens en ga naar het tabblad Invoegen en kies de opdracht Draaitabel.
Wanneer u bij het menu Maak draaitabel komt, schakelt u het selectievakje Deze gegevens aan het gegevensmodel toevoegen in om de gegevens aan het gegevensmodel toe te voegen en in te schakelen voor gebruik met power pivot.
Plaats het veld Datum in het gebied Rijen en het veld Verkoop in het gebied Waarden van de draaitabel.
Met Power Pivot moeten we eventuele extra berekeningen maken met behulp van de DAX-taal. Klik met de rechtermuisknop op de tabelnaam in het venster PivotTable Fields en selecteer vervolgens Add Measure (Maatregel toevoegen) om een nieuwe berekening te maken. Let op, dit is alleen beschikbaar met het gegevensmodel.
=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales Nu kunnen we onze nieuwe doorlopende totaalmaatregel maken.
- In het Measure-venster moeten we een Measure Name toevoegen. In dit geval kunnen we de nieuwe maatregel de naam Lopend totaal geven.
- We moeten ook de bovenstaande formule toevoegen in het vak Formula.
- Het leuke van Power Pivot is de mogelijkheid om een getalnotatie aan een maatregel toe te wijzen. We kunnen de valuta-indeling voor onze maatstaf kiezen. Wanneer we deze maatstaf in een draaitabel gebruiken, wordt de notatie automatisch toegepast.
ruk op de knop OK en de nieuwe maatstaf wordt gemaakt.
Er wordt een nieuw veld weergegeven in het venster PivotTable Fields. Het heeft een klein fx-pictogram aan de linkerkant om aan te geven dat het een maatregel is en geen gewoon veld in de gegevens.
We kunnen dit nieuwe veld net als elk ander veld gebruiken en het naar het gebied Waarden slepen om onze lopende totaalberekening aan de draaitabel toe te voegen.
Wat gebeurt er met het doorlopende totaal als we gegevens uit de brontabel toevoegen of verwijderen? Net als bij een gewone draaitabel hoeven we alleen maar met de rechtermuisknop op de draaitabel te klikken en Vernieuwen te selecteren om de berekening bij te werken.
Lopende totalen met een Power Query
We kunnen ook lopende totalen aan onze gegevens toevoegen met behulp van power query.
Eerst moeten we de tabel in power query importeren. Selecteer de tabel met gegevens en ga naar het tabblad Data en kies de optie From Table/Range. Hiermee wordt de power query-editor geopend.
Volgende stap is het sorteren van onze gegevens op datum. Dit is een optionele stap die we kunnen toevoegen, zodat als we de volgorde van onze brongegevens wijzigen, het lopende totaal nog steeds op datum wordt weergegeven.
Klik op de filterknop in de kolomkop Datum en kies Sorteren oplopend uit de opties.
We moeten een indexkolom toevoegen. Deze wordt later gebruikt bij de berekening van het lopende totaal. Ga naar het tabblad Kolom toevoegen en klik op het kleine pijltje naast de kolom Index om een index in te voegen beginnend bij 1 in de eerste rij.
We moeten een nieuwe kolom aan onze query toevoegen om het doorlopende totaal te berekenen. Ga naar het tabblad Kolom toevoegen en kies de opdracht Aangepaste kolom.
We kunnen de kolom de naam Lopend totaal geven en de volgende formule toevoegen.
De functie List.Range functie maakt een lijst van waarden van de kolom Verkoop beginnend bij de 1e rij (0e item) die een aantal rijen omspant op basis van de waarde in de indexkolom.
De functie List.Sum telt vervolgens deze lijst van waarden op die ons lopende totaal is.
We hebben de indexkolom niet meer nodig, deze heeft zijn doel gediend en we kunnen hem verwijderen. Klik met de rechtermuisknop op de kolomkop en selecteer Verwijderen uit de opties.
We hebben ons lopende totaal en zijn klaar met de query-editor. We kunnen de query sluiten en de resultaten in een nieuw werkblad laden. Ga naar het tabblad Home van de query-editor en druk op de knop Sluiten & Laden.
Wat gebeurt er met het doorlopende totaal als we rijen toevoegen of verwijderen uit onze brongegevens? We moeten de power query uitvoertabel verversen om het lopende totaal bij te werken met de wijzigingen. Klik met de rechtermuisknop op een willekeurige plaats in de tabel en kies Vernieuwen om de tabel bij te werken.
Met de optionele sorteerstap hierboven zal power query, als we data in een verkeerde volgorde aan de brongegevens toevoegen, sorteren op datum en de juiste volgorde op datum voor het doorlopende totaal retourneren.
Conclusies
Er zijn veel verschillende opties voor het berekenen van doorlopende totalen in Excel.
We hebben opties onderzocht zoals formules in het werkblad, draaitabellen, Power Pivot DAX-formules en Power Query. Sommige bieden een meer robuuste oplossing bij het toevoegen of verwijderen van rijen uit de gegevens, andere methoden bieden een eenvoudiger implementatie.
Eenvoudige formules in het werkblad zijn eenvoudig op te zetten, maar zullen niet gemakkelijk omgaan met het invoegen of verwijderen van nieuwe rijen met gegevens. Andere oplossingen zoals draaitabellen, DAX en power query zijn robuuster en kunnen gemakkelijk rijen gegevens invoegen of verwijderen, maar zijn moeilijker op te zetten.
Het is goed om je bewust te zijn van de voors en tegens van elke methode en de meest geschikte te kiezen. Als u geen nieuwe gegevens invoegt of verwijdert, zijn werkbladformules misschien de beste keuze.