Slik sorterer du etter etternavn i Excel (enkel guide)

Se video - Slik sorterer du etter etternavn i Excel

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

Det er ganske enkelt å sortere data alfabetisk basert på hele navnet, der Excel bruker det første tegnet i navnet til å sortere.

Men hva om du vil sorter data etter etternavn i Excel?

Selv om det ikke er like enkelt, kan det fortsatt gjøres (mye avhenger også av måten navnedata er strukturert på).

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

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

Så la oss komme i gang!

Pakk ut og sorter etter etternavn ved hjelp av Finn og erstatt

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

Du kan gjøre det ved å erstatte alt før etternavnet med et tomt, slik at du bare har etternavnet igjen.

Anta at du har et datasett som vist nedenfor, og du vil sortere disse dataene alfabetisk ved å bruke etternavnet.

Nedenfor er trinnene for å sortere etter etternavn:

1. Velg datasettet inkludert overskriften (i dette eksemplet vil det være A1: A10)
2. Kopier den i den tilstøtende kolonnen (hvis den tilstøtende kolonnen ikke er tom, sett inn en ny kolonne og kopier deretter disse navnene)
3. Gi nytt navn til den kopierte kolonneoverskriften. I dette eksemplet vil jeg nevne 'Etternavn'
4. Velg alle de kopierte navnene (ikke velg overskriften)
5. Hold inne Ctrl -tasten og trykk deretter på H -tasten. Dette åpner dialogboksen Finn og erstatt.
6. I feltet Finn hva skriver du inn * (stjerne -symbol etterfulgt av et mellomrom)
7. La feltet Erstatt med tomt være
8. Klikk på Erstatt alle. Dette ville umiddelbart erstatte alt fornavnet, og du vil bare sitte igjen med etternavn.

De ovennevnte trinnene vil beholde etternavnet og fjerne alt før det. Dette fungerer bra selv når du har mellomnavn eller prefikser (for eksempel Mr. eller Ms).

Når du har etternavnene i den tilstøtende kolonnen, kan du enkelt sortere datasettet (inkludert hele navnene) alfabetisk basert på etternavnet.

Nedenfor er trinnene for å sortere etter etternavn:

1. Velg hele datasettet med overskrifter (inkludert de fulle navnene og de ekstraherte etternavnene). Du kan også inkludere andre kolonner du vil sortere sammen med navnene
2. Klikk kategorien Data
3. Klikk på Sorter
4. I dialogboksen Sorter, kontroller at ‘Mine data har overskrifter’ er valgt.
5. I alternativet "Sorter etter" velger du navnet på kolonnen som nettopp har etternavnet
6. Velg "Celleverdier" i "Sorter på"
7. Velg "A til Å" i alternativet Bestill.
8. Klikk OK

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

Når du er ferdig, kan du slette kolonnen som har etternavnet.

Pro tips: Når som helst du tror du kan trenge de originale dataene tilbake, må du ha en måte å fjerne sorteringen av dette datasettet. For å gjøre dette, i en tilstøtende kolonne (venstre eller høyre), ha serienumre før sorteringen. Hvis du trenger de originale dataene tilbake, får du dem ved å sortere basert på tallene.

Pakk ut og alfabetiser etter etternavn ved hjelp av formel

Selv om metoden som er vist ovenfor (ved hjelp av Finn og erstatt) er det jeg foretrekker å få alle etternavnene og sortere basert på den, er en begrensning av den at de resulterende dataene er statiske.

Dette betyr at hvis jeg legger til flere navn på listen min, må jeg gjøre den samme prosessen igjen for å få etternavnene.

Hvis dette er noe du ikke vil, kan du bruke formelmetoden til å sortere data etter etternavn.

Anta at du har datasettet som vist nedenfor.

Nedenfor er formelen som vil trekke etternavnet ut fra det fulle navnet:

= HØYRE (A2, LENG (A2) -FIND ("", A2))

Formelen ovenfor er avhengig av mønsteret med et fullt navn (som bare inneholder for- og etternavnet i dette eksemplet). Mønsteret er at det vil være et mellomrom mellom fornavn og etternavn.

FIND -funksjonen brukes for å få posisjonen til mellomromstegnet. Denne verdien blir deretter trukket fra den totale lengden på navnet for å få det totale antallet tegn i etternavnet.

Denne verdien brukes deretter i RIGHT -funksjonen for å få etternavnet.

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

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

Men hva om du også har et mellomnavn. Eller kan det være en hilsen foran navnet (for eksempel Mr eller Ms.)

I et slikt tilfelle må du bruke formelen nedenfor:

= HØYRE (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))))

Formelen ovenfor finner plasseringen til det siste mellomromstegnet og bruker det deretter til å trekke ut etternavnet.

Jeg anbefaler at du bruker den andre formelen i alle tilfellene, og den er mer idiotsikker og kan håndtere alle saker (så lenge etternavnet er på slutten av navnet).

Merk: Disse to formlene er avhengig av at det bare er ett mellomrom mellom hvert navnelement. I tilfelle det er doble mellomrom eller ledende/bakre mellomrom, vil denne formelen gi uriktige resultater. I et slikt tilfelle er det best å bruke TRIM -funksjonen for å først bli kvitt eventuelle mellomrom, etterfølgende og doble mellomrom, og deretter bruke formelen ovenfor.

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

Bruke tekst til kolonner

Tekst til kolonner er igjen en enkel og enkel måte å dele celler i Excel på.

Du kan spesifisere skilletegn (for eksempel komma eller mellomrom) og bruke den til å dele innholdet i cellen. Når du har delelementene i separate kolonner, kan du bruke kolonnen som har etternavnet til å alfabetisere dataene.

Anta at du har et datasett som vist nedenfor:

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

1. Velg kolonnen som har navnet (unntatt overskriften)
2. Klikk kategorien Data
3. Klikk på alternativet Tekst til kolonner i gruppen "Dataverktøy". Dette åpner veiviseren for tekst til kolonner
4. I trinn 1 i "Konverter tekst til kolonner -veiviseren", velg "Avgrenset" og klikk på Neste
5. I trinn 2, velg ‘Mellomrom’ som skilletegn (og fjern merket for alt annet hvis det er valgt), og klikk deretter på Neste -knappen.
6. I trinn 3, velg fornavnskolonnen i forhåndsvisningen av data, og velg deretter alternativet "Ikke importer kolonner (hopp over)". Dette sikrer at fornavnet ikke er en del av resultatet, og du får bare etternavnet.
7. Også i trinn 3, endre målcellen til den som ligger ved siden av de originale dataene. Dette vil sørge for at du får etternavnet separat og dataene for originale navn er intakte.
8. Klikk på Fullfør

Når du har resultatet, kan du sortere etter etternavnet.

Du kan også skrive til kolonner for å skille fornavn og etternavn når du har et komma som skilletegn.

Bruke Flash Fill

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

Flash Fill ble introdusert i Excel 2013, og det hjelper å manipulere dataene ved å identifisere mønstre. For at dette skal fungere, må du vise Flash Fyll resultatet du forventer et par ganger.

Når det har identifisert mønsteret, vil det raskt gjøre resten av arbeidet for deg.

Anta at du har datasettet nedenfor.

Nedenfor er trinnene for å bruke Flash Fill for å få etternavnet og deretter sortere ved hjelp av det:

1. Skriv inn teksten ‘Maury’ i celle B2. Dette er resultatet du forventer i cellen.
2. Gå til neste celle og skriv inn etternavnet for navnet i cellen ved siden av (Elliot i dette eksemplet).
3. Velg begge cellene
4. Hold markøren over delen nederst til høyre i utvalget. Du vil merke at markøren endres til et plussikon.
5. Dobbeltklikk på den (eller klikk og dra den ned). Dette vil gi deg et resultat i cellene (sannsynligvis ikke det resultatet du ønsker)
6. Klikk på ikonet Autofyll -alternativer.
7. Klikk på Flash Fill

Dette vil gi deg resultatet som sannsynligvis vil være etternavnene i alle cellene.

Jeg sier sannsynlig, siden Flash Fill kanskje ikke fungerer i noen tilfeller. Siden det er avhengig av å identifisere et mønster, kan det hende at det ikke alltid er i stand til å gjøre det. Eller noen ganger kan det hende at mønsteret det avkoder ikke er det riktige.

I slike tilfeller bør du skrive inn et forventet resultat i en eller to celler til og deretter utføre trinn 4-7.

Når du har alle etternavnene i en kolonne, kan du sortere dataene basert på disse etternavnene.

Så dette er fire forskjellige måter du kan bruke til å sortere data etter etternavn. Den beste metoden ville være å bruke Finn og erstatt -teknikken, men hvis du vil gjøre resultatene dynamiske, er formelmetoden veien å gå.

Håper du synes denne opplæringen var nyttig.