Schoon je data op

Garbage in, garbage out? Niet als je tijdens de bouw van je spreadsheet serieus aandacht besteedt aan het opschonen van je invoerdata. We raden je aan om de opschoning te automatiseren, zodat je er bij een volgend gebruik geen omkijken meer naar hebt.

Excel Guidelines voor Professionals - Schoon je data op :: PerfectXL

Als fervent Excel gebruiker herken je waarschijnlijk de volgende situatie wel: Je krijgt verschillende bronnen in verschillende formaten uit verschillende systemen als input. De bronnen blijken verre van compleet, en er zitten net zo goed dubbelingen in. De ene bron gebruikt punten voor decimalen, de andere komma’s. Er zitten complete tabellen bij waar je helemaal niets aan hebt. En er is geen sprake van consequente datumnotatie… en morgen moet je data-analyse klaar zijn!

De meeste Excel gebruikers hebben een hekel aan het opschonen van data en beginnen het liefst direct met het maken van analyses. Maar opschonen is echt nodig! Hoe groot de druk om op te leveren ook is.

Quick wins in de dataset

Vaak zijn er eenvoudige mogelijkheden om de invoerdata zelf bruikbaar te maken voor analyse. Je kunt punten vervangen door komma’s, regels weggooien, ontdubbelen etc. Maar let op: een handmatig aanpassing van data is heel lastig te valideren door controllers en accountants. De aansluiting tussen brondata en de handmatig aangepaste brondata is een alom bekende frustratie. Dus als het kan, schoon dan je data op door middel van formules en reserveer aparte tabbladen voor de originele brondata (1 bron, 1 tabblad) en de bewerkte data.

We zouden een boek kunnen schrijven over het opschonen van data door middel van formules. Maar in deze guideline beperken we ons tot enkele tips:

Importeer niet meer data dan nodig

Krijg je meer informatie aangeleverd dan je eigenlijk nodig hebt? Belast je eigen spreadsheet hier dan niet mee. Aangeleverde tabellen waar je niets mee doet, zou je niet moeten opnemen in je spreadsheet. Indien mogelijk laat je de exportscripts van andere systemen zo draaien dat ze geen zinloze kolommen aanleveren. (Zie ook onze guideline “Gooi al het overbodige weg”). Zorg er in ieder geval voor dat het tabblad met de bewerkte data slechts relevante informatie bevat.

Excel Guidelines voor Professionals - Schoon je data op - Data verwijderen :: PerfectXL

Worden cijfers herkend als cijfers?

Sommige importbestanden zorgen ervoor dat Excel niet in staat is om cijfers te herkennen als cijfers. Cijfers worden dan geïmporteerd als tekstvelden, met allerlei vervelende gevolgen. Een simpel trucje is om de betreffende kolom te selecteren en deze via het menu Data-> tekst naar data om te zetten naar getallen (vraag ons niet waarom het werkt, maar het werkt). Dit trucje moet je helaas wel steeds herhalen en dat maakt de data dan weer lastig te verifiëren. Gelukkig is er de formule NUMBERVALUE(A2), waarbij je kunt aangeven welk symbool er in de bron gebruikt wordt voor decimalen.

Wat te doen met lege velden?

Er zijn datasets waarbij bijvoorbeeld een “hoofdcategorie” van iets boven in kolom A staat, maar alleen als die categorie niet gelijk is aan de hoofdcategorie van de rij erboven. Het menselijk oog begrijpt dit, maar Excel niet. Vul deze lege cellen in de verrijkte dataset op met functies zoals IF(SOURCE!A3=””;A2;SOURCE!A3).

Zijn er overbodige spaties in tekstvelden? Is het hoofdlettergebruik consequent?

Het lijkt niet belangrijk, maar het komt heel vaak voor dat geïmporteerde tekstdata in Excel eindigt met een of meerdere spaties. Als dit inconsequent gebeurt dan kun je problemen verwachten met zoekfuncties en draaitabellen. Een handige functie om dit probleem te herstellen is de functie TRIM(SOURCE!A2). Wil je dit combineren met het verzorgen van consequent hoofdlettergebruik dan kun je kiezen voor PROPER(TRIM(SOURCE!A2)).

Kortom: besteed tijd en aandacht aan het opschonen van invoerdata met behulp van formules. Dit zal je veel ellende besparen. Garbage in, great analyses out.

Never miss a Guideline!