Generieke formule
=SUM(OFFSET(start,0,0,N,1))
Samenvatting
Als u een reeks maandelijkse gegevens over n aantal maanden wilt optellen, kunt u een formule gebruiken die is gebaseerd op de functies SOM en OFFSET. In het getoonde voorbeeld is de formule in G6:
=SUM(OFFSET(C5,0,0,G5,1))
Uitleg
In het getoonde voorbeeld hebben we maandelijkse gegevens voor de jaren 2017 en 2018. Het doel is om dynamisch waarden op te tellen over een bepaald aantal maanden, hard gecodeerd als 6 in cel G5. Dit wordt gedaan door een referentie die is geconstrueerd met de OFFSET-functie in de somfunctie in te voeren.
Met de OFFSET-functie kunt u een referentie bouwen met behulp van een startpunt, een rij- en kolomoffset en een hoogte en breedte. VERSCHUIVING is handig in formules die dynamisch "laatste n waarden", "eerste n waarden", enzovoort, gemiddeld of optellen. In cel G6 is OFFSET als volgt geconfigureerd:
OFFSET(C5,0,0,G5,1)
Vertaald: bouw vanuit een startpositie van C5 een referentie van 6 rijen x 1 kolom zonder offset. Met het nummer 6 in G5, geeft OFFSET de referentie C5: C10 terug naar SUM:
=OFFSET(C5,0,0,G5,1) // returns C5:C10 =SUM(C5:C10)) // returns 5775
De formule in G7 is op dezelfde manier opgezet. Het enige verschil is het uitgangspunt:
=OFFSET(D5,0,0,G5,1) // returns D5:D10 =SUM(D5:D10)) // returns 6380
Wanneer het nummer in G5 wordt gewijzigd, worden beide formules dynamisch bijgewerkt en wordt een nieuw resultaat geretourneerd.
Jaar tot nu toe variatie
In plaats van een waarde in de formule hard te coderen, kunt u de COUNT-functie gebruiken om bestaande items in een bepaalde kolom te tellen en die telling terug te geven naar OFFSET. Een generieke versie van deze formule zou er als volgt uitzien:
=SUM(OFFSET(start,0,0,COUNT(rng),1))
waarbij rng een verwijzing is naar een bereik dat YTD-waarden bevat.
