Podívejte se na video - Jak seřadit podle příjmení v Excelu
Pokud pracujete s datovými sadami názvů, je jejich třídění jedním z běžných úkolů, které byste museli dělat často.
Je docela snadné třídit data podle abecedy podle úplného jména, kde Excel používá k řazení první znak jména.
Ale co když chcete třídit data podle příjmení v Excelu?
I když to není tak jednoduché, stále to lze provést (hodně také závisí na způsobu, jakým jsou strukturována data jmen).
Bez ohledu na to, jakou metodu použijete, budete muset nějakým způsobem extrahovat příjmení z celého jména a zadat jej do samostatného sloupce. Tento sloupec pak můžete použít k seřazení dat podle příjmení podle abecedy.
V tomto tutoriálu k Excelu vám ukážu, jak seřadit sloupec se jmény podle příjmení.
Začněme tedy!
Extrahujte a seřaďte podle příjmení pomocí funkce Najít a nahradit
Prvním krokem k seřazení podle příjmení je získání příjmení v samostatném sloupci.
Můžete to udělat tak, že nahradíte vše před příjmením prázdným místem, takže vám zbude pouze příjmení.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete tato data seřadit abecedně podle příjmení.
Níže jsou uvedeny kroky k seřazení podle příjmení:
- Vyberte datovou sadu včetně záhlaví (v tomto případě by to bylo A1: A10)
- Zkopírujte jej do sousedního sloupce (pokud sousední sloupec není prázdný, vložte nový sloupec a poté zkopírujte tyto názvy)
- Přejmenujte zkopírovanou hlavičku sloupce. V tomto případě se budu jmenovat „Příjmení“
- Vyberte všechny zkopírované názvy (nevybírejte záhlaví)
- Podržte klávesu Control a poté stiskněte klávesu H. Otevře se dialogové okno Najít a nahradit.
- Do pole Najít zadejte * (symbol hvězdičky následovaný mezerou)
- Pole Nahradit ponechejte prázdné
- Klikněte na Nahradit vše. To by okamžitě nahradilo celé křestní jméno a zůstanou vám pouze příjmení.
Výše uvedené kroky by zachovaly příjmení a odstranily vše před ním. To funguje dobře, i když máte prostřední jména nebo předpony (například pan nebo paní).
Jakmile máte v sousedním sloupci příjmení, můžete datovou sadu (včetně celých jmen) snadno řadit abecedně podle příjmení.
Níže jsou uvedeny kroky k seřazení podle příjmení:
- Vyberte celou datovou sadu se záhlavími (včetně úplných jmen a extrahovaných příjmení). Spolu s názvy můžete také zahrnout další sloupce, které chcete seřadit
- Klikněte na kartu Data
- Klikněte na Seřadit
- V dialogovém okně Seřadit zkontrolujte, zda je vybrána možnost „Moje data mají záhlaví“.
- V možnosti „Seřadit podle“ vyberte název sloupce, který obsahuje pouze příjmení
- V části „Seřadit zapnuto“ vyberte „Hodnoty buněk“
- V možnosti Objednat vyberte „A až Z“
- Klikněte na OK
Výše uvedené kroky by seřadily celou vybranou datovou sadu podle příjmení.
Až budete hotovi, můžete sloupec s příjmením smazat.
Profesionální tip: Pokud si kdykoli myslíte, že možná budete potřebovat původní data zpět, musíte mít způsob, jak tuto datovou sadu zrušit. Chcete -li to provést, v sousedním sloupci (vlevo nebo vpravo) mějte před řazením pořadová čísla. Pokud nyní potřebujete původní data zpět, získáte je tříděním podle čísel.Extrahujte a dělejte abecedu podle příjmení pomocí vzorce
Zatímco metoda, která je uvedena výše (pomocí Najít a nahradit), je to, co dávám přednost získávání všech příjmení a řazení na základě nich, jedním omezením je, že výsledná data jsou statická.
To znamená, že pokud do svého seznamu přidám více jmen, budu muset znovu získat stejný název, abych získal příjmení.
Pokud něco takového nechcete, můžete použít metodu vzorce k třídění dat podle příjmení.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže.
Níže je vzorec, který extrahuje příjmení z celého jména:
= RIGHT (A2, LEN (A2) -FIND ("", A2))
Výše uvedený vzorec se spoléhá na vzor s celým jménem (který v tomto příkladu obsahuje pouze jméno a příjmení). Vzorec je takový, že mezi jménem a příjmením by byl mezerník.
Funkce FIND slouží k získání pozice znaku mezery. Tato hodnota se pak odečte od celkové délky jména, aby se získal celkový počet znaků v příjmení.
Tato hodnota je poté použita ve funkci VPRAVO k získání příjmení.
Jakmile máte sloupec příjmení, můžete tato data seřadit (podrobně je to popsáno v první metodě).
Výše uvedený vzorec by fungoval, pokud máte pouze jména a příjmení.
Ale co když máte také prostřední jméno. Nebo může být před jménem pozdrav (například pan nebo paní)
V takovém případě musíte použít následující vzorec:
= RIGHT (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))))
Výše uvedený vzorec najde pozici posledního znaku mezery a poté jej použije k extrahování příjmení.
Doporučuji, abyste ve všech případech použili druhý vzorec, který je bezpečnější a zvládne všechny případy (pokud je příjmení na konci jména).
Poznámka: Tyto dva vzorce vycházejí z podmínky, že mezi každým elementem name je pouze jeden znak mezery. V případě, že existují dvojité mezery nebo úvodní/koncové mezery, tento vzorec poskytne nesprávné výsledky. V takovém případě je nejlepší použít funkci TRIM, abyste se nejprve zbavili všech úvodních, koncových a dvojitých mezer a poté použijte výše uvedený vzorec.
I když se to může zdát jako složitá metoda, výhodou použití vzorce je, že výsledky jsou dynamické. Pokud do seznamu přidáte více jmen, stačí vzorec zkopírovat a poskytne vám příjmení.
Použití textu na sloupce
Text do sloupců je opět jednoduchý a snadný způsob rozdělení buněk v aplikaci Excel.
Můžete určit oddělovač (například čárku nebo mezeru) a použít ho k rozdělení obsahu buňky. Jakmile máte rozdělené prvky v samostatných sloupcích, můžete sloupec s příjmením použít k abecednímu seřazení dat.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Níže jsou uvedeny kroky k použití řazení textu na sloupec podle příjmení:
- Vyberte sloupec s názvem (kromě záhlaví)
- Klikněte na kartu Data
- Ve skupině „Nástroje dat“ klikněte na možnost Text do sloupců. Tím se otevře průvodce textem do sloupců
- V kroku 1 „Průvodce převodem textu na sloupce“ vyberte „Oddělovač“ a klikněte na Další
- V kroku 2 vyberte jako oddělovač „Mezerník“ (a pokud je zaškrtnuto, zrušte zaškrtnutí čehokoli jiného) a poté klikněte na tlačítko Další.
- V kroku 3 vyberte v náhledu dat sloupec s prvním jménem a poté vyberte možnost „Neimportovat sloupce (přeskočit)“. Tím je zajištěno, že křestní jméno nebude součástí výsledku a vy získáte pouze příjmení.
- Také v kroku 3 změňte cílovou buňku na buňku, která sousedí s původními daty. Tím zajistíte, že příjmení získáte samostatně a údaje o původních jménech budou neporušené.
- Klikněte na Dokončit
Jakmile budete mít výsledek, můžete seřadit podle příjmení.
Pokud je jako oddělovač čárka, můžete také oddělit text na sloupce a oddělit jména a příjmení.
Použití Flash Fill
Další rychlý a rychlý způsob získání příjmení je pomocí funkce Flash Fill.
Flash Fill byl představen v aplikaci Excel 2013 a pomáhá manipulovat s daty pomocí identifikace vzorů. Aby to fungovalo, musíte několikrát ukázat výsledek Flash Fill, který očekáváte.
Jakmile identifikuje vzorec, rychle za vás udělá zbytek práce.
Předpokládejme, že máte níže uvedenou datovou sadu názvů.
Níže jsou uvedeny kroky, jak pomocí Flash Fill získat příjmení a poté jej třídit:
- Do buňky B2 zadejte text „Maury“. To je výsledek, který v buňce očekáváte.
- Přejděte na další buňku a zadejte příjmení pro jméno v sousední buňce (v tomto případě Elliot).
- Vyberte obě buňky
- Umístěte kurzor na pravou dolní část výběru. Všimnete si, že se kurzor změní na ikonu plus.
- Poklepejte na něj (nebo klikněte a přetáhněte jej dolů). To vám poskytne určitý výsledek v buňkách (pravděpodobně to nebude požadovaný výsledek)
- Klikněte na ikonu Možnosti automatického vyplňování.
- Klikněte na Flash Fill
Tím získáte výsledek, který bude pravděpodobně příjmením ve všech buňkách.
Říkám pravděpodobné, protože Flash Fill nemusí v některých případech fungovat. Protože to závisí na identifikaci vzoru, nemusí to být vždy možné. Nebo někdy vzor, který dešifruje, nemusí být ten správný.
V takových případech byste měli zadat očekávaný výsledek do jedné nebo dvou dalších buněk a poté provést kroky 4-7.
Jakmile máte ve sloupci všechna příjmení, můžete data seřadit podle těchto příjmení.
Toto jsou tedy čtyři různé způsoby, které můžete použít k třídění dat podle příjmení. Nejlepší metodou by bylo použít techniku Najít a nahradit, ale pokud chcete, aby byly vaše výsledky dynamické, použijte metodu vzorce.
Doufám, že jste našli tento návod užitečný.
