Bekijk Video – Hoe te Sorteren op Achternaam in Excel
Als u met namen-datasets werkt, is Sorteren een van de veel voorkomende 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 u gegevens wilt sorteren op achternaam In Excel?,
hoewel het niet zo eenvoudig is, kan het nog steeds gedaan worden (veel hangt ook af van de manier waarop names data gestructureerd is).
Het maakt niet uit welke methode u gebruikt, u zult op de een of andere manier de achternaam uit de volledige naam moeten halen en deze in een aparte kolom moeten plaatsen. Vervolgens kunt u deze kolom gebruiken om uw gegevens alfabetisch te sorteren op de achternaam.
in deze Excel-zelfstudie laat ik u zien hoe u een kolom met namen sorteert op basis van de achternaam.
dus laten we beginnen!,
Deze Tutorial behandelt:
uitpakken en sorteren op Achternaam met behulp van Zoeken en vervangen
de eerste stap naar Sorteren op Achternaam is om de achternaam in een aparte kolom te krijgen.
u kunt dat doen door alles voor de achternaam te vervangen door een spatie zodat u alleen de achternaam over hebt.
stel dat u een dataset hebt zoals hieronder getoond en dat u deze gegevens Alfabetisch wilt sorteren met behulp van de achternaam.,
Hieronder volgen de stappen om te sorteren op achternaam:
- Selecteer de dataset inclusief de header (in dit voorbeeld zou het A1:A10 zijn)
- kopieer deze in de aangrenzende kolom (als de aangrenzende kolom niet leeg is, voeg een nieuwe kolom in en kopieer deze namen)
- hernoem de gekopieerde kolomkop. In dit voorbeeld noem ik ‘achternaam’
- Selecteer alle gekopieerde namen (selecteer de kop niet)
- Houd de Control-toets ingedrukt en druk vervolgens op de H-toets. Hiermee wordt het dialoogvenster Zoeken en vervangen geopend.,
- voer in het veld Zoeken naar * (sterretje gevolgd door een spatie)
- laat het vervangen door het veld leeg
- klik op Alles vervangen. Dit zou onmiddellijk alle voornaam vervangen en u zult alleen achterblijven met achternaam.
de bovenstaande stappen zouden de achternaam behouden en alles ervoor verwijderen. Dit werkt goed, zelfs als je middelste namen of voorvoegsels hebt (zoals Mr.of Ms).,
zodra u de achternaam in de aangrenzende kolom hebt, kunt u de dataset (inclusief de volledige namen) eenvoudig alfabetisch sorteren op basis van de achternaam.
hieronder staan de stappen om te sorteren op de achternaam:
- Selecteer de volledige dataset met headers (inclusief de volledige namen en de geëxtraheerde achternaam)., U kunt ook andere kolommen toevoegen die u wilt sorteren samen met de namen
- klik op het tabblad Gegevens
- klik op Sorteren
- in het dialoogvenster Sorteren, zorg ervoor dat ‘mijn gegevens hebben headers’ is geselecteerd.,
- in de ‘Sorteer op’ optie, selecteer de naam van de kolom die alleen de achternaam heeft
- In De ‘Sorteer op’, selecteer ‘Cell Values’
- In de volgorde optie, selecteer ‘A tot Z’
- klik OK
de bovenstaande stappen zouden de gehele geselecteerde dataset Sorteren op basis van de achternaam.
Als u klaar bent, kunt u de kolom met de achternaam verwijderen.,
uitpakken en alfabetiseren op Achternaam met behulp van Formule
terwijl de methode die hierboven wordt getoond (met behulp van Zoeken en vervangen) is wat ik de voorkeur aan alle achternaam te krijgen en sorteren op basis van het, een beperking van het is dat de resulterende gegevens in statisch.,
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 formule-methode gebruiken om gegevens op achternaam te sorteren.
stel dat u de dataset hebt zoals hieronder getoond.
Hieronder is de formule die de achternaam zal extraheren uit de volledige naam:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
de bovenstaande formule is gebaseerd op het patroon met een volledige naam (dat alleen de voor-en achternaam in dit voorbeeld bevat)., Het patroon is dat er een spatie teken tussen de voor-en achternaam zou zijn.
De FIND-functie wordt gebruikt om de positie van het spatiekarakter te krijgen. Deze waarde wordt dan afgetrokken van de totale lengte van de naam om het totale aantal tekens in de achternaam te krijgen.
deze waarde wordt dan gebruikt in de juiste functie om de achternaam te krijgen.
zodra u de kolom achternaam hebt, kunt u deze gegevens Sorteren (dit wordt in de eerste methode in detail behandeld).
de bovenstaande formule werkt als u alleen voor-en achternaam hebt.
maar wat als je ook een middelste naam hebt., Of kan er een aanhef zijn voor de naam (zoals Mr of MS)
In dat geval moet u de onderstaande 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 het om de achternaam te extraheren.
Ik raad u aan de tweede formule in alle gevallen te gebruiken, en het is meer fool-proof en kan alle gevallen behandelen (zolang de achternaam aan het einde van de naam staat).
Opmerking: Deze twee formules vertrouwen op de voorwaarde dat er slechts één spatieteken tussen elk naamelement is., In het geval dat er dubbele spaties of voorloopspaties/volgspaties zijn, geeft deze formule onjuiste resultaten. In een dergelijk geval is het het beste om de TRIM-functie te gebruiken om eerst af te komen van leading, trailing en dubbele spaties en vervolgens de bovenstaande formule te gebruiken.
hoewel dit 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 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 komma of spatie) en het 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 op alfabet te zetten.
stel dat u een dataset hebt zoals hieronder getoond:
Hieronder staan de stappen om tekst naar kolom te sorteren op achternaam:
- Selecteer de kolom met de naam (exclusief de header)
- klik op het tabblad Gegevens
- In de groep ‘Data Tools’, klik op de optie Tekst naar kolommen., Dit opent de wizard Tekst naar kolommen
- in Stap 1 van de Wizard Tekst naar kolommen converteren, selecteert u ‘Delimited’ en klikt u op Volgende
- in Stap 2 selecteert u ‘spatie’ als het scheidingsteken (en schakelt u verder uit indien geselecteerd) en klikt u op de knop Volgende.
- in Stap 3 selecteert u de kolom voornaam in het Gegevensvoorbeeld en selecteert u vervolgens de optie ‘kolommen niet importeren (overslaan)’. Dit zorgt ervoor dat de voornaam geen deel uitmaakt van het resultaat en je krijgt alleen de achternaam.,
- wijzig ook in Stap 3 de doelcel in de cel die naast de oorspronkelijke gegevens staat. Dit zorgt ervoor dat u de achternaam apart krijgt en dat de oorspronkelijke naamgegevens intact zijn.
- klik op Finish
zodra u het resultaat hebt, kunt u sorteren op achternaam.
U kunt ook tekst naar kolommen om voor-en achternaam te scheiden wanneer u een komma als scheidingsteken hebt.,
met behulp van Flash Fill
een andere snelle en snelle manier om de achternamen te krijgen is met behulp van de Flash Fill-functie.
Flash Fill werd geïntroduceerd in Excel 2013 en het helpt de gegevens te manipuleren door patronen te identificeren. Om dit te laten werken, je nodig hebt om te laten zien Flash vul het resultaat dat u een paar keer verwacht.
zodra het het patroon identificeert, zal het snel de rest van het werk voor u doen.
stel dat u de onderstaande names-dataset hebt.,
Hieronder staan de stappen om Flash Fill te gebruiken om de achternaam te krijgen en deze vervolgens te sorteren:
- voer in cel B2 de tekst ‘Maury’in. Dit is het resultaat dat je verwacht in de cel.
- ga naar de volgende cel en voer de achternaam in voor de naam in de aangrenzende cel (Elliot in dit voorbeeld).
- Selecteer beide cellen
- beweeg de cursor over het rechtsonder deel van de selectie. U zult merken dat de cursor verandert in een Plus-pictogram.
- dubbelklik erop (of klik en sleep het naar beneden)., Dit geeft u enig resultaat in de cellen (waarschijnlijk niet het gewenste resultaat)
- klik op het pictogram Opties voor Automatisch aanvullen.
- klik op Flash Fill
Dit geeft u het resultaat dat waarschijnlijk de achternaam in alle cellen zal zijn.
Ik zeg waarschijnlijk, omdat Flitsvulling in sommige gevallen niet werkt. Omdat het afhangt van het identificeren van een patroon, kan het niet in staat zijn om dat altijd te doen. Of soms is het patroon dat het ontcijfert misschien niet de juiste.,
in dergelijke gevallen moet u een verwacht resultaat in één of twee cellen invoeren en vervolgens stappen 4-7 uitvoeren.
zodra u alle achternamen in een kolom hebt, kunt u de gegevens Sorteren op basis van deze achternamen.
dus dit zijn vier verschillende manieren die u kunt gebruiken om gegevens te sorteren op de achternaam. De beste methode zou zijn om de techniek zoeken en vervangen te gebruiken, maar als u uw resultaten dynamisch wilt maken, is de formule-methode de juiste manier.
hoop dat je deze tutorial nuttig vond.,
u kunt ook de volgende Excel-tutorials leuk vinden:
- Sorteren op kleur in Excel
- werkbladen sorteren in Excel
- sorteren in Excel met behulp van VBA
- Sorteer gegevens automatisch in alfabetische volgorde met behulp van de Formule
- Sorteren op meerdere niveaus in Excel