Gegevens opschonen in Excel: een beginnersgids

Basisgegevens opschonen in Excel

Verwijder extra spaties

Achterliggende spaties kunnen vervelend zijn en voor problemen zorgen, omdat ze tot inconsistenties in data-analyse en opmaak leiden.

Er zijn twee belangrijke manieren om extra spaties in Excel te verwijderen. We bekijken ze allebei.

Extra spaties verwijderen met Zoeken en vervangen

Volg deze stappen om extra spaties te verwijderen.

• Selecteer het celbereik waarin je extra spaties wilt verwijderen.

• Gebruik de sneltoets Ctrl + H om het dialoogvenster Zoeken en vervangen te openen.

• Druk in het veld Zoeken naar twee keer op de spatiebalk om twee spaties in te voeren.

• Druk in het veld Vervangen door één keer op de spatiebalk om één spatie in te voeren.

• Klik op Alles vervangen.

• Herhaal dit totdat er geen dubbele spaties meer worden gevonden.

Zoek- en vervangdialoogvenster. Bron: afbeelding door auteur

Extra spaties verwijderen met TRIM

• Maak een nieuwe kolom voor de opgeschoonde data.

• Voer in de eerste cel van de nieuwe kolom de formule =TRIM(cel_met_extra_spaties) in.

• Dubbelklik op de rechterbenedenhoek van de cel om de formule toe te passen op de rest van de rijen.

• Kopieer de opgeschoonde data en plak als waarden om de formules te verwijderen.

Alle lege cellen selecteren en behandelen

Lege cellen kunnen formules doorbreken, tot rekenfouten leiden en onnauwkeurige analyseresultaten opleveren. Zo pak je dit aan.

• Markeer het celbereik waarin je lege cellen wilt vinden en behandelen.

• Druk op Ctrl + G om het dialoogvenster Ga naar te openen.

• Klik op de knop „Speciaal…”. Hiermee open je het dialoogvenster Ga naar speciaal.

• Selecteer de optie Lege cellen en klik op OK. Nu zijn alle lege cellen in het gekozen bereik geselecteerd.

• Voer nu een waarde of formule in. Druk op Ctrl + Enter.

Lege cellen selecteren en behandelen. Bron: afbeelding door auteur

Spellingscontrole

Spelfouten laten data onprofessioneel ogen en maken die lastiger te begrijpen, dus het is belangrijk om ze te corrigeren.

• Controleer het hele werkblad of een specifiek bereik.

• Ga naar het tabblad Controleren op het Lint.

• Klik op de knop Spelling in de groep Controle.

• Het dialoogvenster Spelling opent met het eerste gedetecteerde fout gespelde woord en suggesties voor correctie.

• Bekijk en corrigeer spelfouten waar nodig.

Spellingscontrole. Bron: afbeelding door auteur

Foutherstel en validatie in Excel

Laten we kijken naar foutherstel, zoals fouten markeren of dubbele waarden verwijderen.

Fouten markeren

Fouten in je data kunnen tot verkeerde resultaten en slechte besluitvorming leiden, dus het is cruciaal om ze te identificeren en aanpakken. Zo doe je dat.

• Selecteer het celbereik dat je op fouten wilt controleren.

• Ga naar het tabblad Start op het Lint.

• Klik in de groep Stijlen op Voorwaardelijke opmaak.

• Selecteer Nieuwe regel in het dropdownmenu.

• Kies Een formule gebruiken om te bepalen welke cellen worden opgemaakt.

• Voer de formule =ISERROR(cel) in het veld Waarden opmaken waarvoor deze formule waar is in.

• Klik op de knop Opmaak… om je gewenste opmaakopties te kiezen.

• Klik op OK nadat je de opmaak hebt gekozen.

• Klik nogmaals op OK in het dialoogvenster Nieuwe opmaakregel om de regel toe te passen.

Voorwaardelijke opmaak gebruiken. Bron: afbeelding door auteur

Dubbele waarden verwijderen

Dubbele records kunnen analyses en inzichten verstoren, dus door ze te verwijderen verbeter je de nauwkeurigheid van je data.

• Selecteer het werkblad of het celbereik waaruit je dubbele waarden wilt verwijderen.

• Ga naar het tabblad Gegevens op het Lint.

• Klik op Dubbele waarden verwijderen in de groep Hulpmiddelen voor gegevens.

• Geef in het dialoogvenster Dubbele waarden verwijderen aan welke kolom(men) je op duplicaten wilt controleren.

• Klik op OK. Excel toont een bericht met het aantal verwijderde dubbele waarden.

Dubbele rijen verwijderen

Dubbele rijen maken je data rommelig en beïnvloeden de betrouwbaarheid van je analyse. Zo pak je dit aan.

• Selecteer het bereik waaruit je de dubbele rijen wilt verwijderen.

• Ga naar het tabblad Gegevens op het Lint en klik op Geavanceerd in de groep Sorteren en filteren.

• Selecteer in het dialoogvenster Geavanceerd filter de optie Kopiëren naar een andere locatie.

• Stel het Lijstbereik in op je geselecteerde bereik.

• Stel het veld Kopiëren naar in op de cel waar je de unieke rijen wilt laten plaatsen.

• Vink het vakje Alleen unieke records aan en klik op OK.

Tekstbewerkingen in Excel

Laten we kijken naar tekstbewerkingen zoals strings samenvoegen.

Samenvoegen

Meerdere tekststrings in één cel samenvoegen helpt je om informatie uit verschillende bronnen te combineren tot één samenhangend formaat.

• Selecteer de cel waarin je het samengevoegde resultaat wilt tonen.
• Typ =CONCATENATE().
• Selecteer de cellen die je wilt samenvoegen, gescheiden door komma’s of de &-operator.
• Druk op Enter om het resultaat te zien.

Cellen samenvoegen. Bron: afbeelding door auteur

De lettergrootte van tekst wijzigen

Uniforme schrijfwijze in hoofd-/kleine letters verbetert de leesbaarheid en houdt je data consistent. Zo pas je dit aan.

• Maak een nieuwe kolom voor de geconverteerde tekst.

• Voer in een cel de formule in: =UPPER() of =LOWER() of =PROPER()

• Druk op Enter om de formule toe te passen.

• Sleep de vulgreep om de formule indien nodig op andere cellen toe te passen.

Hoofd-/kleine letters wijzigen. Bron: afbeelding door auteur

Niet-afdrukbare tekens uit tekst verwijderen

Niet-afdrukbare tekens, zoals tabs, regeleindes en speciale tekens, kunnen de gegevensverwerking verstoren en het moeilijk maken om data correct te analyseren of visualiseren.

• Identificeer de niet-afdrukbare tekens die je wilt verwijderen.

• Selecteer de cel met de tekst waarin niet-afdrukbare tekens staan.

• Gebruik in een nieuwe cel de volgende functie: =CLEAN(tekst).

• Om extra spaties én niet-afdrukbare tekens te verwijderen, gebruik je: =TRIM(CLEAN(tekst)).

• Kopieer en plak de opgeschoonde tekst om het origineel te vervangen.

De CLEAN-functie gebruiken in Excel. Bron: afbeelding door auteur

Datatransformatie in Excel

Datatransformatietechnieken worden gebruikt om je data te herordenen en te herschikken voor betere analyses, inclusief het parseren van tekst en het gebruik van tools zoals Snel aanvullen (Flash Fill).

Data parseren van tekst naar kolom

Tekstdata opsplitsen in afzonderlijke kolommen maakt het eenvoudiger om specifieke onderdelen te analyseren en zorgt ervoor dat elk stukje informatie duidelijk wordt gecategoriseerd. Dit is het tegenovergestelde van samenvoegen en is handig om complexe data op te breken in beheersbare delen. Zo pak je het aan.

• Selecteer de data die je wilt splitsen.

• Ga naar het tabblad Gegevens en klik op Tekst naar kolommen.

• Kies Gescheiden of Vaste breedte en klik op Volgende.

• Voor Gescheiden vink je de scheidingstekens aan die je data gebruikt en klik je op Volgende.

• Voor Vaste breedte stel je kolombreuken in in het venster Gegevensvoorbeeld en klik je op Volgende.

• Kies het gegevensformaat voor elke kolom.

• Klik op Voltooien.

Tekst naar kolommen gebruiken voor parseren. Bron: afbeelding door auteur

Snel aanvullen (Flash Fill)

Snel aanvullen vult automatisch waarden in op basis van patronen die het in je data herkent. Zo gebruik je Snel aanvullen in Excel:

• Voer in een cel naast je bestaande data waarden in volgens een patroon.

• Geef in de volgende cel nog een voorbeeld om Excel te helpen het patroon te herkennen.

• Selecteer de cel met het voorbeeld.

• Ga naar het tabblad Gegevens op het Lint.

• Klik op Snel aanvullen in de groep Hulpmiddelen voor gegevens.

• Excel vult automatisch de overige cellen in op basis van het herkende patroon.

Kolommen samenvoegen en splitsen

Kolommen samenvoegen en splitsen helpt je data zo te organiseren dat die het beste bij je analysedoelen past. Zo voeg je cellen samen:

• Ga naar het tabblad Start en klik op het dropdownmenu Samenvoegen en centreren in de groep Uitlijning.
• Kies je gewenste samenvoegoptie.

Volg deze stappen om kolommen te splitsen:

• Ga naar het tabblad Gegevens en klik op Tekst naar kolommen in de groep Hulpmiddelen voor gegevens.
• Kies gescheiden of vaste breedte op basis van wat je data nodig heeft.
• Selecteer de bestemming voor de opgesplitste data en klik op Voltooien.

Kolommen en rijen transformeren en herschikken

Data herschikken kan helpen om deze logischer en toegankelijker te presenteren. Voor transformeren van rijen naar rijen en kolommen naar kolommen:

• Markeer de data die je wilt transformeren (inclusief kopteksten indien nodig).

• Klik met de rechtermuisknop op de selectie en kies Kopiëren of druk op Ctrl+C.

• Selecteer de cel waar de getransformeerde data moet beginnen.

• Klik met de rechtermuisknop op de doelcel, kies Plakken speciaal en selecteer vervolgens Transponeren.

• Je kunt ook Ctrl+Alt+V gebruiken om het dialoogvenster Plakken speciaal te openen, vink vervolgens Transponeren aan en klik op OK.

Om kolommen/rijen te herschikken:

• Klik op de kolomkop om de volledige kolom te selecteren die je wilt verplaatsen.

• Klik met de rechtermuisknop en kies Knippen of druk op Ctrl+X.

• Selecteer de kolom waar je de geknipte kolom wilt neerzetten, klik met de rechtermuisknop op de kolomkop en kies Geknipte cellen invoegen.

• Voor rijen werkt het vergelijkbaar: selecteer en knip de rij die je wilt verplaatsen en plak deze op de gewenste plek.

Numerieke en datumcorrecties in Excel

Numerieke en datumcorrecties houden in dat numerieke data en datums worden gecorrigeerd en gestandaardiseerd zodat ze nauwkeurig en consistent opgemaakt zijn.

Getallen en getekens corrigeren

Onjuiste getalformaten kunnen leiden tot misinterpretaties en rekenfouten, en tot problemen bij het sorteren en vergelijken van data.

• Selecteer de cellen met de getallen die je wilt corrigeren.
• Ga naar het tabblad Start, klik op het dropdownmenu Getal in de groep Getal en kies het juiste getalformaat (bijv. Standaard, Getal, Valuta).

Datums en tijd corrigeren

Correct opgemaakte datums zijn cruciaal voor nauwkeurige tijdsgebonden analyses en rapportage.

• Markeer de cellen met datums.

• Ga naar het tabblad Start.

• Klik op het dropdownmenu Getalnotatie en kies Korte datumnotatie of Lange datumnotatie.

Geavanceerd databeheer in Excel

Geavanceerd databeheer omvat het afstemmen en combineren van datasets om een volledige en samenhangende dataset voor analyse te creëren.

Tabelgegevens afstemmen door te joinen of te matchen

Data uit verschillende tabellen samenvoegen of matchen zorgt voor een volledige en samenhangende analyse.

VLOOKUP gebruiken om data te matchen:

• Zorg dat beide tabellen toegankelijk zijn op hetzelfde werkblad.

• Kies de cel waarin je de gematchte data wilt weergeven.

• Gebruik de volgende functie: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

• Sleep de vulgreep om de formule indien nodig naar andere cellen te kopiëren.

INDEX en MATCH gebruiken voor meer flexibiliteit

• Kies de cel waarin je de gematchte data wilt weergeven.

• Gebruik de combinatie van deze functies: =INDEX(array, MATCH(lookup_value, lookup_array, 0))

• Sleep de vulgreep om de formule indien nodig naar andere cellen te kopiëren.

Tot slot

Er zijn meerdere functies voor gegevensopschoning in Excel waarmee je data kunt opschonen en valideren om aan de vastgestelde standaarden te voldoen. Zo verklein je fouten en verbeter je de kwaliteit van je datasets. Wil je Excel’s mogelijkheden verder onder de knie krijgen, schrijf je dan in voor onze cursus Introduction to Excel.

Of je nu data opschoont door dubbele velden te verwijderen of invoerformaten te standaardiseren, Excel heeft alle tools die je nodig hebt om dit makkelijker te maken. Als je je vaardigheden in gegevensvoorbereiding wilt verbeteren, biedt onze cursus Data Preparation in Excel een uitgebreide behandeling van dit onderwerp.

Om je analytische vaardigheden verder te versterken, is onze cursus Data Analysis in Excel bijzonder nuttig. Deze cursus gaat in op de finesses van data-analyse na het opschonen. Ben je daarnaast geïnteresseerd in hoe schone data financiële forecasting ondersteunt, dan is onze cursus Financial Modeling in Excel wellicht interessant.

Wil je je vaardigheden in gegevensopschoning uitbreiden, overweeg dan Power Query, een flexibele en krachtige tool in Excel en Power BI waarmee je data naadloos kunt importeren en transformeren. Verken ook Data Cleaning in Python of Cleaning Data in R. Deze cursussen bieden robuuste technieken en best practices voor gegevensopschoning met populaire programmeertalen.