Táto príručka vám ukáže, ako zoradiť v Exceli podľa abecedy pomocou funkcie Zoradiť a filtrovať, a vzorcov na usporiadanie a formátovanie údajov v poradí.
Microsoft Excel je efektívny softvér na organizovanie dôležitých údajov, ako sú financie, údaje o predaji, informácie o zákazníkoch atď. Zoradenie údajov v hárkoch podľa abecedy je jedným z najlepších spôsobov, ako rýchlo usporiadať, získať prístup k týmto údajom a odkazovať na ne. Zoradenie rozsahu buniek podľa abecedy znamená abecedné zoradenie rozsahu v programe Excel. Údaje je možné triediť oboma spôsobmi, buď vzostupne alebo zostupne.
Povedzme napríklad, že máte veľký súbor údajov o objednávkach zákazníka a chcete nájsť históriu objednávok konkrétneho zákazníka. Prečesať celý zoznam bude poriadna fuška. Namiesto toho, aby ste strácali čas listovaním, môžete stĺpce zoradiť podľa abecedy, aby ste rýchlo našli svoje údaje.
Existujú rôzne spôsoby, ktoré môžete použiť na zoradenie údajov v Exceli podľa abecedy v závislosti od typu množiny údajov, na ktorej pracujete. Excel vám umožňuje zoradiť jeden stĺpec alebo jeden riadok alebo vybraný rozsah alebo celý pracovný hárok, ako aj možnosti zoskupenia viacerých stĺpcov alebo riadkov pri ich zoraďovaní. Dozvieme sa, ako zoradiť údaje v tabuľkách programu Excel podľa abecedy podľa riadkov alebo stĺpcov.
Prečo abecedne usporiadať údaje v Exceli
Zoradenie údajov podľa abecedy bude mať množstvo výhod:
- Vďaka tomu sú údaje rozumnejšie a ľahšie sa čítajú.
- Používateľovi to uľahčuje vyhľadanie konkrétnej hodnoty alebo mena zákazníka v údajovom hárku programu Excel.
- Pomáha používateľovi vizuálne identifikovať duplicitné záznamy, aby sme predišli chybám pri zadávaní údajov.
- Umožňuje používateľom jednoducho zoskupovať stĺpce alebo zoznamy, aby si ich používatelia mohli prezrieť vedľa seba.
Zoradenie podľa abecedy (abecedné zoradenie) je jednoduchý a bežný spôsob rýchleho triedenia informácií. Je to užitočné najmä pre veľmi veľké súbory údajov. Celkovo existujú 4 spôsoby zoradenia údajov v Exceli podľa abecedy: tlačidlo A-Z alebo Z-A, funkcia triedenia, funkcia filtrovania a vzorce.
Zoraďovanie stĺpcov podľa abecedy v Exceli
Najrýchlejší spôsob triedenia podľa abecedy v Exceli je pomocou vstavanej funkcie triedenia.
Najprv otvorte tabuľku, ktorá obsahuje množinu údajov. Vyberte bunky alebo stĺpec, ktorý chcete zoradiť, a uistite sa, že ste nevybrali žiadne prázdne bunky. Potom prejdite na kartu „Údaje“ a kliknite buď na „A-Z“, ak chcete zoradiť vzostupne, alebo na „Z-A“, ak chcete zoradiť zostupne, v skupine Zoradiť a filtrovať.
Ak sú medzi tým nejaké prázdne bunky, Excel predpokladá, že všetky údaje boli zoradené a zastaví sa nad čiernou bunkou. Ak chcete zoradiť celý stĺpec, vyberte ľubovoľnú bunku v stĺpci a použite vyššie uvedenú možnosť.
Rovnaké možnosti sú dostupné aj pod nástrojom „Zoradiť a filtrovať“ v sekcii Úpravy na karte „Domov“.
Po kliknutí na tlačidlo Excel okamžite zoradí zoznam podľa abecedy.
Ako triediť a udržiavať riadky spolu v Exceli
Stĺpec môžete ľahko zoradiť, ak máte v pracovnom hárku iba jeden stĺpec, ale je to trochu komplikované, keď váš pracovný hárok obsahuje viacero stĺpcov vedľa stĺpca, ktorý chcete zoradiť. Niekedy tiež pracujeme na tabuľkách, kde musíme po zoradení stĺpca zachovať príslušné riadky nedotknuté.
Napríklad, ak máme tabuľku so zoznamom známok študentov. Keď zoradíme stĺpec mien študentov v abecednom poradí, chceme, aby sa podľa toho pohybovali aj známky zadané do riadkov vedľa nich.
V takýchto prípadoch môžete použiť tlačidlo „A-Z“ alebo „Z-A“ na zoradenie jedného zo stĺpcov v abecednom poradí a údaje sa automaticky usporiadajú do iných stĺpcov, pričom riadky budú pohromade. Pozrime sa, ako to funguje na inom príklade.
Najprv vyberte prvý stĺpec, podľa ktorého chcete zoradiť ďalšie stĺpce. Potom vyberte buď „A-Z“ alebo „Z-A“ na karte Údaje alebo na karte Domov, aby ste zoradili údaje do jedného stĺpca a Excel podľa toho automaticky preusporiada ostatné stĺpce.
Po kliknutí na tlačidlo triedenia sa zobrazí dialógové okno upozornenia na triedenie. Vyberte prepínač „Rozbaliť výber“ a kliknite na tlačidlo „Zoradiť“.
Riadky tak zostanú pohromade pri zoraďovaní stĺpcov bez toho, aby sa nezhodovali žiadne údaje (ako je uvedené nižšie).
Ak vyberiete možnosť „Pokračovať v aktuálnom výbere“, Excel zoradí iba vybratý stĺpec.
Ako triediť abecedne podľa viacerých stĺpcov
Ak chcete abecedne zoradiť množinu údajov podľa viac ako jedného stĺpca, môžete to urobiť pomocou funkcie Zoradiť v Exceli.
Napríklad chceme usporiadať nasledujúcu tabuľku abecedne najskôr podľa krajiny a potom podľa mena:
Najprv vyberte celú tabuľku, ktorú chcete zoradiť podľa abecedy, potom prejdite na kartu „Údaje“ a v skupine Zoradiť a filtrovať kliknite na „Zoradiť“.
Alebo kliknite na ikonu „Zoradiť a filtrovať“ v sekcii Úpravy na karte „Domov“ a vyberte „Vlastné triedenie“, aby ste získali prístup k rovnakej funkcii.
Zobrazí sa dialógové okno Zoradiť a zobrazí sa prvý stĺpec (parameter), v ktorom bude tabuľka zoradená. V rozbaľovacom poli „Zoradiť podľa“ vyberte primárny stĺpec, podľa ktorého chcete údaje zoradiť najprv v abecednom poradí, v našom prípade „Krajina“ a v rozbaľovacej ponuke „Poradie“ vyberte buď „A po Z“ alebo „Z po A“. .
Potom kliknite na tlačidlo „Pridať úroveň“ a pridajte druhú úroveň triedenia. Vyberte druhý stĺpec (v našom prípade Krstné meno), podľa ktorého chcete zoradiť údaje podľa abecedy v poli „Zoradiť podľa“ a vyberte buď „A po Z“ alebo „Z po A“. Ak má vaša tabuľka hlavičky navrchu, začiarknite políčko „Moje údaje majú hlavičky“, aby pri ich triedení bolo možné hlavičky preskočiť. V prípade potreby pridajte ďalšie úrovne triedenia a kliknite na tlačidlo „OK“.
Tabuľka je usporiadaná abecedne: najprv podľa krajiny a potom podľa mena, ako je uvedené nižšie.
Ako triediť riadky abecedne v Exceli
Niekedy možno budete chcieť v Exceli zoradiť riadky podľa abecedy namiesto stĺpcov. Môžete to urobiť pomocou funkcie triedenia programu Excel. Povedzme napríklad, že máte tabuľku, kde stĺpce B až T v prvom riadku obsahujú názvy miest, kde je počet obchodov dostupných pod rôznymi typmi maloobchodu, a stĺpec A obsahuje typ maloobchodu. Bunky sa používajú na sledovanie počtu obchodov v každom meste v rôznych kategóriách maloobchodníkov.
Najprv vyberte všetky riadky, ktoré chcete zoradiť podľa abecedy; Ak nechcete presunúť menovky riadkov, vynechajte ich z výberu. Teraz prejdite na kartu „Údaje“ a kliknite na „Zoradiť“ v skupine Zoradiť a filtrovať.
V dialógovom okne Zoradiť kliknite na tlačidlo „Možnosti“.
Zobrazí sa ďalšie malé dialógové okno, v ktorom máte možnosť vybrať „Zoradiť zľava doprava“ alebo „Zoradiť zhora nadol“. Ak chcete namiesto toho zoradiť abecedu podľa riadkov, vyberte možnosť „Zoradiť zľava doprava“ a kliknutím na tlačidlo „OK“ sa vráťte do dialógového okna Zoradiť.
Teraz v rozbaľovacom zozname „Zoradiť podľa“ vyberte riadok, ktorý chcete zoradiť podľa abecedy (v tomto príklade riadok 1). V druhom poli ponechajte hodnoty buniek v poli „Zoradiť podľa“ a v poli Poradie vyberte „A až Z“ (vzostupné poradie). Potom kliknutím na „OK“ zatvorte dialógové okno.
Teraz je prvý riadok v našej tabuľke zoradený v abecednom (vzostupnom) poradí a zvyšné riadky sú usporiadané podľa toho, ako je znázornené nižšie.
Ako abecedne zoradiť stĺpec pomocou filtra
Ďalším rýchlym spôsobom zoradenia podľa abecedy v Exceli je použitie funkcie Filter. Akonáhle použijete filtre na stĺpce, možnosti zoradenia pre všetky stĺpce sú dostupné jediným kliknutím myši.
Ak chcete do stĺpcov pridať filter, vyberte jeden alebo viac stĺpcov a kliknite na možnosť „Filter“ v skupine Zoradiť a filtrovať na karte „Údaje“. Ak chcete použiť filter na celú tabuľku, vyberte ľubovoľnú bunku v tabuľke a kliknite na možnosť „Filter“.
K možnosti Filter sa dostanete aj kliknutím na nástroj „Zoradiť a filtrovať“ v skupine Úpravy na karte „Domov“ a výberom položky „Filter“.
V hlavičkách každého stĺpca sa zobrazí malá šípka rozbaľovacej ponuky. Kliknite na šípku ľubovoľného stĺpca, ktorý chcete zoradiť podľa abecedy, a vyberte možnosť „Zoradiť od A po Z“ alebo „Zoradiť od A po A“.
Týmto usporiadate požadovaný stĺpec vo vami zvolenom abecednom poradí a ak vyberiete možnosť „Zoradiť od A po Z“, na tlačidle filtra sa zobrazí malá šípka nahor označujúca poradie zoradenia (vzostupne):
Pokročilé triedenie vo vlastnej objednávke
Zoradenie podľa abecedy nie je ideálne pre všetky abecedné údaje. Niekedy môžu byť údaje zoradené podľa abecedy, ale nie je to najefektívnejší spôsob, ako ich triediť.
Predstavme si situáciu, že môžete mať zoznam obsahujúci názvy mesiacov alebo dní v týždni, abecedné triedenie v takejto situácii nie je užitočné. Je zmysluplnejšie zoradiť tento zoznam chronologicky. Ak zoradíte od A po Z, zoradí mesiace podľa abecedy, ako prvý príde na rad apríl, potom august, február, jún atď. Ale toto nie je to, čo chcete. Našťastie je veľmi jednoduché chronologicky usporiadať v Exceli pomocou možnosti Rozšírené vlastné triedenie.
Začnite výberom tabuľky, ktorú chcete zoradiť. Potom vyberte „Vlastné zoradenie“ v časti „Zoradiť a filtrovať“ v sekcii Úpravy na karte Domov.
V dialógovom okne Zoradiť vyberte stĺpec, ktorý obsahuje mesiace v roku v sekcii stĺpcov, zoraďte ako zvyčajne podľa „Hodnoty“ a v časti Objednávka vyberte „Vlastný zoznam“.
V dialógovom okne Vlastné zoznamy si môžete vytvoriť vlastný zoznam. Existuje niekoľko predvolených vlastných zoznamov vrátane dní v týždňoch, skrátených mesiacov, mesiacov v roku atď. Vyberte si vhodnú možnosť triedenia podľa svojich potrieb (v našom prípade január, február, ..) a kliknite na „OK“. Potom znova kliknite na tlačidlo „OK“, aby ste zoznam zoradili chronologicky.
Všimnete si, že ste zoznam úspešne zoradili podľa mesačného poradia.
Ako triediť abecedne v Exceli pomocou vzorcov programu Excel
Ak ste fanúšikom vzorcov, môžete použiť vzorce programu Excel na zoradenie zoznamu podľa abecedy. Dva vzorce, ktoré možno použiť na zoraďovanie údajov podľa abecedy, sú COUNTIF a VLOOKUP.
Napríklad máme zoznam mien, ktoré chceme zoradiť podľa abecedy pomocou vzorca.
Aby sme to zoradili, pridáme do existujúcej tabuľky dočasný stĺpec s názvom „Poradie triedenia“.
V bunke (A2) vedľa údajov použite nasledujúci vzorec COUNTIF:
=COUNTIF($B$2:$B$20,"<="&B2) Vyššie uvedený vzorec porovnáva textovú hodnotu v bunke B2 so všetkými ostatnými textovými hodnotami (B3:B20) a vracia jej relatívne poradie. Napríklad v bunke B2 vráti 11, pretože existuje 11 textových hodnôt, ktoré sú nižšie alebo rovnaké ako text „Nancy“ v abecednom poradí. Výsledkom je poradie zoradenia mena zamestnanca v bunke B2.
Potom pomocou rukoväte výplne potiahnite tento vzorec, aby ste ho vyplnili v celom rozsahu. Takto získate poradie zoradenia každého z mien v zozname.
Teraz musíme usporiadať údaje zobrazené na obrázku vyššie na základe čísla poradia triedenia a na tento účel použijeme funkciu VLOOKUP.
Syntax:
=VLOOKUP(,A:B,2,0)„Číslo zoradenia“ tu predstavuje čísla vo vzostupnom poradí od 1 do 20. V zostupnom poradí by čísla mali byť od 20 do 1.
Tu máme stĺpec D pre zoradené názvy. Pre bunku D2 zadajte nasledujúci vzorec VLOOKUP:
=VLOOKUP(1;A:B;2;0)Podobne pre druhú a tretiu bunku musíte použiť vzorec ako:
=VLOOKUP(2;A:B;2;0) a =VLOOKUP(3;A:B;2;0) a tak ďalej…
Po zadaní tohto vzorca VLOOKUP do každej bunky vedľa údajov sa zoznam zoradí podľa abecedy.
Namiesto manuálneho zadávania vyššie uvedeného vzorca (1-20) do každej bunky môžete na uľahčenie práce použiť aj funkciu riadkov. Funkcia Row () vráti číslo riadka aktuálnej bunky. Takže pomocou funkcie riadku bude vzorec:
=VLOOKUP(ROW()-1,A:B,2,0)Tento vzorec vám poskytne rovnaký výsledok ako vyššie uvedený vzorec.
A potom pomocou rukoväte výplne potiahnite tento vzorec, aby ste ho vyplnili v celom rozsahu.
Ako abecedne usporiadať záznamy podľa priezviska
Príležitostne musíme často abecedne triediť údajové listy s priezviskami. V takýchto prípadoch, aj keď mená začínajú krstnými menami, musíte ich zoradiť podľa abecedy podľa priezviska. Môžete to urobiť pomocou vzorcov Excel Text.
Ak to chcete urobiť, musíte najprv extrahovať meno a priezvisko z celých mien do dvoch rôznych stĺpcov. Potom mená obráťte, zoraďte ich a potom ich otočte späť do pôvodnej podoby.
S úplným názvom v A2 zadajte nasledujúce vzorce do dvoch rôznych buniek (B2 a C2) a potom skopírujte vzorce nadol v stĺpcoch (pomocou úchytu na vyplnenie) až po poslednú bunku s údajmi:
Ak chcete extrahovať krstné meno, zadajte tento vzorec do bunky C2:
=LEFT(A2,HĽADAŤ(” “,A2)-1)
Ak chcete extrahovať priezvisko, zadajte tento vzorec do bunky D2:
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2;1))
A potom v bunke E2 zreťazili mená a priezviská v opačnom poradí oddelené čiarkou:
=D2&", "&C2
Oddelili sme mená a priezviská a prehodili sme ich. Teraz ich musíme zoradiť podľa abecedy a vrátiť ich späť. Ako môžete vidieť vyššie, keď extrahujeme názvy pomocou vzorca, stĺpce C, D a E v skutočnosti obsahujú vzorce, ale sú naformátované tak, aby vyzerali ako hodnoty. Musíme teda previesť vzorec na hodnoty.
Najprv vyberte všetky bunky vzorca (E1:E31) a stlačte Ctrl + C aby ste ich skopírovali. Kliknite pravým tlačidlom myši na vybrané bunky, kliknite na ikonu „Hodnoty“ v časti „Možnosti prilepenia“ a stlačte kláves „Enter“.
Potom vyberte ľubovoľnú bunku vo výslednom stĺpci a kliknite na možnosť „A až Z“ alebo „Z po A“ na karte „Údaje“ alebo „Domov“ podľa svojich potrieb.
Po kliknutí na tlačidlo sa zobrazí dialógové okno upozornenia na triedenie. Vyberte možnosť „Rozbaliť výber“ a kliknite na tlačidlo „Zoradiť“.
Ako vidíte, celý stĺpec je zoradený abecedne podľa priezviska.
Ak ho teraz chcete vrátiť späť na pôvodný „formát krstného mena a priezviska“, stačí zadať rovnaké vzorce, ale s rôznymi odkazmi na bunky.
Názov (E2) musíte znova rozdeliť na dve časti pomocou nižšie uvedených vzorcov:
V G2 extrahujte krstné meno:
=RIGHT(E2,LEN(E2)-SEARCH(" ",E2))
V G2 vytiahnite priezvisko:
=LEFT(E2,HĽADAŤ(" ",E2)-2)
A spojte dve časti dohromady, aby ste získali pôvodné celé meno:
=G2&" "&H2
Zakaždým, keď zadávate vzorec do prvej bunky, nezabudnite skopírovať vzorec nadol v stĺpci (pomocou rukoväte výplne) až po poslednú bunku s údajmi.
Teraz všetko, čo musíte urobiť, je previesť vzorce na prevod hodnôt ešte raz, ako sme to urobili vyššie, a máte hotovo. toto hotovo a.