V Excelu je velmi snadné rozdělit první, prostřední a příjmení, která se objevují ve stejném sloupci, do samostatných sloupců různými způsoby.
Předpokládejme, že jste obdrželi seznam kontaktů se všemi jmény uvedenými celými jmény v jednom sloupci a pravděpodobně budete muset oddělit první, prostřední a příjmení a rozdělit je do samostatných sloupců. Existuje několik různých způsobů, jak oddělit názvy – pomocí funkce Text do sloupců, výplně Flash a vzorců.
V Excelu je opravdu snadné rozdělit názvy z jednoho sloupce do dvou nebo více sloupců. V tomto článku vám ukážeme, jak rozdělit názvy do různých sloupců v aplikaci Excel různými způsoby.
Jak rozdělit jména v Excelu
Existují tři snadné způsoby rozdělení jmen v Excelu. V závislosti na struktuře dat a na tom, zda chcete, aby byly názvy rozdělení statické nebo dynamické, vyberte jednu z následujících metod:
- Rozdělit názvy pomocí funkce Text to Columns
- Oddělte názvy pomocí vzorců
- Oddělte názvy pomocí Flash Fill
Oddělte jména pomocí Průvodce textem do sloupce
Průvodce textem do sloupce je jedním z nejběžnějších způsobů, jak oddělit křestní jména a příjmení v aplikaci Excel a také prostřední jména. Ujistěte se, že vedle jmen, která chcete rozdělit, jsou prázdné sloupce, protože celá jména budou rozdělena do samostatných sloupců.
Například máte níže uvedenou datovou sadu s úplnými jmény a chcete rozdělit/oddělit jméno a příjmení a uložit je do samostatných buněk.
Nejprve zvýrazněte sloupec plných jmen, které chcete oddělit. Poté přejděte na kartu „Data“ a klikněte na možnost „Text do sloupců“ v části „Nástroje dat“.
Otevře se Průvodce převodem textu na sloupce. V prvním kroku průvodce vyberte možnost ‚Oddělený‘ a klikněte na ‚Další‘.
V kroku 2 ze 3 Průvodce převodem textu na sloupce vyberte oddělovač, který odděluje vaše data, odstraňte všechna ostatní zaškrtnutí a klikněte na „Další“. V našem případě ‚mezera‘ odděluje jméno a příjmení, proto zvolíme tento oddělovač.
Část Náhled dat níže v okně ukazuje, jak jsou vaše jména analyzována.
V kroku 3 ze 3 vyberete formát dat a cíl a kliknete na „Dokončit“.
Normálně bude výchozí „Obecné“ fungovat dobře pro většinu typů dat. V poli ‚Cíl‘ zadejte umístění, kde chcete výstup zobrazit. Musíte zadat adresu první buňky ve sloupci, kde chcete výsledky (v našem případě B2).
Pamatujte, že pokud neurčíte cílovou buňku, průvodce přepíše původní data, takže vyberte prázdný sloupec.
Po kliknutí na tlačítko „Dokončit“ se celá jména okamžitě rozdělí do dvou samostatných sloupců (Jméno a Příjmení).
Postupujte podle stejných kroků, pokud máte jméno, prostřední jméno a příjmení, a vaše jména budou rozdělena do tří sloupců namísto dvou.
Poznámka: Výsledek této metody je statický. To znamená, že pokud změníte původní název, musíte to udělat znovu, abyste jména rozdělili.
Rozdělit jména oddělená čárkami
Pokud jsou jména a příjmení oddělena čárkami, podle níže uvedených kroků odstraňte čárku a oddělte jméno a příjmení.
V následujícím příkladu jsou jména formátována v obráceném formátu (Příjmení, Jméno), kde je na prvním místě příjmení následované čárkou a poté křestním jménem.
Vyberte názvy a přejděte na Data –> Text do sloupce. V kroku 1 vyberte „Oddělovač“ a klikněte na „Další“. V kroku 2 v části Oddělovače zaškrtněte jako oddělovač ‚Čárka‘ (,), protože vaše jména jsou oddělena čárkou.
V posledním kroku zvolíte formát dat jako ‚Obecné‘, určíte cíl a kliknete na ‚Dokončit‘.
Nyní budete mít jména v samostatných sloupcích.
Oddělte jména pomocí vzorců
Průvodce Text to Columns umožňuje rychle a snadno oddělovat názvy. Pokud však chcete upravit původní názvy a chcete dynamickou metodu, která se bude automaticky aktualizovat při každé změně názvů, pak je rozdělení názvů pomocí vzorců tou správnou volbou. K oddělení jmen můžete použít funkce LEFT, RIGHT, MID, LEN a SEARCH nebo FIND.
Oddělte jméno a příjmení v Excelu pomocí vzorců
Získejte křestní jméno
Řekněme, že máte níže uvedenou datovou sadu a chcete oddělit křestní jméno do samostatné buňky. Chcete-li získat křestní jméno, musíte spojit funkci FIND a LEFT do jednoho vzorce.
K získání křestního jména použijte následující vzorec:
=LEFT(A2,FIND(" ",A2)-1)Tento vzorec používá funkci FIND k nalezení pozice znaku mezery („ ““) mezi jménem a příjmením a odečte 1, aby se mezera samotná vyloučila. Toto číslo je pak předáno funkci LEFT, která toto číslo pozice použije k extrahování veškerého textu před ním. Místo funkce FIND můžete také použít funkci HLEDAT.
Poté, co zadáte vzorec do prázdné buňky (B2), přetáhněte táhlo výplně dolů na jiné buňky, abyste použili tento vzorec, a všechna křestní jména budou rozdělena do sloupce B, jak je znázorněno níže:
Chcete-li extrahovat křestní jméno, můžete buď vnořit funkci SEARCH a FIND do funkce LEFT. Hlavní rozdíl mezi těmito dvěma funkcemi je v tom, že FIND rozlišuje malá a velká písmena, zatímco HLEDAT nerozlišuje malá a velká písmena.
Pokud máte místo mezery mezi jménem a příjmením čárku (,), použijte čárku jako první argument ve funkci FIND:
=LEFT(A2,FIND(",",A2)-1)Získejte příjmení
Nyní, pokud potřebujete extrahovat příjmení, použijte funkci RIGHT. Následující vzorec extrahuje příjmení ze stejné datové sady:
=RIGHT(A2,DÉLKA(A2)-NAJÍT(" ",A2))Vzorec nejprve najde pozici znaku mezery, odečte toto číslo od celkové délky řetězce (které vrací funkce LEN) a toto číslo je poté dodáno funkci RIGHT, která extrahuje tolik znaků z konce řetězce. (název).
Oddělte jméno, prostřední a příjmení v Excelu pomocí vzorců
Rozdělení jmen, která obsahují druhé jméno, vyžaduje různé vzorce v závislosti na formátu názvu, který máte.
Chcete-li získat jméno když máte druhé jméno nebo iniciály, použijte stejný vzorec LEFT FIND, který již znáte.
Získejte příjmení
Výše uvedený vzorec RIGHT FIND funguje dobře, pokud je k dispozici pouze jméno a příjmení, nebude příliš užitečný v případě, že vaše původní jména obsahují druhé jméno nebo iniciála. Je to proto, že jste v názvu nepočítali se dvěma mezerami.
Chcete-li získat příjmení, když máte i druhé jméno, použijte tento vzorec:
=RIGHT(A2,DÉLKA(A2)-HLEDAT(" ",A2,HLEDAT(" ",A2,1)+1))Chcete-li extrahovat příjmení, nejprve určete pozici druhého znaku mezery pomocí vnořených funkcí SEARCH, přidejte 1 k SEARCH(” “,A2,1) a začněte extrakci dalším znakem. Dále odečtěte pozici 2. mezery od celkové délky řetězce a získejte délku příjmení jako výsledné číslo. Toto výsledné číslo pak přiřaďte funkci RIGHT, abyste extrahovali počet znaků z konce řetězce.
Získejte prostřední jméno
Funkce MID používá tři argumenty, první argument určuje text nebo adresu buňky, druhý určuje počáteční pozici a poslední argument říká počtu znaků, které mají z této pozice extrahovat prostřední jméno.
Syntaxe:
=MID(text, počáteční_číslo, počet_znaků)Chcete-li získat druhé jméno, zadejte tento vzorec do prázdné buňky:
=MID(A2,HLEDAT(" ",A2)+1,HLEDAT(" ",A2,HLEDAT(" ",A2)+1)-HLEDAT(" ",A2)-1)Podívejme se, jak tento složitý vzorec funguje:
Chcete-li extrahovat druhé jméno nebo prostřední iniciálu, musíte určit pozici obou mezer v celém jméně. Chcete-li zjistit pozici prvního znaku mezery, zadejte toto HLEDAT(" ",A2) funkce v argumentu „počáteční_číslo“ a přidáním 1 zahájíte extrakci od dalšího znaku.
Pak, abyste zjistili délku druhého jména, zadejte toto SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1 vnořená funkce v argumentu ‚num_chars‘, která odečte pozici 1. mezery od pozice 2. mezery a odečte 1 od výsledku, aby se odstranila koncová mezera. Konečný výsledek vám řekne, kolik znaků je třeba extrahovat.
Nyní funkce MID s počáteční pozicí druhého jména a číselnými znaky k extrakci odděluje druhé jméno od celého jména (A2).
Oddělte názvy v Excelu pomocí Flash Fill
Flash výplň se většinou používá pro automatické vyplňování dat konkrétního vzoru. Lze jej také použít k rozdělení jmen. Je k dispozici pouze v Excelu 2013, 2016, 2019 a 365.
Řekněme, že máte níže uvedenou datovou sadu a chcete z celých jmen získat pouze křestní jména.
Do buňky sousedící s původním názvem zadejte křestní jméno. V tomto případě zadejte do buňky B2 ‚Steve‘.
Poté začněte psát křestní jméno do druhé buňky sloupce. Pokud během psaní Excel zjistí vzor, Flash Fill vám automaticky zobrazí seznam křestních jmen v ostatních buňkách (šedě).
Když uvidíte seznam jmen šedě a pokud jsou tato jména správná, stačí stisknout klávesu „Enter“ a Flash Fill automaticky vyplní zbytek sloupce křestními jmény.
Stejným postupem oddělte příjmení do samostatného sloupce.
Výsledek:
Flash Fill funguje tak, že detekuje vzor v těchto datech a následuje tento vzor, přičemž vám poskytuje upravená data. Nejprve, když zadáte křestní jméno do první buňky, Flash Fill nerozpozná vzor. Když ale začnete znovu psát křestní jméno do druhé buňky, Flash Fill rozpozná vzor a zobrazí návrh na rozdělení křestních jmen. Poté stačí stisknout klávesu „Enter“.
Obecně je funkce Flash Fill ve výchozím nastavení povolena. Pokud to ve vašem Excelu nefunguje, po zadání křestního jména do první buňky můžete jednoduše vybrat druhou buňku a kliknout na tlačítko ‚Flash Fill‘ ve skupině Data Tools na kartě ‚Data‘.
Případně můžete také stisknout „Ctrl“ + „E“ a získat stejné výsledky.
Nyní budou zbývající buňky vyplněny křestními jmény.
Někdy se může stát, že návrh vzoru neuvidíte šedě, v takovém případě můžete k získání výsledku Flash Fill použít úchyt výplně.
Nejprve zadejte názvy ručně do dvou buněk a vyberte obě tyto buňky. Poté umístěte kurzor do pravého dolního rohu výběru. Všimnete si, že se kurzor změní z malého zeleného čtverečku (ikona výplně) na ikonu plus.
Dále dvakrát klikněte na ikonu plus. Tím se vyplní zbytek buněk. V tomto okamžiku jsou výsledky nesprávné, uvidíte, že obě křestní jména se opakují znovu a znovu. Poté v pravém dolním rohu výsledných dat uvidíte malou ikonu automatického vyplňování, jak je znázorněno níže. Klikněte na tuto ikonu „Automatické vyplnění“ a vyberte „Flash Fill“.
To by vyplnilo křestní jména ve všech buňkách:
Odeberte prostřední jméno
K odstranění prostředního jména z celého jména můžete také použít nástroj Flash Fill.
Řekněme například, že máte níže uvedenou datovou sadu a chcete získat pouze jméno a příjmení bez druhého jména nebo iniciály.
Chcete-li získat jména bez druhého jména nebo prostřední iniciály, ručně zadejte „Lord Stark“ do sousední buňky. Poté do druhé sousední buňky napište ‚Daenerys Targaryen‘. Zatímco píšete, Flash Fill rozpozná vzor a zobrazí vám seznam jmen bez druhých jmen (šedě).
Pokud je návrh správný, stiskněte klávesu ‚Enter‘ a Flash Fill automaticky vyplní zbytek buněk jmény bez prostředního jména.
Pokud chcete získat pouze prostřední jména bez křestních jmen a příjmení, zadejte prostřední jméno do prvních dvou buněk a pomocí nástroje Flash Fill získejte prostřední jména ze všech celých jmen ve sloupci.
Tento tutoriál ukazuje, jak oddělovat názvy při manipulaci s textovými daty. Tyto metody mohou být také užitečné při práci s jinými formami dat, jako jsou adresy, názvy produktů, názvy značek atd.