Sorteren op achternaam in Excel (eenvoudige handleiding)

Bekijk video - Sorteren op achternaam in Excel

Als u met namen-datasets werkt, is het sorteren ervan een van de veelvoorkomende taken die u vaak zou moeten doen.

Het is vrij eenvoudig om gegevens alfabetisch te sorteren op basis van de volledige naam, waarbij Excel het eerste teken van de naam gebruikt om te sorteren.

Maar wat als je wilt? sorteer gegevens op achternaam in Excel?

Hoewel het niet zo eenvoudig is, kan het nog steeds worden gedaan (veel hangt ook af van de manier waarop naamgegevens zijn gestructureerd).

Welke methode je ook gebruikt, je zult op de een of andere manier de achternaam uit de volledige naam moeten halen en in een aparte kolom moeten plaatsen. U kunt deze kolom vervolgens gebruiken om uw gegevens alfabetisch op achternaam te sorteren.

In deze Excel-zelfstudie laat ik u zien hoe u een kolom met namen sorteert op basis van de achternaam.

Dus laten we beginnen!

Extraheren en sorteren op achternaam met Zoeken en vervangen

De eerste stap om te sorteren op achternaam is om de achternaam in een aparte kolom te krijgen.

Dat doe je door alles voor de achternaam te vervangen door een spatie, zodat je alleen de achternaam overhoudt.

Stel u heeft een dataset zoals hieronder weergegeven en u wilt deze data alfabetisch sorteren op achternaam.

Hieronder vindt u de stappen om op achternaam te sorteren:

1. Selecteer de dataset inclusief de header (in dit voorbeeld zou dit A1:A10 zijn)
2. Kopieer het in de aangrenzende kolom (als de aangrenzende kolom niet leeg is, voegt u een nieuwe kolom in en kopieert u deze namen)
3. Hernoem de gekopieerde kolomkop. In dit voorbeeld zal ik naam is 'Achternaam'
4. Selecteer alle gekopieerde namen (selecteer niet de koptekst)
5. Houd de Control-toets ingedrukt en druk vervolgens op de H-toets. Hiermee wordt het dialoogvenster Zoeken en vervangen geopend.
6. Voer in het veld Zoeken naar * in (sterretje gevolgd door een spatie)
7. Laat het veld Vervangen door leeg
8. Klik op Alles vervangen. Dit zou onmiddellijk alle voornamen vervangen en u houdt alleen achternamen over.

De bovenstaande stappen zouden de achternaam behouden en alles ervoor verwijderen. Dit werkt goed, zelfs als je middelste namen of voorvoegsels hebt (zoals meneer of mevrouw).

Zodra u de achternamen in de kolom ernaast heeft staan, kunt u de dataset (inclusief de volledige namen) eenvoudig alfabetisch op achternaam sorteren.

Hieronder vindt u de stappen om op achternaam te sorteren:

1. Selecteer de volledige dataset met headers (inclusief de volledige namen en de geëxtraheerde achternamen). U kunt ook andere kolommen opnemen die u samen met de namen wilt sorteren
2. Klik op het tabblad Gegevens
3. Klik op Sorteren
4. Zorg ervoor dat in het dialoogvenster Sorteren 'Mijn gegevens hebben kopteksten' is geselecteerd.
5. Selecteer in de optie 'Sorteren op' de naam van de kolom die alleen de achternaam heeft
6. Kies bij 'Sorteren op' voor 'Celwaarden'
7. Selecteer in de optie Bestellen 'A tot Z'
8. Klik OK

De bovenstaande stappen zouden de volledige geselecteerde dataset sorteren op basis van de achternaam.

Als u klaar bent, kunt u de kolom met de achternaam verwijderen.

Pro-tip: Als u op enig moment denkt dat u de originele gegevens terug nodig heeft, moet u een manier hebben om de sortering van deze gegevensset ongedaan te maken. Om dit te doen, moet u in een aangrenzende kolom (links of rechts) serienummers hebben voor het sorteren. Nu, als u de originele gegevens terug nodig heeft, krijgt u deze door te sorteren op basis van de cijfers.

Extraheren en alfabetiseren op achternaam met formule

Hoewel de hierboven getoonde methode (met behulp van Zoeken en vervangen) is waar ik de voorkeur aan geef om alle achternamen te krijgen en daarop te sorteren, is een beperking ervan dat de resulterende gegevens statisch zijn.

Dit betekent dat als ik meer namen aan mijn lijst toevoeg, ik hetzelfde proces opnieuw moet doen om de achternamen te krijgen.

Als u dit niet wilt, kunt u de formulemethode gebruiken om gegevens op achternaam te sorteren.

Stel dat je de dataset hebt zoals hieronder weergegeven.

Hieronder staat de formule die de achternaam uit de volledige naam haalt:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

De bovenstaande formule is gebaseerd op het patroon met een volledige naam (die in dit voorbeeld alleen de voor- en achternaam bevat). Het patroon is dat er een spatie tussen de voor- en achternaam zou staan.

De FIND-functie wordt gebruikt om de positie van het spatieteken te krijgen. Deze waarde wordt vervolgens afgetrokken van de totale lengte van de naam om het totale aantal tekens in de achternaam te krijgen.

Deze waarde wordt vervolgens gebruikt in de functie RECHTS om de achternaam te krijgen.

Zodra u de achternaamkolom hebt, kunt u deze gegevens sorteren (dit wordt in de eerste methode in detail behandeld).

De bovenstaande formule zou werken als je alleen voor- en achternaam hebt.

Maar wat als je ook een tweede naam hebt. Of mag er een aanhef voor de naam staan ​​(zoals meneer of mevrouw)

In dat geval moet u de onderstaande formule gebruiken:

=RIGHT(A2,LEN(A2)-FIND("@",VERVANG(A2," ","@",LEN(A2)-LEN(VERVANG(A2," ","")))))

De bovenstaande formule vindt de positie van het laatste spatieteken en gebruikt deze vervolgens om de achternaam te extraheren.

Ik raad je aan de tweede formule in alle gevallen te gebruiken, en het is meer onfeilbaar en kan alle gevallen aan (zolang de achternaam aan het einde van de naam staat).

Opmerking: deze twee formules zijn afhankelijk van de voorwaarde dat er slechts één spatie tussen elk naamelement staat. In het geval er dubbele spaties zijn, of voorloop/achtervolging spaties, zal deze formule onjuiste resultaten geven. In een dergelijk geval kunt u het beste de TRIM-functie gebruiken om eerst alle voorloop-, volg- en dubbele spaties te verwijderen en vervolgens de bovenstaande formule te gebruiken.

Hoewel dit misschien een ingewikkelde methode lijkt, is het voordeel van het gebruik van een formule dat het de resultaten dynamisch maakt. Als u meer namen aan uw lijst toevoegt, hoeft u alleen maar de formule te kopiëren en krijgt u de achternaam.

Tekst naar kolommen gebruiken

Tekst naar kolommen is opnieuw een eenvoudige en gemakkelijke manier om cellen in Excel te splitsen.

U kunt het scheidingsteken opgeven (zoals een komma of spatie) en dit gebruiken om de inhoud van de cel te splitsen. Zodra u de gesplitste elementen in afzonderlijke kolommen hebt, kunt u de kolom met de achternaam gebruiken om de gegevens te alfabetiseren.

Stel dat u een dataset heeft zoals hieronder weergegeven:

Hieronder vindt u de stappen om Tekst naar kolom te gebruiken om op achternaam te sorteren:

1. Selecteer de kolom met de naam (exclusief de kop)
2. Klik op het tabblad Gegevens
3. Klik in de groep 'Gegevenshulpmiddelen' op de optie Tekst naar kolommen. Dit opent de wizard Tekst naar kolommen
4. Selecteer in stap 1 van de wizard 'Tekst naar kolommen converteren' 'Gescheiden' en klik op Volgende
5. Selecteer in stap 2 'Space' als scheidingsteken (en schakel al het andere uit indien geselecteerd) en klik vervolgens op de knop Volgende.
6. Selecteer in stap 3 de kolom voornaam in het gegevensvoorbeeld en selecteer vervolgens de optie 'Kolommen niet importeren (overslaan)'. Dit zorgt ervoor dat de voornaam geen deel uitmaakt van het resultaat en u alleen de achternaam krijgt.
7. Wijzig ook in stap 3 de bestemmingscel in de cel die grenst aan de originele gegevens. Dit zorgt ervoor dat u de achternaam apart krijgt en dat de originele naamgegevens intact zijn.
8. Klik op Voltooien

Zodra u het resultaat heeft, kunt u sorteren op achternaam.

U kunt ook Tekst naar kolommen gebruiken om voor- en achternaam te scheiden als u een komma als scheidingsteken hebt.

Flash Fill gebruiken

Een andere snelle en snelle manier om de achternamen te krijgen, is door de functie Flash Fill te gebruiken.

Flash Fill is geïntroduceerd in Excel 2013 en helpt de gegevens te manipuleren door patronen te identificeren. Om dit te laten werken, moet u Flash Fill een paar keer het verwachte resultaat laten zien.

Zodra het het patroon identificeert, zal het snel de rest van het werk voor u doen.

Stel dat u de onderstaande dataset met namen hebt.

Hieronder vindt u de stappen om Flash Fill te gebruiken om de achternaam te krijgen en deze vervolgens te sorteren:

1. Voer in cel B2 de tekst 'Maury' in. Dit is het resultaat dat u in de cel verwacht.
2. Ga naar de volgende cel en voer de achternaam in voor de naam in de aangrenzende cel (Elliot in dit voorbeeld).
3. Selecteer beide cellen
4. Beweeg de cursor over het gedeelte rechtsonder in de selectie. U zult merken dat de cursor verandert in een pluspictogram.
5. Dubbelklik erop (of klik en sleep het naar beneden). Dit geeft je enig resultaat in de cellen (waarschijnlijk niet het gewenste resultaat)
6. Klik op het pictogram Opties voor automatisch aanvullen.
7. Klik op Flash Fill

Dit geeft u het resultaat dat waarschijnlijk de achternaam in alle cellen zal zijn.

Ik zeg waarschijnlijk, omdat Flash Fill in sommige gevallen mogelijk niet werkt. Omdat het afhangt van het identificeren van een patroon, kan het dat misschien niet altijd doen. Of soms is het patroon dat het ontcijfert misschien niet het juiste.

In dergelijke gevallen moet u een verwacht resultaat in een of twee extra cellen invoeren en vervolgens stappen 4-7 uitvoeren.

Zodra u alle achternamen in een kolom heeft, kunt u de gegevens sorteren op basis van deze achternamen.

Dit zijn dus vier verschillende manieren die u kunt gebruiken om gegevens op achternaam te sorteren. De beste methode zou zijn om de techniek Zoeken en vervangen te gebruiken, maar als u uw resultaten dynamisch wilt maken, is de formulemethode de juiste keuze.

Ik hoop dat je deze tutorial nuttig vond.