Se video - Sådan sorteres efter efternavn i Excel
Hvis du arbejder med navnesæt, er sortering det en af de almindelige opgaver, du ofte skal udføre.
Det er ret let at sortere data alfabetisk baseret på det fulde navn, hvor Excel bruger det første tegn i navnet til at sortere.
Men hvad nu hvis du vil sorter data efter efternavn i Excel?
Selvom det ikke er lige så ligetil, kan det stadig gøres (meget afhænger også af den måde, navnedata er struktureret på).
Uanset hvilken metode du bruger, skal du på en eller anden måde udtrække efternavnet fra det fulde navn og sætte det i en separat kolonne. Du kan derefter bruge denne kolonne til at sortere dine data efter efternavn alfabetisk.
I denne Excel -tutorial viser jeg dig, hvordan du sorterer en kolonne med navne baseret på efternavnet.
Så lad os komme i gang!
Uddrag og sorter efter efternavn ved hjælp af Find og erstat
Det første trin til at sortere efter efternavnet er at få efternavnet i en separat kolonne.
Du kan gøre det ved at erstatte alt før efternavnet med et tomt, så du kun har efternavnet tilbage.
Antag, at du har et datasæt som vist nedenfor, og du vil sortere disse data alfabetisk ved hjælp af efternavnet.
Nedenfor er trinene til at sortere efter efternavnet:
- Vælg datasættet inklusive overskriften (i dette eksempel ville det være A1: A10)
- Kopier den i den tilstødende kolonne (hvis den tilstødende kolonne ikke er tom, skal du indsætte en ny kolonne og derefter kopiere disse navne)
- Omdøb det kopierede kolonneoverskrift. I dette eksempel vil jeg navngive 'Efternavn'
- Vælg alle de kopierede navne (vælg ikke overskriften)
- Hold Ctrl -tasten nede, og tryk derefter på H -tasten. Dette åbner dialogboksen Find og erstat.
- I feltet Find hvad skal du indtaste * (stjerne -symbol efterfulgt af et mellemrumstegn)
- Lad feltet Erstat med tomt
- Klik på Erstat alle. Dette ville øjeblikkeligt erstatte alt fornavn, og du vil kun have efternavne tilbage.
Ovenstående trin ville beholde efternavnet og fjerne alt før det. Dette fungerer godt, selvom du har mellemnavne eller præfikser (f.eks. Hr. Eller fru).
Når du har de sidste navne i den tilstødende kolonne, kan du nemt sortere datasættet (inklusive de fulde navne) alfabetisk baseret på efternavnet.
Nedenfor er trinene til at sortere efter efternavnet:
- Vælg hele datasættet med overskrifter (inklusive de fulde navne og de ekstraherede efternavne). Du kan også inkludere andre kolonner, som du vil sortere sammen med navnene
- Klik på fanen Data
- Klik på Sorter
- Sørg for, at 'Mine data har overskrifter' i dialogboksen Sort.
- I indstillingen ‘Sorter efter’ skal du vælge navnet på den kolonne, der lige har efternavnet
- I 'Sort On' skal du vælge 'Celleværdier'
- I indstillingen Ordre skal du vælge 'A til Z'
- Klik på OK
Ovenstående trin sorterer hele det valgte datasæt baseret på efternavnet.
Når det er gjort, kan du slette den kolonne, der har efternavnet.
Pro tip: På ethvert tidspunkt, hvis du tror, at du muligvis har brug for de originale data tilbage, skal du have en måde at fjerne sorteringen af dette datasæt på. For at gøre dette skal du have serienumre i en tilstødende kolonne (venstre eller højre) før sorteringen. Nu, hvis du har brug for de originale data tilbage, får du dem ved at sortere baseret på tallene.Uddrag og alfabetiser efter efternavn ved hjælp af formel
Selvom metoden, der er vist ovenfor (ved hjælp af Find og erstat) er, hvad jeg foretrækker at få alle efternavne og sortere baseret på den, er en begrænsning af den, at de resulterende data er statiske.
Det betyder, at hvis jeg tilføjer flere navne til min liste, bliver jeg nødt til at gøre den samme proces igen for at få de sidste navne.
Hvis dette er noget, du ikke ønsker, kan du bruge formelmetoden til at sortere data efter efternavne.
Antag, at du har datasættet som vist nedenfor.
Nedenfor er formlen, der vil udtrække efternavnet fra det fulde navn:
= HØJRE (A2, LEN (A2) -FIND ("", A2))
Ovenstående formel er afhængig af mønsteret med et fuldt navn (der kun indeholder for- og efternavnet i dette eksempel). Mønsteret er, at der ville være et mellemrum mellem for- og efternavn.
FIND -funktionen bruges til at få placeringen af mellemrumstegnet. Denne værdi trækkes derefter fra den samlede længde af navnet for at få det samlede antal tegn i efternavnet.
Denne værdi bruges derefter i HØJRE -funktionen til at få efternavnet.
Når du har efternavnskolonnen, kan du sortere disse data (dette er detaljeret beskrevet i den første metode).
Ovenstående formel fungerer, når du kun har for- og efternavne.
Men hvad nu hvis du også har et mellemnavn. Eller er der en hilsen før navnet (f.eks. Hr. Eller fru)
I et sådant tilfælde skal du bruge nedenstående formel:
= HØJRE (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))))
Ovenstående formel finder placeringen af det sidste mellemrumstegn og bruger det derefter til at udtrække efternavnet.
Jeg anbefaler, at du bruger den anden formel i alle sagerne, og den er mere fjolsikker og kan håndtere alle sager (så længe efternavnet er i slutningen af navnet).
Bemærk: Disse to formler er afhængige af, at der kun er et mellemrumstegn mellem hvert navnelement. Hvis der er dobbelt mellemrum, eller førende/bageste mellemrum, vil denne formel give forkerte resultater. I et sådant tilfælde er det bedst at bruge TRIM -funktionen til først at slippe af med alle mellemliggende, efterfølgende og dobbelte mellemrum og derefter bruge ovenstående formel.
Selvom dette kan virke som en kompliceret metode, er fordelen ved at bruge en formel, at det gør resultaterne dynamiske. Hvis du tilføjer flere navne til din liste, skal du blot kopiere formlen, og det vil give dig efternavnet.
Brug af tekst til kolonner
Tekst til kolonner er igen en enkel og let måde at opdele celler i Excel på.
Du kan angive afgrænsningen (f.eks. Komma eller mellemrum) og bruge den til at dele cellens indhold. Når du har de opdelte elementer i separate kolonner, kan du bruge den kolonne, der har efternavnet, til at alfabetisere dataene.
Antag, at du har et datasæt som vist herunder:
Nedenfor er trinene til at bruge tekst til kolonne til at sortere efter efternavnet:
- Vælg den kolonne, der har navnet (eksklusive overskriften)
- Klik på fanen Data
- I gruppen 'Dataværktøjer' skal du klikke på indstillingen Tekst til kolonner. Dette åbner guiden Tekst til kolonner
- I trin 1 i guiden 'Konverter tekst til kolonner' skal du vælge 'Afgrænset' og klikke på Næste
- I trin 2 skal du vælge 'Mellemrum' som afgrænsning (og fjerne markeringen for alt andet, hvis det er valgt), og derefter klikke på knappen Næste.
- I trin 3 skal du vælge kolonnen med fornavn i forhåndsvisningen af data og derefter vælge "Importer ikke kolonner (spring over)". Dette sikrer, at fornavnet ikke er en del af resultatet, og du får kun efternavnet.
- Også i trin 3 skal du ændre destinationscellen til den, der støder op til de originale data. Dette vil sikre, at du får efternavnet separat, og de originale navnedata er intakte.
- Klik på Afslut
Når du har resultatet, kan du sortere efter efternavnet.
Du kan også skrive til kolonner for at adskille for- og efternavne, når du har et komma som adskiller.
Brug af Flash Fill
En anden hurtig og hurtig måde at få efternavne på er ved hjælp af Flash Fill -funktionen.
Flash Fill blev introduceret i Excel 2013, og det hjælper med at manipulere dataene ved at identificere mønstre. For at dette skal fungere, skal du vise Flash Fyld det resultat, du forventer et par gange.
Når det har identificeret mønsteret, vil det hurtigt udføre resten af arbejdet for dig.
Antag, at du har nedenstående datasæt med navne.
Nedenfor er trinene til at bruge Flash Fill til at få efternavnet og derefter sortere ved hjælp af det:
- I celle B2 skal du indtaste teksten 'Maury'. Dette er det resultat, du forventer i cellen.
- Gå til den næste celle, og indtast efternavnet for navnet i den tilstødende celle (Elliot i dette eksempel).
- Vælg begge celler
- Hold markøren over den nederste højre del af markeringen. Du vil bemærke, at markøren ændres til et plusikon.
- Dobbeltklik på den (eller klik og træk den ned). Dette vil give dig et resultat i cellerne (sandsynligvis ikke det resultat, du ønsker)
- Klik på ikonet AutoFill Options.
- Klik på Flash Fill
Dette vil give dig det resultat, der sandsynligvis vil være efternavne i alle cellerne.
Jeg siger sandsynligvis, da Flash Fill muligvis ikke virker i nogle tilfælde. Da det afhænger af at identificere et mønster, er det muligvis ikke altid i stand til at gøre det. Eller nogle gange er mønsteret, det dekrypterer, måske ikke det rigtige.
I sådanne tilfælde skal du indtaste et forventet resultat i en eller to celler mere og derefter udføre trin 4-7.
Når du har alle efternavne i en kolonne, kan du sortere dataene baseret på disse efternavne.
Så det er fire forskellige måder, du kan bruge til at sortere data efter efternavnet. Den bedste metode ville være at bruge Find og erstat -teknikken, men hvis du vil gøre dine resultater dynamiske, er formelmetoden vejen frem.
Håber du fandt denne vejledning nyttig.
