Titta på video - Hur man sorterar efter efternamn i Excel
Om du arbetar med namnuppsättningar är sortering en av de vanliga uppgifterna du ofta måste utföra.
Det är ganska enkelt att sortera data alfabetiskt baserat på det fullständiga namnet, där Excel använder det första tecknet i namnet för att sortera.
Men tänk om du vill sortera data efter efternamnet i Excel?
Även om det inte är lika enkelt kan det fortfarande göras (mycket beror också på hur namndata är strukturerade).
Oavsett vilken metod du använder måste du på något sätt extrahera efternamnet från det fullständiga namnet och lägga det i en separat kolumn. Du kan sedan använda den här kolumnen för att sortera dina data efternamnet alfabetiskt.
I denna Excel -handledning visar jag dig hur du sorterar en kolumn med namn baserat på efternamnet.
Så låt oss komma igång!
Extrahera och sortera efter efternamn med hjälp av Sök och ersätt
Det första steget för att sortera efter efternamn är att få efternamnet i en separat kolumn.
Du kan göra det genom att ersätta allt före efternamnet med ett tomt så att du bara har efternamnet kvar.
Anta att du har en dataset som visas nedan och du vill sortera dessa data alfabetiskt med efternamnet.
Nedan följer stegen för att sortera efter efternamnet:
- Välj datamängden inklusive rubriken (i det här exemplet skulle det vara A1: A10)
- Kopiera den i den intilliggande kolumnen (om den intilliggande kolumnen inte är tom, infoga en ny kolumn och kopiera sedan dessa namn)
- Byt namn på den kopierade kolumnrubriken. I det här exemplet kommer jag att namnge 'Efternamn'
- Välj alla kopierade namn (välj inte rubriken)
- Håll ned kontrollknappen och tryck sedan på H -knappen. Detta öppnar dialogrutan Sök och ersätt.
- I fältet Hitta vad anger du * (asterisk -symbol följt av ett mellanslagstecken)
- Lämna fältet Ersätt med tomt
- Klicka på Ersätt alla. Detta skulle omedelbart ersätta allt förnamn och du kommer att vara kvar med endast efternamn.
Stegen ovan skulle behålla efternamnet och ta bort allt före det. Detta fungerar bra även när du har mellannamn eller prefix (t.ex. Mr. eller Ms).
När du har efternamnen i den intilliggande kolumnen kan du enkelt sortera datamängden (inklusive fullständiga namn) alfabetiskt baserat på efternamnet.
Nedan följer stegen för att sortera efter efternamnet:
- Välj hela datamängden med rubriker (inklusive fullständiga namn och extraherade efternamn). Du kan också inkludera andra kolumner som du vill sortera tillsammans med namnen
- Klicka på fliken Data
- Klicka på Sortera
- I dialogrutan Sortera, se till att ”My data has headers” är markerat.
- I alternativet "Sortera efter" väljer du namnet på den kolumn som bara har efternamnet
- I "Sortera på" väljer du "Cellvärden"
- I alternativet Beställ väljer du "A till Z"
- Klicka på OK
Stegen ovan skulle sortera hela den valda datamängden baserat på efternamnet.
När du är klar kan du radera kolumnen som har efternamnet.
Proffstips: När som helst, om du tror att du kan behöva den ursprungliga informationen tillbaka, måste du ha ett sätt att avsortera denna datamängd. För att göra detta, i en intilliggande kolumn (vänster eller höger), ha serienummer före sorteringen. Nu, om du behöver den ursprungliga data tillbaka, får du det genom att sortera baserat på siffrorna.Extrahera och alfabetisera efter efternamn med hjälp av formel
Även om metoden som visas ovan (med hjälp av Sök och ersätt) är vad jag föredrar att få alla efternamn och sortera baserat på det, är en begränsning av det att de resulterande dataen är statiska.
Det betyder att om jag lägger till fler namn i min lista måste jag göra samma process igen för att få efternamnen.
Om detta är något du inte vill ha kan du använda formelmetoden för att sortera data efter efternamn.
Anta att du har datauppsättningen enligt nedan.
Nedan är formeln som kommer att extrahera efternamnet från det fullständiga namnet:
= HÖGER (A2, LEN (A2) -FIND ("", A2))
Ovanstående formel bygger på mönstret med ett fullständigt namn (som endast innehåller för- och efternamnet i detta exempel). Mönstret är att det skulle finnas ett mellanslag mellan för- och efternamn.
FIND -funktionen används för att få platsen för mellanslagstecknet. Detta värde subtraheras sedan från namnets totala längd för att få det totala antalet tecken i efternamnet.
Detta värde används sedan i HÖGER -funktionen för att få efternamnet.
När du har efternamnkolumnen kan du sortera dessa data (detta omfattas i detalj i den första metoden).
Ovanstående formel fungerar när du bara har för- och efternamn.
Men tänk om du också har ett mellannamn. Eller kan det finnas en hälsning före namnet (som herr eller fru)
I ett sådant fall måste du använda följande formel:
= HÖGER (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))))
Ovanstående formel hittar positionen för det sista mellanslagstecknet och använder det sedan för att extrahera efternamnet.
Jag rekommenderar att du använder den andra formeln i alla fall, och den är mer idiotsäker och kan hantera alla ärenden (så länge efternamnet står i slutet av namnet).
Obs! Dessa två formler är beroende av villkoret att det bara finns ett mellanslagstecken mellan varje namnelement. Om det finns dubbla mellanslag eller ledande/bakre mellanslag, ger denna formel felaktiga resultat. I ett sådant fall är det bäst att använda TRIM -funktionen för att först bli av med alla ledande, bakre och dubbla mellanslag och sedan använda ovanstående formel.
Även om detta kan verka som en komplicerad metod, är fördelen med att använda en formel att det gör resultaten dynamiska. Om du lägger till fler namn i listan behöver du bara kopiera formeln så får du efternamnet.
Använda text till kolumner
Text till kolumner är återigen ett enkelt och enkelt sätt att dela celler i Excel.
Du kan ange avgränsaren (t.ex. komma eller mellanslag) och använda den för att dela cellens innehåll. När du har delade element i separata kolumner kan du använda kolumnen som har efternamnet för att alfabetisera data.
Anta att du har en dataset som visas nedan:
Nedan följer stegen för att använda Text till kolumn för att sortera efter efternamnet:
- Välj den kolumn som har namnet (exklusive rubriken)
- Klicka på fliken Data
- Klicka på alternativet Text till kolumner i gruppen "Dataverktyg". Detta öppnar guiden Text till kolumner
- I steg 1 i guiden "Konvertera text till kolumner" väljer du "Avgränsad" och klickar på Nästa
- I steg 2, välj "Mellanslag" som avgränsare (och avmarkera allt annat om det är markerat) och klicka sedan på knappen Nästa.
- I steg 3 väljer du kolumnen förnamn i förhandsgranskningen av data och väljer sedan alternativet "Importera inte kolumner (hoppa över)". Detta säkerställer att förnamnet inte är en del av resultatet och att du bara får efternamnet.
- Också i steg 3, ändra målcellen till den som ligger intill originaldata. Detta kommer att se till att du får efternamnet separat och originalnamnsdata är intakta.
- Klicka på Slutför
När du har fått resultatet kan du sortera efter efternamnet.
Du kan också Text till kolumner för att separera för- och efternamn när du har ett komma som avgränsare.
Använda Flash Fill
Ett annat snabbt och snabbt sätt att få efternamnen är att använda Flash Fill -funktionen.
Flash Fill introducerades i Excel 2013 och det hjälper till att manipulera data genom att identifiera mönster. För att detta ska fungera måste du visa Flash Fyll det resultat du förväntar dig ett par gånger.
När det väl har identifierat mönstret gör det snabbt resten av arbetet åt dig.
Anta att du har nedanstående datauppsättning.
Nedan följer stegen för att använda Flash Fill för att få efternamnet och sedan sortera med det:
- I cell B2 anger du texten "Maury". Detta är resultatet du förväntar dig i cellen.
- Gå till nästa cell och ange efternamnet för namnet i den intilliggande cellen (Elliot i detta exempel).
- Markera båda cellerna
- Håll markören över den nedre högra delen av markeringen. Du kommer att märka att markören ändras till en plusikon.
- Dubbelklicka på den (eller klicka och dra den ner). Detta ger dig lite resultat i cellerna (troligen inte det resultat du vill ha)
- Klicka på ikonen Autofyllningsalternativ.
- Klicka på Flash Fill
Detta ger dig resultatet som sannolikt kommer att vara efternamnen i alla celler.
Jag säger sannolikt, eftersom Flash Fill kanske inte fungerar i vissa fall. Eftersom det beror på att identifiera ett mönster, kanske det inte alltid kan göra det. Eller ibland är mönstret som det avkodar kanske inte det rätta.
I sådana fall bör du ange ett förväntat resultat i en eller två celler till och sedan göra steg 4-7.
När du har alla efternamnen i en kolumn kan du sortera data baserat på dessa efternamn.
Så det här är fyra olika sätt som du kan använda för att sortera data efter efternamnet. Den bästa metoden skulle vara att använda tekniken Sök och ersätt, men om du vill göra dina resultat dynamiska är formelmetoden vägen att gå.
Hoppas du tyckte att denna handledning var användbar.
