Duplicitní položky mezi dvěma sloupci můžete najít a zvýraznit pomocí funkce podmíněného formátování v Tabulkách Google.
Při práci v Tabulkách Google s velkými soubory dat pravděpodobně narazíte na problém, kdy se musíte vypořádat s mnoha duplicitními hodnotami. Zatímco některé duplicitní položky jsou umístěny záměrně, zatímco jiné jsou chyby. To platí zejména, když spolupracujete na stejném listu s týmem.
Pokud jde o analýzu dat v Tabulkách Google, schopnost odfiltrovat duplikáty může být zásadní a pohodlná. Ačkoli Tabulky Google nemají žádnou nativní podporu pro hledání duplikátů v listech, nabízejí několik způsobů, jak porovnat, identifikovat a odstranit duplicitní data v buňkách.
Někdy chcete porovnat každou hodnotu ve sloupci s jiným sloupcem a zjistit, zda v něm nejsou nějaké duplikáty a naopak. V Tabulkách Google můžete snadno najít duplikáty mezi dvěma sloupci pomocí funkce podmíněného formátování. V tomto článku vám ukážeme, jak porovnat dva sloupce v Tabulkách Google a najít mezi nimi duplikáty.
Najděte duplicitní položky mezi dvěma sloupci pomocí podmíněného formátování
Podmíněné formátování je funkce v Tabulkách Google, která uživateli umožňuje použít konkrétní formátování, jako je barva písma, ikony a datové pruhy, na buňku nebo rozsah buněk na základě určitých podmínek.
Toto podmíněné formátování můžete použít ke zvýraznění duplicitních položek mezi dvěma sloupci, buď vyplněním buněk barvou, nebo změnou barvy textu. Je třeba porovnat každou hodnotu ve sloupci s jiným sloupcem a zjistit, zda se nějaká hodnota opakuje. Aby to fungovalo, musíte podmíněné formátování aplikovat na každý sloupec zvlášť. Postupujte podle těchto kroků:
Otevřete tabulku, ve které chcete zkontrolovat duplikáty, v Tabulkách Google. Nejprve vyberte první sloupec (A), který chcete zkontrolovat se sloupcem B. Kliknutím na písmeno nad ním můžete zvýraznit celý sloupec.
Poté klikněte na nabídku ‚Formát‘ na liště nabídek a vyberte ‚Podmíněné formátování‘.
Na pravé straně tabulek Google se otevře nabídka Podmíněné formátování. Můžete potvrdit, že rozsah buněk je to, co jste vybrali pod možností „Použít na rozsah“. Pokud chcete změnit rozsah, klikněte na ‚ikonu rozsahu‘ a vyberte jiný rozsah.
Poté klikněte na rozevírací seznam v části „Pravidla formátu“ a vyberte možnost „Vlastní vzorec je“.
Nyní musíte do pole „Hodnota nebo vzorec“ zadat vlastní vzorec.
Pokud jste vybrali celý sloupec (B:B), zadejte následující vzorec COUNTIF do pole „Hodnota nebo vzorec“ v části Pravidla formátu:
=countif($B:$B,$A2)>0Nebo,
Pokud jste vybrali rozsah buněk ve sloupci (řekněme sto buněk, A2:A30), použijte tento vzorec:
=COUNTIF($B$2:$B$30, $A2)>0Když zadáváte vzorec, nezapomeňte nahradit všechny výskyty písmene „B“ ve vzorci písmenem sloupce, který jste zvýraznili. Před odkazy na buňky přidáváme znak „$“, aby měly absolutní rozsah, takže se nemění, použijeme vzorec.
V části Styl formátování si můžete vybrat styl formátování pro zvýraznění duplicitních položek. Ve výchozím nastavení použije zelenou barvu výplně.
Můžete si vybrat jeden z přednastavených stylů formátování kliknutím na ‚Výchozí‘ pod možnostmi ‚Styl formátování‘ a poté výběrem jednoho z přednastavení.
Nebo můžete použít kterýkoli ze sedmi formátovacích nástrojů (tučné, kurzíva, podtržení, přeškrtnutí, barva textu, barva výplně) v části „Styl formátování“ ke zvýraznění duplikátů.
Zde vybíráme barvu výplně pro duplicitní buňky kliknutím na ikonu ‚Výplň barvy‘ a výběrem ‚žluté‘ barvy.
Jakmile vyberete formátování, kliknutím na „Hotovo“ zvýrazněte buňky.
Funkce COUNTIF počítá, kolikrát se každá hodnota buňky ve „sloupec A“ objeví ve „sloupci B“. Pokud se tedy položka ve sloupci B objeví byť jen jednou, vzorec vrátí hodnotu TRUE. Poté bude tato položka zvýrazněna ve „sloupci A“ na základě zvoleného formátování.
To nezvýrazňuje duplikáty, ale zvýrazní položky, které mají duplikáty ve sloupci B. To znamená, že každá žlutě zvýrazněná položka má duplikáty ve sloupci B.
Nyní musíme použít podmíněné formátování na sloupec B pomocí stejného vzorce. Chcete-li to provést, vyberte druhý sloupec (B2:B30), přejděte do nabídky „Formát“ a vyberte „Podmíněné formátování“.
Případně klikněte na tlačítko ‚Přidat další pravidlo‘ v podokně ‚Pravidla podmíněného formátu‘.
Dále potvrďte rozsah (B2:B30) v poli „Použít na rozsah“.
Poté nastavte možnost „Formátovat buňky, pokud...“ na „Vlastní vzorec je“ a do pole vzorce zadejte níže uvedený vzorec:
=COUNTIF($A$2:$A$30, $B2)>0Zde používáme rozsah sloupce A ($A$2:$A$30) v prvním argumentu a „$B2“ ve druhém argumentu. Tento vzorec zkontroluje hodnotu buňky ve „sloupci B“ proti každé buňce ve sloupci A. Pokud je nalezena shoda (duplicitní), podmíněné formátování zvýší tuto položku ve „sloupci B“
Poté zadejte formátování v možnostech „Styl formátování“ a klikněte na „Hotovo“. Zde vybíráme oranžovou barvu pro sloupec B.
Tím se zvýrazní položky sloupce B, které mají ve sloupci A duplikáty. Nyní jste našli a zvýraznili duplicitní položky mezi dvěma sloupci.
Pravděpodobně jste si všimli, že ačkoli ve sloupci A je duplikát pro „Arcelia“, není zvýrazněn. Je to proto, že hodnota duplicatte je pouze v jednom sloupci (A), nikoli mezi sloupci. Není tedy zvýrazněna.
Zvýrazněte Duplikáty mezi dvěma sloupci ve stejném řádku
Můžete také zvýraznit řádky, které mají stejné hodnoty (duplikáty) mezi dvěma sloupci pomocí podmíněného formátování. Pravidlo podmíněného formátování může zkontrolovat každý řádek a zvýraznit řádky, které mají v obou sloupcích odpovídající data. Postupujte takto:
Nejprve vyberte oba sloupce, které chcete porovnat, poté přejděte do nabídky „Formát“ a vyberte „Podmíněné formátování“.
V podokně Pravidla podmíněného formátu potvrďte rozsah v poli „Použít na rozsah“ a z rozbalovací nabídky „Buňky vzorce, pokud...“ vyberte „Vlastní vzorec je“.
Poté do pole „Hodnota nebo vzorec“ zadejte níže uvedený vzorec:
=$A2=$B2Tento vzorec porovná dva sloupce řádek po řádku a zvýrazní řádky, které mají stejné hodnoty (duplikáty). Jak vidíte, vzorec zde zadaný je pouze pro první řádek vybraného rozsahu, ale vzorec bude automaticky aplikován na všechny řádky ve vybraném rozsahu funkcí podmíněného formátování.
Poté zadejte formátování z možností „Styl formátování“ a klikněte na „Hotovo“.
Jak vidíte, budou zvýrazněny pouze řádky, které mají mezi dvěma sloupci shodná data (duplikáty), a všechny ostatní duplikáty budou ignorovány.
Zvýrazněte duplicitní buňky ve více sloupcích
Při práci s většími tabulkami s mnoha sloupci možná budete chtít zvýraznit všechny duplikáty, které se objevují ve více sloupcích, místo pouze jednoho nebo dvou sloupců. Ke zvýraznění duplikátu ve více sloupcích můžete stále používat podmíněné formátování.
Nejprve vyberte rozsah všech sloupců a řádků, ve kterých chcete hledat duplikáty, místo pouze jednoho nebo dvou sloupců. Můžete vybrat celé sloupce podržením klávesy Ctrl a kliknutím na písmeno v horní části každého sloupce. Případně můžete také kliknout na první a poslední buňku ve svém rozsahu a zároveň podržet klávesu Shift a vybrat více sloupců najednou.
V příkladu vybíráme A2:C30.
Poté v nabídce klikněte na možnost „Formátovat“ a vyberte „Podmíněné formátování“.
V pravidlech podmíněného formátu nastavte pravidla formátu na „Vlastní vzorec je“ a poté do pole „Hodnota nebo vzorec“ zadejte následující vzorec:
=countif($A$2:$C$30,A2)>Před odkazy na buňky přidáváme znaménko „$“, abychom z nich udělali absolutní sloupce, takže se nezmění, použijeme vzorec. Vzorec můžete zadat i bez znamének „$“, funguje to oběma způsoby.
Poté pomocí možností „Styl formátování“ vyberte formátování, ve kterém chcete zvýraznit duplicitní buňky. Zde vybíráme barvu výplně „Žlutá“. Poté klikněte na „Hotovo“.
Tím se zvýrazní duplikáty ve všech vybraných sloupcích, jak je znázorněno níže.
Po použití podmíněného formátování můžete pravidlo podmíněného formátování kdykoli upravit nebo odstranit.
Pokud chcete upravit aktuální pravidlo podmíněného formátování, vyberte libovolnou buňku s podmíněným formátováním, přejděte v nabídce na „Formátovat“ a vyberte „Podmíněné formátování“.
Tím se otevře vpravo podokno „Podmíněná pravidla formátu“ se seznamem pravidel formátu aplikovaných na aktuální výběr. Když na pravidlo najedete myší, zobrazí se tlačítko smazat, kliknutím na tlačítko smazat pravidlo odstraníte. Nebo, pokud chcete upravit pravidlo, které se právě zobrazuje, klikněte na samotné pravidlo.
Pokud chcete k aktuálnímu pravidlu přidat další podmíněné formátování, klikněte na tlačítko ‚Přidat další pravidlo‘.
Počítejte duplikáty mezi dvěma sloupci
Někdy chcete spočítat, kolikrát se hodnota v jednom sloupci opakuje v jiném sloupci. To lze snadno provést pomocí stejné funkce COUNTIF.
Chcete-li zjistit, kolikrát existuje hodnota ve sloupci A ve sloupci B, zadejte do buňky v jiném sloupci následující vzorec:
=COUNTIF($B$2:$B$30,$A2)Zadejte tento vzorec do buňky C2. Tento vzorec počítá, kolikrát hodnota v buňce A2 existuje ve sloupci (B2:B30) a vrací počet v buňce C2.
Když napíšete vzorec a stisknete Enter, objeví se funkce Auto-Fill, klikněte na 'Tick mark' pro automatické vyplnění tohoto vzorce do zbytku buněk (C3:C30).
Pokud se funkce automatického vyplňování nezobrazí, klikněte na modrý čtverec v pravém dolním rohu buňky C2 a přetáhněte jej dolů, abyste zkopírovali vzorec v buňce C2 do buněk C3:C30.
Sloupec „Porovnání 1“ (C) vám nyní ukáže, kolikrát se každá odpovídající hodnota ve sloupci A objevila ve sloupci B. Například hodnota A2 nebo „Franklyn“ se nenachází ve sloupci B, takže Funkce COUNTIF vrací „0“. A hodnota „Loreta“ (A5) je ve sloupci B nalezena dvakrát, takže vrací „2“.
Nyní musíme zopakovat stejné kroky, abychom našli duplicitní počty ve sloupci B. Chcete-li to provést, zadejte do buňky D2 ve sloupci D následující vzorec (porovnání 2):
=COUNTIF($A$2:$A$30,$B2)V tomto vzorci nahraďte rozsah od „$B$2:$B$30“ do „$A$2:$A$30“ a „$B2“ na „$A2“. Funkce spočítá, kolikrát existuje hodnota v buňce B2 ve sloupci A (A2:A30) a vrátí počet v buňce D2.
Poté automaticky vyplňte vzorec do zbytku buněk (D3:D30) ve sloupci D. Nyní vám „Porovnání 2“ ukáže, kolikrát se každá odpovídající hodnota ve sloupci B objevila ve sloupci A. Například , hodnota B2 nebo „Stark“ je ve sloupci A nalezena dvakrát, takže funkce COUNTIF vrátí „2“.
Poznámka: Pokud chcete spočítat duplikáty ve všech sloupcích nebo ve více sloupcích, stačí změnit rozsah v prvním argumentu funkce COUNTIF na více sloupců namísto pouze jednoho sloupce. Například změňte rozsah z A2:A30 na A2:B30, který bude počítat všechny duplikáty ve dvou sloupcích namísto pouze v jednom.
A je to.