Erota etu- ja sukunimi Excelissä (jaetut nimet kaavojen avulla)

Katso video - Jaa nimet Excelissä (etu-, keski- ja sukunimeksi)

Excel on hämmästyttävä työkalu tekstitiedon leikkaamiseen ja paloitteluun.

On niin paljon hyödyllisiä kaavoja ja toimintoja, joita voit käyttää tekstitietojen käsittelyyn Excelissä.

Yksi hyvin yleisistä kysymyksistä, joita saan tekstidatan käsittelystä, on - "Kuinka erottaa etu- ja sukunimet (tai etu-, keski- ja sukunimet) Excelissä?“.

On olemassa pari helppoa tapaa jakaa nimet Excelissä. Valitsemasi menetelmä riippuu tietojen rakenteesta ja siitä, haluatko, että lopputulos on staattinen tai dynaaminen.

Joten aloitetaan ja katsotaan erilaisia ​​tapoja jakaa nimet Excelissä.

Jaa nimet käyttämällä tekstiä sarakkeiksi

Excelin Teksti sarakkeisiin -toiminnon avulla voit jakaa tekstiarvot nopeasti erillisiin soluihin peräkkäin.

Oletetaan esimerkiksi, että sinulla on alla olevan kuvan mukainen tietojoukko ja haluat erottaa etu- ja sukunimen ja saada ne erillisiin soluihin.

Alla on vaiheet etu- ja sukunimen erottamiseksi tekstistä sarakkeisiin:

1. Valitse kaikki nimet sarakkeesta (A2: A10 tässä esimerkissä)
2. Napsauta "Data" -välilehteä
3. Napsauta Datatyökalut -ryhmässä Teksti sarakkeisiin -vaihtoehtoa.
4. Tee seuraavat muutokset ohjatussa tekstin muuntamisessa sarakkeeseen:
   1. Vaihe 1/3: Valitse Erotettu (tämän avulla voit käyttää tilaa erottajana) ja napsauta Seuraava
   2. Vaihe 2/3: Valitse välilyönti ja napsauta Seuraava
   3. Vaihe 3/3: Aseta kohdesoluksi B2 (muuten se korvaa olemassa olevat tiedot)
5. Napsauta Valmis

Yllä olevat vaiheet jakavat nimet heti etu- ja sukunimiin (etunimet sarakkeessa B ja sukunimi sarakkeessa C).

Huomautus: Jos soluissa on jo tietoja (ne, joissa teksti -sarakkeet -tulostuksen odotetaan tapahtuvan), Excel näyttää varoituksen, joka kertoo, että soluissa on jo tietoja. Voit korvata tiedot tai peruuttaa teksti sarakkeisiin ja poistaa ne ensin manuaalisesti.

Kun olet valmis, voit poistaa koko nimen tiedot, jos haluat.

Muutama asia, joka sinun on tiedettävä, kun käytät tekstiä sarakkeisiin erottaaksesi etu- ja sukunimet Excelissä:

1. Tämän tulos on staattinen. Tämä tarkoittaa sitä, että jos sinulla on uusia tietoja tai alkuperäisiin tietoihin tulee joitain muutoksia, sinun on tehtävä tämä uudelleen jakaaksesi nimet.
2. Jos haluat vain etunimen tai vain sukunimen, voit ohittaa sarakkeet, joita et halua ohjatun tekstin sarakkeeseen -valintaikkunan vaiheessa 3. Voit tehdä tämän valitsemalla esikatselussa sarakkeen, jonka haluat ohittaa, ja valitsemalla sitten Älä tuo saraketta (ohita).
3. Jos et määritä kohdesolua, teksti sarakkeeseen korvaa nykyisen sarakkeen

Teksti sarakkeisiin -asetus sopii parhaiten, jos sinulla on johdonmukaiset tiedot (esimerkiksi kaikilla nimillä on vain etu- ja sukunimi tai kaikilla nimillä on etu-, keski- ja sukunimi).

Tässä esimerkissä olen näyttänyt sinulle, kuinka erottaa nimet, joissa on välilyönti. Jos erotin on pilkku tai pilkun ja välilyönnin yhdistelmä, voit silti käyttää samoja vaiheita. Tässä tapauksessa voit määrittää erottimen ohjatun toiminnon vaiheessa 2/3. Pilkkua voidaan käyttää erottimena, tai voit valita Muu -vaihtoehdon ja määrittää myös mukautetun erottimen.

Vaikka teksti sarakkeisiin on nopea ja tehokas tapa jakaa nimet, se sopii vain silloin, kun haluat tuloksen olevan staattinen tulos. Jos sinulla on tietojoukko, joka voi laajentua tai muuttua, sinun on parempi käyttää kaavoja nimien erottamiseen.

Erota etu-, keski- ja sukunimet kaavojen avulla

Kaavojen avulla voit leikata ja leikata tekstitiedot ja poimia haluamasi.

Tässä osiossa jaan erilaisia ​​kaavoja, joita voit käyttää nimitietojen erottamiseen (tietojen rakenteen perusteella).

Alla on kolme kaavaa, joiden avulla voit erottaa etu-, keski- ja sukunimen (selitetään yksityiskohtaisesti myöhemmin seuraavissa osissa).

Kaava etunimen saamiseksi:

= VASEN (A2, HAKU ("", A2) -1)

Kaava keskimmäisen nimen saamiseksi:

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

Kaava sukunimen saamiseksi:

= OIKEA (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "")))))

Hanki etunimi

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat nopeasti erottaa etunimen yhdessä solussa ja sukunimen yhdessä solussa.

Alla oleva kaava antaa sinulle etunimen:

= VASEN (A2, HAKU ("", A2) -1)

Yllä oleva kaava käyttää SEARCH -funktiota saadakseen välilyönnin sijainnin etu- ja sukunimen välistä. VASEN -funktio käyttää tämän välilyönnin numeroa poistaakseen kaiken sen edessä olevan tekstin.

Tämä on melko suoraviivainen käyttö, jossa osa tekstiarvosta puretaan. Koska meidän tarvitsee vain tunnistaa ensimmäinen välilyönti, ei ole väliä, onko nimellä jokin toinen nimi vai ei. Yllä oleva kaava toimii hyvin.

Mennään nyt hieman edistyneemmäksi jokaisella esimerkillä.

Hae sukunimi

Oletetaan, että sinulla on sama tietojoukko ja tällä kertaa sinun on saatava sukunimi.

Alla oleva kaava poimii sukunimen yllä olevasta tietojoukosta:

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

Jälleen melko suoraviivaista.

Tällä kertaa löydämme ensin välilyönnin sijainnin, jota käytetään sitten selvittämään välilyönnin jälkeen jäljellä olevien merkkien määrä (mikä olisi sukunimi).

Tämä saavutetaan vähentämällä välilyönnin sijainnin arvo nimen kokonaismäärästä.

Tätä numeroa käytetään sitten OIKEA -funktiossa kaikkien näiden merkkien noutamiseen nimen oikealta puolelta.

Vaikka tämä kaava toimii hyvin, kun on vain etu- ja sukunimi, se ei toimi, jos sinulla on myös toinen nimi. Tämä johtuu siitä, että meillä oli vain yksi välilyönti (etu- ja sukunimen välissä). Keskimmäisen nimen lisääminen lisää nimeen välilyöntejä.

Voit hakea sukunimen, kun sinulla on myös toinen nimi, käyttämällä alla olevaa kaavaa:

= OIKEA (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "")))))

Nyt tämä on alkanut muuttua hieman monimutkaiseksi … eikö niin?

Selitän kuinka tämä toimii.

Yllä oleva kaava löytää ensin nimen välilyönnien kokonaismäärän. Tämä tehdään saamalla nimen pituus välilyönnillä ja ilman sitä ja vähentämällä sitten välilyönnitön välilyönnistä. Tämä antaa välilyönnien kokonaismäärän.

SUBSTITUTE -toimintoa käytetään sitten viimeisen välilyönnin korvaamiseen@-merkillä (voit käyttää mitä tahansa symbolia - jotain, jota ei todennäköisesti esiinty osana nimeä).

Kun @ -merkki on korvattu viimeisen välilyönnin tilalle, löydät helposti @ -merkin sijainnin. Tämä tehdään käyttämällä SEARCH -toimintoa.

Nyt sinun tarvitsee vain purkaa kaikki merkit @ -merkin oikealta puolelta. Tämä tehdään käyttämällä OIKEA -toimintoa.

Hanki toinen nimi

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat poimia keskimmäisen nimen.

Seuraava kaava tekee tämän:

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

Yllä oleva kaava käyttää MID -toimintoa, jonka avulla voit määrittää aloituskohdan ja kyseisestä paikasta otettavien merkkien määrän.

Aloituskohta on helppo löytää käyttämällä SEARCH -toimintoa.

Vaikea osa on löytää kuinka monta merkkiä poimitaan tämän aloitusasennon jälkeen. Tämän saamiseksi sinun on tunnistettava, kuinka monta merkkiä on aloituskohdasta viimeiseen välilyöntiin.

Tämä voidaan tehdä käyttämällä SUBSTITUTE -toimintoa ja korvata viimeinen välilyönti@-merkillä. Kun tämä on tehty, voit etsiä tämän viimeisen välilyönnin sijainnin helposti SEARCH -toiminnolla.

Nyt kun sinulla on lähtö- ja viimeisen välilyönnin sijainti, voit helposti hakea keskimmäisen nimen käyttämällä MID -toimintoa.

Yksi eduista käyttämällä kaavaa nimien erottamiseen on, että tulos on dynaaminen. Joten jos tietojoukko laajenee ja siihen lisätään lisää nimiä tai jos jotkut nimet muuttuvat alkuperäisessä tietojoukossa, sinun ei tarvitse huolehtia tuloksena olevista tiedoista.

Erota nimet Etsi ja korvaa -toiminnolla

Rakastan Etsi ja korvaa -toiminnon joustavuutta - koska voit käyttää siinä jokerimerkkejä.

Selitän ensin, mikä on villi kortti.

Jokerimerkkiä voi käyttää tekstin sijasta. Voit esimerkiksi käyttää tähtimerkkiä (*), ja se edustaa mitä tahansa merkkiä Excelissä. Jos haluan löytää esimerkin, jos haluan löytää kaikki nimet, jotka alkavat aakkosella A, voin käyttää etsimistä ja korvaamista A*: lla. Tämä löytää ja valitse kaikki solut, joissa nimi alkaa A.

Jos et vieläkään ole selvä, älä huoli. Jatka lukemista, ja seuraavat esimerkit tekevät selväksi, mitkä ovat jokerimerkit ja miten niiden avulla erotetaan nimet (tai kaikki Excelin tekstiarvot) nopeasti.

Muista luoda alla olevista esimerkeistä varmuuskopio tietojoukosta. Etsi ja korvaa muuttaa tietoja, joita sitä käytetään. On parasta kopioida ja liittää tiedot ensin ja käyttää sitten Etsi ja korvaa kopioidussa tietojoukossa.

Hanki etunimi

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat saada vain etunimen.

Alla on vaiheet tämän tekemiseen:

1. Kopioi sarakkeen A nimitiedot ja liitä ne sarakkeeseen B.
2. Kun sarakkeen B tiedot on valittu, napsauta Etusivu -välilehteä
3. Napsauta Muokkaus -ryhmässä Etsi ja valitse.
4. Napsauta Korvaa. Tämä avaa Etsi ja korvaa -valintaikkunan.
5. Kirjoita "Etsi ja korvaa" -valintaikkunaan seuraava
   1. Etsi mitä: * (välilyönti ja tähti -symboli)
   2. Korvaa: jätä tämä tyhjäksi
6. Napsauta Korvaa kaikki.

Yllä olevat vaiheet antavat sinulle etunimen ja poistavat kaiken etunimen jälkeen.

Tämä toimii, vaikka sinulla olisi nimiä, joilla on toinen nimi.

Pro -vinkki: Etsi ja korvaa -valintaikkunan pikanäppäin on Control + H (pidä ohjausnäppäintä painettuna ja paina sitten H -näppäintä).

Hae sukunimi

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat saada vain sukunimen.

Alla on vaiheet tämän tekemiseen:

1. Kopioi sarakkeen A nimitiedot ja liitä ne sarakkeeseen B.
2. Kun sarakkeen B tiedot on valittu, napsauta Etusivu -välilehteä
3. Napsauta Muokkaus -ryhmässä Etsi ja valitse.
4. Napsauta Korvaa. Tämä avaa Etsi ja korvaa -valintaikkunan.
5. Kirjoita Etsi ja korvaa -valintaikkunaan seuraava
   1. Etsi mitä: * (tähtimerkki ja välilyönti)
   2. Korvaa: jätä tämä tyhjäksi
6. Napsauta Korvaa kaikki.

Yllä olevat vaiheet antavat sinulle sukunimen ja poistavat kaiken ennen etunimeä.

Tämä toimii, vaikka sinulla olisi nimiä, joilla on toinen nimi.

Poista toinen nimi

Jos haluat vain päästä eroon keskimmäisestä nimestä ja sinulla on vain etu- ja sukunimi, voit tehdä sen käyttämällä Etsi ja korvaa -painiketta.

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat poistaa keskimmäisen nimen näistä.

Alla on vaiheet tämän tekemiseen:

1. Kopioi sarakkeen A nimitiedot ja liitä ne sarakkeeseen B.
2. Kun sarakkeen B tiedot on valittu, napsauta Etusivu -välilehteä
3. Napsauta Muokkaus -ryhmässä Etsi ja valitse.
4. Napsauta Korvaa. Tämä avaa Etsi ja korvaa -valintaikkunan.
5. Kirjoita Etsi ja korvaa -valintaikkunaan seuraava
   1. Etsi mitä: * (välilyönti, jota seuraa tähti -symboli ja välilyönti)
   2. Korvaa seuraavalla: (laita vain välilyönti tähän)
6. Napsauta Korvaa kaikki.

Yllä olevat vaiheet poistaisivat keskimmäisen nimen koko nimestä. Jos joillakin nimillä ei ole keskinimeä, niitä ei muuteta.

Erilliset nimet Flash Fillin avulla

Flash -täyttö otettiin käyttöön Excel 2013: ssa, ja se tekee tekstitietojoukon muokkaamisesta tai puhdistamisesta todella helppoa.

Ja kun on kyse nimitietojen erottamisesta, se on aivan Flash Fillin kujalla.

Tärkein asia, joka on tiedettävä käytettäessä Flash Fill -toimintoa, on, että Flash -täyttö voi tunnistaa kuvion. Kun se on tunnistanut mallin, se auttaa sinua helposti jakamaan nimet Excelissä (saat enemmän selvyyttä tästä, kun käyt läpi muutamia alla olevia esimerkkejä).

Hanki etu- tai sukunimi koko nimestä

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat saada vain etunimen.

1. Kirjoita viereiseen soluun etunimi manuaalisesti koko nimestä. Tässä esimerkissä kirjoitan Rick.
2. Kirjoita toiseen soluun etunimi manuaalisesti viereisen solun nimestä. Kirjoittaessasi Flash Fill näyttää automaattisesti etunimen luettelon (harmaana).
3. Kun näet nimet harmaina, katso ne nopeasti läpi ja varmista, että ne näyttävät oikeat nimet. Jos nämä ovat oikein, paina Enter -näppäintä ja Flash Fill täyttää automaattisesti muut solut etunimellä.

Flash Fillin on annettava sille malli, jota se voi noudattaa, kun se antaa sinulle muutetut tiedot. Esimerkissämme, kun kirjoitat etunimen ensimmäiseen soluun, Flash Fill ei pysty selvittämään mallia.

Mutta heti kun alat kirjoittaa etunimeä toiseen soluun, Flash Fill ymmärtää mallin ja näyttää sinulle ehdotuksen. Jos ehdotus on oikea, paina vain Enter -näppäintä.

Ja jos se ei ole oikein, voit yrittää syöttää manuaalisesti muutamaan soluun ja tarkistaa, kykeneekö Flash Fill havaitsemaan kuvion vai ei.

Joskus kuvio ei ehkä näy harmaana (kuten yllä vaiheessa 2). Jos näin on, noudata seuraavia ohjeita saadaksesi Flash Fill -tuloksen:

1. Kirjoita teksti manuaalisesti kahteen soluun.
2. Valitse molemmat solut
3. Vie hiiri valinnan oikeaan alakulmaan. Huomaat, että kohdistin muuttuu plus -kuvakkeeksi
4. Kaksoisnapsauta sitä (hiiren vasen näppäin). Tämä täyttää kaikki solut. Tällä hetkellä tulokset ovat todennäköisesti vääriä eivätkä sitä mitä odotit.
5. Tuloksena olevien tietojen oikeassa alakulmassa näet pienen automaattisen täytön kuvakkeen. Napsauta tätä automaattisen täytön kuvaketta
6. Napsauta Flash Fill

Yllä olevat vaiheet antavat sinulle Flash Fillin tuloksen (sen johtaman mallin perusteella).

Voit myös käyttää Flash Fill -toimintoa saadaksesi sukunimen tai keskimmäisen nimen. Kirjoita sukunimi (tai keskimmäinen nimi) kahteen ensimmäiseen soluun, ja flash -täyttö voi ymmärtää kuvion

Järjestä nimi uudelleen Flash Fill -toiminnon avulla

Flash Fill on älykäs työkalu ja se voi tulkita myös hieman monimutkaisia ​​kuvioita

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat järjestää nimen uudelleen Rick Novakista Novak, Rick (jossa sukunimi tulee ensin ja sen jälkeen pilkku ja sitten etunimi).

Alla on vaiheet tämän tekemiseen:

1. Kirjoita viereiseen soluun manuaalisesti Novak, Rick
2. Kirjoita toiseen soluun manuaalisesti Connor, Susan. Kirjoittaessasi Flash Fill näyttää luettelon nimistä samassa muodossa (harmaana).
3. Kun näet nimet harmaina, katso ne nopeasti läpi ja varmista, että ne näyttävät oikeat nimet. Jos nämä ovat oikein, paina Enter -näppäintä ja Flash Fill täyttää automaattisesti muut solut samassa muodossa olevilla nimillä.

Poista toinen nimi (tai hanki vain keskimmäinen nimi)

Voit myös käyttää Flash -täyttöä päästäksesi eroon keskimmäisestä nimestä tai saadaksesi vain keskimmäisen nimen.

Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat saada vain etu- ja sukunimen etkä keskimmäistä nimeä.

Alla on vaiheet tämän tekemiseen:

1. Kirjoita viereiseen soluun manuaalisesti Rick Novak
2. Kirjoita toiseen soluun manuaalisesti Susan Connor. Kirjoittaessasi Flash Fill näyttää luettelon nimistä samassa muodossa (harmaana).
3. Kun näet nimet harmaina, katso ne nopeasti läpi ja varmista, että ne näyttävät oikeat nimet. Jos nämä ovat oikein, paina Enter -näppäintä ja Flash Fill täyttää automaattisesti muut solut nimillä ilman keskimmäistä nimeä.

Samoin, jos haluat vain saada keskimmäiset nimet, kirjoita keskimmäinen nimi kahteen ensimmäiseen soluun ja käytä Flash Fill -toimintoa saadaksesi keskimmäisen nimen kaikista muista nimistä.

Tässä opetusohjelmassa esitetyt esimerkit käyttävät nimiä käsitellessään tekstitietoja. Voit käyttää samoja käsitteitä myös muiden tietomuotojen kanssa (kuten osoitteet, tuotenimet jne.)