Duplicitné položky medzi dvoma stĺpcami môžete nájsť a zvýrazniť pomocou funkcie podmieneného formátovania v Tabuľkách Google.
Pri práci v Tabuľkách Google s veľkými súbormi údajov pravdepodobne narazíte na problém, pri ktorom sa musíte vysporiadať s mnohými duplicitnými hodnotami. Zatiaľ čo niektoré duplicitné položky sú umiestnené zámerne, zatiaľ čo iné sú chyby. To platí najmä vtedy, keď spolupracujete na rovnakom hárku s tímom.
Pokiaľ ide o analýzu údajov v Tabuľkách Google, schopnosť odfiltrovať duplikáty môže byť nevyhnutná a pohodlná. Hoci Tabuľky Google nemajú žiadnu natívnu podporu na vyhľadávanie duplikátov v hárkoch, ponúkajú niekoľko spôsobov, ako porovnať, identifikovať a odstrániť duplicitné údaje v bunkách.
Niekedy chcete porovnať každú hodnotu v stĺpci s iným stĺpcom a zistiť, či v ňom nie sú nejaké duplikáty a naopak. V Tabuľkách Google môžete ľahko nájsť duplikáty medzi dvoma stĺpcami pomocou funkcie podmieneného formátovania. V tomto článku vám ukážeme, ako porovnať dva stĺpce v Tabuľkách Google a nájsť medzi nimi duplikáty.
Nájdite duplicitné položky medzi dvoma stĺpcami pomocou podmieneného formátovania
Podmienené formátovanie je funkcia v Tabuľkách Google, ktorá umožňuje používateľovi použiť špecifické formátovanie, ako je farba písma, ikony a dátové pruhy na bunku alebo rozsah buniek na základe určitých podmienok.
Toto podmienené formátovanie môžete použiť na zvýraznenie duplikátov medzi dvoma stĺpcami, a to buď vyplnením buniek farbou, alebo zmenou farby textu. Musíte porovnať každú hodnotu v stĺpci s iným stĺpcom a zistiť, či sa nejaká hodnota opakuje. Aby to fungovalo, musíte použiť podmienené formátovanie na každý stĺpec samostatne. Postupujte podľa týchto krokov:
Otvorte tabuľku, v ktorej chcete skontrolovať duplikáty, v Tabuľkách Google. Najprv vyberte prvý stĺpec (A), ktorý chcete skontrolovať so stĺpcom B. Kliknutím na písmeno stĺpca nad ním môžete zvýrazniť celý stĺpec.
Potom kliknite na ponuku „Formátovať“ na paneli s ponukami a vyberte „Podmienené formátovanie“.
Na pravej strane hárkov Google sa otvorí ponuka Podmienené formátovanie. Môžete potvrdiť, že rozsah buniek je to, čo ste vybrali pod možnosťou „Použiť na rozsah“. Ak chcete zmeniť rozsah, kliknite na ikonu rozsahu a vyberte iný rozsah.
Potom kliknite na rozbaľovaciu ponuku v časti „Pravidlá formátu“ a vyberte možnosť „Vlastný vzorec je“.
Teraz musíte do poľa „Hodnota alebo vzorec“ zadať vlastný vzorec.
Ak ste vybrali celý stĺpec (B:B), zadajte nasledujúci vzorec COUNTIF do poľa „Hodnota alebo vzorec“ v časti Pravidlá formátu:
=countif($B:$B,$A2)>0alebo
Ak ste vybrali rozsah buniek v stĺpci (povedzme sto buniek, A2:A30), použite tento vzorec:
=COUNTIF($B$2:$B$30, $A2)>0Keď zadávate vzorec, nezabudnite nahradiť všetky výskyty písmena „B“ vo vzorci písmenom stĺpca, ktorý ste zvýraznili. Pred odkazy na bunky pridávame znak „$“, aby mali absolútny rozsah, takže sa nemení, použijeme vzorec.
V časti Štýl formátovania si môžete vybrať štýl formátovania na zvýraznenie duplicitných položiek. V predvolenom nastavení použije zelenú farbu výplne.
Môžete si vybrať jeden z prednastavených štýlov formátovania kliknutím na „Predvolené“ pod možnosťami „Štýl formátovania“ a následným výberom jedného z prednastavení.
Alebo môžete použiť ktorýkoľvek zo siedmich nástrojov na formátovanie (tučné, kurzíva, podčiarknutie, prečiarknutie, farba textu, farba výplne) v časti „Štýl formátovania“ na zvýraznenie duplikátov.
Tu vyberáme farbu výplne pre duplicitné bunky kliknutím na ikonu „Farba výplne“ a výberom „žltej“ farby.
Po výbere formátovania kliknite na „Hotovo“ a zvýraznite bunky.
Funkcia COUNTIF počíta, koľkokrát sa každá hodnota bunky v stĺpci A zobrazí v stĺpci B. Ak sa teda položka v stĺpci B objaví čo i len raz, vzorec vráti hodnotu TRUE. Potom bude táto položka zvýraznená v „stĺpci A“ na základe zvoleného formátovania.
Toto nezvýrazní duplikáty, ale skôr položky, ktoré majú duplikáty v stĺpci B. To znamená, že každá žlto zvýraznená položka má duplikáty v stĺpci B.
Teraz musíme použiť podmienené formátovanie na stĺpec B pomocou rovnakého vzorca. Ak to chcete urobiť, vyberte druhý stĺpec (B2:B30), prejdite do ponuky „Formátovať“ a vyberte „Podmienené formátovanie“.
Prípadne kliknite na tlačidlo „Pridať ďalšie pravidlo“ na table „Pravidlá podmieneného formátu“.
Ďalej potvrďte rozsah (B2:B30) v poli „Použiť na rozsah“.
Potom nastavte možnosť „Formátovať bunky, ak...“ na „Vlastný vzorec je“ a do poľa vzorca zadajte nasledujúci vzorec:
=COUNTIF($A$2:$A$30, $B2)>0Tu používame rozsah stĺpca A ($A$2:$A$30) v prvom argumente a „$B2“ v druhom argumente. Tento vzorec skontroluje hodnotu bunky v „stĺpci B“ oproti každej bunke v stĺpci A. Ak sa nájde zhoda (duplikát), podmienené formátovanie zvýši túto položku v „stĺpci B“
Potom zadajte formátovanie v možnostiach „Štýl formátovania“ a kliknite na „Hotovo“. Tu vyberáme oranžovú farbu pre stĺpec B.
Tým sa zvýraznia položky stĺpca B, ktoré majú v stĺpci A duplikáty. Teraz ste našli a zvýraznili duplicitné položky medzi dvoma stĺpcami.
Pravdepodobne ste si všimli, že hoci v stĺpci A je duplikát pre „Arcelia“, nie je zvýraznený. Je to preto, že hodnota duplicatte je iba v jednom stĺpci (A), nie medzi stĺpcami. Preto nie je zvýraznený.
Zvýraznite Duplikáty medzi dvoma stĺpcami v rovnakom riadku
Môžete tiež zvýrazniť riadky, ktoré majú rovnaké hodnoty (duplikáty) medzi dvoma stĺpcami pomocou podmieneného formátovania. Pravidlo podmieneného formátovania môže skontrolovať každý riadok a zvýrazniť riadky, ktoré majú zhodné údaje v oboch stĺpcoch. Urobíte to takto:
Najprv vyberte oba stĺpce, ktoré chcete porovnať, potom prejdite do ponuky „Formátovať“ a vyberte „Podmienené formátovanie“.
Na table Pravidlá podmieneného formátu potvrďte rozsah v poli „Použiť na rozsah“ a z rozbaľovacej ponuky „Bunky vzorca, ak...“ vyberte „Vlastný vzorec je“.
Potom do poľa „Hodnota alebo vzorec“ zadajte nasledujúci vzorec:
=$A2=$B2Tento vzorec porovná dva stĺpce riadok po riadku a zvýrazní riadky, ktoré majú rovnaké hodnoty (duplikáty). Ako vidíte, vzorec zadaný tu platí len pre prvý riadok vybratého rozsahu, ale vzorec sa automaticky použije na všetky riadky vo vybratom rozsahu pomocou funkcie podmieneného formátovania.
Potom zadajte formátovanie z možností „Štýl formátovania“ a kliknite na „Hotovo“.
Ako vidíte, zvýraznia sa iba riadky, ktoré majú zhodné údaje (duplikáty) medzi dvoma stĺpcami a všetky ostatné duplikáty budú ignorované.
Zvýraznite duplicitné bunky vo viacerých stĺpcoch
Pri práci s väčšími tabuľkami s mnohými stĺpcami možno budete chcieť zvýrazniť všetky duplikáty, ktoré sa zobrazujú vo viacerých stĺpcoch, namiesto jedného alebo dvoch stĺpcov. Na zvýraznenie duplikátu vo viacerých stĺpcoch môžete stále použiť podmienené formátovanie.
Najprv vyberte rozsah všetkých stĺpcov a riadkov, v ktorých chcete hľadať duplikáty, namiesto jedného alebo dvoch stĺpcov. Môžete vybrať celé stĺpce podržaním klávesu Ctrl a kliknutím na písmeno v hornej časti každého stĺpca. Prípadne môžete kliknúť na prvú a poslednú bunku vo svojom rozsahu a zároveň podržať kláves Shift a vybrať viacero stĺpcov naraz.
V príklade vyberáme A2:C30.
Potom kliknite na možnosť „Formátovať“ v ponuke a vyberte „Podmienené formátovanie“.
V pravidlách podmieneného formátu nastavte pravidlá formátu na „Vlastný vzorec je“ a potom do poľa „Hodnota alebo vzorec“ zadajte nasledujúci vzorec:
=countif($A$2:$C$30,A2)>Pred odkazy na bunky pridávame znak „$“, aby sa stali absolútnymi stĺpcami, takže sa nemení, použijeme vzorec. Vzorec môžete zadať aj bez znakov „$“, funguje to v oboch smeroch.
Potom pomocou možností „Štýl formátovania“ vyberte formátovanie, v ktorom chcete zvýrazniť duplicitné bunky. Tu vyberáme „žltú“ farbu výplne. Potom kliknite na „Hotovo“.
Tým sa zvýraznia duplikáty vo všetkých stĺpcoch, ktoré ste vybrali, ako je uvedené nižšie.
Po použití podmieneného formátovania môžete pravidlo podmieneného formátovania kedykoľvek upraviť alebo odstrániť.
Ak chcete upraviť aktuálne pravidlo podmieneného formátovania, vyberte ľubovoľnú bunku s podmieneným formátovaním, prejdite do ponuky „Formátovať“ a vyberte „Podmienené formátovanie“.
Tým sa otvorí tabla „Pravidlá podmieneného formátu“ na pravej strane so zoznamom pravidiel formátu aplikovaných na aktuálny výber. Keď umiestnite kurzor myši na pravidlo, zobrazí sa tlačidlo Odstrániť, kliknutím na tlačidlo Odstrániť pravidlo odstránite. Alebo ak chcete upraviť pravidlo, ktoré sa práve zobrazuje, kliknite na samotné pravidlo.
Ak chcete k aktuálnemu pravidlu pridať ďalšie podmienené formátovanie, kliknite na tlačidlo „Pridať ďalšie pravidlo“.
Spočítajte duplikáty medzi dvoma stĺpcami
Niekedy chcete spočítať, koľkokrát sa hodnota v jednom stĺpci opakuje v inom stĺpci. Dá sa to jednoducho urobiť pomocou rovnakej funkcie COUNTIF.
Ak chcete zistiť, koľkokrát existuje hodnota v stĺpci A v stĺpci B, zadajte nasledujúci vzorec do bunky v inom stĺpci:
=COUNTIF($B$2:$B$30,$A2)Zadajte tento vzorec do bunky C2. Tento vzorec spočíta, koľkokrát existuje hodnota v bunke A2 v stĺpci (B2:B30) a vráti počet v bunke C2.
Keď napíšete vzorec a stlačíte Enter, zobrazí sa funkcia automatického dopĺňania, kliknutím na značku začiarknutia automaticky vyplníte tento vzorec do zvyšku buniek (C3:C30).
Ak sa funkcia automatického dopĺňania nezobrazí, kliknite na modrý štvorec v pravom dolnom rohu bunky C2 a potiahnite ho nadol, čím skopírujete vzorec v bunke C2 do buniek C3:C30.
Stĺpec „Porovnanie 1“ (C) vám teraz zobrazí, koľkokrát sa každá zodpovedajúca hodnota v stĺpci A objavila v stĺpci B. Napríklad hodnota A2 alebo „Franklyn“ sa nenachádza v stĺpci B, takže Funkcia COUNTIF vráti „0“. A hodnota „Loreta“ (A5) sa v stĺpci B nachádza dvakrát, preto vráti „2“.
Teraz musíme zopakovať rovnaké kroky, aby sme našli duplicitné počty stĺpca B. Ak to chcete urobiť, zadajte nasledujúci vzorec do bunky D2 v stĺpci D (Porovnanie 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“. Funkcia spočíta, koľkokrát existuje hodnota v bunke B2 v stĺpci A (A2:A30) a vráti počet v bunke D2.
Potom automaticky vyplňte vzorec do zvyšku buniek (D3:D30) v stĺpci D. Teraz vám „Porovnanie 2“ ukáže, koľkokrát sa každá zodpovedajúca hodnota v stĺpci B zobrazí v stĺpci A. Napríklad , hodnota B2 alebo „Stark“ sa v stĺpci A nachádza dvakrát, takže funkcia COUNTIF vráti hodnotu „2“.
Poznámka: Ak chcete počítať duplikáty vo všetkých stĺpcoch alebo vo viacerých stĺpcoch, stačí zmeniť rozsah v prvom argumente funkcie COUNTIF na viacero stĺpcov namiesto iba jedného stĺpca. Napríklad zmeňte rozsah z A2:A30 na A2:B30, ktorý bude počítať všetky duplikáty v dvoch stĺpcoch namiesto jedného.
to je všetko.