Dit artikel behandelt een aantal interessante onderwerpen naar aanleiding van vragen. Ook u kunt er uw voordeel mee doen door deze vragen en antwoorden te bekijken en uit te proberen. Hoe zit het met Zoeken in kruistabel, scenario’s berekenen en seizoenspatronen voorspellen?

Scenario’s berekenen

Vraag: Ik wil in mijn financieel model meerdere scenario’s doorlopen waarbij het rentepercentage kan wijzigen. Hoe kan ik dit in het model inbouwen?

Antwoord: Maak een plaatshouder waarin u de variabelen (bijvoorbeeld rentepercentages) plaatst en met behulp van de functie KIEZEN (CHOOSE) kunt u het bijbehorende rentepercentage opzoeken.

U maakt drie scenario’s aan in de cellen C2 tot en met C4. In cel C6 maakt u een gegevensvalidatie veld aan via Gegevens > Hulpmiddelen voor gegevens > Gegevensvalidatie > Lijst met de volgende instellingen:

Cel C6 maakt u op via Control-1 (Celeigenschappen) > Aangepast: “Scenario “0.

In Cel C7 plaatst u de formule: =KIEZEN(C6;$C$2;$C$3;$C$4)

Opzoeken in kruistabel

Vraag: Ik heb een kruistabel met verkopen per locatie en per maand-jaar (datumopmaak). De kruistabel is opgemaakt als een tabel. Als ik de verkopen van een locatie en een maand-jaar wil opzoeken met VERT.ZOEKEN en VERGELIJKEN dan resulteert de formule in een foutmelding. Wat gaat er fout?

Antwoord: Het opmaken als een Excel-tabel leidt ertoe dat de datumopmaak in rij 1 wordt omgezet als tekst. De tabelnaam in het voorbeeld is DataVerkopen.

In cel C10 converteert u de zoekdatum in cel C8 met de volgende formule: =TEKST(C8;”mmm-jj”). U kunt de verkopen in cel C12 dan opzoeken met de formule: =VERT.ZOEKEN(C7;DataVerkopen;VERGELIJKEN(C10;DataVerkopen[#Kopteksten];0);1).

Seizoenspatronen voorspellen

Vraag: Hoe kan ik in Excel een voorspelling maken als er een seizoenspatroon optreedt?

Antwoord: U kunt daartoe de functie VOORSPELLEN.ETS gebruiken.

Stel, we hebben de volgende opgaaf van het aantal bezoekers van 5 mei tot met 25 mei en u wenst een voorspelling te maken voor de dagen erna.

In cel C22 maakt u een cel-verwijzing naar B22. In cel C23 geeft u de volgende formule in:

=VOORSPELLEN.ETS(A23;$B$2:$B$22;$A$2:$A$22)

Deze formule kunt u naar de volgende cellen kopiëren.

Als u een lijngrafiek maakt dan ziet deze er als volgt uit:

De functie VOORSPELLEN.ETS werkt alleen naar behoren als voldaan wordt aan de volgende voorwaarden:

• Er dient een regelmatig patroon te onderkennen zijn (seizoenspatroon)
• Er dient geen sprake te zijn van een lineair verband.
• Als er geen regelmaat te onderkennen valt, dan gaat Excel over op de lineaire voorspelling.
• De functie kan werken er maximaal 30% van de datapunten ontbreken.
• Indien er duplicaten voorkomen in de dataset, zal Excel deze waarden samenvoegen.

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2020, afl. 10.

Hier leest u meer Excel tips en trucs door Tony De Jonker