Řazení dat je nedílnou součástí analýzy dat. V aplikaci Excel můžete uspořádat seznam jmen podle abecedy, sestavit seznam úrovní skladových zásob produktů od nejvyšší po nejnižší nebo seřadit řádky podle barev nebo ikon. Tento článek se zabývá možnostmi, které Excel nabízí při řazení dat (hodnot, ikon), ať už v řádcích nebo sloupcích. Možná si pokládáte otázku, proč řadit data? Řazení je abecední (u textu) buď od A do Z, nebo zpětně od Z do A. U čísel se řadí od nejmenšího nebo od největšího. Pokud je řazení textu čísel znaků (A - Z), nejprve se seřadí čísla, poté znaky a nakonec písmena, kdy velké písmeno je výše než malé písmeno.
Základy řazení a filtrování dat
Data je možné řadit podle textu (A až Z nebo Z až A), čísel (nejmenší až největší) nebo podle data a času (nejstarší až nejnovější nebo nejnovější až nejstarší) v jednom nebo několika sloupcích. Řadit je možné také podle vlastního vámi vytvořeného seznamu (například Velký, Střední a Malý) nebo podle formátu včetně barvy buněk, barvy písma nebo nastavené ikony.
Použití automatického filtru a podmíněného formátování
Pokud chcete najít nejvyšší nebo nejnižší hodnoty v oblasti buněk nebo v tabulce, například 10 nejvyšších hodnocení nebo 5 nejnižších prodejních částek, použijte funkci Automatický filtr nebo podmíněné formátování. Tabulku označte a na kartě Data vyberte příkaz Filtr (tabulka musí být označena celá). Následně můžeme filtrovat skupinu lidí podle věku, výšky nebo pohlaví. Pokud např. vyberu "muž", zobrazí se v tabulce jen řádky, které mají ve sloupci Pohlaví uvedeno muž. Následně můžete provádět řazení podle jména, věku.
Příprava dat pro řazení
- Zkontrolujte, jestli jsou všechna data uložená jako text. Jestliže sloupec, který chcete seřadit, obsahuje čísla uložená jako čísla a čísla uložená jako text, musíte je všechna naformátovat jako čísla nebo text. Jestliže tento formát nepoužijete, seřadí se čísla uložená jako čísla před čísly uloženými jako text.
- Odeberte případné úvodní mezery. Před daty importovanými z jiných aplikací mohou být někdy úvodní mezery. Před řazením dat úvodní mezery odeberte.
- Zkontrolujte, jestli jsou všechna čísla uložená jako čísla. Jestliže výsledek není takový, jaký jste očekávali, obsahuje sloupec pravděpodobně čísla uložená jako text, ne jako čísla. Například záporná čísla naimportovaná z některých účetních systémů nebo čísla zadaná na začátku se znakem apostrof (') se uloží jako text.
- Zkontrolujte, jestli jsou kalendářní data a časy uložené jako kalendářní data nebo časy. Pro správné seřazení kalendářních dat a časů v aplikaci Excel je nutné, aby všechna kalendářní data a časy byly uložené jako pořadové číslo kalendářního data nebo času. Pokud nebude hodnota rozpoznána jako datum nebo čas, uloží se jako text.
- Pokud chcete řadit podle dnů v týdnu, naformátujte buňky tak, aby zobrazovaly den v týdnu. Jestliže chcete řadit podle dnů v týdnu bez ohledu na datum, převeďte je na text pomocí funkce TEXT. Funkce TEXT ale vrací textovou hodnotu, proto bude operace řazení vycházet z alfanumerických dat.
Řazení podle více sloupců nebo řádků
Pokud máte data, která chcete seskupit podle stejné hodnoty v jednom sloupci nebo řádku, můžete chtít seřadit podle více než jednoho sloupce nebo řádku a potom seřadit další sloupec nebo řádek v rámci této skupiny stejných hodnot. Pokud máte například sloupec Oddělení a sloupec Zaměstnanec, můžete nejprve řadit podle oddělení (seskupit všechny zaměstnance ve stejném oddělení) a pak je seřadit podle názvu (aby se jména v jednotlivých odděleních dala v abecedním pořadí).
V seznamu Řazení vyberte typ řazení. V rozevíracím seznamu v části Pořadí vyberte, jak chcete řadit. Pokud jste při ručním nebo podmíněném formátování oblasti buněk nebo sloupce tabulky použili barvu buňky nebo barvu písma, můžete řadit také podle těchto barev. Dále zvolte požadovaný způsob řazení. Poznámka: Neexistuje výchozí řazení podle barev buněk, barev písma nebo ikon.
Čtěte také: Jak namontovat ukončovací lišty
Vlastní seznamy pro řazení
Pokud chcete použít řazení definované uživatelem, můžete použít vlastní seznam. Sloupec může například obsahovat hodnoty, podle kterých chcete řadit - třeba Hodně, Středně a Málo. Jak dosáhnete toho, aby řádky obsahující položku Hodně byly první, za nimi byly řádky obsahující položku Středně a nakonec byly řádky s položkou Málo? Bez ohledu na pořadí budete vždycky chtít střední uprostřed. Vyberte oblast hodnot, které jste zadali. Vlastní seznam můžete vytvořit pouze podle hodnoty (textu, čísla, kalendářního data a času). V dialogovém okně Vlastní seznamy vyberte požadovaný seznam.
Řazení podle částečné hodnoty
Chcete-li provést řazení podle částečné hodnoty ve sloupci, jako například podle kódu v čísle náhradního dílu (789-WDG-34), příjmení (Věra Kopecká) nebo křestního jména (Kopecká, Věra), je nejdříve nutné sloupec rozdělit na dva nebo více sloupců, aby se hodnota, podle které chcete provádět řazení, nacházela ve vlastním sloupci. K tomu můžete použít textové funkce k oddělení částí buněk nebo Průvodce převodem textu na sloupce.
Důležité poznámky před řazením
- Zkontrolujte, jestli se nezměnily hodnoty vrácené vzorci. Pokud data, která jste seřadili, obsahují nějaké vzorce, můžou se hodnoty těchto vzorců při přepočítání listu změnit.
- Před řazením zobrazte skryté řádky a sloupce. Skryté sloupce se při řazení sloupců nepřesouvají a skryté řádky se nepřesouvají při řazení řádků.
- Zkontrolujte nastavení národního prostředí. Pořadí řazení se liší podle nastavení národního prostředí. Ujistěte se, že máte na počítači v Ovládacích panelech v Místním nastavení nebo v Místním a jazykovém nastavení nastavené správné národní prostředí.
- Zapněte nebo vypněte řádek záhlaví. Při řazení sloupců je obvykle lepší mít řádek záhlaví. Význam dat je pak jasnější. Hodnota v záhlaví není ve výchozím nastavení zahrnutá do operace řazení. Někdy může být potřeba záhlaví zapnout nebo vypnout, aby záhlaví bylo nebo nebylo zahrnuté do operace řazení.
Řazení dat v Excelu pomocí funkcí
Jedním z největších problémů v Excelu vždy bylo, jak seřadit hodnoty v tabulce pomocí excelových funkcí. Můžeme do tabulky pomocí CTRL+SHIFT+L vložit automatický filtr, který vloží filtr do záhlaví, pomocí kterého můžeme mimo jiné řadit hodnoty v tabulce. Co když ale potřebujeme hodnoty seřadit dynamicky a z nějakého důvodu třeba nemůžeme změnit pořadí hodnot ve zdrojové tabulce?
Řazení ve starších verzích Excelu (bez dynamických polí)
Asi nejjednodušším způsobem, jak hodnoty seřadit, je použít pomocné sloupce. V tabulce máme hodnoty, které potřebujeme seřadit od nejvyšší hodnoty po nejnižší. Nejprve musíme určit pořadí hodnot v seznamu, následně musíme pomocí vyhledávacích funkcí přiřadit pozici první, druhé, třetí hodnotě v seznamu a následně k tomu přiřadit správnou hodnotu.
- Určení pořadí hodnot: Prvním pomocným sloupcem je určení pořadí hodnot. Pořadí hodnot v rámci seznamu určíme pomocí funkce RANK.EQ. Ve funkci RANK.EQ nejprve označíme první hodnotu, u které chceme určit pořadí, tedy první hodnotu v seznamu, a v parametru odkaz označíme celý sloupec hodnot. Jelikož chceme funkci stahovat dolů, tak musíme buňky správně zafixovat klávesou F4. První buňku fixovat nemusíme, ale musíme zafixovat rozpětí buněk, a to plně. Teď můžeme funkci stáhnout dolů.
- Ruční doplnění pořadí: Ve druhém pomocném sloupci musíme ručně doplnit pořadí hodnot od jedničky do koncového čísla, například od jedné do osmičky. Na prvním místě chceme mít nejvyšší hodnotu, tedy pozici jedna podle funkce RANK.EQ, na druhém místě v tabulce chceme mít druhou nejvyšší hodnotu podle pozice RANK.EQ a tak dále.
- Přiřazení pořadových čísel řádků: Ve třetím pomocném sloupci musíme teď k jednotlivým pořadovým číslům přiřadit pořadová čísla řádků. K tomu využijeme vyhledávací funkci POZVYHLEDAT (anglicky funkce MATCH), která najde hledanou hodnotu a vrátí pořadové číslo řádku, na kterém se hodnot nachází. Ve funkci POZVYHLEDAT nejprve označíme, co hledáme (první pozici ve druhém pomocném sloupci) a kde tuto hodnotu hledáme (ve sloupci pořadí, které nám určila funkce RANK.EQ). Hledáme přesnou shodu, takže nulu. Opět musíme plně zafixovat rozpětí buněk ve sloupci hodnot. Funkci ukončíme a potvrdíme a pošleme ji dolů.
- Přiřazení hodnot k řádkům: V posledním sloupci přiřadíme k řádkům hodnoty. K tomu poslouží funkce INDEX. Napíšeme funkci INDEX, kde nejprve označujeme hodnoty, které má funkce INDEX vrátit, což jsou čísla ze zdrojové tabulky. Rovnou je plně zafixujeme klávesou F4. V parametru řádek označíme pořadová čísla řádků z třetího pomocného sloupce. Funkci ukončíme, potvrdíme a pošleme ji dolů.
Řazení v nových verzích Excelu (s dynamickými poli - Microsoft 365)
Pro seřazení hodnot se použije funkce SORT. Ve funkci SORT se označuje pole hodnot, které chceme seřadit, tedy zdrojový seznam. Pokud máme pouze jeden sloupec hodnot, druhý parametr funkce nepotřebujeme a ve třetím parametru si vybereme, zda chceme hodnoty seřadit sestupně nebo vzestupně. Například pro sestupné řazení vybereme -1. Funkci potvrdíme.
Čtěte také: Nezbytný nástroj pro beton
Příklad: Dynamické řazení hodnot
V následující tabulce ukážeme, jak se liší řešení řazení hodnot v tabulce mezi staršími a novějšími verzemi Excelu.
| Popis kroku | Starší Excel (bez dynamických polí) | Novější Excel (s dynamickými poli - Microsoft 365) |
|---|---|---|
| Určení pořadí | Funkce RANK.EQ s pomocnými sloupci | Není potřeba |
| Vyhledání pozice | Funkce POZVYHLEDAT s pomocnými sloupci | Není potřeba |
| Přiřazení hodnot | Funkce INDEX s pomocnými sloupci | Není potřeba |
| Konečné řazení | Kombinace několika funkcí a pomocných sloupců | Jediná funkce SORT |
Pokročilé scénáře řazení a sběru dat
Sběr a řazení dat z více listů
Pokud potřebujete shromáždit data ze tří listů a ve čtvrtém (summary) listu je zobrazit ve stejném formátu, ale seřazené buď podle data, nebo podle pořadového čísla, je to možné. Cílem je, aby se v tabulce summary zobrazily jen nejmladší data, resp. data s nejvyšším pořadovým číslem, a jen ty, které se vejdou do zvýrazněné tabulky. Zároveň je potřeba, aby se s daty přenášely i další údaje v řádku a aby se list summary aktualizoval, když se na zdrojovém listu doplní nový údaj. Nejstarší údaj vypadne, nejmladší se zobrazí nahoře a zbytek se posune dolů.
Pro řešení v Power Query je potřeba znát "přesně" odkud má načítat data. Jestli to bude více souborů a z nich jeden nebo více listů listů, jestli budou ve stejné složce, jestli mají "naprosto stejnou" strukturu, jestli jsou názvy sloupců stejné atd. Petr-M: Do souboru jsem vytvořil ještě jeden list, PREP LIST, kde mám natvrdo sesbírané data, resp statusy. Sloupec status bude mít vždy různé číslo a bude vzrůstající. V sešitě summary jsem schopný je vyfiltrovat a setřídit. Teď už k nim jen dosadit přes VVYHLEDAT jednotlivé popisky. List "Cesta" tam si zadejte cestu, kam si uložíte !! tento !! List "SumData": do něj se načítají posbíraná a seřazená data aktuálně ze třech listů. Na vašem původním listu "summary" si už můžete udělat odkazy na data z listu SumData.
Přenos a řazení dat v rámci jednoho listu
V případě, že se jedná jen o jeden list v Excelu, kde se ve spodní tabulce doplňují údaje ručně (na poslední volný řádek dole), a tyto data potřebujete přenést, zkopírovat nahoru do horní tabulky a seřadit, je potřeba zohlednit několik specifik. Rozlišovacím prvkem je údaj ve sloupci "Kod", který označuje daný řádek a bude vždy jiný. A podle toho, co je v daném řádku, potom potřebujete doplnit do řádku k danému kódu. Stává se, že nejmladší datum nemá největší hodnotu kódu. A aby to bylo ještě drobet složitější, přenáší se to do sloučených buněk.
Zjednodušeně, nejvyšší číslo kódu spolu s daty na daném řádku z tabulky dole převézt na první řádek do tabulky nahoře. Pokud se vypíše v tabulce dole nový řádek (excel řádek 33) a bude mít nejvyšší číslo, tak se zapíše do horní tabulky nahoru (řádek 2, sloučené buňky). Kód bude vždycky jiný. Nemusejí být v té spodní tabulce seřazeny podle hodnoty, ale bude vždy jiný. A je to normální číslo.
Čtěte také: Dilatační spáry v betonu
tags: #lista #razeni #excel #navod