Kako razvrstiti po priimku v Excelu (preprost vodnik)

Če delate z naborom podatkov imen, je njegovo razvrščanje ena od pogostih nalog, ki jih morate pogosto izvajati.

Podatke je enostavno razvrstiti po abecedi po polnem imenu, pri čemer Excel za razvrščanje uporablja prvi znak imena.

Vendar, če želiteRazvrsti podatke po priimku v ExceluPojdi naRazvrstikaj storiti?

Čeprav ni tako preprosto, je še vedno mogoče storiti (veliko je odvisno tudi od tega, kako so strukturirani podatki o imenu).

Ne glede na metodo, ki jo uporabite, morate nekako izluščiti priimek iz polnega imena in ga postaviti v ločen stolpec.Nato lahko uporabite ta stolpec za razvrščanje podatkov po abecedi po priimku.

V tej vadnici za Excel vam bom pokazal, kako razvrstiti stolpec z imeni na podlagi priimka.

Pa začnimo!

Izvlecite in razvrstite po priimku z uporabo Najdi in zamenjaj

Prvi korak pri razvrščanju po priimku je pridobitev priimka v ločenem stolpcu.

To lahko storite tako, da vse pred priimkom zamenjate s praznimi, tako da vam ostane samo priimek.

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

Tukaj so koraki za razvrščanje po priimku:

1. Izberite nabor podatkov, ki vsebuje naslov (v tem primeru bo to A1:A10)
2. kopirajte ga v sosednji stolpec (če sosednji stolpec ni prazen, vstavite nov stolpec, nato kopirajte imena)
3. Preimenujte kopirane glave stolpcev.V tem primeru ga bom poimenoval "Priimek"
4. Izberite vsa kopirana imena (ne izberite naslovov)
5. Pridržite tipko Control in nato pritisnite tipko H.To bo odprlo pogovorno okno Najdi in zamenjaj.
6. V polje Najdi vnesite * (zvezdica, ki ji sledi presledek)
7. Polje Zamenjaj z pustite prazno
8. Kliknite Zamenjaj vse.S tem boste naenkrat zamenjali vsa imena in ostali vam bodo samo priimki.

Zgornji koraki bodo ohranili priimek in izbrisali vse pred njim.To deluje tudi, če imate srednje ime ali predpono, na primer g. ali ga.

Ko imate priimke v sosednjih stolpcih, lahko preprosto razvrstite nabor podatkov (vključno s polnimi imeni) po abecedi po priimku.

Tukaj so koraki za razvrščanje po priimku:

1. Izberite celoten nabor podatkov z naslovi (vključno s polnimi imeni in izvlečenimi priimki).Vključite lahko tudi druge stolpce za razvrščanje z imenom
2. Kliknite zavihek Podatki
3. Kliknite za razvrščanje
4. V pogovornem oknu Razvrsti se prepričajte, da je potrjeno možnost Moji podatki imajo glave.
5. V možnosti "Razvrsti po" izberite ime stolpca samo s priimkom
6. V razvrstitvi po izberite Vrednost celice
7. V možnosti "Naročilo" izberite "A do Ž"
8. Kliknite V redu

Zgornji koraki bodo razvrstili celoten izbrani niz podatkov na podlagi priimka.

Ko končate, lahko spustite stolpec s priimkom.

Profesionalni nasveti: Kadar koli mislite, da se boste morda morali vrniti na izvirne podatke, morate imeti način, da ta nabor podatkov razvrstite.Za to so v sosednjih stolpcih (levo ali desno) pred razvrščanjem zaporedne številke.Zdaj, če morate vrniti prvotne podatke, jih lahko dobite z razvrščanjem na podlagi številk.

Izvlecite in razvrstite po abecedi po priimku s formulo

Medtem ko je zgoraj prikazana metoda (z uporabo najdi in zamenjaj) tista, ki mi je ljubše, da dobim vse priimke in razvrstim na podlagi nje, je ena od njenih omejitev ta, da so nastali podatki statični.

To pomeni, da če na seznam dodam več imen, bom moral znova iti skozi isti postopek, da dobim priimek.

Če to ni tisto, kar želite, lahko uporabite formulo za razvrščanje podatkov po priimku.

Recimo, da imate spodnji nabor podatkov.

Tukaj je formula za izločanje priimka iz polnega imena:

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

Zgornja formula se opira na vzorec s polnimi imeni (v tem primeru samo ime in priimek).Vzorec je, da bo med imenom in priimkom presledek.

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

Nato uporabite to vrednost v funkciji DESNO, da dobite priimek.

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

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

A kaj, če imaš tudi srednje ime.ali ima lahko pozdrav pred imenom (npr. g. ali ga.)

V tem primeru morate uporabiti naslednjo formulo:

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

Zgornja formula poišče položaj zadnjega presledka in ga nato uporabi za ekstrakcijo priimka.

Predlagam, da v vseh primerih uporabite drugo formulo, je enostavnejša in obravnava vse primere (dokler je priimek na koncu imena).

Opomba: ti dve formuli se zanašata na pogoj, da je med vsakim elementom imena samo en presledek.Ta formula bo dala napačne rezultate, če so dvojni presledki ali začetni/končni presledki.V tem primeru je najbolje uporabiti funkcijo TRIM, da najprej odstranite vse vodilne, končne in dvojne presledke, nato pa uporabite zgornjo formulo.

Čeprav se to morda zdi zapleten pristop, je prednost uporabe formule v tem, da naredi rezultate dinamične.Če na seznam dodate več imen, morate samo kopirati formulo in dala vam bo priimek.

Uporabite besedilo za stolpce

Besedilo v stolpce je še en preprost način za razdelitev celic v Excelu.

Določite lahko ločilo (kot je vejica ali presledek) in ga uporabite za razdelitev vsebine celice.Po razdelitvi elementov v ločene stolpce lahko uporabite stolpec s priimki za abecedo podatkov.

Recimo, da imate nabor podatkov, ki izgleda takole:

Tu so koraki za razvrščanje po priimku z uporabo besedila v stolpec:

1. Izberite stolpce z imeni (razen naslovov)
2. Kliknite zavihek Podatki
3. V skupini Podatkovna orodja kliknite možnost Besedilo v stolpce.To bo odprlo čarovnika za besedilo v stolpce
4. V 1. koraku čarovnika za pretvarjanje besedila v stolpce izberite Ločeno in kliknite Naprej
5. V 2. koraku izberite "Presledek" kot ločilo (počistite vse drugo, če je potrjeno) in kliknite gumb Naprej.
6. V 3. koraku izberite stolpec z imenom v predogledu podatkov, nato izberite možnost »Ne uvozi stolpca (preskoči)«.To zagotavlja, da ime ni del rezultata, dobite le priimek.
7. Tudi v 3. koraku spremenite ciljno celico v celico, ki meji na izvirne podatke.To bo zagotovilo, da boste dobili samo priimek in da bodo podatki o izvirnem imenu popolni.
8. Kliknite Dokončaj

Ko imate rezultate, jih lahko razvrstite po priimku.

Za ločevanje imen in priimkov lahko uporabite tudi besedilo v stolpec, če imate kot ločila vejice.

Napolnite z bleščicami

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

Flash Fill, predstavljen v Excelu 2013, pomaga pri manipulaciji s podatki z identifikacijo vzorcev.Če želite to narediti, morate večkrat prikazati Flash Fill za rezultat, ki ga pričakujete.

Ko prepozna vzorec, hitro naredi ostalo namesto vas.

Recimo, da imate naslednji nabor imen.

Tukaj so koraki za pridobitev priimka z uporabo Flash Fill in njegovo razvrščanje:

1. V celico B2 vnesite besedilo "Maury".To bi pričakovali v celici.
2. Pojdite na naslednjo celico in v sosednjo celico vnesite priimek imena (v tem primeru Elliot).
3. izberite dve celici
4. Premaknite kazalec nad spodnji desni del izbire.Opazili boste, da se kazalec spremeni v ikono plus.
5. Dvokliknite ga (ali kliknite in povlecite navzdol).To vam bo dalo nekaj rezultata v celici (malo verjetno tistega, ki ga želite)
6. Kliknite ikono Možnosti samodejnega izpolnjevanja.
7. Kliknite Hitro izpolnjevanje

To vam bo dalo rezultat, ki je lahko priimek v vseh celicah.

Pravim, da morda zato, ker Flash Fill v nekaterih primerih morda ne deluje.Ker je odvisno od načina prepoznavanja, tega morda ne bo vedno.Včasih pa vzorec, ki ga dešifrira, morda ni pravilen vzorec.

V tem primeru morate pričakovani rezultat vnesti v eno ali več celic in nato iti skozi korake 4-7.

Ko imate v stolpcu vse priimke, lahko podatke razvrstite na podlagi teh priimkov.

Zato lahko podatke razvrstite po priimku na štiri različne načine.Najboljši način je uporaba tehnik iskanja in zamenjave, če pa želite, da bodo vaši rezultati dinamični, so metode formule prava pot.

Upam, da se vam je ta vadnica zdela koristna.