Kako sortirati prema prezimenu u Excelu (jednostavan vodič)

Pogledajte video - Kako sortirati prema prezimenu u Excelu

Ako radite s skupovima podataka o imenima, sortiranje je jedan od uobičajenih zadataka koje biste morali često raditi.

Lako je sortirati podatke po abecedi na temelju punog imena, pri čemu Excel koristi prvi znak imena za sortiranje.

Ali što ako želite sortirajte podatke prema prezimenu u Excelu?

Iako nije tako jednostavno, ipak se može učiniti (mnogo toga ovisi i o načinu na koji su podaci o imenima strukturirani).

Bez obzira koju metodu koristili, morat ćete nekako izvući prezime iz punog imena i staviti ga u zasebnu kolonu. Zatim možete koristiti ovaj stupac za sortiranje podataka prema prezimenu po abecedi.

U ovom Excel vodiču pokazat ću vam kako poredati stupac s imenima na temelju prezimena.

Pa krenimo!

Izdvojite i sortirajte prema prezimenu pomoću značajke Pronađi i zamijeni

Prvi korak za sortiranje prema prezimenu je da prezime dobijete u posebnom stupcu.

To možete učiniti tako da sve ispred prezimena zamijenite praznim, tako da vam ostane samo prezime.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite ove podatke sortirati po abecednom redu pomoću prezimena.

U nastavku su navedeni koraci za sortiranje prema prezimenu:

1. Odaberite skup podataka uključujući zaglavlje (u ovom primjeru to bi bilo A1: A10)
2. Kopirajte ga u susjedni stupac (ako susjedni stupac nije prazan, umetnite novi stupac, a zatim kopirajte ove nazive)
3. Preimenujte kopirano zaglavlje stupca. U ovom primjeru ime ću nazvati "Prezime"
4. Odaberite sva kopirana imena (ne odaberite zaglavlje)
5. Držite tipku Control, a zatim pritisnite tipku H. Ovo će otvoriti dijaloški okvir Pronađi i zamijeni.
6. U polje Find what unesite * (simbol zvjezdice iza kojeg slijedi razmak)
7. Ostavite polje Zamijeni praznim
8. Pritisnite Zamijeni sve. Ovo bi odmah zamijenilo sva imena i ostat ćete samo s prezimenima.

Gore navedeni koraci zadržali bi prezime i uklonili sve prije njega. Ovo dobro funkcionira čak i ako imate srednja imena ili prefikse (poput Mr. ili Ms).

Nakon što imate prezimena u susjednom stupcu, možete jednostavno sortirati skup podataka (uključujući puna imena) po abecedi na temelju prezimena.

U nastavku su navedeni koraci za sortiranje prema prezimenu:

1. Odaberite cijeli skup podataka s zaglavljima (uključujući puna imena i izdvojena prezimena). Možete dodati i druge stupce koje želite poredati zajedno s imenima
2. Kliknite karticu Podaci
3. Pritisnite Sortiraj
4. U dijaloškom okviru Sortiranje provjerite je li odabrano "Moji podaci imaju zaglavlja".
5. U opciji "Poredaj po" odaberite naziv stupca koji ima samo prezime
6. U "Sortiraj uključeno" odaberite "Vrijednosti ćelije"
7. U opciji Naruči odaberite "A do Z"
8. Pritisnite U redu

Gore navedeni koraci razvrstali bi cijeli odabrani skup podataka na temelju prezimena.

Nakon što završite, možete izbrisati stupac s prezimenom.

Profesionalni savjet: U bilo kojem trenutku, ako mislite da će vam možda trebati izvorni podaci, morate pronaći način za poništavanje sortiranja ovog skupa podataka. Da biste to učinili, u susjednom stupcu (lijevo ili desno) prije sortiranja navedite serijske brojeve. Sada, ako trebate izvorne podatke natrag, dobivate ih razvrstavanjem na temelju brojeva.

Izdvojite i abecedite po prezimenu koristeći formulu

Iako je gore prikazana metoda (pomoću značajke Pronađi i zamijeni) ono što preferiram da dobijem sva prezimena i sortiram na temelju njih, jedno ograničenje je to što rezultirajući podaci u statičkom stanju.

To znači da ću, ako na popis dodam još imena, morati ponoviti isti postupak da bih dobio prezimena.

Ako ovo ne želite, možete upotrijebiti metodu formule za sortiranje podataka prema prezimenima.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku.

Ispod je formula koja će izdvojiti prezime iz punog imena:

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

Gornja formula oslanja se na uzorak s punim imenom (koji u ovom primjeru sadrži samo ime i prezime). Uzorak je takav da bi između imena i prezimena postojao razmak.

Funkcija FIND koristi se za dobivanje pozicije razmaka. Ta se vrijednost zatim oduzima od ukupne duljine imena kako bi se dobio ukupan broj znakova u prezimenu.

Ta se vrijednost zatim koristi u funkciji DESNO za dobivanje prezimena.

Nakon što dobijete stupac s prezimenom, možete sortirati ove podatke (to je detaljno obrađeno u prvoj metodi).

Gornja formula bi funkcionirala kada imate samo ime i prezime.

Ali što ako imate i srednje ime. Ili može postojati pozdrav prije imena (kao što je gospodin ili gospođa)

U tom slučaju morate koristiti donju formulu:

= DESNO (A2, LEN (A2) -FIND ("@", ZAMJENA (A2, "", "@", LEN (A2) -LEN (ZAMJENA (A2, "", ""))))

Gornja formula pronalazi položaj zadnjeg znaka razmaka i zatim ga koristi za izdvajanje prezimena.

Preporučujem da u svim slučajevima koristite drugu formulu, koja je otpornija na budale i može se nositi sa svim slučajevima (sve dok se prezime nalazi na kraju imena).

Napomena: Ove dvije formule oslanjaju se na uvjet da između svakog elementa imena postoji samo jedan razmak. U slučaju da postoje dvostruki razmaci ili vodeći/krajnji razmaci, ova formula će dati netočne rezultate. U tom je slučaju najbolje upotrijebiti funkciju TRIM da biste se najprije riješili svih vodećih, zadnjih i dvostrukih razmaka, a zatim upotrijebili gornju formulu.

Iako se ovo može činiti kompliciranom metodom, prednost korištenja formule je u tome što rezultate čini dinamičkim. Ako na popis dodate još imena, sve što trebate učiniti je kopirati formulu i ona će vam dati prezime.

Korištenje teksta u stupcima

Tekst u stupce opet je jednostavan i lak način za dijeljenje ćelija u Excelu.

Možete navesti razdjelnik (poput zareza ili razmaka) i upotrijebiti ga za podjelu sadržaja ćelije. Nakon što podijeljene elemente postavite u zasebne stupce, možete upotrijebiti stupac s prezimenom za alfabetiziranje podataka.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:

U nastavku su navedeni koraci za korištenje teksta u stupcu za sortiranje prema prezimenu:

1. Odaberite stupac koji ima naziv (isključujući zaglavlje)
2. Kliknite karticu Podaci
3. U grupi ‘Data Tools’ kliknite opciju Text to Columns. Ovo će otvoriti čarobnjaka za tekst u stupce
4. U 1. koraku "Čarobnjaka za pretvaranje teksta u stupce" odaberite "Razgraničeno" i kliknite Dalje
5. U 2. koraku odaberite "Razmak" kao razdjelnik (i poništite sve ostalo ako je odabrano), a zatim kliknite gumb Dalje.
6. U 3. koraku odaberite stupac s imenom u pregledu podataka, a zatim odaberite opciju "Ne uvozi stupce (preskoči)". To osigurava da ime nije dio rezultata i da ćete dobiti samo prezime.
7. Također u koraku 3 promijenite odredišnu ćeliju u onu koja je uz izvorne podatke. To će osigurati da prezime dobijete zasebno, a podaci o izvornim imenima su netaknuti.
8. Kliknite na Finish

Nakon što dobijete rezultat, možete sortirati po prezimenu.

Također možete poslati tekst u stupce da biste odvojili ime i prezime kada imate zarez kao razdjelnik.

Korištenje Flash Fill -a

Još jedan brz i brz način dobivanja prezimena je upotreba značajke Flash Fill.

Flash Fill uveden je u Excel 2013 i pomaže u manipulaciji podacima identificiranjem obrazaca. Da bi ovo funkcioniralo, morate nekoliko puta prikazati rezultat Flash Fill koji očekujete.

Nakon što identificira uzorak, brzo će obaviti ostatak posla umjesto vas.

Pretpostavimo da imate skup podataka imena u nastavku.

U nastavku su navedeni koraci za korištenje Flash Fill -a da biste dobili prezime, a zatim ga sortirali:

1. U ćeliju B2 unesite tekst 'Maury'. Ovo je rezultat koji očekujete u ćeliji.
2. Idite na sljedeću ćeliju i unesite prezime za ime u susjednu ćeliju (Elliot u ovom primjeru).
3. Odaberite obje ćelije
4. Zadržite pokazivač iznad donjeg desnog dijela odabira. Primijetit ćete da se kursor mijenja u ikonu plus.
5. Dvaput kliknite na nju (ili kliknite i povucite prema dolje). To će vam dati neke rezultate u ćelijama (vjerojatno neće biti rezultat koji želite)
6. Kliknite ikonu mogućnosti automatskog popunjavanja.
7. Kliknite na Flash Fill

To će vam dati rezultat koji će vjerojatno biti prezimena u svim ćelijama.

Kažem vjerojatno, jer Flash Fill u nekim slučajevima možda neće raditi. Budući da ovisi o identificiranju uzorka, možda to neće moći učiniti uvijek. Ili ponekad uzorak koji dešifrira možda nije pravi.

U takvim slučajevima trebate unijeti očekivani rezultat u još jednu ili dvije ćelije, a zatim izvršiti korake 4-7.

Nakon što imate sva prezimena u stupcu, možete sortirati podatke na temelju tih prezimena.

Dakle, ovo su četiri različita načina pomoću kojih možete sortirati podatke prema prezimenu. Najbolja metoda bila bi korištenje tehnike Find and Replace (Pronađi i zamijeni), ali ako želite učiniti rezultate dinamičkim, metoda formule je pravi put.

Nadam se da vam je ovaj vodič bio koristan.