Blog Excel: Dynamisch sommeren van variabele bereiken

TIP: Wekelijks leidt Excel-expert Tony De Jonker ons door de diepe en soms ondoordringbare krochten van Excel. Zijn tips maken je werkzaamheden een stuk makkelijker. Deze week: Omzet berekenen van een bepaalde locatie voor een variabel tijdbereik.

Bij het maken van rekenmodellen in Excel komt het voor dat u de omzet wenst te berekenen van een bepaalde locatie voor een variabel tijdbereik, bijvoorbeeld van mei 2023 tot en met augustus 2023. In het rekenmodel zijn locatie, startdatum en einddatum variabele velden die de gebruiker zelf kan invullen, waarna automatisch met een formule de daarop betrekking hebbende omzet automatisch wordt berekend.

Uitgangspunt
De omzetcijfers worden in de vorm van een kruistabel bijgehouden:

In kolom A komen alleen uniek locaties voor en in daaropvolgende kolommen worden per tijdvak de omzetcijfers bijgehouden. De bladnaam van deze tabel is Data. De gegevens zijn niet geconverteerd naar een Excel-tabel. In de loop van tijd kunnen er nieuwe locaties en nieuwe tijdvakken worden toegevoegd.

Reportblad
We willen nu in een apart blad drie invulvelden aanmaken, te weten Locatie, Startdatum en Einddatum, waarin de gebruiker de gewenste data kan invoeren. In een aparte cel dient dan de omzet te worden berekend.

Allesomvattende formule
De allesomvattende formule is als volgt:

=SOM(VERSCHUIVING(Data!A1;VERGELIJKEN(C2;Data!A:A;0)-1;VERGELIJKEN(C4;Data!1:1;0)-1;1;VERGELIJKEN(C6;Data!1:1;0)-VERGELIJKEN(C4;Data!1:1;0)+1))

De functie VERSCHUIVING levert een dynamisch bereik op:

De VERSCHUIVING-functie bestaat uit 5 argumenten:

1. Het eerste argument (Verwijzing) is de startpositie en begint altijd in de linkerbovenhoek van de kruistabel.
2. Het tweede argument (rijen) bepaalt hoeveel rijen vanuit de startpositie naar beneden gesprongen wordt. Dit wordt berekend met de functie VERGELIJKEN. We kijken wat het positienummer van de gewenste locatie is in de eerste sleutelkolom van de kruistabel waarin zich de locaties bevinden. We dienen het getal 1 in mindering te brengen op het positienummer om de sprongstap te berekenen. VERGELIJKEN retourneert een positienummer in een bereik met unieke waarden. In dit geval wordt de rijpositie van de locatie bepaald.
3. Het derde argument (kolommen) bepaalt hoeveel kolommen vanuit de vorige positie naar rechts gesprongen wordt. Dit wordt berekend met de functie VERGELIJKEN. We kijken wat het positienummer van de startdatum is in de eerste sleutelrij van de kruistabel waarin zich de tijdvakken bevinden. We dienen het getal 1 in mindering te brengen op het positienummer om de sprongstap te berekenen. VERGELIJKEN retourneert een positienummer in een bereik met unieke waarden. In dit geval wordt de kolompositie van de datum bepaald.
4. Het vierde argument (hoogte) is 1, omdat we slechts naar een rij voor een locatie kijken.
5. Het vijfde argument (breedte) berekent het verschil in positienummers tussen einddatum en startdatum. Dit wordt wederom door middel van de functie VERGELIJKEN uitgevoerd. Daarbij tellen we nog 1 bij op om de breedte te completeren.

Schematisch voorgesteld ziet de formule er als volgt uit:

Analyseer, presenteer en rapporteer efficienter en effectiever met Excel
Werk je regelmatig met Excel? Ben je daarbij veel energie kwijt aan het opstellen van analyses en presentaties? Ontdek in de cursus Excel voor financieel professionals vele mogelijkheden die jou direct voordeel opleveren. Win tijd, verbeter je analyses en presenteer effectiever. Klik hier voor meer informatie of meld je direct aan.

Tony de Jonker is interim controller en trainer op het snijvlak van finance, Excel en Power BI voor gerenommeerde bedrijven. Sinds 1985 heeft hij honderden rekenmodellen ontwikkeld en is hij door Microsoft benoemd tot Excel Most Valuable Professional (MVP). Bij Alex van Groningen verzorgt hij meerdere programma’s waarmee je slimmer leert werken met Excel. Ontdek zijn expertise en trainingen.