V dnešním videu se podíváme na to, jak v Excelu pracovat s duplicitami. A uděláme si rovnou takový průlet práce s duplicitami z několika různých pohledů. Podíváme se na to, jak odstranit duplicity ze seznamu a vyselektovat jedinečný seznam několika různými způsoby, ukážeme si, jak obarvit duplicity, jak je označit ikonami nebo jak spočítat počet duplicit a jedinečných hodnot v seznamu. V předchozích videích jsme se již věnovali tomu jak pracovat s excelovými seznamy, jak vyhledávat s duplicitami, jak vytvořit jedinečný seznam hodnot, jak vytvořit jedinečný seznam hodnot, nebo jak vyhledávat s duplicitami pomocí funkce SVYHLEDAT.
Excelový soubor ke stažení
Odstranění duplicit
V prvním příkladu máme ve sloupci seznam produktů, ve kterém se nám na první pohled opakují některé produkty. Naším úkolem je do vedlejšího sloupce vyselektovat jedinečný seznam hodnot, tedy jinými slovy odstranit duplicity ze seznamu. Máme několik způsobů, jak to udělat.

Odebrat duplicity
Pro odstranění duplicit můžeme použít nástroj Odstranit duplicity. Zkopírujeme hodnoty včetně duplicit a vložíme je do sloupce, kde chceme mít seznam bez duplicit. Tento zkopírovaný seznam necháme označený a na kartě Data vybereme Odebrat duplicity. Potvrdíme, že chceme odebrat duplicity z vybraného sloupce a po potvrzení za nás Excel duplicitní hodnoty odstraní a nechá pouze seznam jedinečných hodnot. Nevýhodou tohoto postupu je, že nový seznam není nijak propojený s původním seznamem, takže se změny ve zdrojovém seznamu neprojeví v seznamu novém.

Rozšířený filtr
Druhou možností, jak odstranit duplicity, je nástroj Rozšířený filtr. V takovém případě klikneme do sloupce, ze kterého chceme odstranit duplicity. Na kartě Data vybereme Upřesnit, což otevře okno rozšířeného filtru. Nový seznam s jedinečnými hodnotami chceme vykopírovat z původního seznamu, takže vybereme možnost Kopírovat jinam. Oblast seznamu je zdrojový seznam hodnot, včetně záhlaví. Kritérium v tomto případě nemáme, takže pole necháme prázdné. Ale vyplníme pole Kopírovat do, kde si vybereme první buňku, kde chceme aby začínalo záhlaví nového seznamu. A nezapomeneme dole zaškrtnout Bez duplicitních záznamů.

Funkce UNIQUE
Ti z vás, co mají přístup k dynamické funkci UNIQUE, mohou použít pro odstranění duplicit funkci UNIQUE. Ve funkci UNIQUE stačí označit pouze pole, což je seznam s duplicitami a funkci potvrdit.

Kombinace funkcí pro jedinečný seznam
Ti z vás, co nemají přístup k dynamické funkci UNIQUE mohou stále vytvořit seznam jedinečných hodnot pomocí excelových funkcí, jen musí použít kombinaci několika funkcí. Základem řešení je funkce COUNTIF. Ve funkci COUNTIF vytvoříme dynamické rozpětí z buňky záhlaví nového seznamu. A v parametru kritérium označíme celý seznam s duplicitami. Jelikož jsme v parametru kritérium označili více než jednu buňku, tak se ze vzorce stal maticový zápis.
= COUNTIF($I$3:I3;$A$4:$A$20)
Tuto funkci COUNTIF zabalíme do funkce POZVYHLEDAT neboli funkce MATCH. Ve funkci POZVYHLEDAT hledáme nulu, jako parametr prozkoumat poslouží celá funkce COUNTIF a hledáme přesnou shodu.
= POZVYHLEDAT(0;COUNTIF($I$3:I3;$A$4:$A$20);0)
Funkci POZVYHLEDAT zabalíme do funkce INDEX, jelikož potřebujeme k pořadovým číslům, které vrátí funkce POZVYHLEDAT, přiřadit názvy produktů.
= INDEX($A$4:$A$20;POZVYHLEDAT(0;COUNTIF($I$3:I3;$A$4:$A$20);0))
Pro potvrzení této funkce musí ti z vás, co používají Excely bez dynamických polí, potvrdit funkci pomocí kláves CTRL + SHIFT a ENTER.
A jelikož tato funkce na posledních řádcích vrátí chybové hlášky, tak funkci nakonec ještě zabalíme do funkce IFERROR, kde v případě chyby chceme vrátit prázdný textový řetězec.

Označení duplicit
Velmi častým úkolem je i obarvení duplicitních hodnot v seznamu. Toho můžeme jednoduše dosáhnout pomocí podmíněného formátu. Označíme seznam včetně duplicit a na kartě Domů vybereme Podmíněný formát, kde v Pravidlech zvýraznění buněk vybereme Duplicitní hodnoty.

Vybereme formát a po potvrzení se označí duplicitní hodnoty.

vícOznačení duplicit pomocí ikon
V dalším příkladu máme za úkol označit duplicitní hodnoty ve vedlejším sloupci pomocí ikony pomlčky. Máme v podstatě dvě možnosti, jak se k úkolu postavit. Buď chceme označit duplicity včetně prvního výskytu nebo až každý druhý výskyt. Začneme tím, že nejprve označíme všechny duplicity včetně prvního výskytu.
Základem bude funkce COUNTIF, ve které označíme celý sloupec hodnot a v parametru kritérium označíme první buňku seznamu. Tato funkce vrátí počet výskytů jednotlivých produktů. Nás zajímají produkty, které se v seznamu vyskytují vícekrát, takže funkci COUNTIF zabalíme do podmínky KDYŽ, kde ověříme podmínku, že funkce COUNTIF je vyšší než jedna. A pokud bude podmínka splněná, tak chceme vrátit ikonu pomlčky. Okno ikon vyvoláme pomocí kláves Windows a tečka. Zde si vybereme ikonu a vložíme ji do funkce. Ikonu zabalíme do uvozovek, jelikož se jedná o textovou hodnotu. A pokud podmínka splněná nebude, tak chceme vrátit prázdný textový řetězec.
=KDYŽ(COUNTIF($A$47:$A$63;A47)>1;”“;””)

Ve druhém sloupci chceme opět označit duplicity, ale tentokrát bez prvního výskytu. Základem bude rovněž funkce COUNTIF, kde ale tentokrát vytvoříme dynamické rozpětí z první buňky. A podmínka bude stejná, opět nás zajímají hodnoty, kde je funkce COUNTIF vyšší než jedna. A i zbytek podmínky bude stejný. Tato funkce označí duplicitní řádky bez prvotního výskytu.

Počet duplicit
V příkladu chceme spočítat počet duplicit ve sloupci. Pomůžeme si funkcí UNIQUE, ve které vytvoříme seznam unikátních hodnot. To znamená, že v poli vybereme seznam hodnot a vyplníme i poslední nepovinný parametr, kde vybereme jedničku, jelikož chceme vrátit unikátní seznam. Následně spočítáme počet unikátních hodnot pomocí funkce POČET2. A abychom získali počet duplicit, tak stačí toto číslo odečíst od celkového počtu produktů. Celkový počet produktů zjistíme pomocí funkce POČET2,kde označíme celý seznam produktů.

V posledním příkladu potřebujeme spočítat počet jedinečných hodnot ve sloupci. Opět bude základem funkce COUNTIF, kde v obou parametrech označíme celý seznam produktů. Tato funkce vrátí počet výskytů jednotlivých produktů. A jelikož nás zajímají jedinečné položky, tak nás zajímají hodnoty, kde se funkce COUNTIF rovná jedničce. Abychom pravdy a nepravdy převedly na jedničky a nuly, které můžeme sečíst, tak funkci zabalíme do funkce SOUČN.SKALÁRNÍ a před podmínku uvedeme dvojitý negativ.

Jedna odpověď
😊👍