Tento tutoriál poskytuje podrobnú ukážku toho, ako používať funkcie SUMIF a SUMIFS v Tabuľkách Google so vzorcami a príkladmi.
SUMIF je jedna z matematických funkcií v Tabuľkách Google, ktorá sa používa na podmienené sčítanie buniek. V podstate funkcia SUMIF hľadá konkrétnu podmienku v rozsahu buniek a následne sčítava hodnoty, ktoré spĺňajú danú podmienku.
Napríklad máte zoznam výdavkov v hárkoch Google a chcete sčítať iba výdavky, ktoré presahujú určitú maximálnu hodnotu. Alebo máte zoznam položiek objednávky a ich zodpovedajúce sumy a chcete vedieť iba celkovú sumu objednávky konkrétnej položky. Tu sa hodí funkcia SUMIF.
SUMIF možno použiť na sčítanie hodnôt na základe podmienky čísla, podmienky textu, podmienky dátumu, zástupných znakov, ako aj na základe prázdnych a neprázdnych buniek. Tabuľky Google majú dve funkcie na sčítanie hodnôt na základe kritérií: SUMIF a SUMIFS. Funkcia SUMIF sčítava čísla na základe jednej podmienky, zatiaľ čo SUMIFS sčítava čísla na základe viacerých podmienok.
V tomto návode si vysvetlíme, ako používať funkcie SUMIF a SUMIFS v Tabuľkách Google na sčítanie čísel, ktoré spĺňajú určité podmienky.
Funkcia SUMIF v Tabuľkách Google – syntax a argumenty
Funkcia SUMIF je len kombináciou funkcie SUM a IF. Funkcia IF prehľadá rozsah buniek pre danú podmienku a potom funkcia SUM spočíta čísla zodpovedajúce bunkám, ktoré spĺňajú podmienku.
Syntax funkcie SUMIF:
Syntax funkcie SUMIF v Tabuľkách Google je nasledovná:
=SUMIF(rozsah, kritériá, [rozsah_sumu])Argumenty:
rozsah - Rozsah buniek, v ktorých hľadáme bunky, ktoré spĺňajú kritériá.
kritériá – Kritériá, ktoré určujú, ktoré bunky je potrebné pridať. Kritérium môžete založiť na čísle, textovom reťazci, dátume, odkaze na bunku, výraze, logickom operátore, zástupnom znaku, ako aj na iných funkciách.
rozsah_sumu – Tento argument je nepovinný. Je to rozsah údajov s hodnotami, ktoré sa majú sčítať, ak zodpovedajúca položka rozsahu zodpovedá podmienke. Ak nezahrniete tento argument, namiesto toho sa sčíta „rozsah“.
Teraz sa pozrime, ako použiť funkciu SUMIF na sčítanie hodnôt s rôznym kritériom.
Funkcia SUMIF s číselnými kritériami
Čísla, ktoré spĺňajú určité kritériá, môžete sčítať v rozsahu buniek pomocou jedného z nasledujúcich porovnávacích operátorov na vytvorenie kritérií.
- väčšie ako (>)
- menej ako (<)
- väčšie alebo rovné (>=)
- menšie alebo rovné (<=)
- rovná sa (=)
- nerovná sa ()
Predpokladajme, že máte nasledujúcu tabuľku a zaujíma vás celkový predaj 1 000 alebo vyšší.
Tu je návod, ako môžete zadať funkciu SUMIF:
Najprv vyberte bunku, v ktorej sa má zobraziť súčet (D3). Ak chcete sčítať čísla v B2:B12, ktoré sú väčšie alebo rovné 1 000, zadajte tento vzorec a stlačte „Enter“:
=SUMIF(B2:B12;">=1000",B2:B12)V tomto vzorovom vzorci sú argumenty range a sum_range (B2:B12) rovnaké, pretože predajné čísla a kritériá sa aplikujú na rovnaký rozsah. A zadali sme číslo pred operátor porovnávania a uzavreli ho do úvodzoviek, pretože kritériá by mali byť vždy uzavreté v dvojitých úvodzovkách okrem odkazu na bunku.
Vzorec hľadal čísla, ktoré sú väčšie alebo rovné 1 000 a potom sčítal všetky zhodné hodnoty a zobrazil výsledok v bunke D3.
Keďže argumenty range a sum_range sú rovnaké, môžete dosiahnuť rovnaký výsledok bez argumentov sum_range vo vzorci, ako je tento:
=SUMIF(B2:B12,">=1000")
Alebo môžete zadať odkaz na bunku (D2), ktorý obsahuje číslo namiesto číselných kritérií, a spojiť operátor porovnávania s týmto odkazom na bunku v argumente kritéria:
=SUMIF(B2:B12,">="&D2)Ako vidíte, operátor porovnávania je stále zadaný v dvojitých úvodzovkách a operátor a odkaz na bunku sú spojené znakom ampersand (&). Odkaz na bunku nemusíte uzatvárať do úvodzoviek.
Poznámka: Keď odkazujete na bunku, ktorá obsahuje kritériá, dbajte na to, aby ste v hodnote v bunke nenechali žiadnu medzeru na začiatku ani na konci. Ak má vaša hodnota pred alebo za hodnotou v odkazovanej bunke zbytočné medzery, vzorec v dôsledku toho vráti hodnotu „0“.
Rovnakým spôsobom môžete použiť aj iné logické operátory na vytvorenie podmienok v argumente kritéria. Ak chcete napríklad sčítať hodnoty menšie ako 500:
=SUMIF(B2:B12,"<500")
Súčet, ak sa čísla rovnajú
Ak chcete pridať čísla, ktoré sa rovnajú určitému číslu, môžete zadať iba číslo alebo zadať číslo so znamienkom rovnosti v argumente kritéria.
Ak chcete napríklad sčítať zodpovedajúce sumy predaja (stĺpec B) pre množstvá (stĺpec C), ktorých hodnoty sa rovnajú 20, vyskúšajte ktorýkoľvek z týchto vzorcov:
=SUMIF(C2:C12,"=20"B2:B12)
=SUMIF(C2:C12,"20"B2:B12)
=SUMIF(C2:C12;E2;B2:B12)
Ak chcete sčítať čísla v stĺpci B s množstvom nerovnajúcim sa 20 v stĺpci C, skúste tento vzorec:
=SUMIF(C2:C12,"20"B2:B12)
Funkcia SUMIF s kritériami textu
Ak chcete sčítať čísla v rozsahu buniek (stĺpec alebo riadok), ktorý zodpovedá bunkám so špecifickým textom, môžete tento text alebo bunku, ktorá text obsahuje, jednoducho zahrnúť do argumentu kritéria vo vzorci SUMIF. Upozorňujeme, že textový reťazec by mal byť vždy uzavretý v dvojitých úvodzovkách (“ “).
Napríklad, ak chcete celkový objem predaja v regióne „Západ“, môžete použiť nasledujúci vzorec:
=SUMIF(C2:C13,"Západ",B2:B13)V tomto vzorci funkcia SUMIF vyhľadá hodnotu „Západ“ v rozsahu buniek C2:C13 a spočíta zodpovedajúcu hodnotu predaja v stĺpci B. Potom zobrazí výsledok v bunke E3.
Namiesto použitia textu v argumente kritéria sa môžete odkázať aj na bunku, ktorá obsahuje text:
=SUMIF(C2:C12;E2;B2:B12)
Teraz získajme celkové príjmy všetkých regiónov okrem „západu“. Aby sme to dosiahli, použijeme vo vzorci operátor nerovná sa ():
=SUMIF(C2:C12,""&E2,B2:B12)
SUMIF so zástupnými kartami
Vo vyššie uvedenej metóde funkcia SUMIF s textovými kritériami kontroluje rozsah oproti presne špecifikovanému textu. Potom spočíta čísla rovnobežne s presným textom a ignoruje všetky ostatné čísla vrátane čiastočne zhodného textového reťazca. Ak chcete sčítať čísla s čiastočne zhodnými textovými reťazcami, musíte vo svojich kritériách prispôsobiť jeden z nasledujúcich zástupných znakov:
?(otáznik) sa používa na priradenie ľubovoľného jednotlivého znaku kdekoľvek v textovom reťazci.*(hviezdička) sa používa na nájdenie zhodných slov spolu s ľubovoľnou sekvenciou znakov.~(tilda) sa používa na párovanie textov s otáznikom (?) alebo hviezdičkou (*).
V tomto príklade tabuľky pre produkty a ich množstvá spočítame čísla so zástupnými znakmi:
Hviezdička (*) Zástupný znak
Napríklad, ak chcete sčítať množstvá všetkých produktov Apple, použite tento vzorec:
=SUMIF(A2:A14,"Jablko*",B2:B14)Tento vzorec SUMIF nájde všetky produkty so slovom „Apple“ na začiatku a ľubovoľným počtom znakov za ním (označené „*“). Keď sa nájde zhoda, sumarizuje sa množstvo čísla zodpovedajúce zodpovedajúcim textovým reťazcom.
V kritériách je tiež možné použiť viacero zástupných znakov. Môžete tiež zadať zástupné znaky s odkazmi na bunky namiesto priameho textu.
Aby ste to dosiahli, musia byť zástupné znaky vložené do dvojitých úvodzoviek („ “) a spojené s odkazom na bunky:
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)Tento vzorec spočítava množstvá všetkých produktov, ktoré obsahujú slovo „Redmi“, bez ohľadu na to, kde sa slovo nachádza v reťazci.
Otáznik (?) Zástupný znak
Zástupný znak otáznika (?) môžete použiť na priradenie textových reťazcov k jednotlivým znakom.
Napríklad, ak chcete nájsť množstvo všetkých variantov Xiaomi Redmi 9, môžete použiť tento vzorec:
=SUMIF(A2:A14,"Xiaomi Redmi 9?",B2:B14)Vyššie uvedený vzorec hľadá textové reťazce so slovom „Xiaomi Redmi 9“, za ktorým nasledujú jednotlivé znaky a sčítava zodpovedajúce množstvo čísla.
Vlnovka (~) Zástupný znak
Ak chcete nájsť zhodu so skutočným otáznikom (?) alebo hviezdičkou (*), vložte pred zástupný znak v podmienkovej časti vzorca vlnovku (~).
Ak chcete pridať množstvá v stĺpci B so zodpovedajúcim reťazcom, ktoré majú na konci znak hviezdičky, zadajte nasledujúci vzorec:
=SUMIF(A2:A14,"Samsung Galaxy V~*",B2:B14)
Ak chcete pridať množstvá v stĺpci B, ktoré majú otáznik (?) v stĺpci A v rovnakom riadku, skúste použiť nasledujúci vzorec:
=SUMIF(A2:A14,"~?",B2:B14)
Funkcia SUMIF s dátumovými kritériami
Funkcia SUMIF vám tiež môže pomôcť podmienečne sčítať hodnoty na základe kritérií dátumu – napríklad čísla zodpovedajúce určitému dátumu, pred dátumom alebo po dátume. Na vytvorenie kritérií dátumu pre súčtové čísla môžete použiť aj ktorýkoľvek z porovnávacích operátorov s hodnotou dátumu.
Dátum je potrebné zadať vo formáte dátumu podporovanom tabuľkami Google alebo ako odkaz na bunku, ktorá obsahuje dátum, alebo pomocou funkcie dátumu, ako je DATE() alebo TODAY().
Tento príklad tabuľky použijeme na to, aby sme vám ukázali, ako funguje funkcia SUMIF s kritériami dátumu:
Predpokladajme, že chcete sčítať sumy predajov, ktoré sa udiali 29. novembra 2019 alebo pred (<=) vo vyššie uvedenom súbore údajov, tieto čísla predaja môžete pridať pomocou funkcie SUMIF jedným z týchto spôsobov:
=SUMIF(C2:C13,"<=29. novembra 2019"B2:B13)Vyššie uvedený vzorec skontroluje každú bunku od C2 do C13 a zhoduje sa iba s tými bunkami, ktoré obsahujú dátumy 29. novembra 2019 alebo skôr (29/11/2019). Potom spočíta sumu predaja zodpovedajúcu zodpovedajúcim bunkám z rozsahu buniek B2:B13 a zobrazí výsledok v bunkách E3.
Dátum je možné do vzorca dodať v akomkoľvek formáte, ktorý rozpoznávajú Tabuľky Google, ako napríklad „29. november 2019“, „29. november 2019“ alebo „29. 11. 2019“ atď. Zapamätajte si hodnotu dátumu a operátor musí byť vždy v dvojitých úvodzovkách.
Môžete tiež použiť funkciu DATE() v kritériách namiesto priamej hodnoty dátumu:
=SUMIF(C2:C13,"<="&DÁTUM(2019,11;29);B2:B13)
Alebo môžete použiť odkaz na bunku namiesto dátumu v časti kritérií vzorca:
=SUMIF(C2:C13,"<="&E2,B2:B13)
Ak chcete spočítať sumy predaja na základe dnešného dátumu, môžete použiť funkciu DNES() v argumente kritéria.
Ak chcete napríklad sčítať všetky sumy predaja k dnešnému dátumu, použite tento vzorec:
=SUMIF(C2:C13,DNES(),B2:B13)
Funkcia SUMIF s prázdnymi alebo neprázdnymi bunkami
Niekedy možno budete musieť sčítať čísla v rozsahu buniek s prázdnymi alebo neprázdnymi bunkami v rovnakom riadku. V takýchto prípadoch môžete použiť funkciu SUMIF na sčítanie hodnôt na základe kritérií, či sú bunky prázdne alebo nie.
Suma if Blank
V Tabuľkách Google existujú dve kritériá na nájdenie prázdnych buniek: „“ alebo „=“.
Napríklad, ak chcete sčítať všetky sumy predaja, ktoré obsahujú reťazce nulovej dĺžky (vizuálne vyzerajú prázdne) v stĺpci C, použite vo vzorci dvojité úvodzovky bez medzier:
=SUMIF(C2:C13,"",B2:B13)
Ak chcete sčítať celú sumu predaja v stĺpci B s úplne prázdnymi bunkami v stĺpci C, zahrňte „=“ ako kritérium:
=SUMIF(C2:C13,"=",B2:B13)
Súčet, ak nie je prázdny:
Ak chcete sčítať bunky, ktoré obsahujú akúkoľvek hodnotu (nie prázdnu), môžete použiť „“ ako kritérium vo vzorci:
Ak chcete napríklad získať celkovú sumu predaja s ľubovoľnými dátumami, použite tento vzorec:
=SUMIF(C2:C13,"",B2:B13)
SUMIF na základe viacerých kritérií s logikou OR
Ako sme doteraz videli, funkcia SUMIF je navrhnutá tak, aby sčítala čísla len na základe jedného kritéria, ale pomocou funkcie SUMIF v Tabuľkách Google je možné sčítať hodnoty na základe viacerých kritérií. Dá sa to urobiť spojením viac ako jednej funkcie SUMIF do jedného vzorca s logikou OR.
Napríklad, ak chcete sčítať sumu predaja v regióne „Západ“ alebo „Juh“ (OR) v zadanom rozsahu (B2:B13), použite tento vzorec:
=SUMIF(C2:C13,"Západ",B2:B13)+SUMIF(C2:C13,"Juh",B2:B13)Tento vzorec spočítava bunky, keď je aspoň jedna z podmienok PRAVDA. Preto je to známe ako „logika ALEBO“. Pri splnení všetkých podmienok bude tiež sumarizovať hodnoty.
Prvá časť vzorca skontroluje rozsah C2:C13 pre text „Západ“ a pri splnení zhody spočíta hodnoty v rozsahu B2:B13. Sekundová časť kontroluje textovú hodnotu „Juh“ v rovnakom rozsahu C2:C13 a potom sčítava hodnoty so zodpovedajúcim textom v rovnakom rozsahu_súčtu B2:B13. Potom sa oba súčty sčítajú a zobrazia sa v bunke E3.
V prípade, že je splnené iba jedno kritérium, vráti iba túto hodnotu súčtu.
Môžete tiež použiť viacero kritérií namiesto jedného alebo dvoch. A ak používate viacero kritérií, je lepšie použiť ako kritérium odkaz na bunku namiesto zapisovania priamej hodnoty do vzorca.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
SUMIF s logikou OR pridáva hodnoty, keď je splnené aspoň jedno zo špecifikovaných kritérií, ale ak chcete sčítať hodnoty len vtedy, keď sú splnené všetky špecifikované podmienky, musíte použiť jeho novú súrodeneckú funkciu SUMIFS().
Funkcia SUMIFS v Tabuľkách Google (viaceré kritériá)
Keď použijete funkciu SUMIF na sčítanie hodnôt na základe viacerých kritérií, vzorec môže byť príliš dlhý a komplikovaný a budete náchylní robiť chyby. Okrem toho vám SUMIF umožní sčítať hodnoty iba v jednom rozsahu a keď je niektorá z podmienok TRUE. Tu prichádza funkcia SUMIFS.
Funkcia SUMIFS vám pomáha sčítať hodnoty na základe viacerých kritérií zhody v jednom alebo viacerých rozsahoch. A funguje na logike AND, čo znamená, že dokáže sčítať hodnoty iba vtedy, keď sú splnené všetky dané podmienky. Aj keď je jedna podmienka nepravdivá, vo výsledku vráti hodnotu „0“.
Syntax a argumenty funkcie SUMIFS
Syntax funkcie SUMIFS je nasledovná:
=SUMIFS(rozsah_sumu, rozsah_kriterii1, kritérium1, [rozsah_kritérií2, ...], [kritérium2, ...])Kde,
- rozsah_súčtov – Rozsah buniek obsahujúcich hodnoty, ktoré chcete sčítať, keď sú splnené všetky podmienky.
- rozsah_kritérií1 – Je to rozsah buniek, kde kontrolujete kritériá1.
- kritériá 1 – Je to podmienka, ktorú musíte porovnať s kritériami_rozsah1.
- criteria_range2, criterion2, …– Dodatočné rozsahy a kritériá na vyhodnotenie. A do vzorca môžete pridať ďalšie rozsahy a podmienky.
Súbor údajov na nasledujúcej snímke obrazovky použijeme na demonštráciu toho, ako funkcia SUMIFS funguje s rôznymi kritériami.
SUMIFS s textovými podmienkami
Hodnoty môžete sčítať na základe dvoch rôznych textových kritérií v rôznych rozsahoch. Povedzme napríklad, že chcete zistiť celkovú predajnú sumu dodanej položky stanu. Na tento účel použite tento vzorec:
=SUMIFS(D2:D13;A2:A13,"Stan",C2:C13,"Doručené")V tomto vzorci máme dve kritériá: „stan“ a „doručené“. Funkcia SUMIFS skontroluje položku „Stan“ (kritérium1) v rozsahu A2:A13 (rozsah_kritérií1) a skontroluje stav „Doručené“ (kritérium2) v rozsahu C2:C13 (rozsah_kritérií2). Keď sú splnené obe podmienky, potom spočíta zodpovedajúcu hodnotu v rozsahu buniek D2:D13 (rozsah_sumu).
SUMIFS s číselnými kritériami a logickými operátormi
Podmienkové operátory môžete použiť na vytvorenie podmienok s číslami pre funkciu SUMIFS.
Ak chcete zistiť celkový predaj viac ako 5 množstiev akejkoľvek položky v štáte Kalifornia (CA), použite tento vzorec:
=SUMIFS(E2:E13;D2:D13;">5"B2:B13,"CA")Tento vzorec má dve podmienky: „>5“ a „CA“.
Tento vzorec kontroluje množstvá (množstvo) väčšie ako 5 v rozsahu D2:D13 a kontroluje stav „CA“ v rozsahu B2:B13. A keď sú splnené obe podmienky (to znamená, že sú v rovnakom riadku), suma sa sčíta v E2:E13.
SUMIFS s dátumovými kritériami
Funkcia SUMIFS vám tiež umožňuje kontrolovať viacero podmienok v rovnakom rozsahu, ako aj v rôznych rozsahoch.
Predpokladajme, že chcete skontrolovať celkovú sumu predaja dodaných položiek po 31.5.2021 a pred dátumom 10.6.2021, potom použite tento vzorec:
=SUMIFS(E2:E13;D2:D13;">"&G1;D2:D13,"<"&G2,C2:C13;G3)Vyššie uvedený vzorec má tri podmienky: 31.5.2021, 10.5.2021 a Doručené. Namiesto použitia priamych dátumových a textových hodnôt sme odkázali na bunky obsahujúce tieto kritériá.
Vzorec kontroluje dátumy po 31. 5. 2021 (G1) a dátumy pred 6. 10. 2021 (G2) v rovnakom rozsahu D2:D13 a kontroluje stav „Doručené“ medzi týmito dvoma dátumami. Potom spočíta súvisiacu sumu v rozsahu E2:E13.
SUMIFS s prázdnymi a neprázdnymi bunkami
Niekedy možno budete chcieť nájsť súčet hodnôt, keď je zodpovedajúca bunka prázdna alebo nie. Ak to chcete urobiť, môžete použiť jedno z troch kritérií, o ktorých sme hovorili predtým: „=“, „“ a „“.
Napríklad, ak chcete sčítať iba množstvo položiek „stan“, pre ktoré ešte nebol potvrdený dátum doručenia (prázdne bunky), môžete použiť kritériá „=“:
=SUMIFS(D2:D13;A2:A13,"Stan",C2:C13,"=")Vzorec hľadá položku „Stan“ (kritérium 1) v stĺpci A so zodpovedajúcimi prázdnymi bunkami (kritérium 2) v stĺpci C a potom sčíta zodpovedajúcu sumu v stĺpci D. „=“ predstavuje úplne prázdnu bunku.
Ak chcete nájsť súčet položiek „stan“, pre ktoré bol potvrdený dátum doručenia (nie prázdne bunky), použite ako kritérium „“:
=SUMIFS(D2:D13;A2:A13,"Stan";C2:C13,"")V tomto vzorci sme práve vymenili „=“ za „“. Nájde súčet položiek stanu s neprázdnymi bunkami v stĺpci C.
SUMIFS s logikou OR
Keďže funkcia SUMIFS funguje na logike AND, sčítava sa iba vtedy, keď sú splnené všetky podmienky. Ale čo ak chcete sčítať hodnotu na základe viacerých kritérií, keď je splnené jedno z kritérií? Trik je v použití viacerých funkcií SUMIFS.
Napríklad, ak chcete sčítať sumu predaja buď pre „Nosič na bicykle“ ALEBO „Batoh“, keď je ich stav „Objednané“, skúste tento vzorec:
=SUMIFS(D2:D13,A2:A13,"Nosič na bicykle",C2:C13,"Objednané") +SUMIFS(D2:D13,A2:A13,"Batoh",C2:C13,"Objednané")Prvá funkcia SUMIFS kontroluje dve kritériá „Nosič na bicykle“ a „Objednané“ a sčítava hodnoty množstva v stĺpci D. Potom druhá funkcia SUMIFS skontroluje dve kritériá „Batoh“ a „Objednané“ a sčítava hodnoty množstva v stĺpci D. , oba súčty sa sčítajú a zobrazia sa na F3. Jednoducho povedané, tento vzorec sa sčítava, keď si objednáte buď „Nosič na bicykle“ alebo „Batoh“.
To je všetko, čo potrebujete vedieť o funkciách SUMIF a SUMIFS v Tabuľkách Google.