Kako razvrstiti po priimku v Excelu (enostaven vodnik)

Oglejte si video - Kako razvrstiti po priimku v Excelu

Če delate z naborom podatkov o imenih, je razvrščanje eno od običajnih opravil, ki bi jih morali pogosto opravljati.

Precej enostavno je razvrščanje podatkov po abecedi glede na polno ime, pri čemer Excel za razvrščanje uporablja prvi znak imena.

Kaj pa, če želite razvrstite podatke po priimku v Excelu?

Čeprav to ni tako preprosto, je še vedno mogoče storiti (veliko je odvisno tudi od načina strukturiranja podatkov o imenih).

Ne glede na to, katero metodo uporabljate, boste morali nekako izvleči priimek iz polnega imena in ga dati v ločen stolpec. Ta stolpec lahko nato uporabite za razvrščanje podatkov po priimku po abecedi.

V tej vadnici Excel vam bom pokazal, kako razvrstite stolpec z imeni glede na priimek.

Pa začnimo!

Izvlecite in razvrstite po priimku s funkcijo Poišči in zamenjaj

Prvi korak pri razvrščanju po priimku je, da priimek vnesete v ločen stolpec.

To lahko storite tako, da vse pred priimkom zamenjate s praznim, tako da vam ostane le priimek.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite te podatke razvrstiti po abecedi po priimku.

Spodaj so navedeni koraki za razvrščanje po priimku:

1. Izberite nabor podatkov, vključno z glavo (v tem primeru bi bilo to A1: A10)
2. Kopirajte ga v sosednji stolpec (če sosednji stolpec ni prazen, vstavite nov stolpec in nato kopirajte ta imena)
3. Preimenujte kopirano glavo stolpca. V tem primeru bom ime "Priimek"
4. Izberite vsa kopirana imena (ne izberite glave)
5. Držite tipko Control in pritisnite tipko H. S tem se odpre pogovorno okno Najdi in zamenjaj.
6. V polje Najdi kaj vnesite * (simbol zvezdice, ki mu sledi presledek)
7. Pustite polje Zamenjaj s prazno
8. Kliknite Zamenjaj vse. To bi takoj nadomestilo vse ime in ostali boste samo priimki.

Zgornji koraki bi ohranili priimek in odstranili vse pred njim. To dobro deluje tudi, če imate srednja imena ali predpone (na primer Mr. ali Ms).

Ko imate priimke v sosednjem stolpcu, lahko niz podatkov (vključno s polnimi imeni) preprosto razvrstite po abecedi glede na priimek.

Spodaj so navedeni koraki za razvrščanje po priimku:

1. Izberite celoten nabor podatkov z glavami (vključno s polnimi imeni in izvlečenimi priimki). Poleg imen lahko vključite tudi druge stolpce, ki jih želite razvrstiti
2. Kliknite zavihek Podatki
3. Kliknite na Razvrsti
4. V pogovornem oknu Razvrsti preverite, ali je izbrana možnost »Moji podatki imajo glave«.
5. V možnosti »Razvrsti po« izberite ime stolpca, ki ima samo priimek
6. V vrstici »Razvrsti vklopljeno« izberite »Vrednosti celic«
7. V možnosti Naroči izberite od A do Ž
8. Kliknite V redu

Zgornji koraki bi razvrstili celoten izbrani nabor podatkov glede na priimek.

Ko končate, lahko izbrišete stolpec s priimkom.

Nasvet za profesionalce: Kadar koli menite, da boste morda potrebovali nazaj prvotne podatke, morate imeti na voljo način, da razvrstite ta nabor podatkov. Če želite to narediti, v sosednjem stolpcu (levo ali desno) imejte serijske številke pred razvrščanjem. Če potrebujete nazaj izvirne podatke, jih dobite z razvrščanjem na podlagi številk.

Izvleči in abecediraj po priimku s formulo

Medtem ko je zgoraj prikazana metoda (s funkcijo Poišči in zamenjaj) najraje, da dobim vsa priimka in jih razvrstim, je ena od omejitev ta, da so nastali podatki statični.

To pomeni, da bom moral, če na svoj seznam dodam več imen, znova pridobiti isti priimek.

Če tega ne želite, lahko uporabite metodo formule za razvrščanje podatkov po priimkih.

Recimo, da imate nabor podatkov, kot je prikazano spodaj.

Spodaj je formula, ki bo izvzela priimek iz polnega imena:

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

Zgornja formula temelji na vzorcu s polnim imenom (ki v tem primeru vsebuje samo ime in priimek). Vzorec je tak, da bi med imenom in priimkom obstajal presledek.

Funkcija FIND se uporablja za določitev položaja presledka. Ta vrednost se nato odšteje od skupne dolžine imena, da dobimo skupno število znakov v priimku.

Ta vrednost se nato uporabi v funkciji DESNO za priimek.

Ko imate stolpec s priimkom, lahko te podatke razvrstite (to je podrobno opisano v prvi metodi).

Zgornja formula bi delovala, če imate samo ime in priimek.

Kaj pa, če imate tudi srednje ime. Ali pa je pred imenom pozdrav (na primer gospod ali gospa)

V tem primeru morate uporabiti naslednjo formulo:

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

Zgornja formula najde položaj zadnjega presledka in nato z njim izvleče priimek.

Priporočam, da v vseh primerih uporabite drugo formulo, ki je bolj varna in lahko obravnava vse primere (če je priimek na koncu imena).

Opomba: Ti dve formuli se opirata na pogoj, da je med vsakim elementom imena samo en presledek. Če obstajajo dvojni presledki ali vodilni/zadnji razmiki, bo ta formula dala napačne rezultate. V takem primeru je najbolje uporabiti funkcijo TRIM, da se najprej znebite vseh začetnih, zadnjih in dvojnih presledkov, nato pa uporabite zgornjo formulo.

Čeprav se to morda zdi zapletena metoda, je prednost uporabe formule, da naredi rezultate dinamične. Če na seznam dodate več imen, morate le kopirati formulo in prikazala se bo njen priimek.

Uporaba besedila v stolpcih

Besedilo v stolpce je spet preprost in enostaven način za razdelitev celic v Excelu.

Določite lahko ločilo (na primer vejico ali presledek) in ga uporabite za razdelitev vsebine celice. Ko imate razdeljene elemente v ločenih stolpcih, lahko uporabite stolpec s priimkom za razvrščanje podatkov po abecedi.

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

Spodaj so navedeni koraki za uporabo besedila v stolpcu za razvrščanje po priimku:

1. Izberite stolpec z imenom (brez glave)
2. Kliknite zavihek Podatki
3. V skupini »Podatkovna orodja« kliknite možnost Besedilo v stolpce. S tem se odpre čarovnik Besedilo v stolpce
4. V 1. koraku čarovnika za pretvorbo besedila v stolpce izberite »Razmejeno« in kliknite Naprej
5. V 2. koraku izberite "Presledek" kot ločilo (in počistite polje, če je izbrano), nato kliknite gumb Naprej.
6. V 3. koraku v predogledu podatkov izberite prvi stolpec z imenom in nato izberite možnost »Ne uvozi stolpcev (preskoči)«. To zagotavlja, da ime ni del rezultata in dobite samo priimek.
7. Tudi v 3. koraku spremenite ciljno celico v tisto, ki meji na izvirne podatke. To bo zagotovilo, da boste priimek prejeli ločeno in da so podatki o izvirnih imenih nedotaknjeni.
8. Kliknite Dokončaj

Ko dobite rezultat, ga lahko razvrstite po priimku.

Če imate za ločilo vejico, lahko tudi pošljete besedilo v stolpce, da ločite ime in priimek.

Uporaba Flash Fill

Drug hiter in hiter način za priimek je uporaba funkcije Flash Fill.

Flash Fill je bil predstavljen v Excelu 2013 in pomaga pri obdelavi podatkov z identifikacijo vzorcev. Če želite, da to deluje, morate nekajkrat prikazati rezultat Flash Fill, ki ga pričakujete.

Ko bo identificiral vzorec, bo hitro opravil preostalo delo namesto vas.

Recimo, da imate spodnji niz imen.

Spodaj so navedeni koraki za uporabo Flash Fill, da dobite priimek in ga nato razvrstite:

1. V celico B2 vnesite besedilo 'Maury'. To je rezultat, ki ga pričakujete v celici.
2. Pojdite na naslednjo celico in vnesite priimek za ime v sosednjo celico (Elliot v tem primeru).
3. Izberite obe celici
4. Premaknite kazalec na spodnji desni del izbire. Opazili boste, da se kazalec spremeni v ikono plus.
5. Dvokliknite nanjo (ali jo kliknite in povlecite navzdol). To vam bo dalo nekaj rezultatov v celicah (verjetno ne bo rezultat, ki ga želite)
6. Kliknite ikono možnosti samodejnega izpolnjevanja.
7. Kliknite na Flash Fill

Tako boste dobili rezultat, ki bo verjetno priimek v vseh celicah.

Pravim verjetno, saj Flash Fill v nekaterih primerih morda ne bo deloval. Ker je to odvisno od prepoznavanja vzorca, tega morda ne bo zmoglo vedno. Ali včasih vzorec, ki ga dešifrira, morda ni pravi.

V takih primerih morate v eno ali dve celici vnesti pričakovani rezultat in nato narediti korake 4-7.

Ko imate v stolpcu vse priimke, lahko podatke razvrstite glede na te priimke.

To so torej štirje različni načini, s katerimi lahko podatke razvrstite po priimku. Najboljša metoda bi bila uporaba tehnike Najdi in zamenjaj, če pa želite rezultate narediti dinamične, je metoda formule prava pot.

Upam, da se vam je ta vadnica zdela uporabna.