Je krijgt gegevens aangeleverd in een formaat dat specifiek een bepaalde analyse weergeeft, en je krijgt de opdracht om met die gegevens de analyse vanuit een andere hoek te bekijken. Dan stel je vast dat het aangeleverde formaat die andere invalshoek niet zomaar ondersteunt, omdat de gegevens niet op de gepaste manier gestructureerd zijn …

Iedereen die een tijd lang in een “data analyst” functie heeft gewerkt, herkent die situatie ongetwijfeld.

Daarom heb ik een les uitgewerkt over het verbeteren van de structuur van een gegevenstabel, om meer flexibele analyse en visualisatie mogelijk te maken. De bedoeling is om die “omvorming” van de tabel zo efficiënt mogelijk te doen, namelijk door de ingebouwde functionaliteiten van Excel te combineren en manuele ingrepen te beperken.

Die les vertrekt van een voorbeeld dat is opgemaakt als een “rapport” eerder dan een gegevenstabel, en dat een inzicht probeert te geven in de opleidingssituatie van Belgen en andere nationaliteiten in ons land afhankelijk van leeftijd en woonplaats (brongegevens van de nationale volkstelling in 2012).

We zien in deze tabel heel wat kenmerken die een flexibele analyse onmogelijk maken. Zo zijn er bijvoorbeeld heel wat samengevoegde cellen, zijn rijen en kolommen die (sub-)totalen bevatten vermeng met andere rijen en kolommen, en zijn de beschikbare “dimensies” (velden) star gedefinieerd voor de ene invalshoek van het basisbestand.

Waar we naartoe willen, is een tabel met een beter geschikte structuur, zonder overbodige (berekenbare) subtotalen en met een geoptimaliseerd aantal dimensies.

Vanuit dergelijke structuur kan je dan immers gemakkelijk een draaitabel maken …

… of een draaigrafiek met Slicers zodat je naar hartelust invalshoeken kan veranderen en scenario’s analyseren.

Je begint met het bedenken van een pad om van “punt A naar punt B” te gaan. In deze les resulteert dat in een 15-stappenplan waarin je met amper 7 Excel-functies en een handjevol andere Excel-technieken de omvorming uitvoert.

Het uitgewerkte plan is toepasbaar in ALLE momenteel ondersteunde Excel versies. Wellicht kan een en ander nog efficiënter in de meest recente versie, maar de concepten (en de “10 geboden” van een goede gegevenstabel) blijven dezelfde.

De omvorming van de meer dan 30000 gegevens in het rapport tot een tabel waarop flexibele analyse en visualisaties het bijvoorbeeld draaitabellen en -grafieken) mogelijk zijn, is mogelijk op amper een half uurtje …

Onderaan dit artikel vind je een PDF met het uitgeschreven stappenplan (beschikbaar in Nederlandse en Engelse versie).

Drie oefenbestanden zijn beschikbaar voor download (klik HIER om ze te downloaden):

• Het oorspronkelijke bronbestand
• Een bestand met de 15 stappen in aparte werkbladen toegelicht
• Het uiteindelijk gegevensbestand (amper groter in opslagruimte, maar veel nuttiger voor analyse)

Het toepassen van deze principes kan een ingrijpend effect hebben op de hoeveelheid tijd en werk die je in data analytics steekt.