Als u met een dataset van namen werkt, is het sorteren ervan een van de veelvoorkomende taken die u regelmatig moet uitvoeren.
Het is gemakkelijk om gegevens alfabetisch op volledige naam te sorteren, waarbij Excel het eerste teken van de naam gebruikt om te sorteren.
Als u echter wiltSorteer gegevens op achternaam in Excelga verderopeenvolgingwat te doen?
Hoewel het niet zo eenvoudig is, kan het nog steeds worden gedaan (hangt ook sterk af van hoe de naamgegevens zijn gestructureerd).
Welke methode je ook gebruikt, je moet op de een of andere manier de achternaam uit de volledige naam halen en in een aparte kolom plaatsen.U kunt deze kolom vervolgens gebruiken om de gegevens alfabetisch op achternaam te sorteren.
In deze Excel-zelfstudie laat ik u zien hoe u een kolom met namen sorteert op basis van achternaam.
Dus laten we beginnen!
Inhoud
Extraheer en sorteer op achternaam met Zoeken en vervangen
De eerste stap om te sorteren op achternaam is om de achternaam in een aparte kolom te krijgen.
U kunt dit doen door alles voor de achternaam te vervangen door spaties, zodat u alleen de achternaam overhoudt.
Stel dat u een dataset heeft zoals hieronder, en u wilt die data alfabetisch sorteren op achternaam.
Hier zijn de stappen om op achternaam te sorteren:
- Selecteer de dataset die de titel bevat (in dit geval is dit A1:A10)
- kopieer het naar de aangrenzende kolom (als de aangrenzende kolom niet leeg is, voegt u een nieuwe kolom in en kopieert u de namen)
- Hernoem de gekopieerde kolomkoppen.In dit voorbeeld noem ik het "Achternaam"
- Selecteer alle gekopieerde namen (selecteer geen titels)
- Houd de Control-toets ingedrukt en druk vervolgens op de H-toets.Dit opent het dialoogvenster Zoeken en vervangen.
- Voer in het veld Zoeken naar * in (een asterisk gevolgd door een spatie)
- Laat het veld Vervangen door leeg
- Klik op Alles vervangen.Hiermee worden alle voornamen in één keer vervangen en houdt u alleen achternamen over.
De bovenstaande stappen behouden de achternaam en verwijderen alles ervoor.Dit werkt zelfs als je een tweede naam of voorvoegsel hebt, zoals de heer of mevrouw.
Zodra u achternamen in aangrenzende kolommen heeft, kunt u de dataset (inclusief volledige namen) eenvoudig alfabetisch op achternaam sorteren.
Hier zijn de stappen om op achternaam te sorteren:
- Selecteer de volledige dataset met titels (inclusief volledige namen en geëxtraheerde achternamen).U kunt ook andere kolommen opnemen om met de naam te sorteren
- Klik op het tabblad Gegevens
- Klik om te sorteren
- Controleer in het dialoogvenster Sorteren of Mijn gegevens hebben kopteksten is aangevinkt.
- Selecteer in de optie "Sorteren op" de naam van de kolom met alleen de achternaam
- Selecteer in Sorteren op Celwaarde
- Selecteer in de optie "Bestellen" "A tot Z"
- Klik OK
De bovenstaande stappen sorteren de volledige geselecteerde dataset op basis van achternaam.
Als u klaar bent, kunt u de kolom met de achternaam neerzetten.
Professionele tips: Op elk moment dat u denkt dat u terug moet naar de oorspronkelijke gegevens, moet u een manier hebben om die gegevensset te de-sorteren.Hiervoor staan in aangrenzende kolommen (links of rechts) volgnummers voor het sorteren.Als u nu de oorspronkelijke gegevens wilt terugkrijgen, kunt u deze verkrijgen door op getallen te sorteren.
Extraheer en sorteer alfabetisch op achternaam met behulp van formule
Hoewel ik de hierboven getoonde methode (met behulp van zoeken en vervangen) de voorkeur geef om alle achternamen te krijgen en daarop te sorteren, is een van de beperkingen dat de resulterende gegevens statisch zijn.
Dit betekent dat als ik meer voornamen aan de lijst toevoeg, ik hetzelfde proces opnieuw moet doorlopen om de achternaam te krijgen.
Als dit niet is wat u wilt, kunt u een formulemethode gebruiken om de gegevens op achternaam te sorteren.
Stel dat u de onderstaande dataset heeft.
Hier is de formule om de achternaam uit de volledige naam te halen:
= RECHTS (A2, LEN (A2) -FIND ("", A2))
De bovenstaande formule is gebaseerd op een patroon met volledige namen (in dit voorbeeld alleen voor- en achternaam).Het patroon is dat er een spatie tussen de voor- en achternaam komt.
De FIND-functie wordt gebruikt om de positie van het spatieteken te krijgen.Deze waarde wordt vervolgens afgetrokken van de totale lengte van de voornaam om het totale aantal tekens in de achternaam te krijgen.
Gebruik vervolgens die waarde in de functie RECHTS om de achternaam te krijgen.
Zodra u de achternaamkolom hebt, kunt u deze gegevens sorteren (dit wordt gedetailleerd beschreven in de eerste methode).
De bovenstaande formule werkt als je alleen voor- en achternaam hebt.
Maar wat als je ook een tweede naam hebt.of kan een aanhef voor de naam hebben (bijv. Dhr. of Ms.)
In dit geval moet u de volgende formule gebruiken:
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
De bovenstaande formule vindt de positie van het laatste spatieteken en gebruikt deze vervolgens om de achternaam te extraheren.
Ik stel voor dat u in alle gevallen de tweede formule gebruikt, deze is eenvoudiger en behandelt alle gevallen (zolang de achternaam aan het einde van de voornaam staat).
Opmerking: deze twee formules zijn afhankelijk van de voorwaarde dat er slechts één spatie tussen elk naamelement staat.Deze formule geeft onjuiste resultaten als er dubbele spaties of voorloop-/naloopspaties zijn.In dit 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 benadering lijkt, is het voordeel van het gebruik van een formule dat het de resultaten dynamisch maakt.Als u meer namen aan de lijst toevoegt, hoeft u alleen maar de formule te kopiëren en krijgt u de achternaam.
Tekst naar kolommen gebruiken
Tekst naar kolommen is nog een andere gemakkelijke manier om cellen in Excel te splitsen.
U kunt een scheidingsteken opgeven (zoals een komma of spatie) en dit gebruiken om de inhoud van een cel te splitsen.Na het splitsen van elementen in afzonderlijke kolommen, kunt u de kolom met de achternaam gebruiken om de gegevens te alfabetiseren.
Stel dat u een dataset heeft die er als volgt uitziet:
Hier zijn de stappen om op achternaam te sorteren met Tekst naar kolom:
- Selecteer kolommen met namen (exclusief koppen)
- Klik op het tabblad Gegevens
- Klik in de groep Gegevenshulpmiddelen op de optie Tekst naar kolommen.Dit opent de wizard Tekst naar kolommen
- Selecteer in stap 1 van de wizard Tekst naar kolommen converteren Gescheiden en klik op Volgende
- In stap 2 selecteert u "Space" als scheidingsteken (schakel alles uit indien aangevinkt) en klik op de knop Volgende.
- Selecteer in stap 3 de voornaamkolom in het gegevensvoorbeeld en selecteer vervolgens de optie "Kolom niet importeren (overslaan)".Dit zorgt ervoor dat de voornaam geen deel uitmaakt van het resultaat, u krijgt alleen de achternaam.
- Verander ook in stap 3 de doelcel in de cel naast de originele gegevens.Dit zorgt ervoor dat u alleen de achternaam krijgt en dat de oorspronkelijke naamgegevens compleet zijn.
- Klik op Voltooien
Zodra u de resultaten heeft, kunt u sorteren op achternaam.
U kunt ook tekst-naar-kolom gebruiken om voor- en achternaam te scheiden als u komma's als scheidingstekens hebt.
Vul met glitter
Een andere snelle manier om de achternaam te krijgen, is door de functie Flash Fill te gebruiken.
Flash Fill, geïntroduceerd in Excel 2013, helpt bij het manipuleren van gegevens door patronen te identificeren.Om dit te doen, moet u Flash Fill meerdere keren weergeven voor het verwachte resultaat.
Zodra het het patroon herkent, doet het snel de rest voor u.
Stel dat u de volgende dataset met namen hebt.
Hier zijn de stappen om de achternaam te krijgen met Flash Fill en deze te gebruiken om te sorteren:
- Voer in cel B2 de tekst "Maury" in.Dit is wat je zou verwachten in een cel.
- Ga naar de volgende cel en voer de achternaam van de naam in de aangrenzende cel in (in dit geval Elliot).
- selecteer twee cellen
- Beweeg de cursor over het gedeelte rechtsonder van de selectie.U zult merken dat de cursor verandert in een pluspictogram.
- Dubbelklik erop (of klik en sleep naar beneden).Dit geeft je een resultaat in de cel (onwaarschijnlijk degene die je wilt)
- Klik op het pictogram Opties voor automatisch aanvullen.
- Klik op Snel invullen
Dit geeft u een resultaat dat in alle cellen de achternaam kan zijn.
Ik zeg misschien omdat Flash Fill in sommige gevallen niet werkt.Omdat het afhangt van de herkenningsmodus, doet het dit mogelijk niet altijd.Of soms is het patroon dat het ontcijfert niet het juiste patroon.
In dit geval moet u het verwachte resultaat in een of meer cellen invoeren en vervolgens stap 4-7 doorlopen.
Nadat u alle achternamen in de kolom hebt staan, kunt u de gegevens sorteren op basis van die achternamen.
Daarom kunt u de gegevens op vier verschillende manieren op achternaam sorteren.De beste manier is om zoek- en vervangingstechnieken te gebruiken, maar als u uw resultaten dynamisch wilt maken, zijn formulemethoden de juiste keuze.
Ik hoop dat je deze tutorial nuttig vond.
