Funkcia IF v Exceli. Príklady (s viacerými podmienkami)

Jednou z hlavných výhod excelovských tabuliek je možnosť naprogramovať funkčnosť konkrétneho dokumentu. Ako väčšina ľudí vie zo školských hodín informatiky, jednou z hlavných súčastí, ktorá vám to umožní uviesť do praxe, sú logické operátory. Jedným z nich je operátor IF, ktorý zabezpečuje vykonávanie určitých akcií pri splnení určitých podmienok. 

Ak sa napríklad hodnota zhoduje s určitou hodnotou, v bunke sa zobrazí jeden štítok. Ak nie, je to iné. Pozrime sa na tento účinný nástroj podrobnejšie v praxi.

Funkcia IF v Exceli (všeobecné informácie)

Každý program, aj keď je malý, nevyhnutne obsahuje postupnosť akcií, ktorá sa nazýva algoritmus. Môže to vyzerať takto:

1. Skontrolujte celý stĺpec A, či neobsahuje párne čísla.
2. Ak sa nájde párne číslo, pridajte také a také hodnoty.
3. Ak sa nenájde párne číslo, zobrazte nápis „nenájdené“.
4. Skontrolujte, či je výsledné číslo párne. 
5. Ak áno, pridajte ho ku všetkým párnym číslam vybraným v odseku 1.

A aj keď ide len o hypotetickú situáciu, ktorá v reálnom živote pravdepodobne nebude potrebná, vykonanie akejkoľvek úlohy nevyhnutne znamená prítomnosť podobného algoritmu. Pred použitím funkcie AK, musíte mať v hlave jasnú predstavu o tom, aký výsledok chcete dosiahnuť. 

Syntax funkcie IF s jednou podmienkou

Akákoľvek funkcia v Exceli sa vykonáva pomocou vzorca. Vzor, podľa ktorého musia byť údaje odovzdané funkcii, sa nazýva syntax. V prípade operátora IF, vzorec bude v tomto formáte.

=IF (logický_výraz, hodnota_ak_pravda, hodnota_ak_nepravda)

Pozrime sa na syntax podrobnejšie:

1. Booleovský výraz. Ide o samotnú podmienku, ktorej splnenie či nedodržanie Excel kontroluje. Je možné kontrolovať číselné aj textové informácie.
2. Hodnota_ak_pravda. Výsledok, ktorý sa zobrazí v bunke, ak kontrolované údaje spĺňajú zadané kritériá.
3. hodnota_ak_nepravda. Výsledok, ktorý sa zobrazí v bunke, ak kontrolované údaje nezodpovedajú podmienke.

Tu je príklad pre prehľadnosť.

Tu funkcia porovnáva bunku A1 s číslom 20. Toto je prvý odsek syntaxe. Ak je obsah väčší ako táto hodnota, v bunke, kde bol vzorec napísaný, sa zobrazí hodnota „väčšia ako 20“. Ak situácia nezodpovedá tejto podmienke – „menej alebo rovná 20“.

Ak chcete v bunke zobraziť textovú hodnotu, musíte ju uzavrieť do úvodzoviek.

Tu je iná situácia. Aby boli študenti oprávnení zúčastniť sa na skúške, musia absolvovať testovacie sedenie. Študentom sa podarilo získať kredity zo všetkých predmetov a teraz zostáva posledný, ktorý sa ukázal ako rozhodujúci. Našou úlohou je určiť, ktorí zo študentov sú na skúšky prijatí a ktorí nie.

Keďže chceme kontrolovať text a nie číslo, prvým argumentom je B2=”cons.”.

Syntax funkcie IF s viacerými podmienkami

Na porovnanie hodnoty často nestačí jedno kritérium. Ak potrebujete zvážiť viac ako jednu možnosť, môžete funkcie vnoriť IF jeden do druhého. Bude tam niekoľko vnorených funkcií.

Aby to bolo jasnejšie, tu je syntax.

=AK(logický_výraz, hodnota_ak_pravda, AK(logický_výraz, hodnota_ak_pravda, hodnota_ak_nepravda))

V tomto prípade funkcia skontroluje dve kritériá naraz. Ak je prvá podmienka pravdivá, vráti sa hodnota získaná ako výsledok operácie v prvom argumente. Ak nie, skontroluje sa súlad druhého kritéria.

Tu je príklad.

A pomocou takého vzorca (zobrazeného na obrázku nižšie) môžete analyzovať výkon každého študenta.

Ako vidíte, pribudla tu ešte jedna podmienka, ale princíp sa nezmenil. Môžete teda skontrolovať niekoľko kritérií naraz.

Ako rozšíriť funkčnosť IF pomocou operátorov AND a OR

Z času na čas nastane situácia, že je potrebné okamžite skontrolovať súlad s niekoľkými kritériami a nepoužívať logické vnorené operátory, ako v predchádzajúcom príklade. Ak to chcete urobiť, použite buď funkciu И alebo funkciu OR podľa toho, či potrebujete splniť viacero kritérií naraz alebo aspoň jedno z nich. Pozrime sa bližšie na tieto kritériá.

Funkcia IF s podmienkou AND

Niekedy potrebujete skontrolovať výraz pre viacero podmienok naraz. Na to sa používa funkcia AND, zapísaná v prvom argumente funkcie IF. Funguje to takto: ak sa a rovná jednej a a rovná sa 2, hodnota bude c.

Funkcia IF s podmienkou „ALEBO“.

Funkcia OR funguje podobným spôsobom, ale v tomto prípade je splnená iba jedna z podmienok. Maximálne je možné takto skontrolovať až 30 podmienok. 

Tu je niekoľko spôsobov použitia funkcií И и OR ako argument funkcie IF.

Porovnanie údajov v dvoch tabuľkách

Z času na čas je možné porovnať dve podobné tabuľky. Napríklad človek pracuje ako účtovník a potrebuje porovnať dve zostavy. Existujú aj ďalšie podobné úlohy, napríklad porovnávanie nákladov na tovar rôznych šarží, potom hodnotenia študentov za rôzne obdobia atď.

Na porovnanie dvoch tabuliek použite funkciu COUNTIF. Pozrime sa na to podrobnejšie.

Povedzme, že máme dve tabuľky obsahujúce špecifikácie dvoch kuchynských robotov. A musíme ich porovnať a zvýrazniť rozdiely farbou. Dá sa to urobiť pomocou podmieneného formátovania a funkcie COUNTIF. 

Náš stôl vyzerá takto.

Vyberáme sortiment zodpovedajúci technickým vlastnostiam prvého kuchynského robota.

Potom kliknite na nasledujúce ponuky: Podmienené formátovanie – vytvorte pravidlo – použite vzorec na určenie formátovaných buniek.

Vo forme vzorca na formátovanie napíšeme funkciu =COUNTIF (rozsah na porovnanie; prvá bunka prvej tabuľky)=0. Tabuľka s vlastnosťami druhého kuchynského robota slúži ako porovnávacia rada.

Musíte sa uistiť, že adresy sú absolútne (so znakom dolára pred názvami riadkov a stĺpcov). Za vzorec pridajte =0, aby Excel hľadal presné hodnoty.

Potom musíte nastaviť formátovanie buniek. Ak to chcete urobiť, vedľa vzorky musíte kliknúť na tlačidlo „Formátovať“. V našom prípade používame výplň, pretože je na tento účel najvhodnejšia. Môžete si však vybrať ľubovoľné formátovanie.

Ako rozsah sme priradili názov stĺpca. Je to oveľa pohodlnejšie ako manuálne zadávanie rozsahu.

Funkcia SUMIF v Exceli

Teraz prejdime k funkciám IF, čo pomôže nahradiť dva body algoritmu naraz. Prvým je SUMMESLEY, ktorý sčíta dve čísla, ktoré spĺňajú určitú podmienku. Stojíme napríklad pred úlohou určiť, koľko peňazí by sa malo vyplácať mesačne všetkým predajcom. Na to je potrebné.

1. Pridajte riadok s celkovým príjmom všetkých predajcov a po zadaní vzorca kliknite na bunku, ktorá bude obsahovať výsledok. 
2. Nájdeme tlačidlo fx, ktoré sa nachádza vedľa riadku pre vzorce. Ďalej sa zobrazí okno, v ktorom pomocou vyhľadávania nájdete potrebnú funkciu. Po výbere operátora musíte kliknúť na tlačidlo „OK“. Manuálne zadávanie je však vždy možné.
   

   

3. Ďalej sa zobrazí okno na zadanie argumentov funkcie. Všetky hodnoty je možné zadať v príslušných poliach a rozsah je možné zadať pomocou tlačidla vedľa nich.
   

   

4. Prvým argumentom je rozsah. Tu zadáte bunky, ktorých súlad s kritériami chcete skontrolovať. Ak hovoríme o nás, ide o pozície zamestnancov. Zadajte rozsah D4:D18. Alebo jednoducho vyberte bunky, ktoré vás zaujímajú.
5. Do poľa „Kritériá“ zadajte pozíciu. V našom prípade – „predajca“. Ako súčtový rozsah uvádzame tie bunky, v ktorých sú uvedené platy zamestnancov (to sa robí manuálne aj výberom myši). Kliknite na „OK“ a získame hotové vypočítané mzdy všetkých zamestnancov, ktorí sú predajcovia.

Súhlaste s tým, že je to veľmi pohodlné. Nieje to?

Funkcia SUMIFS v Exceli

Táto funkcia vám umožňuje určiť súčet hodnôt, ktoré spĺňajú viaceré podmienky. Dostali sme napríklad za úlohu určiť celkový plat všetkých manažérov pracujúcich v južnej pobočke firmy.

Pridajte riadok, kde bude konečný výsledok, a vložte vzorec do požadovanej bunky. Ak to chcete urobiť, kliknite na ikonu funkcie. Zobrazí sa okno, v ktorom musíte nájsť funkciu SUMMESLIMN. Potom ho vyberte zo zoznamu a otvorí sa známe okno s argumentmi. Ale počet týchto argumentov je teraz iný. Tento vzorec umožňuje použiť nekonečné množstvo kritérií, ale minimálny počet argumentov je päť. 

V dialógovom okne na zadávanie argumentov je možné zadať iba päť. Ak potrebujete viac kritérií, budete ich musieť zadať ručne podľa rovnakej logiky ako prvé dve.

Pozrime sa na hlavné argumenty podrobnejšie:

1. Rozsah súčtu. Bunky, ktoré sa majú sčítať.
2. Rozsah podmienok 1 – rozsah, v ktorom sa bude kontrolovať súlad s určitým kritériom. 
3. Podmienka 1 je samotná podmienka.
4. Rozsah kritérií 2 je druhý rozsah, ktorý sa bude porovnávať s kritériom.
5. Podmienka 2 je druhá podmienka.

Ďalšia logika je podobná. V dôsledku toho sme určili mzdy všetkým manažérom južnej pobočky.

Funkcia COUNTIF v Exceli

Ak potrebujete určiť, koľko buniek spadá pod určité kritérium, použite funkciu COUNTIF. Povedzme, že musíme pochopiť, koľko predajcov pracuje v tejto organizácii:

1. Najprv pridajte riadok obsahujúci počet predajcov. Potom musíte kliknúť na bunku, kde sa zobrazí výsledok.
2. Potom musíte kliknúť na tlačidlo „Vložiť funkciu“, ktoré nájdete na karte „Vzorce“. Zobrazí sa okno so zoznamom kategórií. Musíme vybrať položku „Úplný abecedný zoznam“. V zozname nás zaujíma vzorec COUNTIF. Keď ho vyberieme, musíme kliknúť na tlačidlo „OK“.
   

   

3. Potom máme počet predajcov zamestnaných v tejto organizácii. Získalo sa spočítaním počtu buniek, v ktorých je napísané slovo „predajca“. Všetko je jednoduché. 

Funkcia COUNTSLIM v Exceli

Podobne ako vo vzorci SUMMESLIMN, tento vzorec počíta počet buniek, ktoré zodpovedajú viacerým podmienkam. Syntax je podobná, ale mierne odlišná od vzorca SUMMESLIMN:

1. Rozsah podmienok 1. Toto je rozsah, ktorý sa bude testovať podľa prvého kritéria.
2. Podmienka 1. Priamo prvé kritérium.
3. Rozsah podmienok 2. Toto je rozsah, ktorý sa bude testovať podľa druhého kritéria. 
4. Podmienka 2.
5. Podmienky dosahu 3.

A tak ďalej.

Takže funkcia IF v Exceli nie je jediný, existuje niekoľko ďalších jeho odrôd, ktoré automaticky vykonávajú najbežnejšie akcie, čo výrazne zjednodušuje život človeka. 

Z veľkej časti kvôli funkcii IF Excelové tabuľky sa považujú za programovateľné. Je to viac než len obyčajná kalkulačka. Ak sa nad tým zamyslíte, tak funkcia IF je základným kameňom akéhokoľvek programovania.

Ak sa teda naučíte pracovať s veľkým množstvom údajov v Exceli, bude oveľa jednoduchšie naučiť sa programovať. Vďaka logickým operátorom majú tieto oblasti naozaj veľa spoločného, ​​hoci Excel častejšie využívajú účtovníci. Mechanizmus práce s údajmi je však do značnej miery rovnaký. 

Funkcia v správnych rukách IF a jeho variácie vám umožňujú premeniť hárok programu Excel na plnohodnotný program, ktorý dokáže pracovať so zložitými algoritmami. Pochopenie, ako funkcia funguje IF je prvým krokom k učeniu sa makier – ďalším krokom k flexibilnejšej práci s tabuľkami. Ale toto je už profesionálnejšia úroveň.