Tato příručka vám ukáže, jak abecedně seřadit v Excelu pomocí funkce Seřadit a filtrovat, a vzorců pro uspořádání a formátování dat v pořadí.
Microsoft Excel je efektivní software pro organizování důležitých dat, jako jsou finance, prodejní data, informace o zákaznících atd. Abecední řazení dat v listech je jedním z nejlepších způsobů, jak rychle uspořádat data, získat přístup k nim a odkazovat na ně. Seřadit rozsah buněk podle abecedy znamená seřadit rozsah podle abecedy v Excelu. Data lze třídit oběma způsoby, buď vzestupně nebo sestupně.
Řekněme například, že máte velký soubor údajů o zákaznických objednávkách a chcete najít historii objednávek konkrétního zákazníka. Pročesat celý seznam bude docela oříšek. Spíše než ztrácet čas procházením můžete sloupce seřadit podle abecedy a rychle najít data.
Existují různé způsoby, které můžete použít k abecednímu řazení dat v Excelu, v závislosti na typu datové sady, se kterou pracujete. Excel umožňuje seřadit jeden sloupec nebo jeden řádek nebo vybranou oblast nebo celý list, stejně jako možnosti seskupit více sloupců nebo řádků dohromady, zatímco je třídíte. Pojďme se naučit, jak abecedně seřadit data v excelových tabulkách podle řádků nebo sloupců.
Proč seřadit data v Excelu podle abecedy
Seřazení dat podle abecedy bude mít řadu výhod:
- Díky tomu jsou data srozumitelnější a snáze čitelná.
- Usnadňuje uživateli vyhledání konkrétní hodnoty nebo jména zákazníka v datovém listu aplikace Excel.
- Pomáhá uživateli vizuálně identifikovat duplicitní záznamy, takže můžeme předejít chybám při zadávání dat.
- Umožňuje uživatelům snadno seskupit sloupce nebo seznamy, aby je uživatelé mohli prohlížet vedle sebe.
Abecední řazení (řazení podle abecedy) je snadný a běžný způsob, jak rychle třídit informace. To je užitečné zejména pro velmi velké datové sady. Celkem existují 4 způsoby řazení dat v Excelu podle abecedy: tlačítko A-Z nebo Z-A, funkce řazení, funkce filtrování a vzorce.
Seřazení sloupce podle abecedy v Excelu
Nejrychlejším způsobem abecedního řazení v Excelu je použití vestavěné funkce řazení.
Nejprve otevřete tabulku obsahující datovou sadu. Vyberte buňky nebo sloupec, který chcete seřadit, a ujistěte se, že nevybíráte žádné prázdné buňky. Poté přejděte na kartu „Data“ a klikněte buď na „A-Z“ pro seřazení vzestupně, nebo na „Z-A“ pro seřazení sestupně ve skupině Seřadit a filtrovat.
Pokud jsou mezi nimi nějaké prázdné buňky, Excel předpokládá, že všechna data byla seřazena, a zastaví se nad černou buňkou. Pokud chcete seřadit celý sloupec, vyberte libovolnou buňku ve sloupci a použijte výše uvedenou možnost.
Ke stejným možnostem lze přistupovat také pomocí nástroje „Seřadit a filtrovat“ v části Úpravy na kartě „Domů“.
Jakmile kliknete na tlačítko, Excel okamžitě seřadí váš seznam podle abecedy.
Jak třídit a udržovat řádky pohromadě v Excelu
Pokud máte v listu pouze jeden sloupec, můžete snadno seřadit sloupec, ale je to trochu komplikované, když list obsahuje více sloupců vedle sloupce, který chcete seřadit. Někdy také pracujeme na tabulkách, kde potřebujeme po seřazení sloupce zachovat odpovídající řádky nedotčené.
Například, pokud máme tabulku se seznamem známek studentů. Když seřadíme sloupec se jmény studentů v abecedním pořadí, chceme, aby se podle toho pohybovaly i známky zadané do řádků vedle nich.
V takových případech můžete pomocí tlačítka „A-Z“ nebo „Z-A“ seřadit jeden ze sloupců v abecedním pořadí a automaticky uspořádá data do dalších sloupců, přičemž řádky zůstanou pohromadě. Podívejme se, jak to funguje na jiném příkladu.
Nejprve vyberte první sloupec, podle kterého chcete seřadit další sloupce. Poté vyberte buď „A-Z“ nebo „Z-A“ na kartě Data nebo na kartě Domů, abyste data seřadili do jednoho sloupce a Excel podle toho automaticky přeuspořádá ostatní sloupce.
Po kliknutí na tlačítko řazení se zobrazí dialogové okno Varování pro řazení. Vyberte přepínač pro „Rozbalit výběr“ a klikněte na tlačítko „Seřadit“.
To udrží řádky pohromadě při řazení sloupců, aniž by se neshodovala žádná data (jak je uvedeno níže).
Pokud vyberete možnost „Pokračovat v aktuálním výběru“, Excel seřadí pouze vybraný sloupec.
Jak řadit abecedně podle více sloupců
Pokud chcete abecedně seřadit datovou sadu podle více než jednoho sloupce, můžete to udělat pomocí funkce Seřadit v Excelu.
Například chceme uspořádat následující tabulku abecedně nejprve podle země a poté podle jména:
Nejprve vyberte celou tabulku, kterou chcete seřadit podle abecedy, poté přejděte na kartu „Data“ a ve skupině Seřadit a filtrovat klikněte na „Seřadit“.
Nebo klikněte na ikonu „Seřadit a filtr“ v části Úpravy na kartě „Domů“ a vyberte „Vlastní třídění“, abyste získali přístup ke stejné funkci.
Zobrazí se dialogové okno Seřadit a zobrazí se první sloupec (parametr), ve kterém bude tabulka seřazena. V rozevíracím seznamu 'Sort by' vyberte primární sloupec, podle kterého chcete data nejprve seřadit podle abecedy, v našem případě 'Země' a v rozevíracím seznamu 'Pořadí' vyberte buď 'A až Z' nebo 'Z až A' .
Poté kliknutím na tlačítko „Přidat úroveň“ přidejte druhou úroveň řazení. Vyberte druhý sloupec (v našem případě Křestní jméno), podle kterého chcete data seřadit podle abecedy, v poli ‚Sort By‘ a vyberte buď ‚A až Z‘ nebo ‚Z to A‘. Pokud má vaše tabulka záhlaví nahoře, zaškrtněte políčko „Moje data mají záhlaví“, aby bylo možné při řazení přeskočit záhlaví. V případě potřeby přidejte další úrovně řazení a klikněte na „OK“.
Tabulka je uspořádána abecedně: nejprve podle země a poté podle jména, jak je uvedeno níže.
Jak řadit řádky abecedně v Excelu
Někdy možná budete chtít seřadit řádky v Excelu podle abecedy, nikoli sloupce. To lze provést pomocí funkce řazení aplikace Excel. Řekněme například, že máte tabulku, kde sloupce B až T v prvním řádku obsahují názvy měst, kde je počet obchodů dostupných pod různými typy maloobchodu, a sloupec A obsahuje typ prodejce. Buňky se používají ke sledování počtu obchodů v každém městě v různých kategoriích maloobchodníků.
Nejprve vyberte všechny řádky, které chcete seřadit podle abecedy; Pokud štítky řádků přesouvat nechcete, vynechejte je ve výběru. Nyní přejděte na kartu „Data“ a klikněte na „Seřadit“ ve skupině Seřadit a filtrovat.
V dialogovém okně Seřadit klikněte na tlačítko „Možnosti“.
Objeví se další malé dialogové okno, které vám dá možnost vybrat ‚Řadit zleva doprava‘ nebo ‚Řadit shora dolů‘. Vyberte možnost „Seřadit zleva doprava“ pro abecední řazení podle řádku a kliknutím na „OK“ se vraťte do dialogového okna Seřadit.
Nyní vyberte řádek, který chcete seřadit podle abecedy, v rozevíracím seznamu „Seřadit podle“ (v tomto příkladu řádek 1). V druhém poli ponechte Hodnoty buněk v poli „Třídit podle“ a v poli Pořadí vyberte „A až Z“ (vzestupně). Poté kliknutím na „OK“ zavřete dialogové okno.
Nyní je první řádek v naší tabulce seřazen v abecedním (vzestupném) pořadí a zbývající řádky jsou uspořádány podle toho, jak je uvedeno níže.
Jak abecedně seřadit sloupec pomocí filtru
Dalším rychlým způsobem abecedního řazení v Excelu je použití funkce Filtr. Jakmile použijete filtry na sloupce, lze k možnostem řazení pro všechny sloupce přistupovat jediným kliknutím myši.
Chcete-li do sloupců přidat filtr, vyberte jeden nebo více sloupců a klikněte na možnost „Filtr“ ve skupině Seřadit a filtrovat na kartě „Data“. Pokud chcete použít filtr na celou tabulku, vyberte libovolnou buňku v tabulce a klikněte na možnost ‚Filtr‘.
K možnosti Filtr se dostanete také kliknutím na nástroj „Seřadit a filtrovat“ ve skupině Úpravy na kartě „Domů“ a výběrem „Filtr“.
V záhlaví každého sloupce se objeví malá rozbalovací šipka. Klikněte na šipku libovolného sloupce, který chcete abecedně seřadit, a vyberte buď „Seřadit od A do Z“ nebo „Seřadit od Z do A“.
Tím uspořádáte požadovaný sloupec ve zvoleném abecedním pořadí, a pokud zvolíte „Seřadit od A do Z“, na tlačítku filtru se objeví malá šipka nahoru označující pořadí řazení (vzestupně):
Pokročilé třídění ve vlastní objednávce
Řazení podle abecedy není ideální pro všechna abecední data. Někdy lze data seřadit podle abecedy, ale není to nejefektivnější způsob, jak je třídit.
Představme si situaci, kdy můžete mít seznam obsahující názvy měsíců nebo dnů v týdnu, řazení podle abecedy v této situaci není užitečné. Je smysluplnější seřadit tento seznam chronologicky. Pokud seřadíte od A do Z, seřadí měsíce podle abecedy, jako první přijde na řadu duben, pak srpen, únor, červen atd. Ale to není to, co chcete. Naštěstí je velmi snadné seřadit v Excelu chronologicky pomocí možnosti Advanced Custom Sort.
Začněte výběrem tabulky, kterou chcete seřadit. Poté vyberte ‚Vlastní řazení‘ v části ‚Řadit a filtr‘ v části Úpravy na kartě Domů.
V dialogovém okně Seřadit vyberte sloupec, který obsahuje měsíce v roce v části sloupců, seřaďte jako obvykle podle ‚Hodnoty‘ a v části Pořadí vyberte ‚Vlastní seznam‘.
V dialogu Vlastní seznamy můžete vytvořit svůj vlastní seznam. Existuje několik výchozích vlastních seznamů včetně dnů v týdnech, zkrácených měsíců, měsíců v roce a tak dále. Vyberte si vhodnou možnost řazení podle svých potřeb (v našem případě leden, únor, ..) a klikněte na „OK“. Poté znovu klikněte na „OK“, abyste seznam seřadili chronologicky.
Všimnete si, že jste seznam úspěšně seřadili podle měsíčního pořadí.
Jak třídit abecedně v Excelu pomocí vzorců Excelu
Pokud jste fanouškem vzorců, můžete k seřazení seznamu podle abecedy použít vzorce Excel. Dva vzorce, které lze použít pro abecední řazení dat, jsou COUNTIF a SVYHLEDAT.
Máme například seznam jmen, která chceme seřadit podle abecedy pomocí vzorce.
Abychom to seřadili, přidáme do stávající tabulky dočasný sloupec s názvem „Pořadí řazení“.
V buňce (A2) vedle dat použijte následující vzorec COUNTIF:
=COUNTIF($B$2:$B$20,"<="&B2) Výše uvedený vzorec porovná textovou hodnotu v buňce B2 se všemi ostatními textovými hodnotami (B3:B20) a vrátí její relativní pořadí. Například v buňce B2 vrátí 11, protože existuje 11 textových hodnot, které jsou nižší nebo rovné textu „Nancy“ v abecedním pořadí. Výsledkem je pořadí řazení jména zaměstnance v buňce B2.
Poté pomocí úchytu výplně přetáhněte tento vzorec a vyplňte jej v celém rozsahu. Tím získáte pořadí řazení každého z názvů v seznamu.
Nyní musíme uspořádat data zobrazená na výše uvedeném snímku obrazovky na základě čísla pořadí řazení a k tomu použijeme funkci VLOOKUP.
Syntaxe:
=VLOOKUP(,A:B,2,0)„Číslo řazení“ zde představuje čísla ve vzestupném pořadí od 1 do 20. Pro sestupné pořadí by čísla měla být od 20 do 1.
Zde máme sloupec D pro seřazená jména. Pro buňku D2 zadejte následující vzorec SVYHLEDAT:
=VLOOKUP(1;A:B;2;0)Podobně pro druhou a třetí buňku musíte použít vzorec jako:
=VLOOKUP(2;A:B;2;0) a =VLOOKUP(3;A:B;2;0) a tak dále…
Po zadání tohoto vzorce VLOOKUP do každé buňky vedle dat se seznam seřadí podle abecedy.
Místo ručního zadávání výše uvedeného vzorce (1-20) do každé buňky můžete pro usnadnění práce použít také funkci řádku. Funkce Row () vrací číslo řádku aktuální buňky. Takže s pomocí funkce řádku bude vzorec:
=VLOOKUP(ROW()-1,A:B,2,0)Tento vzorec vám poskytne stejný výsledek jako výše uvedený vzorec.
A pak pomocí úchytu výplně přetáhněte tento vzorec tak, aby byl vyplněn v celém rozsahu.
Jak abecedně seřadit záznamy podle příjmení
Občas musíme datové listy seřadit podle abecedy s příjmením. V takových případech, i když jména začínají křestními jmény, je třeba je seřadit podle abecedy podle příjmení. Můžete to udělat pomocí textových vzorců aplikace Excel.
Chcete-li to provést, musíte nejprve extrahovat jméno a příjmení z celých jmen do dvou různých sloupců. Poté názvy přehoďte, seřaďte je a poté je obraťte zpět do původní podoby.
S celým názvem v A2 zadejte následující vzorce do dvou různých buněk (B2 a C2) a poté zkopírujte vzorce dolů po sloupcích (pomocí úchytu výplně) až do poslední buňky s daty:
Chcete-li extrahovat křestní jméno, zadejte tento vzorec do buňky C2:
=LEFT(A2,HLEDAT(” “,A2)-1)
Chcete-li extrahovat příjmení, zadejte do buňky D2 tento vzorec:
=RIGHT(A2,DÉLKA(A2)-HLEDAT(" ",A2;1))
A pak v buňce E2 zřetězil jména a příjmení v opačném pořadí oddělených čárkou:
=D2&", "&C2
Oddělili jsme jména a příjmení a obrátili jsme je. Nyní je musíme seřadit podle abecedy a obrátit je zpět. Jak můžete vidět výše, když extrahujeme názvy pomocí vzorce, sloupce C, D a E ve skutečnosti obsahují vzorce, ale jsou naformátovány tak, aby vypadaly jako hodnoty. Musíme tedy vzorec převést na hodnoty.
Chcete-li to provést, nejprve vyberte všechny buňky vzorce (E1:E31) a stiskněte Ctrl + C zkopírovat je. Klikněte pravým tlačítkem na vybrané buňky, klikněte na ikonu ‚Hodnoty‘ pod ‚Možnosti vložení‘ a stiskněte klávesu ‚Enter‘.
Poté vyberte libovolnou buňku ve výsledném sloupci a klikněte na možnost „A až Z“ nebo „Z až A“ na kartě „Data“ nebo „Domů“ podle vašich potřeb.
Po klepnutí na tlačítko se zobrazí dialogové okno Varování řazení. Vyberte možnost „Rozbalit výběr“ a klikněte na tlačítko „Seřadit“.
Jak vidíte, celý sloupec seřazen abecedně podle příjmení.
Nyní, pokud jej chcete vrátit zpět na původní „Formát jména a příjmení“, stačí zadat stejné vzorce, ale s jinými odkazy na buňky.
Název (E2) musíte znovu rozdělit na dvě části pomocí níže uvedených vzorců:
V G2 extrahujte křestní jméno:
=RIGHT(E2,DÉLKA(E2)-HLEDAT(" ",E2))
V G2 vytáhněte příjmení:
=LEFT(E2,HLEDAT(" ",E2)-2)
A spojte obě části dohromady, abyste získali původní celé jméno:
=G2&" "&H2
Pokaždé, když zadáte vzorec do první buňky, nezapomeňte zkopírovat vzorec dolů ve sloupci (pomocí úchytu výplně) až k poslední buňce s daty.
Nyní vše, co musíte udělat, je převést vzorce na převod hodnot ještě jednou, jak jsme to udělali výše, a máte hotovo. toto hotovo a.