Slik sorterer du etter etternavn i Excel (Enkel veiledning)

Hvis du jobber med et datasett med navn, er sortering av det en av de vanlige oppgavene du må utføre ofte.

Det er enkelt å sortere data alfabetisk etter fullt navn, der Excel bruker det første tegnet i navnet for å sortere.

Men hvis du vilSorter data etter etternavn i Excelkom påsekvenshva å gjøre?

Selv om det ikke er så enkelt, kan det fortsatt gjøres (avhenger også mye av hvordan navnedataene er strukturert).

Uansett hvilken metode du bruker, må du på en eller annen måte trekke ut etternavnet fra det fulle navnet og sette det i en egen kolonne.Du kan deretter bruke denne kolonnen til å sortere dataene alfabetisk etter etternavn.

I denne Excel-opplæringen skal jeg vise deg hvordan du sorterer en kolonne med navn basert på etternavn.

Så la oss komme i gang!

Trekk ut og sorter etter etternavn ved å bruke Finn og erstatt

Det første trinnet for å sortere etter etternavn er å få etternavnet i en egen kolonne.

Du kan gjøre dette ved å erstatte alt før etternavnet med blanke felter, så du sitter igjen med bare etternavnet.

Anta at du har et datasett som det nedenfor, og du vil sortere disse dataene alfabetisk etter etternavn.

Her er trinnene for å sortere etter etternavn:

1. Velg datasettet som inneholder tittelen (i dette tilfellet vil det være A1:A10)
2. kopier den inn i den tilstøtende kolonnen (hvis den tilstøtende kolonnen ikke er tom, sett inn en ny kolonne, og kopier deretter navnene)
3. Gi nytt navn til de kopierte kolonneoverskriftene.I dette eksemplet vil jeg kalle det "Etternavn"
4. Velg alle kopierte navn (ikke velg titler)
5. Hold nede Ctrl-tasten, og trykk deretter på H-tasten.Dette åpner dialogboksen Finn og erstatt.
6. I Finn hva-feltet skriver du inn * (en stjerne etterfulgt av et mellomrom)
7. La feltet Erstatt med stå tomt
8. Klikk Erstatt alle.Dette vil erstatte alle fornavn på en gang, og du vil bare sitte igjen med etternavn.

Trinnene ovenfor vil beholde etternavnet og slette alt før det.Dette fungerer selv om du har et mellomnavn eller prefiks som Mr. eller Ms.

Når du har etternavn i tilstøtende kolonner, kan du enkelt sortere datasettet (inkludert fulle navn) alfabetisk etter etternavn.

Her er trinnene for å sortere etter etternavn:

1. Velg hele datasettet med titler (inkludert fulle navn og utpakkede etternavn).Du kan også inkludere andre kolonner for å sortere med navnet
2. Klikk på Data-fanen
3. Klikk for å sortere
4. I Sorter-dialogen må du kontrollere at Mine data har overskrifter er merket av.
5. I alternativet "Sorter etter" velger du navnet på kolonnen med bare etternavn
6. I Sorter etter velger du Celleverdi
7. I "Bestill"-alternativet, velg "A til Å"
8. Klikk OK

Trinnene ovenfor vil sortere hele det valgte datasettet basert på etternavn.

Når du er ferdig, kan du slippe kolonnen med etternavnet.

Profesjonelle tips: Når som helst du tror du må gå tilbake til de opprinnelige dataene, må du ha en måte å avsortere det datasettet på.For dette er det i tilstøtende kolonner (venstre eller høyre) sekvensnummer før sortering.Nå, hvis du trenger å få tilbake de opprinnelige dataene, kan du få dem ved å sortere basert på tall.

Trekk ut og sorter alfabetisk etter etternavn ved hjelp av formel

Mens metoden vist ovenfor (ved å bruke finn og erstatt) er det jeg foretrekker å få alle etternavn og sortere basert på det, er en av begrensningene at de resulterende dataene er statiske.

Dette betyr at hvis jeg legger til flere fornavn på listen, må jeg gå gjennom samme prosess på nytt for å få etternavnet.

Hvis dette ikke er det du ønsker, kan du bruke en formelmetode for å sortere dataene etter etternavn.

Anta at du har datasettet vist nedenfor.

Her er formelen for å trekke ut etternavnet fra det fulle navnet:

=HØYRE(A2,LEN(A2)-FINN(" ",A2))

Formelen ovenfor er avhengig av et mønster med fulle navn (i dette eksemplet bare for- og etternavn).Mønsteret er at det blir et mellomrom mellom for- og etternavn.

FINN-funksjonen brukes til å finne plassering av mellomromstegnet.Denne verdien trekkes deretter fra den totale lengden på fornavnet for å få det totale antallet tegn i etternavnet.

Bruk deretter verdien i HØYRE-funksjonen for å få etternavnet.

Når du har etternavnskolonnen, kan du sortere disse dataene (dette er detaljert i den første metoden).

Formelen ovenfor vil fungere når du bare har for- og etternavn.

Men hva om du også har et mellomnavn.eller kan ha en hilsen før navnet (f.eks. Mr. eller Ms.)

I dette tilfellet må du bruke følgende formel:

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

Formelen ovenfor finner posisjonen til det siste mellomromstegn og bruker det deretter til å trekke ut etternavnet.

Jeg foreslår at du bruker den andre formelen i alle tilfeller, den er enklere og håndterer alle tilfeller (så lenge etternavnet står på slutten av fornavnet).

Merk: Disse to formlene er avhengige av at det bare er ett mellomrom mellom hvert navneelement.Denne formelen vil gi feil resultater hvis det er doble mellomrom eller innledende/etterfølgende mellomrom.I dette tilfellet er det best å bruke TRIM-funksjonen for å fjerne eventuelle innledende, etterfølgende og doble mellomrom først, og deretter bruke formelen ovenfor.

Selv om dette kan virke som en komplisert tilnærming, er fordelen med å bruke formler at det gjør resultatene dynamiske.Hvis du legger til flere navn på listen, er alt du trenger å gjøre å kopiere formelen og den vil gi deg etternavnet.

Bruk tekst til kolonner

Tekst til kolonner er nok en enkel måte å dele celler i Excel.

Du kan spesifisere et skilletegn (som et komma eller et mellomrom) og bruke det til å dele innholdet i en celle.Etter å ha delt elementer i separate kolonner, kan du bruke kolonnen med etternavnet til å alfabetisere dataene.

Anta at du har et datasett som ser slik ut:

Her er trinnene for å sortere etter etternavn ved å bruke Tekst til kolonne:

1. Velg kolonner med navn (unntatt overskrifter)
2. Klikk på Data-fanen
3. I Dataverktøy-gruppen klikker du på alternativet Tekst til kolonner.Dette vil åpne tekst til kolonner-veiviseren
4. I trinn 1 i veiviseren Konverter tekst til kolonner velger du Avgrenset og klikker på Neste
5. I trinn 2, velg "Mellomrom" som skilletegn (fjern merket for noe annet hvis det er merket av) og klikk på Neste-knappen.
6. I trinn 3 velger du fornavnskolonnen i dataforhåndsvisningen, og velger deretter alternativet "Ikke importer kolonne (hopp over)".Dette sikrer at fornavnet ikke er en del av resultatet, du får kun etternavnet.
7. Også i trinn 3 endrer du målcellen til cellen ved siden av de opprinnelige dataene.Dette vil sikre at du får etternavnet alene og at de originale navnedataene er fullstendige.
8. Klikk Fullfør

Når du har resultatene, kan du sortere etter etternavn.

Du kan også bruke tekst-til-kolonne for å skille for- og etternavn når du har komma som skilletegn.

Fyll med glitter

En annen rask måte å få etternavnet på er å bruke Flash Fill-funksjonen.

Flash Fill, introdusert i Excel 2013, hjelper med å manipulere data ved å identifisere mønstre.For å gjøre dette må du vise Flash Fill flere ganger for resultatet du forventer.

Når den gjenkjenner mønsteret, gjør den raskt resten for deg.

Anta at du har følgende datasett med navn.

Her er trinnene for å få etternavnet ved å bruke Flash Fill og bruke det til å sortere:

1. I celle B2 skriver du inn teksten "Maury".Dette er hva du kan forvente i en celle.
2. Gå til neste celle og skriv inn etternavnet til navnet i den tilstøtende cellen (Elliot i dette tilfellet).
3. velg to celler
4. Hold markøren over den nedre høyre delen av utvalget.Du vil legge merke til at markøren endres til et plussikon.
5. Dobbeltklikk på den (eller klikk og dra ned).Dette vil gi deg et resultat i cellen (usannsynlig det du vil ha)
6. Klikk på ikonet Alternativer for autofyll.
7. Klikk Hurtigfyll

Dette vil gi deg et resultat som kan være etternavnet i alle celler.

Jeg sier kanskje fordi Flash Fill kanskje ikke fungerer i noen tilfeller.Siden det avhenger av gjenkjenningsmodusen, kan det hende den ikke alltid gjør dette.Eller noen ganger kan det hende at mønsteret den tyder ikke er det riktige mønsteret.

I dette tilfellet bør du legge inn det forventede resultatet i en eller flere celler og deretter gå gjennom trinn 4-7.

Etter at du har alle etternavnene i kolonnen, kan du sortere dataene basert på disse etternavnene.

Derfor kan du sortere dataene etter etternavn på fire forskjellige måter.Den beste måten er å bruke en finn og erstatt-teknikk, men hvis du vil gjøre resultatene dine dynamiske, er formelmetoden veien å gå.

Håper du fant denne opplæringen nyttig.