V niektorých prípadoch musíte bunky v Exceli rozdeliť.Môže ísť o získavanie údajov z databázy, kopírovanie údajov z internetu alebo získavanie údajov od kolegu.
Jednoduchý príklad, keď potrebujete rozdeliť bunky v Exceli, je, keď máte celé mená a chcete ich rozdeliť na mená a priezviská.
Alebo získate adresu“ a chcete adresu rozdeliť, aby ste mohli analyzovať mesto alebo heslo samostatne.
Ako rozdeliť bunky v Exceli
V tomto návode sa naučíte, ako rozdeliť bunky v Exceli pomocou nasledujúcich techník:
- Použite funkciu Text to Columns.
- Použite textové funkcie programu Excel.
- Použite Flash Fill (k dispozícii v rokoch 2013 a 2016).
Začnime!
Rozdeľte bunky v Exceli pomocou textu na stĺpec
Nižšie uvádzam mená niektorých mojich obľúbených fiktívnych postáv, ktoré by som rád rozdelil do samostatných buniek. :
Tu sú kroky na rozdelenie týchto mien na mená a priezviská:
- Vyberte bunku obsahujúcu text, ktorý chcete rozdeliť (v tomto prípade A2:A7).
- Kliknite na kartu Údaje
- V skupine Nástroje údajov kliknite na položku Text do stĺpcov.
- V sprievodcovi prevodom textu na stĺpce:
To okamžite rozdelí text bunky do dvoch rôznych stĺpcov.
poznámky:
- Funkcia Text to Columns rozdeľuje obsah buniek na základe oddeľovačov.Aj keď to funguje dobre, ak chcete oddeliť mená a priezviská, v prípade mena, stredného mena a priezviska to rozdelí na tri časti.
- Výsledok získaný pomocou funkcie Text to Column je statický.To znamená, že ak dôjde k akejkoľvek zmene v pôvodných údajoch, budete musieť proces zopakovať, aby ste získali aktualizované výsledky.
Rozdeľte bunky v Exceli pomocou textových funkcií
Textové funkcie programu Excel sú užitočné, keď chcete textové reťazce deliť a deliť na kocky.
Zatiaľ čo funkcia Text to Column poskytuje statické výsledky, výsledky získané pomocou funkcie sú dynamické a automaticky sa aktualizujú pri zmene pôvodných údajov.
Rozdeliť meno na meno a priezvisko
Predpokladajme, že máte rovnaké údaje, ako je tento:
názov extraktu
Ak chcete získať krstné mená z tohto zoznamu, použite nasledujúci vzorec:
=LEFT(A2,HĽADAŤ(" ",A2)-1)
Tento vzorec nájde prvý znak medzery a potom vráti všetok text pred znak medzery:
Tento vzorec používa funkciu HĽADAŤ na získanie pozície znaku medzery.V príklade Brucea Wayna je vesmírna postava na 6. mieste.Potom použije funkciu LEFT na extrahovanie všetkých znakov naľavo od nej.
Extrahujte priezvisko
Podobne na získanie priezviska použite nasledujúci vzorec:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
Tento vzorec používa funkciu HĽADAŤ na nájdenie umiestnenia medzerníka pomocou funkcie HĽADAŤ.Toto číslo potom odpočíta od celkovej dĺžky názvu (danej funkciou LEN).Toto udáva počet znakov v priezvisku.
Potom použite funkciu RIGHT na extrahovanie tohto priezviska.
Poznámka:Tieto funkcie nemusia fungovať správne, ak sú v názve úvodné, koncové alebo dvojité medzery.Kliknutím sem sa dozviete, ako odstrániť úvodné/koncové/dvojité medzery v Exceli.
Rozdeľte mená na mená, stredné a priezviská
V niektorých prípadoch môžete skončiť s kombináciou mien, z ktorých niektoré majú aj stredné mená.
Vzorec je v tomto prípade trochu komplikovaný.
názov extraktu
Ak chcete získať meno:
=LEFT(A2,HĽADAŤ(" ",A2)-1)
Toto je rovnaký vzorec, aký používame bez stredného mena.Vyhľadá iba prvý znak medzery a vráti všetky znaky pred medzeru.
extrahovať stredné meno
Ak chcete získať stredné meno:
=IFERROR(MID(A2,HĽADAŤ(" ",A2)+1,HĽADAŤ(" ",A2,HĽADAŤ(" ",A2)+1)-HĽADAŤ(" ",A2)),"")
Funkcia MID začína prvým znakom medzery a využíva rozdiel v polohe prvého a druhého znaku medzery na extrahovanie druhého mena.
Ak neexistuje stredné meno, funkcia MID vráti chybu.Aby sa predišlo chybám, je zabalený do funkcie IFERROR.
Extrahujte priezvisko
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) -SEARCH(" ",A2,SEARCH(" ",A2)+1)))
Tento vzorec kontroluje stredné meno (počítaním počtu medzier).Ak je tam iba 1 znak medzery, vráti všetok text napravo od znaku medzery.
Ale ak sú 2, potom nájde druhú medzeru a vráti počet znakov za druhou medzerou.
Poznámka: Tieto vzorce fungujú dobre, ak máte iba krstné mená a priezviská alebo mená, stredné a priezviská.Ak však máte kombináciu prípon alebo pozdravov, budete musieť vzorec ďalej upraviť.
Rozdeľte bunky v Exceli pomocou funkcie QuickFill
Flash Fill je nová funkcia predstavená v Exceli 2013.
Môže to byť veľmi užitočné, keď máte vzor a chcete z neho rýchlo extrahovať časti.
Zoberme si napríklad údaje o mene a priezvisku:
Rýchla výplň funguje tak, že rozpozná vzor a skopíruje ho do všetkých ostatných buniek.
Tu je návod, ako použiť Flash Fill na extrahovanie mien zo zoznamu:
Ako funguje Flash Fill?
Flash Fill nájde vzor v množine údajov a skopíruje ho.
Flash Fill je prekvapivo inteligentná funkcia, ktorá vo väčšine prípadov funguje dobre.Ale aj to v niektorých prípadoch zlyhá.
Napríklad, ak mám zoznam mien s kombináciami mien, niektoré majú stredné mená a iné nie.
Ak v tomto prípade extrahujem druhé meno, Flash Fill nesprávne vráti priezvisko v prípade, že neexistuje žiadne krstné meno.
Úprimne povedané, toto je stále dobré priblíženie trendu.Toto však nie je to, čo chcem.
Ale stále je to dosť dobrý nástroj na to, aby ste ho mohli mať vo svojom arzenáli a použiť ho v prípade potreby.
Tu je ďalší príklad rýchleho plnenia, ktorý funguje skvele.
Mám množinu adries, z ktorých chcem rýchlo extrahovať mestá.
Ak chcete rýchlo získať mesto, zadajte názov mesta z prvej adresy (v tomto príklade Londýn do bunky B2) a pomocou automatického dopĺňania vyplňte všetky bunky.Teraz použite Flash Fill a okamžite vám dá názov mesta pre každú adresu.
Podobne môžete rozdeliť adresu a extrahovať ľubovoľnú časť adresy.
Upozorňujeme, že to vyžaduje, aby adresy boli homogénnou množinou údajov s rovnakým oddeľovačom (v tomto prípade čiarkou).
Ak sa pokúsite použiť Flash Fill bez vzoru, zobrazí sa takáto chyba:
V tomto návode som sa zaoberal tromi rôznymi spôsobmi rozdelenia buniek v Exceli do viacerých stĺpcov (pomocou textu na stĺpce, vzorcov a rýchleho vyplnenia)
Dúfam, že tento návod Excelu bol pre vás užitočný.
