Schoon je invoerdata goed op

Invoerdata vormt de basis van je spreadsheet en daarom verdient het serieuze aandacht. Indien mogelijk, schoon het zo op dat het een geautomatiseerd proces wordt voor de volgende keer dat je dezelfde spreadsheet gebruikt.

Controleer je bronnen

Komt de onderstaande situatie je bekend voor?

Je krijgt verschillende bronnen in verschillende formaten uit verschillende systemen als input. De bronnen zijn verre van compleet, en er zitten talloze dubbele waarden in. De ene bron gebruikt punten voor decimalen, de andere komma’s en er staan tabellen in waar je helemaal niets aan hebt. Ten slotte mist er consequente datumnotatie… en je data-analyse moet morgen 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, ookal is de druk nog zo hoog.

Quick wins

Vaak zijn er eenvoudige manieren om de invoerdata handmatig gebruiksklaar 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 de originele brondata en de aangepaste brondata is een alom bekende frustratie. Dus leer jezelf aan om data op te schonen door middel van formules en reserveer aparte tabbladen voor de originele brondata (1 bron per tabblad) en voor de bewerkte data.

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

Importeer niet meer dan nodig is

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

Worden cijfers gelezen 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), waarmee je kunt aangeven welk symbool er in de bron gebruikt wordt voor decimalen.

Lees meer over dit onderwerp in ons artikel “Let op eenheden en getalnotatie“

Lege velden

Er zijn datasets waarbij bijvoorbeeld een ‘hoofdcategorie’ van iets bovenin 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).

Onnodige spaties in tekstvelden

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)).

Samengevat

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