Ako rozdeliť bunky v programe Excel (rozdeliť do viacerých stĺpcov)

Môžu nastať situácie, keď budete musieť bunky v Exceli rozdeliť. Môže to byť vtedy, keď údaje získate z databázy alebo ich skopírujete z internetu alebo ich získate od kolegu.

Jednoduchý príklad, kedy je potrebné rozdeliť bunky v Exceli, je, keď máte celé mená a chcete ich rozdeliť na krstné meno a priezvisko.

Alebo dostanete adresu ‘a chcete adresu rozdeliť, aby ste mohli analyzovať mestá alebo kód PIN oddelene.

Ako rozdeliť bunky v programe Excel

V tomto návode sa naučíte, ako rozdeliť bunky v programe Excel pomocou nasledujúcich techník:

• Použitie funkcie Text do stĺpca.
• Používanie textových funkcií programu Excel.
• Použitie Flash Fill (k dispozícii v rokoch 2013 a 2016).

Poďme začať!

Rozdeľte bunky v programe Excel pomocou textu do stĺpca

Nasleduje zoznam mien niektorých mojich obľúbených fiktívnych postáv a tieto mená chcem rozdeliť do samostatných buniek:

Tu je postup, ako rozdeliť tieto mená na krstné meno a priezvisko:

• Vyberte bunky, v ktorých máte text, ktorý chcete rozdeliť (v tomto prípade A2: A7).
• Kliknite na kartu Údaje
• V skupine „Nástroje údajov“ kliknite na „Text do stĺpcov“.
• V sprievodcovi prevodom textu na stĺpce:
  • Krok 1 z 3 sprievodcu textom do stĺpcov: Uistite sa, že je vybratá možnosť Delimited (je to predvolený výber). To vám umožní oddeliť krstné meno a priezvisko na základe zadaného oddeľovača (v tomto prípade medzerníka).
  • Kliknite na Ďalej.
  • Krok 2 z 3 Sprievodca textom do stĺpcov: Vyberte medzerník ako medzerník a zrušte výber všetkého. Ako bude váš výsledok vyzerať, môžete vidieť v dialógovom okne v časti Ukážka údajov.
  • Kliknite na Ďalej.
  • Krok 3 z 3 Sprievodca textom do stĺpcov: V tomto kroku môžete určiť formát údajov a miesto, kde chcete výsledok. Formát údajov ponechám ako všeobecný, pretože mám textové údaje na rozdelenie. Predvolený cieľ je A2 a ak v tom budete pokračovať, nahradí pôvodný súbor údajov. Ak chcete zachovať pôvodné údaje nedotknuté, vyberte ako cieľ inú bunku. V tomto prípade je vybratý B2.
  • Kliknite na Dokončiť.

Tým sa text bunky okamžite rozdelí do dvoch rôznych stĺpcov.

Poznámka:

• Funkcia Text na stĺpec rozdeľuje obsah buniek na základe oddeľovača. Aj keď to funguje dobre, ak chcete oddeliť krstné meno a priezvisko, v prípade krstného mena, priezviska a priezviska ho rozdelí na tri časti.
• Výsledok, ktorý získate z použitia funkcie Text na stĺpec, je statický. To znamená, že ak dôjde k akýmkoľvek zmenám v pôvodných údajoch, budete musieť postup zopakovať, aby ste získali aktualizované výsledky.

Rozdeľte bunky v programe Excel pomocou textových funkcií

Funkcie textu v Exceli sú skvelé, ak chcete rozrezať a nakrájať textové reťazce.

Aj keď funkcia Text na stĺpec poskytuje statický výsledok, výsledok, ktorý získate z používania funkcií, je dynamický a automaticky sa aktualizuje pri zmene pôvodných údajov.

Rozdelenie mien, ktoré majú krstné meno a priezvisko

Predpokladajme, že máte rovnaké údaje, ako sú uvedené nižšie:

Extrahovanie krstného mena

Na získanie krstného mena z tohto zoznamu použite nasledujúci vzorec:

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

Tento vzorec by našiel prvý znak medzery a potom vrátil všetok text pred tento znak medzery:

Tento vzorec používa funkciu VYHĽADÁVANIE na získanie polohy medzery. V prípade Brucea Wayna je vesmírna postava na 6. pozícii. Potom pomocou funkcie VĽAVO extrahuje všetky znaky naľavo od neho.

Extrahovanie priezviska

Podobne, ak chcete získať priezvisko, použite nasledujúci vzorec:

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

Tento vzorec používa funkciu vyhľadávania na nájdenie polohy medzerníka pomocou funkcie VYHĽADÁVAŤ. Potom číslo odpočíta od celkovej dĺžky názvu (ktorá je daná funkciou LEN). To udáva počet znakov v priezvisku.

Toto priezvisko sa potom extrahuje pomocou funkcie DOPRAVA.

Poznámka: Tieto funkcie nemusia fungovať správne, ak máte v názvoch úvodné, koncové alebo dvojité medzery. Kliknutím sem sa dozviete, ako v programe Excel odstrániť úvodné/koncové/dvojité medzery.

Rozdelenie mien, ktoré majú krstné meno, stredné meno a priezvisko

Môžu nastať prípady, keď dostanete kombináciu mien, kde niektoré mená majú aj stredné meno.

Vzorec v takýchto prípadoch je trochu zložitý.

Extrahovanie krstného mena

Ak chcete získať krstné meno:

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

Je to ten istý vzorec, ktorý sme používali, keď neexistovalo žiadne stredné meno. Jednoducho vyhľadá prvý znak medzery a vráti všetky znaky pred medzeru.

Extrahovanie stredného mena

Ak chcete získať stredné meno:

= IFERROR (MID (A2, SEARCH ("", A2)+1, SEARCH ("", A2, SEARCH ("", A2) +1) -HĽADAŤ ("", A2)), "")

Funkcia MID začína od prvého znaku medzery a extrahuje stredné meno pomocou rozdielu polohy prvého a druhého znaku medzery.

V prípade, že neexistuje žiadne stredné meno, funkcia MID vráti chybu. Aby sa predišlo chybe, je zabalený do funkcie IFERROR.

Extrahovanie priezviska

Ak chcete získať priezvisko, použite nasledujúci vzorec:

= IF (LEN (A2) -LEN (SUBSTITUTE (A2, "", "")) = 1, RIGHT (A2, LEN (A2) -SEARCH ("", A2)), RIGHT (A2, LEN (A2) -HĽADAŤ ("", A2, VYHĽADÁVAŤ ("", A2) +1)))

Tento vzorec kontroluje, či existuje stredné meno alebo nie (počítaním počtu medzier). Ak existuje iba 1 medzerník, jednoducho vráti všetok text napravo od medzery.

Ale ak sú 2, potom spozoruje druhý znak medzery a vráti počet znakov za druhým medzerom.

Poznámka: Tento vzorec funguje dobre, ak máte mená, ktoré majú iba prvé meno a priezvisko, alebo krstné, stredné a priezvisko. Ak však máte mix s príponami alebo pozdravmi, budete musieť vzorce ďalej upraviť.

Rozdeľte bunky v programe Excel pomocou programu Flash Fill

Flash Fill je nová funkcia zavedená v programe Excel 2013.

Mohlo by to byť veľmi praktické, keď máte vzor a chcete rýchlo extrahovať jeho časť.

Zoberme si napríklad údaje o krstnom mene a priezvisku:

Flash fill funguje tak, že identifikuje vzory a replikuje ich pre všetky ostatné bunky.

Takto môžete zo zoznamu extrahovať krstné meno pomocou funkcie Flash Fill:

• Do bunky B2 zadajte krstné meno pre Bruce Wayne (t.j. Bruce).
• Keď je bunka vybratá, všimnete si malý štvorec na pravom konci výberu bunky. Dvakrát naň kliknite. Tým sa vo všetkých bunkách vyplní rovnaký názov.
• Keď sú bunky vyplnené, vpravo dole sa vám zobrazí ikona Možnosti automatického dopĺňania. Kliknite naň.
• V zozname vyberte položku Flash Fill.
• Hneď ako vyberiete položku Flash Fill, všimnete si, že všetky bunky sa aktualizujú samy a pre každé meno sa teraz zobrazí krstné meno.

Ako funguje Flash Fill?

Flash Fill hľadá vzory v množine údajov a replikuje vzor.

Flash Fill je prekvapivo múdra funkcia a vo väčšine prípadov funguje podľa očakávania. Ale v niektorých prípadoch to tiež zlyhá.

Napríklad, ak mám zoznam mien, ktorý obsahuje kombináciu mien, pričom niektoré majú stredné meno a niektoré nie.

Ak v takom prípade extrahujem stredné meno, Flash Fill chybne vráti priezvisko v prípade, že neexistuje krstné meno.

Aby som bol úprimný, je to stále dobré priblíženie trendu. Nie je to však to, čo som chcel.

Ale stále je to dosť dobrý nástroj na to, aby ste ho udržali vo svojom arzenáli a používali ho vždy, keď to bude potrebné.

Tu je ďalší príklad, kde Flash Fill funguje skvele.

Mám súbor adries, z ktorých chcem rýchlo extrahovať mesto.

Ak chcete rýchlo získať mesto, zadajte názov mesta pre prvú adresu (v tomto prípade zadajte Londýn do bunky B2) a pomocou automatického dopĺňania vyplňte všetky bunky. Teraz použite Flash Fill a z každej adresy vám okamžite poskytne názov mesta.

Podobne môžete rozdeliť adresu a extrahovať akúkoľvek časť adresy.

Upozorňujeme, že na to by adresa potrebovala byť homogénnou množinou údajov s rovnakým oddeľovačom (v tomto prípade čiarkou).

V prípade, že sa pokúsite použiť Flash Fill a neexistuje žiadny vzor, ​​zobrazí sa vám chyba, ako je uvedené nižšie:

V tomto návode som sa zaoberal tromi rôznymi spôsobmi rozdelenia buniek v programe Excel do viacerých stĺpcov (pomocou textu na stĺpce, vzorcov a výplne Flash)

Dúfam, že ste našli tento návod na Excel užitočný.