Funkce SUMIF v Excelu a součet podle více podmínek

Excel je neuvěřitelně funkční program. Dokonce i vestavěná sada funkcí stačí k dokončení téměř jakéhokoli úkolu. A kromě těch standardních, pro mnohé známé, existují i ​​takové, o kterých málokdo slyšel. Ale zároveň nepřestávají být užitečné. Mají užší specializaci a ne vždy je o ně nouze. Ale pokud o nich víte, pak v kritickém okamžiku mohou být velmi užitečné.

Dnes budeme hovořit o jedné z takových funkcí − SUMMESLIMN.

Pokud je uživatel postaven před úkol sečíst několik hodnot se zaměřením na určitá kritéria, je nutné použít funkci SUMMESLIMN. Vzorec, který používá tuto funkci, bere tyto podmínky jako argumenty, pak sečte hodnoty, které je splňují, a pak se nalezená hodnota zadá do buňky, ve které je zapsána. 

Podrobný popis funkce SUMIFS

Před zvážením funkce SUMMESLIMN, musíte nejprve pochopit, jaká je jeho jednodušší verze – SUMMESLI, protože právě na něm je funkce, o které uvažujeme, založena. Každý z nás pravděpodobně již zná dvě často používané funkce – SOUČET (provádí sčítání hodnot) a POKUD (testuje hodnotu proti zadané podmínce).

Pokud je zkombinujete, získáte další funkci – SUMMESLI, který kontroluje data podle kritérií zadaných uživatelem a sečte pouze ta čísla, která tato kritéria splňují. Pokud mluvíme o anglické verzi Excelu, pak se tato funkce nazývá SUMIF. Jednoduše řečeno, jméno -language je přímým překladem názvu v angličtině. Tuto funkci lze použít pro různé účely. Zejména může být použit jako alternativa VPR, tedy napsat

Hlavní rozdíl mezi funkcí SUMMESLIMN  z běžné funkce SUMMESLI je, že se používá několik kritérií. Její syntaxe je na první pohled poměrně složitá, ale při bližším zkoumání se ukazuje, že logika této funkce je velmi jednoduchá. Nejprve je třeba vybrat rozsah, ve kterém budou data kontrolována, a poté nastavit podmínky pro dodržení, se kterými bude analýza provedena. A taková operace může být provedena pro poměrně velké množství podmínek.

Samotná syntaxe je:

SUMIFS(rozsah_součtu; rozsah_podmínek1; podmínka1; [rozsah_stavy2; podmínka2]; …)

Na vhodná místa je nutné umístit pole buněk vhodných v konkrétním případě. 

Podívejme se na argumenty podrobněji:

1. Rozsah_součtu. Tento argument, stejně jako rozsah podmínky 1 a podmínky 1, je povinný. Je to soubor buněk, které je třeba sečíst.
2. Condition_range1. Toto je rozsah, kde bude podmínka kontrolována. Je spárován s dalším argumentem – Podmínka1. Sčítání hodnot odpovídajících kritériu se provádí v buňkách uvedených v předchozím argumentu.
3. Podmínka1. Tento argument specifikuje kritéria, která mají být kontrolována. Lze jej nastavit například takto: „> 32“.
4. Rozsah podmínek 2, Podmínka 2… Zde jsou následující podmínky nastaveny stejným způsobem. Pokud je třeba zadat více než několik podmínek, jsou přidány argumenty Rozsah podmínky 3 a Podmínka 3. Syntaxe je stejná pro následující argumenty.

Funkce umožňuje maximální zpracování až 127 párů podmínek a rozsahů. 

Můžete jej použít v několika oblastech najednou (uvedeme jen některé, seznam je ve skutečnosti ještě delší):

1. Účetnictví. Například je dobré použít funkci SUMMESLIMN vytvářet souhrnné zprávy, například po čtvrtletích pro útratu nad určitou částku. Nebo vytvořte zprávu o jednom produktu z určité cenové kategorie.
2. Vedení prodeje. I zde může být funkce velmi užitečná. Stojíme například před úkolem sečíst pouze náklady na zboží, které bylo v určitou dobu prodáno určitému zákazníkovi. A v takové situaci funkce SUMMESLIMN může být velmi užitečné.
3. Vzdělání. Více praktických příkladů z této oblasti si dnes uvedeme. Zejména jej můžete použít k získání souhrnu hodnocení studentů. Můžete si vybrat pro jeden předmět nebo pro jednotlivé ročníky. Osoba může okamžitě nastavit několik kritérií, podle kterých bude hodnocení vybráno, což je velmi pohodlné a může ušetřit spoustu času.

Jak vidíte, škála aplikací pro tuto funkci je velmi široká. Ale to není jeho jediná zásluha. Pojďme se podívat na několik dalších výhod, které tato funkce má:

1. Možnost nastavení více kritérií. Proč je to výhoda? Můžete použít normální funkci SUMMESLI! A to vše proto, že je to pohodlné. Není třeba provádět samostatné výpočty pro každé z kritérií. Všechny akce lze naprogramovat předem, dokonce i předem. jak bude datová tabulka vytvořena. To je skvělá úspora času.
2. Automatizace. Moderní doba je dobou automatizace. Jen ten, kdo umí svou práci správně automatizovat, může vydělat hodně. Proto schopnost ovládat Excel a funkci SUMMESLIMN je zvláště důležité pro každého, kdo si chce vybudovat kariéru. Znalost jedné funkce vám umožňuje provádět několik akcí najednou, jako jednu. A zde přejdeme k další výhodě této funkce.
3. Úspora času. Už jen díky tomu, že jedna funkce plní více úkolů najednou.
4. Jednoduchost. Navzdory tomu, že syntaxe je na první pohled svou objemností poměrně těžká, ve skutečnosti je logika této funkce velmi jednoduchá. Nejprve se vybere rozsah dat, poté rozsah hodnot, u kterých se bude kontrolovat splnění určité podmínky. A samozřejmě musí být upřesněna i samotná podmínka. A tak několikrát. Ve skutečnosti je tato funkce založena pouze na jedné logické konstrukci, díky čemuž je jednodušší než dobře známá VPR a to i přesto, že jej lze použít pro stejné účely, a to is přihlédnutím k většímu počtu kritérií. 

Vlastnosti použití funkce SUMIFS

Existuje několik funkcí používání této funkce, kterým musíte věnovat pozornost. Za prvé, tato funkce ignoruje rozsahy s textovými řetězci nebo hodnotami null, protože tyto datové typy nelze sčítat v aritmetickém vzoru, pouze je zřetězit jako řetězce. Tato funkce to neumí. Musíte také věnovat pozornost následujícím podmínkám:

1. Tyto typy hodnot můžete použít jako podmínky pro výběr buněk k dalšímu přidání hodnot v nich obsažených: číselné hodnoty, booleovské výrazy, odkazy na buňky a tak dále. 
2. Pokud se kontroluje text, logické výrazy nebo matematické znaky, pak jsou tato kritéria specifikována pomocí uvozovek.
3. Nelze použít výrazy delší než 255 znaků.
4. Je možné použít přibližná kritéria pro výběr hodnot pomocí zástupných znaků. Otazník se používá k nahrazení jednoho znaku a znak násobení (hvězdička) je potřeba k nahrazení více znaků. 
5. Booleovské hodnoty, které jsou v rozsahu součtu, jsou automaticky převedeny na číselné hodnoty podle jejich typu. Hodnota „TRUE“ se tedy změní na jedničku a „FALSE“ – na nulu. 
6. Pokud #HODNOTA! se v buňce objeví chyba, znamená to, že počet buněk v rozsahu podmínky a součtu je jiný. Musíte se ujistit, že velikosti těchto argumentů jsou stejné. 

Příklady použití funkce SUMIFS

funkce SUMMESLIMN není to tak složité, jak se na první pohled zdá. Pro větší názornost se ale podívejme na praktické příklady, jak můžete funkci využít SUMMESLIMN. Díky tomu bude mnohem snazší ponořit se do tématu.

Dynamický rozsah součtu podmínek

Začněme tedy prvním příkladem. Řekněme, že máme tabulku, která obsahuje informace o tom, jak se žáci vyrovnávají s učivem v konkrétním předmětu. Existuje soubor známek, výkon se hodnotí na 10bodové škále. Úkolem je najít známku ze zkoušky těch studentů, jejichž příjmení začíná písmenem A a jejich minimální skóre je 5.

Tabulka vypadá takto.

Abychom mohli vypočítat celkové skóre na základě výše popsaných kritérií, musíme použít následující vzorec.

Pojďme si argumenty popsat podrobněji:

1. C3:C14 je náš souhrnný rozsah. V našem případě se shoduje s rozsahem podmínek. Z něj budou vybrány body použité pro výpočet částky, ale pouze ty, které spadají pod naše kritéria.
2. „>5“ je naše první podmínka.
3. B3:B14 je druhý rozsah součtu, který je zpracován tak, aby odpovídal druhému kritériu. Vidíme, že s rozsahem součtu není žádná shoda. Z toho usuzujeme, že rozsah součtu a rozsah podmínky mohou, ale nemusí být totožné. 
4. „A*“ je druhý rozsah, který určuje výběr známek pouze pro ty studenty, jejichž příjmení začíná na A. V našem případě hvězdička znamená libovolný počet znaků. 

Po výpočtech dostaneme následující tabulku.

Jak vidíte, vzorec shrnul hodnoty na základě dynamického rozsahu a na základě podmínek určených uživatelem.

Selektivní sčítání podle podmínky v Excelu

Nyní předpokládejme, že chceme získat informace o tom, jaké zboží bylo odesláno do kterých zemí během posledního čtvrtletí. Poté zjistěte celkové tržby ze zásilek za červenec a srpen.

Samotný stůl vypadá takto. 

K určení konečného výsledku potřebujeme takový vzorec.

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

V důsledku výpočtů provedených podle tohoto vzorce dostaneme následující výsledek.

Pozor! Tento vzorec vypadá docela velký, i když jsme použili pouze dvě kritéria. Pokud je rozsah dat stejný, můžete výrazně zkrátit délku vzorce, jak je znázorněno níže.

Funkce SUMIFS k součtu hodnot za více podmínek

Nyní uveďme pro ilustraci další příklad. V tomto případě zůstává tabulka stejná jako v předchozím případě. 

Použijeme následující vzorec (ten však zapíšeme jako maticový vzorec, to znamená, že jej zadáme pomocí kombinace kláves CTRL + SHIFT + ENTER).

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

Po funkci SUMMESLIMN sečte pole hodnot na základě kritérií uvedených ve vzorci (tj. země Čína a Gruzie), výsledné pole se sečte obvyklou funkcí SOUČET, který je zapsán jako maticový vzorec.

Pokud byly podmínky předány jako konstanta pole pro více než jeden pár, vzorec poskytne nesprávný výsledek.

Nyní se podívejme na tabulku obsahující součty.

Jak vidíte, uspěli jsme. Určitě uspějete i vy. Velký úspěch v této oblasti. Jedná se o velmi jednoduchou funkci, kterou pochopí i člověk, který právě vkročil na cestu učení Excelu. A už víme, že funkce SUMMESLIMN vám umožní být efektivní v jakékoli oblasti činnosti, od účetnictví až po vzdělávání. I když si budujete kariéru v jakékoli jiné oblasti, která nebyla popsána výše, tato funkce vám stále pomůže vydělat peníze. Proto je cenná.

A co je nejdůležitější, umožňuje vám ušetřit čas, který je bohužel omezený zdroj. Zdálo by se, že na použití dvou funkcí je pár sekund, ale když musíte provádět obrovské množství opakujících se operací, pak tyto sekundy dávají dohromady hodiny, které by se daly strávit něčím jiným. Proto vám doporučujeme, abyste si tuto funkci procvičili. Navíc je to neuvěřitelně jednoduché.