Oddeľte meno a priezvisko v Exceli (rozdeľte mená pomocou vzorcov)

Sledujte video - Rozdeľte mená v Exceli (na krstné, stredné a priezvisko)

Excel je úžasný nástroj na krájanie a kocky textových údajov.

Existuje toľko užitočných vzorcov a funkcií, ktoré môžete použiť na prácu s textovými údajmi v programe Excel.

Jedna z veľmi častých otázok, ktoré sa mi týkajú manipulácie s textovými údajmi, je - „Ako oddeliť meno a priezvisko (alebo meno, priezvisko a priezvisko) v programe Excel?“.

V programe Excel existuje niekoľko jednoduchých spôsobov, ako rozdeliť mená. Metóda, ktorú si vyberiete, bude závisieť od toho, ako sú vaše údaje štruktúrované a od toho, či chcete, aby výsledok bol statický alebo dynamický.

Začnime teda a pozrime sa na rôzne spôsoby rozdelenia mien v programe Excel.

Rozdeľte mená pomocou textu na stĺpce

Funkcia textu na stĺpce v programe Excel vám umožňuje rýchlo rozdeliť textové hodnoty do samostatných buniek v rade.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete oddeliť meno a priezvisko a dostať ich do samostatných buniek.

Nasledujú kroky na oddelenie mena a priezviska pomocou textu na stĺpce:

1. Vyberte všetky mená v stĺpci (v tomto prípade A2: A10)
2. Kliknite na kartu „Údaje“
3. V skupine „Nástroje údajov“ kliknite na možnosť „Text do stĺpcov“.
4. V sprievodcovi Previesť text do stĺpca vykonajte nasledujúce zmeny:
   1. Krok 1 z 3: Vyberte položku Delimited (to vám umožní použiť ako oddeľovač priestor) a kliknite na Ďalej
   2. Krok 2 z 3: Vyberte možnosť Medzerník a kliknite na Ďalej
   3. Krok 3 z 3: Nastavte B2 ako cieľovú bunku (v opačnom prípade prepíše existujúce údaje)
5. Kliknite na Dokončiť

Vyššie uvedené kroky by okamžite rozdelili mená na meno a priezvisko (s krstnými menami v stĺpci B a priezviskom v stĺpci C).

Poznámka: V prípade, že v bunkách už sú nejaké údaje (tie, kde sa očakáva výstup textu do stĺpcov), Excel vám zobrazí varovanie, ktoré vás upozorní, že v bunkách už nejaké údaje sú. Môžete sa rozhodnúť prepísať údaje alebo zrušiť text do stĺpcov a najskôr ich manuálne odstrániť.

Po dokončení môžete úplné údaje o názvoch odstrániť, ak chcete.

Niekoľko vecí, ktoré by ste mali vedieť pri použití textu na stĺpce na oddelenie mien a priezvisk v programe Excel:

1. Výsledok je statický. To znamená, že v prípade, že máte nové údaje alebo pôvodné údaje majú nejaké zmeny, musíte to urobiť znova, aby ste rozdelili názvy.
2. Ak chcete iba krstné meno alebo iba priezvisko, môžete v kroku 3 dialógového okna „Sprievodca textom do stĺpca“ preskočiť stĺpce, ktoré nechcete. Ak to chcete urobiť, vyberte v ukážke stĺpec, ktorý chcete preskočiť, a potom vyberte možnosť „Neimportovať stĺpec (preskočiť)“.
3. V prípade, že nezadáte cieľovú bunku, text do stĺpca prepíše aktuálny stĺpec

Možnosť Text do stĺpcov je najvhodnejšia, ak máte konzistentné údaje (napríklad všetky mená majú iba meno a priezvisko alebo všetky mená majú meno, stred a priezvisko).

V tomto prípade som vám ukázal, ako oddeliť názvy, ktoré majú ako oddeľovač medzeru. V prípade, že oddeľovač je čiarka alebo kombinácia čiarky a medzery, stále môžete použiť rovnaké kroky. V takom prípade môžete oddeľovač zadať v kroku 2 z 3 sprievodcu. Už existuje možnosť použiť čiarku ako oddeľovač, alebo môžete vybrať možnosť „Iné“ a zadať tiež vlastný oddeľovač.

Aj keď je text do stĺpcov rýchly a efektívny spôsob delenia názvov, je vhodný iba vtedy, ak chcete, aby bol výstup statickým výsledkom. V prípade, že máte množinu údajov, ktorá sa môže rozšíriť alebo zmeniť, je lepšie oddeliť názvy pomocou vzorcov.

Mená, stredné a priezviská oddeľte pomocou vzorcov

Vzorce vám umožňujú nakrájať a nakrájať textové údaje na malé kocky a extrahovať to, čo chcete.

V tejto časti budem zdieľať rôzne vzorce, ktoré môžete použiť na oddelenie údajov o názvoch (podľa štruktúry vašich údajov).

Nasledujú tri vzorce, ktoré môžete použiť na oddelenie mena, priezviska a priezviska (podrobnejšie vysvetlené ďalej v nasledujúcich častiach).

Vzorec na získanie krstného mena:

= VĽAVO (A2, VYHĽADÁVANIE ("", A2) -1)

Vzorec na získanie stredného mena:

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

Vzorec na získanie priezviska:

= SPRÁVNE (A15, LEN (A15) -HĽADAŤ ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "" ""))))))

Získajte krstné meno

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete rýchlo oddeliť krstné meno v jednej bunke a priezvisko v jednej bunke.

Nasledujúci vzorec vám poskytne krstné meno:

= VĽAVO (A2, VYHĽADÁVANIE ("", A2) -1)

Vyššie uvedený vzorec používa funkciu VYHĽADÁVANIE na získanie polohy medzery medzi menom a priezviskom. Funkcia LEFT potom použije toto číslo medzery na extrahovanie celého textu pred ním.

Toto je celkom jednoduché použitie extrahovania časti textovej hodnoty. Pretože všetko, čo musíme urobiť, je identifikovať pozíciu prvého znaku medzery, nezáleží na tom, či má meno nejaké stredné meno alebo nie. Vyššie uvedený vzorec bude fungovať dobre.

Teraz poďme trochu pokročilejšie s každým príkladom.

Získajte priezvisko

Povedzme, že máte rovnaký súbor údajov a tentoraz musíte zadať priezvisko.

Nasledujúci vzorec extrahuje priezvisko z vyššie uvedenej množiny údajov:

= PRAVÉ (A2, LEN (A2) -HĽADAŤ ("", A2))

Opäť celkom priamočiare.

Tentokrát najskôr nájdeme pozíciu medzery, ktorá sa potom použije na zistenie počtu znakov, ktoré zostanú za medzerou (čo by bolo priezvisko).

To sa dosiahne odčítaním hodnoty polohy medzery od celkového počtu znakov v názve.

Toto číslo sa potom použije vo funkcii VPRAVO na načítanie všetkých týchto znakov napravo od mena.

Aj keď tento vzorec funguje skvele, keď existuje iba meno a priezvisko, nefungoval by v prípade, že máte aj stredné meno. Dôvodom je, že sme účtovali iba jeden znak medzery (medzi menom a priezviskom). Prostredné meno dodá názvu viac medzier.

Ak chcete získať priezvisko aj so stredným menom, použite nasledujúci vzorec:

= SPRÁVNE (A15, LEN (A15) -HĽADAŤ ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", "" ""))))))

Teraz to začalo byť trochu zložité … však?

Vysvetlím, ako to funguje.

Vyššie uvedený vzorec najskôr nájde celkový počet medzier v názve. To sa dosiahne tak, že sa zadá dĺžka názvu s medzerou a bez medzery a potom sa odčíta názov bez medzery od medzery. To dáva celkový počet medzier.

Funkcia SUBSTITUTE sa potom použije na nahradenie posledného znaku medzery symbolom „@“ (môžete použiť akýkoľvek symbol - niečo, čo sa ako súčasť názvu pravdepodobne nevyskytuje).

Keď nahradíte symbol @ namiesto posledného znaku medzery, môžete ľahko nájsť polohu tohto symbolu @. To sa vykonáva pomocou funkcie VYHĽADÁVAŤ.

Teraz stačí extrahovať všetky znaky napravo od tohto symbolu @. To sa deje pomocou funkcie DOPRAVA.

Získajte stredné meno

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete extrahovať stredné meno.

Na to slúži nasledujúci vzorec:

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

Vyššie uvedený vzorec používa funkciu MID, ktorá vám umožňuje určiť začiatočnú pozíciu a počet znakov, ktoré z tejto polohy chcete extrahovať.

Počiatočnú pozíciu je možné ľahko nájsť pomocou funkcie VYHĽADÁVAŤ.

Najťažšie je zistiť, koľko znakov je možné extrahovať po tejto počiatočnej pozícii. Aby ste to dosiahli, musíte zistiť, koľko znakov je od počiatočnej pozície po posledný znak medzery.

To sa dá dosiahnuť pomocou funkcie SUBSTITUTE a nahradenia posledného znaku medzery symbolom „@“. Akonáhle to urobíte, môžete ľahko použiť funkciu VYHĽADÁVANIE na nájdenie polohy tohto posledného znaku medzery.

Teraz, keď máte počiatočnú pozíciu a pozíciu posledného priestoru, môžete stredné meno ľahko načítať pomocou funkcie MID.

Jednou z výhod použitia vzorca na oddelenie mien je, že výsledok je dynamický. V prípade, že sa váš súbor údajov rozšíri a bude k nemu pridaných viac mien, alebo ak sa niektoré názvy v pôvodnom súbore údajov zmenia, nemusíte si s výslednými údajmi robiť starosti.

Oddeľte mená pomocou funkcie Nájsť a nahradiť

Milujem flexibilitu, ktorú prináša „Nájsť a nahradiť“ - pretože v nej môžete používať zástupné znaky.

Najprv vysvetlím, čo je to zástupný znak.

Zástupný znak je niečo, čo môžete použiť namiesto akéhokoľvek textu. Môžete napríklad použiť symbol hviezdičky (*) a v programe Excel bude predstavovať ľubovoľný počet znakov. Aby som vám dal príklad, ak chcem nájsť všetky mená, ktoré začínajú abecedou A, môžem pri hľadaní a nahradzovaní použiť A*. Nájde a vyberie všetky bunky, v ktorých názov začína na A.

Ak vám to stále nie je jasné, nebojte sa. Pokračujte v čítaní a niekoľko ďalších príkladov objasní, čo sú to zástupné znaky a ako ich použiť na rýchle oddelenie názvov (alebo akýchkoľvek textových hodnôt v programe Excel).

Vo všetkých nižšie uvedených príkladoch nezabudnite vytvoriť záložnú kópiu množiny údajov. Funkcia Nájsť a nahradiť zmení údaje, na ktorých sa používa. Najlepšie je najskôr skopírovať a prilepiť údaje a potom v skopírovanej množine údajov použiť funkciu Nájsť a nahradiť.

Získajte krstné meno

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete získať iba krstné meno.

Nasledujú kroky, ako to urobiť:

1. Skopírujte údaje o názve do stĺpca A a prilepte ich do stĺpca B.
2. Keď sú vybraté údaje v stĺpci B, kliknite na kartu Domov
3. V skupine Úpravy kliknite na Hľadať a vybrať.
4. Kliknite na Nahradiť. Otvorí sa dialógové okno „Nájsť a nahradiť“.
5. V dialógovom okne „Nájsť a nahradiť“ zadajte nasledujúce
   1. Zistite, čo: * (medzerník nasleduje za znakom hviezdičky)
   2. Nahradiť: ponechajte prázdne
6. Kliknite na položku Nahradiť všetko.

Vyššie uvedené kroky vám poskytnú krstné meno a odstránia všetko za krstným menom.

Funguje to, aj keď poznáte mená so stredným menom.

Pro tip: Klávesová skratka na otvorenie dialógového okna Hľadať a nahradiť je Ovládanie + H (podržte ovládací kláves a potom stlačte kláves H).

Získajte priezvisko

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete získať iba priezvisko.

Nasledujú kroky, ako to urobiť:

1. Skopírujte údaje o názve do stĺpca A a prilepte ich do stĺpca B.
2. Keď sú vybraté údaje v stĺpci B, kliknite na kartu Domov
3. V skupine Úpravy kliknite na Hľadať a vybrať.
4. Kliknite na Nahradiť. Otvorí sa dialógové okno „Nájsť a nahradiť“.
5. V dialógovom okne „Nájsť a nahradiť“ zadajte nasledujúce
   1. Zistite, čo: * (symbol hviezdičky nasleduje za medzerou)
   2. Nahradiť týmto: nechajte toto prázdne
6. Kliknite na položku Nahradiť všetko.

Vyššie uvedené kroky vám poskytnú priezvisko a odstránia všetko pred krstným menom.

Funguje to, aj keď poznáte mená so stredným menom.

Odstráňte stredné meno

V prípade, že sa chcete zbaviť iba stredného mena a mať iba krstné meno a priezvisko, môžete to urobiť pomocou funkcie Nájsť a nahradiť.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete z nich odstrániť stredné meno.

Nasledujú kroky, ako to urobiť:

1. Skopírujte údaje o názve do stĺpca A a prilepte ich do stĺpca B.
2. Keď sú vybraté údaje v stĺpci B, kliknite na kartu Domov
3. V skupine Úpravy kliknite na Hľadať a vybrať.
4. Kliknite na Nahradiť. Otvorí sa dialógové okno „Nájsť a nahradiť“.
5. V dialógovom okne „Nájsť a nahradiť“ zadajte nasledujúce
   1. Zistite, čo: * (medzerník, za ktorým nasleduje symbol hviezdičky a medzera)
   2. Nahradiť: (stačí sem vložiť medzeru)
6. Kliknite na položku Nahradiť všetko.

Vyššie uvedené kroky by odstránili stredné meno z celého mena. V prípade, že niektoré mená nemajú žiadne stredné meno, nebudú zmenené.

Oddeľte mená pomocou výplne Flash

Flash fill bol predstavený v Exceli 2013 a uľahčuje úpravu alebo čistenie sady textových údajov.

A pokiaľ ide o oddeľovanie údajov o menách, je to priamo v uličke Flash Fill.

Najdôležitejšou vecou, ​​ktorú musíte vedieť pri použití Flash Fill, je, že existuje vzor, ​​ktorý môže výplň Flash identifikovať. Akonáhle identifikuje vzor, ​​ľahko vám pomôže rozdeliť názvy v programe Excel (v tomto získate jasnejšie informácie, keď prejdete niekoľkými príkladmi nižšie).

Získajte krstné meno alebo priezvisko od celého mena

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete získať iba krstné meno.

1. Do susednej bunky ručne zadajte krstné meno z celého mena. V tomto prípade by som napísal Ricka.
2. Do druhej bunky ručne zadajte krstné meno z názvu susednej bunky. Počas písania sa zobrazí funkcia Flash Fill, ktorá vám automaticky zobrazí zoznam krstných mien (sivou farbou).
3. Keď uvidíte mená sivou farbou, rýchlo sa na ne pozrite a uistite sa, že zobrazuje správne mená. Ak sú správne, stlačte kláves Enter a funkcia Flash Fill automaticky vyplní ostatné bunky krstným menom.

Flash Fill od vás požaduje, aby ste mu poskytli vzor, ​​ktorý môže pri poskytovaní upravených údajov nasledovať. V našom prípade, keď zadáte krstné meno do prvej bunky, Flash Fill nemôže zistiť vzor.

Akonáhle však zadáte krstné meno do druhej bunky, Flash Fill porozumie vzoru a ukáže vám nejaký návrh. Ak je návrh správny, stačí stlačiť kláves Enter.

A ak to nie je správne, môžete skúsiť zadať manuálne do niekoľkých ďalších buniek a skontrolovať, či Flash Fill dokáže rozpoznať vzor alebo nie.

Niekedy sa môže stať, že vzor neuvidíte sivý (ako je uvedené v kroku 2 vyššie). Ak je to tak, výsledok Flash Fill získate nasledujúcim spôsobom:

1. Zadajte text ručne do dvoch buniek.
2. Vyberte obe tieto bunky
3. Umiestnite kurzor na pravú dolnú časť výberu. Všimnete si, že sa kurzor zmení na ikonu plus
4. Dvakrát naň kliknite (ľavé tlačidlo myši). Tým sa vyplnia všetky bunky. V tomto okamihu sú výsledky pravdepodobne nesprávne a nie to, čo ste očakávali.
5. V pravom dolnom rohu výsledných údajov sa zobrazí malá ikona automatického dopĺňania. Kliknite na túto ikonu automatického dopĺňania
6. Kliknite na položku Flash Fill

Vyššie uvedené kroky by vám poskytli výsledok z Flash Fill (na základe vzoru, ktorý odvodil).

Flash Fill môžete použiť aj na získanie priezviska alebo stredného mena. Do prvých dvoch buniek zadajte priezvisko (alebo stredné meno) a funkcia flash fill bude schopná porozumieť vzoru

Usporiadajte názov pomocou výplne Flash

Flash Fill je inteligentný nástroj a dokáže rozlúštiť aj mierne zložité vzory

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete zmeniť usporiadanie mena od Rick Novak po Novak, Rick (kde na prvom mieste je priezvisko, za ktorým nasleduje čiarka a potom krstné meno).

Nasledujú kroky, ako to urobiť:

1. Do susednej bunky zadajte ručne Novák, Rick
2. Do druhej bunky zadajte ručne Connor, Susan. Počas písania sa vo Flash Fill zobrazí zoznam mien v rovnakom formáte (sivou farbou).
3. Keď uvidíte mená sivou farbou, rýchlo sa na ne pozrite a uistite sa, že zobrazuje správne mená. Ak sú správne, stlačte kláves Enter a funkcia Flash Fill automaticky vyplní ostatné bunky názvami v rovnakom formáte.

Odstráňte stredné meno (alebo získajte iba stredné meno)

Flash Fill môžete použiť aj na zbavenie sa stredného mena alebo na získanie iba stredného mena.

Predpokladajme napríklad, že máte množinu údajov, ako je uvedené nižšie, a chcete získať iba krstné meno a priezvisko, nie stredné meno.

Nasledujú kroky, ako to urobiť:

1. Do susednej bunky zadajte ručne Rick Novak
2. Do druhej bunky zadajte ručne Susan Connorová. Počas písania sa vo Flash Fill zobrazí zoznam mien v rovnakom formáte (sivou farbou).
3. Keď uvidíte mená sivou farbou, rýchlo sa na ne pozrite a uistite sa, že zobrazuje správne mená. Ak sú správne, stlačte kláves Enter a funkcia Flash Fill automaticky vyplní ostatné bunky názvami bez stredného mena.

Podobne, ak chcete získať iba stredné mená, zadajte stredné meno do prvých dvoch buniek a pomocou funkcie Flash Fill získajte stredné meno zo všetkých zvyšných mien.

Príklady uvedené v tomto návode používajú názvy pri manipulácii s textovými údajmi. Rovnaké koncepty môžete použiť aj na prácu s inými formátmi údajov (ako sú adresy, názvy produktov atď.)