Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce-matematicke » SUMIFS-SUMIFS-soucet-dle-podminek-Excel
Microsoft Excel logo

SUMIFS (SUMIFS) - součet dle podmínek v Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak sčítat hodnoty v tabulce splňující zadané podmínky - zadanou podmínku Podmínky můžou být různého typu (číselné, textové, logické), ale i intervaly (číslo, datum), případně část textu (využívající speciální znaky).

Úvodem do funkce SUMIFS

Jak získat součet hodnot, které splňují požadovanou podmínku nebo požadované podmínky v Excel, aneb praktické využití funkce SUMIFS SUM - jako suma a IF jako podmínka a poslední S jako množné číslo - tedy i více podmínek (v angličtině je název funkce SUMIFS), jednak teorie, základní použití v listu, praktické příklady, možné komplikace, tipy, triky až po využití funkce ve VBA .

Pro přehlednost je článek rozdělen na kapitoly:


SUMIFS syntaxe a trocha teorie

Funkce sečte hodnoty v oblasti na základě zadaných podmínek (zadané podmínky). Například chci plat pro ženy, a pro osoby starší 20 let, v oddělení IT, atd.

Syntaxe česky:

SUMIFS(oblast_součtu;oblast_kritérií1;kritérium1;[oblast_kritérií2; kritéria2]; ..)

Syntaxe anglicky:

SUMIFS(oblast_součtu,oblast_kritérií1,kritérium1,[oblast_kritérií2, kritéria2]; ..)

Popis argumentů:

  • oblast_součtu - povinný argument - oblast ve které se sčítají buňky, pokud vyhovují kritériu v oblasti
  • oblast_kritérií1 - povinný argument - oblast testovaná pomocí argumentu kritéria1
  • kritérium1 - povinný argument - kritérium které platí pro oblast v argumentu oblast_kritérií1, kritéria se třeba dají zadat takto: 32, ">32", B4, ">"&32, "Pavel", "*Pavel*", A42 atd.
  • oblast_kritérií2 - nepovinný argument - oblast testovaná pomocí argumentu kritéria2 (oblastí může být až 127)
  • kritéria2 - nepovinný argument - kritérium, které platí pro oblast v argumentu oblast_kritérií2 až po 127
Excel funkce SUMIFS - syntaxe argumenty

Použití operátoru

Ve funkcích SUMIFS, potažmo SUMIFSS, COUNTIF lze pro kritérium použít i logické operátory:

  • = - rovná se
  • < - menší než
  • > - větší než
  • <= - menší nebo rovno
  • >= - větší nebo rovno
  • <> - různé od zadané hodnoty

Použití zástupných znaků

Při hledání textu, lze naopak používat i zástupné znaky (podobně jako když hledáte v databázích, nebo ve filtrech). V Excelu se používá hvězdička a otazník.

  • * - zastupuje libovolný řetězec
  • ? - zastupuje právě jeden libovolný znak
  • ~ - znak za vlnovkou ztrácí speciální funkci, platí pro *, ? a ~ neboli pokud hledáte i tyto znaky (tak se bude hledat například hvězdička a ne jakékoliv znaky)

Poznámka

Z vlastní zkušenosti doporučuji používat primárně funkcí SUMIFS.

Další alternativy jak spočítat hodnoty, podle zadaných podmínek je možnost využít funkce jako SUBTOTAL, KDYŽ, databázové funkce, využít maticový zápis funkcí, vytvořit kontingenční tabulku, filtry, VBA. Odkazy na články jsou v poslední kapitole.

SUMIFS základ - vyplacená mzda

Máte tabulku zaměstnanců, kde jsou jejich jména, informace o pohlaví, zda je muž/žena, na kterém oddělení pracuje, jeho věk a jeho mzda. Potřebuji zjistit vyplacený plat - sumu vyplacených finančních prostředku pro muže a pro ženy.

Vyřešit lze jednoduše, pomocí funkce SUMIFS.

  • Vyplacený plat pro muže/ženy
=SUMIFS(F16:F28;D16:D28;"muž") =SUMIFS(F16:F28;D16:D28;"žena")

Nemusíte hledat jen podle pohlaví, lze i podle oddělení, atd.

Případně hledat podle více kritérií, plat vypalcený mužům v oddělení IT.

=SUMIFS(F16:F28;D16:D28;"muž";E16:E28;"IT") ... pokud se budu odkazovat do buněk =SUMIFS(F16:F28;D16:D28;E10;E16:E28;F10)

Nebo jen součet platu větších než 10 - podmínka je ve stejném sloupci, ve kterém se provádí součet

=SUMIFS(F16:F28;F16:F28;10) SUMIFS Excel - suma na základě podmínky ukázka

SUMIFSS - rozsah podmínek - operátory

Zdrojová tabulka (z prvního příkladu) je doplněna o sloupec věk (může být i datum narození atd.).

Využitím funkce SUMIFS zjistíme z tabulky:

  • Vyplacený plat pro osoby nad 35 let
  • Vyplacený plat pro ženy nad 35 let

Funkce budou vypadat:

=SUMIFS(G14:G24;D14:D24;"<35") =SUMIFS(G14:G24;D14:D24;E4) =SUMIFS(G14:G24;D14:D24;"<"&G4 =SUMIFS(G14:G24;D14:D24;F4&G4) =SUMIFS(G14:G24;D14:D24;CONCATENATE(F4;G4))

Mohu se odkázat na číslo a operátor mít ve funkci, nebo spojit operátor a funkci. Pro ženu jen přidám další podmínku a opět mohu mít kombinace s odkazem an buňku atd.

=SUMIFS(G14:G24;D14:D24;"<35";E14:E24;"žena")

Výsledek

SUMIFS - rozsah podmínek - operátory

Poznámka

Pokud máte k dispozici hodnotu a rozsah (<,>) a nechcete mít natrvalo vloženo, lze využít speciální znaky pro spojování & nebo funkci CONCATENATE.

SUMIFS - více podmínek - jeden sloupec

Více podmínek nad několika sloupci jsem popsal v předchozí kapitole, co ale když potřebuji více podmínek nad jedním sloupcem? Pokud se zamyslíte, zjistíte, že nad sloupcem se dájí provádět úkoly:

    >sčítat - chci sečíst různo hodnoty třeba určité divize
  • rozdíl - například data meti dvěma hodnotama

Sčítání

Začneme součtem například potřebuji sečíst obyvatele ČR a SR, a pak Německa a Rakouska, určitě vás napadne sečíst, nejprve spočítat např. ČR a pak přičíst SR, ale co když lze použít něco jiného. Zkusíte matici, ale ta nefunguje vezme jen první položku, ale ve spojení s SOUČIN.SKALÁRNÍ už funguje báječně. Ukáži nad tím že sečteme muže a ženy. Ufouni nás zajímat nebudou ;)

=SUMIFS(F16:F28;D16:D28;"muž")+SUMIFS(F16:F28;D16:D28;"žena") =SOUČIN.SKALÁRNÍ(SUMIFS(F16:F28;D16:D28;{"muž";"žena"})) =SOUČIN.SKALÁRNÍ(SUMIFS(F16:F28;D16:D28;E9:F9))

Pokud si chcete ověřit že maticový výpočet opravdu nefunguje (Ctrl + Shift + Enter):

{=SUMIFS(F16:F28;D16:D28;{"muž";"žena"})} SUMIFS - více podmínek podmínek

Rozdíl

Potřebujete zjisti rozdíl, například mzdu pro osoby starší než a mladší než, můžete využít opět dvě funkce jednu nad požadovaný věk a od tohoto počtu odečtete mzdy, které už toto hodnotu nesplňují, nebo vložit do jedné funkce větší a zároveň menší než.

Celkové vyplacené mzdy pro věk > než 24 a zároveň <= než 29

=SUMIFS(E14:E24;D14:D24;">24")-SUMIFS(E14:E24;D14:D24;">29") =SUMIFS(E14:E24;D14:D24;">24";D14:D24;"<=29")

Opět můžete doplnit o další podmínku (kromě věku) třeba ženy v daném věku.

=SUMIFS(E14:E24;D14:D24;">24"; D14:D24;"<=29";C14:C24;"žena")

Pozor! Na rozsahy!

=SUMIFS(E14:E24;D14:D24;">24";D14:D24;">29") SUMIFS - více podmínek podmínek nad sloupcem

SUMIFS - hledat text

Jak hledat slova, texty, ale nejen přesnou shodu, ale i slova obsahující část slova, například pro pro slovo "sto" lze najít jednak slovo sto, ale i stokoruna, korun sto, atd.

Budeme hledat jen slova, která neobsahují speciální znaky, aneb respektující, že do popisu názvu výrobku, jmen, atd., se nezadávají speciální znaky jako hvězdičky, otazníky a vlnovky.

Jak na tyto speciální znaky jsem popsal v následující kapitole, abych vás neochudil ;) o trik s vlnovkou ~ .

Začneme klasickým slovem - to už umíte.

=SUMIFS($E$16:$E$28;$D$16:$D$28;B5) =SUMIFS($E$16:$E$28;$D$16:$D$28;"koruna")

Samozřejmě mohu přes buňku a využívat konkrétní slovo, nebo slova obsahující hledáne slovo, nebo slovo a znak (několik znaků pevně danných) které mohou být různé. Pak je potřeba využít hvězdičku, otazník. hvězdička jakykoliv počet, otazník přesný počet, jeden otazník jeden znak.

=SUMIFS($E$16:$E$28;$D$16:$D$28;B7) =SUMIFS($E$16:$E$28;$D$16:$D$28;B8) =SUMIFS($E$16:$E$28;$D$16:$D$28;B9) =SUMIFS($E$16:$E$28;$D$16:$D$28;B10) =SUMIFS($E$16:$E$28;$D$16:$D$28;B11) =SUMIFS($E$16:$E$28;$D$16:$D$28;B12) =SUMIFS($E$16:$E$28;$D$16:$D$28;B13)

Hledané slovo může být i v buňce jen doplním hvězdičky.

=SUMIFS($E$17:$E$29;$D$17:$D$29;"*"&B8&"*") SUMIFS - texty

Poznámka, Excel v základu nezvládá regulární výrazy, ale ve VBA ano. Ale to již nechávám čtenáři k samo dostudování, nebo se zmíním v některém z dalších článků ;)

SUMIFS - speciální znaky

Jak najít, neboli spočítat sumu - součet pro buňky, které obsahují speciální znaky (*, ~, ?) jako hvězdička, vlnovka, otazník?

klasické znaky se hledají dobře, jen u dvou znaku je problém otazník a hvězdička, protože otazník představuje právě jeden hledaný znak a hvězdička několik znaků.

Pro zjištění daného znaku je potřeba použít znak vlnovku ~ a pak daný hledaný znak, například otazník, nebo hvězdička, případně i vlnovka. Další hvězdička, případně otazník opět představuje několik.

Funkce

funkce =SUMIFS(C12:C19;B12:B19;"*~**") =SUMIFS(C12:C19;B12:B19;"~*") =SUMIFS(C12:C19;B12:B19;"*~?*") =SUMIFS(C12:C19;B12:B19;"*~~*")

Ukázka

SUMIFS - speciální znaky

SUMIFS - datum a čas

Jak na zpracování součtu s datem a časem, například dle konkterního datumu, třeba dnešní den, dle konkrétního měsíce, roku, týdne...

Dnešní den, případně před/po dnešním dnu

=SUMIFS(F17:F25;E17:E25;DNES()) =SUMIFS(F17:F25;E17:E25;"<"&DNES())

Platy za konkrétní měsíc je vcelku sranda ;), pokud máte zadáno od kterého dne do kterého dne, ale pokud to chce člověk dynamicky (pro aktuální měsíc) tam už si trochu pohraje ;) Využije funkce jako DATUM, ROK, DNES; MĚSÍC; EOMONTH. Aneb to už začne člověka bavit, takové "Lego" pro dospělé.

"=SUMIFS(F17:F25;E17:E25;"">=""&DATUM(ROK(DNES()); MĚSÍC(DNES());1);E17:E25;""<""&EOMONTH(DNES();0))" "=SUMIFS(F17:F25;E17:E25;"">=""&DATUM(2018; 8;1);E17:E25;""<""&DATUM(2018;8;31))" "=SUMIFS(F17:F25;E17:E25;"">=""&""1.8.2018""; E17:E25;""<""&""31.8.2018"")" ... kontrola ... =SUMIFS(F18:F26;G18:G26;8)

Lze spočítat konkretní rok, posledních x dnů (třeba 7), konkretní/aktuální víkend, atd., hrátky s časem, budu-li chtít, případně něco zajímavého řešit, rád přidám další ukázky.

Ukázka

SUMIFS - speciální znaky

SUMIFS - pro více listů

Data máte na více listech například platy pracovníků za jednotlivé měsíce a v souhrnném listu seznam všech pracovníků a potřebujete součty platů za celé období (IT oddělení vám nechce vytáhnout z databáze, přes kontingenčku řešit nechcete, spravovat na jeden list také ne).

Proto ukáži jak propojit více se SUMIFS další funkce jako SOUČIN.SKALÁRNÍ a NEPŘÍMÝ.ODKAZ, pro jednoduchost jsem si přidal názvy listů do buněk, stejně tak bych mohl přidat i oblast. atd.

=SOUČIN.SKALÁRNÍ(SUMIFS(NEPŘÍMÝ.ODKAZ("'"&$D$15:$D$16&"'!"&"F7:F10");NEPŘÍMÝ.ODKAZ("'"&$D$15:$D$16&"'!"&"C7:C10");B19))

a pokud i oblasti jsou "natvrdo" v buňce.

=SOUČIN.SKALÁRNÍ(SUMIFS(NEPŘÍMÝ.ODKAZ("'"&$D$15:$D$16&"'!"&$F$15);NEPŘÍMÝ.ODKAZ("'"&$D$15:$D$16&"'!"&$F$16);B20))

Poznámky

NEPŘÍMÝ.ODKAZ odkaz dotáhne data, SUMIFS to spočte a jak už víte maticově to funkce SUIMIFS nedává tak použijeme SOUČIN.SKALÁRNÍ.

Ukázka

SUMIFS - s odkazy na jiné listy

SUMIFS - ve spojení s ostatními funkcemi

Podmínku poskládat využitím funkce CONCATENATE, znáte z předchozí kapitoly.

=SUMIFS(G14:G24;D14:D24;CONCATENATE(F4;G4))

Spojení dvou funkcí, kdy chci sečíst muže a ženy, případně mohu využít SOUČIN.SKALÁRNÍ.

=SUMIFS(F16:F28;D16:D28;"muž")+SUMIFS(F16:F28;D16:D28;"žena") =SOUČIN.SKALÁRNÍ(SUMIFS(F16:F28;D16:D28;{"muž";"žena"}))

Datum, mohu využít funkce pro dnešní den, nebo datum poskládat

=SUMIFS(D12:D17;DNES();G12:G17) =SUMIFS(D12:D17;DATUM(F7;E7;D7);G12:G17) .. případně ve spojení s operátory =SUMIFS(D12:D17;">="&DATUM(F7;E7;D7);G12:G17)

Další ukázky v přípravě

Chyby v SUMIFS

Oblasti nestejně velké

=SUMIFS(D5:D9;"muž";F5:F10) oblasti posunuty =SUMIFS(D5:D9;"muž";F6:F10)

v argumentu součet nejsou čísla

=SUMIFS(D5:D10;"muž";E5:E10)

sčítám logické hodnoty

=SUMIFS(D5:D10;"muž";G5:G10)

Problém je i v maticovém zápisu. Pokud chcete používat maticově použijte SUMA a KDYŽ nebo SOUČIN.SKALÁRNÍ.

{=SUMIFS(D9:D14;F4:F5;F9:F14)}

Další chyby v přípravě.

SUMIFS - chyby

Použití s funkce SUMIFS ve VBA

Při zadávání do buňky lze využít Formula nebo FormulaLocal.

Range("C12").Formula = "=SUMIFS(F5:F10,D5:D10,""muž"")" Range("C13").FormulaLocal = "=SUMIFS(F5:F10;D5:D10;""muž"")"

Nebo využít Application s WorksheetFunction.

hodnota = Application.WorksheetFunction.SumIfs( _ Range("F5:F10"), _ Range("D5:D10"), _ "muž") MsgBox (hodnota)

využít i pojmenování argumentu a lze i přehazovat ;)

hodnota = Application.WorksheetFunction.SumIfs( _ Arg2:=Range("D5:D10"), _ Arg3:="muž", _ Arg1:=Range("F5:F10")) MsgBox (hodnota) Excel funkce SUMIFS - ukázka VBA

Ke stažení

SUMIFS - funkce EXCEL - stahuj logo

Soubory ke stažení:

  • ukázky v přípravě

Další související články:

Další funkce, které souvisejí s funkci SUMIFS.

Závěrem

Využíváte funkci SUMIFS nebo máte stejně jako já raději SUMIFS? Máte nějakou zajímavou ukázku použití? Použijte komentáře a pochlubte se.

Článek byl aktualizován: 10.08.2018 08:47

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 3180

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.