Excelissä on erittäin helppoa jakaa samassa sarakkeessa esiintyvät etu-, keski- ja sukunimet eri tavoilla eri sarakkeiksi.
Oletetaan, että sait yhteystietoluettelon, jossa on kaikki nimet lueteltuina täydellisinä niminä yhdessä sarakkeessa, ja sinun on luultavasti erotettava etu-, keski- ja sukunimi ja jaettava ne erillisiin sarakkeisiin. Voit erottaa nimet muutamalla eri tavalla – käyttämällä Tekstistä sarakkeiksi -ominaisuutta, Flash-täyttöä ja kaavoja.
Excelissä on todella helppoa jakaa nimet yhdestä sarakkeesta kahteen tai useampaan sarakkeeseen. Tässä artikkelissa näytämme sinulle, kuinka nimet jaetaan eri sarakkeisiin Excelissä eri tavoilla.
Kuinka jakaa nimet Excelissä
On kolme helppoa tapaa jakaa nimet Excelissä. Valitse jokin seuraavista menetelmistä riippuen tietorakenteesta ja siitä, haluatko jaetun nimen olevan staattinen vai dynaaminen:
- Jaa nimet tekstistä sarakkeiksi -ominaisuuden avulla
- Erottele nimet kaavoilla
- Erottele nimet Flash Fill -toiminnolla
Erottele nimet ohjatulla tekstistä sarakkeeksi -toiminnolla
Ohjattu teksti sarakkeeksi -toiminto on yksi yleisimmistä tavoista erottaa etu- ja sukunimet Excelissä sekä keskinimet. Varmista, että jaettavien nimien vieressä on tyhjiä sarakkeita, koska koko nimet jaetaan erillisiin sarakkeisiin.
Sinulla on esimerkiksi alla oleva tietojoukko täydellisillä nimillä ja haluat jakaa/erottaa etu- ja sukunimet ja tallentaa ne erillisiin soluihin.
Korosta ensin sarake kokoisista nimistä, jotka haluat erottaa. Siirry sitten "Data" -välilehteen ja napsauta "Teksti sarakkeisiin" -vaihtoehtoa "Tietotyökalut" -osiossa.
Ohjattu tekstin muunnos sarakkeiksi avautuu. Valitse ohjatun toiminnon ensimmäisessä vaiheessa 'Delimited' -vaihtoehto ja napsauta 'Seuraava'.
Valitse ohjatun tekstin muunnos sarakkeiksi -toiminnon vaiheessa 2/3 erotin, joka erottaa tietosi, poista muut valintamerkit ja napsauta Seuraava. Meidän tapauksessamme välilyönti erottaa etu- ja sukunimet, joten valitsemme tämän erottimen.
Ikkunan alla oleva Tietojen esikatselu -osio näyttää, kuinka nimesi jäsennetään.
Vaiheessa 3/3 valitset tietomuodon ja kohteen ja napsautat Valmis.
Normaalisti oletusarvoinen "Yleinen" toimii hyvin useimmille tietotyypeille. Määritä Kohde-kenttään sijainti, jossa haluat tulosteen näkyvän. Sinun on määritettävä sarakkeen ensimmäisen solun osoite, johon haluat tulokset (tapauksessamme B2).
Muista, että jos et määritä kohdesolua, ohjattu toiminto korvaa alkuperäiset tiedot, joten muista valita tyhjä sarake.
Kun napsautat Valmis-painiketta, se erottaa täydelliset nimet välittömästi kahteen erilliseen sarakkeeseen (etunimi ja sukunimi).
Noudata samoja vaiheita, jos sinulla on etu-, keski- ja sukunimi, ja nimesi jaetaan kolmeen sarakkeeseen kahden sijaan.
merkintä: Tämän menetelmän tulos on staattinen. Se tarkoittaa, että jos muutat alkuperäistä nimeä, sinun on tehtävä tämä uudelleen jakaaksesi nimet.
Jaetut nimet pilkuilla erotettuina
Jos etu- ja sukunimet erotetaan pilkuilla, poista pilkku ja jaa etu- ja sukunimi seuraavien ohjeiden mukaisesti.
Seuraavassa esimerkissä nimet muotoillaan käänteisessä muodossa (Sukunimi, Etunimi), jossa sukunimi tulee ensin, sen jälkeen pilkku ja sen jälkeen etunimi.
Valitse nimet ja siirry kohtaan Data -> Text to Column. Valitse vaiheessa 1 Erotin ja napsauta Seuraava. Valitse Vaiheessa 2 Erottimet-kohdassa erotinmerkki "Pilkku" (,), koska nimesi on erotettu pilkulla.
Viimeisessä vaiheessa valitset tietomuodoksi 'Yleinen', määrität kohteen ja napsautat 'Valmis'.
Nyt sinulla on nimet erillisissä sarakkeissa.
Erottele nimet kaavoilla
Tekstistä sarakkeiksi ohjattu toiminto on nopea ja helppo erottaa nimet. Jos kuitenkin haluat muokata alkuperäisiä nimiä ja haluat dynaamisen menetelmän, joka päivittyy automaattisesti aina, kun muutat nimiä, nimien jakaminen kaavoilla on oikea valinta. Voit käyttää LEFT-, RIGHT-, MID-, LEN- ja SEARCH- tai FIND-toimintoja nimien erottamiseen.
Erottele etu- ja sukunimi Excelissä kaavoilla
Hanki etunimi
Oletetaan, että sinulla on alla oleva tietojoukko ja haluat erottaa etunimen erilliseen soluun. Sinun on yhdistettävä ETSI- ja LEFT-funktiot yhdeksi kaavaksi saadaksesi etunimen.
Käytä seuraavaa kaavaa saadaksesi etunimi:
=VASEN(A2,ETSI(" ",A2)-1)Tämä kaava käyttää ETSI-funktiota etsimään välilyönnin (" ") paikan etu- ja sukunimen väliltä ja vähentää 1:n sulkeakseen pois itse välilyönnin. Tämä numero syötetään sitten LEFT-funktiolle, joka käyttää tätä paikkanumeroa poimimaan kaiken sitä edeltävän tekstin. Voit myös käyttää ETSI-toimintoa ETSI-toiminnon sijasta.
Kun olet kirjoittanut kaavan tyhjään soluun (B2), vedä täyttökahva alas muihin soluihin käyttääksesi tätä kaavaa, ja kaikki etunimet on jaettu sarakkeeseen B alla olevan kuvan mukaisesti:
Voit joko upottaa SEARCH ja FIND -toiminnot LEFT-funktion sisään etunimen poimimiseksi. Suurin ero näiden kahden funktion välillä on se, että FIND on kirjainkoon erottelukykyinen, kun taas SEARCH ei erota kirjainkoosta.
Jos etunimen ja sukunimen välissä on pilkku (,) välilyönnin sijaan, käytä pilkkua FIND-funktion ensimmäisenä argumenttina:
=VASEN(A2,ETSI(",",A2)-1)Hanki sukunimi
Jos sinun on nyt poimittava sukunimi, käytä OIKEA-toimintoa. Seuraava kaava poimii sukunimen samasta tietojoukosta:
=OIKEA(A2,PITO(A2)-ETSI(" ",A2))Kaava etsii ensin välilyöntimerkin sijainnin, vähennä tämä luku merkkijonon kokonaispituudesta (jonka LEN-funktio palauttaa), ja tämä luku syötetään sitten OIKEA-funktiolle, jotta merkkijonon lopusta saadaan niin monta merkkiä. (nimi).
Erottele etu-, keski- ja sukunimi Excelissä kaavoilla
Toisen nimen sisältävien nimien jakaminen vaatii erilaisia kaavoja käyttämäsi nimimuodon mukaan.
Saadaksesi etunimi kun sinulla on toinen nimi tai keskimmäinen alkukirjain, käytä samaa LEFT FIND -kaavaa, jonka jo tunnet.
Hanki sukunimi
Yllä oleva RIGHT FIND -kaava toimii hyvin, kun on vain etu- ja sukunimi, siitä ei ole paljon hyötyä, jos alkuperäisissä nimissäsi on toinen nimi tai keskimmäinen alkukirjain. Tämä johtuu siitä, että et huomioinut nimessä kahta välilyöntiä.
Jos haluat saada sukunimen, kun sinulla on myös toinen nimi, käytä tätä kaavaa:
=OIKEA(A2,PITO(A2)-HAKU(" ",A2,HAKU(" ",A2,1)+1))Poimiksesi sukunimen, määritä ensin toisen välilyönnin sijainti käyttämällä sisäkkäisiä SEARCH-funktioita, lisää 1 kohtaan SEARCH(” “,A2,1), jolloin purkaminen aloitetaan seuraavalla merkillä. Seuraavaksi vähennä 2. välilyönnin sijainti merkkijonon kokonaispituudesta ja hanki sukunimen pituus tuloksena. Anna sitten tämä luku OIKEALLE-funktiolle poimimaan merkkien lukumäärän merkkijonon lopusta.
Hanki keskimmäinen nimi
MID-funktio käyttää kolmea argumenttia, joista ensimmäinen määrittää tekstin tai solun osoitteen, toinen määrittää aloituspaikan ja viimeinen argumentti kertoo merkkien lukumäärän, jotta keskimmäinen nimi saadaan erotettua kyseisestä paikasta.
Syntaksi:
=MID(teksti, aloitusnumero, merkkien määrä)Saat keskimmäisen nimen kirjoittamalla tämän kaavan tyhjään soluun:
=KESKUS(A2,HAKU(" ",A2)+1,HAKU(" ",A2,HAKU(" ",A2)+1)-HAKU(" ",A2)-1)Katsotaan kuinka tämä monimutkainen kaava toimii:
Jotta voit poimia keskimmäisen nimen tai keskimmäisen alkukirjaimen, sinun on määritettävä molempien välilyöntien sijainti koko nimessä. Voit etsiä ensimmäisen välilyönnin sijainnin kirjoittamalla tämän HAKU(" ",A2) funktio 'aloitus_num'-argumentissa ja lisää 1 aloittaaksesi purkamisen seuraavasta merkistä.
Laita sitten tämä saadaksesi selville keskimmäisen nimen pituuden HAKU(" ",A2,HAKU(" ",A2)+1)-HAKU(" ",A2)-1 sisäkkäinen funktio 'num_chars'-argumentissa, joka vähentää 1. välilyönnin sijainnin 2. välilyönnin paikasta ja 1:n tuloksesta poistaakseen lopputilan. Lopputulos kertoo, kuinka monta merkkiä puretaan.
Nyt MID-toiminto, jossa on keskimmäisen nimen aloituspaikka ja poimittavat numeromerkit, erottaa keskimmäisen nimen koko nimestä (A2).
Erottele nimet Excelissä Flash Fillillä
Flash-täyttöä käytetään useimmiten tietyn kuvion tietojen automaattiseen täyttämiseen. Sitä voidaan käyttää myös nimien jakamiseen. Se on saatavilla vain Excel 2013-, 2016-, 2019- ja 365-versioissa.
Oletetaan, että sinulla on alla oleva tietojoukko ja haluat saada koko nimistä vain etunimet.
Kirjoita etunimi alkuperäisen nimen viereiseen soluun. Kirjoita tässä tapauksessa "Steve" soluun B2.
Aloita sitten etunimen kirjoittaminen sarakkeen toiseen soluun. Jos Excel havaitsee kuvion kirjoittaessasi, Flash Fill näyttää sinulle automaattisesti listan muiden solujen etunimestä (harmaalla).
Kun näet nimiluettelon harmaana ja jos nuo nimet ovat oikein, paina vain Enter-näppäintä ja Flash Fill täyttää automaattisesti muun sarakkeen etunimillä.
Erota sukunimet erilliseen sarakkeeseen noudattamalla samaa menettelyä.
Lopputulos:
Flash Fill toimii havaitsemalla kuvion tiedoista ja noudattamalla sitä samalla kun saat muokatut tiedot. Aluksi, kun kirjoitat etunimen ensimmäiseen soluun, Flash Fill ei tunnista kuviota. Mutta kun alat kirjoittaa etunimeä uudelleen toiseen soluun, Flash Fill tunnistaa kuvion ja näyttää ehdotuksen etunimien jakamisesta. Paina sitten 'Enter'-näppäintä.
Yleensä Flash Fill -ominaisuus on oletusarvoisesti käytössä. Jos se ei toimi Excelissäsi, kun olet kirjoittanut etunimen ensimmäiseen soluun, voit valita toisen solun ja napsauttaa 'Flash Fill' -painiketta Data Tools -ryhmästä Data-välilehdellä.
Vaihtoehtoisesti voit myös painaa 'Ctrl' + 'E' saadaksesi samat tulokset.
Nyt loput solut täytetään etunimillä.
Joskus kuvioehdotusta ei ehkä näy harmaana, jolloin voit käyttää täyttökahvaa saadaksesi Flash Fill -tuloksen.
Kirjoita ensin nimet manuaalisesti kahteen soluun ja valitse molemmat solut. Vie sitten osoitin valinnan oikeaan alakulmaan. Huomaat, että kohdistin muuttuu pienestä vihreästä neliöstä (täyttökuvake) pluskuvakkeeksi.
Seuraavaksi kaksoisnapsauta pluskuvaketta. Tämä täyttää loput solut. Tässä vaiheessa tulokset ovat virheellisiä, näet molemmat etunimet toistuvat uudestaan ja uudestaan. Sitten tuloksena olevien tietojen oikeassa alakulmassa näet pienen automaattisen täytön kuvakkeen, kuten alla on esitetty. Napsauta tätä "Automaattinen täyttö" -kuvaketta ja valitse "Flash Fill".
Tämä täyttäisi etunimet kaikissa soluissa:
Poista keskimmäinen nimi
Voit myös käyttää Flash Fill -työkalua päästäksesi eroon keskimmäisestä nimestä koko nimestä.
Oletetaan esimerkiksi, että sinulla on alla oleva tietojoukko ja haluat saada vain etu- ja sukunimen ilman keskimmäistä nimeä tai alkukirjainta.
Jos haluat saada nimet ilman keskimmäistä nimeä tai keskimmäistä alkukirjainta, kirjoita manuaalisesti "Lord Stark" viereiseen soluun. Kirjoita sitten toiseen viereiseen soluun "Daenerys Targaryen". Kun kirjoitat, Flash Fill tunnistaa kuvion ja näyttää luettelon nimistä ilman keskimmäisiä nimiä (harmaalla).
Jos ehdotus on oikea, paina 'Enter'-näppäintä ja Flash Fill täyttää automaattisesti loput solut nimillä ilman keskimmäistä nimeä.
Jos haluat saada vain toiset nimet ilman etu- ja sukunimiä, kirjoita toinen nimi kahteen ensimmäiseen soluun ja käytä Flash Fill -työkalua saadaksesi keskimmäiset nimet kaikista sarakkeen kokonaisista nimistä.
Tämä opetusohjelma näyttää, kuinka nimiä voidaan erottaa tekstidatan käsittelyn aikana. Nämä menetelmät voivat olla hyödyllisiä myös muiden tietojen, kuten osoitteiden, tuotenimien, tuotenimien jne., kanssa.