Sumif s více kritérii založenými na logice OR a AND
Při práci na listech aplikace Excel může být nutné sečíst hodnoty na základě více kritérií. Někdy je více kritérií ze stejného sloupce (logika NEBO), ale někdy z různých sloupců (logika AND). Jak byste se v tomto případě mohli vypořádat s tímto úkolem v Excelu?
- Sumif s více kritérii založenými na logice NEBO
- Sumif s více kritérii založenými na logice AND pomocí funkce SUMIFS
Sumif s více kritérii založenými na logice NEBO
Předpokládejme, že máte seznam produktů, který obsahuje dodavatele a množství, jak je uvedeno níže, nyní chcete získat všechna celková množství, která dodává dodavatel John a David. Zde pro vás představím dva jednoduché vzorce.
Pomocí vzorce SUMIF + SUMIF +…
Pokud chcete sečíst čísla, která splňují některé z kritérií (OR) z více kritérií, můžete sečíst několik funkcí SUMIF do jednoho vzorce, obecná syntaxe je:
- criteria_range: Rozsah buněk, které mají odpovídat kritériím;
- criteria1: První kritérium, které se používá k určení, které buňky se mají sečíst;
- criteria2: Druhé kritérium, které se používá k určení, které buňky se mají sečíst;
- sum_range: Rozsah buněk, ze kterých chcete sčítat.
Nyní prosím zkopírujte nebo zadejte některý z níže uvedených vzorců do prázdné buňky a stiskněte vstoupit klíč k získání výsledku:
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14) (Use a cell reference)
Vysvětlení vzorce:
=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)
- První SUMIF(B4:B14,"Jan";C4:C14) najde řádky Jana a sečte celková množství;
- Druhý SUMIF(B4:B14,"David";C4:C14) najde řádky Davida a sečte celková množství;
- Poté sečtěte tyto dva vzorce SUMIF, abyste získali všechna celková množství dodaná jak Johnem, tak Davidem.
Pomocí funkcí SUM a SUMIF
Výše uvedený vzorec se velmi snadno používá, pokud existuje pouze několik kritérií, ale pokud chcete sečíst hodnoty s více podmínkami NEBO, může být výše uvedený vzorec nadbytečný. V tomto případě vám může pomoci lepší vzorec, který je vytvořen na základě funkcí SUM a SUMIF. Obecné syntaxe jsou:
Obecný vzorec s pevně zakódovaným textem:
- criteria_range: Rozsah buněk, které mají odpovídat kritériím;
- criteria1: První kritérium, které se používá k určení, které buňky se mají sečíst;
- criteria2: Druhé kritérium, které se používá k určení, které buňky se mají sečíst;
- sum_range: Rozsah buněk, ze kterých chcete sčítat.
Obecný vzorec s odkazy na buňky:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Rozsah buněk, které mají odpovídat kritériím;
- criteria_cells: Buňky obsahující kritéria, která chcete použít;
- sum_range: Rozsah buněk, ze kterých chcete sčítat.
Zadejte nebo zkopírujte některý z níže uvedených vzorců, které potřebujete, do prázdné buňky a poté získejte výsledek:
=SUM(SUMIF(B4:B14, E4:E5, C4:C14)) (Use cell references, array formula, should press Ctrl + Shift + Enter keys)
Vysvětlení vzorce:
=SUM(SUMIF(B4:B14; {"Jan","David"}; C4:C14))
> SUMIF(B4:B14; {"John","David"}; C4:C14):
- {"Jan","David"}: Konstanta pole, která je sadou více kritérií poskytovaných ve složených závorkách.
- SUMIF(B4:B14,"David";C4:C14) Konstanta pole používající logiku OR nutí funkci SUMIF sčítat čísla v C4:C14 na základě jednoho z více kritérií ("John" a "David") a vrátí dva samostatné výsledky: {410,1520}.
> SUM(SUMIF(B4:B14, {"Jan","David"}, C4:C14))=SUM({410,1520}): Nakonec tato funkce SUM sečte tyto výsledky pole a vrátí výsledek: 1930.
Sumif s více kritérii založenými na logice AND pomocí funkce SUMIFS
Pokud chcete sečíst hodnoty s více kritérii v různých sloupcích, můžete tento úkol rychle vyřešit pomocí funkce SUMIF. Obecná syntaxe je:
- sum_range: Rozsah buněk, ze kterých chcete sčítat;
- criteria_range1: Rozsah, kde je použito kritérium1;
- criteria1: První kritérium, které se kontroluje podle rozsahu_kritérií1 a určuje, které buňky se mají přidat; (typ kritérií může být: číslo, logický výraz, odkaz na buňku, text, datum nebo jiná funkce Excelu.)
- criteria_range2, criteria2…: Další dodatečné rozsahy a související kritéria. (ve vzorci SUMIFS můžete nastavit 127 párů kritérií_rozsah a kritéria.)
Řekněme, že mám tabulku se sloupci Produkt, Dodavatel, Množství a Cena, jak je uvedeno níže. Nyní chci zjistit součet celkové ceny produktu Apple a množství, které je větší nebo rovno 200.
Aplikujte prosím některý z níže uvedených vzorců do prázdné buňky a stiskněte vstoupit klíč k vrácení výsledku:
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4) (Use cell references)
Vysvětlení vzorce:
=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")
- Rozsah A4:A14 kontroluje produkt „Apple“ a rozsah C4:C14 extrahuje všechna množství větší nebo rovna 200, poté funkce SUMIFS sečte pouze odpovídající buňky z rozsahu D4:D14.
Použitá relativní funkce:
- SUMIF:
- Funkce SUMIF může pomoci sečíst buňky na základě jednoho kritéria.
- SUMIFS:
- Funkce SUMIFS v Excelu může pomoci sečíst hodnoty buněk na základě více kritérií.
Další články:
- Součet, pokud začíná nebo končí konkrétním textem nebo znaky
- Chcete-li sečíst hodnoty, pokud odpovídající buňky začínají nebo končí určitou hodnotou, můžete použít funkci SUMIF se zástupným znakem (*), abyste ji vytáhli. Tento článek podrobně představí, jak používat vzorec.
- Sečíst, pokud buňky obsahují konkrétní text v jiném sloupci
- Tento tutoriál vám ukáže, jak sečíst hodnoty, pokud buňky obsahují konkrétní nebo částečný text v jiném sloupci. Vezměte si níže uvedený rozsah dat jako příklad, abyste získali celkové množství produktů obsahujících text „T-Shirt“, s tímto úkolem v Excelu si poradí jak funkce SUMIF, tak funkce SUMPRODUCT.
- Součet nejmenších nebo dolních hodnot N v aplikaci Excel
- V Excelu je pro nás snadné sečíst rozsah buněk pomocí funkce SUM. Někdy může být nutné sečíst nejmenší nebo dolní 3, 5 nebo n čísel v datovém rozsahu, jak je znázorněno na obrázku níže. V tomto případě vám SUMPRODUCT spolu s funkcí SMALL může pomoci vyřešit tento problém v Excelu.
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel - pomůže vám vyniknout před davem
Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...
Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)
- Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
- Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
- Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
- Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
