Abacus by Schott // PerfectXL Spreadsheet Validation

PerfectXL Explore

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.

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

Schoon je data in Excel files op // 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! 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.

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.

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

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.

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

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.