Madame Lelica

titta på Video – Hur man sorterar efter efternamn i Excel

om du arbetar med namndataset är sortering en av de vanliga uppgifterna du skulle behöva göra ofta.

det är ganska lätt 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 vad händer om du vill sortera data efter efternamn i Excel?,

Även om det inte är så enkelt kan det fortfarande göras (mycket beror också på hur namndata är strukturerad).

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 efter efternamn alfabetiskt.

i denna Excel-handledning visar jag dig hur du sorterar en kolumn med namn baserat på efternamn.

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 till sortering 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 datauppsättning som visas nedan och du vill sortera dessa data i alfabetisk ordning med efternamn.,

nedan är stegen för att Sortera efter efternamn:

1. Välj datauppsättningen inklusive rubriken (i det här exemplet skulle det vara A1:A10)
2. kopiera det i den intilliggande kolumnen (om den intilliggande kolumnen inte är tom, sätt in en ny kolumn och kopiera sedan dessa namn)
3. Byt namn på den kopierade kolumnrubriken. I det här exemplet kommer jag att namnge ”efternamn”
4. Välj alla kopierade namn (välj inte rubriken)
5. Håll kontrollknappen och tryck sedan på H-tangenten. Detta öppnar dialogrutan Sök och ersätt.,
6. i fältet hitta vad anger du * (asterisksymbol följt av ett mellanslagstecken)
7. lämna Ersätt med fältet tomt
8. Klicka på Ersätt alla. Detta skulle omedelbart ersätta alla förnamn och du kommer att lämnas med efternamn bara.

ovanstående steg skulle behålla efternamn och ta bort allt innan det. Detta fungerar bra även när du har mellannamn eller prefix (som MR eller Ms).,

När du har efternamn i den intilliggande kolumnen kan du enkelt sortera datauppsättningen (inklusive fullständiga namn) alfabetiskt baserat på efternamnet.

nedan är stegen för att Sortera efter efternamn:

1. Välj hela datauppsättningen med rubriker (inklusive fullständiga namn och extraherade efternamn)., Du kan också inkludera andra kolumner som du vill sortera tillsammans med namnen
2. Klicka på fliken Data
3. Klicka på Sortera
4. i dialogrutan Sortera, se till att ”mina data har rubriker” är markerat.,
5. I alternativet”Sortera efter”väljer du namnet på kolumnen som bara har efternamnet
6. I alternativet”Sortera På”väljer du” cellvärden ”
7. i Orderalternativet väljer du ”A till Z”
8. Klicka på OK

ovanstående steg skulle sortera hela den valda datauppsättningen baserat på efternamn.

När du är klar kan du ta bort kolumnen med efternamn.,

extrahera och alfabetisera efter efternamn med Formel

medan 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, en begränsning av det är att de resulterande data i statisk.,

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å efternamn.

om detta är något du inte vill kan du använda formelmetoden för att sortera data efter efternamn.

anta att du har datauppsättningen som visas nedan.

nedan är formeln som kommer att extrahera efternamnet från det fullständiga namnet:

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

ovanstående formel bygger på mönstret med ett fullständigt namn (som bara innehåller för-och efternamn i det här exemplet)., Mönstret är att det skulle finnas ett mellanslag mellan för-och efternamn.

sökfunktionen används för att få positionen för mellanslagstecknet. Detta värde subtraheras sedan från den totala längden av namnet för att få det totala antalet tecken i efternamn.

detta värde används sedan i rätt funktion för att få efternamnet.

När du har kolumnen efternamn kan du sortera dessa data (detta omfattas i den första metoden i detalj).

ovanstående formel skulle fungera när du bara har för-och efternamn.

men vad händer om du har ett mellannamn också., Eller kan det finnas en hälsning före namnet (till exempel Mr eller ms)

i så fall måste du använda nedanstående formel:

=RIGHT(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 för att extrahera efternamn.

Jag rekommenderar att du använder den andra formeln i alla fall, och det är mer idiotsäkert och kan hantera alla fall (så länge efternamnet är i slutet av namnet).

OBS: Dessa två formler är beroende av villkoret att det bara finns ett mellanslag mellan varje namnelement., Om det finns dubbla utrymmen, eller ledande/avslutande utrymmen, kommer denna formel att ge felaktiga resultat. I så fall är det bäst att använda TRIMFUNKTIONEN för att först bli av med alla ledande, efterföljande och dubbla utrymmen och använd sedan 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 din lista, allt du behöver göra är att kopiera formeln och det kommer att ge dig efternamn.

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. kommatecken eller mellanslag) och använda den för att dela upp cellens innehåll. När du har de delade elementen i separata kolumner kan du använda kolumnen som har efternamn för att alfabetisera data.

anta att du har en datauppsättning som visas nedan:

nedan är stegen för att använda Text till kolumn för att Sortera efter efternamn:

1. välj kolumnen som har namnet (exklusive rubriken)
2. Klicka på fliken Data
3. i gruppen dataverktyg klickar du på alternativet Text till kolumner., Detta öppnar Text till Kolumnguiden
4. i steg 1 i”Konvertera Text till Kolumnguiden”väljer du” avgränsad ”och klickar på Nästa
5. i steg 2 väljer du ”Mellanslag” som avgränsare (och avmarkerar allt annat om markerat) och klickar sedan på Nästa knapp.
6. i steg 3 väljer du kolumnen förnamn i dataförhandsgranskningen och väljer sedan alternativet ” Importera inte kolumner (hoppa över)”. Detta säkerställer att förnamn inte är en del av resultatet och du får bara efternamn.,
7. i steg 3 ändrar du målcellen till den som ligger intill originaldata. Detta kommer att se till att du får efternamn separat och ursprungliga namn data är intakt.
8. Klicka på Finish

När du har resultatet kan du sortera efter efternamn.

Du kan också Text till kolumner för att separera för-och efternamn när du har ett kommatecken som separator.,

använda Flash Fill

ett annat snabbt och snabbt sätt att få efternamn använder Flash Fill-funktionen.

Flash Fill infördes i Excel 2013 och det hjälper 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 identifierar mönstret kommer det snabbt att göra resten av arbetet för dig.

anta att du har datauppsättningen nedan namn.,

nedan är stegen för att använda Flash Fill för att få efternamnet och sortera sedan med det:

1. i cell B2 anger du texten ”Maury”. Detta är det resultat du förväntar dig i cellen.
2. gå till nästa cell och ange efternamnet för namnet i den intilliggande cellen (Elliot i det här exemplet).
3. välj båda cellerna
4. håll markören över den nedre högra delen av markeringen. Du kommer att märka att markören ändras till en plusikon.
5. dubbelklicka på den (eller klicka och dra ner den)., Detta ger dig ett visst resultat i cellerna (sannolikt inte det resultat du vill ha)
6. Klicka på ikonen AutoFill Options.
7. Klicka på Flash Fill

detta ger dig resultatet som sannolikt kommer att vara de sista namnen 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 kan göra det alltid. Eller ibland, mönstret det dechiffrerar kanske inte den rätta.,

i sådana fall bör du ange ett förväntat resultat i en eller två fler celler och sedan göra steg 4-7.

När du har alla efternamn 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 efternamn. Den bästa metoden skulle vara att använda Sök-och ersätt-tekniken, men om du vill göra dina resultat dynamiska är formelmetoden vägen att gå.

hoppas du hittade den här handledningen användbar.,

Du kanske också gillar följande Excel-handledning:

• hur man sorterar efter färg i Excel
• hur man sorterar kalkylblad i Excel
• hur man sorterar Data i Excel med VBA
• Sortera automatiskt Data i alfabetisk ordning med hjälp av Formel
• hur man gör en Datasortering på flera nivåer i Excel