Booleovské funkce v Excelu. Vše o aplikaci logických funkcí v Excelu

Excel je neuvěřitelně funkční program, který umožňuje nejen zaznamenávat data v tabulkové podobě, ale také automatizovat jejich zpracování. Logické funkce jsou hlavním prvkem, který umožňuje provádět jakékoli operace tohoto druhu. Používají se ve vzorcích a dalších funkcích za účelem zjednodušení všech operací.

Jsou navrženy tak, aby zkontrolovaly, zda hodnoty splňují zadaná kritéria. Pokud taková shoda existuje, je v buňce, kde je zapsána, uvedena hodnota „TRUE“, v případě nesrovnalosti – „FALSE“. Dnes se budeme podrobněji zabývat otázkami, jako je struktura logických funkcí, rozsah jejich použití.

Seznam booleovských funkcí v Excelu

Existuje velké množství logických funkcí, ale nejčastěji používané jsou následující:

1. TRUE
2. LEŽÍCÍ
3. IF
4. IFERROR
5. OR
6. И
7. NENÍ
8. EOSIBKA
9. ISBLANK

Všechny lze použít k vytvoření složitých struktur a specifikaci kritérií libovolné zakázky. Téměř všechny tyto funkce zahrnují předávání určitých parametrů. Jedinými výjimkami jsou TRUE a FALSE, které se samy vrátí. Jako parametry se často používají čísla, text, odkazy na buňky, rozsahy a tak dále. Pojďme se podívat na všechny výše uvedené operátory.

Operátory TRUE a FALSE

Obě tyto funkce mají společné to, že vracejí pouze jednu hodnotu. Rozsah jejich použití je použití jako součásti jiných funkcí. Jak je patrné z názvu operátorů, funkcí TRUE и LEŽÍCÍ návratové hodnoty TRUE и LEŽÍCÍ resp.

NE operátor

Tato funkce se používá s jedním argumentem a do buňky zapíše opačnou hodnotu. Pokud projdete tímto operátorem TRUE, pak se to vrátí LEŽÍCÍ a v souladu s tím platí opačné tvrzení. Výsledek zpracování dat tímto operátorem tedy zcela závisí na tom, jaké parametry mu předat.

Syntaxe tohoto operátoru je následující: =NOT(pravda nebo nepravda).

Operátory AND a OR

Tyto dva operátory jsou nezbytné k tomu, aby bylo možné vyjádřit vzájemný vztah podmínek výrazu. Funkce И se používá k označení, že dvě kritéria musí odpovídat stejnému číslu nebo textu současně. Tato funkce vrací hodnotu TRUE pouze za podmínky, že všechna kritéria vytvářejí tuto hodnotu současně. Pokud selže alespoň jedno kritérium, celá sekvence vrátí hodnotu LEŽÍCÍ.

Způsob, jakým je vytvořen operátor AND, je velmi jednoduchý: =A(argument1; argument2; …). Maximální počet argumentů, které může tato funkce použít, je 255. Syntaxe operátora OR podobné, ale mechanika práce se mírně liší. Pokud jedna ze seznamu funkcí poskytne výsledek TRUE, pak bude toto číslo vráceno jako celá logická posloupnost.

Příkazy IF a ISERROR

Tyto dvě funkce mají velmi důležitý účel – přímo nastavují kritérium splnění, se kterým musí být určitý výraz kontrolován. Pro hlubší pochopení toho, jak operátor pracuje IFERROR, musíte nejprve popsat funkci IF. Jeho obecná struktura je o něco složitější než předchozí: =IF(logický_výraz, hodnota_pokud_pravda, hodnota_pokud_nepravda).

Úkolem tohoto operátora je vytvářet co nejsložitější konstrukce. Kontroluje, zda jsou kritéria splněna. Pokud ano, operátor se vrátí TRUE, Pokud ne - LEŽÍCÍ. Ale operátor se často používá ve spojení s ostatními. Například pokud je použit jako argument funkce NENÍ, pak bude součet automaticky nahrazen opačným. To znamená, že pokud existuje shoda s kritériem, bude vrácena hodnota LEŽÍCÍ. To je hlavní výhoda logických funkcí: lze je kombinovat v nejbizarnějších formách.

Dále se schéma stává složitějším. Pokud tímto kritériem dostaneme výsledek „PRAVDA“, můžete zadat text, číslo, které se zobrazí, nebo funkci, která se bude počítat. Podobně lze nastavit výsledek, který se zobrazí, pokud byl výsledek vrácen po zpracování dat. LEŽÍCÍ.

Struktura operátora IFERROR dost podobné, ale přece jen trochu jiné. Obsahuje dva povinné argumenty:

1. Význam. Testuje se samotný výraz. Pokud se ukáže, že je pravdivá, vrátí se tato hodnota.
2. Hodnota v případě chyby. Toto je text, číslo nebo funkce, která se zobrazí nebo provede, pokud byl výsledek kontroly prvního argumentu NEPRAVDA.

Syntaxe: =IFERROR(hodnota;hodnota_pokud_chyba).

Operátoři ISERROW a ISEMPLAND

První výše uvedená funkce obsahuje pouze jednu hodnotu a má následující syntaxi: =ISERROR(hodnota). Úkolem tohoto operátoru je kontrolovat, jak dobře jsou buňky naplněny (jedna nebo v celém rozsahu). Pokud se ukáže, že výplň byla chybná, vrátí skutečný výsledek. Pokud je vše dobré - nepravda. Lze použít přímo jako kritérium pro jinou funkci.

Excel může zkontrolovat odkazy na následující typy chyb:

• #NÁZEV?;
• #N/A;
• #DEL/0!;
• #ČÍSLO!;
• #TAK;
• #PRÁZDNÝ!;
• #ODKAZ!.

funkce ISBLANK Celkově je to neuvěřitelně jednoduché. Obsahuje pouze jeden parametr, kterým je buňka/rozsah, který se má kontrolovat. Pokud existuje buňka, která neobsahuje ani text, ani čísla, ani netisknutelné znaky, vrátí se výsledek TRUE. Pokud jsou tedy data ve všech buňkách rozsahu, uživatel obdrží výsledek LEŽÍCÍ.

Memo tabulka „Logické funkce v Excelu“

Abychom vše výše popsané shrnuli, uveďme malou tabulku obsahující informace o všech běžně používaných logických funkcích.

Logické funkce a příklady řešení problémů

Logické funkce umožňují řešit různé úlohy, včetně složitých. Uveďme si pár příkladů, jak fungují v praxi.

Úkol 1. Předpokládejme, že nám po určité prodejní době zbyla část zboží. Musí být přehodnocena podle následujících pravidel: pokud nebylo možné jej prodat do 8 měsíců, vydělte jeho cenu 2krát. Nejprve vytvořte rozsah, který popisuje počáteční data. Vypadá to takto.

Aby byl popsaný úkol úspěšně vyřešen, je třeba použít následující funkci.

Můžete to vidět v řádku vzorců na snímku obrazovky. Nyní si udělejme několik vysvětlení. Logický výraz, který byl zobrazen na snímku obrazovky (tj. C2>=8), znamená, že produkt musí být skladem po dobu až 8 měsíců včetně. Pomocí aritmetických operátorů >= definujeme pravidlo větší nebo rovno. Poté, co tuto podmínku zapíšeme, funkce vrátí jednu ze dvou hodnot: „TRUE“ nebo „FALSE“. Pokud vzorec splňuje kritérium, je hodnota po přecenění zapsána do buňky (dobře nebo předána jako argument jiné funkci, vše závisí na parametrech nastavených uživatelem), dělená dvěma (pro to jsme rozdělili cena v době příjmu na sklad o dva) . Pokud se poté zjistí, že produkt byl na skladě méně než 8 měsíců, vrátí se stejná hodnota, která je obsažena v buňce.

Nyní si úkol ztížíme. Aplikujeme podmínku: rozsah slev musí být progresivní. Jednoduše řečeno, pokud zboží leží déle než 5 měsíců, ale méně než 8, cena by se měla vydělit jedenapůlnásobkem. Pokud je více než 8, dva. Aby tento vzorec odpovídal hodnotě, musí být následující. Podívejte se na snímek obrazovky v řádku vzorců, abyste to viděli.

Důležité! Jako argumenty je přípustné používat nejen číselné, ale i textové hodnoty. Proto je přípustné stanovit kritéria v nejrozmanitějším pořadí. Například udělat slevu na zboží přijaté v lednu a nedělat to, pokud dorazilo v dubnu.

Úkol 2. Aplikujme toto kritérium na produkt, který je skladem. Předpokládejme, že pokud se po výše provedené srážce její hodnota snížila na méně než 300 rublů nebo pokud byla bez prodeje déle než 10 měsíců, pak je jednoduše odstraněna z prodeje. Vzorec je následující.

Pojďme to analyzovat. Jako kritérium jsme použili funkci OR. Je potřeba zajistit takovou vidlici. Pokud buňka D2 obsahuje číslo 10, pak se v odpovídajícím řádku sloupce E automaticky zobrazí hodnota „odepsáno“. Totéž platí pro další podmínku. Pokud není splněna žádná z nich, pak se jednoduše vrátí prázdná buňka.

Úkol 3. Řekněme, že máme vzorek studentů, kteří se snaží dostat na střední školu. K tomu musí složit zkoušky z několika předmětů, jak je znázorněno na obrázku níže. Aby byli považováni za způsobilé pro přijetí do této vzdělávací instituce, musí získat celkem 12 bodů. Důležitou podmínkou zároveň je, že skóre v matematice by nemělo být nižší než 4 body. Úkolem je automatizovat zpracování těchto údajů a také sestavit zprávu o tom, kteří studenti zadali a kteří ne. K tomu si vyrobíme takovou tabulku.

Naším úkolem je tedy nechat program vypočítat, kolik bodů bude celkem, podívat se na výsledek a provést srovnání. Po těchto operacích musí funkce vložit výsledek do buňky, do které se vejde. Existují dvě možnosti: „přijímám“ nebo „ne“. Chcete-li tento úkol implementovat, zadejte podobný vzorec (stačí zadat hodnoty): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

S booleovskou funkcí И můžeme ověřit, že jsou splněny dvě podmínky najednou. V tomto případě jsme použili funkci SOUČET pro výpočet celkového skóre. Jako první podmínku (v prvním argumentu funkce AND) jsme zadali vzorec B3>=4. Tento sloupec obsahuje skóre z matematiky, které by nemělo být nižší než 4 body.

Vidíme široké uplatnění funkce IF při práci s tabulkami. Proto je to nejoblíbenější logická funkce, kterou musíte znát jako první.

Důrazně se doporučuje procvičit si na testovacím diagramu před použitím těchto dovedností v reálné práci. To pomůže ušetřit spoustu času.

Úkol 4. Stojíme před úkolem určit celkovou cenu zboží po přirážce. Požadavek – cena produktu musí být vyšší nebo průměrná. Při nesplnění této podmínky je nutné zboží odepsat. V tomto příkladu uvidíme, jak funguje spousta aritmetických a statistických funkcí.

Použijme tabulku, kterou jsme již nakreslili. Pro vyřešení tohoto problému je nutné jako podmínku stanovit pravidlo, že buňka D2 musí být menší než aritmetický průměr celého sortimentu zboží. Pokud je pravidlo potvrzeno, pak se v buňce, kde je tento vzorec zapsán, nastaví hodnota „odepsáno“. Pokud není kritérium splněno, je nastavena prázdná hodnota. Chcete-li vrátit aritmetický průměr, existuje funkce PRŮMĚRNÝ.

Úkol 5. Předpokládejme, že potřebujeme vypočítat průměrné prodeje různých produktů v různých obchodech stejné značky. Udělejme si takový stůl.

Naším úkolem je určit průměr pro všechny hodnoty, který vyhovuje určitým charakteristikám. K tomu používáme speciální funkci, která nebyla ve výše uvedeném seznamu. Umožňuje kombinovat dvě funkce PRŮMĚRNÝ и LI. A zavolala BEZCITNÝ. Obsahuje tři argumenty:

1. Rozsah ke kontrole.
2. Stav ke kontrole.
3. Průměrování rozsahu.

V důsledku toho se získá následující vzorec (na snímku obrazovky).

Vidíme, že rozsah použití logických funkcí je prostě obrovský. A jejich seznam je ve skutečnosti mnohem větší, než je popsáno výše. Uvedli jsme pouze nejoblíbenější z nich, ale také jsme popsali příklad další funkce, která je kombinací statistické a logické. Existují také další podobné hybridy, které si zaslouží samostatnou pozornost.