Vraag: Ik werk met dynamische matrixformules om in een rechthoek de allocaties per tijdvak bereken. Hoe kan ik de totalen per tijdvak berekenen met één dynamische matrixformule?
Antwoord: Stel je hebt de volgende allocatie aangemaakt waarmee je de verwachte omzet per tijdvak kunt berekenen:
In cel G8 heb je een dynamische matrixformule ingevoerd die het volledige rechthoekgebied bestrijkt, herkenbaar aan de blauwe kaderrand.
In cellen G3 t/m M3 willen we automatisch de totalen per tijdvak. Plaats daarvoor in cel G3 de volgende formule: =BYCOL(G8#;SOM)
Met de functie BYCOL kun je met behulp van de SOM-functie de totalen per kolom berekenen binnen een dynamisch matrixbereik. Deze functie is in de Nederlandse versie van Excel onvertaald gebleven.
Vraag: Hoe maak ik een dynamische tijdlijn voor mijn budget- of liquiditeitsmodel?
Antwoord: Hiervoor maak je gebruik van diverse matrixformules, die er als volgt uitzien:
Ga op de volgende manier te werk:
Toekennen bereiknamen aan variabelen
Markeer cellen B2-C2 en kies uit het Lint: Formules > Gedefinieerde namen > Maken o.b.v. selectie > Linkerkolom > OK. Op analoge wijze markeer je E2-F2 en kies je uit het Lint: Formules > Gedefinieerde namen > Maken o.b.v. selectie > Linkerkolom > OK.
Aanmaken dynamische reeks
Plaats in cel B4 de formule: =REEKS(1;12;0;1). Deze hulprij beslaat één rij en 12 kolommen, begint met het cijfer 0 en springt bij elke volgende cel één stap vooruit. De reeks fungeert als dynamische plaatshouder voor de formules die hierna volgen. Omdat de reeks wordt omkaderd door een blauwe rand, zien we dat het gaat om een dynamisch matrixbereik.
Aanmaken startdatum
Plaats in cel B5 de volgende formule: =DATUM(InputYear;InputMonth+B4#;1)*(B4#>=0)
Met het hashtag-teken (#) geef je aan dat de formule automatisch naar rechts doorloopt. De formule verwijst intern naar de dynamische reeks, waardoor de startdatumformule de eigenschappen van de plaatshouderreeks overneemt en daardoor eveneens dynamisch wordt.
Aanmaken einddatum
Plaats in cel B6 de volgende formule: =DATUM(JAAR(B5#);MAAND(B5#)+1;0)
Met het hashtag-teken (#) geef je aan dat de formule automatisch naar rechts doorloopt. De formule verwijst intern naar de dynamische reeks, waardoor de startdatumformule de eigenschappen van de plaatshouderreeks overneemt en daardoor eveneens dynamisch wordt. De datum-functie met dag nul van een opgegeven maand en jaar levert automatisch de laatste dag van de voorafgaande maand op.
Berekenen werkdagen
Plaats in cel B7 de volgende formule: =NETTO.WERKDAGEN(ALS({1};B5#);ALS({1};B6#))
De functie NETTO.WERKDAGEN is van nature niet bedoeld voor dynamische reeksuitbreiding.
In combinatie met de ALS-functie wordt beoordeeld of er sprake is van een dynamisch matrixbereik. Dit gebeurt met behulp van {1}.
Vraag: Hoe kan ik een forecast maken van de afschrijvingen voor de komende 6 maanden?
Antwoord: Hiervoor maak je twee bladen aan: één blad met de aanschaffingen en de afschrijvingspercentages en één blad met de allocaties.
Het blad met aanschaffingen en de afschrijvingspercentages ziet er als volgt uit:
Zorg ervoor dat de tabellen als een Excel-tabel zijn opgemaakt met respectievelijke de volgende namen: DataMVA en Tbl_Afschrijvingen (Start > Stijlen > Opmaken als tabel > Ontwerp > Normaal en Tabelontwerp > Eigenschappen > Tabelnaam)
Het allocatieblad ziet er als volgt uit:
Het blad bevat de volgende 13 dynamische matrixformules:
Verticaal: 7 formules voor het ophalen van de basisgegevens
- Categorie: B7 =DataMVA[Categorie]
- Datum: C7 =DataMVA[Datum]
- Bedrag: D7 =DataMVA[Bedrag]
- Afschrijvings%: E7 =X.ZOEKEN(B7#;Tbl_Afschrijvingen[Categorie];Tbl_Afschrijvingen[AfschrPerc])
- Startdatum: F7 =DATUM(JAAR(C7#);MAAND(C7#)+1;1)
- Einddatum: G7 =DATUM(JAAR(F7#);MAAND(F7#)+1/E7#*12;0)
- MndAfschr: H7 =D7#*E7#/12
De afschrijving gebeurt in de maand volgend op de aanschaffing van het activum.
Horizontaal: 4 formules voor het aanmaken van een dynamische tijdreeks
- Zorg ervoor dat je in een aparte cel de Startdatum invult en deze cel de bereiknaam Startdatum geeft.
- Reeks: =REEKS(1;7;0;1)
- Start: =DATUM(JAAR(Startdatum);MAAND(Startdatum)+I3#;1)
- Eind: =DATUM(JAAR(Startdatum);MAAND(Startdatum)+1+I3#;0)
- Tijdlijn koptekst: I6=I4#
Rechthoek: 1 formule voor het berekenen van de allocatie van de afschrijvingskosten
- Allocatie: I7 =(I4#>=F7#)*(I5#>=F7#)*(I5#<=G7#)*(I5#<=G7#)*H7#
Voordeel: Voor de allocatieformule hoef je geen $-tekens te gebruiken om kolommen en rijen te fixeren.
Berekenen van de totalen per tijdvak: 1 formule
Totalen per tijdvak: I2 =BYCOL(I7#;SOM)
Lees hier meer Excel tips en trucs van Tony De Jonker.
