Potřebujete v Excelu vytvořit ze seznamu hodnot jedinečný seznam? V novém Excelu na to máme funkci UNIQUE…ale co když ji nemáte? V tomhle videu si ukážeme 5 různých způsobů, jak to udělat – od starých, trochu bolestivých a manuálních metod…až po moderní řešení, které to zvládne na jedno kliknutí myši.
Excelový soubor ke stažení:
V příkladu máme seznam produktů a tržeb. Naším úkolem je dopočítat celkové tržby k produktům. Abychom to ale mohli udělat, tak musíme nejprve vytvořit jedinečný seznam hodnot.
Odstranit duplicity
Začneme nejjednodušší možností, jak vytvořit jedinečný seznam, která funguje ve všech verzích Excelů. Zkopírujeme seznam, kde máme všechny produkty a vložíme ho do sloupce, kde chceme mít jedinečný seznam hodnot. Ještě když je seznam označený, tak na kartě Data vybereme Odebrat duplicity. To odebere duplicity ze seznamu a zanechá pouze seznam jedinečných hodnot.
K tomuto seznamu musíme dopočítat tržby. To uděláme pomocí funkce SUMIFS. V této funkci se nejprve označují hodnoty pro součet, což je sloupec s tržbami. Následuje sloupec s kritérii, což je v tomto případě sloupec s produkty a jako kritérium první produkt z našeho seznamu jedinečných hodnot.
Tento způsob tvorby jedinečného seznamu je jednoduchý a funguje ve všech verzích Excelů. Nevýhodou je, že seznam vznikl kopírováním, takže nebude reagovat na změny ve zdrojové tabulce.
INDEX & POZVYHLEDAT
Další možností, jak vytvořit ve všech verzích Excelů jedinečný seznam, je kombinací několika funkcí. Začneme s funkcí INDEX, ve které se označuje pole s výsledkem, tedy pole, které chceme vrátit, což je v tomto případě sloupec s produkty. V parametru řádky bude funkce POZVYHLEDAT neboli funkce MATCH. V této funkci hledáme nulu a v parametru prohledat bude funkce COUNTIF, ve které vytvoříme dynamické rozpětí. A nesmíme zapomenout na to, že hledáme přesnou shodu.
Tato funkce vrátí jedinečný seznam hodnot. Pokud chceme zajistit, aby seznam reagoval i na nové položky v seznamu, tak funkci stáhneme pro více řádků dolů. Abychom se zbavili chybových hlášek, tak funkci zabalíme do funkce IFERROR. Následně opět pomocí funkce SUMIFS dopočítáme celkové tržby.
KONTINGENČNÍ TABULKA
Jedinečný seznam a celkové tržby dopočítáme i pomocí kontingenční tabulky. Nejprve kontingenční tabulku vytvoříme, a to tak, že klikneme do tabulky a na kartě Vložení vybereme Kontingenční tabulka a Z tabulky nebo oblasti.
Ve druhém kroku vytvoříme kontingenční tabulku. Na řádky vložíme produkty a do pole hodnot vložíme tržby. A jedinečný seznam s celkovými tržbami je vytvořený.
UNIQUE
V novějších verzích Excelů je funkce UNIQUE, která umí vrátit jedinečný seznam hodnot. Její použití je jednoduché. Ve funkci se pouze označí sloupec hodnot a po potvrzení funkce vrátí jedinečný seznam.
Následně pomocí funkce SUMIFS dopočítáme celkové tržby. Jelikož ale tentokrát pracujeme s dynamickým polem ve funkci UNIQUE, tak můžeme za kritérium napsat křížek a tím se odkážeme na celé dynamické pole a funkce SUMIFS tím z jedné buňky vrátí všechny hodnoty. Výhodou těcht ofunkcí je to, že dynamicky reagují na jekékoliv změny ve zdorjové tabulce.
SESKUPITPODLE / GROUPBY
Předplatitelé Microsoft 365 mohou použít funkci SESKUPITPODLE neboli funkci GROUPBY. Tato funkce vrátí jedinečný seznam se součtem tržeb. Do parametru řádky nejprve označíme sloupec, jehož hodnoty chceme mít na řádcích, což je sloupec s produkty. Následuje pole hodnoty, což jsou tržby a jako výpočet chceme SUMA. A pokud chceme, aby se tabulka vrátila i se záhlavím, tak v prvním nepovinném parametru vybereme 3. Výhodou této funkce je, že je plně dynamická a reaguje okamžitě na jakékoli změny ve zdorjové tabulce.
POWER QUERY
Poslední možností, jak velmi lehce vytvořit seznam jedinečných hodnot a celkových tržeb, je pomocí Power Query. Klikneme do zdrojové tabulky a na kartě Data vybereme Z tabulky nebo oblasti.
Potom, co se tabulka nahraje do editoru Power Query označíme sloupec s produkty a na kartě Transformace vybereme Seskupit podle. Tabulku chceme seskupit podle sloupce s produkty a nový sloupec nazveme jako Tržba celkem. V tomto novém sloupci potřebujeme sečíst sloupec s Tržbami.
Seskupenou tabulku nahrajeme zpátky do Excelu pomocí karty Zavřít a načíst.
