Lees meer over dashboards en welke geschikt is voor jou in het nieuwe artikel van Stephan van Heusden: Welk dashboard presenteert mijn data het beste?

In deel 1 hebben jullie kunnen zien hoe je de functie ‘tabel’ in Excel moet gebruiken voor optimaal resultaat. Een aantal handige tips deel ik hier. Lees hier deel één van dit artikel. Ik ga in dit artikel verder met het delen van tips die je werk als analist sterk kunnen verbeteren en/of makkelijker maken.

In de afgelopen jaren heb ik aardig wat dashboards gebouwd, waarin het resultaat werd vergeleken met de doelstelling. In die jaren heb ik kennis opgedaan die ik graag met jullie deel. Dit ga ik doen in enkele korte artikelen, waarin ik aardig wat voorbeelden gebruik. Dit gaat van het vaststellen van doelstellingen tot het bouwen van een dashboard in Excel.

Tip 4: Verwijs naar data in een tabel

Nu komt het magische stuk. Waar je wellicht voorheen gebruik maakten van verticaal en horizontaal zoeken met cellen als bereik, kan je nu op een leesbare manier naar gegevens verwijzen.

Voorbeeld: toon het aantal bezoekers dat we in week 2 gehad hebben

In onderstaande tabel zie je de data op dagniveau, waarbij van elke dag bekend is bij welk weeknummer deze dag hoort. Je kunt nu heel eenvoudig het totaal aantal bezoekers optellen van één week, bijvoorbeeld week 2, door de volgende formule te gebruiken:

=SOMMEN.ALS(Verkooptabel[Bezoek];Verkooptabel[Week];2)

Bovenstaande formule doet het volgende:
• Kijk in de kolom ‘Bezoek’ van ‘Verkooptabel’.
• Tel in deze kolom alles op.
• Maar alleen als de kolom ‘Week’ van ‘Verkooptabel’ gelijk is aan ‘2’.

Hier zie je dat in week 2 er 36.404 bezoeken zijn geweest.

Het wordt nog mooier: meerdere criteria!

Je kunt in deze formule (SOMMEN.ALS) meerdere criteria gebruiken, bijvoorbeeld:
=SOMMEN.ALS(Verkooptabel[Bezoek];Verkooptabel[Week];">="&1;Verkooptabel[Week];"<="&3)

Deze formule telt alles op in de kolom ‘Bezoek’ in de tabel ‘Verkooptabel’ wanneer ‘Week’ groter of gelijk is aan 1 én ‘Week’ kleiner of gelijk is aan 3. Dit voorbeeld ziet er gelijk al wat ingewikkelder uit, denk ook bijvoorbeeld aan het toevoegen van ‘Jaar’ in de tabel, zodat je, wanneer je data van meerdere jaren in één tabel hebt staan, je enkel de weekdata van dit (of vorig) jaar uit de data kunt halen. Voorbeeld (hypothetisch, want ‘Jaar’ staat niet in de tabel hierboven)
=SOMMEN.ALS(Verkooptabel[Bezoek];Verkooptabel[Week];1;Verkooptabel[Jaar];2016)

De opbouw bij SOMMEN.ALS is als volgt: SOMMEN.ALS(optelbereik;criteriumbereik1;criterium1;[criteriumbereik2; criterium2];…).

Hierdoor wordt het heel gemakkelijk om elke vraag te beantwoorden met een formule, zolang de data maar in de tabel aanwezig is. Belangrijke tip hierbij is, zorg ervoor dat je tabel op dagniveau de data bezit, zodat je dit eenvoudig kunt optellen tot weken, maanden, kwartalen en jaren. Zou je de tabel opbouwen op weekniveau, dan ga je problemen krijgen wanneer je een maand of kwartaal vraag voor je kiezen krijgt.

Tip 5: denk goed na over de indeling van je tabel, zodat je flexibel bent in het optellen van de data. Mijn advies: haal data zoveel mogelijk op dagniveau binnen

Deze techniek, het gebruik van de SOMMEN.ALS formule in combinatie met een tabel kan je gebruiken voor een samenvattingsblad in een dashboard (waarop je enkel gegevens ‘ophaalt’ uit de brondata (de tabel)). Zoals: de doelstelling, wat gerealiseerd is en wat het vorig jaar was. Op basis van die gegevens kan je weer percentages berekenen. Voordeel van het gebruik van tabellen en de bijbehorende verwijzingen in formules náár deze tabellen, is dat je echt ‘ziet’ wat je doet, dus dat je een formule kunt lezen en kunt begrijpen. Hierdoor daalt de kans op fouten in je rapportages en dashboards.

Hopelijk kun je nu sterk starten met het nieuwe jaar en de nieuwe doelstellingen die je organisatie heeft. Succes!

Je werk in Excel begint met het opstellen van doelstellingen (lees deel één, lees deel twee).