Tento výukový program vysvětluje rozdíl mezi funkcemi SUMIF a SUMIFS z hlediska jejich syntaxe a použití a poskytuje řadu příkladů vzorců pro součet hodnot s více kritérii AND / OR v aplikaci Excel 2019, 2016, 2013, 2010, 2007, 2003 a nižší.
Jak každý ví, Microsoft Excel poskytuje pole funkcí pro provádění různých výpočtů s daty. Před několika články jsme prozkoumali COUNTIF a COUNTIFS, které jsou určeny pro počítání buněk na základě jedné podmínky a několika podmínek. Minulý týden jsme zahrnovali Excel SUMIF, který přidává hodnoty splňující zadaná kritéria. Nyní je čas projít si množnou verzi SUMIF – Excel SUMIFS, která umožňuje sčítání hodnot podle více kritérií.
Ti, kteří jsou obeznámeni s funkcí SUMIF, si mohou myslet, že její převod na SUMIFS vyžaduje jen něco navíc „S“ a několik dalších kritérií. To by se zdálo docela logické… ale „logické“ to vždy neplatí, když se jedná o Microsoft:)
- Funkce SUMIF – syntaxe & využití
- funkce SUMIFS – syntaxe & využití
- Jak používat SUMIFS v aplikaci Excel (A logika)
- Excel SUMIF s více kritérii (NEBO logika)
- Excel SUMIFS s více podmínkami OR
- Použití součtu Excel v maticových vzorcích
Funkce Excel SUMIF – syntaxe & použití
Funkce SUMIF se používá k podmíněnému součtu hodnot na základě jediného kritéria. Syntaxi SUMIF jsme podrobně probrali v předchozím článku, dovolte mi tedy nyní jen stručné shrnutí.
-
range– požadovaný rozsah buněk, které mají být hodnoceny podle vašich kritérií. -
criteria– podmínka, kterou je nutné splnit, povinné. -
sum_range– buňky, které se mají sečíst, pokud je podmínka splněna, nepovinné.
Jak vidíte, syntaxe funkce Excel SUMIF umožňuje pouze jednu podmínku. A přesto říkáme, že Excel SUMIF lze použít k součtu hodnot s více kritérii. Jak to může být? Přidáním výsledků několika funkcí SUMIF a použitím vzorců SUMIF s kritérii pole, jak je ukázáno v následujících příkladech.
Funkce Excel SUMIFS – syntaxe & využití
SUMIFS v aplikaci Excel použijete k vyhledání podmíněného součtu hodnot na základě více kritérií. Funkce SUMIFS byla představena v aplikaci Excel 2007, takže ji můžete použít ve všech moderních verzích aplikace Excel 2019, 2016, 2013, 2010 a 2007.
Ve srovnání s SUMIF je syntaxe SUMIFS o něco složitější :
První 3 argumenty jsou povinné, další rozsahy a jejich přidružená kritéria jsou volitelná.
-
sum_range– povinná jedna nebo více buněk. Může to být jedna buňka, řada buněk nebo pojmenovaná oblast. Sečteny jsou pouze buňky s čísly; prázdné a textové hodnoty jsou ignorovány. -
criteria_range1– je vyžadován první rozsah, který má být vyhodnocen podle přidružených kritérií. -
criteria1– první podmínka, která musí být splněna, je povinná. Kritéria můžete zadat ve formě čísla, logického výrazu, odkazu na buňku, textu nebo jiné funkce aplikace Excel. Můžete například použít kritéria jako 10, „> = 10″, A1, „třešně“ nebo TODAY (). -
criteria_range2, criteria2, …– toto jsou další rozsahy a kritéria s nimi spojená, volitelná. Ve vzorcích SUMIFS můžete použít až 127 párů rozsahů / kritérií.
A teď, dovolte “ Podívejte se na vzorec Excel SUMIFS se dvěma podmínkami. Předpokládejme, že máte tabulku se seznamem zásilek ovoce od různých dodavatelů. Názvy ovoce máte ve sloupci A, názvy dodavatelů ve sloupci B a množství ve sloupci C. To, co chcete, je zjistit součet částek vztahujících se k danému ovoci a dodavateli, např všechna jablka dodávaná Petem.
Když se učíte něco nového, je vždy dobré začít s jednoduchými věcmi. Nejprve tedy definujme všechny argumenty pro náš vzorec SUMIFS:
- sum_range – C2: C9
- criteria_range1 – A2: A9
- kritéria1 – „jablka“
- kritéria_rozsah2 – B2: B9
- kritéria2 – „Pete“
Nyní sestavte výše uvedené parametry a vy získá následující vzorec SUMIFS:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Upřesnit vzorec dále můžete nahradit textová kritéria „jablka“ a „Pete“ odkazy na buňky.V takovém případě nebudete muset změnit vzorec pro výpočet množství jiného ovoce od jiného dodavatele:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Používání SUMIFS a SUMIF v aplikaci Excel – co si pamatovat
Jelikož cílem tohoto tutoriálu je pokrýt všechny možné způsoby součtu hodnot podle několika podmínek, probereme příklady vzorců s oběma funkcemi – Excel SUMIFS a SUMIF s více kritérii. Abyste je mohli správně používat, musíte jasně pochopit, co mají tyto dvě funkce společné a v čem se liší.
Zatímco společná část je jasná – podobný cíl a parametry – rozdíly nejsou tak zřejmé, i když velmi zásadní.
1. Pořadí argumentů
Ve funkcích Excel SUMIF a SUMIFS je pořadí argumentů odlišné. Zejména je sum_range první parametr v SUMIFS, ale v SUMIF je třetí.
Na první pohled se může zdát, že Microsoft záměrně komplikuje křivku učení svým uživatelům. Howev ehm, při bližším pohledu uvidíte důvody. Jde o to, že sum_range je v SUMIF volitelný. Pokud to vynecháte, žádný problém, váš vzorec SUMIF sečte hodnoty v rozsahu (první parametr).
V SUMIFS je sum_range velmi důležitý a povinný, a proto je na prvním místě. Pravděpodobně si lidé z Microsoftu mysleli, že po přidání 10. nebo 100. páru rozsah / kritéria by někdo mohl zapomenout určit rozsah, který se má sečíst:)
2. Rozsah součtu a rozsah kritérií by měly být stejně velké
Ve funkci SUMIF nemusí mít argument sum_range nutně stejnou velikost jako argument rozsahu, pokud máte levou horní buňku vpravo. V Excelu SUMIFS musí každý rozsah_kritérií obsahovat stejný počet řádků a sloupců jako parametr sum_range.
Například =SUMIF(A2:A9,F1,C2:C18) vrátí správný výsledek, protože Excel uvažuje pouze buňka vlevo nahoře v argumentu sum_range (C2 v tomto příkladu, což je správné) a poté obsahuje tolik sloupců a řádků, kolik je obsaženo v argumentu range.
Dobře, dost strategie (tj. teorie) pojďme se pustit do taktiky (tj. příklady vzorců:)
Jak používat SUMIFS v aplikaci Excel – příklady vzorců
Před chvílí jsme diskutovali o jednoduchém vzorci SUMIFS se dvěma texty stejným způsobem můžete použít Excel SUMIFS s více kritérii vyjádřenými čísly, daty, logickými výrazy a dalšími funkcemi aplikace Excel.
Příklad 1. Excel SUMIFS s operátory porovnání
V naší tabulce dodavatelů ovoce předpokládejme, že chcete sečíst všechny dodávky od Mika s množstvím 200 nebo více. K tomu použijete operátor porovnání „větší než o r rovno „(> =) v kritériu2 a získejte následující vzorec SUMIFS:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Při projednávání funkce Excel SUMIF, stejných operátorů, jsme podrobně popsali všechny možné operátory porovnání lze použít v kritériích SUMIFS. Například následující vzorec s návratem součtu všech hodnot v buňkách C2: C9, které jsou větší nebo rovny 200 a menší nebo rovny 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Příklad 2. Používání Excel SUMIFS s daty
Pokud chcete sečíst hodnoty s více kritérii na základě aktuálního data, použijte funkci TODAY () ve vašem SUMIFS kritéria, jak je ukázáno níže. Následující vzorec shrnuje hodnoty ve sloupci D, pokud odpovídající datum ve sloupci C spadá do posledních 7 dnů, včetně dneška:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
Podobným způsobem můžete pomocí funkce Excel SUMIF sčítat hodnoty v daném časovém období. Například následující vzorec SUMIFS přidá hodnoty do buněk C2: C9, pokud datum ve sloupci B spadne mezi 1. října 2014 a 31. října 2014 včetně.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Stejného výsledku lze dosáhnout výpočtem rozdílu dvou funkcí SUMIF, jak je ukázáno v tomto příkladu – Jak použít SUMIF k součtu hodnot v daném časovém období. Excel SUMIFS je však mnohem jednodušší a srozumitelnější, že?
Příklad 3. Excel SUMIFS s prázdnými a neprázdnými buňkami
Při analýze přehledů a dalších údajů může často být nutné sčítat hodnoty odpovídající prázdným nebo neprázdným buňkám.
A teď se podívejme, jak můžete použít vzorec SUMIFS s kritérii „prázdné“ a „neprázdné“ na skutečné data.
Předpokládejme, že máte datum objednávky ve sloupci B, datum dodání ve sloupci C a množství. ve sloupci D. Jak zjistíte celkový počet produktů, které ještě nebyly dodány? To znamená, že chcete znát součet hodnot odpovídajících neprázdným buňkám ve sloupci B a prázdným buňkám ve sloupci C.
Řešením je použít vzorec SUMIFS se 2 kritérii:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Použití Excel SUMIF s více kritérii OR
Jak je uvedeno na začátku tohoto kurzu, funkce SUMIFS je navržena s logikou AND. Ale co když potřebujete sčítat hodnoty s více kritérii OR, tj. Když je splněna alespoň jedna z podmínek?
Příklad 1. SUMIF + SUMIF
Nejjednodušším řešením je sčítat výsledky vrácené několika funkcemi SUMIF. Například následující vzorec ukazuje, jak najít celkový počet produktů dodaných Mikem a Johnem:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Jak vidíte, první funkce SUMIF přidává množství odpovídající „Mikeovi“, druhá funkce SUMIF vrací částky související s „Johnem“ a poté přidáte tato 2 čísla.
Příklad 2. SUM & SUMIF s argumentem pole
Výše uvedené řešení je velmi jednoduché a může rychle dokončit práci, pokud existují pouze několik kritérií. Ale vzorec SUMIF + SUMIF může enormně vyrůst, pokud chcete sečíst hodnoty s více podmínkami NEBO. V tomto případě je lepším přístupem použití argumentu kritéria pole ve funkci SUMIF. Podívejme se nyní na tento přístup.
Můžete začít uvedením všech svých podmínek oddělených čárkami a následným uzavřením výsledného seznamu odděleného čárkami do {curly brackets}, což se odborně nazývá pole.
Argument pole skládající se ze 3 hodnot vynutí, aby váš vzorec SUMIF vrátil tři samostatné výsledky, ale protože zapíšeme vzorec do jedné buňky, vrátil by pouze první výsledek – tj. celkový počet produktů dodaných Johnem . Chcete-li, aby tento přístup založený na kritériích polí fungoval, musíte použít ještě jeden malý trik – uzavřete svůj vzorec SUMIF do funkce SUM, například takto:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Jak vidíte, díky kritériím pole je vzorec mnohem kompaktnější ve srovnání s SUMIF + SUMIF a umožňuje přidat tolik hodnot, kolik se vám v poli líbí.
Tento přístup funguje s čísly i s textovými hodnotami. Například pokud jste místo jmen dodavatelů ve sloupci C měli ID dodavatele jako 1, 2, 3 atd. ., pak by váš vzorec SUMIF vypadal podobně jako tento:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
Na rozdíl od textových hodnot nemusí být čísla uzavřena do uvozovek argumenty pole.
Příklad 3. SUMPRODUCT & SUMIF
V případě, že preferujete způsob, je vypsat kritéria v některých buňkách, spíše že specifikujte je přímo ve vzorci, můžete použít SUMIF ve spojení s funkcí SUMPRODUCT, která znásobí komponenty v daných polích a vrátí součet těchto produktů.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Kde G2: G4 jsou buňky obsahující vaše kritéria, jména dodavatelů v našem případě, jak ukazuje obrázek níže.
Ale samozřejmě vám nic nebrání výpis hodnot v kritériích pole vaší funkce SUMIF, pokud chcete:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
Výsledek vrácený oběma vzorci bude identické s tím, co vidíte na snímku obrazovky:
Excel SUMIFS s více kritérií OR
Pokud chcete podmíněně sčítat hodnoty v aplikaci Excel nejen s více podmínkami OR, ale s několika sadami podmínek, budete muset místo SUMIF použít SUMIFS. Vzorce budou velmi podobné tomu, o čem jsme právě hovořili.
Jako obvykle může příklad pomoci obrázek lépe ilustrovat. V naší tabulce dodavatelů ovoce přidejte datum dodání (sloupec E) a najděte celkové množství dodané Mikem, Johnem a Petem v říjnu.
Příklad 1. SUMIFS + SUMIFS
Vzorec vytvořený tímto přístupem zahrnuje spoustu opakování a vzhledů těžkopádné, ale snadno pochopitelné a hlavně funguje:)
Příklad 2. SUM & SUMIFS s argumentem pole
nažil jsem se vysvětlit podstatu tohoto přístupu na příkladu SUMIF, takže nyní můžeme tento vzorec jednoduše zkopírovat, změnit pořadí argumentů (jak si pamatujete, v SUMIF a SUMIFS se liší) a přidat další kritéria. výsledný vzorec je kompaktnější než SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
Výsledek vrácený tímto vzorcem je přesně stejný, jak vidíte v výše uvedený snímek obrazovky.
Exampl e 3. SUMPRODUCT & SUMIFS
Jak si pamatujete, přístup SUMPRODUCT se od předchozích dvou liší tím, že zadáváte každé z vašich kritérií do samostatné buňky spíše je specifikujte přímo ve vzorci. V případě více sad kritérií nebude funkce SUMPRODUCT dostačující a budete muset použít také ISNUMBER a MATCH.
Takže za předpokladu, že názvy zásob jsou v buňkách H1: H3, počáteční datum je v buňce H4 a konečné datum v buňce H5, má náš vzorec SUMPRODUCT následující tvar:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Mnoho lidí se diví, proč používat ve vzorcích SUMPRODUCT dvojité pomlčky (-). Jde o to, že Excel SUMPRODUCT ignoruje všechny kromě číselných hodnot, zatímco operátory porovnání v našem vzorci vracejí logické hodnoty (TRUE / FALSE), které jsou nečíselné. Chcete-li převést tyto booleovské hodnoty na 1 „sa 0“, použijete znaménko dvojitého mínusu, které se odborně nazývá operátor dvojitého unárního výrazu. První unár vynucuje TRUE / FALSE na -1/0. Druhý unární neguje hodnoty, tj. Obrátí znaménko a promění je na +1 a 0, čemuž funkce SUMPRODUCT rozumí.
Doufám, že výše uvedené vysvětlení má smysl. A i když to nejde, zapamatujte si toto základní pravidlo – při použití operátorů porovnání ve vzorcích SUMPRODUCT použijte dvojitý unární operátor (-).
Použití součtu Excel ve vzorcích pole
Jak si pamatujete, Microsoft implementoval funkci SUMIFS v aplikaci Excel 2007. Pokud někdo stále používá Excel 2003, 2000 nebo starší, budete muset použít vzorec pole SUM k přidání hodnot s více kritérii AND. Tento přístup přirozeně funguje také v moderních verzích aplikace Excel 2013 – 2007 a lze ji považovat za staromódní protějšek funkce SUMIFS.
Ve výše diskutovaných vzorcích SUMIF jste již použili argumenty pole, ale maticový vzorec je něco jiného.
Příklad 1. Součet s více kritérii AND v aplikaci Excel 2003 a starší
Vraťme se k úplně prvnímu příkladu, kde jsme zjistili součet částek vztahujících se danému ovoci a dodavateli:
Jak již víte, tohoto úkolu lze snadno dosáhnout pomocí běžný vzorec SUMIFS:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Nyní se podívejme, jak lze stejný úkol splnit v raných verzích aplikace Excel bez SUMIFS . Nejprve si zapište všechny podmínky, které by měly být splněny, ve formě range = „condition“. V tomto příkladu máme dva páry rozsah / podmínka:
Podmínka 1: A2: A9 = „jablka“
Podmínka 2: B2: B9 = „Pete“
Poté napíšete SUMY vzorce, které „znásobí“ všechny vaše páry rozsah / podmínka, každý uzavřený v závorkách. Posledním multiplikátorem je rozsah součtu, v našem případě C2: C9:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Jak je znázorněno na následujícím obrázku, vzorec dokonale funguje v nejnovější verzi aplikace Excel 2013.
Příklad 2. SUM maticové vzorce v moderních verzích aplikace Excel
Sudé v moderních verzích aplikace Excel by neměl být podceňován výkon funkce SUM. Vzorec pole SUM není jen gymnastikou mysli, ale má praktickou hodnotu, jak ukazuje následující příklad.
Předpokládejme , máte dva sloupce, B a C, a musíte spočítat, kolikrát je sloupec C větší než sloupec B, když je hodnota ve sloupci C větší nebo rovna 10. Okamžité řešení, které vás napadne, je použití součtu vzorec pole:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
Nevidím žádné praktické aplikace na výše uvedený vzorec? Přemýšlejte o tom jiným způsobem:)
Předpokládejme, že máte seznam objednávek, jak je zobrazen na následujícím obrázku, a chcete vědět, kolik produktů nebylo k danému datu dodáno v plném rozsahu. Přeloženo do jazyka aplikace Excel máme následující podmínky:
Podmínka 1: Hodnota ve sloupci B (objednané položky) je větší než 0
Podmínka 2: Hodnota ve sloupci C (Dodáno) v méně než ve sloupci B
Podmínka 3: Datum ve sloupci D (Datum splatnosti) je menší než 1. 11. 2014.
Uvedení tří rozsahů / dohromady párů podmínek, získáte následující vzorec:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Příklady vzorců popsané v tomto tutoriálu pouze poškrábaly povrch toho, co funkce Excel SUMIFS a SUMIF opravdu dokážou. Ale doufejme, že vám pomohly nasměrovat vás správným směrem a nyní můžete sečíst hodnoty v sešitech aplikace Excel bez ohledu na to kolik složitých podmínek musíte vzít v úvahu.
- Jak používat SUMIF v aplikaci Excel – příklady vzorců pro podmíněné sčítání buněk
- Jak používat Excel SUMIFS a SUMIF s více kritérii – příklady vzorců
- Jak používat funkce VLOOKUP & SUM nebo SUMIF v aplikaci Excel
- SUMIF v Tabulkách Google s příklady vzorců
- COUNTIF v aplikaci Excel – počet, pokud ne prázdné, větší než, duplicitní nebo jedinečné
- Jak počítat a sečíst buňky podle barvy v aplikaci Excel