Jak zvýraznit duplicitní buňky a řádky v Excelu

V tomto kurzu se dozvíte, jak v Excelu zobrazit duplicity. Podíváme se blíže na různé metody stínování duplicitních buněk, celých řádků nebo po sobě jdoucích duplicit pomocí podmíněného formátování. Také uvidíte, jak zvýraznit duplicity různými barvami pomocí specializovaného nástroje.

Poslední týden jsme prozkoumali různé způsoby identifikace duplicit v aplikaci Excel. Duplicitní vzorce jsou nepochybně velmi užitečné, ale zvýraznění duplicitních položek definovanou barvou by mohlo analýzu dat ještě více usnadnit.

Nejrychlejší způsob, jak v Excelu najít a zvýraznit duplicity, je použití podmíněného formátování. Největší výhodou této metody je, že nejen zobrazuje existující duplicity, ale při zadávání, úpravách nebo přepisování dat detekuje a barevně zvýrazňuje nové duplicity.

Dále v tomto návodu najdete řadu způsobů zvýraznění duplicitních záznamů v závislosti na konkrétní úloze. Tyto techniky fungují ve všech verzích aplikace Excel 2016, Excel 2013, Excel 2010 a nižších.

• Zvýraznění duplicitních záznamů v aplikaci Excel s 1. výskytem (vestavěným-).zabudované pravidlo)
• Jak zvýraznit duplicity v aplikaci Excel s výjimkou 1. výskytu
• Jak zvýraznit duplicity v rozsahu (více sloupců)
• Jak zvýraznit N-tou a následující duplicitu záznamů
• Zastínění celých řádků na základě duplicitních hodnot v jednom sloupci
• Jak zvýraznit duplicitní řádky v Excelu
• Jak zobrazit po sobě jdoucí duplicitní buňky
• Odstranění duplicit – nejrychlejší způsob zvýraznění duplicit v aplikaci Excel

Jak zvýraznit duplicity v aplikaci Excel pomocí vestavěného pravidla (s 1. výskytem)

Pro začátek, ve všech verzích aplikace Excel existuje předdefinované pravidlo pro zvýraznění duplicitních buněk. Chcete-li toto pravidlo použít v pracovních listech, proveďte následující kroky:

1. Vyberte data, která chcete zkontrolovat na duplicity. Může to být sloupec, řádek nebo rozsah buněk.
2. Na kartě Domů klepněte ve skupině Styly na tlačítko Podmíněné formátování > Pravidla zvýraznění buněk > Duplicitní hodnoty…
   
3. Dialogové okno Duplicitní hodnoty se otevře s výchozí volbou Světle červená výplň a Tmavě červený text. Chcete-li použít výchozí formát, stačí klepnout na tlačítko OK.

Kromě červené výplně a formátování textu je v rozevíracím seznamu k dispozici několik dalších předdefinovaných formátů. Chcete-li zvýraznit duplikáty jinou barvou, klikněte na položku Vlastní formát… (poslední položka v rozevíracím seznamu) a vyberte výplň a/nebo barvu písma podle svých představ.

Pomocí vestavěného pravidla můžete zvýraznit duplicity v jednom sloupci nebo v několika sloupcích, jak ukazuje následující snímek obrazovky:

Při použití vestavěného pravidla aplikace Excel pro zvýraznění duplicitních hodnot mějte na paměti následující dvě věci:

• Funguje pouze pro jednotlivé buňky. Pro zvýraznění duplicitních řádků byste museli vytvořit vlastní pravidla buď na základě hodnot v konkrétním sloupci, nebo porovnáním hodnot v několika sloupcích.
• Zastíní duplicitní buňky včetně jejich prvního výskytu. Chcete-li zvýraznit všechny duplicity kromě prvních výskytů, vytvořte pravidlo podmíněného formátování založené na tomto vzorci.

Jak zvýraznit duplicity bez 1. výskytů

Chcete-li zvýraznit 2. a všechny další výskyty duplicit, vyberte buňky, které chcete obarvit, a vytvořte pravidlo založené na vzorci tímto způsobem:

1. Na kartě Domů klikněte ve skupině Styly na tlačítko Podmíněné formátování > Nové pravidlo > Pomocí vzorce určete, které buňky se mají formátovat.
2. V poli Formátovat hodnoty, kde platí tento vzorec zadejte vzorec podobný tomuto:
   =COUNTIF($A:$A2,$A2)>1

   Kde A2 je nejvyšší buňka vybraného rozsahu.

3. Klikněte na tlačítko Formátovat… a vyberte požadovanou barvu výplně a/nebo písma.
4. Klepnutím na tlačítko OK nakonec pravidlo uložte a použijte.

Pokud nemáte s podmíněným formátováním aplikace Excel mnoho zkušeností, najdete podrobný postup pro vytvoření pravidla založeného na vzorci v následujícím návodu: Podmíněné formátování aplikace Excel založené na hodnotě jiné buňky.

Výsledkem bude, že duplicitní buňky s výjimkou prvních instancí budou zvýrazněny vámi zvolenou barvou:

Jak zobrazit 3., 4. a všechny následující duplicitní záznamy

Chcete-li zobrazit duplicity začínající N-tým výskytem, vytvořte pravidlo podmíněného formátování na základě vzorce jako v předchozím příkladu, pouze s tím rozdílem, že na konci vzorce nahradíte >1 požadovaným číslem. Například:

Chcete-li zvýraznit 3. a všechny následující duplicitní výskyty, vytvořte pravidlo podmíněného formátování založené na tomto vzorci:

=COUNTIF($A:$A2,$A2)>=3

Chcete-li zvýraznit 4. a všechny následující duplicitní záznamy, použijte tento vzorec:

=COUNTIF($A:$A2,$A2)>=4

Chcete-li zvýraznit pouze konkrétní výskyty, použijte operátor rovná se (=). Chcete-li například zvýraznit pouze 2. výskyt, použijete tento vzorec:

=COUNTIF($A:$A2,$A2)=2

Jak zvýraznit duplicity v rozsahu (více sloupců)

Pokud chcete zkontrolovat duplicity nad více sloupci, nikoliv vzájemným porovnáním sloupců, ale najít všechny výskyty stejné položky ve všech sloupcích, použijte jedno z následujících řešení.

Zvýraznění duplicit ve více sloupcích včetně 1. výskytu

Pokud je první instance položky, která se v datovém souboru vyskytuje více než jednou, považována za duplicitu, je nejjednodušší použít vestavěné pravidlo aplikace Excel pro duplicity.

Nebo vytvořte pravidlo podmíněného formátování pomocí tohoto vzorce:

Příklad pro zvýraznění duplicit v rozsahu A2:C8, vzorec vypadá následovně:

=COUNTIF($A:$C, A2)>1

Všimněte si prosím použití absolutních odkazů na buňky pro rozsah ($A$2:$C$8) a relativních odkazů pro horní buňku (A2).

Zvýraznění duplicit ve více sloupcích bez 1. výskytu

Řešení tohoto scénáře je mnohem složitější, není divu, že Excel pro něj nemá vestavěné pravidlo 🙂

Chcete-li zvýraznit duplicitní položky ve více sloupcích s ignorováním 1. výskytů, budete muset vytvořit 2 pravidla s následujícími vzorci:

Pravidlo 1: Duplicitní položky ve více sloupcích. Platí pro první sloupec

Zde použijete přesně stejný vzorec, jaký jsme použili pro zvýraznění duplicit bez 1. výskytů v jednom sloupci (podrobný postup najdete zde).

V tomto příkladu vytváříme pravidlo pro A2:A8 s tímto vzorcem:

=COUNTIF($A:$A2,$A2)>1

Výsledkem je zvýraznění duplicitních položek bez 1. výskytu v nejlevějším sloupci rozsahu (v našem případě je taková položka pouze jedna):

Pravidlo 2. Platí pro všechny následující sloupce

Pro zvýraznění duplicit ve zbývajících sloupcích (B2:C8) použijte tento vzorec:

=COUNTIF(A:$A,B2)+COUNTIF(B:B2,B2)>1

V uvedeném vzorci první funkce COUNTIF počítá výskyty dané položky v prvním sloupci a druhá funkce COUNTIF provádí totéž pro všechny následující sloupce. A pak tato čísla sečtete a zkontrolujete, zda je součet větší než 1.

Výsledkem je nalezení a zvýraznění všech duplicitních položek s výjimkou jejich 1. výskytu:

Zvýraznění duplicit ve všech sloupcích pomocí jediného pravidla

Jiným možným řešením je přidat vlevo od souboru dat prázdný sloupec a výše uvedené vzorce spojit do jediného vzorce takto:

=IF(COLUMNS($B2:B2)>1,COUNTIF(A:$B,B2),0)+COUNTIF(B:B2,B2)>1

Kde B2 je horní buňka s daty ve 2. sloupci cílového rozsahu.

Pro lepší pochopení vzorce jej rozdělíme na 2 hlavní části:

• Pro první sloupec (B) není nikdy splněna podmínka IF, takže se použije pouze druhá funkce COUNTIF, což je běžný vzorec, který jsme již použili pro vyhledání duplicit bez 1. výskytu v jednom sloupci.
• Pro všechny následující sloupce (C2:D8) je klíčové chytré použití absolutních a relativních odkazů v obou funkcích COUNTIF. Pro snazší pochopení jsem jej zkopíroval do sloupce G, abyste viděli, jak se vzorec změní při aplikaci na další buňky:
  

Protože podmínka IF je vždy TRUE pro všechny sloupce kromě prvního (počet sloupců je větší než 1), vzorec postupuje tímto způsobem:

• Spočítá počet výskytů dané položky (D5 na obrázku výše) ve všech sloupcích nalevo od daného sloupce: COUNTIF(B:$C,D5)
• Spočítá počet výskytů dané položky v daném sloupci až po buňku dané položky: COUNTIF(D:D5,D5)
• Nakonec vzorec sečte výsledky obou funkcí COUNTIF. Pokud je celkový počet větší než 1, tj. pokud existuje více než jeden výskyt položky, použije se pravidlo a položka se zvýrazní.

Zvýraznění celých řádků na základě duplicitních hodnot v jednom sloupci

Pokud vaše tabulka obsahuje několik sloupců, můžete chtít zvýraznit celé řádky na základě duplicitních záznamů v určitém sloupci.

Jak již víte, vestavěné pravidlo aplikace Excel pro duplicity funguje pouze na úrovni buněk. Vlastní pravidlo založené na vzorci však nemá problém se stínováním řádků. Klíčové je vybrat celé řádky a poté vytvořit pravidlo s jedním z následujících vzorců:

• Pro zvýraznění duplicitních řádků bez 1. výskytu:
  =COUNTIF($A:$A2, $A2)>1
• Pro zvýraznění duplicitních řádků včetně 1. výskytu:
  =COUNTIF($A:$A, $A2)>1

Kde A2 je první buňka a A15 je poslední použitá buňka ve sloupci, který chcete kontrolovat na duplicity. Jak vidíte, rozdíl je v chytrém použití absolutních a relativních odkazů na buňky.
Následující snímek obrazovky demonstruje obě pravidla v akci:

Jak zvýraznit duplicitní řádky v aplikaci Excel

Předchozí příklad ukázal, jak podbarvit celé řádky na základě duplicitních hodnot v konkrétním sloupci. Co když ale chcete zobrazit řádky, které mají shodné hodnoty v několika sloupcích? Nebo jak zvýraznit absolutně duplicitní řádky, tedy ty, které mají zcela stejné hodnoty ve všech sloupcích?

Pro tyto účely použijte funkci COUNTIFS, která umožňuje porovnávat buňky podle více kritérií. Chcete-li například zvýraznit duplicitní řádky, které mají shodné hodnoty ve sloupcích A a B, použijte jeden z následujících vzorců:

• Pro zvýraznění duplicitních řádků s výjimkou 1. výskytu:
  =COUNTIFS($A:$A2, $A2, $B:$B2, $B2)>1
• Pro zvýraznění duplicitních řádků s 1. výskytem:
  =COUNTIFS($A:$A, $A2, $B:$B, $B2)>1

Následující snímek obrazovky demonstruje výsledek:

Jak jistě chápete, výše uvedený příklad slouží pouze pro demonstrační účely. Při zvýrazňování duplicitních řádků v reálných listech nejste samozřejmě omezeni na porovnávání hodnot pouze ve 2 sloupcích, funkce COUNTIFS dokáže zpracovat až 127 dvojic rozsah/kritérium.

Zvýraznění po sobě jdoucích duplicitních buněk v Excelu

Někdy nemusíte potřebovat zvýraznit všechny duplicity ve sloupci, ale spíše zobrazit pouze po sobě jdoucí duplicitní buňky, tj. ty, které jsou vedle sebe. Za tímto účelem vyberte buňky s daty (bez záhlaví sloupce) a vytvořte pravidlo podmíněného formátování s jedním z následujících vzorců:

• Pro zvýraznění po sobě jdoucích duplicit bez 1. výskytu:
  =$A1=$A2
• Pro zvýraznění po sobě jdoucích duplicit s 1. výskytem:
  =OR($A1=$A2, $A2=$A3)

Následující obrázek demonstruje zvýraznění po sobě jdoucích duplicitních textů, ale tato pravidla budou fungovat i pro po sobě jdoucí duplicitní čísla a data:

Pokud může mít váš list aplikace Excel prázdné řádky a nechcete, aby se po sobě jdoucí prázdné buňky zvýraznily, proveďte následující vylepšení vzorců:

• Pro zvýraznění po sobě jdoucích duplicitních buněk bez 1. výskytu a ignorování prázdných buněk:
  =AND($A2<>"", $A1=$A2)
• Pro zvýraznění po sobě jdoucích duplicitních buněk s 1. výskytem a ignorování prázdných buněk:
  =AND($A2<>"", OR($A1=$A2, $A2=$A3))

Jak vidíte, zvýraznění duplicit v aplikaci Excel pomocí podmíněného formátování není žádný velký problém. Existuje však ještě rychlejší a jednodušší způsob. Chcete-li ho zjistit, přečtěte si další část tohoto návodu.

Jak zvýraznit duplicity v Excelu pomocí nástroje Odstraňovač duplicit

Doplněk Odstraňovač duplicit je univerzálním řešením pro řešení duplicitních záznamů v Excelu. Dokáže najít, zvýraznit, vybrat, zkopírovat nebo přesunout duplicitní buňky nebo celé duplicitní řádky.

Přes svůj název dokáže doplněk rychle zvýraznit duplicity různými barvami, aniž by je odstranil.

Odstraňovač duplicit přidává na pás karet aplikace Excel 3 nové funkce:

• Duplicitní tabulka – k okamžitému vyhledání a zvýraznění duplicit v jedné tabulce.
• Odstranění duplicit – průvodce krok za krokem s pokročilými možnostmi pro identifikaci a zvýraznění duplicit nebo jedinečných hodnot v 1 tabulce.
• Porovnat 2 tabulky – nalezení a zvýraznění duplicit porovnáním dvou sloupců nebo dvou samostatných tabulek.

Po instalaci sady Ultimate Suite pro aplikaci Excel najdete tyto nástroje na kartě Ablebits Data ve skupině Dedupe:

Zvýraznění duplicit v Excelu na pár kliknutí

Pro tento příklad jsem vytvořil následující tabulku s několika stovkami řádků. A naším cílem je zvýraznit duplicitní řádky, které mají stejné hodnoty ve všech třech sloupcích:

Věřte nebo ne, ale kýženého výsledku dosáhnete pouhými dvěma kliknutími myší 🙂

1. Při výběru libovolné buňky v tabulce klikněte na tlačítko Deduplikovat tabulku a chytrý doplněk vybere celou tabulku.
2. Otevře se dialogové okno Deduplikovat tabulku s automaticky vybranými všemi sloupci a s výchozí volbou Vybarvit duplikáty. Takže stačí jen kliknout na tlačítko OK 🙂 Hotovo!

Tip. Pokud chcete zjistit duplicitní řádky podle jednoho nebo více sloupců, zrušte zaškrtnutí všech nepodstatných sloupců a ponechte vybraný pouze klíčový sloupec (sloupce).

A výsledek by vypadal podobně:

Jak vidíte na výše uvedeném obrázku, nástroj Dupe Table zvýraznil duplicitní řádky bez prvních výskytů.

Pokud chcete zvýraznit duplicity včetně prvních výskytů, nebo pokud chcete vybarvit jedinečné záznamy místo duplicit, nebo pokud se vám nelíbí výchozí červená barva, pak použijte průvodce Odstranění duplicit, který má všechny tyto funkce a mnoho dalších.

Zvýrazněte duplicity v Excelu pomocí pokročilého průvodce krok za krokem

V porovnání s rychlým nástrojem Deduplikační tabulka vyžaduje průvodce Odstranění duplicit o několik kliknutí více, ale vynahrazuje to řadou dalších možností. Ukážu vám ho v akci:

1. Vybere libovolnou buňku v tabulce, ve které chcete zvýraznit duplicity, a klikne na tlačítko Odstranění duplicit na pásu karet. Spustí se průvodce a vybere se celá tabulka. Doplněk vám také pro jistotu nabídne vytvoření záložní kopie tabulky. Pokud ji nepotřebujete, zrušte zaškrtnutí tohoto políčka.

   Ověřte, zda byla tabulka vybrána správně, a klepněte na tlačítko Další.

   

2. Vyberte jeden z následujících typů dat, které chcete najít:
   • Duplikáty kromě 1. výskytu
   • Duplikáty s 1. výskytem
   • Jedinečné hodnoty
   • Jedinečné hodnoty a 1. výskyt duplikátu

   Pro tento příklad najdeme Duplikáty + 1. výskyt:
   

3. Nyní vyberte sloupce, které chcete zkontrolovat na duplicity. Protože chceme zvýraznit kompletní duplicitní řádky, vybral jsem všechny 3 sloupce.
   

   Přídavek navíc umožňuje určit, zda má tabulka záhlaví a zda chcete vynechat prázdné buňky. Obě možnosti jsou ve výchozím nastavení zaškrtnuty.

4. Nakonec vyberte akci, která se má u duplicit provést. Máte řadu možností, například výběr, odstranění, kopírování, přesun duplicit nebo přidání stavového sloupce pro identifikaci duplicit.

Protože dnes zkoumáme různé způsoby zvýraznění duplicit v Excelu, je naše volba zřejmá :.) Vyberte tedy možnost Vyplnit barvou a zvolte jednu ze standardních barev motivu, nebo klepněte na tlačítko Další barvy… a vyberte libovolnou vlastní barvu RGB nebo HSL.

Klepněte na tlačítko Dokončit a užijte si výsledek 🙂

Takto zvýrazníte duplicity v Excelu pomocí našeho doplňku Odstraňovač duplicit. Pokud jste zvědaví, zda si tento nástroj vyzkoušíte na vlastních listech, rádi si stáhnete plně funkční zkušební verzi sady Ultimate Suite, která obsahuje všechny naše nástroje pro úsporu času v Excelu. A velmi oceníme vaši zpětnou vazbu v komentářích!“

Jestliže jste s výkonem nástroje Duplicate Remover spokojeni, rádi vám tuto mimořádnou příležitost poskytneme:

Mohlo by vás také zajímat

• Jak identifikovat duplicity v Excelu (v 1 tabulce)
• Jak porovnat 2 sloupce nebo tabulky na shody a rozdíly
• Jak odstranit duplicitní řádky v Excelu

.