|
Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Jak sčítat hodnoty v tabulce splňující zadanou podmínku zadané podmínky. Podmínkou můžou být nejen hodnoty (číselné, textové), ale i intervaly (u hodnoty a číslo, datum), případně část(i) z textových hodnot.
Doplněno: 7.7.2015
Jak na sčítání hodnot, které odpovídají požadovaným podmínkám. Například auta, která jsou na plyn, vyplacená mzda ženám z oddělení PR, atd. Včetně využití dynamických výběru.
Pro přehlednost je článek rozdělen na jednotlivé kapitoly:
Sečte buňky ve vybrané oblasti podle zadaného kritéria.
Syntaxe
SUMIF(oblast;kritéria;součet)
Popis argumentů
Poznámka: SUMIF umožňuje vyhledávat i v nesouvislé oblasti, ale osobně si nedokáži představit praktické použití.
Sečte buňky ve vybrané oblasti podle zadaných kritérií.
Syntaxe
SUMIFS(oblast_součtu, oblast_kritérií1, kriterium1 [oblast_kritérií2, kriterium2]...)
Popis argumentů
Poznámka: Max 127 kritérií.
Ve funkcích SUMIF, SUMIFS lze pro kritérium použít i logické operátory:
Při hledání textu lze použít i zástupné znaky (podobně jako když hledáte v databázích). V Excelu se používá hvězdička a otazník.
Z vlastní zkušenosti doporučuji používat funkcí SUMIFS, protože suma součtu je u SUMIF na posledním místě a u SUMIFS na prvním místě, což se plete. A funkce SUMIFS dokáže spočítat sumu i pro podmínku z jedné oblasti.
Další alternativy jak spočítat hodnoty dle daných podmínek je možnost využít funkce SUBTOTAL a KDYŽ, případně vytvořit kontingenční tabulku.
Zdrojová tabulka z které budeme vybírat.
Máme tabulku zaměstnanců, kde jsou jejich jména, zda je muž/žena, na kterém oddělení pracuje a jeho mzda.
Použijeme funkce SUMIF a SUMIFS pro zjištění vyplacené mzdy (platu) pro muže:
=SUMIF(D16:D28;"muž";F16:F28)
=SUMIFS(F16:F28;D16:D28;"muž")
Podobně pro zjištění dalších informací:
=SUMIF(D16:D28;"žena";F16:F28)
=SUMIFS(F16:F28;D16:D28;"žena")
=SUMIF(E16:E28;"IT";F16:F28)
=SUMIFS(F16:F28;E16:E28;"IT")
Výsledek může vypadat například:
Soubor
SUMIF - vyplacená mzda
ke stažení zdarma (pro Excel 2007 a novější).
Předpokladem pro tento příklad je stejná zdrojová tabulka jako v předchozím příkladu.
Použijeme funkci SUMIFS pro zjištění vyplacené mzdy (platu) pro muže z oddělení IT a pro porovnání žen opět v oddělení IT:
=SUMIFS(F9:F21;E9:E21;"IT";D9:D21;"žena")
=SUMIFS(F9:F21;E9:E21;"IT";D9:D21;"muž")
Výsledek může vypadat například:
Soubor
SUMIFS dvě podmínky
ke stažení zdarma (pro Excel 2007 a novější).
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:
Funkce budou vypadat:
=SUMIFS(G9:G21;D9:D21;">48")
=SUMIFS(G9:G21;D9:D21;"<30")
Výsledek může vypadat například:
Pokud máte k dispozici hodnotu a rozsah (<,>) chcete mít natrvalo vložen, lze využít & nebo CONCATENATE. Předpoklad že v buňce A1 je požadované číslo (hodnota).
=SUMIFS(G9:G21;D9:D21;"<"&A1)
=SUMIFS(G9:G21;D9:D21;CONCATENATE("<";A1))
Soubor
SUMIFS - rozsah podmínek
ke stažení zdarma (pro Excel 2007 a novější).
Základy máte za sebou, teď si trochu s tabulkami "pohrajeme" ať požadovaný součet se pouze vybere z rozbalovacího menu.
Zdrojem bude tabulka s dopravním prostředkem, palivem a ziskem.
Cílem bude vybírat dle paliva a dopravního prostředku a Excel (SUMIFS) nám vypočte zisk.
Pro řešení je využito výběrového pole, funkce SVYHLEDAT a pro zajímavost jsem doplnil o podmíněné formátování.
=SUMIFS(E12:E33;C12:C33;O4;D12:D33;Q4)
Výsledek může vypadat například:
Soubor
SUMIFS dynamický výběr
ke stažení zdarma (pro Excel 2007 a novější).
Pokud bude zdrojová tabulka na jiném listě od Microsoft Excel 2007 již to Excel také zvládá. Jako zdrojovou tabulku použijeme úplně stejnou jako v předchozím příkladu (tj. Dopravní prostředky).
Stačí se jen odkazovat na daný list.
=SUMIFS(List1!E6:E27;List1!C6:C27;O3;List1!D6:D27;Q3)
Výsledek může vypadat například:
Soubor
SUMIFS - zdrojová tabulka na jiném listu
ke stažení zdarma (pro Excel 2007 a novější).
Tabulku lze doplnit o výběr listu. Například na jednotlivých listech můžete mít měsíční mzdy. A jen si vyberete měsíc a již se spočte vyplacená suma na daném listě
Jak najít spočítat sumu pro buňky, které obsahují speciální znaky (*, ~, ?) jako hvězdička, vlnovka, otazník?
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, nebo jeden znak (viz kapitola jak hledat texty).
=SUMIFS(C10:C17;B10:B17;"*~**")
=SUMIFS(C10:C17;B10:B17;"~*")
=SUMIFS(C10:C17;B10:B17;"*~?*")
=SUMIFS(C10:C17;B10:B17;"*~~*")
Soubor
SUMIFS - speciální znaky ke stažení zdarma (pro Excel 2007 a novější).
Jak vyfiltrovat rozdílovou oblast, například lidé s věkem vězším než 26 let a menším než 55 let.
Zatím není veřejně k dispozici. Bude součásti sbírky zajímavých šablon ke stažení, která je v přípravě.
Jak najít spočítat sumu v oblasti součtu, pro buňky, které obsahují speciální datum (čas)?
Soubor
SUMIFS - datum ke stažení zdarma (pro Excel 2007 a novější).
Potřebuji sumu za jednotlivé měsíce. Využitím SUMIFS mohu porovnávat datum od do. NEb oli od prvního dne v daném měsící do posledného dne v daném měsící (aneb využít funkci EOMONTH).
=SUMIFS($H$5:$H$15;$F$5:$F$15;">="&D19;$F$5:$F$15;"<="&EOMONTH(D19;0))
Soubory ke stažení:
Další funkce, které souvisejí s funkci SUMIF, SUMFS
Využíváte funkci SUMIFS? Máte nějakou zajímavou ukázku použití? Použijte komentáře a pochlubte se.
Článek byl aktualizován: 19.09.2020 10:57
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.
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ů.
Přidáno: 21.12.14 23:27
Myslím, že jediný důvod, proč existuje SUMIF (která neumí nic, co by neuměla i SUMIFS), je historická. Zatímco SUMIF byla už dříve, SUMIFS přišla až v roce 2007. Ještě bych doplnil že alternativní funkcí k SUMIFS je kromě zmíněných také DSUMA (byť s trochu jinou logikou zápisu). DSUMA má proti SUMIFS jednu obrovskou výhodu, že umožňuje, aby více kritérií mělo mezi sebou vztah "nebo" (nejenom "a"), což je někdy ou SUMIFSu dost omezující.
Přidáno: 22.12.14 12:49
To Jiří Beran: Děkuji za doplnění. DSUMA doplním, tu jsem nechtěně vynechal, mám ještě v plánu doplnit článek o pár dalších ukázek použití. Holt včera se mi už nechtělo :(
Přidáno: 22.12.14 22:55
Tak ono by se slušelo uvádět i ekvivalentní možnosti, nematicové i maticové - SUMA(KDYŽ...)), SUMA((...)*(...)*...), SOUČIN.SKALÁRNÍ(...). Jinak je pravda že tzv. "databázové" funkce Dxxxx nikdo moc nepoužívá. Sám na ně zapomínám.
Přidáno: 23.12.14 18:11
To Petr Pecháček: Díky za další nápady, doplním do příslušných článku o SUMA, SOUČIN.SKALÁRNÍ, KDYŽ.
Přidáno: 29.12.14 18:35
Ještě jedna drobnost. Funkce SUMIF přeci jen umí jednu věc, kteoru neumí SUMIFS. A sice používat nestejně dlouhé oblasti v oblasti kritérií a součtů. Ale nemyslím že by to byla vlastnost až tak klíčová :)
Přidáno: 03.01.15 09:22
To Jiří Beran: U použití nestejně dlouhých oblastí zatím nevidím praktické využití. Ale je pravda, že tuto "klíčovou" vlastnost jsem přehlédl.
Přidáno: 16.01.15 10:14
Existuje nějaká alternativa k funkci SUMIF, která by dokázala sumovat čísla z více sloupců? (Např. mám v jednotlivých sloupcích údaje po měsících a potřebuji výběry se součty za čtvrtletí a rok.)
Přidáno: 19.01.15 07:26
To Antonín Sedláček: -- buď použiť pomocné stĺpce so súčtami pre štvrťroky, alebo SUMIF+SUMIF+SUMIF pre tri stĺpce
Přidáno: 22.01.15 19:49
To Antonín Sedláček: Nelze využít kontingenčních tabulek?
Přidáno: 24.01.15 19:06
Děkuji za odpovědi. Vyzkouším.
Přidáno: 16.06.15 09:48
Prosím o radu, pravděpodobně s nastavením. Mám problém s fcemi SUMIF a SUMIFS (ale i s COUNTIF a COUNTIFS) v případě, že chci součet hodnot (počet hodnot) jiných než hodnota v konkrétní buňce. Např. vzorec =SUMIFS(A1:A50;B1:B50;">C1") nesečte buňky, které mají větší hodnotu než C1, ale snaží se sečíst buňky, které mají(!) hodnotu >C1. Jedná se konkrétní instalaci MS Excel 2010. Zkoušel jsem i obnovu nastavení národního prostředí.
Přidáno: 16.06.15 12:30
Beru zpět. Pomohl jsem si takto ...;">"&C1;... Díky
Přidáno: 02.07.15 15:03
Dobrý den pane Lašák, dovoluji se na Vás obrátit s dotazem ohledně funkce sumif. potřeboval bych pomocí této funkce sečíst předpis splátek úvěru v období od xy k dnešnímu datu, kdy pro určení dnešního data používám funkci DNES. Nemohl by jste mi prosím pomoci? S pozdravem Petr K.
Přidáno: 07.07.15 19:47
To Petr Kromíchal: Jak na datum ve funkcích SUMIFS, SUMIF jsem doplnil do článku.
Přidáno: 08.07.15 22:22
Děkuji mockrát, už jsem mi to podařilo také vypátrat. Hezký den.
Přidáno: 02.08.15 11:10
Dobrý den, jsem naprostý začátečník a laik a proto požádám o polopatickou odpověď. Ve cvičném zadání mám sečíst hodnoty ze tří různých listů (A,B,C), kde se v každém nachází buňka s hodnotou např. denní tržba - řekněme A1, B1, C1. Na list D mám provést do samostatné buňky součet do buňky D1. Můj (neúspěšný) postup je tento : do buňky D1 si nastavím tento vzorec =SUMIF('1.týden'!I5,+'2.týden'!I4,+'3.týden'!I4) ale vůbec nevím, co mám zadat do kritéria. Ať napíšu co napíšu (např. 1), vždy je výsledek, že napsaný vzorec obsahuje chybu. Tedy, co má kriterium vyjadřovat? Má mít nějakou přesně definovanou podobu a tvar? Děkuji
Přidáno: 12.08.15 11:36
Dobrý den, chci se zeptat, jestli existuje podobná funkce i pro násobení. Díky BJ
Přidáno: 17.10.15 16:45
To Petr Burda: Provést klasický součet =A!A1+=B!A1+=C!A1 PRo začátečníka doporučuji začít článkem Excel 2010 krok za krokem a pak pokračovat On-line kurz Excelu nebo si u mě objednat školení ;)
Přidáno: 17.10.15 16:57
To B Jarous: Existuje SOUČIN.SKALÁRNÍ více v samostatném článku: Funkce SOUČIN.SKALÁRNÍ (SUMPRODUCT) v Excel
Přidáno: 19.10.15 14:51
Dobrý den, chtěl bych se zeptat zda je možné sečíst jen čísla ve sloupci, pokud buňky obsahují i znak např.: S, 100.
Přidáno: 26.01.16 08:58
To Daniel Dědek: Záleží jak je hodnota vložena, pokud jako text, tak využít textové funkce ZPRAVA, ZLEVA, ČÁST a získat dané číslo. Pak již je možno sčítat. Pokud je hodnota vložena jako číslo s využití vlastního formátu, pak lze sčítat přímo.
Přidáno: 23.02.16 13:37
Dobrý den, zde SUMIF využít pro součet hodnot barevně označených? Například jen text označený červeně (nebo celé pole buňky)?
Přidáno: 25.02.16 10:44
mfg. Tabulka sloupce roky a řádky hodnoty podle kriterií min,nad,am....V jiné tabulce jsou jednotlivé bunky s =SVYHLEDAT(J$13;W$3:DC$1000;5;0)
, což mi vyhledává hodnotu za příslušný rok J$13. Všechny hodnoty v bunkách mám sečteny pomocí =SUMIF(A3:A78;"=min";C3:C78)
. Bylo by možné sjednotit vzorečky v jeden, abych měl součet podle kriteria roku a min.
Přidáno: 10.03.16 09:26
Zdravím, marně zatím přemýšlím, jak získat součet buněk na základě více podmínek a současně ve více sloupcích. Konkrétně - mám v v jednom řádku (výrobní výkaz) 10 lidí (10 sloupců, souvisle, OS. ČÍSLO), kteří se na výrobě podíleli a jejich PRÉMIE (10 sloupců, souvisle). Potřebuji udělat tabulku, kde v řádcích budou jedinečná os.čísla zaměstnanců, ve sloupcích kombinace DEN+SMĚNA (ranní, noční) a v hodnotě buňky pak bude nějaká SUMA PRÉMIÍ při současném splnění podmínek (OS.ČÍSLO;DEN;SMĚNA), přičemž OS.ČÍSLO a jeho PRÉMIE není vždy ve stejném z 10 sloupců... může být ve kterémkoliv z nich (např. u jednoho výrobku je pracovník 1111 uveden jako 6/10 a jeho prémie v(6/10), jindy může být v 2/10 a prémie v P2/10) a potřebuji do sumy hledat ve všech 10. Zatím mně napadlo pouze otrocky zopakovat 10x sumifs pro sloupce 1/10, 2/10, 3/10.... ale jde to i jinak? =SUMIFS($BE:$BE;$AK:$AK;A8;$D:$D;F$1;$E:$E;F$2) ...KDE $BE$ je první z 10 sloupců pro prémie, $AK je první sloupec pro osobní čísla, $A8 je konkrétní osobní číslo které mě pro výstup zajímá, $D je sloupec pro den, $F$1 je hodnota konkrétního dne, $E je sloupec pro směnnost (ráno/noc) a $F$2 pak konkrétní zkoumaná směna
Přidáno: 07.04.16 14:25
Dobrý den, lze do kritéria SUMIFS zadat rozsah čísel v daném sloupci A. Ve sloupci A jsou čísla od 1 do 100 a potřebuji vysčítat pouze ty řádky, kde jsou ve sloupci A čísla větší jak 10 a menší než 20. Děkuji
Přidáno: 20.04.16 14:46
to Hana Lze to zadat jak požadujete.
=SUMIFS(A:A;A:A;">10";A:A;"<20")
Přidáno: 12.05.16 21:26
Dobrý den, řeším problém, jak v daném řádku nebo sloupci sečíst hodnoty buněk pouze určité barvy. Nevím, jak zadat do kriteria tu barvu pozadí..? Dá se to udělat pomocí SUMIFS? Děkuji za radu.
Přidáno: 30.06.16 16:51
To David: Nutno využít VBA makra a napsat si vlastní funkci využitím například ColorIndex.
Využít SUBTOTAL a filtry podle barev - pracnější
Přidáno: 11.08.16 14:05
Dobrý den, s kolegyní jsme před lety daly dohromady takový mzdový prográmek v excelu, podklad pro další zpracování pro účetní. Funguje výborně. Malá stavební firma, několik zakázek, několik zedníků. Každá zakázka má svůj list, kam se napíšou hodiny, kolik tam odpracoval zedník, na základě hodinové sazby se do dalšího sloupce znásobí mzda na té zakázce. Pak je sešit, kde jsou všichni zedníci pod sebou a sloupce se zakázkami, takže dole je součet mezd všech na zakázce. A na straně zase mzda zedníka ze všech zakázek. A nějaké další funkce. Aby se to do toho listu přelilo, máme to zadané přes sumif. Ve sloupci se zakázkou je vzorec sumif - vezme se oblast z listu se zakázkou, podmínka je jméno a pod podmínkou vyhledá excel tu mzdu, kterou dá do toho souhrnného listu. Jenže - někdy, naštěstí výjimečně - se stane, že sumif vyhodí u někoho najednou nulu. A když zadám vzorec znovu, úplně stejně, tak nic. Prostě jako kdyby v té buňce bydlel nějaký zlý skřítek. Ani jedné nám to není jasné, zkusila jsem zrušit celý řádek, zadat vše znovu ...... a pořád stejně. Netušíte, čím to může být? Snad to píšu aspoň trochu srozumitelně.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 - 2021 |