Abacus by Schott // PerfectXL Spreadsheet Validation

PerfectXL Explore

Schoon je invoerdata 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.

Opschonen is essentieel!

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 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!

Schoon je invoerdata op in Excel files // PerfectXL Explore

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 in de dataset

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:

Gebruik niet meer invoerdata dan nodig

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 “Gooi al het overbodige weg”). Zorg er in ieder geval voor dat het tabblad met de bewerkte data slechts relevante informatie bevat.

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

Schoon je data in Excel files op // Worden cijfers herkend als cijfers? // PerfectXL Explore

Zie over dit onderwerp ook het artikel Let op eenheden en getalnotatie

Wat doe je met 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).

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!

  • Supersnel en efficiënt
  • Bespaart frustratie én tijd
  • Onmisbaar bij grote Excel files

Relevante artikelen
PerfextXL Explore

Bekijk een stapsgewijze walkthrough van de PerfectXL Explore tool. Hier leer je hoe je de Explore tool kunt gebruiken om beter inzicht in Excel files te krijgen.

Technische informatie over PerfectXL Explore. Systeemvereisten, releases en antwoord op veelgestelde vragen over onze software.

Cijfers zonder betekenis zijn waardeloos. Excel is een programma voor cijferaars, maar is pas zinvol als het voor de lezer of gebruiker duidelijk is wat er met die cijfers wordt bedoeld. Dus, verzeker jezelf ervan dat je alle data en formules van tekstuele informatie voorziet.

In deze video laten wij zien hoe je snel je input bronnen kunt controleren en hoe je kunt vaststellen of er onbekende bronnen zijn. Ook laten we je zien hoe je een efficiënte visualisatie kunt maken van de informatiestromen in je spreadsheet, waarmee je eenvoudig de functie en afhankelijkheden per worksheet kunt bepalen.

Als we een boek lezen of een rapport, dan lezen we het liefst van boven naar beneden en van links naar rechts. Denk bij het maken van een spreadsheet ook eens aan de leesbaarheid en besteed zorg aan de opbouw je spreadsheet.

Vergeet nooit: een spreadsheet heeft gemiddeld 13 gebruikers! Bedenk wat er gebeurt als je begint met een slordige opzet.

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.

Om een spreadsheet correct te interpreteren is het essentieel om te begrijpen hoe hij precies werkt. Spreadsheet visualisatie is een essentieel onderdeel van PerfectXL Explore.

In één oogopslag alle algemene eigenschappen van je spreadsheet inzien? Dat kan met PerfectXL Explore. Onder ‘General spreadsheets characteristics’ vind je een samenvatting van o.a. de werkbladen, de externe bronnen en mogelijke risico’s.

Wat kan PerfectXL voor jou doen?

Stel ons gerust al je vragen, we helpen je graag verder. We streven ernaar om je binnen 48 uur te beantwoorden.