Kuidas Excelis perekonnanime järgi sortida (lihtne juhend)

Vaadake videot - kuidas Excelis perekonnanime järgi sorteerida

Kui töötate nimede andmekogumitega, on selle sortimine üks levinumaid ülesandeid, mida peaksite sageli tegema.

On üsna lihtne sortida andmeid tähestikulises järjekorras täisnime järgi, kus Excel kasutab sortimiseks nime esimest tähemärki.

Aga mis siis, kui soovite sortida andmeid Exceli perekonnanime järgi?

Kuigi see pole nii lihtne, saab seda siiski teha (palju sõltub ka nimede andmete ülesehitusest).

Ükskõik, millist meetodit te kasutate, peate kuidagi perekonnanime täisnimest välja tõmbama ja eraldi veergu panema. Seejärel saate seda veergu kasutada andmete sortimiseks perekonnanime järgi tähestikulises järjekorras.

Selles Exceli õpetuses näitan teile, kuidas perekonnanime põhjal nimedega veergu sortida.

Nii et alustame!

Ekstraheerige ja sorteerige perekonnanime järgi, kasutades funktsiooni Otsi ja asenda

Perekonnanime järgi sorteerimise esimene samm on perekonnanime hankimine eraldi veergu.

Seda saate teha, asendades kõik perekonnanime ees olevad tühjad kohad, nii et teil on alles ainult perekonnanimi.

Oletame, et teil on allpool näidatud andmekogum ja soovite neid andmeid perekonnanime järgi tähestikulises järjekorras sortida.

Perekonnanime järgi sortimiseks toimige järgmiselt.

1. Valige päis sisaldav andmekogum (selles näites oleks see A1: A10)
2. Kopeerige see külgnevasse veergu (kui külgnev veerg pole tühi, sisestage uus veerg ja kopeerige need nimed)
3. Nimetage kopeeritud veeru päis ümber. Selles näites panen nimeks "Perekonnanimi"
4. Valige kõik kopeeritud nimed (ärge valige päist)
5. Hoidke all juhtklahvi ja seejärel vajutage klahvi H. See avab dialoogiboksi Otsi ja asenda.
6. Sisestage väljale Otsi mida * (tärn, millele järgneb tühik)
7. Jätke väli Asenda kohaga tühjaks
8. Klõpsake nuppu Asenda kõik. See asendaks koheselt kogu eesnime ja teile jäetakse ainult perekonnanimed.

Ülaltoodud toimingud säilitaksid perekonnanime ja eemaldaksid kõik enne seda. See toimib hästi isegi siis, kui teil on keskmised nimed või eesliited (näiteks härra või proua).

Kui perekonnanimed on külgnevas veerus, saate andmekogumi (sh täisnimed) perekonnanime alusel tähestikulises järjekorras hõlpsasti sortida.

Perekonnanime järgi sortimiseks toimige järgmiselt.

1. Valige päistega kogu andmekogum (sh täisnimed ja väljavõetud perekonnanimed). Lisaks nimedele saate lisada ka muid veerge, mida soovite sortida
2. Klõpsake vahekaarti Andmed
3. Klõpsake Sorteeri
4. Veenduge, et dialoogiboksis Sortimine oleks valitud „Minu andmetel on päised”.
5. Valige suvandist „Sortimisalus” selle perekonnanimega veeru nimi
6. Valige jaotises „Sorteeri” suvand „Lahtrite väärtused”
7. Valige suvandis Tellimine „A kuni Z“
8. Klõpsake nuppu OK

Ülaltoodud toimingud sorteeriksid kogu valitud andmekogumi perekonnanime alusel.

Kui olete lõpetanud, saate perekonnanimega veeru kustutada.

Pro näpunäide: Kui arvate, et teil on vaja algandmeid igal ajal tagasi, peab teil olema võimalus selle andmestiku sorteerimine tühistada. Selleks pange külgnevas veerus (vasakul või paremal) enne sorteerimist seerianumbrid. Nüüd, kui vajate algandmeid tagasi, saate need numbrite alusel sorteerimise teel.

Väljavõte ja tähestik perekonnanime järgi valemi abil

Kuigi ülaltoodud meetod (kasutades otsingut ja asendamist) on see, mida ma eelistan kõigi perekonnanimede saamiseks ja selle alusel sortimiseks, on selle üks piirang see, et saadud andmed on staatilised.

See tähendab, et kui lisan oma nimekirja rohkem nimesid, pean perekonnanimede saamiseks sama protsessi uuesti tegema.

Kui te seda ei soovi, saate andmete sortimiseks perekonnanimede järgi kasutada valemimeetodit.

Oletame, et teil on andmestik, nagu allpool näidatud.

Allpool on valem, mis eraldab perekonnanime täisnimest:

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

Ülaltoodud valem tugineb täisnimega mustrile (mis sisaldab selles näites ainult ees- ja perekonnanime). Muster on see, et ees- ja perekonnanime vahel oleks tühik.

Funktsiooni FIND kasutatakse tühiku asukoha määramiseks. Seejärel lahutatakse see väärtus nime kogupikkusest, et saada perekonnanime tähemärkide koguarv.

Seda väärtust kasutatakse seejärel funktsioonis PAREM, et saada perekonnanimi.

Kui teil on perekonnanime veerg, saate neid andmeid sorteerida (seda käsitletakse üksikasjalikult esimeses meetodis).

Ülaltoodud valem töötab, kui teil on ainult ees- ja perekonnanimed.

Aga mis siis, kui teil on ka keskmine nimi. Või võib tervitada nime ees (näiteks härra või proua)

Sellisel juhul peate kasutama järgmist valemit:

= PAREM (A2, LEN (A2) -FIND ("@", ASENDUS (A2, "", "@", LEN (A2) -LEN (ASENDUS (A2, "", ""))))))

Ülaltoodud valem leiab viimase tühikutähe asukoha ja kasutab seda seejärel perekonnanime väljavõtmiseks.

Soovitan teil kõigil juhtudel kasutada teist valemit, mis on lollikindlam ja saab kõigi juhtumitega hakkama (kui perekonnanimi on nime lõpus).

Märkus. Need kaks valemit toetuvad tingimusele, et iga nimeelemendi vahel on ainult üks tühik. Kui vahed on kahekordsed või esi-/lõppruumid, annab see valem vale tulemuse. Sellisel juhul on kõige parem kasutada funktsiooni TRIM, et esmalt vabaneda kõikidest esi-, taga- ja topeltruumidest ning seejärel kasutada ülaltoodud valemit.

Kuigi see võib tunduda keeruline meetod, on valemi kasutamise eeliseks see, et see muudab tulemused dünaamiliseks. Kui lisate loendisse rohkem nimesid, peate vaid valemi kopeerima ja see annab teile perekonnanime.

Teksti veergudele kasutamine

Tekst veergudele on jällegi lihtne ja lihtne viis lahtrite jagamiseks Excelis.

Saate määrata eraldaja (nt koma või tühik) ja kasutada seda lahtri sisu jagamiseks. Kui jagatud elemendid on eraldi veergudes, saate andmete tähestamiseks kasutada veergu, millel on perekonnanimi.

Oletame, et teil on allpool näidatud andmekogum:

Allpool on toodud sammud teksti veergu kasutamiseks perekonnanime järgi sortimiseks.

1. Valige veerg, millel on nimi (välja arvatud päis)
2. Klõpsake vahekaarti Andmed
3. Klõpsake rühmas „Andmetööriistad” valikut Tekst veergudele. See avab viisardi Teksti veergudele
4. Valige teksti teisendamise viisardi viisardi esimeses etapis „Eraldatud” ja klõpsake nuppu Edasi
5. 2. etapis valige eraldajaks „Tühik” (ja tühjendage mis tahes muu märkeruut, kui see on valitud) ja klõpsake siis nuppu Järgmine.
6. 3. sammus valige andmete eelvaates eesnimeveerg ja seejärel suvand „Ära impordi veerge (jäta vahele”). See tagab, et eesnimi ei ole tulemuse osa ja saate ainult perekonnanime.
7. Samuti muutke 3. sammus sihtrakuks see, mis asub algandmete kõrval. See tagab, et saate perekonnanime eraldi ja originaalnimede andmed on puutumatud.
8. Klõpsake nuppu Lõpeta

Kui olete tulemuse saanud, saate sortida perekonnanime järgi.

Kui eraldaja on koma, saate ees- ja perekonnanime eraldamiseks saata teksti veergudesse.

Flash Filli kasutamine

Veel üks kiire ja kiire viis perekonnanimede saamiseks on funktsiooni Flash Fill kasutamine.

Flash Fill võeti kasutusele programmis Excel 2013 ja see aitab mustreid tuvastades andmetega manipuleerida. Et see toimiks, peate paar korda näitama Flash Fill tulemust, mida ootate.

Kui see on mustri tuvastanud, teeb see teie eest kiiresti ülejäänud töö.

Oletame, et teil on alltoodud nimede andmekogum.

Allpool on toodud sammud Flash Filli kasutamiseks perekonnanime saamiseks ja seejärel selle järgi sortimiseks:

1. Lahtrisse B2 sisestage tekst "Maury". Seda tulemust ootate lahtris.
2. Minge järgmise lahtri juurde ja sisestage kõrvalolevasse lahtrisse perekonnanimi (selles näites Elliot).
3. Valige mõlemad lahtrid
4. Hõljutage kursorit valiku paremas alanurgas. Märkate, et kursor muutub plussikooniks.
5. Topeltklõpsake seda (või klõpsake ja lohistage see alla). See annab teile lahtrites teatud tulemuse (tõenäoliselt pole see soovitud tulemus)
6. Klõpsake automaatse täitmise suvandite ikooni.
7. Klõpsake nuppu Flash Fill

See annab teile tulemuse, mis on tõenäoliselt kõigi lahtrite perekonnanimed.

Ma ütlen tõenäoliselt, kuna Flash Fill ei pruugi mõnel juhul töötada. Kuna see sõltub mustri tuvastamisest, ei pruugi see alati seda teha. Või mõnikord ei pruugi selle dešifreeritav muster olla õige.

Sellistel juhtudel peaksite sisestama oodatud tulemuse veel ühte või kahte lahtrisse ja seejärel tegema samme 4-7.

Kui teil on veerus kõik perekonnanimed, saate andmeid nende perekonnanimede alusel sortida.

Nii et need on neli erinevat viisi, mida saate kasutada andmete sortimiseks perekonnanime järgi. Parim meetod oleks kasutada otsi ja asenda tehnikat, kuid kui soovite oma tulemused dünaamiliseks muuta, on õige viis valemimeetod.

Loodan, et leidsite selle õpetuse kasulikuks.