V dnešním videu se podíváme na to, jak můžeme v Excelu zabránit vložení duplicitních hodnot do sloupce. Tato znalost se vám bude hodit v případě, že chcete zabránit, aby vám při vyplňování hodnot někdo omylem nebo schválně vložil do sloupce duplicitní záznamy, které byste pak museli hledat a mazat. V tomto videu si ukážeme dvě metody, jak můžeme vložení duplicitních hodnot ošetřit. V závěru videu si rovněž ukážeme obrovskou nevýhodu, které si musíte být vědomi, když se pokoušíte o zabránění vložení duplicit.
Excelový soubor ke stažení:
Podmíněný formát
První možností, jak se s problémem poprat, je použít podmíněné formátování. Tato možnost sice přímo nezabrání vložení duplicitní hodnoty, ale pouze duplicitní hodnotu označí, takže máte vizuální indikaci toho, že máte ve sloupci duplicitní záznam. Jediné, co stačí udělat je, označit sloupec hodnot, včetně prázdných buněk, které budete teprve vyplňovat a na kartě Domů najít podmíněné formátování a zde vybrat Pravidla zvýraznění buněk a možnost dole Duplicitní hodnoty. Ve vyskakovacím okně si můžete nastavit vlastní formát, my pro účely tohoto videa necháme základní nastavení, tedy červenou barvu. Potvrdíme formát. Jelikož v seznamu momentálně nemáme duplicity, tak se nic nestalo. Pokud ale napíšeme duplicitní záznam, tak po se po potvrzení duplicitní záznamy obarví na červeno a vy tak máte okamžitě vizuální indikaci toho, že máte ve sloupci duplicity.
Ověření dat
Druhou možností je použít ověření dat. Tato možnost vám přímo zabrání ve vložení duplicitního záznamu. Pravidlo ověření dat bude založené na funkci COUNTIF, která spočítá počet výskytů hodnot v označen oblasti. Nejprve funkci COUNTIF nastavíme. Vedle ve sloupci napíšeme funkci COUNTIF, ve které nejprve označíme celé rozpětí hodnot, které chceme sledovat. Rozpětí buněk zafixujeme klávesou F4 a jako kritérium označíme první produkt v oblasti. Funkci potvrdíme a stáhneme dolů a u produktů se objevily jedničky, jelikož ve sloupci je každý produkt uvedený pouze jednou.
Pokud do sloupce připíši duplicitu, tak se u takového produktu objeví dvojka, jelikož teď máme tento produkt ve sloupci dvakrát. Pokud ho do sloupce napíši znovu, tak se u produktu objeví trojka atd.
Základem pravidla tedy je, že abychom identifikovali jedinečné hodnoty, tak se funkce COUNTIF musí rovnat jedničce. Když máme pravidlo hotové, tak funkci zkopírujeme.
Na kartě Data najdeme Ověření dat a v nabídce vybereme Vlastní pravidlo. Do pole vložíme zkopírovanou funkci. Potvrdíme a vrátíme se k seznamu.
Pokud do seznamu napíšeme produkt, který už v seznamu existuje, tak na nás vyskočí upozornění, že takový produkt nelze vložit. Což je přesně to, co jsme chtěli.
Tuto hlášku, která na vás vyskočí můžete klidně upravit. A to tak, že označíte hodnoty a vrátíte se do ověření dat, kde vyberete Chybové hlášení a nastavíte si zde vlastní zprávu. Třeba Vložili jste duplicitní záznam, což není povoleno. Teď když se pokusíme vložit duplicitní záznam, tak na nás vyskočí chybové hlášení s naším vlastním textem.
Toto je skvělá metoda pro zabránění vložení duplicitních hodnot. Ale jak jsem říkala na začátku videa, obě tyto metody mají jednu obrovskou nevýhodu, respektive nedokonalost. A tím je kopírování a vkládání hodnot. Podmíněné formátování i ověření dat se totiž dá obejít pomocí prostého kopírování a vložení hodnot. Obě metody fungují pouze tehdy, pokud hodnoty do buněk přímo píšete. To i můžeme lehce ověřit. Vedle máme schovaný list produktů, kde máme očividně duplicity. Zkopírujeme seznam a vložíme ho nejprve do sloupce, na kterém máme uplatněné podmíněné formátování, které má označovat duplicity. Hodnoty vložíme a nic se nestalo. To samé se stane i u druhého sloupce, na kterém máme zákaz vložení duplicit pomocí ověření dat. Zkopírujeme hodnoty a vložíme je do sloupce a nic se nestalo. Žádná chybová hláška na nás nevyskočila a my jsme tak obešli pravidlo ověření dat pomocí prostého kopírování a vložení.
Co s tím?
V zásadě neexistuje jednoduchý způsob, jak tomuto jevu zabránit. Můžete pouze zařídit, že když někdo do sloupce duplicitní záznamy nakopíruje, že na to budete vizuálně upozorněni. A těch možností, jak to zařídit je několik.
První možností je použít nástroj kroužkování neplatných dat přímo v ověření dat. Řekněme, že máme sloupec, na kterém máme uplatněné naše pravidlo ověření dat. Někdo nám do sloupce ale vloží nakopírované hodnoty s duplicitami, čímž naše pravidlo poruší. My můžeme následně sloupec hodnot označit a na kartě Data vybrat v možnosti Ověření dat Zakroužkovat neplatná data. Tím se označí data, která porušují stanovené pravidlo a my máme možnost hodnoty zkontrolovat. Nicméně kroužkovat neplatná data můžete až potom, co je do sloupce vložíte. Bohužel nefunguje to, že bychom nejprve na hodnoty uplatnili kroužkovat neplatná data, následně do sloupce tato neplatná data vložíme a pokud jsme čekali, že se rovnou zakroužkují, tak vás musím zklamat.
To samé platí o podmíněném formátu. V rámci kontroly hodnot můžeme na sloupec uplatnit podmíněné formátování se zvýrazněním duplicit a následně zjistit, zda máte ve sloupci duplicity nebo ne. Nicméně neplatí to, že i když máme toto pravidlo nastavené, že by se označili hodnoty po vložení do seznamu.
Poslední možnost je použít pomocný sloupec s funkcí, která okamžitě zajistí, že v případě vložení neplatných hodnot dojde k upozornění na tuto chybu. Vedle sloupce přidáme nový sloupec, který nazveme třeba kontrola. Do hodnot pro kontrolu přidáme duplicitu. V tomto pomocném sloupci kontrola nejprve pomocí funkce COUNTIF ověříme, zda je zadaná hodnota duplicita. To uděláme pomocí dynamického rozpětí ve funkci COUNTIF. Když tuto funkci stáhneme dolů, tak se u prvního záznamu objeví jednička, jakmile ale funkce narazí na duplicitu, tak vrátí dvojku.
Jedinečnou hodnotu tedy poznáme podle toho, že funkce COUNTIF vrátí jedničku. Můžeme tedy použít pravidlo, že se funkce COUNTIF nemá rovnat jedničce. Tím pádem se pravda vrátí u chybné hodnoty. Ještě ošetříme, že se pravda nevrátí u i prázdných řádků. Takže funkci COUNTIF zabalíme do funkce A, kde druhým pravidlem je, že buňka není prázdná. Teď se pravda vrátí pouze u duplicitní hodnoty.
A toto pravidlo můžeme použít ve funkci KDYŽ. Kde stanovíme, že v případě splnění podmínky vrátí funkce „Duplicitní hodnota“ a pokud podmínka splněná nebude, tak se vrátí prázdný textový řetězec.
Teď máme vizuální indikaci toho, že jsme zadali duplicitu. Funkce bude samozřejmě reagovat i na kopírování a vkládání. Takže když duplicitu vložíme, tak se rovnou vedle objeví duplicitní hodnota. Samozřejmě bychom toto řešení moli ještě vylepšit tak, že uplatníme na sloupce podmíněný formát. Text duplicitní hodnota můžeme udělat třeba červeně. Takže označíme sloupec a vybereme podmíněný formát a text, který obsahuje.
