Funkcia SUMIF v Exceli a súčet podľa viacerých podmienok

Excel je neuveriteľne funkčný program. Dokonca aj vstavaná sada funkcií stačí na dokončenie takmer akejkoľvek úlohy. A okrem tých štandardných, pre mnohých známych, existujú aj také, o ktorých počul len málokto. Ale zároveň neprestávajú byť užitočné. Majú užšiu špecializáciu a nie vždy je o nich núdza. Ale ak o nich viete, v kritickom momente môžu byť veľmi užitočné.

Dnes budeme hovoriť o jednej z takýchto funkcií − SUMMESLIMN.

Ak používateľ stojí pred úlohou sčítať niekoľko hodnôt so zameraním na určité kritériá, potom je potrebné použiť funkciu SUMMESLIMN. Vzorec, ktorý používa túto funkciu, berie tieto podmienky ako argumenty, potom sčítava hodnoty, ktoré ich spĺňajú, a potom sa nájdená hodnota zadá do bunky, v ktorej je zapísaná. 

Podrobný popis funkcie SUMIFS

Pred zvážením funkcie SUMMESLIMN, musíte najprv pochopiť, aká je jeho jednoduchšia verzia – SUMMESLI, pretože práve na ňom je založená funkcia, o ktorej uvažujeme. Každý z nás už pravdepodobne pozná dve často používané funkcie – SUM (vykonáva sčítanie hodnôt) a AK (testuje hodnotu podľa špecifikovanej podmienky).

Ak ich skombinujete, získate ďalšiu funkciu – SUMMESLI, ktorý kontroluje údaje podľa kritérií zadaných používateľom a sčítava len tie čísla, ktoré tieto kritériá spĺňajú. Ak hovoríme o anglickej verzii Excelu, potom sa táto funkcia nazýva SUMIF. Jednoducho povedané, názov -jazyk je priamym prekladom anglického názvu. Túto funkciu možno použiť na rôzne účely. Môže sa použiť najmä ako alternatíva VPR, teda napíšte

Hlavný rozdiel medzi funkciou SUMMESLIMN  z bežnej funkcie SUMMESLI je, že sa používa viacero kritérií. Jeho syntax je na prvý pohľad pomerne zložitá, no pri bližšom skúmaní sa ukazuje, že logika tejto funkcie je veľmi jednoduchá. Najprv je potrebné vybrať rozsah, v ktorom sa budú údaje kontrolovať, a potom nastaviť podmienky dodržiavania, s ktorými sa bude analýza vykonávať. A takáto operácia môže byť vykonaná pre pomerne veľký počet podmienok.

Samotná syntax je:

SUMIFS(rozsah_sumu; rozsah_stavy1; podmienka1; [rozsah_stavy2; podmienka2]; …)

Na vhodné miesta je potrebné umiestniť polia buniek vhodné v konkrétnom prípade. 

Pozrime sa na argumenty podrobnejšie:

1. Rozsah_sumu. Tento argument, ako aj rozsah podmienky 1 a podmienky 1, sú povinné. Je to súbor buniek, ktoré je potrebné sčítať.
2. Stav_rozsah1. Toto je rozsah, v ktorom sa bude kontrolovať stav. Je spárovaný s ďalším argumentom – Podmienka1. Súčet hodnôt zodpovedajúcich kritériu sa vykonáva v bunkách špecifikovaných v predchádzajúcom argumente.
3. Podmienka 1. Tento argument špecifikuje kritériá, podľa ktorých sa má kontrolovať. Dá sa nastaviť napríklad takto: „> 32“.
4. Rozsah podmienok 2, Podmienka 2... Tu sú nasledujúce podmienky nastavené rovnakým spôsobom. Ak je potrebné zadať viac ako niekoľko podmienok, pridajú sa argumenty Rozsah podmienok 3 a Podmienka 3. Syntax je rovnaká pre nasledujúce argumenty.

Funkcia umožňuje maximálne spracovanie až 127 párov podmienok a rozsahov. 

Môžete ho použiť v niekoľkých oblastiach naraz (uvedieme len niektoré, zoznam je v skutočnosti ešte dlhší):

1. účtovníctvo. Napríklad je dobré použiť funkciu SUMMESLIMN na vytváranie súhrnných výkazov, napríklad štvrťročne pre výdavky nad určitú sumu. Alebo vytvorte správu o jednom produkte z určitej cenovej kategórie.
2. Manažment predaja. Aj tu môže byť funkcia veľmi užitočná. Stojíme napríklad pred úlohou spočítať len náklady na tovar, ktorý bol predaný určitému zákazníkovi v určitom čase. A v takejto situácii funkcia SUMMESLIMN môže byť veľmi užitočné.
3. Vzdelávanie. Viac praktických príkladov z tejto oblasti si dnes uvedieme. Môžete ho použiť najmä na získanie súhrnu známok študentov. Môžete si vybrať pre jeden predmet alebo pre jednotlivé ročníky. Osoba môže okamžite nastaviť niekoľko kritérií, podľa ktorých bude hodnotenie vybrané, čo je veľmi pohodlné a môže ušetriť veľa času.

Ako vidíte, rozsah aplikácií pre túto funkciu je veľmi široký. Ale to nie je jeho jediná zásluha. Poďme sa pozrieť na niekoľko ďalších výhod, ktoré má táto funkcia:

1. Možnosť nastavenia viacerých kritérií. Prečo je to výhoda? Môžete použiť normálnu funkciu SUMMESLI! A to všetko preto, že je to pohodlné. Nie je potrebné vykonávať samostatné výpočty pre každé z kritérií. Všetky akcie je možné naprogramovať vopred, dokonca aj predtým. ako sa vytvorí tabuľka údajov. To je skvelá úspora času.
2. automatizácia. Moderná doba je dobou automatizácie. Len ten, kto vie svoju prácu poriadne zautomatizovať, môže veľa zarobiť. Preto schopnosť ovládať Excel a funkciu SUMMESLIMN je obzvlášť dôležité pre každého, kto si chce vybudovať kariéru. Znalosť jednej funkcie vám umožňuje vykonávať niekoľko akcií naraz, ako jednu. A tu prejdeme k ďalšej výhode tejto funkcie.
3. Úspora času. Už len vďaka tomu, že jedna funkcia vykonáva viacero úloh naraz.
4. Jednoduchosť. Napriek tomu, že syntax je na prvý pohľad dosť ťažká kvôli svojej objemnosti, v skutočnosti je logika tejto funkcie veľmi jednoduchá. Najprv sa vyberie rozsah údajov, potom rozsah hodnôt, pri ktorých sa skontroluje súlad s určitou podmienkou. A samozrejme treba špecifikovať aj samotný stav. A tak niekoľkokrát. V skutočnosti je táto funkcia založená iba na jednej logickej konštrukcii, čo ju robí jednoduchšou ako dobre známa VPR napriek tomu, že ho možno použiť na rovnaké účely aj s prihliadnutím na väčší počet kritérií. 

Vlastnosti používania funkcie SUMIFS

Existuje niekoľko funkcií používania tejto funkcie, ktorým musíte venovať pozornosť. V prvom rade táto funkcia ignoruje rozsahy s textovými reťazcami alebo hodnotami null, pretože tieto dátové typy nemožno sčítať v aritmetickom vzore, iba ich zreťaziť ako reťazce. Táto funkcia to nedokáže. Musíte tiež venovať pozornosť nasledujúcim podmienkam:

1. Tieto typy hodnôt môžete použiť ako podmienky na výber buniek na ďalšie pridávanie hodnôt v nich obsiahnutých: číselné hodnoty, booleovské výrazy, odkazy na bunky atď. 
2. Ak sa kontroluje text, logické výrazy alebo matematické znaky, potom sú takéto kritériá špecifikované v úvodzovkách.
3. Nie je možné použiť výrazy dlhšie ako 255 znakov.
4. Je možné použiť približné kritériá na výber hodnôt pomocou zástupných znakov. Otáznik sa používa na nahradenie jedného znaku a znak násobenia (hviezdička) je potrebný na nahradenie viacerých znakov. 
5. Booleovské hodnoty, ktoré sú v rozsahu súčtu, sa automaticky skonvertujú na číselné hodnoty podľa ich typu. Hodnota „TRUE“ sa teda zmení na jednu a „FALSE“ na nulu. 
6. Ak #HODNOTA! sa v bunke objaví chyba, znamená to, že počet buniek v rozsahu podmienky a súčtu je odlišný. Musíte sa uistiť, že veľkosti týchto argumentov sú rovnaké. 

Príklady použitia funkcie SUMIFS

Funkcie SUMMESLIMN nie je to také zložité, ako sa na prvý pohľad zdá. Pre lepšiu prehľadnosť sa však pozrime na niekoľko praktických príkladov, ako môžete funkciu použiť SUMMESLIMN. Oveľa ľahšie sa tak ponoríte do témy.

Dynamický rozsah súčtu podmienok

Začnime teda prvým príkladom. Povedzme, že máme tabuľku, ktorá obsahuje informácie o tom, ako žiaci zvládajú učivo v konkrétnom predmete. Existuje súbor známok, výkon sa hodnotí na 10-bodovej škále. Úlohou je nájsť známku na skúške tých študentov, ktorých priezvisko sa začína na písmeno A a ich minimálny počet bodov je 5.

Tabuľka vyzerá takto.

Aby sme mohli vypočítať celkové skóre na základe kritérií opísaných vyššie, musíme použiť nasledujúci vzorec.

Poďme si argumenty popísať podrobnejšie:

1. C3:C14 je náš sumárny rozsah. V našom prípade sa zhoduje s rozsahom podmienok. Z nej budú vybrané body použité na výpočet sumy, ale len tie, ktoré spadajú pod naše kritériá.
2. „>5“ je naša prvá podmienka.
3. B3:B14 je druhý rozsah súčtu, ktorý sa spracováva tak, aby zodpovedal druhému kritériu. Vidíme, že neexistuje žiadna zhoda so sumačným rozsahom. Z toho usudzujeme, že rozsah súčtu a rozsah podmienky môžu, ale nemusia byť totožné. 
4. „A*“ je druhý rozsah, ktorý určuje výber známok len pre tých študentov, ktorých priezvisko začína na A. V našom prípade hviezdička znamená ľubovoľný počet znakov. 

Po výpočtoch dostaneme nasledujúcu tabuľku.

Ako vidíte, vzorec zhrnul hodnoty na základe dynamického rozsahu a na základe podmienok určených používateľom.

Selektívny súčet podľa podmienky v Exceli

Teraz predpokladajme, že chceme získať informácie o tom, aký tovar bol odoslaný do ktorých krajín za posledný štvrťrok. Potom nájdite celkový príjem zo zásielok za júl a august.

Samotný stôl vyzerá takto. 

Na určenie konečného výsledku potrebujeme takýto vzorec.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Ako výsledok výpočtov vykonaných podľa tohto vzorca dostaneme nasledujúci výsledok.

Pozor! Tento vzorec vyzerá dosť veľký, aj keď sme použili iba dve kritériá. Ak je rozsah údajov rovnaký, môžete výrazne skrátiť dĺžku vzorca, ako je uvedené nižšie.

Funkcia SUMIFS na sčítanie hodnôt vo viacerých podmienkach

Teraz uveďme na ilustráciu ďalší príklad. V tomto prípade tabuľka zostáva rovnaká ako v predchádzajúcom prípade. 

Použijeme nasledujúci vzorec (zapíšeme ho však ako vzorec poľa, to znamená, že ho zadáme pomocou kombinácie kláves CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Po funkcii SUMMESLIMN spočíta pole hodnôt na základe kritérií špecifikovaných vo vzorci (t. j. krajiny Číny a Gruzínska), výsledné pole sa sčíta obvyklou funkciou SUM, ktorý je zapísaný ako vzorec poľa.

Ak boli podmienky odovzdané ako konštanta poľa pre viac ako jeden pár, vzorec poskytne nesprávny výsledok.

Teraz sa pozrime na tabuľku obsahujúcu súčty.

Ako vidíte, podarilo sa nám to. Určite uspejete aj vy. Veľký úspech v tejto oblasti. Ide o veľmi jednoduchú funkciu, ktorú pochopí aj človek, ktorý sa práve vydal na cestu učenia sa Excelu. A už vieme, že funkcia SUMMESLIMN vám umožní byť efektívny v akejkoľvek oblasti činnosti, od účtovníctva až po vzdelávanie. Aj keď si budujete kariéru v akejkoľvek inej oblasti, ktorá nebola popísaná vyššie, táto funkcia vám stále pomôže zarobiť peniaze. Preto je cenná.

A čo je najdôležitejšie, umožňuje vám ušetriť čas, ktorý je, žiaľ, obmedzeným zdrojom. Zdalo by sa, že na použitie dvoch funkcií je pár sekúnd, ale keď musíte vykonať veľké množstvo opakujúcich sa operácií, potom tieto sekundy pripočítajú hodiny, ktoré by ste mohli stráviť niečím iným. Preto vám odporúčame precvičiť si používanie tejto funkcie. Navyše je to neuveriteľne jednoduché.