Jak v Excelu najít duplicity

Co je to duplicita

Slovo duplicita samo o sobě znamená zdvojení, dvojitost, dvojí provádění. Ve spojení s daty tedy situace, kdy máte některou z hodnot zadanou dva (nebo i vícekrát), v naprosto stejné podobě.

Když se odkážeme na úvod tohoto článku, může to být například situace, kdy se vám během let sbírka rozrostla natolik, že už se opakujete a některé věci máte zadané (omylem) vícekrát. V dnešní době je již Excel natolik chytrý, že vám už při samotném zadávání položky pomůže zjistit, že ji už zadanou máte – poznáte to tak, že po zadání prvních několika znaků textu Excel „napovídá“, co byste mohli chtít doplnit. Pokud tedy ve vašem seznamu již tato hodnota existuje, měli byste to pomocí této nápovědy poznat.

Jsou situace, kdy tomu tak ale být nemusí – kdy Excel prostě z nějakého důvodu nepozná, že se opakujete. Proč a v jakých případech toto může nastat, se dozvíte ke konci tohoto článku, v boxíku “na co si dát pozor”.

Jak v Excelu najít duplicity podmíněným formátováním (jednodušší způsob)

Ať už porovnáváte data v jednom sloupci (jakýkoliv seznam), nebo ve dvou (data ze dvou různých zdrojů, které potřebujete porovnat), dohledat duplicity je zřejmě nejsnadnější pomocí tzv. podmíněného formátování – ač název může evokovat opak, je to velmi jednoduché.

V prvním kroku si označíte oblast (ideálně celý sloupec, tak budete mít jistotu, že po přidání dat v budoucnu zjistíte duplicity i u těchto nově přidaných) buněk, kterou chcete prohledat. Tedy jeden, nebo dva sloupce, podle toho, co porovnáváte. Pokud nevíte, jak označit celý sloupec, nebo více sloupců najednou, podívejte se do boxíku níže.

Poté na záložce „Domů“ (měla by to být ta, která se vám automaticky otevře po spuštění Excelu) klikněte postupně na tyto volby: Podmíněné formátování -> Pravidla zvýraznění buněk -> Duplicitní hodnoty (v anglické verzi Conditional Formatting -> Highlight Cells Rules -> Duplicate Values).

V novém okně si pak již jen vyberete, zda chcete označit „Duplicitní“ (Duplicate), nebo „Jedinečné“ (Unique) hodnoty – tedy zda chcete, aby Excel označil hodnoty opakující se, nebo naopak ty, které jsou v souboru jen jednou. To může mít praktické využití, když si například v Excelu vedete domácí účetnictví a nesouhlasí vám částka s internetovým bankovnictvím – exportem dat z bankovnictví, jejich nakopírováním do Excelu a následným označením jedinečných hodnot zjistíte, kterou platbu jste si zapomněli zapsat.

V tomto okně si můžete i zvolit, jak se mají duplicitní hodnoty označit – tedy například zbarvení buňky, nebo písma, ale pro správné vyhledání duplicit toto nastavení nemá vliv.

Jakmile máte duplicity nalezené, stačí hodnoty prohlédnout, nebo v prvním řádku zapnout filtr: Seřadit a filtrovat -> Filtr (Sort & Filter -> Filter), případně kliknout kamkoliv do tabulky a stisknout klávesovou zkratku CTRL+SHIFT+L, a vyfiltrovat buňky zbarvené dle vaší volby.

Jak v Excelu označit najednou více sloupců

Jeden celý sloupec označíte jednoduše tak, že najedete myší zhruba do míst, kde je označení sloupce písmenkem, tedy nad první řádek vašich dat a, jakmile se místo kurzoru objeví tlustá šipka, směřující dolů, můžete na sloupec kliknout – označí se celý (až do konce, tedy do zhruba miliontého řádku, což vůbec ničemu nevadí).

Pokud chcete označit dva sousední sloupce, podržíte stisknutou myš a současně s ní dojedete nad sousední sloupec.

V případě, že dva sloupce, kde chcete v Excelu najít duplicity, spolu nesousedí, je jejich označení také jednoduché – označíte jeden, pustíte myš, stisknete a podržíte klávesu CTRL, a současně označíte jiný sloupec.

Jak v Excelu najít duplicity vzorcem (složitější způsob)

Druhý způsob, jak v Excelu najít duplicity, uvedeme jen pro úplnost – pokud si nejste v prací s Excelem 100% jistí, je jednodušší použít podmíněné formátování. Vzorec, nebo správněji funkce, bude fungovat dobře, pokud budete hledat duplicity v jednom sloupci – zde vám dokonce dokáže říct, kolikrát se hodnota opakuje. Pokud však chcete porovnat sloupce dva, musíte myslet na to, že Excel bude vyhledávat duplicity pouze vzájemně mezi sloupci – za určitých okolností tedy bude ignorovat duplicity v rámci stejného sloupce. Je proto potřeba předem zvážit, jak chcete data prohledávat.

Pokud nevíte, přečtěte si náš návod jak v Excelu zadat funkci.

Pokud chcete v Excelu najít duplicity vzorcem, použijete funkci COUNTIF. Do prvního volného sloupce za porovnávaná data napíšete „=“, a poté vepíšete název funkce, tedy COUNTIF. Stisknete buď tabelátor (klávesa TAB), nebo ručně vypíšete začátek závorky, a uvidíte, že vám Excel napovídá – tučně zvýraznil slovo „oblast“. Myší tedy označíte oblast buněk, kde chcete duplicity hledat. Stisknete středník, „;“, a myší kliknete opět na buňku s daty, která porovnáváte, a která sousedí s tou, kam vpisujete vzorec. Uzavřete závorku, stisknete Enter a máte skoro hotovo.

Vzorec si rozkopírujte do zbylých řádků – klikněte do buňky se vzorcem, najeďte na pravý dolní roh a jednoduše dvakrát rychle klikněte – vůbec není potřeba myš „stahovat“ dolů. Excel sám vzorec zkopíruje až tam, kde máte nějaká data. Pokud náhodou najde prázdné pole, s kopírováním skončí, ale pokud je prázdná buňka v pořádku a vaše data pokračují, stačí udělat to stejné znova na místě, kde našel prázdnou buňku a skončil.

Pohledem na tabulku pak snadno zjistíte (nebo si vyfiltrujete), která data máte v tabulce jen jednou (1), a která se opakují (2 a výše).

Na co si dát při hledání duplicit pozor, aneb „proč to nefunguje“

Pokud máte pocit, že v datech určitě duplicity máte, ale Excel je nenašel, důvodem může být formát, jak data ukládáte. Pokud si vedete seznam alb oblíbených zpěváků a chcete si v grafu zobrazit, kolikrát máte nějaké album každého z nich, musíte si dát pozor, abyste měli vždy data napsaná totožně. Excel je poměrně inteligentní, ale Karla Gotta si s Gottem Karlem nespojí. Jak jste si ale mohli všimnout v předchozím obrázku, rozdíl mezi velkými a malými písmeny Excelu nevadí.

Abyste se podobných chyb vyvarovali, je vhodné si už do názvu sloupce psát, v jakém formátu budete data vkládat, a držet se toho.

Dalším problémem, hůře odhalitelným, může být to, že zadání hodnoty v seznamu ukončíte mezerou, tedy: „Karel Gott “, namísto „Karel Gott“. Mezera je bohužel zrádná v tom, že je to znak, který nejde vidět, tudíž je těžké ji odhalit. Pokud jste si tento nešvar psaní mezer tam, kde nemají být, osvojili, není ještě nic ztraceno – Excel sice rozdíl mezi oběma Karly nepozná, ale umí přebytečné mezery odstranit, ať už je napíšete před Karlem, za Gottem, nebo jich prostě mezi Karla a Gotta dáte více, než je potřeba.

Jak v Excelu odstranit přebytečné mezery

K odstranění přebytečných mezer slouží funkce PROČISTIT (TRIM v AJ verzi). Do volné buňky vedle seznamu tedy napíšete „=PROČISTIT“, opět stisknete TAB nebo vepíšete závorku a kliknete na buňku stejného řádku, ve vedlejším sloupci, kde máte text k odstranění mezer. Tento vzorec zase zkopírujete až dolů a máte hotovo – Excel odstraní všechny přebytečné mezery a nechá vždy jen jednu mezi jednotlivými slovy.

Jakmile pročistění dokončíte, vyberte myší všechny opravené hodnoty, zkopírujte je a uložte místo těch původních – vyberte přitom možnost „uložit jako hodnoty“ (Paste as Values), tedy pravým klikem myši -> Možnosti vložení (Paste options) -> ikonka s 123 (hodnoty – values). Tímto důležitým krokem přepíšete původní data (s mezerami navíc) ve svém seznamu. Data v pomocném sloupci (funkce PROČISTIT) pak můžete vymazat.