V dnešním videu se podíváme na jedinečné a unikátní hodnoty v Excelu. Myslíte si, že tyto dva pojmy znamenají totéž? V češtině možná ano, ale v Excelu nikoliv. Unikátní hodnoty, v angličtině unique values, jsou ty, které se v seznamu hodnot vyskytují pouze jednou. To znamená, že v seznamu nemají duplicitu. Naproti tomu, kdybychom ze seznamu chtěli dostat jedinečné hodnoty, anglicky distinct values, někdy nazývané jako odlišné hodnoty, tak bychom ze seznamu museli dostat všechny hodnoty, které se v seznamu vyskytují, bez duplicitních hodnot. Pořád vám to přijde stejné? Tak se podívejte na toto video, a já vám ukáži, v čem je rozdíl.
Excelový soubor ke stažení
Jedinečné a unikátní hodnoty v Excelu
Pro dnešní ukázku máme následující krátký list hodnot, na kterém si rozdíl v jedinečných a unikátních hodnotách ukážeme. Od pohledu vidíme, že máme v seznamu duplicitní záznamy, tedy, že se některá čísla v listu hodnot vyskytují vícekrát. Nejprve si ukážeme rozdíl mezi jedinečnými a unikátními hodnotami.
Jedinečné neboli odlišné hodnoty jsou všechny hodnoty, které se v seznamu vyskytují bez duplicitních záznamů. V tomto případě by se jednalo o hodnoty 10, 15, 14, 18, 20 a 21. Pokud bychom ale chtěli vrátit list unikátních hodnot, tak by správná odpověď byla pouze čísla 15, 14, 18 a 21. Proč? Protože unikátní hodnoty jsou ty, které se v listu hodnot vyskytují pouze jednou a nemají duplicitu. Čísla, která se v listu vyskytují vícekrát nebudou započítána, tedy čísla 10 a 20 z listu unikátních hodnot vypadnou.
Teď když víme rozdíl mezi těmito pojmy, tak si pojďme ukázat, jak nejprve jedinečné a unikátní hodnoty najít. Rozdíl si názorně ukážeme ve dvou sloupcích vedle sebe.
Jak najít unikátní hodnoty
Nejprve se budeme v seznamu hodnot snažit určit unikátní hodnoty. Tedy ty, které se v seznamu hodnot nevyskytují vícekrát. V seznamu tedy musíme označit hodnoty 15, 14, 18 a 21. K najití unikátních hodnot nám pomůže funkce COUNTIF. Potřebujeme totiž spočítat počet výskytů hodnot v seznamu. Začneme s funkcí COUNTIF, kde nejprve jako oblast označíme celý list hodnot, buňky zafixujeme klávesou F4, jak pro řádky, tak sloupce. Jako kritérium označíme první buňku, kterou fixovat nemusíme. Funkci potvrdíme a stáhneme ji dolů a podíváme se, co nám funkce vrací.
Funkce COUNTIF vrací počet výskytů daného čísla v seznamu hodnot. Hodnotu 10 máme v seznamu dvakrát a proto se u obou desítek vrací číslo 2. Hodnotu 15 máme v seznamu jen jednou a proto funkce COUNTIF vrací jedničku. Stejné je to u čísel 14 a 18. Hodnota 20 se opět v seznamu vyskytuje dvakrát, takže u obou dvacítek máme dvojku. Pokud bychom poslední hodnotu 21 změnili na 20. Tak by funkce COUNTIF u čísla 20 vrátila trojky. Hledáme unikátní hodnoty, tedy ty, které se v seznamu nevyskytují vícekrát. Klíčové jsou tedy řádky, na kterých funkce COUNTIF vrací jako výsledek číslo 1. Funkci COUNTIF tedy zabalíme do podmínkové funkce KDYŽ, kde základem podmínky je funkce COUNTIF, která se rovná 1. A pokud je tato podmínka splněná, tak se jedná o unikátní hodnotu a chceme vrátit číslo 1, v opačném případě se jedná o duplicitní položku a chceme vrátit nulu. Funkci ukončíme, potvrdíme a stáhneme ji dolů. Teď máme v sloupci označené unikátní hodnoty a víme, že v listu hodnot máme pouze 4 unikátní hodnoty a máme je označené jedničkou.
Jak najít jedinečné hodnoty
Ve druhém sloupci budeme chtít určit jedinečné neboli odlišné hodnoty. Tedy označit ve sloupci hodnoty, které jsou bez duplicit. K určení jedinečných hodnot použijeme rovněž funkci COUNTIF, jen ji trochu upravíme. Napíšeme funkci COUNTIF, kde tentokrát v oblasti využijeme dynamického rozpětí a označíme první buňku seznamu, napíšeme dvojtečku a opět označíme první buňku. Je zde tedy rozdíl od příkladu na unikátní hodnoty, kde jsme v oblasti nejprve označili celý seznam hodnot. První buňku ještě plně zafixujeme klávesou F4, jak pro řádky, tak sloupce. Jako kritérium označíme opět první buňku. Funkci potvrdíme a pošleme ji dolů.
V tomto případě vrací funkce COUNTIF něco trochu jiného. V případě unikátních hodnot nám u duplicitních položek vrátila počet výskytů hodnot a u unikátních položek vrátila jedničku. Teď, jelikož jsme ve funkci COUNTIF použili dynamické rozpětí, tak funkce COUNTIF u prvního výskytu hodnoty vrátí jedničku a u druhého výskytu vrátí dvojku. Kdybychom opět poslední hodnotu 21 změnili na 20, tak se u první dvacítky vrátí jednička, u druhé dvacítky vrátí dvojka a u třetí se vrátí trojka. V tomto případě, jelikož nás zajímá jedinečný seznam hodnot, tak nás zajímají řádky, na kterých je funkce COUNTIF opět rovna jedné. Funkci COUNTIF opět zabalíme do funkce KDYŽ, kde funkce COUNTIF bude základem podmínky, pokud se rovná jedné, a pokud je podmínka splněná, chceme vrátit opět jedničku a pokud ne, tak chceme vrátit nulu. Potvrdíme a pošleme funkci dolů. Teď máme označené řádky, na kterých máme jedinečné hodnoty, kterých máme v seznamu hodnot přesně šest.
Jak spočítat unikátní a jedinečné hodnoty
Pro spočítání počtu unikátních a jedinečných hodnot můžete buď využít funkci SUMA nebo SUBTOTAL nad pomocnými sloupci, záleží, zda budete chtít sloupce filtrovat. Nad pomocné sloupce napíšeme například funkci SUBTOTAL, kde bychom jako funkci vybrali SUMA, takže číslo 9 a jako oblast bychom označili celý pomocný sloupec. To samé bychom udělali i pro sloupec s jedinečnými hodnotami, takže bychom výpočet pouze protáhli nad druhý sloupec.
Můžeme rovněž hodnoty spočítat bez pomocných sloupců v jedné buňce. Začneme opět unikátními hodnotami. Do buňky napíšeme funkci COUNTIF, kde nejprve označíme celý sloupec hodnot. Tentokrát buňky nemusíme fixovat, jelikož vzorec nepánujeme nikam stahovat. Teď ale jelikož chceme celý výpočet provést v jedné buňce, tak i ve druhé části funkce COUNTIF označíme celý sloupec hodnot. Tím jsme ze vzorce udělali maticový vzorec. Víme, že unikátní hodnoty jsou ty, kde se funkce COUNTIF rovná jedné, takže funkci rovnou zabalíme do funkce KDYŽ, kde pokud bude podmínka splněná, tak chceme vrátit jedničku a pokud nebude splněná, tak chceme vrátit nulu. Abychom hodnoty sečetli, tak ještě na závěr musíme funkci zabalit do funkce SUMA. Toto je celý vzorec na výpočet počtu výskytu unikátních hodnot v seznamu. Ti z vás, kteří používají starší verze Excelu než je Office 2020, tak budou muset pro potvrzení tohoto vzorce zmáčknout kombinaci kláves CTRL+SHIFT a ENTER, jelikož se jedná o maticový vzorec. Uživatelům novějších Excelů stačí pro potvrzení pouze klávesa ENTER. Funkci potvrdíme a vrátilo se číslo 4, což je správně. V seznamu máme 4 unikátní hodnoty.
Pro výpočet počtu jedinečných hodnot použijeme opět funkci COUNTIF. Ve funkci COUNTIF nejprve označíme celý sloupec hodnot, napíšeme středník a znovu označíme celý sloupec hodnot. Ukončíme závorku u funkce COUNTIF a před vzorec COUNTIF napíšeme jedničku a znaménko děleno.
Vzorec tedy vypadá jako jedna děleno funkce COUNTIF. Označíme celý vzorec a zmáčkneme klávesu F9 abychom se podívali, co nám tento vzorec vrací.
U prvního čísla deset vrací tento vzorec 0,5, abychom pochopil proč, tak si musíme vzpomenout, co nám funkce COUNTIF u jedinečných hodnot vracela. Funkce u každého čísla vrátila počet výskytů čísla. Číslo deset je v seznamu dvakrát, takže dělíme ½, což je 0,5. U druhé desítky máme to samé. U čísla 15 máme jedničku, jelikož se číslo 15 vyskytuje v seznamu pouze jednou a proto se dělí jedna děleno jedna. Teď stačí tyto hodnoty opět sečíst. Překlikneme se do vzorce použitím kláves CTRL+Z a celý vzorec zabalíme do funkce SUMA. Funkci potvrdíme. U starších Excelů budete muset opět funkci potvrdit stisknutím kláves CTRL+SHIFT a ENTER. Funkce vrátila číslo 6, což je počet jedinečných hodnot v seznamu.
Obarvení unikátních hodnot
Řekněme, že bychom teď chtěli barevně označit nejprve jedinečné a následně unikátní hodnoty. Na tomto místě je třeba říct, že v Excelu je občas problém s překladem. Řekněme, že nejprve chceme obarvit unikátní hodnoty v Excelu. K obarvení řádků použijeme podmíněné formátování. Označíme celý sloupec hodnot a na kartě Domů najdeme Podmíněné formátování a zde vybereme Pravidla zvýraznění buněk a duplicitní hodnoty. Nechceme ovšem formátovat duplicitní hodnoty, ale unikátní hodnoty. A zde máme právě nesoulad s překladem, v anglické verzi Excelu by zde bylo napsáno Unique values, v české verzi Excelu je zde Jedinečné hodnoty. Vybereme tedy jedinečné a ještě vybereme formát, vybereme třeba modrou výplň. Potvrdíme a jak vidíte, tak se v seznamu obarvily unikátní hodnoty na modro, i přesto, že výběr říkal jedinečné hodnoty.
Obarvení jedinečných hodnot
Pro obarvení jedinečných hodnot v seznamu není v podmíněném formátování žádná předvolená možnost. Musíme nejprve označit celý sloupec hodnot, a na kartě Domů vybrat Podmíněné formátování a zde vybrat Nové pravidlo. V seznamu vybereme Určit buňky k formátování pomocí vzorce. Do příkazového pole podmíněného formátování napíšeme stejný vzorec COUNTIF, který jsme použili pro určení unikátních hodnot. Napíšeme rovná se, funkce COUNTIF, kde jako oblast nejprve označíme první buňku, která bude plně zafixovaná, dvojtečku a opět první buňku. Tato druhá buňka ale zafixována být nesmí, takže ji pomocí klávesy F4 odfixujeme. Následuje středník a opět jako kritérium nezafixovaná první buňka. Ukončíme závorku a jelikož podmíněné formátování pracuje na bázi pravd a nepravd, tak se celá tato funkce COUNTIF musí rovnat jedné. Pokud bude tato podmínka splněná, tak se jedná o unikátní hodnotu a chceme buňku obarvit třeba na žluto. Potvrdíme a máme jedinečné hodnoty obarvené na žluto.
Vyselektování jedinečných hodnot
Uživatelé Office 2021 a 365 mají velmi jednoduchou možnost jedinečné a unikátní hodnoty rovněž vyselektovat ze seznamu. Stačí jim k tomu použít funkce UNIQUE. Což je opět trochu nešťastný překlad do češtiny. Funkce UNIQUE umí totiž vrátit jak unikátní, tak jedinečné hodnoty. Nejprve chceme vyselektovat jedinečné hodnoty. Napíšeme funkci UNIQUE, kde označíme sloupec hodnot a potvrdíme. Funkce UNIQUE vrátí seznam jedinečných hodnot ze seznamu.
Ti z vás, kteří nemají přístup k této funkci by museli seznam hodnot zkopírovat, vložit ho někam vedle, seznam označit a na kartě Data vybrat Odstranit duplicity. Potvrdíme odstranění duplicit a Excel vrátí seznam jedinečných hodnot. Na rozdíl od funkce UNIQUE ovšem tato metoda není dynamická a nereaguje na změny v seznamu. Pokud v seznamu hodnot něco změníme, funkce UNIQUE se přepočítá, kdežto u odstranění duplicit bychom celý proces museli opakovat.
Pro vyfiltrování unikátních hodnot můžete rovněž použít funkci UNIQUE. V takovém případě bychom napsali funkci UNIQUE, opět bychom označili celý sloupec hodnot a tentokrát bychom ještě využili třetí nepovinný parametr funkce. Druhý parametr nepotřebujeme, takže ho přeskočíme a ve třetím parametru vybereme PRAVDU, tedy položky, které se v seznamu vyskytují právě jednou. Funkci potvrdíme a máme unikátní seznam hodnot.
2 komentáře
Možná špatná otázka, ale v jakých praktických případech bych mohl využít?
Dobrý den, já to využívám u porovnávání seznamů. Kolik mám jedinečných lidí v seznamu, když hledám zda mám na seznamu někoho dvakrát a koho, atd. 🙂