10 Supernette manieren om gegevens op te schonen in Excel-spreadsheets

Bekijk video - 10 manieren om gegevens op te schonen in Excel

Gegevens vormen de ruggengraat van elke analyse die u in Excel doet. En als het op gegevens aankomt, zijn er talloze dingen die fout kunnen gaan - of het nu gaat om de structuur, plaatsing, opmaak, extra spaties, enzovoort.

In deze blogpost laat ik je 10 eenvoudige manieren zien om gegevens in Excel op te schonen.

#1 Weg met extra spaties

Extra spaties zijn pijnlijk moeilijk te herkennen. Hoewel je op de een of andere manier de extra spaties tussen woorden of cijfers kunt zien, zijn volgspaties niet eens zichtbaar. Hier is een handige manier om van deze extra spaties af te komen: gebruik de TRIM-functie.

Syntaxis: TRIM(tekst)

Excel TRIM-functie neemt de celverwijzing (of tekst) als invoer. Het verwijdert voorloop- en volgspaties evenals de extra spaties tussen woorden (behalve enkele spaties).

#2 Selecteer en behandel alle lege cellen

Lege cellen kunnen schade aanrichten als ze niet vooraf worden behandeld. Ik heb vaak problemen met lege cellen in een dataset die wordt gebruikt om rapporten/dashboards te maken.

Misschien wilt u alle lege cellen vullen met '0' of 'Niet beschikbaar', of u wilt het misschien gewoon markeren. Als er een enorme dataset is, kan het uren duren om dit handmatig te doen. Gelukkig is er een manier om alle lege cellen tegelijk te selecteren.

1. Selecteer de volledige dataset
2. Druk op F5 (hiermee wordt het dialoogvenster Ga naar geopend)
3. Klik op de knop Speciaal… (linksonder). Dit opent het dialoogvenster Ga naar speciaal
   
4. Selecteer Leeg en klik op OK
   

Hiermee worden alle lege cellen in uw dataset geselecteerd. Als u in al deze cellen 0 of Niet beschikbaar wilt invoeren, typt u het gewoon en drukt u op Controle + Enter (onthoud dat als u alleen op enter drukt, de waarde alleen in de actieve cel wordt ingevoegd).

#3 Converteer getallen die als tekst zijn opgeslagen naar getallen

Soms, wanneer u gegevens importeert uit tekstbestanden of externe databases, worden getallen opgeslagen als tekst. Sommige mensen hebben ook de gewoonte om een ​​apostrof (') voor een nummer te gebruiken om er tekst van te maken. Dit kan ernstige problemen veroorzaken als u deze cellen in berekeningen gebruikt. Hier is een onfeilbare manier om deze getallen die als tekst zijn opgeslagen, weer om te zetten in getallen.

1. Typ in een lege cel 1
2. Selecteer de cel waarin je 1 hebt getypt en druk op Control + C
3. Selecteer de cel/het bereik dat u naar getallen wilt converteren
4. Selecteer Plakken -> Plakken speciaal (Sneltoets toetsenbord - Alt + E + S)
5. Selecteer in het vak Speciale dialoog plakken de optie Vermenigvuldigen (in de categorie bewerkingen)
   
6. Klik OK. Dit converteert alle getallen in tekstformaat terug naar getallen.

Er is veel meer dat u kunt doen met speciale bewerkingsopties voor plakken. Hier zijn verschillende andere manieren om in Excel te vermenigvuldigen met Plakken speciaal.

#4 - Duplicaten verwijderen

U kunt twee dingen doen met dubbele gegevens: Markeer het of Verwijder het.

• Markeer dubbele gegevens:
  • Selecteer de gegevens en ga naar Home -> Conditionele opmaak -> Markeer Cellen-regels -> Dubbele waarden.
  • Geef de opmaak op en alle dubbele waarden worden gemarkeerd.
    
• Duplicaten in gegevens verwijderen:
  • Selecteer de gegevens en ga naar Gegevens -> Duplicaten verwijderen.
  • Als uw gegevens headers hebben, zorg er dan voor dat het selectievakje rechtsboven is aangevinkt.
  • Selecteer de kolom(men) waaruit u duplicaten wilt verwijderen en klik op OK.
    

Hiermee worden dubbele waarden uit de lijst verwijderd. Als u wilt dat de originele lijst intact blijft, kopieert u de gegevens naar een andere locatie en doet u dit.

Verwant: De ultieme gids voor het zoeken en verwijderen van duplicaten in Excel.

#5 Markeer fouten

Er zijn 2 manieren om fouten in gegevens in Excel te markeren:

Voorwaardelijke opmaak gebruiken

1. Selecteer de volledige dataset
2. Ga naar Home -> Conditionele opmaak -> Nieuwe regel
3. Selecteer in het dialoogvenster Nieuwe opmaakregel 'Alleen cellen opmaken die bevatten'
4. Selecteer in de regelbeschrijving de optie Fouten in de vervolgkeuzelijst
5. Stel het formaat in en klik op OK. Dit markeert elke foutwaarde in de geselecteerde dataset
   

Ga naar speciaal gebruiken

1. Selecteer de volledige dataset
2. Druk op F5 (hiermee wordt het dialoogvenster Ga naar dialoog geopend)
3. Klik op de speciale knop linksonder
4. Selecteer Formules en schakel alle opties uit behalve Fouten
   

Dit selecteert alle cellen die een fout bevatten. Nu kunt u deze handmatig markeren, verwijderen of er iets in typen.

#6 Tekst wijzigen in kleine letters/hoofdletters/juiste letters

Wanneer u een werkmap overneemt of gegevens uit tekstbestanden importeert, zijn de namen of titels vaak niet consistent. Soms kan alle tekst in hoofdletters/kleine letters zijn of een combinatie van beide. Je kunt het allemaal gemakkelijk consistent maken door deze drie functies te gebruiken:

LOWER() - Converteert alle tekst naar kleine letters.
UPPER() - Converteert alle tekst naar hoofdletters.
PROPER() - Converteert alle tekst naar juiste hoofdletters.

#7 Gegevens ontleden met tekst naar kolom

Wanneer u gegevens uit een database haalt of uit een tekstbestand importeert, kan het voorkomen dat alle tekst in één cel te krap is. U kunt deze tekst in meerdere cellen ontleden door de functie Tekst naar kolom in Excel te gebruiken.

1. Selecteer de gegevens/tekst die u wilt ontleden
2. Ga naar gegevens -> Tekst naar kolom (hiermee wordt de wizard Tekst naar kolommen geopend)
   • Stap 1: Selecteer het gegevenstype (selecteer Gescheiden als uw gegevens niet gelijkmatig verdeeld zijn en worden gescheiden door tekens zoals komma, koppelteken, punt…). Klik volgende
     
   • Stap 2: Selecteer scheidingsteken (het teken dat uw gegevens scheidt). U kunt een vooraf gedefinieerd scheidingsteken of iets anders selecteren met behulp van de optie Overige
     
   • Stap 3: Selecteer het gegevensformaat. Selecteer ook de bestemmingscel. Als de bestemmingscel niet is geselecteerd, wordt de huidige cel overschreven
     

Verwant: Extraheer gebruikersnaam uit e-mail-ID met tekst naar kolom.

#8 Spellingcontrole

Niets verlaagt de geloofwaardigheid van uw werk dan een spelfout.

Gebruik de sneltoets F7 om een ​​spellingcontrole uit te voeren voor uw dataset.

Hier is een gedetailleerde zelfstudie over het gebruik van spellingcontrole in Excel.

#9 Alle opmaak verwijderen

In mijn werk gebruikte ik meerdere databases om de gegevens in Excel te krijgen. Elke database had zijn eigen gegevensopmaak. Als u alle gegevens op hun plaats hebt, kunt u als volgt alle opmaak in één keer verwijderen:

1. Selecteer de dataset
2. Ga naar Home -> Wissen -> Formaten wissen
   

Op dezelfde manier kunt u ook alleen de opmerkingen, hyperlinks of inhoud wissen.

#10 Gebruik Zoeken en vervangen om gegevens op te schonen in Excel

Vinden en vervangen is onmisbaar als het gaat om het opschonen van gegevens. U kunt bijvoorbeeld alle nullen selecteren en verwijderen, verwijzingen in formules wijzigen, opmaak zoeken en wijzigen, enzovoort.

Lees meer over hoe Zoeken en vervangen kan worden gebruikt om gegevens op te schonen.

Dit zijn mijn top 10 technieken om gegevens in Excel op te schonen. Als u nog meer technieken wilt leren, vindt u hier een handleiding van het MS Excel-team - Gegevens opschonen in Excel.

Als er nog meer technieken zijn die u gebruikt, deel deze dan met ons in het opmerkingengedeelte!