Booleovské funkcie v Exceli. Všetko o aplikácii logických funkcií v Exceli

Excel je neuveriteľne funkčný program, ktorý umožňuje nielen zaznamenávať údaje v tabuľkovej forme, ale aj automatizovať ich spracovanie. Logické funkcie sú hlavným prvkom, ktorý vám umožňuje vykonávať akékoľvek operácie tohto druhu. Používajú sa vo vzorcoch a iných funkciách s cieľom zjednodušiť všetky operácie.

Sú určené na kontrolu, či hodnoty spĺňajú špecifikované kritériá. Ak takáto zhoda existuje, do bunky, kde je zapísaná, sa zapíše hodnota „TRUE“, v prípade nezrovnalosti – „FALSE“. Dnes budeme podrobnejšie zvážiť také otázky, ako je štruktúra logických funkcií, rozsah ich použitia.

Zoznam boolovských funkcií v Exceli

Existuje veľké množstvo logických funkcií, ale najčastejšie používané sú tieto:

1. TRUE
2. KLAMEŤ
3. IF
4. IFERROR
5. OR
6. И
7. NIE JE
8. EOSIBKA
9. ISBLANK

Všetky môžu byť použité na vytváranie zložitých štruktúr a špecifikovanie kritérií akejkoľvek zákazky. Takmer všetky tieto funkcie zahŕňajú odovzdávanie určitých parametrov. Jedinými výnimkami sú TRUE a FALSE, ktoré sa samy vracajú. Ako parametre sa často používajú čísla, text, odkazy na bunky, rozsahy atď. Poďme sa pozrieť na všetkých vyššie uvedených operátorov.

Operátory TRUE a FALSE

Obe tieto funkcie majú spoločné to, že vracajú iba jednu hodnotu. Rozsah ich použitia je použitie ako súčasť iných funkcií. Ako je zrejmé z názvu operátorov, funkcií TRUE и KLAMEŤ návratové hodnoty TRUE и KLAMEŤ resp.

NIE operátor

Táto funkcia sa používa s jedným argumentom a do bunky zapíše opačnú hodnotu. Ak miniete tohto operátora TRUE, potom sa to vráti KLAMEŤ a teda opačné tvrdenie je pravdivé. Preto výsledok spracovania údajov týmto operátorom úplne závisí od toho, aké parametre mu odovzdať.

Syntax tohto operátora je nasledovná: =NOT(pravda alebo nepravda).

Operátory AND a OR

Tieto dva operátory sú potrebné na sprostredkovanie vzájomného vzťahu podmienok výrazu. Funkcia И sa používa na označenie, že dve kritériá sa musia zhodovať s rovnakým číslom alebo textom súčasne. Táto funkcia vráti hodnotu TRUE len za podmienky, že všetky kritériá vytvárajú túto hodnotu súčasne. Ak zlyhá aspoň jedno kritérium, celá sekvencia vráti hodnotu KLAMEŤ.

Spôsob zostavenia operátora AND je veľmi jednoduchý: =A(argument1; argument2; …). Maximálny počet argumentov, ktoré môže táto funkcia použiť, je 255. Syntax operátora OR podobné, ale mechanika práce je mierne odlišná. Ak jedna zo zoznamu funkcií poskytne výsledok TRUE, potom sa toto číslo vráti ako celá logická postupnosť.

príkazy IF a ISERROR

Tieto dve funkcie majú veľmi dôležitý účel – priamo nastavujú kritérium dodržiavania, s ktorým sa musí určitý výraz kontrolovať. Pre hlbšie pochopenie fungovania operátora IFERROR, musíte najprv opísať funkciu IF. Jeho všeobecná štruktúra je o niečo komplikovanejšia ako predchádzajúce: =AK(logický_výraz, hodnota_ak_pravda, hodnota_ak_nepravda).

Úlohou tohto operátora je vytvárať čo najzložitejšie konštrukcie. Kontroluje, či sú splnené kritériá. Ak áno, operátor sa vráti TRUE, Ak nie - KLAMEŤ. Ale operátor sa často používa v spojení s inými. Napríklad, ak sa používa ako argument funkcie NIE JE, potom bude súčet automaticky nahradený opačným. To znamená, že ak existuje zhoda s kritériom, hodnota sa vráti KLAMEŤ. Toto je hlavná výhoda logických funkcií: možno ich kombinovať v najbizarnejších formách.

Ďalej sa schéma stáva zložitejšou. Ak podľa tohto kritéria dostaneme výsledok „PRAVDA“, môžete zadať text, číslo, ktoré sa zobrazí, alebo funkciu, ktorá sa vypočíta. Podobne môžete nastaviť výsledok, ktorý sa zobrazí, ak sa výsledok po spracovaní údajov vrátil. KLAMEŤ.

Štruktúra operátora IFERROR dosť podobné, no predsa trochu iné. Obsahuje dva povinné argumenty:

1. Význam. Testuje sa samotný výraz. Ak sa ukáže, že je to pravda, potom sa táto hodnota vráti.
2. Hodnota v prípade chyby. Toto je text, číslo alebo funkcia, ktorá sa zobrazí alebo vykoná, ak bol výsledok kontroly prvého argumentu FALSE.

syntaxe: =IFERROR(hodnota;hodnota_pri_chybe).

Operátori ISERROW a ISEMPLAND

Prvá z vyššie uvedených funkcií obsahuje iba jednu hodnotu a má nasledujúcu syntax: =ISERROR(hodnota). Úlohou tohto operátora je kontrolovať, ako dobre sú bunky naplnené (jedna alebo v celom rozsahu). Ak sa ukáže, že výplň bola nesprávna, vráti skutočný výsledok. Ak je všetko dobré - falošné. Môže sa použiť priamo ako kritérium pre inú funkciu.

Excel dokáže skontrolovať odkazy na nasledujúce typy chýb:

• #NÁZOV?;
• #N/A;
• #DEL/0!;
• #ČÍSLO!;
• #SO;
• #PRÁZDNY!;
• #LINK!.

Funkcie ISBLANK Celkovo je to neuveriteľne jednoduché. Obsahuje iba jeden parameter, ktorým je bunka/rozsah, ktorý sa má kontrolovať. Ak existuje bunka, ktorá neobsahuje ani text, ani čísla, ani netlačiteľné znaky, vráti sa výsledok TRUE. Ak teda existujú údaje vo všetkých bunkách rozsahu, používateľ dostane výsledok KLAMEŤ.

Tabuľka poznámok „Logické funkcie v Exceli“

Aby sme zhrnuli všetko popísané vyššie, uveďme malú tabuľku obsahujúcu informácie o všetkých bežne používaných logických funkciách.

Logické funkcie a príklady riešenia problémov

Logické funkcie umožňujú riešiť rôzne úlohy, vrátane zložitých. Uveďme niekoľko príkladov, ako fungujú v praxi.

Úloha 1. Predpokladajme, že nám po určitom predajnom čase zostane časť tovaru. Treba ho prehodnotiť podľa nasledujúcich pravidiel: ak sa ho nepodarilo predať do 8 mesiacov, jeho cenu vydeľte 2-násobkom. Najprv vytvorte rozsah, ktorý popisuje počiatočné údaje. Vyzerá to takto.

Aby bola opísaná úloha úspešne vyriešená, musíte použiť nasledujúcu funkciu.

Môžete to vidieť v riadku vzorcov na snímke obrazovky. Teraz si urobme nejaké objasnenia. Logický výraz, ktorý bol zobrazený na snímke obrazovky (tj C2>=8), znamená, že produkt musí byť na sklade až 8 mesiacov vrátane. Pomocou aritmetických operátorov >= definujeme pravidlo väčšie alebo rovné. Po napísaní tejto podmienky funkcia vráti jednu z dvoch hodnôt: „TRUE“ alebo „FALSE“. Ak vzorec spĺňa kritérium, potom sa hodnota po precenení zapíše do bunky (dobre alebo sa odovzdá ako argument inej funkcii, všetko závisí od parametrov nastavených používateľom), vydelená dvoma (na tento účel sme rozdelili cenu v čase prijatia na sklad o dve) . Ak sa potom zistí, že produkt bol na sklade menej ako 8 mesiacov, vráti sa rovnaká hodnota, aká je obsiahnutá v bunke.

Teraz si úlohu sťažíme. Uplatňujeme podmienku: rozsah zliav musí byť progresívny. Jednoducho povedané, ak tovar leží dlhšie ako 5 mesiacov, ale menej ako 8, cena by sa mala vydeliť jeden a pol krát. Ak je viac ako 8, dve. Aby sa tento vzorec zhodoval s hodnotou, musí byť nasledujúci. Pozrite sa na snímku obrazovky v riadku vzorcov, aby ste to videli.

Dôležité! Ako argumenty je dovolené používať nielen číselné, ale aj textové hodnoty. Preto je prípustné stanoviť kritériá najrôznejšieho poradia. Napríklad urobiť zľavu na tovar prijatý v januári a nerobiť to, ak prišiel v apríli.

Úloha 2. Aplikujme toto kritérium na produkt, ktorý je na sklade. Predpokladajme, že ak sa po znížení vykonanom vyššie jeho hodnota znížila na menej ako 300 rubľov alebo ak sa nepredával dlhšie ako 10 mesiacov, potom sa jednoducho odstráni z predaja. Vzorec je nasledujúci.

Poďme si to rozobrať. Ako kritérium sme použili funkciu OR. Je potrebné poskytnúť takúto vidličku. Ak bunka D2 obsahuje číslo 10, potom sa v príslušnom riadku stĺpca E automaticky zobrazí hodnota „odpísaná“. To isté platí aj pre druhú podmienku. Ak nie je splnená žiadna z nich, potom sa jednoducho vráti prázdna bunka.

Úloha 3. Povedzme, že máme vzorku študentov, ktorí sa snažia dostať na strednú školu. Aby to urobili, musia zložiť skúšky z niekoľkých predmetov, ktoré sú zobrazené na obrázku nižšie. Aby boli považovaní za spôsobilých na prijatie do tejto vzdelávacej inštitúcie, musia získať spolu 12 bodov. Zároveň je dôležitou podmienkou, že skóre v matematike by nemalo byť menšie ako 4 body. Úlohou je automatizovať spracovanie týchto údajov, ako aj zostaviť prehľad o tom, ktorí žiaci zadali a ktorí nie. Aby sme to urobili, urobíme takú tabuľku.

Našou úlohou je teda prinútiť program, aby vypočítal, koľko bude celkovo bodov, pozrel sa na výsledok a porovnal. Po týchto operáciách musí funkcia vložiť výsledok do bunky, do ktorej sa zmestí. Existujú dve možnosti: „akceptované“ alebo „nie“. Ak chcete implementovať túto úlohu, zadajte podobný vzorec (stačí zadať svoje hodnoty): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

S boolovskou funkciou И môžeme overiť, či sú splnené dve podmienky naraz. V tomto prípade sme použili funkciu SUM na výpočet celkového skóre. Ako prvú podmienku (v prvom argumente funkcie AND) sme zadali vzorec B3>=4. Tento stĺpec obsahuje skóre z matematiky, ktoré by nemalo byť nižšie ako 4 body.

Vidíme široké uplatnenie funkcie IF pri práci s tabuľkami. Preto je to najobľúbenejšia logická funkcia, ktorú musíte poznať ako prvú.

Pred použitím týchto zručností v skutočnej práci sa dôrazne odporúča precvičiť si na testovacej tabuľke. Pomôže to ušetriť veľa času.

Úloha 4. Stojíme pred úlohou určiť celkové náklady na tovar po znížení ceny. Požiadavka – cena produktu musí byť vyššia alebo priemerná. Pri nesplnení tejto podmienky je potrebné tovar odpísať. V tomto príklade uvidíme, ako funguje množstvo aritmetických a štatistických funkcií.

Využime tabuľku, ktorú sme už nakreslili. Pre vyriešenie tohto problému je potrebné ako podmienku stanoviť pravidlo, že bunka D2 musí byť menšia ako aritmetický priemer celého sortimentu tovaru. Ak je pravidlo potvrdené, v bunke, kde je tento vzorec napísaný, sa nastaví hodnota „odpísané“. Ak kritérium nie je splnené, nastaví sa prázdna hodnota. Na vrátenie aritmetického priemeru existuje funkcia PRIEMERNÁ.

Úloha 5. Predpokladajme, že potrebujeme vypočítať priemerný predaj rôznych produktov v rôznych obchodoch tej istej značky. Urobme si takú tabuľku.

Našou úlohou je určiť priemer pre všetky hodnoty, ktorý vyhovuje určitým charakteristikám. Na to používame špeciálnu funkciu, ktorá nebola v zozname vyššie. Umožňuje kombinovať dve funkcie PRIEMERNÁ и IF. A zavolala BEZ SRDCE. Obsahuje tri argumenty:

1. Rozsah na kontrolu.
2. Stav, ktorý sa má skontrolovať.
3. Priemerovanie rozsahu.

V dôsledku toho sa získa nasledujúci vzorec (na snímke obrazovky).

Vidíme, že rozsah použitia logických funkcií je jednoducho obrovský. A ich zoznam je v skutočnosti oveľa väčší, ako je opísané vyššie. Uviedli sme len najobľúbenejšie z nich, ale opísali sme aj príklad ďalšej funkcie, ktorá je kombináciou štatistickej a logickej. Existujú aj iné podobné hybridy, ktoré si zaslúžia osobitnú pozornosť.