Gegevens opschonen

Een tabel die nauwkeurig lijkt, kan toch het verkeerde antwoord geven. Opschonen begint bij de betekenis van de records: wat er is gebeurd, wie of wat elke rij beschrijft en welke velden de analyse nodig heeft.

Wat gegevensintegriteit betekent

Gegevensintegriteit betekent juistheid, volledigheid, consistentie en betrouwbaarheid gedurende de hele levenscyclus van gegevens. Waarden moeten overeenkomen met de feiten die ze beweren vast te leggen. Vereiste informatie moet aanwezig zijn en hetzelfde feit moet dezelfde betekenis houden wanneer het tussen systemen wordt overgedragen.

Integriteit kan worden aangetast tijdens invoer, opslag, overdracht of analyse. Iemand kan een aantal verkeerd typen. Kopieën in twee systemen kunnen uiteen gaan lopen, een probleem dat gegevensreplicatie heet. Bij een onderbroken overdracht kan de helft van een bestand worden geladen. Als je één kolom afzonderlijk sorteert, kan elk bedrag aan de verkeerde bestelling worden gekoppeld. Ook systeemstoringen en ongeautoriseerde wijzigingen kunnen records beschadigen.

Herken het probleem voordat je het oplost

Vervuilde gegevens zijn onvolledig, onjuist, inconsistent of niet relevant voor de vraag. Verschillende problemen vereisen verschillende acties.

Een nette opmaak bewijst niet dat gegevens juist zijn. Een leverdatum kan eruitzien als een datum maar toch vóór de besteldatum liggen. Een testklant kan een volledig record hebben maar niet relevant zijn voor de werkelijke omzet van klanten.

Volg het effect op een antwoord

Stel dat bestelling 1003 ter waarde van 250,00 twee keer voorkomt in een export. Als je rijen telt, komt er één extra bestelling bij en als je bedragen optelt, komt er 250,00 te veel bij. De formules kunnen perfect werken terwijl de invoer een onjuist resultaat oplevert. Tekstvarianten veroorzaken een ander probleem: een grafiek kan de omzet van Lissabon verdelen over afzonderlijke stadslabels.

Opgeschoonde gegevens zijn geschikt voor de analyse die wordt uitgevoerd. Een ontbrekende tweede voornaam heeft geen effect op de omzet per regio. Een ontbrekend bestelbedrag heeft wel invloed op de totale omzet. Beoordeel de volledigheid aan de hand van de velden die nodig zijn voor de vraag en leg vervolgens resterende ontbrekende waarden vast.

Bescherm het uitgangspunt

Bewaar een ongewijzigde ruwe kopie voordat je wijzigingen aanbrengt. Gebruik een ander bestand, een apart werkblad of de oorspronkelijke databasetabel en schoon een werkkopie op. Een hulpmiddel zoals Duplicaten verwijderen verwijdert rijen uit het geselecteerde bereik; met de ruwe kopie kun je vergelijken en een beslissing opnieuw uitvoeren.

Doorloop vijf fasen: onderzoeken, beslissen, toepassen, documenteren en verifiëren. Leg een probleem vast voordat je kiest hoe je het behandelt. Leg elke behandeling vast wanneer je die toepast en controleer daarna het resultaat aan de hand van de ruwe gegevens. Interne gegevens vereisen dezelfde controle als een extern bestand: herhaalde imports en veranderde betekenissen van velden komen ook binnen organisaties voor. Meld terugkerende invoer- of exportproblemen aan de gegevenseigenaar, zodat de volgende levering beter wordt.

Dataprofilering is een systematisch onderzoek van een dataset voordat je die verandert. Het geeft je een uitgangssituatie en een probleemlijst: de omvang van de tabel, de inhoud van elke kolom, ontbrekende waarden, herhaalde sleutels en waarden die onderzoek vereisen.

Begin met de structuur van de tabel

Bepaal wat één rij betekent, zoals één bestelling of één klant. Controleer of er één koprij is en één soort informatie per kolom. Zoek de sleutel, de kolom of combinatie van kolommen die één record moet identificeren. Een herhaalde bestel-ID vereist onderzoek; herhaalde stadsnamen zijn te verwachten.

Leg het rijaantal aan het begin vast. Onderzoek vervolgens per kolom het verwachte type, of de kolom verplicht is en welke waarden zijn toegestaan. Een gegevenswoordenboek of de gegevenseigenaar geeft deze betekenissen. Het minimum en maximum kunnen een datum buiten de rapportageperiode of een plaatsaanduiding zoals 999 in een leeftijdskolom aan het licht brengen.

Onderzoek een spreadsheet

Sorteer en filter de tabel en houd hele rijen bij elkaar. Sorteer oplopend en aflopend om lege waarden, onverwachte tekst en extreme waarden te vinden. Vervolgkeuzelijsten van filters tonen schrijfwijzen van categorieën. Filters in Excel negeren hoofdletterverschillen, zodat één weergegeven categorie verschillen in hoofdlettergebruik kan verbergen; gebruik EXACT wanneer je het hoofdlettergebruik zelf moet controleren. Voorwaardelijke opmaak kan herhaalde sleutels en waarden buiten een bereik markeren.

Voor rijen 2 tot en met 501 zijn onder meer deze controles bruikbaar:

• =AANTALARG(B2:B501) telt niet-lege cellen.
• =AANTAL.LEGE.CELLEN(B2:B501) telt lege waarden, inclusief formules die lege tekst teruggeven.
• =AANTAL(B2:B501) telt numerieke cellen.
• =MIN(B2:B501) en =MAX(B2:B501) tonen het numerieke bereik.
• =AANTAL.ALS(C2:C501;"Lisbon") telt overeenkomende waarden en negeert hoofdletterverschillen.

Als het aantal getallen in een bedragkolom kleiner is dan het aantal niet-lege waarden, onderzoek dan het verschil. Sommige waarden kunnen getallen als tekst zijn; andere kunnen labels zijn zoals “N/A”. Ook een cel met alleen een spatie vraagt aandacht, al lijkt die leeg.

Onderzoek met SQL

Deze query's lezen de gegevens zonder ze te veranderen:

SELECT COUNT(*) AS rows_total,
       COUNT(amount) AS amounts_present,
       COUNT(*) - COUNT(amount) AS amounts_missing,
       MIN(order_date) AS first_date,
       MAX(order_date) AS last_date
FROM orders;

COUNT(*) telt rijen. COUNT(amount) telt bedragen die niet NULL zijn. Het verschil telt ontbrekende bedragen die als NULL zijn weergegeven, maar detecteert geen tekstuele plaatsaanduidingen.

Om categoriewaarden en hun frequentie te onderzoeken:

SELECT city, COUNT(*) AS n
FROM orders
GROUP BY city
ORDER BY city;

GROUP BY verzamelt gelijke waarden; COUNT(*) telt de rijen in elke groep. NULL vormt een eigen groep. Tekstgelijkheid volgt de sorteer- en vergelijkingsregels van de database. PostgreSQL en SQLite houden hoofdlettervarianten doorgaans apart; veelgebruikte instellingen van MySQL voegen ze samen.

Zet controles om in een probleemlijst

Voor zes bedragen 40, NULL, 25, 60, NULL en 35 is het rijaantal 6 en het aantal aanwezige bedragen 4: twee bedragen vereisen aandacht. Voor de steden “Lisbon”, “lisbon ”, “Porto”, NULL, “LISBON” en “Porto” vindt een exacte tekstvergelijking vier schrijfwijzen die niet NULL zijn, hoewel er slechts twee steden zijn vertegenwoordigd.

Leg beide bevindingen vast, samen met de betrokken kolom en het aantal. Onderzoek veelvoorkomende waarden en uitersten op verborgen plaatsaanduidingen, testrecords en datums zoals “1900-01-01”. Door eerst de hele tabel te profileren ontdek je ook afhankelijkheden: overbodige spaties verwijderen kan het aantal duplicaten veranderen, dus wacht met duplicaten verwijderen totdat de vergelijkingsvelden zijn gestandaardiseerd.

Gegevensvalidatie controleert of ingevoerde waarden voldoen aan regels voor toegestane gegevens. Een waarde kan aanwezig en netjes weergegeven zijn en toch een regel overtreden. Een aantal van -3 is nog steeds ongeldig wanneer bestelaantallen positief moeten zijn.

Kies regels op basis van de betekenis van gegevens

Lees het gegevenswoordenboek of vraag de gegevenseigenaar. Een hotelboeking voor gebruik overdag kan nul nachten toestaan; een ander boekingssysteem kan er minstens één vereisen. Leg de werkelijke grens vast en of het veld verplicht is.

Een formaatcontrole toetst een patroon, niet of het onderliggende feit waar is. Een eenvoudige e-mailcontrole op één “@” met tekst aan beide kanten kan niet bevestigen dat een mailbox bestaat.

Markeer fouten in een spreadsheet

Zet een controle in een hulpkolom en trek die naar beneden door. Voor een verplicht numeriek percentage in B2 controleert =EN(ISGETAL(B2);B2>=0;B2<=100) zowel type als bereik. De typecontrole is van belang omdat een lege waarde zich in een vergelijking als nul kan gedragen.

Met echte datums in C en D markeert =ALS(D2<C2;"check";"ok") een levering vóór de bestelling. Controleer verplichte datums afzonderlijk op lege waarden. Voor ID's in kolom A markeert =AANTAL.ALS($A$2:$A$501;A2)>1 herhaalde waarden. Filter de resultaten van de hulpkolom om alle fouten te zien.

Geef fouten weer met SQL

SELECT * FROM orders
WHERE quantity <= 0 OR quantity IS NULL;

Dit vindt niet-positieve en ontbrekende aantallen. Om statussen te controleren:

SELECT * FROM orders
WHERE status NOT IN ('open', 'shipped', 'returned')
   OR status IS NULL;

De expliciete NULL-controle is van belang. NOT IN alleen geeft geen rij met een NULL-status terug, omdat de uitkomst van de vergelijking onbekend is. Een controle tussen velden zoals delivery_date < order_date vereist ook afzonderlijke aanwezigheidscontroles voor verplichte datums.

Voorkom nieuwe fouten en los oude op

Gegevensvalidatie in Excel en Google Sheets kan een vervolgkeuzelijst aanbieden, een getalbereik beperken of waarschuwen bij een invoer. Databasetypen en beperkingen zoals NOT NULL en UNIQUE dwingen verwante regels af. Een regel toevoegen herstelt oude records niet; onderzoek die afzonderlijk. Met Ongeldige gegevens omcirkelen in Excel kun je bestaande fouten markeren.

Bekijk leeftijden uit een enquête onder volwassenen: 34, 0, 41, 230 en “veertig”, met een bereik van gehele getallen van 18 tot 120. “Veertig” voldoet niet aan de typecontrole; 0 en 230 voldoen niet aan de bereikcontrole. Het woord heeft de duidelijke numerieke betekenis 40. De bedoelde waarde van 230 is onzeker, dus raad niet tussen 23 en 30. Herstel een betrouwbare waarde, markeer die als ontbrekend of sluit het record uit en leg de beslissing vast.

Een ongebruikelijke waarde verschilt van een ongeldige waarde. Een bestelling van 500 eenheden kan echt zijn. Toets die aan de bestelregels en het bronrecord voordat je besluit dat het een fout is.

Twee vergelijkbare rijen zijn niet noodzakelijk één record dat twee keer is ingevoerd. Een duplicaat beschrijft dezelfde entiteit of gebeurtenis uit de werkelijkheid als een andere rij. Bepaal eerst wat die entiteit of gebeurtenis identificeert.

Definieer de sleutel en vergelijkingsregels

Een bestel-ID kan een bestelling identificeren. Een naam alleen is meestal een slechte klantsleutel, omdat verschillende mensen dezelfde naam kunnen hebben. Zelfs twee identieke aankopen op dezelfde dag kunnen echte afzonderlijke gebeurtenissen zijn. Controleer de betekenis van de rij en de sleutel bij de gegevenseigenaar voordat je rijen verwijdert die op elkaar lijken.

Exacte duplicaten komen in alle kolommen overeen. Bijna-duplicaten verwijzen naar hetzelfde, maar hebben kleine verschillen, zoals een extra spatie of een veranderd telefoonnummer. Schoon de vergelijkingsvelden op voordat je gaat zoeken. Als e-mail de afgesproken sleutel is, maak je spaties en hoofdlettergebruik gelijk in een hulpkolom.

Voor vijf e-mailadressen “ana@example.com”, “ben@example.com”, “Ana@Example.com ”, “chen@example.com” en “ben@example.com” vindt een exacte vergelijking van de ruwe waarden de herhaalde invoer van Ben. Als je overbodige spaties verwijdert en alles in kleine letters zet, wordt ook de herhaalde invoer van Ana zichtbaar. Na het verwijderen van twee extra kopieën blijven drie klantsleutels over.

Markeer rijen voordat je ze verwijdert

Voor opgeschoonde sleutels in B2:B501:

• =AANTAL.ALS($B$2:$B$501;B2)>1 markeert elke rij waarvan de sleutel wordt herhaald.
• =AANTAL.ALS($B$2:B2;B2)>1 markeert alleen de tweede en latere voorkomens.

Het tweede bereik groeit wanneer je naar beneden doortrekt, dus de eerste keer dat een waarde voorkomt, is het aantal één. AANTAL.ALS negeert hoofdletterverschillen; verwijder eerst overbodige spaties. Onderzoek gemarkeerde groepen op tegenstrijdige informatie voordat je een bewaarregel toepast.

Kies wat blijft staan

Duplicaten verwijderen in Excel en Google Sheets vergelijkt de kolommen die je selecteert. Als je alleen e-mail selecteert, kan een rij met een ander telefoonnummer worden weggegooid. Als je alle kolommen selecteert, blijft die tegenstrijdigheid behouden omdat de volledige rijen verschillen. Beide hulpmiddelen gebruiken eigen vergelijkingsregels, waaronder hoofdletterongevoelige vergelijkingen. Daardoor zijn ze zonder extra controle ongeschikt om hoofdletterverschillen teken voor teken af te dwingen.

Excel bewaart de eerste keer dat een waarde voorkomt. Als het nieuwste record moet blijven staan, sorteer dan eerst de volledige rijen op bijwerkdatum, met het nieuwste record bovenaan. Los records met gelijke datums of tegenstrijdige gegevens expliciet op. Een gedocumenteerde regel kan het meest recent bevestigde record bewaren of geverifieerde velden samenvoegen. Leg zowel de gekozen kolommen als het aantal verwijderde records vast.

=UNIEK(A2:C501) geeft unieke rijen terug in een nieuw bereik in huidige versies van Excel en Google Sheets. De functie behoudt de bron en is bruikbaar om een resultaat vooraf te bekijken.

Vind herhaalde sleutels met SQL

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

GROUP BY maakt een groep voor elk e-mailadres; HAVING behoudt groepen waarvan het aantal groter is dan één. WHERE filtert afzonderlijke rijen vóór het groeperen. Voer de controle uit op gestandaardiseerde sleutels, via een opgeschoonde tabel of door op de opschoningsexpressie te groeperen.

SELECT DISTINCT email, name FROM customers; geeft elke combinatie van e-mailadres en naam één keer terug. Dat garandeert geen unieke e-mailadressen: twee rijen met één e-mailadres en verschillende namen blijven beide staan. Controleer de afgesproken sleutel na het ontdubbelen.

Herhaalde imports en overlappende exports veroorzaken vaak duplicaten. Leg de oorzaak vast met de gegevenseigenaar, zodat een verbetering van de tabel van vandaag ook de volgende export verbetert.

Een ontbrekende waarde kan zichtbaar zijn als een lege plek of verborgen achter een waarde die aannemelijk lijkt. Zoek uit hoe de bron ontbrekende informatie vastlegt voordat je die gaat tellen. Een vastgelegde nul kan echt zijn, terwijl “unknown” een plaatsaanduiding kan zijn.

Onderscheid lege waarden, tekst en NULL

In een spreadsheet kunnen een lege cel, een cel met een spatie en een formule die "" teruggeeft er hetzelfde uitzien. ISLEEG is alleen waar voor een werkelijk lege cel. Een spatie is tekst. AANTAL.LEGE.CELLEN telt ook formules die lege tekst teruggeven, maar telt een cel met een spatie niet. Voor gewone spaties detecteert =SPATIES.WISSEN(B2)="" zowel lege invoer als invoer met alleen spaties.

SOM, GEMIDDELDE en AANTAL in spreadsheets slaan lege cellen en tekst in een bereik waarnaar wordt verwezen over. Ze tellen numerieke nul mee. Voor de bedragen 48, 52, een lege waarde, 45, een lege waarde, 55, 50 en 46 is de som van de bekende bedragen 296. GEMIDDELDE deelt door zes en geeft ongeveer 49,33. Als je de lege waarden vervangt door nul, verandert de noemer naar acht en het gemiddelde naar 37.

In SQL markeert NULL een onbekende of afwezige waarde. Nul is een bekend getal. De lege tekenreeks '' is tekst zonder tekens in PostgreSQL, MySQL, SQL Server en SQLite. Geïmporteerde lege waarden kunnen in beide vormen voorkomen, dus controleer de opgeslagen vorm:

SELECT * FROM orders
WHERE city IS NULL OR TRIM(city) = '';

Gebruik IS NULL en IS NOT NULL. amount = NULL vindt geen ontbrekende bedragen: een vergelijking met NULL geeft een onbekende uitkomst, die WHERE niet behoudt.

Ken de noemer

COUNT(*) telt alle rijen. COUNT(amount), SUM, AVG, MIN en MAX slaan NULL-bedragen over. Voor 40, NULL, 25, 60, NULL en 35 zijn er zes rijen maar vier bekende bedragen. SUM is 160 en AVG is 40 omdat de functie 160 door vier deelt.

Deze functies kunnen een numerieke plaatsaanduiding zoals -1 of 999 niet herkennen. Ga de betekenis van plaatsaanduidingen na bij de gegevenseigenaar en tel ze vervolgens afzonderlijk of zet ze om naar ontbrekende waarden. Onderzoek naast tellingen van lege waarden ook de frequenties van ongebruikelijke codes.

Zoek naar patronen in ontbrekende waarden

Tel ontbrekende waarden per kolom en als aandeel van de rijen. Twee ontbrekende bedragen van de acht is 25%. Onderzoek vervolgens of ontbrekende waarden zich concentreren per winkel, bron of maand. Een ontbrekend veld dat beperkt blijft tot één exportbron kan wijzen op een importprobleem dat kan worden opgelost.

De reden waarom waarden ontbreken is van belang:

• Volledig willekeurig ontbrekende waarden: een niet-gerelateerde gebeurtenis, zoals een willekeurige opslagfout, veroorzaakt de ontbrekende waarde.
• Willekeurig ontbrekende waarden: de kans op een ontbrekende waarde hangt af van vastgelegde informatie, zoals oudere klanten die vaker een optioneel online veld overslaan.
• Niet-willekeurig ontbrekende waarden: de kans hangt af van de ontbrekende waarde zelf, zoals mensen met zeer hoge inkomens die hun inkomen niet invullen.

In het inkomensvoorbeeld kan het gemiddelde onder respondenten lager zijn dan het gemiddelde van iedereen. Ontbrekende waarden met één getal invullen neemt dat selectieprobleem niet weg. Rapporteer wat ontbreekt en wie de resterende waarden beschrijven voordat je een gemiddelde interpreteert.

Voor ontbrekende waarden moet je een beslissing nemen die past bij de vraag die je beantwoordt. Een klant zonder leeftijd kan nog steeds bijdragen aan de totale omzet. Een bestelling zonder bedrag kan geen bekende waarde bijdragen aan de omzet.

Kies een actie

Probeer eerst de waarde te herstellen. Controleer een factuur, oorspronkelijk formulier of gezaghebbend bronsysteem. Een regeltotaal dat is afgeleid van een geverifieerd aantal en een geverifieerde prijs per stuk is herstelde informatie. Een waarde die op basis van andere klanten is geschat, is een schatting; dit heet imputatie.

Behoud de betekenis van een getal

Als je een categorie invult met “Onbekend”, blijft de rij meetellen terwijl de ontbrekende waarde zichtbaar blijft. Een numeriek veld invullen met nul is alleen gerechtvaardigd als ontbrekend daadwerkelijk nul betekent. Een ontbrekend aantal retouren kan nul betekenen als het bronproces die conventie bevestigt; een onbekend verkoopbedrag heeft die betekenis niet.

Voor de bekende bedragen 48, 52, 45, 55, 50 en 46 is de som 296 en het gemiddelde ongeveer 49,33. Als twee lege waarden in nul worden veranderd, blijft de som 296 maar daalt het gemiddelde naar 37. De extra rijen tellen nu als waarnemingen met waarde nul.

Ook invullen met het gemiddelde of de mediaan verandert de gegevens. De mediaan van de zes bekende bedragen is 49. Als je beide ontbrekende waarden invult met 49, voeg je 98 toe en wordt het gemiddelde van de acht waarden 49,25. Die twee toegevoegde waarden zijn niet waargenomen. Herhaalde centrale waarden laten de gegevens minder gespreid lijken, dus bewaar een markering die ze aanduidt. Bij sterk scheve waarden zoals inkomens beschrijft de mediaan een typische waarde vaak beter dan het gemiddelde.

Pas de regel zichtbaar toe

In SQL geeft COALESCE(city, 'Unknown') het eerste argument terug dat niet NULL is. De functie vult steden met NULL in maar laat een lege tekenreeks onveranderd. NULLIF(city, 'N/A') zet precies die plaatsaanduiding om in NULL. Door normalisatie en invulling te combineren maak je de behandeling expliciet:

COALESCE(NULLIF(TRIM(city), ''), 'Unknown') AS city

Een hulpkolom voor categorieën in een spreadsheet kan =ALS(SPATIES.WISSEN(B2)="";"Unknown";B2) gebruiken. Een afzonderlijke markering kan =SPATIES.WISSEN(B2)="" gebruiken, zodat ook na het invullen zichtbaar blijft dat de oorspronkelijke waarde ontbrak.

Stem uitsluitingen af op de analyse

Zoek bij de totale omzet van 1.000 bestellingen met drie ontbrekende bedragen eerst de facturen op. Als herstel niet lukt, rapporteer dan dat het totaal 3 van de 1.000 bestellingen uitsluit, oftewel 0,3%. Gooi een rij niet weg omdat een ongebruikt veld leeg is.

Als 400 van de 1.000 klantleeftijden ontbreken, zou invullen met het gemiddelde 40% van de kolom in schattingen veranderen. Bij een analyse van de 600 bekende leeftijden moet duidelijk worden vermeld wat de dekking is. Onderzoek waarom die leeftijden ontbreken: rijen verwijderen kan de resultaten ook vertekenen wanneer die klanten verschillen van de rest. Behandel ontbrekende waarden consistent en documenteer elke latere verandering in die behandeling.

Door tekst op te schonen maak je waarden met dezelfde betekenis vergelijkbaar. Maak eerst een lijst van de unieke waarden en hun aantallen. Daaruit blijkt of een kolom aanpassing van spaties, consistent hoofdlettergebruik of een omzetting van meerdere schrijfwijzen naar één label nodig heeft.

Verwijder spaties en verborgen tekens

In Excel en Google Sheets verwijdert =SPATIES.WISSEN(A2) spaties aan beide uiteinden en brengt de functie herhaalde spaties binnen de tekst terug tot één. De functie zet “ Ana Silva ” om in “Ana Silva”. =WISSEN.CONTROL(A2) verwijdert niet-afdrukbare tekens zoals regeleinden.

Een vaste spatie die van een webpagina is gekopieerd, kan na TRIM blijven staan. Vervang teken 160 eerst door een gewone spatie:

=SPATIES.WISSEN(SUBSTITUEREN(A2;TEKEN(160);" "))

Lees geneste functies van binnen naar buiten: SUBSTITUEREN wordt eerst uitgevoerd, daarna werkt TRIM op het resultaat. Onderzoek opgeschoonde voorbeelden, want door een regeleinde te verwijderen kunnen woorden aan elkaar komen die gescheiden moeten blijven.

SQL TRIM(city) verwijdert spaties aan de uiteinden maar laat herhaalde spaties binnen de tekst staan. TRIM(' Ana Silva ') behoudt daarom drie spaties tussen de namen. LTRIM verandert het linkeruiteinde en RTRIM het rechter. Kies een opschoningsexpressie die past bij de tekens die je hebt gevonden.

Standaardiseer hoofdlettergebruik bewust

UPPER en LOWER werken in beide hulpmiddelen. Kleine letters kunnen een bruikbare conventie zijn voor e-mailadressen; hoofdletters kunnen geschikt zijn voor landcodes. PROPER in een spreadsheet schrijft woorden met een hoofdletter, maar kan niet bepalen wat de juiste spelling van een naam is: “mcdonald” wordt “Mcdonald” en “PT123” wordt “Pt123”. Controleer namen en houd codes in het bedoelde hoofdlettergebruik.

Tekstvergelijkingen volgen de instellingen van het hulpmiddel. AANTAL.ALS, VERT.ZOEKEN en filters in Excel negeren hoofdletterverschillen. PostgreSQL behandelt “Lisbon” en “LISBON” doorgaans als verschillende tekstwaarden. =GELIJK(A2;B2) in een spreadsheet controleert of tekst identiek is, inclusief hoofdlettergebruik. Standaardiseer expliciet wanneer dezelfde categorie in verschillende hulpmiddelen consistent moet werken, en behoud betekenisvolle hoofdletterverschillen in hoofdlettergevoelige identificatiecodes.

Zet hele waarden om naar één categorie

Overbodige spaties verwijderen en hoofdlettergebruik aanpassen verbetert geen verschillende schrijfwijzen. Een omzettabel kan elke variant koppelen aan een standaardlabel. Zoek de volledige opgeschoonde waarde op met een exacte overeenkomst en onderzoek waarden waarvoor geen overeenkomst is.

In SQL kan CASE dezelfde omzetting uitdrukken:

CASE
  WHEN UPPER(TRIM(country)) IN
       ('NL', 'NETHERLANDS', 'THE NETHERLANDS', 'HOLLAND')
    THEN 'Netherlands'
  ELSE TRIM(country)
END AS country

ELSE bewaart niet-omgezette waarden voor controle. Tel de resterende categorieën na de omzetting; neem niet aan dat de eerste paar rijen alle schrijfwijzen vertegenwoordigen.

Voorkom schade door vervangen

SUBSTITUEREN, Zoeken en vervangen en SQL REPLACE kunnen tekst binnen een langere waarde vervangen. Als je overal “NY” vervangt, verandert “Store NYC, NY” in “Store New YorkC, New York”. Vergelijk bij categorielabels de volledige celinhoud of gebruik een omzettabel. Onderzoek bij adressen en namen de bedoelde reikwijdte van elke vervanging.

Voor de steden “Lisbon”, “lisbon ”, “Porto”, een ontbrekende waarde, “LISBON” en “Porto” levert UPPER(TRIM(city)) drie keer LISBON en twee keer PORTO op, met één ontbrekende waarde. Analyseer de opgeschoonde kolom en gebruik die vervolgens voor vergelijkingen en duplicaatcontroles. Als je de hulpkolom bewaart maar op de ruwe stad groepeert, blijft het oorspronkelijke probleem bestaan.

Een gecombineerde tekstwaarde kan informatie verbergen die nodig is om te sorteren of vergelijken. Zet elk vereist deel in een eigen kolom en bewaar het origineel totdat de splitsing is gecontroleerd.

Splits op een betrouwbaar scheidingsteken

Een scheidingsteken markeert de grens tussen delen. Een komma in “Silva, Ana” scheidt de achternaam van de voornaam. Met Tekst naar kolommen in Excel en Tekst splitsen in kolommen in Google Sheets kun je dat scheidingsteken kiezen. Voeg vóór het splitsen rechts lege kolommen in, zodat de uitvoer geen andere velden overschrijft.

In Google Sheets kun je =SPLIT(A2;",") gebruiken; in huidige versies van Excel kun je =TEKST.SPLITSEN(A2;",") gebruiken. Verwijder overbodige spaties uit de teruggegeven delen als na de komma een spatie staat. Controleer rijen met een ontbrekend scheidingsteken of meer dan één scheidingsteken voordat je één regel op de hele kolom toepast.

Splits niet elke naam op een spatie. “Ana Maria Silva” heeft meer delen dan een naam van twee woorden en een spatie alleen maakt niet duidelijk welke woorden bij een voor- of achternaam horen. Gebruik een vastgelegde bronstructuur of behoud een veld met de volledige naam als de structuur onzeker is.

Haal tekens op positie uit tekst bij een vaste indeling

Voor productcode “PT-LIS-0042” staat het land op posities 1–2, de locatie op posities 4–6 en de laatste code op posities 8–11.

Het tellen van posities begint bij één. Bewaar de uitgelezen code als tekst zodat de voorloopnullen behouden blijven. Vaste posities werken alleen als de veldindeling consistent is.

Zoek bij tekst met wisselende lengte het scheidingsteken. In “Silva, Ana” geeft VIND.ALLES(",";A2) 6 terug. =LINKS(A2;VIND.ALLES(",";A2)-1) neemt de vijf tekens vóór de komma. Door de aftrekking valt de komma zelf buiten het resultaat. FIND is hoofdlettergevoelig; SEARCH negeert hoofdletterverschillen bij het zoeken naar letters.

Gebruik de equivalente SQL-functie

SUBSTRING(code, 4, 3) haalt de locatiecode in het midden uit de tekst in PostgreSQL; sommige databases gebruiken SUBSTR. Ook tekenlengtefuncties verschillen: PostgreSQL en SQLite gebruiken LENGTH, MySQL gebruikt CHAR_LENGTH en SQL Server gebruikt LEN, dat spaties aan het eind negeert. MySQL LENGTH telt bytes en kan daarom afwijken van het aantal tekens bij tekst zoals “é”.

Een controle in PostgreSQL met WHERE LENGTH(code) <> 11 vindt codes met een verkeerde lengte. Een lengtecontrole alleen bewijst niet dat de letters, cijfers en streepjes op de juiste plaatsen staan.

Voeg delen samen met een duidelijk scheidingsteken

=A2&" "&B2 of =TEKST.SAMENVOEGEN(A2;" ";B2) voegt twee spreadsheetvelden samen met een spatie. =TEKST.COMBINEREN(" ";WAAR;A2:C2) voegt een bereik samen en slaat lege cellen over. TEKST.SAMENV in Excel accepteert meerdere argumenten; TEKST.SAMENV in Google Sheets accepteert er precies twee.

In PostgreSQL voegt first_name || ' ' || last_name tekst samen. Bij deze operator maakt een NULL-deel het resultaat NULL. TEKST.SAMENV gaat in verschillende dialecten anders om met NULL: MySQL geeft NULL door, terwijl PostgreSQL en SQL Server het NULL-deel overslaan. Behandel optionele delen bewust met COALESCE en controleer de scheidingstekens in het resultaat.

Een consistente weergave is alleen bruikbaar als de waarden hetzelfde betekenen. Datums vereisen een bekende volgorde, getallen vereisen bekende scheidingstekens en metingen vereisen één eenheid voordat berekeningen beginnen.

Stel de bronconventie vast

De tekst “03/04/2025” kan 4 maart of 3 april betekenen. Een datum zoals “25/03/2025” bevestigt voor die invoer dat de dag voorop staat, maar een gecombineerd bestand kan meerdere notaties bevatten. Ga de conventie na bij de gegevenseigenaar of in de exportinstellingen en controleer op gemengde bronnen voordat je dubbelzinnige invoer omzet.

Gebruik voor duidelijke uitwisseling de ISO 8601-datumvolgorde JJJJ-MM-DD: “2025-03-04” staat voor 4 maart. Uniforme ISO-datumtekst sorteert ook chronologisch. Gebruik voor tijden de 24-uursnotatie, zoals “14:30”, en leg een tijdzone vast wanneer gebeurtenissen in verschillende tijdzones plaatsvinden.

Onderscheid opgeslagen waarden van weergave

Spreadsheets slaan echte datums op als seriële datumgetallen. In het standaarddatumsysteem van Excel en in Google Sheets is 1 januari 2025 gelijk aan 45658 en 4 maart 2025 aan 45720. Als je die numerieke datum opmaakt als JJJJ-MM-DD, verandert de weergave; de vastgelegde dag verandert niet.

Tekst die op een datum lijkt, is anders. Een datumnotatie toepassen op tekst maakt er geen echte datumwaarde van. Controleer het type en zet de tekst om met de bevestigde bronindeling voordat je die opmaakt. Uitlijning kan een aanwijzing zijn, maar typecontroles en het gedrag bij berekeningen geven sterker bewijs, omdat uitlijning handmatig kan worden aangepast.

Getalopmaak werkt op dezelfde manier. Een opgeslagen waarde van 0,125 kan met twee decimalen als 0,13 worden weergegeven, terwijl berekeningen nog steeds 0,125 gebruiken. =AFRONDEN(A2;2) verandert het numerieke resultaat naar twee decimalen. Behoud de volledige precisie tijdens het opschonen, tenzij de velddefinitie een afgeronde waarde vereist: drie derden die afzonderlijk zijn afgerond op 0,33 hebben samen een som van 0,99.

Lees getalscheidingstekens correct

“1,234.5” en “1.234,5” drukken onder verschillende conventies hetzelfde getal uit. Importeren met de verkeerde landinstelling kan de grootte veranderen of een waarde als tekst laten staan. Vergelijk meerdere geïmporteerde waarden met hun bron, inclusief waarden met decimale en duizendtalscheidingstekens.

In Excel noemt =NUMBERVALUE("1.234,5",",",".") expliciet de decimale komma en de punt als duizendtalscheidingsteken en geeft 1.234,5 terug. Stel in andere hulpmiddelen de landinstelling voor het importeren correct in. Verwijder niet elke komma zonder de rol ervan te controleren: een komma kan duizendtallen scheiden of het decimale deel aangeven.

Reken eenheden om voordat je waarden combineert

Een gewichtskolom met “2.5 kg”, “750 g” en “1.2 kg” mengt labels en eenheden. Haal het getal en de eenheid uit de tekst, reken gram om naar kilogram en sla 2,5, 0,75 en 1,2 op in een numerieke kolom weight_kg.

Als je de ruwe numerieke delen optelt, krijg je 753,7, wat geen consistente eenheid heeft. Zet één eenheid in de kolomkop en getallen in de cellen. Een geldkolom heeft eveneens één valuta nodig voordat totalen of gemiddelden een gemeenschappelijke betekenis hebben; leg elke gebruikte omrekenregel vast.

Onderzoek na het omrekenen de minimum- en maximumdatums en -metingen. Controleer afzonderlijke dubbelzinnige datums aan de hand van de bron, ook als ze binnen de toegestane periode vallen: als je maand en dag verwisselt, kan een geldige maar onjuiste datum ontstaan.

Een datatype vertelt software hoe een waarde moet worden behandeld. Met numerieke hoeveelheden kun je rekenen, met datums kun je op datum sorteren en verschillen berekenen, tekst bevat labels en identificatiecodes en booleaanse velden bevatten TRUE of FALSE. Bij het importeren worden bedoelde getallen of datums vaak tekst.

Stel het type vast voordat je iets verandert

Numerieke tekst kan standaard links zijn uitgelijnd, een Excel-waarschuwing tonen of “10” vóór “9” sorteren. Controleer de hele kolom met AANTAL en AANTALARG. In een bereik waarnaar wordt verwezen en dat het numerieke getal 120, de tekst “80” en het numerieke getal 45 bevat, geeft SOM 165 en AANTAL 2. Het bedoelde totaal is 245, dus de typefout beïnvloedt de berekening.

Het weergaveformaat veranderen naar Getal zet bestaande tekst niet om. Maak een hulpkolom, zet daarin de waarde om en vergelijk het resultaat met de ruwe invoer.

Zet tekst in een spreadsheet om

=WAARDE(A2) leest numerieke tekst volgens de landinstelling van de spreadsheet. =DATUMWAARDE(A2) leest datumtekst volgens de herkende datumconventies. Ga eerst de scheidingstekens en datumvolgorde na.

Bouw voor een bekende waarde met de dag voorop, “04.03.2025”, een datum uit de delen op. Expliciete VALUE-aanroepen maken de invoer numeriek in zowel Excel als Google Sheets:

=DATUM(WAARDE(RECHTS(A2;4));WAARDE(DEEL(A2;4;2));WAARDE(LINKS(A2;2)))

RIGHT geeft de jaartekst “2025” terug, MID de maandtekst “03” en LEFT de dagtekst “04”. VALUE zet elk deel om; DATE bouwt 4 maart 2025 op. Valideer eerst de delen: DATE kan een onmogelijke dag of maand naar een latere datum doorschuiven in plaats van een fout te melden.

Verwijder bij een waarde zoals “ 1250 kg” vóór de omzetting het bekende eenheidslabel en de spaties: =WAARDE(SPATIES.WISSEN(SUBSTITUEREN(A2;"kg";""))). Verwijder niet willekeurig letters uit elk veld, want letters kunnen betekenis hebben of op ongeldige invoer wijzen. =ALS.FOUT(WAARDE(A2);"check") houdt mislukte omzettingen zichtbaar voor controle.

Bewaar identificatiecodes als tekst

De code “00427” identificeert iets; het is geen hoeveelheid om op te tellen. Omzetten naar een getal geeft 427 en laat de voorloopnullen verloren gaan. Telefoonnummers, veel postcodes en rekeningidentificaties horen ook thuis in tekstkolommen. Stel het type in vóór het importeren. Excel bewaart bij numerieke opslag slechts 15 significante cijfers, dus een identificatiecode met 16 cijfers kan informatie verliezen voordat je het merkt. Die achteraf als tekst opmaken kan de verloren cijfers niet herstellen.

Zet om met SQL en onderzoek fouten

In databases zoals PostgreSQL leest CAST(amount_text AS DECIMAL(10,2)) numerieke tekst en geeft een decimaal getal met twee decimalen terug. CAST(quantity_text AS INTEGER) vraagt om een geheel getal. ISO-tekst kan in veel dialecten naar DATE worden omgezet; andere datumindelingen vereisen een passende parser zoals PostgreSQL TO_DATE, MySQL STR_TO_DATE of BigQuery PARSE_DATE.

Ongeldige tekst stopt vaak een CAST-query. TRY_CAST in SQL Server en SAFE_CAST in BigQuery geven bij gewone omzettingen die mislukken in plaats daarvan NULL terug. Tel deze fouten afzonderlijk van waarden die al ontbraken. Bijvoorbeeld in SQL Server:

SELECT amount_text FROM sales
WHERE TRY_CAST(amount_text AS DECIMAL(10,2)) IS NULL
  AND amount_text IS NOT NULL;

SQLite kan “12abc” zonder melding omzetten in 12 en “abc” in 0. Een geslaagde query is daarom onvoldoende bewijs voor een correcte omzetting. Controleer de oorspronkelijke tekenreeksen, omgezette waarden, aantallen en bereiken voordat je de opgeschoonde kolom accepteert.

Een opschoningsprocedure in een spreadsheet maakt elke verandering zichtbaar en bewaart voldoende bewijs om die te herhalen. Gebruik een ruw werkblad, een werkblad met hulpkolommen en een gecontroleerde opgeschoonde tabel.

Richt het werk in en bepaal de volgorde

Houd ruwe gegevens ongewijzigd. Zet de koprij vast en gebruik een tabel of filters om het volledige gegevensbereik te selecteren. Verplaats bij het sorteren hele rijen samen, zodat namen, ID's en bedragen gekoppeld blijven.

Een praktische volgorde is:

1. Profileer de tabel en schrijf een probleemlijst.
2. Verbeter koppen en splits velden die meerdere vereiste delen bevatten.
3. Verwijder overbodige spaties, standaardiseer hoofdlettergebruik en zet categorievarianten om.
4. Zet typen om en maak datum-, getal- en eenheidsnotaties consistent.
5. Verwerk ontbrekende waarden en behoud waar nodig markeringen.
6. Verwijder duplicaten met de afgesproken sleutel en bewaarregel.
7. Valideer, voer een aansluitingscontrole uit en controleer het resultaat steekproefsgewijs.

Deze volgorde houdt rekening met afhankelijkheden. Een duplicaatcontrole op ruwe sleutels kan extra spaties missen. Een bereikcontrole op numerieke tekst toetst mogelijk niet de bedoelde waarden. Leg na elke stap het effect vast, zodat je een latere afwijking tot de oorzaak kunt herleiden.

Maak en onderzoek hulpkolommen

Als B2 “ ana SILVA” bevat, geeft =BEGINLETTERS(SPATIES.WISSEN(B2)) “Ana Silva” terug. Trek de formule door over de hele tabel en vergelijk ruwe en opgeschoonde namen. Controleer schrijfwijzen die PROPER niet correct kan afleiden.

Een hulpkolom met bijvoorbeeld =HOOFDLETTERS(SPATIES.WISSEN(SUBSTITUEREN(B2;TEKEN(160);" "))) werkt van binnen naar buiten: vervang vaste spaties, verwijder overbodige gewone spaties en verander vervolgens het hoofdlettergebruik. Gebruik beschrijvende hulpkolomkoppen zoals city_clean, zodat latere formules naar de bedoelde kolom verwijzen.

Filter resultaten en controleer waarden aan het begin, in het midden en aan het eind van het bereik. Een formule die slechts over een deel van de tabel is gekopieerd, laat een gemengde kolom achter. Zoek zowel naar fouten als naar lege waarden.

Zet categorieën om met een exacte opzoekbewerking

Houd een omzettabel met twee kolommen bij: variant en standaardlabel. Met een voorbereide variant in B2 vindt =VERT.ZOEKEN(B2;Map!$A$2:$B$50;2;ONWAAR) het overeenkomende label. De absolute celverwijzingen houden het omzetbereik vast bij het doortrekken. FALSE vraagt om een exacte overeenkomst; een benaderende overeenkomst kan voor een niet-vermelde waarde een aannemelijke maar onjuiste categorie teruggeven.

X.ZOEKEN kan afzonderlijke zoek- en resultaatbereiken gebruiken. Standaard wordt een exacte overeenkomst gezocht. Een variant zonder overeenkomst moet zichtbaar blijven als fout voor controle. Voeg de omzetting toe of verbeter die in plaats van elke mislukte opzoekbewerking om te zetten in een bekende categorie.

Rond af en documenteer

Kopieer na controle van de hulpkolommen de resultaten en gebruik Plakken speciaal > Waarden in Excel of Plakken speciaal > Alleen waarden in Google Sheets om de opgeschoonde tabel te maken. Controleer of de tabel onafhankelijk is voordat je bronkolommen verwijdert: formules die nog naar verwijderde cellen verwijzen, kunnen #REF! teruggeven.

Ingebouwde hulpmiddelen zoals Duplicaten verwijderen, Zoeken en vervangen en Tekst naar kolommen kunnen veel cellen tegelijk veranderen. Leg de opties, het geselecteerde bereik en het betrokken aantal vast terwijl die informatie zichtbaar is. Hulpkolommen met formules tonen hun logica direct; bewaar dat werkblad bij het logboek.

De Engelse formules gebruiken hier komma's. De taal en landinstelling van de spreadsheet kunnen functienamen en scheidingstekens veranderen; zo gebruikt Nederlandse Excel SPATIES.WISSEN voor TRIM en gebruiken landinstellingen met een decimale komma vaak puntkomma's tussen argumenten.

Excel staat 1.048.576 rijen en 16.384 kolommen per werkblad toe; Google Sheets begrenst het aantal cellen per bestand. De prestaties kunnen al ruim vóór een harde grens een probleem worden. Gegevens die vaak worden ververst of al in een database staan, passen vaak beter bij een opgeslagen SQL-procedure dan bij herhaalde handmatige wijzigingen in een spreadsheet.

Een SQL-opschoningsquery kan bruikbare gegevens teruggeven zonder de ruwe tabel te veranderen. Elke expressie maakt een regel expliciet en een alias geeft de opgeschoonde uitvoer een naam.

Bouw het resultaat kolom voor kolom op

De volgende query is geschikt voor PostgreSQL wanneer bedrag- en datumtekst al door omzettingscontroles zijn gekomen en quantity numeriek is:

SELECT DISTINCT
  order_id,
  LOWER(TRIM(email)) AS email,
  COALESCE(NULLIF(UPPER(TRIM(city)), ''), 'UNKNOWN') AS city,
  CAST(amount AS DECIMAL(10,2)) AS amount,
  CAST(order_date AS DATE) AS order_date
FROM raw_orders
WHERE order_id IS NOT NULL
  AND quantity > 0;

Uit e-mailadressen worden overbodige spaties verwijderd en ze worden omgezet in kleine letters. Voor stad verwijdert TRIM spaties aan de uiteinden, standaardiseert UPPER het hoofdlettergebruik, zet NULLIF lege tekst om in NULL en levert COALESCE “UNKNOWN”. De omzettingen geven numerieke bedragen en datums terug. WHERE sluit ontbrekende ID's en aantallen uit die niet positief of NULL zijn. DISTINCT verwijdert herhaalde geselecteerde resultaatrijen.

Profileer de uitvoer na elke toegevoegde expressie. Een ingewikkelde query kan met succes worden uitgevoerd terwijl een omzetting of typeconversie onjuist is.

Begrijp de filtervolgorde

De bruikbare logische volgorde is FROM, WHERE, SELECT, DISTINCT, ORDER BY. WHERE ziet bronkolommen voordat de SELECT-expressies hun aliassen maken. Een alias maakt een ruwe kolom niet schoon binnen de WHERE-clausule van dezelfde query.

Gebruik WHERE UPPER(TRIM(city)) = 'LISBON' om een ruwe stad consistent te filteren, of sla het opgeschoonde resultaat op en filter dat resultaat in een latere query. Als je de ruwe stad filtert met city = 'LISBON', kun je varianten missen, ook als SELECT de weergegeven stad opschoont.

CASE controleert voorwaarden in volgorde en geeft het eerste overeenkomende resultaat terug:

CASE
  WHEN LOWER(TRIM(status)) IN ('shipped', 'sent') THEN 'shipped'
  WHEN status IS NULL THEN 'unknown'
  ELSE LOWER(TRIM(status))
END AS status

Zonder ELSE wordt een waarde zonder overeenkomst NULL. Houd niet-omgezette statussen zichtbaar totdat hun betekenis is opgehelderd.

Kies tussen filteren en markeren

WHERE verwijdert een rij uit het resultaat. Een markering behoudt de rij en geeft een probleem aan, bijvoorbeeld CASE WHEN delivery_date < order_date THEN 1 ELSE 0 END AS date_issue. Controleer verplichte datums afzonderlijk op NULL.

Tel uitgesloten rijen expliciet:

SELECT COUNT(*) FROM raw_orders
WHERE order_id IS NULL
   OR quantity <= 0
   OR quantity IS NULL;

Dit omvat elke reden waarom het eerdere filter een rij afwijst, inclusief ontbrekende aantallen. Controleer na DISTINCT de bedrijfssleutel met GROUP BY en HAVING. Rijen met dezelfde ID maar verschillende bedragen blijven na DISTINCT staan en moeten nog worden afgehandeld.

Sla de methode op en kies het juiste dialect

CREATE VIEW clean_orders AS SELECT ... slaat een query op die telkens de huidige brongegevens leest wanneer de view wordt gebruikt. CREATE TABLE clean_orders AS SELECT ... maakt in veel dialecten een opgeslagen resultaat; SQL Server gebruikt doorgaans SELECT INTO. Bewaar de query en het commentaar zodat je dat resultaat opnieuw kunt opbouwen wanneer de invoer verandert.

Functienamen en gedrag verschillen tussen SQL-systemen. Controleer het dialect voor het lezen van datums, tekenlengte en veilige omzetting. UPDATE en DELETE veranderen opgeslagen records: bekijk dezelfde WHERE-voorwaarde vooraf met SELECT en zorg voor een back-up voordat je een van beide toepast. Zonder WHERE beïnvloeden ze elke rij. Opschonen via SELECT is een bruikbare standaard voor analisten met alleen leestoegang.

Een opgeschoonde tabel heeft een uitleg nodig van hoe die is gemaakt. Documentatie laat iemand anders een gerapporteerde waarde herleiden, een beslissing beoordelen en het werk bij de volgende levering opnieuw uitvoeren.

Schrijf een bruikbare vermelding in het wijzigingslogboek

Een wijzigingslogboek is een gedateerd overzicht van wijzigingen. Elke vermelding moet het volgende aangeven:

• De datum en auteur.
• De dataset en de betrokken kolom of het betrokken bereik.
• Het probleem en de oorspronkelijke omvang ervan.
• De actie, inclusief formule, query of opties van het hulpmiddel.
• Het aantal betrokken rijen of waarden.
• De reden en elke bron die is gebruikt om een correctie te bevestigen.

Bijvoorbeeld: “2025-03-10, A. Silva, orders.city: bij 412 waarden overbodige spaties verwijderd en met UPPER(TRIM(city)) omgezet in hoofdletters; 9 schrijfwijzen teruggebracht tot 4 steden; nodig voor omzet per stad.”

Die vermelding legt zowel de methode als het doel uit. “Steden opgeschoond” geeft de vergelijkingsregel, reikwijdte of het aantal niet aan. Schrijf vermeldingen tijdens het werk, zodat de melding van het hulpmiddel over het aantal betrokken rijen en de reden voor een beslissing nog beschikbaar zijn.

Leg zowel beslissingen als handelingen vast

Een formule verklaart niet waarom die geschikt was. Noem bij duplicaten de sleutelkolommen en welk record is blijven staan. Vermeld bij ontbrekende bedragen of ze zijn hersteld, uitgesloten, ingevuld of als ontbrekend zijn gelaten. Als je waarden hebt ingevuld, leg dan de schattingsregel en markeringskolom vast. Geef bij een correctie aan welke factuur of welk bronrecord de werkelijke waarde leverde.

Leg ook gemarkeerde maar behouden rijen vast. Een beoordelaar moet een bekend probleem dat je zichtbaar hebt gehouden kunnen onderscheiden van een probleem dat de opschoning heeft gemist. Als een behandeling later verandert, voeg dan een gedateerde vermelding toe die de nieuwe beslissing uitlegt.

Bewaar de methode en haar geschiedenis

Celnotities in een spreadsheet kunnen één correctie toelichten, terwijl een werkblad Wijzigingslogboek de volledige volgorde bevat. Met de versiegeschiedenis van Google Sheets kun je eerdere bestandsversies herstellen. Versiegeschiedenis van Excel is beschikbaar voor bestanden die in OneDrive of SharePoint zijn opgeslagen. Geschiedenis helpt bij het herstellen van een bestand, maar een afzonderlijk logboek legt de bedoeling van de wijzigingen uit.

SQL-commentaar kan een regel naast de bijbehorende expressie uitleggen. Gebruik -- voor één regel of /* ... */ voor langer commentaar. Bewaar queryversies in versiebeheer of opslag met versiegeschiedenis. Een gewone gedeelde map maakt de huidige query toegankelijk maar legt eerdere versies niet vast.

Maak herhaling mogelijk

Een proces is reproduceerbaar wanneer dezelfde ruwe invoer en regels dezelfde opgeschoonde uitvoer opleveren. Opgeslagen query's en bewaarde formules in hulpkolommen maken hun regels zichtbaar. Voor een handmatige celwijziging zijn de record-ID, oude waarde, nieuwe waarde en reden nodig als iemand die nauwkeurig moet kunnen herhalen.

Bewaar de ruwe kopie, omzettingsstappen, omzettabellen en het logboek bij elkaar. Als een regel afhangt van een bewaarvolgorde, zoals het behouden van het nieuwste klantrecord, sla dan ook de sorteer- of selectieregel op. Om een resultaat te reproduceren zijn zowel de beslissingen als de software-expressies nodig.

Deel het logboek en een kort opschoningsverslag met de mensen die de gegevens gebruiken. Iemand die resultaten interpreteert, moet weten dat testbestellingen zijn verwijderd of dat voor een deel van het klantenbestand leeftijden ontbreken. Meld terugkerende bronfouten aan de gegevenseigenaar, zodat die het formulier, de import of export kan verbeteren die ze veroorzaakt.

Verificatie onderzoekt of de opschoning de problemen heeft opgelost zonder nieuwe te veroorzaken. Verificatie controleert ook of de resterende gegevens de oorspronkelijke vraag nog steeds beantwoorden. Gebruik toetsen over hele kolommen en vergelijkingen en onderzoek daarna afzonderlijke records.

Sluit rijaantallen stap voor stap aan

Begin met het ruwe rijaantal. Trek de rijen af die in elke fase zijn verwijderd en tel elke verwijdering één keer. Voor 1.250 ruwe rijen, 37 verwijderde dubbele kopieën en 12 later uitgesloten ongeldige rijen:

Het verwachte opgeschoonde aantal is 1.201. Een ander aantal vereist een verklaring vóór vrijgave. Controleer de stap waarin het aantal voor het eerst afweek. Mogelijk heeft een filter rijen verborgen voor een spreadsheetbewerking of liet een geselecteerd bereik een deel van de tabel weg.

Als één rij meerdere problemen heeft, trek die dan niet meerdere keren af. Gebruik het werkelijke rijaantal vóór en na elke bewerking. Houd afzonderlijke aantallen bij voor verwijderde rijen en gecorrigeerde cellen: tien stadscellen veranderen verwijdert geen tien rijen.

Herhaal de profilering en regels

Voer de oorspronkelijke controles uit op de opgeschoonde uitvoer. Vergelijk elk resultaat met de beslissing die in het logboek is vastgelegd:

• Aantallen ontbrekende waarden komen overeen met de waarden die zijn blijven ontbreken, hersteld of ingevuld.
• Categorielijsten hebben de bedoelde standaardschrijfwijzen.
• Numerieke en datumminima en -maxima zijn geldig.
• De sleutel heeft geen onopgeloste duplicaten.
• Type-, aanwezigheids-, bereik- en consistentiecontroles hebben geen onverklaarde fouten.

Nul lege waarden na het invullen betekent niet dat er geen waarden ontbraken. Controleer ook de markeringen. Een herhaalde bedrijfssleutel kan na DISTINCT blijven bestaan wanneer één geselecteerd veld verschilt, dus controleer die sleutel direct.

Sluit bedragen aan en onderzoek records

Wanneer geen waarden zijn gecorrigeerd of ingevuld, moet het opgeschoonde totaal gelijk zijn aan het ruwe totaal min de bedragen in verwijderde rijen. Een ruw totaal van 152.400 min 3.100 aan verwijderde dubbele bestellingen moet 149.300 opleveren. Als waarden zijn gecorrigeerd of ingevuld, neem die wijzigingen dan afzonderlijk op in de aansluitingscontrole.

Vergelijk records uit verschillende delen van de tabel met de ruwe gegevens en waar mogelijk met de oorspronkelijke bron. Richt je op de rijen die zijn geraakt door stappen met risico op fouten: vervangen, splitsen, inlezen en typeconversie. Onderzoek namen op te ruime vervangingen, kolommen op verschoven uitvoer van splitsingen, identificatiecodes op verloren nullen en datums op verwisselde dag en maand. Een aannemelijke datum kan nog steeds de verkeerde datum zijn.

Controleer de dekking van de vraag

Zorg dat de opgeschoonde gegevens nog steeds de vereiste periode, regio's, winkels, producten en velden omvatten. Als je alle bestellingen van een winkel laat vallen omdat de datums een andere indeling gebruiken, verandert de populatie die wordt geanalyseerd. Verbeter waar mogelijk de leesregel en rapporteer welke dekking nog ontbreekt.

Schrijf een kort verslag met bron en periode, problemen en aantallen, uitgevoerde acties, uitsluitingen of ingevulde waarden, verificatieresultaten en resterende beperkingen. Bijvoorbeeld: “Voor 12% van de klanten ontbreekt de leeftijd; resultaten naar leeftijd beschrijven de overige 88%.” Een resultaat moet worden geïnterpreteerd op basis van die dekking.

Voer zowel de opschoning als de verificatie opnieuw uit wanneer nieuwe ruwe gegevens binnenkomen. Opgeslagen regels maken het proces herhaalbaar, maar nieuwe categorieën, veranderde notaties en nieuwe patronen van ontbrekende waarden vereisen nog steeds controle.