V dnešním videu si ukážeme 12 praktických příkladů, jak pomocí vlastního nastavení ověření dat kreativně omezíte, jaká data se do vybraných buněk v Excelu budou moci vyplnit. Tento nástroj se vám bude hodit zejména, když potřebujete ošetřit, aby do určitých buněk byly vyplněné pouze hodnoty, které povolíte. Ukážeme si, jak zamezíte, aby vám do buněk někdo vyplňoval duplicitní hodnoty, jak zajistíte, aby do sloupce byly vepsány pouze hodnoty ve správném formátu a nebo aby vám v datumech někdo nevyplňoval třeba víkendy. A to na základě jednoduchých ale velmi účinných funkcí a ověření dat. Toto video je navazujícím videem na Základy ověření dat v Excelu. Takže pokud nevíte, jak s Ověřením dat v Excelu pracovat, tak se nejprve podívejte na toto video.
Excelový soubor ke stažení
Ověření dat v Excelu pomocí vlastních funkcí
K ověření dat použijeme nástroj Ověření dat, který najdeme na liště Data. Pod ověřením dat se skrývá několik možností na výběr, včetně rozbalovacího seznamu. Videa, která máme o rozbalovacím seznamu na Akademii Excelu:
Rozbalovací seznam v buňce – základy
Vyhledávací rozbalovací seznam v buňce
Nás dnes bude zajímat možnost Vlastního nastavení v Ověření dat. Ve vlastním nastavení můžete použít v podstatě jakoukoliv excelovou funkci, jejíž výsledkem je PRAVDA nebo NEPRAVDA, jelikož Ověření dat funguje na principu PRAVD a NEPRAVD.
Pojďme si několik praktických příkladů ukázat.
Omezení celkového součtu hodnot
A začneme hned prvním praktickým příkladem. Řekněme, že plánujeme náklady pro jednotlivé produkty v tabulce. Celkové náklady pro celou skupinu produktů mohou být maximálně ve výši 100 000 Kč (E2).
To znamená, že suma těchto pěti nákladů musí být menší nebo rovno limitu 100 000 Kč. Chceme tedy zamezit situaci, kdy nám někdo nebude respektovat toto omezení a vyplní náklady, které budou v součtu vyšší než dovolený limit 100 000 Kč. Zkusíme si funkci, kterou následně použijeme v Ověření dat, nejprve napsat vedle tabulky. Jednoduché pravidlo vypadá tak, že SUMA pěti buněk (B4:B8), buňky zafixujeme klávesou F4, jak pro sloupce, tak řádky ($B$4:$B$8), musí být menší nebo rovno limitu v buňce E2. Buňku opět plně zafixujeme ($E$2).
Zkusíme vyplnit rozpočet pro produkty. Vyplníme několik prvních čísel a funkce v buňce stále vrací pravdu, jelikož suma vyplněných čísel je menší než limit. Vyplníme i rozpočet pro poslední produkt a najednou se vrátila nepravda, jelikož jsme přesáhli rozpočet. Teď když víme, že funkce funguje, můžeme ji zkopírovat a vložit do Ověření dat. Smažeme nejprve tyto cvičné hodnoty, zkopírujeme funkci, označíme sloupec hodnot s náklady a na kartě Data vybereme ověření dat. Zde vybereme pomocí vlastního pravidla. Do pole vzorce vložíme zkopírovaný vzorec. Funkci potvrdíme a vrátíme se k tabulce.
Začneme vyplňovat rozpočet a jakmile přesáhneme limit, tak nás Excel upozorní.
Tím, že jsme jako limit použili odkaz na buňku E2, tak kdykoliv změníme limit, změní se i celková logika Ověření dat. Změníme limit na 150 000 Kč a změníme některé náklady, a vidíme, že limit v ověření dat reaguje a nechá nás vyplňovat až do limitu 150 000 Kč. Pokud limit přesáhneme, Excel nás upozorní.
DNES
V dalším úkolu máme vyřešit to, že chceme omezit datum, které se vkládá do buňky, a to tak že datum nesmí být novější než dnešní datum. K tomuto účelu můžeme využít datumovou funkci DNES. Opět si nejprve funkci zkusíme vedle buňky. Datum musí být menší než je dnešní datum, takže označíme buňku B3 a napíšeme, že tato buňka musí být menší nebo rovna funkci DNES().
Zkusíme do buňky B3 vyplnit dnešní datum a funkce vrací pravdu, zkusíme vyplnit zítřejší datum a vrátí se nepravda. Cvičnou hodnotu smažeme a když víme, že funkci máme správně, tak funkci zkopírujeme, klikneme do buňky, kam chceme pravidlo vložit a na kartě Data vybereme Ověření dat a vybereme Vlastní možnost. Do pole vzorec vložíme zkopírovanou funkci a potvrdíme. Zkusíme vyplnit zítřejší datum a Excel nás upozorní na chybu.
Pouze čísla
V dalším příkladu chceme omezit, aby se do vybrané buňky B5 mohla psát pouze čísla. Chceme tak zabránit situaci, kdy někdo do buněk s čísly zamotá i text. Pro tento úkol můžeme použít jednoduchou funkci JE.ČISLO. Opět zkusíme funkci nejprve vedle tabulky. Napíšeme funkci JE.ČISLO, kde označíme vybranou buňku, na kterou chceme toto pravidlo uplatnit (B5). Zkusíme do buňky B5 vyplnit číslo a funkce vrací PRAVDU. Když ale do buňky vyplníme text, tak se vrátí NEPRAVDA. To je přesně to, co potřebujeme. Cvičnou hodnotu smažeme, zkopírujeme vzorec, označíme buňku a na kartě Data vybereme Ověření dat. V Ověření dat vybereme Vlastní a zkopírovaný vzorec vložíme do pole vzorec. Potvrdíme a máme zajištěno, že do buňky bude kdokoliv moci vepsat pouze číslo.
Zamezení duplicit
Velmi užitečné je Ověření dat i když chcete zabránit, aby vám někdo do sloupce vyplňoval duplicitní záznamy. Ukážeme si to na následujícím příkladu. Máme sloupec (B7:B9), do kterého chceme vyplnit kódy produktů. Chceme ale zabránit tomu, aby někdo do sloupce vyplnil identické kódy. Jakou funkci můžeme použít pro identifikaci duplicit?
V tomto případu nám poslouží jednoduchá funkce COUNTIF. Zkusíme si opět funkci nejprve napsat vedle tabulky. Když chcete k odhalení duplicit použít funkci COUNTIF, tak ve funkci COUNTIF jako oblast vyplníte nejprve první buňku z rozsahu (B7), následuje dvojtečka a znovu vyplníte první buňku (B7:B7). První buňku v tomto rozsahu ale musíte zafixovat plně jak pro řádky, tak sloupce ($B$7:B7). Tím, že je první buňka plně zafixována, tak se nemůže hýbat a tím jak vzorec potáhnete dolů, tak se vytvoří dynamické rozpětí buněk. Následuje kritérium, což je opět první buňka (B7). Funkci potvrdíme a stáhneme vzorec pro všechny buňky dolů a zkusíme vyplňovat hodnoty.
Tím, že jsme zafixovali první buňku v rozpětí, tak funkce COUNTIF porovnává každé nově zapsané číslo oproti všem již dříve zapsaným. Když narazí na duplicitní záznam, tak napíše dvojku, jelikož se toto číslo ve sloupci vyskytuje již podruhé. Pokud bychom ho napsali znovu, tak se objeví trojka a tak dále. Základem našeho pravidla tedy je, že jedinečné hodnoty jsou takové, kdy se funkce COUNTIF rovná jedné. Funkci máme, takže smažeme pomocné hodnoty, zkopírujeme vzorec, označíme sloupec hodnot a na kartě Data vybereme Ověření dat a Vlastní pravidlo. Vložíme zkopírovaný vzorec do pole vzorce a potvrdíme. Opět zkusíme vyplňovat hodnoty. Jedinečné hodnoty jsou v pořádku a jakmile napíšeme duplicitu, tak nás Excel upozorní.
Pouze čísla a bez duplicit
Pravidla a funkce můžete i spojovat. Jediným pravidlem je, že výsledkem vaší funkci musí být buď pravda nebo nepravda, aby vám ověření dat fungovalo. V dalším příkladu spojíme dvě předchozí pravidla, jelikož chceme, aby se do vyznačených buněk mohli napsat pouze číselné hodnoty a ještě bez duplicit. Obě pravidla přitom musí platit zároveň. Základ funkcí jsme si ukázali, takže je teď musíme pouze spojit pomocí podmínkové funkce A. Vedle napíšeme funkci A, kde v první podmínce ověříme, že je v buňce napsané číslo. Napíšeme tedy funkci JE.ČISLO, kde označíme první buňku (JE.ČISLO(B11)). Následuje středník a ve druhé podmínce ověříme, že hodnota není duplicitní. Napíšeme funkci COUNTIF, kde označíme první buňku, plně ji zafixujeme, dvojtečka a opět první buňka, středník a jako kritérium je opět první buňka (COUNTIF($B$11:B11;B11)). Ukončíme závorku a víme, že se tato funkce musí rovnat jedné (COUNTIF($B$11:B11;B11))=1. Ukončíme závorku u funkce A a funkci potvrdíme. Zkusíme do sloupce vyplnit čísla a nic se neděje, jakmile ale vyplníme duplicitní číslo, tak funkce vrací nepravdu, zkusíme vyplnit text a opět funkce vrací nepravdu. Teď tuto funkci můžete nakopírovat do Ověření dat.
Správný formát kódu
Kde můžete být opravdu kreativní je, když se snažíte omezit správnost formátu. Na výběr máte z obrovského množství textových funkcí, které vám pomohou zamezit špatně vepsanému formátu dat. Ukážeme si tři příklady. O textových funkcích máme pro členy Akademie Excelu bonusové video o využití textových funkcích v praxi.
V prvním případě chceme omezit, aby byl do buňky B15 zapsaný pouze produkt, který začíná označením A. Pro tento úkol využijeme třeba textovou funkci HLEDAT. Textová funkce HLEDAT dělá přesně to, co napovídá její název, hledá zadanou hodnotu v textu. Když hledanou hodnotu najde, tak vrátí pozici tohoto textu, pokud hledanou hodnotu nenajde, vrátí chybovou hlášku. Vedle buňky napíšeme funkci HLEDAT, kde nejprve napíšeme, co hledáme. Chceme, aby byl do buňky zapsaný pouze produkt začínající písmenem A, takže vlastně hledáme písmeno A, jelikož se jedná o textovou hodnotu, musíme ji napsat v uvozovkách. A kde písmeno A hledáme? Hledáme ho v této buňce (HLEDAT(“A”;B15). Funkci potvrdíme.
Pokud do buňky napíšeme produkt A100, tak funkce vrátí jedničku. To znamená, že se písmeno A nachází na první pozici v textu. Pokud napíšeme 100A, tak funkce vrátí čtyřku, jelikož teď se písmeno A nachází na čtvrté pozici v textu. Pokud tedy chceme, aby produkty začínaly písmenem A, tak to znamená, že se celá funkce HLEDAT musí rovnat jedničce (HLEDAT(“A”;B15)=1). To ale není všechno. Celou funkci musíme ještě zabalit do funkce IFERROR, pro případ, že by produkt začínal jiným písmenem. A co chceme vrátit v případě, že bude produkt začínat jiným písmenem? Chceme vrátit nepravdu.
V dalším příkladu posuneme příklad o trochu dále a potřebujeme zároveň omezit, aby produktové označení začínalo písmenem A, navíc bude mít celkem pouze 4 znaky. Pro vyřešení tohoto úkolu nám pomohou dvě textové funkce ZLEVA a DÉLKA. Jelikož musí obě podmínky platit zároveň, tak musíme použít logickou funkci A. Vedle tedy napíšeme funkci A, kde jako první podmínka bude, zda je první písmeno v buňce A. Zde můžete klidně použít i předchozí řešení s funkcí HLEDAT, která by se rovnala jedničce. Tentokrát to vyřešíme ale s funkcí ZLEVA. Napíšeme tedy funkci ZLEVA, kde označíme buňku a ověříme, že první pozice v buňce je písmeno A v uvozovkách (ZLEVA(B17)=”A”.
Následuje středník a druhé pravidlo, kde musíme ověřit, že celková délka textu v buňce bude čtyři znaky. Použijeme tedy funkci DÉLKA, kde označíme buňku a rovná se číslu 4 (DÉLKA(B17)=4. Pravidlo máme hotové, ukončíme závorku u funkce A a funkci potvrdíme. Zkusíme do buňky vyplnit správný kód produktu a funkce vrací pravdu, zkusíme ale vyplnit nesprávný kód, třeba kód, který začíná písmenem A, ale má 5 znaků a funkce vrací nepravdu. Zkusíme ještě vyplnit kód, který začíná jiným písmenem a opět funkce vrací nepravdu. Tuto funkci teď můžete nakopírovat do pravidla Ověření dat.
V posledním příkladu na formát zkusíme omezit, že správné produktové označení je takové, které začíná písmenem A nebo B a má libovolný počet znaků. Víme, že v tomto případě budeme muset použít funkci NEBO, jelikož produkt může začínat buď písmenem A nebo B. Než ale začneme celou funkci kombinovat dohromady, tak si ukážeme základ jejího řešení. Jako všechny příklady v Excelu, i tento příklad můžete vyřešit pomocí několika různých funkcí. Můžete zde využít předchozí řešení s funkcí HLEDAT. Pro ukázku toho, jak kreativně ale můžete využít nejrůznější funkce, zkusíme příklad vyřešit pomocí funkce COUNTIF.
Napíšeme funkci COUNTIF, kde nejprve označujeme buňky, ve kterých hledáme kritérium, označíme tedy vybranou buňku (B19). Následuje kritérium. Hledáme písmeno A, které musí být v uvozovkách. Za písmenem A může následovat libovolný počet znaků. K tomu využijeme wildcards neboli zástupný znak hvězdičku. O zástupných znacích máme na webu Akademie Excelu ještě video určené pro členy s praktickými příklady využití zástupných znaků a jak použít zástupné znaky a podmíněný formát.
Písmeno A spojíme s ampersandem a hvězdičkou v uvozovkách (COUNTIF(B19;”A”&”*”). Ukončíme závorku. To je základem celá naší funkce. Teď tuto funkci COUNTIF zabalíme do funkce NEBO. Tato první funkce COUNTIF je první podmínkou funkce NEBO, následuje středník a druhá funkce COUNTIF, která je úplně stejná, takže ji zkopírujeme, jediným rozdílem je to, že ve druhé podmínce hledáme písmeno B (COUNTIF(B19;”B”&”*”). Ukončíme závorku u funkce NEBO a funkci potvrdíme.
30 dnů od dnešního dne
V další buňce chceme omezit vyplňování datumových hodnot. Pravidlo je, že chceme, aby se do buňky mohly vyplnit pouze datumy ode dneška 30 dnů do historie. Tedy dnes mínus 30 dnů. Opět máme dvě podmínky, které musí být splněné naráz, takže začneme s funkcí A. Ve funkci A bude prvním pravidlem, že datum ve vybrané buňce musí být větší nebo rovno dnešnímu datu mínus 30 (B21>=DNES()). Tato část podmínky zajistí, že funkce DNES spočítá dnešní datum, odečte od něho 30 dnů a zkontroluje, zda vyplněné datum není náhodou větší než toto datum.
Následuje středník a druhá podmínka, kde ověříme, že vyplněné datum je zároveň menší nebo rovno dnešnímu datu (B21<=DNES()). Ukončíme závorky a funkci potvrdíme. Vyplníme dnešní datum a vše v pořádku, vyplníme datum zítřejší a vrací se nepravda. Zkusíme vyplnit datum, které spadá do povoleného limitu 30 dnů a funkce vrací pravdu. Zkusíme vyplnit datum, které je třeba 3 měsíce staré a funkce vrací nepravdu.
Pouze emailové adresy
V dalším příkladu chceme omezit, aby se do buňky B23 zapsala pouze emailová adresa. Jak to uděláme? Nejjednodušší je ověřit, zda text zapsaný v buňce obsahuje jednu věc, kterou mají všechny emailové adresy a tou je zavináč. Využijeme zde zase funkci HLEDAT. Tentokrát hledaná hodnota nemusí být jako první znak, ale musí se nacházet kdekoliv v textové hodnotě. Napíšeme tedy funkci HLEDAT, kde hledáme zavináč, jedná se o textovou hodnotu, takže v uvozovkách a hledáme ho v buňce B23 (HLEDAT(“@”;B23)). Funkci potvrdíme a napíšeme do buňky emailovou adresu. Funkce vrací číselnou pozici zavináče. Abychom funkci ale mohli vložit do ověření dat, tak musí být výsledkem funkce pravda nebo nepravda. Zabalíme celou funkci HLEDAT do funkce JE.ČISLO. Pokud funkce HLEDAT najde zavináč, vrátí číslo a funkce JE.ČISLO vrátí pravdu, pokud zavináč nenajde vrátí chybovou hlášku a funkce JE.ČISLO vrátí nepravdu.
Datumy bez víkendů
V dalším příkladu chceme opět omezit datumy, ale tentokrát chceme, aby se do buňky B25 nesměly zapsat víkendové datumy. Opět si nejprve ukážeme jádro funkce v buňce. K identifikaci dne můžeme využít datumovou funkci DENTÝDNE. Tato funkce z datumu pozná, o který den v týdnu se jednalo a vrátí pořadové číslo tohoto dne. Napíšeme tedy funkci DENTÝDNE, kde stačí označit buňku s datumem, následuje středník a ještě musíme vyplnit druhý parametr, kde musíme vybrat dvojku, jelikož v Čechách začíná týden pondělkem (DENTÝDNE(B25;2). Funkci potvrdíme a do buňky zkusíme napsat datum. Vrací se pořadové číslo dne v týdnu. Jak teď omezíme, že se nebude jednat o sobotu a neděli? Jednoduše řekneme funkci DENTÝDNE, že se nesmí rovnat číslu 6, což je sobota jako šestý den týdne.
Základ máme hotový. Máme dva víkendové dny, takže musíme použít funkci A. První funkci zabalíme do funkce A, napíšeme středník a funkci zkopírujeme a pouze změníme, že se druhá podmínka nesmí rovnat číslu sedm, což je neděle. Ukončíme závorku a funkci potvrdíme.
Text bez nadbytečných mezer
V posledním příkladu si ukážeme, jak zabránit tomu, aby nám někdo do buňky B27 vyplňoval textové hodnoty s nadbytečnými mezerami. Nadbytečné mezery mohou způsobit spoustu problémů, pokud následně vyplněné hodnoty používáte třeba pro vyhledávání. Toto pravidlo je jednoduché, pro identifikaci nadbytečných mezer se používá funkce PROČISTIT. Napíšeme tedy funkci PROČISTIT, kde označíme vybranou buňku (PROČISTIT(B27)). Vyplníme text s nadbytečnou mezerou a funkce PROČISTIT vrací text bez nadbytečné mezery, tedy pročištěný. Teď stačí porovnat hodnotu ve vybrané buňce s touto funkcí.
Pokud v buňce bude text bez nadbytečných mezer, tak se obě strany rovnice budou rovnat a výsledkem bude pravda, pokud ovšem buňka bude obsahovat nadbytečné mezery, tak se rovnice rovnat nebude a vrátí se nepravda. Toto pravidlo teď můžeme vložit do Ověření dat.
Jedna odpověď
👍👍