Soms wil je in Excel niet steeds handmatig de celverwijzingen aanpassen als je nieuwe gegevens toevoegt. Denk aan een overzicht met dagelijkse verkopen of metingen. In zulke gevallen kun je een dynamisch bereik gebruiken om automatisch de som van de laatste 5 waarden te berekenen. In dit artikel leer je stap voor stap hoe je dit doet met de functies SOM(), VERSCHUIVING() en AANTALARG().
Je hoeft dus niet langer telkens de formule aan te passen: Excel doet het werk voor je. Handig bij financiële dashboards, voortschrijdende gemiddelden of KPI-rapportages.
1. Basisprincipe: SOM()
De SOM() functie berekent de totale waarde van een reeks cellen. De syntaxis is eenvoudig:
=SOM(getal1; [getal2]; …)Als je bijvoorbeeld de waarden in B2:B6 wilt optellen, gebruik je:
=SOM(B2:B6)Maar wat als je gegevens doorgaan tot B100, en je altijd de laatste 5 cellen wilt optellen, ongeacht hoeveel er al ingevuld zijn? Dan heb je een dynamische aanpak nodig.
2. Het dynamisch bereik maken met VERSCHUIVING()
De VERSCHUIVING() functie is ideaal voor dynamische bereiken. Deze functie kan een cel of een bereik verschuiven op basis van een startpunt, aantal rijen en kolommen.
De syntaxis is:
=VERSCHUIVING(referentie; rijen; kolommen; [hoogte]; [breedte])Om de laatste 5 cellen in kolom A te selecteren, kun je de volgende formule gebruiken:
=VERSCHUIVING(B1;AANTALARG(B:B)-5;0;5;1)Laten we dat even ontleden:
- B1 is de startcel.
- AANTALARG(B:B) telt hoeveel cellen in kolom B gegevens bevatten.
- -5 zorgt ervoor dat het bereik start bij de vijfde cel vanaf het einde.
- De hoogte is 5, dus het bereik bevat precies vijf rijen.
3. De som van de laatste 5 waarden berekenen
Combineer nu de bovenstaande formule met SOM():
=SOM(VERSCHUIVING(B1;AANTALARG(B:B)-5;0;5;1))Deze formule telt automatisch de laatste vijf ingevulde waarden in kolom A op. Voeg je een nieuwe waarde toe, dan verschuift het bereik vanzelf mee.
Praktijkvoorbeeld:
Stel, je hebt de dagelijkse omzet in kolom B:
De formule geeft dan als resultaat de som van de laatste 5 waarden: 150 + 160 + 200 + 180 + 210 = 900.
4. Dynamisch bereik met SOM.ALS of SOMMEN.ALS
Wil je de som van de laatste 5 waarden berekenen, maar alleen voor waarden die voldoen aan een voorwaarde? Dan kun je SOMMEN.ALS() gebruiken.
Bijvoorbeeld: alleen waarden groter dan 100 in de laatste 5 rijen:
=SOMMEN.ALS(VERSCHUIVING(B1;AANTALARG(B:B)-5;0;5;1);">100")Zo combineer je dynamiek met logica, ideaal voor rapportages met filters of KPI’s.
5. Belangrijke aandachtspunten
- Als er minder dan 5 waarden zijn ingevuld, kan de formule een foutmelding geven.
Voeg dan een ALS.FOUT() controle toe:=ALS.FOUT(SOM(VERSCHUIVING(A1;AANTALARG(A:A)-5;0;5;1));0)Zo voorkom je foutcodes zoals#VERW!of#NAAM?. - In Excel Online of Mobile kan VERSCHUIVING() trager zijn bij grote datasets, omdat de functie vluchtig is (Excel berekent deze telkens opnieuw bij elke wijziging).
- In sommige taalversies (zoals Engels) heet de functie OFFSET(), en AANTALARG() is daar COUNTA(). Let daarop bij het delen van bestanden met anderstalige collega’s.
6. Alternatief met INDEX()
Als je liever een formule zonder vluchtige functies gebruikt, kun je INDEX() toepassen.
Bijvoorbeeld:
=SOM(INDEX(B:B;AANTALARG(B:B)-4):INDEX(B:B;AANTALARG(B:B)))Deze methode is efficiënter bij grote reeksen en werkt in alle Excel-versies, inclusief Online.
Het resultaat is hetzelfde: Excel telt de laatste 5 waarden op, maar zonder prestatieverlies.
7. Handige uitbreidingen
Wil je de formule nog flexibeler maken?
Gebruik dan een aparte cel (bijvoorbeeld C1) om het aantal rijen te bepalen:
=SOM(VERSCHUIVING(A1;AANTALARG(A:A)-C1;0;C1;1))Als je in C1 de waarde 5 invult, krijg je de som van de laatste 5 rijen.
Verander je C1 naar 10, dan berekent Excel automatisch de som van de laatste 10 rijen.
Zo bouw je eenvoudig een dynamisch dashboard zonder dat je de formules telkens hoeft te bewerken.
Conclusie
Met SOM(), VERSCHUIVING() en AANTALARG() kun je in Excel eenvoudig de som van de laatste 5 waarden berekenen — volledig automatisch. Voeg je nieuwe gegevens toe, dan past Excel het bereik dynamisch aan.
Gebruik INDEX() als je de prestaties wilt verbeteren of met grote datasets werkt. Combineer deze aanpak eventueel met SOMMEN.ALS() om ook voorwaarden toe te passen.
Of je nu je dagelijkse verkoop, kosten of meetwaarden bijhoudt: met deze formule blijft je berekening altijd actueel.