V dnešním videu zkombinuje několik technik, které jsme si představili v minulých videích, a to podmíněné formátování a částečnou shodu textu, tedy wildcards. Kombinaci podmíněného formátování a částečné shody textu si ukážeme na několika následujících praktických příkladech. Pokud si chcete práci v Excelu procvičit, tak si stáhněte cvičný excelový soubor a zkuste následující příklady nejprve vyřešit sami. Následně se podívejte na řešení v tomto videu.
Excelový soubor ke stažení
Podmíněné formátování na základě částečné shody textu
Úkol č. 1 – zadání
V prvním příkladu máme tabulku s označením čísel faktur a potřebujeme zjistit, zda máme u všech faktur správný formát označení faktury. Správný formát označení je takový, který začíná číslem jedna a končí písmenem U, přičemž celkový počet znaků je šest. Řádky s fakturami, které nevyhovují zadanému formátu potřebujeme označit červenou barvou.
Úkol č. 1 – řešení
K vyřešení tohoto příkladu můžeme použít funkci COUNTIF. Funkci si nejprve vytvoříme vedle tabulky, abychom ověřili, že správně počítá a když budeme s řešením spokojeni, tak ho zkopírujeme do podmíněného formátování. Začneme tedy s funkcí COUNTIF.
= COUNTIF(A4;”1????U”)=0
Ve funkci COUNTIF v parametru oblast označíme první buňku s fakturou (A4). Následuje kritérium, ve kterém použijeme zástupných znaků, neboli wildcards, jelikož hledáme částečnou shodu textu. Jediné, co víme je, že správné označení faktury začíná jedničkou, má celkem šest znaků a končí písmenem U. Takže jako kritérium napíšeme v uvozovkách 1, čtyři otazníky a písmeno U, ukončíme uvozovky, jelikož se jedná o textovou hodnotu (“1????U”).
Funkci potvrdíme a stáhneme ji dolů pro ostatní faktury. Na řádcích, kde se vyskytuje správný formát se objevila jednička, na ostatních nula. A přesně tento jednoduchý vzorec můžeme využít v podmíněném formátování. Vzorec z první buňky si zkopírujeme, označíme sloupec s fakturami a na kartě Domů vybereme Podmíněné formátování a zde v Pravidlech zvýraznění buněk vybereme Další pravidla a zde vybereme formátovat pomocí vzorce. Do příkazového řádku vložíme zkopírovaný vzorec a vybereme formát. Faktury, které nesplňují kritérium chceme obarvit na červeno. Potvrdíme. Teď se nám obarvily řádky, které kritérium splňují. Musíme se tedy k pravidlu vrátit a opravit ho. V podmíněném formátování vybereme Spravovat pravidla a pravidlo rozklikneme. Víme, že na řádcích, které pravidlo nesplňují vrací funkce COUNTIF nulu, takže stačí, abychom opravili vzorec a napsali, že COUNTIF se bude rovnat nule. Potvrdíme změnu a teď se obarvili řádky, které kritérium nesplňují.
Úkol č.2 – zadání
V dalším příkladu předchozí příklad trochu obměníme a tentokrát víme pouze to, že správná faktura začíná písmenem jedna a končí písmenem U, ale počet znaků může být libovolný. Opět chceme řádky, které nesplňují kritérium obarvit na červeno. Jak to vyřešíme? Opět můžete zkusit pauznout video a zkusit nejprve vyřešit příklad sami.
Úkol č. 2 – řešení
I k tomuto řešení můžeme využít funkci COUNTIF, jen trochu upravíme kritérium.
=COUNTIF(A15;”1*U”)=0
Začneme tedy s funkcí COUNTIF, kde jako oblast opět označíme první fakturu a v kritérium tentokrát využijeme zástupného znaku hvězdičky, který značí libovolný počet znaků. V uvozovkách tedy napíšeme 1, hvězdička a písmeno U, ukončíme uvozovky a závorku a funkci potvrdíme. Funkci stáhneme pro ostatní řádky a ověříme, že funkce počítá správně. Na rozdíl od předešlého příkladu se za správné označily i faktury, které mají méně znaků. Když jsme si jisti, že máme funkci COUNTIF správně, tak ji zkopírujeme, označíme sloupec s fakturami a vybereme Podmíněné formátování a formátovat pomocí vzorce. Do příkazového řádku vložíme funkci a opět chceme obarvit řádky, které zadání nesplňují, takže ještě k funkci dopíšeme rovná se nule. Vybereme formát, chceme řádek obarvit na červeno a potvrdíme. Úkol je vyřešen.
Úkol č. 3 – zadání
V dalším příkladu máme opět tabulku s číselným označením faktur a opět potřebujeme ověřit, zda jsou všechna číselná označení správně. Správné číselné označení je takové, které má 5 znaků, začíná číslem 1 nebo 3 a končí písmenem U. Řádky, které nesplňují správnost formátu chceme opět obarvit na červeno.
Úkol č. 3 – řešení
Pro řešení tohoto příkladu tentokrát nemůžeme použít funkci COUNTIF, jelikož pro správné označení faktury musí být splněné dvě podmínky, a to s logickou podmínkou NEBO. Buď bude faktura začínat číslem jedna nebo tři a končí písmenem U, počet znaků ve faktuře může být libovolný. Funkce COUNTIF, ale i její vylepšená verze COUNITFS, která si poradí s více podmínkami, obě pracují pouze s logickým vyjádřením A.
=NE(NEBO(JE.ČISLO(HLEDAT(“1*U”;A26));JE.ČISLO(HLEDAT(“3*U”;A26))))
Tyto funkce nám tedy tentokrát nepomohou. Jelikož ale máme dvě varianty označení faktur, faktura může buď začínat jedničkou nebo trojkou, tak se to zdá být jako příklad pro funkci NEBO. Vedle tabulky tedy začneme s touto funkcí a uvidíme, kam se dostaneme. Důvodem, proč si píši vzorce i mimo tabulku je to, že příkazový řádek v podmíněném formátování vám neposkytuje nápovědu ke struktuře funkcí, je tak o mnohem snazší si funkci napsat vedle tabulky, ověřit její správnost a následně ji nakopírovat do podmíněného formátování.
Ještě než ale použijeme funkci NEBO, tak si musíme stanovit postup, jakým způsobem ověříme, že číslo faktury splňuje naše kritéria. Pro tuto práci by se daly využít například textové funkce HLEDAT nebo NAJÍT. Obě funkce jsou schopné v rámci textové hodnoty najít jinou textovou hodnotu. Rozdíl mezi nimi je v tom, že funkce NAJÍT rozlišuje malá a velká písmena. My si nejsme stoprocentně jistí, že některá faktura nebude mít malé písmeno, takže raději použijeme funkci HLEDAT, která velikost písmen nerozlišuje. Důvod, proč nepoužijeme rovnou funkci NEBO a v ní nenapíšeme, že pokud se faktura rovná částečné shodě textu, nějak takto, je ten, že wildcards nefungují za znaménkem rovná se. Musíme tedy částečnou shodu textu zabalit do textové funkce HLEDAT.
Začneme tedy s funkcí HLEDAT. Ve funkci HLEDAT je jako první parametr funkce co, tedy co hledáme. Nejprve hledáme označení faktury, které začíná jedničkou, pak následuje libovolný počet znaků, tedy hvězdička a faktura končí písmenem U. Jelikož se jedná o text, tak musíme celý parametr uvést do uvozovek. Druhým parametrem funkce je kde, tedy kde hledáme první část funkce. Označíme tedy první fakturu. Konec závorky pro funkci HLEDAT a potvrdíme. Jelikož hned první faktura podmínku splňuje, tak funkce HLEDAT vrátila jedničku. U řádků, které nebudou splňovat podmínku vrátí funkce HLEDAT chybu. Základ toho, jak najdeme správné označení máme. Teď už to spojit jen s funkcí NEBO. Ve funkci NEBO budou dvěma parametry dvě funkce HLEDAT.
Před tuto první funkci HLEDAT, kterou již máme napsanou tak napíšeme NEBO, otevřeme závorku, jako prvním parametrem je tato první funkce HLEDAT a jako druhý parametr použijeme funkci HLEDAT, tentokrát ale hledáme fakturu která začíná trojkou, následuje libovolný počet znaků vyjádřený hvězdičkou, a faktura končí písmenem U, nezapomeneme na uvozovky a jako místo, kde hledáme označíme opět první buňku. Ukončíme závorku u funkce NEBO a potvrdíme. Vrátily se chybové hlášky.
Chybové hlášky se vrátily, protože funkce NEBO potřebuje, aby výsledkem jejích parametrů byly PRAVDA nebo NEPRAVDA. Kdežto funkce HLEDAT vrací jedničky nebo nuly. Tento problém vyřešíme tak, že funkci HLEDAT zabalíme do funkce JE.ČÍSLO. Tato jednoduchá funkce vrátí PRAVDU, pokud je výsledek číslo a NEPRAVDU pokud výsledek číslo není. To je pro naše řešení perfektní, jelikož pokud funkce HLEDAT najde shodu, tak vrátí jedničku. A pokud shodu nenajde, tak vrátí chybu, což technicky není číslo, takže i funkce JE.ČÍSLO vrátí nepravdu. Zabalíme tedy obě funkce HLEDAT do funkce JE.ČÍSLO. Potvrdíme a zkontrolujeme, zda to máme správně. Zkontrolujeme prvních několik faktur a zdá se že funkce funguje. Nicméně my víme, že chceme obarvit řádky, které kritérium nesplňují. Tedy přesně obráceně. Nejjednodušší způsob, jak pravdu obrátit za nepravdu a obráceně je, zabalit celou funkci NEBO do funkce NE. Tato funkce přehodí PRAVDU za NEPAVDU a obráceně. Teď můžeme funkci vzít, zkopírovat, označit sloupec s fakturami, otevřít podmíněné formátování pomocí vzorce a vzorec vložit do příkazového řádku, vybereme formát buněk a potvrdíme. Hotovo.
Úkol č. 4 – zadání
V posledním příkladu budeme v tabulce s produkty chtít označit zeleně řádky, jejichž produkt obsahuje slovo kalhoty. Nechceme ale označit pouze buňku, která obsahuje slovo kalhoty, ale celý řádek. Opět máte šanci pauznout video, než si ukážeme správný postup.
Úkol č.4 – řešení
Jak v tabulce vidíme, tak slovo kalhoty se nikdy v označení produktu nevyskytuje samo. Vždy je u něj nějaké přídavné jméno, jako dámské, dětské nebo pánské. Co víc, toto přídavné slovo je jednou před slovem kalhoty, jednou za slovem kalhoty a jednou je oddělené dokonce pomlčkou. Příklad zkusíme vyřešit pomocí funkce HLEDAT.
=JE.ČISLO(HLEDAT(“*”&$F$36&”*”;$A37))
Napíšeme funkci HLEDAT, v parametru co hledáme si musíme nejprve uvědomit, co hledáme. Hledáme slovo kalhoty, ale před slovem i za slovem může být libovolný počet znaků. Takže před i za slovem kalhoty musíme použít hvězdičku. Nemůžeme ovšem napsat jen slovo kalhoty mezi hvězdičkami, ale hvězdičky musí být v uvozovkách. Tedy uvozovky, hvězdička, uvozovky, následuje ampersand, označíme slovo kalhoty, které zafixujeme klávesou F4, znovu ampersand a hvězdička v dalších uvozovkách, středník a kde tuto hodnotu hledáme. Konec závorky. Víme, že funkci musíme zabalit do funkce JE.ČISLO, tak to rovnou uděláme. Potvrdíme a vrátila se nám série PRAVD a NEPRAVD, které můžeme použít v podmíněném formátování. Vzorec zkopírujeme, označíme celou tabulku, jelikož chceme označit celé řádky, najdeme podmíněné formátování a zde formátovat pomocí vzorce. Do příkazového řádku vložíme vzorec a vybereme formát. Nicméně proto, aby se označil celý řádek v tabulce musíme upravit fixaci buňky u buňky A37 na fixaci sloupce. Potvrdíme a máme obarvené řádky, které obsahují slovo kalhoty.
3 komentáře
První příklad jsem řešil zbytečně složitě přes funkci HLEDAT.
COUNTIF je mnohem elegantnější.
Pro vyřešení úkolu č.4 jsem použil tento vzorec:
=JE.ČISLO(HLEDAT(“kalhoty”;$A37))
Slovo kalhoty jsem zadal přímo do vzorce. Uvědomuji si, že pokud bych vyhledával více slov, bylo by lépe použít odkaz na buňku s textem.
Výsledkem funkce HLEDAT v řádku 40 je číslo 8.
Zabalením do funkce JE.ČISLO dostaneme výsledek PRAVDA.
Tak důležité je, že jste na řešení přišel sám 🙂 Já funkci HLEDAT využívám taky často, zvlášť u wildcards, tím se nedá nic zkazit. A druhé řešení je taky dobré, přesně jak jste řekl, když nehledáte několik různých položek, tak je občas lehčí napsat to jako vy, jako textovou hodnotu 😀 Paráda 🙂 moc děkuji za reakci 🙂
U třetího příkladu je uvedeno, že “pro řešení tohoto příkladu tentokrát nemůžeme použít funkci COUNTIF, jelikož pro správné označení faktury musí být splněné dvě podmínky, a to s logickou podmínkou NEBO. Buď bude faktura začínat číslem jedna nebo tři a končí písmenem U, počet znaků ve faktuře může být libovolný. Funkce COUNTIF, ale i její vylepšená verze COUNITFS, která si poradí s více podmínkami, obě pracují pouze s logickým vyjádřením A.”. Nicméně mně se podařilo buňky obarvit, když jsem v podmíněném formátování pomocí vzorce zadal =NEBO(COUNTIF(A26;”1*U”);COUNTIF(A26;”3*U”))=NEPRAVDA.