Tento výukový program poskytuje podrobnou ukázku toho, jak používat funkce SUMIF a SUMIFS v Tabulkách Google se vzorci a příklady.
SUMIF je jedna z matematických funkcí v Tabulkách Google, která se používá k podmíněnému sčítání buněk. V podstatě funkce SUMIF hledá konkrétní podmínku v rozsahu buněk a následně sečte hodnoty, které dané podmínce vyhovují.
Máte například seznam výdajů v listech Google a chcete sečíst pouze výdaje, které jsou nad určitou maximální hodnotou. Nebo máte seznam položek objednávky a jejich odpovídající částky a chcete znát pouze celkovou částku objednávky konkrétní položky. Zde se hodí funkce SUMIF.
SUMIF lze použít k součtu hodnot na základě číselné podmínky, podmínky textu, podmínky data, zástupných znaků a také na základě prázdných a neprázdných buněk. Tabulky Google mají dvě funkce pro sčítání hodnot na základě kritérií: SUMIF a SUMIFS. Funkce SUMIF sčítá čísla na základě jedné podmínky, zatímco SUMIFS sčítá čísla na základě více podmínek.
V tomto tutoriálu vysvětlíme, jak používat funkce SUMIF a SUMIFS v Tabulkách Google k sečtení čísel, která splňují určité podmínky.
Funkce SUMIF v Tabulkách Google – syntaxe a argumenty
Funkce SUMIF je pouze kombinací funkcí SUM a IF. Funkce KDYŽ prohledá rozsah buněk pro danou podmínku a poté funkce SUM sečte čísla odpovídající buňkám, které podmínku splňují.
Syntaxe funkce SUMIF:
Syntaxe funkce SUMIF v Tabulkách Google je následující:
=SUMIF(rozsah; kritéria; [rozsah_součtu])Argumenty:
rozsah - Rozsah buněk, kde hledáme buňky, které splňují kritéria.
kritéria – Kritéria, která určují, které buňky je třeba přidat. Kritérium můžete založit na čísle, textovém řetězci, datu, odkazu na buňku, výrazu, logickém operátoru, zástupném znaku a také na dalších funkcích.
rozsah_součtu – Tento argument je nepovinný. Je to rozsah dat s hodnotami, které se mají sečíst, pokud odpovídající záznam rozsahu odpovídá podmínce. Pokud tento argument neuvedete, pak se místo toho sečte „rozsah“.
Nyní se podívejme, jak použít funkci SUMIF k sečtení hodnot s různým kritériem.
Funkce SUMIF s číselnými kritérii
Můžete sečíst čísla, která splňují určitá kritéria v rozsahu buněk, pomocí jednoho z následujících operátorů porovnání k vytvoření kritérií.
- větší než (>)
- méně než (<)
- větší nebo rovno (>=)
- menší nebo rovno (<=)
- rovno (=)
- nerovná se ()
Předpokládejme, že máte následující tabulku a zajímáte se o celkové prodeje, které jsou 1000 nebo vyšší.
Zde je návod, jak můžete zadat funkci SUMIF:
Nejprve vyberte buňku, kde se má zobrazit výstup součtu (D3). Chcete-li sečíst čísla v B2:B12, která jsou větší nebo rovna 1000, zadejte tento vzorec a stiskněte „Enter“:
=SUMIF(B2:B12;">=1000",B2:B12)V tomto vzorci jsou argumenty rozsah a rozsah_součtu (B2:B12) stejné, protože prodejní čísla a kritéria se použijí na stejný rozsah. A zadali jsme číslo před operátor porovnání a uzavřeli ho do uvozovek, protože kritéria by měla být vždy uzavřena do dvojitých uvozovek s výjimkou odkazu na buňku.
Vzorec hledal čísla, která jsou větší nebo rovna 1000, a poté sečetl všechny odpovídající hodnoty a výsledek ukázal v buňce D3.
Protože argumenty rozsah a rozsah_součtu jsou stejné, můžete dosáhnout stejného výsledku bez argumentů rozsah_součtu ve vzorci, jako je tento:
=SUMIF(B2:B12,">=1000")
Nebo můžete zadat odkaz na buňku (D2), který obsahuje číslo namísto číselného kritéria, a spojit operátor porovnání s tímto odkazem na buňku v argumentu kritéria:
=SUMIF(B2:B12,">="&D2)Jak vidíte, operátor porovnání je stále zadán ve dvojitých uvozovkách a operátor a odkaz na buňku jsou zřetězeny ampersandem (&). A nemusíte odkaz na buňku uzavírat do uvozovek.
Poznámka: Když odkazujete na buňku obsahující kritéria, ujistěte se, že v hodnotě v buňce není ponechána žádná mezera na začátku nebo na konci. Pokud má vaše hodnota před nebo za hodnotou v odkazované buňce nějakou zbytečnou mezeru, vzorec jako výsledek vrátí „0“.
Stejným způsobem můžete také použít jiné logické operátory k vytvoření podmínek v argumentu kritéria. Chcete-li například sečíst hodnoty menší než 500:
=SUMIF(B2:B12,"<500")
Součet, pokud se čísla rovnají
Pokud chcete přidat čísla, která se rovnají určitému číslu, můžete buď zadat pouze číslo, nebo zadat číslo se rovnítkem v argumentu kritéria.
Chcete-li například sečíst odpovídající částky prodeje (sloupec B) pro množství (sloupec C), jejichž hodnoty se rovnají 20, vyzkoušejte některý z těchto vzorců:
=SUMIF(C2:C12,"=20"B2:B12)
=SUMIF(C2:C12,"20"B2:B12)
=SUMIF(C2:C12;E2;B2:B12)
Chcete-li sečíst čísla ve sloupci B s množstvím, které se nerovná 20 ve sloupci C, zkuste tento vzorec:
=SUMIF(C2:C12,"20"B2:B12)
Funkce SUMIF s kritérii textu
Pokud chcete sečíst čísla v oblasti buněk (sloupec nebo řádek) odpovídající buňkám se specifickým textem, můžete tento text nebo buňku obsahující text jednoduše zahrnout do argumentu kritéria vzorce SUMIF. Upozorňujeme, že textový řetězec by měl být vždy uzavřen do dvojitých uvozovek (“ “).
Pokud například chcete celkový objem prodeje v regionu „Západ“, můžete použít níže uvedený vzorec:
=SUMIF(C2:C13,"Západ",B2:B13)V tomto vzorci funkce SUMIF hledá hodnotu ‚Západ‘ v oblasti buněk C2:C13 a sečte odpovídající hodnotu prodeje ve sloupci B. Výsledek pak zobrazí v buňce E3.
Místo použití textu v argumentu kritéria můžete také odkazovat na buňku, která obsahuje text:
=SUMIF(C2:C12;E2;B2:B12)
Nyní se podívejme na celkové příjmy všech regionů kromě „západu“. K tomu použijeme ve vzorci operátor nerovná se ():
=SUMIF(C2:C12,""&E2;B2:B12)
SUMIF se zástupnými kartami
Ve výše uvedené metodě funkce SUMIF s textovými kritérii kontroluje rozsah proti přesně zadanému textu. Potom sečte čísla paralelně k přesnému textu a ignoruje všechna ostatní čísla včetně částečně shodného textového řetězce. Chcete-li sečíst čísla s částečně odpovídajícími textovými řetězci, musíte ve svých kritériích upravit jeden z následujících zástupných znaků:
?(otazník) se používá pro shodu libovolného jednotlivého znaku kdekoli v textovém řetězci.*(hvězdička) se používá k nalezení odpovídajících slov spolu s libovolnou sekvencí znaků.~(tilda) se používá k přiřazení textů s otazníkem (?) nebo hvězdičkou (*).
V tomto příkladu tabulky pro produkty a jejich množství sečteme čísla se zástupnými znaky:
Hvězdička (*) Zástupný znak
Pokud například chcete sečíst množství všech produktů Apple, použijte tento vzorec:
=SUMIF(A2:A14,"Jablko*",B2:B14)Tento vzorec SUMIF najde všechny produkty se slovem „Apple“ na začátku a libovolným počtem znaků za ním (označeno „*“). Jakmile je nalezena shoda, shrne to Množství čísla odpovídající odpovídajícím textovým řetězcům.
V kritériích je také možné použít více zástupných znaků. Místo přímého textu můžete také zadat zástupné znaky s odkazy na buňky.
Chcete-li to provést, musí být zástupné znaky uzavřeny do dvojitých uvozovek („ “) a zřetězeny s odkazem na buňku:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Tento vzorec sečte množství všech produktů, které obsahují slovo „Redmi“, bez ohledu na to, kde se slovo nachází v řetězci.
Otazník (?) Zástupný znak
Zástupný znak otazníku (?) můžete použít k přiřazení textových řetězců k libovolným jednotlivým znakům.
Pokud například chcete najít množství všech variant Xiaomi Redmi 9, můžete použít tento vzorec:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Výše uvedený vzorec hledá textové řetězce se slovem „Xiaomi Redmi 9“, za nímž následují jednotlivé znaky a sečte odpovídající Množství čísla.
Vlnovka (~) Zástupný znak
Pokud chcete odpovídat skutečnému otazníku (?) nebo hvězdičce (*), vložte před zástupný znak v podmínkové části vzorce vlnovku (~).
Chcete-li přidat množství ve sloupci B s odpovídajícím řetězcem, který má na konci hvězdičku, zadejte níže uvedený vzorec:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Chcete-li přidat množství ve sloupci B, která mají ve sloupci A ve stejném řádku otazník (?), vyzkoušejte níže uvedený vzorec:
=SUMIF(A2:A14,"~?",B2:B14)
Funkce SUMIF s kritérii data
Funkce SUMIF vám také může pomoci podmíněně sečíst hodnoty na základě kritérií data – například čísla odpovídající určitému datu, před datem nebo po datu. K vytvoření kritérií data pro sčítání čísel můžete také použít libovolný operátor porovnání s hodnotou data.
Datum musí být zadáno ve formátu data podporovaného Tabulkami Google nebo jako odkaz na buňku obsahující datum nebo pomocí funkce data, jako je DATE() nebo TODAY().
Tuto ukázkovou tabulku použijeme, abychom vám ukázali, jak funguje funkce SUMIF s kritérii data:
Předpokládejme, že chcete sečíst částky prodejů, ke kterým došlo 29. listopadu 2019 nebo před (<=) ve výše uvedené sadě dat, můžete tato čísla prodeje přidat pomocí funkce SUMIF jedním z těchto způsobů:
=SUMIF(C2:C13,"<=29. listopadu 2019"B2:B13)Výše uvedený vzorec kontroluje každou buňku od C2 do C13 a shoduje se pouze s těmi buňkami, které obsahují data 29. listopadu 2019 nebo dříve (29. 11. 2019). A pak sečte částku prodeje odpovídající odpovídajícím buňkám z oblasti buněk B2:B13 a zobrazí výsledek v buňkách E3.
Datum lze do vzorce zadat v libovolném formátu, který rozpoznávají Tabulky Google, například „29. listopadu 2019“, „29. listopadu 2019“ nebo „29. 11. 2019“ atd. Zapamatujte si hodnotu data a operátor musí být vždy uzavřen do dvojitých uvozovek.
Můžete také použít funkci DATE() v kritériích místo přímého data vale:
=SUMIF(C2:C13,"<="&DATUM(2019,11;29);B2:B13)
Nebo můžete použít odkaz na buňku místo data v části kritéria vzorce:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Pokud chcete sečíst prodejní částky na základě dnešního data, můžete použít funkci DNES() v argumentu kritéria.
Chcete-li například sečíst všechny částky prodeje za dnešní datum, použijte tento vzorec:
=SUMIF(C2:C13,DNES(),B2:B13)
Funkce SUMIF s prázdnými nebo neprázdnými buňkami
Někdy může být nutné sečíst čísla v rozsahu buněk s prázdnými nebo neprázdnými buňkami ve stejném řádku. V takových případech můžete použít funkci SUMIF k sečtení hodnot na základě kritérií, zda jsou buňky prázdné nebo ne.
Suma if Blank
V Tabulkách Google existují dvě kritéria pro vyhledání prázdných buněk: „“ nebo „=“.
Chcete-li například sečíst všechny částky prodeje, které obsahují řetězce nulové délky (vizuálně vypadají prázdné) ve sloupci C, použijte ve vzorci dvojité uvozovky bez mezer:
=SUMIF(C2:C13,"",B2:B13)
Chcete-li sečíst celou částku prodeje ve sloupci B s úplnými prázdnými buňkami ve sloupci C, zahrňte jako kritérium „=“:
=SUMIF(C2:C13,"=",B2:B13)
Součet, pokud není prázdný:
Pokud chcete sečíst buňky, které obsahují libovolnou hodnotu (ne prázdnou), můžete jako kritérium ve vzorci použít „“:
Chcete-li například získat celkovou částku prodeje s libovolnými daty, použijte tento vzorec:
=SUMIF(C2:C13,"",B2:B13)
SUMIF Na základě více kritérií s logikou OR
Jak jsme dosud viděli, funkce SUMIF je navržena tak, aby sčítala čísla pouze na základě jediného kritéria, ale pomocí funkce SUMIF v Tabulkách Google je možné sčítat hodnoty na základě více kritérií. Lze to provést spojením více než jedné funkce SUMIF do jednoho vzorce s logikou OR.
Chcete-li například sečíst objem prodeje v regionu „Západ“ nebo „Jih“ (logika NEBO) v určeném rozsahu (B2:B13), použijte tento vzorec:
=SUMIF(C2:C13,"Západ",B2:B13)+SUMIF(C2:C13,"Jih",B2:B13)Tento vzorec sečte buňky, když je alespoň jedna z podmínek PRAVDA. Proto je to známé jako „logika NEBO“. Při splnění všech podmínek také sečte hodnoty.
První část vzorce kontroluje rozsah C2:C13 pro text ‚Západ‘ a sečte hodnoty v rozsahu B2:B13, když je shoda splněna. Sekundová část kontroluje textovou hodnotu „Jih“ ve stejném rozsahu C2:C13 a poté sečte hodnoty s odpovídajícím textem ve stejném rozsahu_součtu B2:B13. Poté se oba součty sečtou a zobrazí se v buňce E3.
V případech, kdy je splněno pouze jedno kritérium, vrátí pouze tuto hodnotu součtu.
Můžete také použít více kritérií namísto jednoho nebo dvou. A pokud používáte více kritérií, je lepší použít jako kritérium odkaz na buňku místo zápisu přímé hodnoty do vzorce.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF s logikou OR přidává hodnoty, když je splněno alespoň jedno ze zadaných kritérií, ale pokud chcete sčítat hodnoty pouze tehdy, když jsou splněny všechny zadané podmínky, musíte použít jeho novou sourozeneckou funkci SUMIFS().
Funkce SUMIFS v Tabulkách Google (více kritérií)
Když použijete funkci SUMIF k sečtení hodnot na základě více kritérií, vzorec může být příliš dlouhý a komplikovaný a budete náchylní k chybám. Kromě toho vám SUMIF umožní sčítat hodnoty pouze v jednom rozsahu a pokud je některá z podmínek PRAVDA. Zde přichází na řadu funkce SUMIFS.
Funkce SUMIFS vám pomáhá sčítat hodnoty na základě více kritérií shody v jednom nebo více rozsazích. A funguje na logice AND, což znamená, že může sčítat hodnoty pouze tehdy, když jsou splněny všechny dané podmínky. I když je jedna podmínka nepravdivá, ve výsledku vrátí „0“.
Syntaxe a argumenty funkce SUMIFS
Syntaxe funkce SUMIFS je následující:
=SUMIFS(rozsah_součtu, rozsah_kritérií1, kritérium1, [rozsah_kritérií2, ...], [kritérium2, ...])Kde,
- rozsah_součtu – Rozsah buněk obsahujících hodnoty, které chcete sečíst, když jsou splněny všechny podmínky.
- rozsah_kritérií1 – Je to rozsah buněk, kde kontrolujete kritéria1.
- kritéria 1 – Je to podmínka, kterou musíte porovnat s rozsahem kritérií1.
- kriteria_range2, criterion2, …– Dodatečné rozsahy a kritéria k vyhodnocení. A do vzorce můžete přidat další rozsahy a podmínky.
Datovou sadu na následujícím snímku obrazovky použijeme k demonstraci toho, jak funkce SUMIFS funguje s různými kritérii.
SUMIFS s textovými podmínkami
Hodnoty můžete sčítat na základě dvou různých textových kritérií v různých rozsazích. Řekněme například, že chcete zjistit celkovou prodejní částku dodaného stanu. K tomu použijte tento vzorec:
=SUMIFS(D2:D13;A2:A13,"Stan",C2:C13,"Dodáno")V tomto vzorci máme dvě kritéria: „stan“ a „doručeno“. Funkce SUMIFS kontroluje položku ‚Stan‘ (kritérium1) v rozsahu A2:A13 (rozsah_kritérií1) a zkontroluje stav ‚Doručeno‘ (kritérium2) v rozsahu C2:C13 (rozsah_kritérií2). Když jsou obě podmínky splněny, pak sečte odpovídající hodnotu v oblasti buněk D2:D13 (rozsah_součtu).
SUMIFS s číselnými kritérii a logickými operátory
Podmínkové operátory můžete použít k vytvoření podmínek s čísly pro funkci SUMIFS.
Chcete-li zjistit celkový prodej více než 5 množství jakékoli položky ve státě Kalifornie (CA), použijte tento vzorec:
=SUMIFS(E2:E13;D2:D13;">5"B2:B13,"CA")Tento vzorec má dvě podmínky: „>5“ a „CA“.
Tento vzorec kontroluje množství (množství) větší než 5 v rozsahu D2:D13 a kontroluje stav ‚CA‘ v rozsahu B2:B13. A když jsou obě podmínky splněny (to znamená, že jsou ve stejném řádku), sečte částku v E2:E13.
SUMIFS s kritérii data
Funkce SUMIFS také umožňuje zkontrolovat více podmínek ve stejném rozsahu i v různých rozsazích.
Předpokládejme, že chcete zkontrolovat celkovou prodejní částku dodaných položek po 31. 5. 2021 a před datem 10. 6. 2021, pak použijte tento vzorec:
=SUMIFS(E2:E13,D2:D13;">"&G1;D2:D13,"<"&G2,C2:C13,G3)Výše uvedený vzorec má tři podmínky: 31. 5. 2021, 10. 5. 2021 a Doručeno. Namísto použití přímých hodnot data a textu jsme odkazovali na buňky obsahující tato kritéria.
Vzorec kontroluje data po 31. 5. 2021 (G1) a data před 6. 10. 2021 (G2) ve stejném rozsahu D2:D13 a kontroluje stav „Doručeno“ mezi těmito dvěma daty. Potom sečte související částku v rozsahu E2:E13.
SUMIFS s prázdnými a neprázdnými buňkami
Někdy můžete chtít najít součet hodnot, když je odpovídající buňka prázdná nebo ne. Chcete-li to provést, můžete použít jedno ze tří kritérií, o kterých jsme hovořili dříve: „=“, „“ a „“.
Pokud si například přejete sečíst pouze množství položek „stan“, u kterých ještě nebylo potvrzeno datum dodání (prázdné buňky), můžete použít kritéria „=“:
=SUMIFS(D2:D13;A2:A13,"Stan",C2:C13,"=")Vzorec hledá položku ‚Stan‘ (kritérium1) ve sloupci A s odpovídajícími prázdnými buňkami (kritérium2) ve sloupci C a poté sečte odpovídající částku ve sloupci D. „=“ představuje zcela prázdnou buňku.
Chcete-li najít součet položek ‚stan‘, u kterých bylo potvrzeno datum dodání (nikoli prázdné buňky), použijte jako kritérium „“:
=SUMIFS(D2:D13;A2:A13,"Stan";C2:C13,"")V tomto vzorci jsme právě zaměnili „=“ za „“. Najde součet položek stanu s neprázdnými buňkami ve sloupci C.
SUMIFS s logikou OR
Vzhledem k tomu, že funkce SUMIFS pracuje na logice AND, sčítá pouze tehdy, jsou-li splněny všechny podmínky. Ale co když chcete sečíst hodnotu na základě více kritérií, když je splněno kterékoli z kritérií? Trik je v použití více funkcí SUMIFS.
Chcete-li například sečíst prodejní částku pro „Nosič na kola“ NEBO „Batoh“, když je jejich stav „Objednáno“, zkuste tento vzorec:
=SUMIFS(D2:D13,A2:A13,"Vozík na kola",C2:C13,"Objednáno") +SUMIFS(D2:D13,A2:A13,"Batoh",C2:C13,"Objednáno")První funkce SUMIFS kontroluje dvě kritéria „Nosič na kola“ a „Objednáno“ a sečte hodnoty množství ve sloupci D. Poté druhá funkce SUMIFS zkontroluje dvě kritéria „Batoh“ a „Objednáno“ a sečte hodnoty množství ve sloupci D. A pak , oba součty se sečtou a zobrazí se na F3. Jednoduše řečeno, tento vzorec se sčítá, když je objednáno buď „Nosič na kola“ nebo „Batoh“.
To je vše, co potřebujete vědět o funkcích SUMIF a SUMIFS v Tabulkách Google.