Eraldage Excelis ees- ja perekonnanimi (jagage nimed valemite abil)

Vaadake videot - jagage nimed Excelis (ees-, kesk- ja perekonnanimeks)

Excel on hämmastav tööriist tekstiandmete viilutamisel ja tükeldamisel.

Excelis tekstiandmetega töötamiseks on nii palju kasulikke valemeid ja funktsioone.

Üks levinumaid küsimusi, mida saan tekstiandmetega manipuleerimise kohta, on - „Kuidas eraldada Excelis ees- ja perekonnanimed (või ees-, keskmine ja perekonnanimi)?“.

Excelis on nimede jagamiseks paar lihtsat viisi. Valitud meetod sõltub teie andmete ülesehitusest ja sellest, kas soovite, et tulemus oleks staatiline või dünaamiline.

Nii et alustame ja vaatame erinevaid viise nimede jagamiseks Excelis.

Jagage nimed teksti abil veergudeks

Funktsioon Tekst veergudesse Excelis võimaldab teil tekstiväärtused kiiresti rea eraldi lahtritesse jagada.

Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite eraldada ees- ja perekonnanime ning hankida need eraldi lahtritesse.

Allpool on toodud sammud ees- ja perekonnanime eraldamiseks, kasutades teksti veergudeks.

1. Valige kõik veerus olevad nimed (selles näites A2: A10)
2. Klõpsake vahekaarti „Andmed”
3. Klõpsake rühmas „Andmetööriistad” valikut „Tekst veergudele”.
4. Tehke viis Teksti teisendamise viisardis järgmised muudatused.
   1. Samm 1/3: Valige Eraldatud (see võimaldab teil eraldajana ruumi kasutada) ja klõpsake nuppu Edasi
   2. Samm 2/3: Valige tühik ja klõpsake nuppu Edasi
   3. Samm 3/3: määrake sihtkohaks B2 (muidu kirjutab see olemasolevad andmed üle)
5. Klõpsake nuppu Lõpeta

Ülaltoodud sammud jagaksid nimed koheselt ees- ja perekonnanimeks (eesnimed veerus B ja perekonnanimed veerus C).

Märkus. Kui lahtrites on juba andmeid (neid, mille puhul oodatakse väljundit tekstist veergudesse), kuvab Excel teile hoiatuse, mis annab teile teada, et lahtrites on juba andmeid. Saate andmed üle kirjutada või tühistada teksti veergudele ja need esmalt käsitsi eemaldada.

Kui olete lõpetanud, saate soovi korral täisnime andmed kustutada.

Teksti veergudele kasutamisel Exceli ees- ja perekonnanimede eraldamiseks peate teadma järgmist.

1. Selle tulemus on staatiline. See tähendab, et kui teil on uusi andmeid või algsetes andmetes on muudatusi, peate nimede jagamiseks seda uuesti tegema.
2. Kui soovite ainult eesnime või ainult perekonnanime, võite dialoogiboksi „Tekst veergu viisardis” 3. sammus vahele jätta veerud, mida te ei soovi. Selleks valige eelvaates veerg, mille soovite vahele jätta, ja seejärel valige suvand „Ära impordi veergu (jäta vahele”).
3. Kui te sihtkoha lahtrit ei määra, kirjutab tekst veergu praeguse veeru üle

Valik Tekst veergudesse sobib kõige paremini siis, kui teil on järjepidevad andmed (näiteks kõigil nimedel on ainult ees- ja perekonnanimi või kõigil nimedel ees-, keskmine ja perekonnanimi).

Selles näites olen näidanud teile, kuidas eraldada nimesid, mille eraldajaks on tühik. Kui eraldaja on koma või koma ja tühiku kombinatsioon, saate siiski samu samme kasutada. Sel juhul saate eraldaja määrata viisardi sammus 2/3. Juba on võimalus kasutada eraldajana koma või võite valida suvandi „Muu” ja määrata ka kohandatud eraldaja.

Kuigi tekst veergudele on kiire ja tõhus viis nimede jagamiseks, sobib see ainult siis, kui soovite, et väljund oleks staatiline tulemus. Kui teil on andmekogum, mida saab laiendada või muuta, on parem nimede eraldamiseks kasutada valemeid.

Eraldage ees-, keskmine ja perekonnanimi valemite abil

Valemid võimaldavad teil tekstiandmeid lõigata ja kuubikuteks lõigata.

Selles jaotises jagan erinevaid valemeid, mida saate kasutada nimeandmete eraldamiseks (vastavalt teie andmete struktuurile).

Allpool on toodud kolm valemit, mille abil saate ees-, kesk- ja perekonnanime eraldada (seda selgitatakse üksikasjalikult hiljem järgmistes jaotistes).

Valem eesnime saamiseks:

= VASAK (A2, OTSI ("", A2) -1)

Valem keskmise nime saamiseks:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

Valem perekonnanime saamiseks:

= PAREM (A15, LEN (A15) -SEARCH ("@", ASENDUS (A15, "", "@", LEN (A15) -LEN (ASENDUS (A15, "", ""))))))

Hankige eesnimi

Oletame, et teil on andmestik, nagu allpool näidatud, ja soovite kiiresti eraldada eesnime ühes lahtris ja perekonnanime ühes lahtris.

Allolev valem annab teile eesnime:

= VASAK (A2, OTSI ("", A2) -1)

Ülaltoodud valem kasutab funktsiooni SEARCH, et saada tühiku asukoht ees- ja perekonnanime vahele. Funktsioon LEFT kasutab seda tühiku positsiooni numbrit, et eraldada kogu tekst enne seda.

See on tekstiväärtuse osa väljavõtmise üsna sirgjooneline kasutamine. Kuna meil pole vaja teha muud, kui tuvastada esimene tühikutäht, pole vahet, kas nimel on keskmine nimi või mitte. Ülaltoodud valem töötab suurepäraselt.

Nüüd läheme iga näitega veidi edasi.

Hankige perekonnanimi

Oletame, et teil on sama andmekogum ja seekord peate saama perekonnanime.

Allolev valem ekstraheerib ülaltoodud andmestikust perekonnanime:

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

Jällegi üsna otsekohene.

Seekord leiame esmalt tühiku tähemärgi positsiooni, mida kasutatakse seejärel tühikute järele jäänud märkide arvu väljaselgitamiseks (mis oleks perekonnanimi).

See saavutatakse tühimärgi positsiooniväärtuse lahutamisega nimes olevate märkide koguarvuga.

Seejärel kasutatakse seda numbrit funktsioonis PAREM, et tuua kõik need tähemärgid nime paremalt poolt.

Kuigi see valem töötab suurepäraselt, kui on ainult ees- ja perekonnanimi, ei tööta see juhul, kui teil on ka teine ​​nimi. Seda seetõttu, et arvestasime ainult ühe tühiku (ees- ja perekonnanime vahel). Keskmise nime lisamine lisab nimele rohkem tühikuid.

Perekonnanime toomiseks, kui teil on ka keskmine nimi, kasutage järgmist valemit:

= PAREM (A15, LEN (A15) -SEARCH ("@", ASENDUS (A15, "", "@", LEN (A15) -LEN (ASENDUS (A15, "", ""))))))

Nüüd on see muutunud natuke keeruliseks … kas pole?

Las ma selgitan, kuidas see toimib.

Ülaltoodud valem leiab kõigepealt nimes tühikute arvu. Seda tehakse nii, et saadakse nime pikkus koos tühikutega ja ilma ning lahutatakse tühikuteta üks tühikuga. See annab tühikute koguarvu.

Funktsiooni SUBSTITUTE kasutatakse seejärel viimase tühiku asendamiseks sümboliga „@” (võite kasutada mis tahes sümbolit - midagi, mida nime osana tõenäoliselt ei esine).

Kui @ sümbol on viimase tühikutähe asemel asendatud, leiate selle @ sümboli asukoha hõlpsalt. Seda tehakse funktsiooni SEARCH abil.

Nüüd pole vaja teha muud, kui eraldada kõik sümbolid @st paremal olevad märgid. Seda tehakse parempoolset funktsiooni kasutades.

Hankige keskmine nimi

Oletame, et teil on allpool näidatud andmekogum ja soovite keskmise nime välja võtta.

Seda teeb järgmine valem:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

Ülaltoodud valem kasutab funktsiooni MID, mis võimaldab määrata lähtepositsiooni ja sellest asendist eraldatavate märkide arvu.

Funktsiooni SEARCH abil on lähtepositsiooni lihtne leida.

Raske osa on leida, mitu tähemärki pärast seda lähtepositsiooni välja võtta. Selle saamiseks peate tuvastama, kui palju tähemärke on alguspositsioonist viimase tühimärgini.

Seda saab teha funktsiooni SUBSTITUTE abil ja asendada viimane tühik sümboliga „@”. Kui see on tehtud, saate hõlpsasti kasutada funktsiooni SEARCH, et leida selle viimase tühikutähe asukoht.

Nüüd, kui teil on lähtepositsioon ja viimase tühiku asukoht, saate keskmise nime hõlpsalt MID -funktsiooni abil tuua.

Üks nimede eraldamise valemi kasutamise eeliseid on see, et tulemus on dünaamiline. Seega, kui teie andmekogum laieneb ja sellele lisatakse rohkem nimesid või kui mõned nimed muutuvad algses andmekogumis, ei pea te saadud andmete pärast muretsema.

Eraldage nimed, kasutades funktsiooni Otsi ja asenda

Mulle meeldib paindlikkus, mis kaasneb funktsiooniga „Leia ja asenda” - sest selles saate kasutada metamärke.

Lubage mul kõigepealt selgitada, mis on metsakaardi tegelane.

Metamärk on midagi, mida saate kasutada mis tahes teksti asemel. Näiteks võite kasutada tärni sümbolit (*) ja see tähistab Excelis suvalist arvu tähemärke. Toon näite: kui soovin leida kõik nimed, mis algavad tähestikuga A, võin otsimisel ja asendamisel kasutada tähte A*. See leiab ja valib kõik lahtrid, kus nimi algab tähega A.

Kui te pole ikka veel selge, ärge muretsege. Jätkake lugemist ja järgmised näited selgitavad, mis on metamärgid ja kuidas neid nimede (või Exceli mis tahes tekstiväärtuste) kiireks eraldamiseks kasutada.

Kõikides allpool käsitletud näidetes looge kindlasti andmestiku varukoopia. Otsimine ja asendamine muudab andmeid, mille kohta seda kasutatakse. Kõige parem on kõigepealt andmed kopeerida ja kleepida ning seejärel kasutada kopeeritud andmekogumis nuppu Otsi ja asenda.

Hankige eesnimi

Oletame, et teil on allpool näidatud andmekogum ja soovite saada ainult eesnime.

Allpool on toodud sammud selle tegemiseks.

1. Kopeerige nime andmed veergu A ja kleepige need veergu B.
2. Kui veerus B olevad andmed on valitud, klõpsake vahekaarti Kodu
3. Klõpsake rühmas Redigeerimine nuppu Leia ja vali.
4. Klõpsake nuppu Asenda. See avab dialoogiboksi "Otsi ja asenda".
5. Sisestage dialoogiboksis „Otsi ja asenda” järgmine
   1. Otsige üles: * (tühik, millele järgneb tärn)
   2. Asenda: jäta see tühjaks
6. Klõpsake nuppu Asenda kõik.

Ülaltoodud sammud annaksid teile eesnime ja eemaldaksid kõik eesnime järel.

See toimib isegi siis, kui teil on nimed, millel on keskmine nimi.

Pro näpunäide: Dialoogiboksi Otsing ja asendamine avamiseks on kiirklahv Control + H (hoidke all juhtklahvi ja seejärel vajutage klahvi H).

Hankige perekonnanimi

Oletame, et teil on allpool näidatud andmekogum ja soovite saada ainult perekonnanime.

Allpool on toodud sammud selle tegemiseks.

1. Kopeerige nime andmed veergu A ja kleepige need veergu B.
2. Kui veerus B olevad andmed on valitud, klõpsake vahekaarti Kodu
3. Klõpsake rühmas Redigeerimine nuppu Leia ja vali.
4. Klõpsake nuppu Asenda. See avab dialoogiboksi "Otsi ja asenda".
5. Sisestage dialoogiboksis „Otsi ja asenda” järgmine
   1. Otsige, mida: * (tärn, millele järgneb tühik)
   2. Asenda: jäta see tühjaks
6. Klõpsake nuppu Asenda kõik.

Ülaltoodud sammud annaksid teile perekonnanime ja eemaldaksid kõik eesnime ees.

See toimib isegi siis, kui teil on nimed, millel on keskmine nimi.

Eemaldage keskmine nimi

Kui soovite vabaneda ainult keskmisest nimest ja teil on ainult ees- ja perekonnanimi, saate seda teha, kasutades funktsiooni Otsi ja asenda.

Oletame, et teil on allpool näidatud andmekogum ja soovite neist keskmise nime eemaldada.

Allpool on toodud sammud selle tegemiseks.

1. Kopeerige nime andmed veergu A ja kleepige need veergu B.
2. Kui veerus B olevad andmed on valitud, klõpsake vahekaarti Kodu
3. Klõpsake rühmas Redigeerimine nuppu Leia ja vali.
4. Klõpsake nuppu Asenda. See avab dialoogiboksi "Otsi ja asenda".
5. Sisestage dialoogiboksis „Otsi ja asenda” järgmine
   1. Otsige üles: * (tühik, millele järgneb tärn ja seejärel tühik)
   2. Asenda järgmisega: (pane siia tühik)
6. Klõpsake nuppu Asenda kõik.

Ülaltoodud toimingud eemaldaksid täisnimelt keskmise nime. Kui mõnel nimel pole keskmist nime, siis neid ei muudeta.

Eraldage nimed välktäite abil

Välktäitmine võeti kasutusele programmis Excel 2013 ja see muudab teksti andmekogumi muutmise või puhastamise väga lihtsaks.

Ja mis puutub nimede andmete eraldamisse, siis see on kohe Flash Filli alleel.

Välktäite kasutamisel on kõige olulisem teada, et välgu täitmisel on vaja mustrit. Kui see on mustri tuvastanud, aitab see teil hõlpsasti nimesid Excelis jagada (saate selle kohta rohkem selgust, kui vaatate läbi mõned näited allpool).

Hankige täisnimest ees- või perekonnanimi

Oletame, et teil on allpool näidatud andmekogum ja soovite saada ainult eesnime.

1. Sisestage külgnevasse lahtrisse eesnimi käsitsi täisnimest. Selle näite puhul kirjutaksin Rick.
2. Teises lahtris sisestage käsitsi külgneva lahtri nime eesnimi. Sisestamise ajal näete, et Flash Fill näitab teile automaatselt eesnime loendit (halli värviga).
3. Kui näete nimesid hallis, vaadake seda kiiresti ja veenduge, et see näitab õigeid nimesid. Kui need on õiged, vajutage sisestusklahvi ja välktäide täidab ülejäänud lahtrid automaatselt eesnimega.

Flash Fill peab andma sellele mustri, mida see saab järgida muudetud andmete esitamisel. Meie näites, kui sisestate esimesse lahtrisse eesnime, ei suuda Flash Fill mustrit välja mõelda.

Kuid niipea, kui hakkate teise lahtrisse eesnime sisestama, mõistab Flash Fill mustrit ja näitab teile soovitusi. Kui soovitus on õige, vajutage lihtsalt sisestusklahvi.

Ja kui see pole õige, võite proovida sisestada käsitsi veel mõne lahtri ja kontrollida, kas Flash Fill suudab mustrit eristada või mitte.

Mõnikord ei pruugi te mustrit hallina näha (nagu on näidatud ülaltoodud 2. toimingus). Sel juhul järgige välgutäite tulemuse saamiseks alltoodud samme.

1. Sisestage tekst käsitsi kahte lahtrisse.
2. Valige mõlemad lahtrid
3. Hõljutage kursorit valiku paremas alanurgas. Märkate, et kursor muutub plussikooniks
4. Topeltklõpsake seda (hiire vasak klahv). See täidab kõik lahtrid. Praegusel hetkel on tulemused tõenäoliselt valed ja mitte need, mida ootasite.
5. Saadud andmete paremas alanurgas näete väikest automaatse täitmise ikooni. Klõpsake seda automaatse täitmise ikooni
6. Klõpsake nuppu Flash Fill

Ülaltoodud toimingud annaksid teile välktäite tulemuse (selle järeldatud mustri põhjal).

Perekonnanime või keskmise nime saamiseks võite kasutada ka välktäitmist. Kahes esimeses lahtris sisestage perekonnanimi (või keskmine nimi) ja välktäide saab mustrist aru

Nime ümberkorraldamine välktäite abil

Flash Fill on nutikas tööriist ja see võib dešifreerida ka pisut keerukaid mustreid

Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite nime Rick Novakist ümber paigutada Novak, Rick (kus perekonnanimi järgneb kõigepealt koma ja seejärel eesnimi).

Allpool on toodud sammud selle tegemiseks.

1. Tippige külgnevasse lahtrisse käsitsi Novak, Rick
2. Tippige teise lahtrisse käsitsi Connor, Susan. Sisestamise ajal näete, et Flash Fill näitab teile samas vormingus nimede loendit (hallina).
3. Kui näete nimesid hallis, vaadake seda kiiresti ja veenduge, et see näitab õigeid nimesid. Kui need on õiged, vajutage sisestusklahvi ja Flash Fill täidab ülejäänud lahtrid automaatselt samas vormingus nimedega.

Eemaldage keskmine nimi (või hankige lihtsalt keskmine nimi)

Võite kasutada ka Flash Fillit, et vabaneda keskmisest nimest või saada ainult keskmine nimi.

Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite saada ainult ees- ja perekonnanime, mitte keskmist nime.

Allpool on toodud sammud selle tegemiseks.

1. Tippige külgnevasse lahtrisse käsitsi Rick Novak
2. Tippige teise lahtrisse käsitsi Susan Connor. Sisestamise ajal näete, et Flash Fill näitab teile samas vormingus nimede loendit (hallina).
3. Kui näete nimesid hallis, vaadake seda kiiresti ja veenduge, et see näitab õigeid nimesid. Kui need on õiged, vajutage sisestusklahvi ja välktäide täidab ülejäänud lahtrid automaatselt nimedega, millel puudub keskmine nimi.

Sarnaselt, kui soovite saada ainult keskmisi nimesid, tippige esimene nimi kahte esimesse lahtrisse ja kasutage Flash Fillit, et saada keskmine nimi kõigist ülejäänud nimedest.

Selles juhendis näidatud näited kasutavad tekstiandmetega manipuleerimisel nimesid. Samu mõisteid saate kasutada ka muude andmevormingutega (nt aadressid, tootenimed jne) töötamiseks.