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:

• Het eerste argument (Verwijzing) is de startpositie en begint altijd in de linkerbovenhoek van de kruistabel.
• 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.
• 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.

• Het vierde argument (hoogte) is 1, omdat we slechts naar een rij voor een locatie kijken.
• 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: