Gegevensextractie en -opschoning is de eerste en meest cruciale taak van elk gegevensanalyseproject. In mijn vijf jaar ervaring heb ik gegevens ontvangen die eruit zagen als rommel, maar met de Excel-functies RECHTS en LINKS kon ik ze omzetten in zinvolle rapporten.
Gegevens opschonen in Excel is eenvoudig. Hier ga ik uitleggen hoe je specifieke tekst in Excel 2016 uit een cel kunt extraheren en afvalgegevens kunt achterlaten.
In de bovenstaande afbeelding krijg ik gegevens in Excel. Het heeft Naam, Leeftijd, en als de persoon wel of geen verzekering heeft (J/N). Om deze gegevens te gebruiken, moet ik gegevens in verschillende kolommen extraheren. Elke kolom mag slechts één soort waarde bevatten. Als het klaar is, kunnen we draairapporten maken in Excel die ons beter vertellen over de gegevens. Dus laten we beginnen.
Excel RECHTS-functie om gegevens van rechts van de tekst te extraheren
Laten we beginnen met de gemakkelijkste. In dit voorbeeld hebben we "Heeft verzekering?" gegevens aan het einde van de tekenreeks. En dat is maar één teken (Y of N). Om deze gegevens te extraheren, gebruiken we de JUISTE formule die Excel biedt. De RECHTS-functie is beschikbaar in Excel 2016, Excel 2013, Excel 2010 en oudere versies van Excel.
De functie RECHTS in Excel heeft slechts twee argumenten. Eerst de TEKST, en ten tweede het AANTAL TEKENS dat u van rechts wilt halen in Excel.
Generieke formule:
=RIGHT(tekst, aantal_tekens)
Hier hebben we onze tekst in cel B3 en het aantal tekens dat we willen extraheren is 1. Schrijf deze formule gewoon in Excel in cel D3 of waar u deze gegevens ook wilt hebben.
=RECHTS(B3,1)
U krijgt uw resultaat in cel D3. kopieer of sleep deze formule gewoon in de onderstaande cellen.
Excel LEFT-functie om gegevens van links van de tekst te extraheren
Laten we nu onze leeftijd nemen. Voordat ik het uitleg, wil ik u de formule laten zien. Kopieer deze formule in de gewenste cellen.
=LINKS(RECHTS(B3,4),2)
In de bovenstaande formule hebben we de functies LEFT en RIGHT gecombineerd om onze specifieke tekst uit de string te halen. Dit is hoe het eruit ziet.
Laten we nu deze nietige formule begrijpen.
Elke functie werkt van binnen naar buiten. Dus laten we beginnen met:
RECHTS(B3,4): Het extraheert eenvoudig 4 tekens uit een bepaalde tekst. Dat geeft ons 30_Y. Waarom 4? Omdat we weten dat leeftijd slechts een van de twee tekens is, en we hebben 1 "_" en 1 Y of N.
LINKS(RECHTS(B3,4),2): Nu hebben we onze tekst die begint met leeftijd van 2 karakters. Hier komt de LEFT-functie in actie. Het duurt RECHTS(B3,4) als zijn tekstargument en haalt daar 2 tekens uit. Dat geeft ons 30 voor de eerste cel. Kopieer de formule in de onderstaande cellen en u krijgt de rest van de gegevens zoals weergegeven in de afbeelding hierboven.
Algemene formule voor LINKS:
=LINKS(tekst, Aantal_tekens)
Net als de RECHTS-functie in Excel, heeft de LEFT-functie van Excel ook twee dezelfde argumenten. Het extraheert alleen gegevens aan de linkerkant van de verstrekte tekenreeks.
Excel LINKS en rechts-functie om onbekende lengte van tekst uit een string te extraheren
Plak deze formule gewoon in de gewenste cellen om de naam uit de tekst in Excel te extraheren.
=LINKS(B3,LENGTE(B3)-5)
In de bovenstaande voorbeelden wisten we het exacte aantal teksten dat we uit de gegeven tekst moesten halen. Maar nu weten we niet hoe lang een naam is. Wat gedaan kan worden?
Wel weten we dat de naam gevolgd wordt door exact 5 karakters. 1 voor spatie, 2 voor leeftijd, 1 voor "_" underscore en één voor Y.
Als we alle tekst kunnen krijgen en daar 5 van kunnen aftrekken, hebben we het exacte aantal tekst voor de naam.
Voor het vinden van de lengte van een tekst in Excel kunnen we de LEN-functie gebruiken. De LEN-functie van Excel retourneert het aantal betrokken tekens.
Syntaxis:
=LENGTE(tekst)
Dus LENTE (B3) retour 20.
LENTE (B3)-5 geeft 15 terug. En dit is de lengte van de naam in cel B3
Eindelijk LINKS(B3, LENTE(B3)-5) geeft ons Georgina Schalk als resultaat. Wanneer u de formule in de onderstaande cellen kopieert, krijgt u alle namen.
Nu kunt u rapporten zoals deze opstellen op basis van geëxtraheerde gegevens met behulp van draaitabellen. Het vertelt ons dat de helft van onze proefpersonen een verzekering heeft en de andere helft niet.
LINKS en RECHTS FUNCTIE van Excel is zeer krachtig en handig voor het opschonen van gegevens. In combinatie met de LEN-functie, FIND-functie en SUBSTITUTE-functie van Excel kunnen ze elke substring in een string in Excel extraheren.
In de toekomst zullen we complexere gegevensextractie in Excel zien. Voor nu tot ziens. En ja, laat uw mening achter in de opmerkingen hieronder. Ik zal u graag helpen.